Archive log Location space gets critically filled in DR Database | Oracle Database

The following opinions expressed in this post are my own and in no way connected to my employer. 

The below steps could be followed if you have an DR instance on which the Archive space gets filled faster than the deletion script you scheduled and in times when you need to clear space in urgency given you have no policies to keep the archive logs at DR site once they are applied.

Once you receive an alert,

check the last applied sequence on DR site and latest sequence on Primary DB using below query:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

This could give an output similar to:

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                  65598                 65598          0

         2                  64388                 64388          0

         3                  60908                 60908          0

Note the Last Sequence Applied on DR site for individual thread and the Difference.

Based on your policy or comfort ground decide how many sequence log you would like to leave on disk before deleting rest. For an example if you decide to delete all logs keeping recent 20 logs for each thread.

The above would mean that you are willing to delete up-to below mark:

Thread     Last Sequence Applied

-------------------------------------

1            65598-20 =65578

2            64388-20 =64368

3            60908-20 =60888

Once decided on the log deletion, proceed as below.

Log on to RMAN of DR site and execute the below :

RMAN TARGET /

CROSSCHECK ARCHIVELOG ALL;

DELETE ARCHIVELOG UNTIL SEQUENCE 65578 THREAD 1;

DELETE ARCHIVELOG UNTIL SEQUENCE 64368 THREAD 2;

DELETE ARCHIVELOG UNTIL SEQUENCE 60888 THREAD 3;

It would ask for confirmation for deletion after listing logs to delete, check and proceed.

Once deleted check for  the archive log space.