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 Responses so far.

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

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

  3. Scofield says:

    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?

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

  5. Anonymous says:

    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.

  6. Anonymous says:

    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.

  7. Fayyaz says:

    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
    ~

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

  8. Anonymous says:

    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_}
    ~

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

  10. Syed says:

    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

  11. Anonymous says:

    Hi Emre,

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

  12. Jerome says:

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

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

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

  15. Anonymous says:

    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

  16. Anonymous says:

    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.

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -