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 /

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.

About Jeremy

Building and running reliable data platforms that scale and perform. about.me/jeremy_schneider


4 thoughts on “Delete Archived Logs from Standby

  1. We use RMAN:

    configure archivelog deletion policy to applied on standby;
    run {
    delete noprompt archivelog all;


    Paul Houghton


    Posted by huffton | November 19, 2013, 5:13 am
  2. Here is my script with RMAN to do the same thing:

    connect target /
    run { delete noprompt archivelog all completed before ‘sysdate-7’; }

    I keep the archived redo logs for 7 days, but there are other options as well.


    Posted by Brian Peasland | November 19, 2013, 8:37 am
  3. I remembered there was a bug with – configure archivelog deletion policy to applied on standby and should use configure archivelog deletion policy to applied on all standby

    Another issue is that most do not use persistent configuration.


    Posted by dinh | November 19, 2013, 9:13 pm


This is my personal website. The views expressed here are mine alone and may not reflect the views of my employer.

contact: 312-725-9249 or schneider @ ardentperf.com




Enter your email address to receive notifications of new posts by email.

Join 56 other subscribers
%d bloggers like this: