How To Recreate a Physical Standby Control file

Recreate of Physical Standby control file is done by us in many cases. When you want to apply incremental backup, we recreate a new control file and restore the control file on physical standby. 

Recreate a Physical Standby Control file requires down time of standby database. Make sure we have downtime in place. As in some environment, Physical Standby  is used for reporting purpose. 

Pre-caution :-

1. Always check status of database, as we are going to shutdown the database.

2. Always your a different location for creating backup control file.

3. Close all the other sessions whenever you are doing an activity which involves shutdown of database.

4. Always make the commands on a notepad before typing it directly on sql prompt.

5. Make sure file structure at production and standby is same ( recommended) but for reason it is not there then additional steps might be required to perform.

 Here we will cover all ways for How To Recreate a Physical Standby Control file -

1) How To Recreate a Physical Standby Control file on Standalone with file system.
2) How To Recreate a Physical Standby Control file on Standalone with ASM.
3) How To Recreate a Physical Standby Control file on RAC.

How To Recreate a Physical Standby Control file on Standalone with file system

Step 1. Log on to standby database. Check the database status -just to make sure we are connected to right database.

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
CBSLIVE   MOUNTED              PHYSICAL STANDBY

Take down all the details of datafile location, redolog location, controlfile location and make sure db is started with SPFILE.

select name from v$datafile;

select member from v$logfile;

show parameter spfile;

Step 2. Stop Managed Recovery Process on the Standby Database - 

SQL> alter database recover managed standby database cancel;

Step 3. Shutdown the Standby Database 

SQL> shutdown immediate;

3. Connect to Primary database using SYS as sysdba log in - And check the status.

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
CBSLIVE   READ WRITE           PRIMARY

Step 4. Create a new Physical Standby Controlfile from the Primary - 

SQL> alter database create standby controlfile as '/rmanbkp/controlfile/control_for_standby';

Step 5. Backup all the control files on standby database using cp command.

cp /u01/oradata/control/control01.ctl  /rmanbkp/

cp /u02/oradata/control/control02.ctl  /rmanbkp/

cp /u03/oradata/control/control03.ctl  /rmanbkp/

Step 6. SCP ( Transfer the file server.) /rmanbkp/controlfile/control_for_standby to standby server from primary 

Step 7. On Standby database replace all the controlfiles with newly created standby control file.

We had 3 controlfile in our case.

cp /rmanbkp/control_for_standby /u01/oradata/control/control01.ctl

cp /rmanbkp/control_for_standby /u02/oradata/control/control02.ctl

cp /rmanbkp/control_for_standby /u03/oradata/control/control03.ctl

Step 8. Startup the standby database and mount the standby database

SQL> startup nomount;

SQL> alter database mount standby database;

Step 9. If filesystem at standby differs from that of primary, then make sure 

a) set db_file_name_convert or/and log_file_name_convert init parameters in the standby database. 

or 

b) Update the control file by following commands - ( make sure STANDBY_FILE_MANAGEMENT is  MANUAL when you fire below command ) 

SQL> alter database rename file <file#> to '<new File location and file name>';

After completion of rename for all the files which were at different location, make sure STANDBY_FILE_MANAGEMENT is back to what it was.

10. Start Managed Recovery again:

SQL> alter database recover managed standby database disconnect using current logfile parallel 8;

How To Recreate a Physical Standby Control file on Standalone with ASM.

There is only two differences in steps when you are handling a standalone database with asm 

1 The way you take backup (Step 4)

2 The way you restore the backup (Step 7)
Always make sure your database is started with SPFILE , if it is started with pfile then you need to update the control_files location in pfile.

Step 4. Create a new Physical Standby Controlfile from the Primary -

$ export ORACLE_SID=IMPS
RMAN> backup current controlfile for standby format '\rmanbkp\controlfile_for_standby.bkp';


Step 7. Restore the standby control file on standby database.

RMAN> restore standby controlfile from '\rmanbkp\controlfile_for_standby.bkp';

Starting restore at 10-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+DATA_IMPS01/IMPS/controlfile/current.267.531045489
output filename=+DATA_IMPS02/IMPS/controlfile/current.257.661096899
output filename=+DATA_IMPS03/IMPS/controlfile/current.327.222958919
Finished restore at 10-AUG-19
RMAN> exit

How To Recreate a Physical Standby Control file on RAC.

When you have multiple nodes, you need to stop the database on both the nodes. And during restore of controlfile, you need to make sure only one instance is up. Rest all steps are same.