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.

Resize Online Redo log on Production database

Here we are going to discuss about how to resize online redo log on production database. Once we had a recommendation and request of resizing online redo log on production database as we were having performance issue ( log file sync wait event). Following post involves doing same activity on different environment we are in. There is no other way till date except you have to add the group and drop the old groups. That is recreating the redo log.

1) Resize Redo Log  on Standalone file system Database.

2) Resize Redo Log  on Standalone ASM Database.

3) Resize Redo Log  on RAC database.

It is a online activity. Though it is advisable to do it on minimum load on the system. 

Pre-cautions :-

1. Make sure you have enough space available on the Redo log member mount point/drive.

2. Keep all the logs before proceeding with any activity. 

3. Always make the commands on a notepad before typing it directly on sql prompt to avoid raising different issue we will discuss in different post :P 

Resize Online Redo Log on Standalone Production Database.

Step 1 Check the database name, status and role just to make sure you logged in to desired db (if you have multiple database on that server) -

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

Step 2 Now check the size of redo log member -

set linesize 1000
set pagesize 1000
set trim on
set lines 120
col group# format 999
col thread# format 999
col member format a80 wrap
col status format a10
col archived format a10
select lg.group#, lg.thread#,lgf.member,lg.archived,lg.status,(bytes/1024/1024) fsize from v$log lg, v$logfile lgf
where lgf.group# = lg.group# order by 1,2;

You will have a output as shown in following manner - In my environment, we had 5 redo log groups which were multiplexed in two different mount point. /u01 and /u02.

GROUP# THREAD# MEMBER                             ARCHIVED STATUS    MB
------ ------- ---------------------------------- -------- --------- ---
     3       1 /u01/oradata/redologs/redo03a.log  NO       INACTIVE  100
     3       1 /u02/oradata/redologs/redo03b.log  NO       INACTIVE  100
     4       1 /u01/oradata/redologs/redo04a.log  NO       ACTIVE    100
     4       1 /u02/oradata/redologs/redo04b.log  NO       ACTIVE    100
     5       1 /u01/oradata/redologs/redo05a.log  NO       CURRENT   100
     5       1 /u02/oradata/redologs/redo05b.log  NO       CURRENT   100
     6       1 /u01/oradata/redologs/redo06a.log  NO       INACTIVE  100
     6       1 /u02/oradata/redologs/redo06b.log  NO       INACTIVE  100
     7       1 /u01/oradata/redologs/redo07a.log  NO       INACTIVE  100
     7       1 /u02/oradata/redologs/redo07b.log  NO       INACTIVE  100

Step 3 Following is the command to create redo log group. Since we already had 3 to 7 redo group, we can't add a new redolog with same number, Hence redo log group got a new number that is 8. Since that redo log member were multiplexed hence there are two file name mentioned in below location. Please note for illustration purpose I have reduced the size to 10M, you have to keep whatever you want to set.

alter database add logfile group 8 ('/u01/oradata/redologs/redo03a.log','/u02/oradata/redologs/redo03b.log') size 10M;

Post addition always crosscheck with following command,

   SQL> select group#, status from v$log;                                   

      GROUP# STATUS
   --------- ----------------
           1 UNUSED
           2 UNUSED
           3 CURRENT
           4 INACTIVE
           5 INACTIVE
           6 INACTIVE
           7 INACTIVE
           8 UNUSED

Step 4 Now you have to follow step 3 and add same number of online redo log group as we had earlier.Here we had 7 online redo log groups, so I have added 7 more online redo log groups following same step 3. ** repeat as necessary until group 8 is CURRENT **

Step 5 Here if you want to drop the groups you have to make sure it is INACTIVE . You can't drop a online redo log group if it is ACTIVE or CURRENT.

Step 6 To make CURRENT TO ACTIVE and then  ACTIVE --> INACTIVE fire switch logfile command-

alter system switch logfile;

alter system checkpoint

Step 7 Now drop all the old redo log groups once they are INACTIVE.

                            

  SQL> alter database drop logfile group 4;
  SQL> alter database drop logfile group 5;
  SQL> alter database drop logfile group 6;


Now just crosscheck the size if it the one which you were looking for.

select group#, bytes, status from v$log;

      GROUP#     BYTES STATUS
   --------- --------- ----------------
           8  10240    CURRENT
           9  10240    UNUSED
          10  10240    UNUSED       

    . . . .<output trimmed> . . . .

Step 8 Delete the files physically if OMF is not enabled.

rm /u01/oradata/redologs/redo03a.log

rm /u02/oradata/redologs/redo03b.log

rm /u01/oradata/redologs/redo04a.log

rm /u02/oradata/redologs/redo04b.log

rm /u01/oradata/redologs/redo05a.log

rm /u02/oradata/redologs/redo05b.log

rm /u01/oradata/redologs/redo06a.log

rm /u02/oradata/redologs/redo06b.log

rm /u01/oradata/redologs/redo07a.log

rm /u02/oradata/redologs/redo07b.log

Resize Online Redo Log on Standalone ASM Database.

When it comes to asm, oracle recommends to enable OMF (Oracle Manage Files) . Here we are doing it with OMF. Make sure these parameters are set - DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n

Changes in OMF and ASM - 

1) When you are adding a diskgroup, never miss the + before diskgroup name, else you will see the redo getting created in $ORACLE_HOME location.

2)You don't have to mention absolute path, while creating redo log group, instead we will mention diskgroup name. For example - 

alter database add logfile group 8 ('+REDO1_CBS','+REDO2_CBS') size 10M;

Rest all steps will be same till 7 as step 8 will not be involved in OMF.

Resize Online Redo Log on RAC Database.

As you all know RAC database involves multiple threads hence your add command will change reset all process will be same. So while adding Online Redo Log (ORL) you have to use following command -  Only Highlighted one is the difference.

alter database add logfile thread 1 group 8 ('+REDO1_CBS','+REDO2_CBS') size 10M;

For every instance we will add desired online redo log group.