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;