How to Add/Drop/Rename Standby Redolog file

 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 ;

 Adding a standby/Redo log file to standby database:

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;

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.