How to Recreate Standby Redo logs on Standby Database

Hello, Today we were increasing the size of redo logs. And since we had standby redologs as well. So we had to resize the standby redo logs too. I will create a detailed blog post on how to resize redo log groups. Before that, let me tell you what happened today. For now lets talk about -> How to Recreate Standby Redo logs on Standby Database

Standby redo log should be of same size as that of redo log size and number of groups should be +1 of whatever number of redo log groups you have it on your database.

Here is test case environment ->

1) ORACLE VERSION -> 11.2.0.4
2) SINGLE INSTANCE PRODUCTION and SINGLE INSTANCE STANDBY
3) OS -> RHEL 6

In our test case we are trying to resize the standby redo logs from 50M to 100M since we have resized the redo log groups from 50M to 100M. Please note my standby database is in mount stage and are not using any of the standby redo log groups.

Prechecks ->

Please check if you have enough space to hold the new size of standby redologs. Once this holds true, you can start with recreation Standby Redo logs on Standby Database

Steps to recreate standby redo log groups ->

1 -> Check status of existing standby redo logs before you start the activity -> How to Recreate standby Redo logs on Standby Database

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
sansui    MOUNTED              PHYSICAL STANDBY

SQL> select group#,type,member from v$logfile order by group#;

    GROUP# TYPE    MEMBER                                      
---------- ------- --------------------------------------------
         1 ONLINE  /sansui/redo/redo1.log                      
         2 ONLINE  /sansui/redo/redo2.log                      
         3 ONLINE  /sansui/redo/redo3.log                      
         4 STANDBY /sansui/redo/standby_redo1.log              
         5 STANDBY /sansui/redo/standby_redo2.log              
         6 STANDBY /sansui/redo/standby_redo3.log              
         7 STANDBY /sansui/redo/standby_redo4.log              

7 rows selected.

SQL> select group#, bytes/1024/1024, status from v$standby_log;

GROUP#     BYTES/1024/1024 STATUS  
---------- --------------- ---------- 
         4              50 UNASSIGNED
         5              50 UNASSIGNED
         6              50 UNASSIGNED
         7              50 UNASSIGNED

4 rows selected.

SQL>

2 -> Stop Media Recovery of the physical standby database and change the standby_file_management

SQL> -- Stop MRP
SQL> alter database recover standby database cancel;

Database altered.

SQL> -- Change parameter from AUTO to Manual
SQL> alter system set standby_file_management='MANUAL' ;

System altered.

3 -> Here first we will try to drop existing standby redo log groups

Syntax -> 

alter database drop standby logfile group <group#>;

In our case we had following group 4, group 5. group 6 and group 7 standby redo log

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

4 -> Now we are going Add standby redo log groups

Syntax --> 
alter database add standby logfile thread <thread no> group <group no> ('<member1_file_name>') size <size>;

We usually don’t multiplex standby redo log groups, though option is available. In our test case we had 3 redo log groups hence we are creating 4 standby redo log groups.

SQL> alter database add standby logfile thread 1  group 4 ('/sansui/redo/standby_redo_new01.log') size 100M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 ('/sansui/redo/standby_redo_new02.log') size 100M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 ('/sansui/redo/standby_redo_new03.log') size 100M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 ('/sansui/redo/standby_redo_new04.log') size 100M;

Database altered.

5 -> Here we have added standby redo logs. Now it’s time to revert back the changes we did before starting the activity.

SQL> -- Change parameter from Manual to AUTO
SQL> alter system set standby_file_management='AUTO';

System altered.


SQL> -- Start MRP
SQL> alter database recover standby database disconnect from session;

Database altered.

End of Activity!

But, I know this won’t be that easy for you. We always have challenges finding all the scenarios under one article. So I am trying to consolidate it for you. In your environment you will have different cases. Since I have faced these issues. What if you are using standby redo logs. And it’s active or it’s clearing_current.

Let’s talk when you have status as “Clearing_curent”.

For Instance, we have group 5 as “clearing_current” and you are trying to drop it and you will have following error ->

ERROR at line 1:
ORA-01609: log 5 is the current log for thread 1 - cannot drop members
ORA-00312: online log 5 thread 1: 'logfile name'
ORA-00312: online log 5 thread 1: 'logfile name'

Solution ->
You can just clear the standby redo log group before dropping it.

Syntax ->
alter database clear logfile group <group#>;

In our case , it’s group 5, so my command will be ->

SQL>alter database clear logfile group 5;
SQL>alter database drop  standby logfile group 5;

And in case you find a standby redo group in active status.

Since standby redo log is in use, there is fair chances you will find a group in active status. In this case you have to just shutdown the database, bring it to mount stage and drop the standby redo log group.

Reference –> How to Drop/recreate A Standby Redo Log Group From Standby Database Which is Active status (Doc ID 2516522.1)

On Primary database ->

SQL>alter system switch logfile;

On Standby Database -->

Check if it is moved other group. 

SQL>select group#, thread#,  status from v$standby_log;

If standby redo log group 4 is still ACTIVE, 

SQL>shutdown abort;

SQL>startup mount;

SQL>shutdown immediate;

$ ps -ef |grep -i <standby instance_name>

$ kill -9 <ospid>

SQL>startup mount;

Make sure MRP is not running, stanby_file_management is changed And then try to drop that standby redo log group.

What if you have a disk group which doesn’t exists on standby?

For instance. the scenario where you have copied the control file from primary to standby and standby doesn’t have only one diskgroup which had standby redolog groups. In this case you will face issues while activating standby database.

Solution ->

Clear the standby redolog group and drop the standby redo log groups.

Thank you for reading. I have tried to cover all the scenarios I came across so far. Hope you enjoyed reading this blog. We will meet you in next blog, until then Keep Reading!