Delete Archived Logs from Standby

This is a little surprising to me because it’s so simple – but I couldn’t find a script anywhere on oracle support or on the internet which elegantly (IMHO) cleaned up archived logs on a standby system.  (Specifically, a RAC/thread aware script.)

There are a few scripts published:

• Emre Baransel [2009, single-thread only, uses alert log instead of data dictionary, more scripts in the comments]
• Alex Lima [2011, single-thread only]
• Sameer Shaik [2011, hard-coded three redo threads]

I took these scripts and slightly improved on them. Here’s the result: a little seven-line snippet to cleanup archivelogs from a standby database in a slightly safer and more dynamic fashion.

PRE="set pagesize 0 \n set feedback off \n"; SS="$ORACLE_HOME/bin/sqlplus -L -S / as sysdba"
ROLE=$(echo -e "$PRE select database_role from v\$database;" | $SS)
[[ "$ROLE" != "PHYSICAL STANDBY" ]] && { echo "ERROR: database not a physical standby"; exit 1; }
THREADS=$(echo -e "$PRE select distinct thread# from v\$archived_log;" | $SS)
for THREAD in $THREADS; do
  MAX_APPLIED=$(echo -e "$PRE select max(sequence#) from v\$archived_log where applied='YES' and thread#=$THREAD;" | $SS)
  echo "delete noprompt archivelog until sequence $MAX_APPLIED thread $THREAD;"|rman target /
done

One important note about this snippit – I assume that you’re running backups from your primary. This script is not safe to run on a system where you are taking backups. But RMAN should take care of cleaning up the archivelogs itself if you’re running backups, so this script shouldn’t be necessary in that situation.

Hope you find it useful! Also please leave a link in a comment if there was a script already published somewhere that I just missed.