Drop SRL

How to drop the standby redo logs

0. Enviroment
1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’
2. Drop SRL On Standby
3. SET STANDBY_FILE_MANAGEMENT=’AUTO’
4. Enable MRP


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: APAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: EMEA
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’

On Standby

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO
         4         STANDBY /u01/app/oracle/oradata/emea/redo04.log                      NO
         5         STANDBY /u01/app/oracle/oradata/emea/redo05.log                      NO
         6         STANDBY /u01/app/oracle/oradata/emea/redo06.log                      NO
         7         STANDBY /u01/app/oracle/oradata/emea/redo07.log                      NO

7 rows selected.

SQL>

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY            PHYSICAL STANDBY

SQL> 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

System altered.

SQL>


2. Drop SRL On Standby

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/emea/redo04.log'

SQL> 

Note: If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as below

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO

SQL> select * from v$standby_log;

no rows selected

SQL>


3. SET STANDBY_FILE_MANAGEMENT=’AUTO’

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

System altered.

SQL>


4. Enable MRP

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.