Resize Redo Logs on Primary and Standby

RESIZE REDO LOGS ON PRIMARY AND STANDBY

Applies to: 

Oracle Database 10gR2

Description:

Need to increase the size of existing Redo Logs. In current environment there is one physical standby database.

Solution:

SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

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

    GROUP# STATUS

---------- ----------------

         1 CURRENT

         2 INACTIVE

         3 INACTIVE

SQL> alter system switch logfile;

SQL> alter system switch logfile;

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

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

-- To make primary redo log changes on primary and standbys: set

-- STANDBY_FILE_MANAGEMENT to MANUAL so file changes not transferred to standby

SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL scope=both;

To stop Redo Apply, issue the following SQL statement on Standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Drop the redo log group on Primary:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: '<file_name>'

 
Perform a checkpoint to resolve the above issue if occur:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Re-create dropped online redo log group on Primary:

Re-create the dropped redo log group with different size (if desired):

SQL> alter database add logfile group 1('/d01/oracle/papsdata/log01a.dbf', '/d01/oracle/papsdata/log01b.dbf') size 100M;

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

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         2 INACTIVE

         3 CURRENT

SQL> alter system switch logfile;

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

    GROUP# STATUS

---------- ----------------

         1 CURRENT

         2 INACTIVE

         3 ACTIVE

Repeat the above step to re-create all the redo logs.

Drop Redo Logs on Standby

Check the Status of the Online Redo log Group.

SQL> SELECT GROUP#, STATUS FROM V$LOG;

GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING

If Status is CLEARING_CURRENT or CURRENT then you cannot drop Online Redo log Group. You will get ORA-01623 if you try to drop a Redo log Group with Status CLEARING_CURRENT or CURRENT.

For Status CLEARING, UNUSED, INACTIVE please follow below steps.
Clear the Online Redo Log Group:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

If you have skipped the above step then you will get ORA-01624 while dropping the online redo log Group with Status CLEARING

Drop the Online Redo Log Group:

SQL>ALTER DATABASE DROP LOGFILE GROUP 2; 

Add Redo Logs on Standby

 Add Redo log File Group:

SQL> alter database add logfile group 2 ('/d01/oracle/papsdata/log02a.dbf',  '/d01/oracle/papsdata/log02b.dbf') size 100M reuse;

Database altered.

Add as many Redo Logfile Groups (or Members) you want to add.

Set STANDBY_FILE_MANAGEMENT to AUTO.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both;

Start Redo Apply (Managed Recovery):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

Size of Redo Logs have been successfully increased on Primary and also on Physical Standby database.

Your comments, especially which will help us improve the functionality, will be greatly appreciated :)