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.
http://joordsblog.vandenoord.eu/2011/05/rman-configuration-in-data-guard.html?m=1
Perfect solution for archive deletion with rman settings
LikeLike
We use RMAN:
configure archivelog deletion policy to applied on standby;
run {
delete noprompt archivelog all;
}
exit
Cheers
Paul Houghton
LikeLike
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.
LikeLike
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.
LikeLike