Friday, February 24, 2012

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.

3 comments:

  1. hi ravikumar.
    i've same sort of issue.
    in my standby db , redo logs are generated automatically when I've void my standby db for testing purpose. I opened standby db,enabled archivelog and flashback.

    then after I came back to normal state.
    Now recovery is going on.but anyhow db is not opening as read only.

    Please suggest solution to open standby db as read only

    thanks
    bhavin dave

    ReplyDelete
  2. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2016/11/07/stp-loop-guard/

    ReplyDelete