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.
