How to Drop/Rename Standby Redolog file in Oracle 11g

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 :

On Standby Database : 
SQL> select member,type from v$logfile;
MEMBER                                                                     TYPE                      
----------------------------------                                         -----------
D:\APP\STANDBY\ORADATA\REDO03.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO02.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO01.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO04.LOG      STANDBY    
D:\APP\STANDBY\ORADATA\REDO05.LOG      STANDBY

Here,we have to drop the two standby redolog file .

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

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 4;
Database altered.

SQL>alter database drop standby logfile group 5;
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;

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.

More detail click here


Enjoy    :-)