Delete Applied Archivelogs on Physical Standby Database

.


Edit (07,2013): I recommend using FRA for automatic deletion of archivelogs on a physical standby database. 
Let's see an example of configuring automatic maintenance of the archived logs on a standby database:
1. Enable the fast recovery area on the standby database by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=500G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/data/FRA'; 
If we're using ASM, we can specify a disk group as DB_RECOVERY_FILE_DEST.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA';
2. Set the LOG_ARCHIVE_DEST_1 parameter as follows so that the archived logfiles will be created at the DB_RECOVERY_FILE_DEST parameter:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_ FILE_DEST';
3. Set the RMAN archived log deletion policy as follows. With this setting, the applied archived logs will be automatically deleted when there is a space constraint in FRA, depending on DB_RECOVERY_FILE_DEST_SIZE. If the archived logs are not applied, they will not be deleted.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;



---------------------------------------

Original Post:
The shell script below, can be used to automate the deletion of applied archivelogs on unix standby servers. In this case archivelogs on the primary database are automatically deleted by rman after the backup operation. This shell script is being used in crontab of the standby machine to automate applied arhivelog deletion. It finds the last applied log using alertlog file, then deletes archivelogs smaller then the "last archivelog number - 10" in the archive directory.

#!/usr/bin/ksh
ARCH_DIR=/archive/orcl

#take the log number to be applied
LogNo=`tail -30000 /oracle/app/oracle/product/10.2.0/admin/orcl/bdump/alert* | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 5 | tail -1 `
echo "Oracle applied LogNo is $LogNo"

#extract 10
let SecLogNo=${LogNo}-10
echo "new backlog log No: $SecLogNo"

#delete small numbers from this in arch dir
cd $ARCH_DIR
for i in `ls *.arc`
do

Newi=`echo $i | cut -d "_" -f 4`

if [ $Newi -lt $SecLogNo ] ; then
echo "$i to be deleted..."
rm $i
fi
done

---------------------------------------
This script is special for my enviroment, it should be modified for any other environments. Archive log directory and name format effects the field numbers in the cut commands. If you need help for modifying the script for your env. please write me the "Media Recovery Log" lines in the alert log file.
The important think here is the idea.

(Thanks to my friend Selcuk Karaca who owns the idea and the script)

16 comments:

GÖKHAN ATIL said...

Hi Emre,

Thanks for sharing this script. I had disk space problem on one of our physical standby servers and wanted to code a script to remove the applied logs automatically. I googled and found your document.

Searching the applied log files in the alert log is a good idea. I have used that idea and created my own simple script. Here it is:


#!/bin/bash

ALERT=/oracle/app/oracle/diag/rdbms/dgbrm/dgbrm/trace/alert_dgbrm.log

tail -30000 $ALERT | grep "Media Recovery Log" | cut -d " " -f 4 | head --lines=-2 > files.txt

awk '{ system( "rm -rf " $_ ) }' files.txt


regards

Gokhan

Emre Baransel said...

Hi Gokhan, you're welcome
i'm glad this post helped you in some way.. my friend, Selcuk Karaca had the idea and also wrote this script, so thanks to him :)

Also, thank you for sharing your shorter altenative :)

Regards

Emre

Scofield said...

Hi Emre
Thanks for enlightening blog.
I tried your script but "LogNo" doesnt produce any output from my alert log.

My alert log is like:

Media Recovery Log /ora_archive/pcdg_0000001784_1.arc
Media Recovery Waiting for thread 1 seq# 1785
Media Recovery Log /ora_archive/pcdg_0000001785_1.arc
Media Recovery Waiting for thread 1 seq# 1786
Media Recovery Log /ora_archive/pcdg_0000001786_1.arc
Media Recovery Waiting for thread 1 seq# 1787


How should I modify the script?

Emre Baransel said...

Hi Scofield;
Try with the following lines;

