APPS DBA
How To Add Redo Logfiles In Standby Database
Step 1. First, Check MEMBER present in Standby Database.
SQL> select member from v$logfile;
MEMBER
———————————-
E:\STANDBYLOGS\REDO03.LOG
E:\STANDBYLOGS\REDO02.LOG
E:\STANDBYLOGS\REDO01.LOG
Step 2. Cancel the recovery Process.
SQL> alter database recover managed standby database cancel;
Step 3. Check the standby_file_management parameter and set the parameter to MANUAL.
SQL> show parameter standby_file_management
NAME TYPE VALUE
———————————— ———– ——
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual scope=both;
SQL> show parameter standby_file_standby_file_management;
NAME TYPE VALUE
———————————— ———– ——
standby_file_management string MANUAL
Step 4. Add Redo Logfiles in Standby Database.
SQL> alter database add logfile group 4 ‘E:\STANDBYLOGS\REDO04.LOG’ size 250M;
SQL> alter database add logfile group 5 ‘E:\STANDBYLOGS\REDO05.LOG’ size 250M;
SQL> alter database add logfile group 6 ‘E:\STANDBYLOGS\REDO06.LOG’ size 250M;
SQL> alter database add standby logfile group 7 ‘E:\STANDBYLOGS\STDREDO07.LOG’ size 250M;
SQL> alter database add standby logfile group 8 ‘E:\STANDBYLOGS\STDREDO08.LOG’ size 250M;
SQL> alter database add standby logfile group 9 ‘E:\STANDBYLOGS\STdREDO09.LOG’ size 250M;
SQL> alter database add standby logfile group 10 ‘E:\STANDBYLOGS\STdREDO10.LOG’ size 250M;
Step 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
SQL> select member from v$logfile;
MEMBER
————————
E:\STANDBYLOGS\REDO03.LOG
E:\STANDBYLOGS\REDO02.LOG
E:\STANDBYLOGS\REDO01.LOG
E:\STANDBYLOGS\REDO04.LOG
E:\STANDBYLOGS\REDO05.LOG
E:\STANDBYLOGS\REDO06.LOG
E:\STANDBYLOGS\STDREDO07.LOG
E:\STANDBYLOGS\STDREDO08.LOG
E:\STANDBYLOGS\STDREDO09.LOG
E:\STANDBYLOGS\STDREDO10.LOG
10 rows selected.
Step 6. Check the standby_file_management parameter and set the parameter to AUTO.
SQL> alter system set standby_file_management=auto scope=both;
Step 7. Finally, Activate the Recovery Process.
SQL> alter database recover managed standby database disconnect from session;
How To Drop Redo Logfiles on Physical Standby Database
Step 1. First, Cancel the Standby Recovery Process.
SQL> alter database recover managed standby database cancel;
Step 2. Check the standby_file_management parameter and set the parameter to MANUAL.
SQL> alter system set standby_file_management=manual scope=both;
Step 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;
SQL> alter database clear logfile group 2;
SQL> alter database clear logfile group 3;
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;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
3D. Check the status of 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
Step 4. Check the standby_file_management parameter and set the parameter to AUTO.
SQL> alter system set standby_file_management=auto scope=both;
Step 5. Activate the Recovery Process.
SQL> alter database recover managed standby database disconnect from session;
ERROR
Error stack:
AutoConfig completed with errors.
The log file for this session is located at: /u01/apps/inst/apps/TEST_apps/admin/log/03451250/adconfig.log
(more…)