19c: Delete archivelogs applied on Standby

 Hi Everyone, 

Here is a small handy script to delete archivelogs that are applied on Standby Database. This has to be 

installed on standby database.  You can copy and  paste the script with the modification of red colored 

variables Oracle Home and  Oracle Instance SID.  

[oracle@ap155rt~]$ cat /home/oracle/delete_applied_archivelogs.sh

#!/bin/sh

# Usage: This purges archivelogs on standby DB after its applied 

# Avoids space fillings on Standby DBs

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_2

export ORACLE_SID=payprod1

export PATH=$ORACLE_HOME/bin:$PATH

TABLE='v$archived_log'

THREAD1=`sqlplus -s /nolog <<EOF

conn /  as sysdba

set echo off feed off head off pause off pages 0 lines 180 trimout on

select max(sequence#)-10 from ${TABLE} where applied ='YES' and REGISTRAR='RFS' and thread#=1;

exit

EOF`

DELETED1=`sqlplus -s /nolog <<EOF

conn /  as sysdba

set echo off feed off head off pause off pages 0 lines 180 trimout on

select max(sequence#) from ${TABLE} where applied ='YES' and REGISTRAR='RFS' and thread#=1 and deleted='YES';

exit

EOF`

if [ -z $DELETED1 ]

then

DELETED1=1

fi

if [ -z $THREAD1 ]

then

echo

else

rman target / <<EOF1

crosscheck archivelog all;

delete noprompt archivelog from sequence $DELETED1 until sequence $THREAD1 thread 1;

exit

EOF1

fi

[oracle@ap6155rt~]$ 

Standby DB's RMAN configuration needs to be modified to below to have the purge work. If NOT

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

will be thrown during executions with (RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default)

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Hope this helps someone.