Moving Datafile on an Oracle Physical Standby

There are situations where the standby server does not have the same mounts as the prod server. In this situation when the SA is incapable, as a DBA you can be a hero again by following these steps.


Steps

1. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --stop it

2. alter system set standby_file_management=manual;

3. shweeb.ecp.priv:/dba0B/oradata/ORAP> mv APPS_TS_SUMMARY05.dbf /dba0A/oradata/ORAP
    shweeb.ecp.priv:/dba0B/oradata/ORAP> mv APPS_TS_SUMMARY06.dbf /dba0A/oradata/ORAP

4.alter database rename file '/dba0B/oradata/ORAP/APPS_TS_SUMMARY05.dbf' to '/dba0A/oradata/ORAP/APPS_TS_SUMMARY05.dbf';

alter database rename file '/dba0B/oradata/ORAP/APPS_TS_SUMMARY06.dbf' to '/dba0A/oradata/ORAP/APPS_TS_SUMMARY06.dbf';


5. shutdown immediate;
6. startup nomount;
7. alter database mount standby database;
8. alter database recover managed standby database disconnect from session;

Check the progress

1. DR Log sync

--Checking Logs

select max(sequence#) from v$archived_log where applied='YES';

select sequence# from v$archived_log where applied='NO';

--CHECK WHERE IT IS STUCK

select SEQUENCE#,DELAY_MINS,STATUS,PROCESS FROM v$managed_standby;