Adding and Dropping Redo Logfiles in Physical Standby Database

Adding and Dropping Redo Logfiles in Standby Database

Adding Redo Logfiles in Standby Database:

Adding Redo Logfiles and Standby Logfiles in Primary Database does not affect the Standby Database.
But during switchover some performance issues occur. So Whenever a logfile is added or removed from the primary database, the same should be done in the standby database also manually.

1. Check the member present in standby database.

SQL> select member from v$logfile;


MEMBER
----------------------------------
D:\STANDB1\REDO03.LOG
D:\STANDB1\REDO02.LOG
D:\STANDB1\REDO01.LOG

2. Cancel the recovery Process.

SQL> alter database recover managed standby database cancel;
Database altered.

3. Check the standby_file_management parameter and set the parameter to MANUAL.

SQL> show parameter standby_file_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
standby_file_management              string      AUTO

SQL> alter system set standby_file_management=manual scope=both;
System altered.

SQL> show parameter standby_file_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
standby_file_management              string      MANUAL

4. Add Redo Logfiles in Standby Database.

SQL> alter database add logfile group 4 'D:\STANDB1\REDO04.LOG' size 100M;
Database altered.

SQL> alter database add logfile group 5 'D:\STANDB1\REDO05.LOG' size 100M;
Database altered.

SQL> alter database add logfile group 6 'D:\STANDB1\REDO06.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 7 'D:\STANDB1\STDREDO07.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 8 'D:\STANDB1\STDREDO08.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 9 'D:\STANDB1\STdREDO09.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 10 'D:\STANDB1\STdREDO10.LOG' size 100M;
Database altered.

5. Check the Redo Logfiles.

SQL> col members for 999999999
SQL> set lines 120 pages 1000
SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CLEARING
         2          1 CLEARING
         3          1 CLEARING
         4          1 UNUSED
         5          1 CLEARING_CURRENT
         6          1 UNUSED

6 rows selected.

SQL> select member from v$logfile;

MEMBER
------------------------
D:\STANDB1\REDO03.LOG
D:\STANDB1\REDO02.LOG
D:\STANDB1\REDO01.LOG
D:\STANDB1\REDO04.LOG
D:\STANDB1\REDO05.LOG
D:\STANDB1\REDO06.LOG
D:\STANDB1\STDREDO07.LOG
D:\STANDB1\STDREDO08.LOG
D:\STANDB1\STDREDO09.LOG
D:\STANDB1\STDREDO10.LOG

10 rows selected.

6. Check the standby_file_management parameter and set the parameter to AUTO.

SQL> alter system set standby_file_management=auto scope=both;
System altered.

7. Activate the Recovery Process.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

Dropping Redo Logfiles On Physical Standby Database

1. Cancel the recovery process.

SQL> alter database recover managed standby database cancel;
Database altered.

2.Check the standby_file_management parameter and set the parameter to MANUAL.

SQL> alter system set standby_file_management=manual scope=both;
System altered.

3. Check the status of the Logfiles Before Dropping them.

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

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         3 CLEARING
         4 UNUSED
         5 CLEARING_CURRENT
         6 UNUSED

6 rows selected.

NOTE: If the status is CLEARING or CURRENT_CLEARING or ACTIVE sync the logfiles with the primary. If you proceed without sync then the following error occurs.

SQL> alter database drop logfile group 1;                             

           alter database drop logfile group 1
           ERROR at line 1:                       
           ORA-01156: recovery in progress may need access to files   
          
3a. To sync the logfiles issue the following command.,
   
SQL> alter database clear logfile group 1;
Database altered.

SQL> alter database clear logfile group 2;
Database altered.

SQL> alter database clear logfile group 3;
Database altered.

3b. Check the status of the logfiles.

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

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 UNUSED
         3 UNUSED
         4 UNUSED
         5 CLEARING_CURRENT
         6 UNUSED

6 rows selected.

3c. Drop the Logfiles accordingly.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

3d. Check the status logfiles.

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

    GROUP# STATUS
---------- ----------------
         4 UNUSED
         5 CLEARING_CURRENT
         6 UNUSED

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

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         4          1 UNUSED
         5          1 CLEARING_CURRENT
         6          1 UNUSED

4. Check the standby_file_management parameter and set the parameter to AUTO.

SQL> alter system set standby_file_management=auto scope=both;
System altered.

5. Activate the Recovery Process.

SQL> alter database recover managed standby database disconnect from session;
Database altered.