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

Doing stuff with Oracle Database, Performance, Clusters, Linux. 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

Leave a New Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.



Jeremy Schneider

Get every new post delivered to your Inbox.

Join 931 other followers

%d bloggers like this: