Recreate the standby controlfile

Recreate the standby controlfile  Without RMAN

Check if flashback is enabled on the standby database

1. SQL_STANDBY> alter database recover managed standby database cancel;

2. SQL_STANDBY> shutdown immediate;   and Backup the control files for the standby database


3. SQL_PRIMARY> alter system set log_archive_dest_state_2=DEFER;

4. SQL_PRIMARY> alter database create standby controlfile as '/data3/oradata/FNGPROD/standby_ctlfile/standby.ctl';

5. Copy the standby.ctl from primary to locations mentioned in control_files in standby server


6. SQL_STANDBY> startup nomount;

7. SQL_STANDBY> alter database mount standby database;

8. SQL_STANDBY> alter database rename file '/old_location/file_name' to '/new_location/file_name';
                                                                                                   

9. SQL_STANDBY> alter database recover managed standby database disconnect from session;

10. SQL_PRIMARY> alter system set log_archive_dest_state_2=ENABLE;



Using RMAN for ASM

Check if flashback is enabled on the standby database

1. SQL_STANDBY> alter database recover managed standby database cancel;

2. SQL_STANDBY> shutdown immediate;   and Backup the control files for the standby database

RMAN_STDBY > backup current controlfile format '/tmp/standby.ctl';

3. SQL_PRIMARY> alter system set log_archive_dest_state_2=DEFER;

4. SQL_PRIMARY> alter database create standby controlfile as '/data3/oradata/FNGPROD/standby_ctlfile/standby.ctl';

5. Copy the standby.ctl from primary to locations mentioned in control_files in standby server


6. SQL_STANDBY> startup nomount;


RMAN_STDBY > restore controlfile from '/tmp/primary.ctl';
RMAN_STDBY > "sql alter database mount;"
RMAN_STDBY > catalog start with '+data/stdby/datafiles_locations/';
RMAN_STDBY > switch database to copy;

9. SQL_STANDBY> alter database recover managed standby database disconnect from session;

10. SQL_PRIMARY> alter system set log_archive_dest_state_2=ENABLE;