Drop/Rename Standby Redolog Files In Oracle

While performing the dataguard Broker, we need to drop the standby database while switchover the standby . As it seems an easy task but it is bit tricky . Below are the steps to drop the redolog file from standby database :

Dropping Standby Redolog Files:

On Standby Database : 

1. Check Redolog Files Status

SQL> select member,type from v$logfile;

GROUP#         MEMBER TYPE                                        
------         --------------------------------- --------
 1             /SAP/Oracle/OriglogA/redo_01a.log ONLINE   
 1             /SAP/Oracle/MirrlogA/redo_01b.log ONLINE     
 2             /SAP/Oracle/OriglogB/redo_02a.log STANDBY     
 2             /SAP/Oracle/MirrlogB/redo_02b.log STANDBY      3             /SAP/Oracle/OriglogC/redo_03a.log STANDBY      3             /SAP/Oracle/MirrlogC/redo_03b.log STANDBY     

2. We have to drop the standby redolog files .

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

NOTE: Now to solve this issue we have cancel the managed recovery session and set  "standby_file_management"  to manual and drop the standby redolog file  as 

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

SQL> alter system set standby_file_management='MANUAL' ;
System altered.

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

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

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

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

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

Once the standby redologs are dropped then again back to recover the standby.


SQL>alter system set standby_file_management='AUTO' ;
System altered.

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

Renaming Standby Redolog Files:

On Standby Database : 
1. Check Redolog Files Status

SQL> select member,type from v$logfile;

GROUP#         MEMBER TYPE                                        
------         --------------------------------- --------
 1             /SAP/Oracle/OriglogA/redo_01a.log ONLINE     
 1             /SAP/Oracle/MirrlogA/redo_01b.log ONLINE     
 2             /SAP/Oracle/OriglogB/redo_02a.log STANDBY     
 2             /SAP/Oracle/MirrlogB/redo_02b.log STANDBY      3             /SAP/Oracle/OriglogC/redo_03a.log STANDBY      3             /SAP/Oracle/MirrlogC/redo_03b.log STANDBY     

2. We have to drop the standby redolog files .

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

NOTE: Now to solve this issue we have cancel the managed recovery session and set  "standby_file_management"  to manual and drop the standby redolog file  as 

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

SQL> alter system set standby_file_management='MANUAL' ;
System altered.

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

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

SQL> alter database add standby logfile group 1 ('/SAP/ORADATA/OriglogA/redo_01a.log','/SAP/ORADATA/MirrlogA/redo_01b.log') size 500M reuse;

Database altered.

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

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

SQL> alter database add standby logfile group 2 ('/SAP/ORADATA/OriglogB/redo_02a.log','/SAP/ORADATA/MirrlogB/redo_02b.log') size 500M reuse;

Database altered.

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

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

SQL> alter database add standby logfile group 3 ('/SAP/ORADATA/OriglogC/redo_03a.log','/SAP/ORADATA/MirrlogC/redo_03b.log') size 500M reuse;

Database altered.

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



Once the standby redologs are dropped then again back to recover the standby.


SQL>alter system set standby_file_management='AUTO' ;
System altered.

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

Finally check the status 

SQL> select member,type from v$logfile;

GROUP#         MEMBER TYPE                                        
------         --------------------------------- --------
 1             /SAP/ORADATA/OriglogA/redo_01a.log ONLINE   
 1             /SAP/ORADATA/MirrlogA/redo_01b.log ONLINE     
 2             /SAP/ORADATA/OriglogB/redo_02a.log STANDBY     
 2             /SAP/ORADATA/MirrlogB/redo_02b.log STANDBY      3             /SAP/ORADATA/OriglogC/redo_03a.log STANDBY      3             /SAP/ORADATA/MirrlogC/redo_03b.log STANDBY 

######################################################  All The Best ######################################################