(I'm sure you changed the alert_log and archive directories for your env.)

LogNo=`tail -100 /oracle/app/oracle/product/10.2.0/admin/usagedb/bdump/alert* | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 3 | tail -1 `

Just changed the the number 5 to 3. Because when cutting with delimiter _ you need the 3th part.

And also you should change the number 4 to 2 in the following line:

Newi=`echo $i | cut -d "_" -f 2`

Please try with these changes and let me know if it works or not. I'll also edit the post to specify that my script is special for my enviroment, it should be modified for any other environments. The important think here is the idea.

Thanks&Regards
Emre

Anonymous said...

This solution is not very good.
There is maximum number of archive logs, and if you delete them in shell, oracle doesn't know of this and still continue to number them until maximum number, then database stops :).

you must delete them using rman.

Anonymous said...

I suggest you to be sure before writing a comment like this and study some Oracle before dataguard.
Why I'm saying this? Because you wrote your post so surely that i was going to believe...:)

Oracle names-creates the archivelogs on the primary database then sends these archivelog files to standby database to apply. In the case that you backup your archivelogs on the primary side, the applied archivelogs on the standby side are some trash that you should get rid of. RMAN or no one need to know where they are. What you say is something that would never occur.

Fayyaz said...

I changed the script and used RMAN to delete archive logs. Here is my script
--------------------
#!/usr/bin/ksh
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
ARCH_DIR=/u01/oradata/flash_recovery_area/SIDE/localstby/; export ARCH_DIR
rm test.rman
#take the log number to be applied
LogNo=`tail -30000 $ORACLE_HOME/admin/side_dr/bdump/alert_side_dr.log | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d
"_" -f 5 | tail -1 `
echo "Oracle applied LogNo is $LogNo"

#extract 10
let SecLogNo=${LogNo}-10
echo "new backlog log No: $SecLogNo"

#delete small numbers from this in arch dir
cd $ARCH_DIR
for i in `ls *.arc`
do

Newi=`echo $i | cut -d "_" -f 3`
if [ $Newi -lt $SecLogNo ] ; then
echo "$i to be deleted..."
fi
done
echo "delete archivelog sequence between $SecLogNo and $Newi;" >> test.rman
$ORACLE_HOME/bin/rman target / @test.rman
~

---------------------

Anonymous said...

Hi I would suggest, query the database before you delete any files.

Here is my script...

# Functionality : Script to purge Archivelog from Physical standby database.
# Modified : Aug 27,2007
# Changes : Praveen Added functionality to purge archivelog based on last applied sequence on Standby DB.
# Oct 15,2007 " Added functionality to accept parameter for archivelog deletion.
# set -x
export ORACLE_SID=$1
CMDFILE1=/tmp/purge_standby_arc_${ORACLE_SID}.rman
timefile=/tmp/purge_standby_arc_${ORACLE_SID}.time.txt

lastapplied_seq_=/tmp/purge_standby_arc_${ORACLE_SID}.tmp

sqlplus -s /"as sysdba" << EOF > ${lastapplied_seq_}
set pagesize 0
set feedback off
select ' delete archivelog until sequence '||to_char( max(sequence#)-${2}) ||' thread '|| to_char( thread# ) ||';'
from v\$log_history group by thread#;
exit;
EOF


echo " " > $CMDFILE1
echo " run { " >> $CMDFILE1
echo " allocate channel c1 device type disk; " >> $CMDFILE1
cat ${lastapplied_seq_} >> $CMDFILE1
echo " " >> $CMDFILE1
echo "}" >> $CMDFILE1

rman target / nocatalog cmdfile ${CMDFILE1}

exit
++++++++++++

cronjob :-
10 * * * * /apps/orautil/app/oracle/admin/bin/PhysicalStdby/clean_arc.myStandbyDB0.run >/tmp/clean_arc.myStandbyDB.run.tmp 2>&1


Run file :
#!/bin/ksh
# Created : Praveen K Ponna,
# Functionality : Script to purge Archivelog from Physical standby database.
# Modified : Aug 27,2007
# Changes : Praveen Added functionality to purge archivelog based on last applied sequence on Standby DB.
#
# set -x

export ORACLE_BASE=/u01/app/oracle
export RUNTIME=`date '+%Y%m%d.%H%M%S'`
export HOSTNAME=`hostname|cut -d"." -f1`
export ORACLE_SID=gptprd0
export LOG=${ORACLE_BASE}/admin/${ORACLE_SID}/logbook/YYYYMMDD/${HOSTNAME}.${ORACLE_SID}.clean_arc.${RUNTIME}

echo \
> ${LOG}
env \
>> ${LOG}
echo \
>> ${LOG}
. ~/.bash_profile.1020 \
>> ${LOG} 2>&1
ORACLE_SID=gptprd0
echo \
>> ${LOG}
env \
>> ${LOG}
echo \
>> ${LOG}

echo \
>> ${LOG}
find ${ORACLE_BASE}/admin/${ORACLE_SID}/logbook/YYYYMMDD/ -name "*.clean_arc.*" -mtime +1 -exec rm {} \;

echo $? \
>> ${LOG}
echo \
>> ${LOG}

# /apps/orautil/app/oracle/admin/bin/PhysicalStdby/clean_archive_standby_v2.ksh ${ORACLE_SID} 9 >> ${LOG} 2>&1
/apps/orautil/app/oracle/admin/bin/PhysicalStdby/clean_archive_standby_v2.ksh ${ORACLE_SID} 1 >> ${LOG} 2>&1
exit_=$?
echo \
>> ${LOG}
echo ${exit_} \
>> ${LOG}

ls -al ${LOG} \
>> ${LOG}

echo ${LOG}
# Purge old log files.
find ${ORACLE_BASE}/admin/${ORACLE_SID}/logbook/YYYYMMDD/ -name "*clean_arc.*" -mtime +12 -exec rm -f {} \;

exit ${exit_}
~

Emre Baransel said...

Thanks for your all sharings; I'm sure these information will help people searching for a solution on this topic.

Syed said...

Hi Emre,

Thanks for sharing the script.

For any one interested in a batch script to delete the applied archive logs on primary and standby servers, the link is given below

http://syedzulfikar.blogspot.com/2010/08/remove-applied-archive-logs-on-primary.html

Thanks,
Syed

Anonymous said...

Hi Emre,

How do i delete the applied archive logs which are older than 3 days?.

Jerome said...

Marvelous work.Just wanted to drop a comment and say I am new to your blog and really like what I am reading.

sendil kumar said...

SEO Friendly Article Directory and Best SEO Friendly Directory Approval in a 48 hrs...Gaurented..100% Human Edited and approved quickly..

Michelle Anne Constantino said...

Hi Emre,
Thanks for this :) I'm a newbie in the field of Oracle database.

Anonymous said...

Cleaning old archivelog files from standby database.
del_date=`sqlplus -s sys/xxxx@dbname as sysdba << EOF
SET HEADING OFF
select max(FIRST_TIME)-3
from v\\$log_history;
EOF`

rman <>$OUT
connect target /

delete noprompt archivelog until time '$del_date';

crosscheck archivelog all ;

EOF

Anonymous said...

Dear Emre,

When does Oracle 11g decide that there's a `space constraint`?

We often get warnings from CloudControl that our FRA is 90% full, so we go and clean the logs manually. Should we let them approach 100% so Oracle would claim the space by itself?

Thank you for any answer you could provide.

Regards,

S.