>
Technical

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.

About these ads

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider

Discussion

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;
    }
    exit

    Cheers

    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

Disclaimer

(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.

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 926 other followers

%d bloggers like this: