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)

51 Responses so far.

  1. Unknown says:

    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. Mr. Z 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. Unknown says:

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

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

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

  16. Sravani says:

    Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training

  17. sushma says:

    Hey Really Thanks for sharing the best information regarding category, hope you will write more great blogs.
    Oracle Fusion Financials Online Training

  18. Naga says:

    Very interesting blog, keep posting such an informative post.

    Oracle Fusion Financials Online Training

  19. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    oracle fusion financials online training

  20. Awesome post. your article is really informative and helpful for me and other bloggers too

    Oracle Fusion SCM Online Training

  21. shivani says:

    Thanks for sharing a useful information.. we have learnt so much information from your blog..... keep sharing
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  22. shivani says:

    Astonishing web diary I visit this blog it's incredibly magnificent. Strangely, in this blog content made doubtlessly and sensible. The substance of information is instructive.
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    oracle Fusion Technical online training

  23. shivani says:

    Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  24. Anonymous says:

    An amazing web journal I visit this blog, it's unbelievably wonderful. Oddly, in this blog's content made without a doubt and reasonable. The substance of data is informative.
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  25. Norton.com/Setup is best antivirus available in the market. If you want to protect your system online or locally from any unforeseen events Norton is is a must have software in your PC or Mac. Activate your Norton.com/Setup to protect yourself ad your data from your system from malware and antivirus. Browse internet without any hesitation norton will take care of all malicious antiviruses floating all over internet.

    For any concern and help just visit website for Office.com/Setup help and key activation of Office.com/Setup You can do it by yourself if you know how to install office.com/Setup on your PC or Mac or you can call third party companies as well who can do it on your behalf.

  26. McAfee.com/Activate Since the world is developing each day with new computerized advances, digital dangers, malware, information, and harming diseases have additionally turned out to be increasingly more progressed with every day. These digital contaminations harm a gadget or documents in different ways.McAfee.com/Activate

  27. shivani says:

    An overwhelming web journal I visit this blog, it's unfathomably amazing. Unusually, in this present blog's substance made inspiration driving truth and reasonable. The substance of data is enlightening
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  28. shivani says:

    A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  29. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article
    John Clarke
    Digi me

  30. I really enjoyed reading your blog, you have lots of great content. Please visit here:
    McAfee.com/Activate
    Bruce Wayne

  31. Such a nice blog, I really like what you write in this blog, I also have some relevant information about if you want more information.

    Oracle Fusion HCM Online Training

  32. Nice post……. your article is really informative and helpful for me and other bloggers too

    Big Data and Hadoop Online Training
    Big Data Hadoop Training
    Hyderabad

  33. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Spark and Scala Online Training
    Spark Scala Training
    Hyderabad

  34. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    oracle fusion functional training
    oracle integration cloud service online training

  35. Norton 360 Deluxe – for up to 5 gadgets with Auto Renewal – gives you ground-breaking layers of insurance for your associated gadgets and online protection. norton.com/setup Your own gadgets and data have assurance as you bank, shop and post on the web, both at home and on open Wi-Fi.

  36. i found this article to be very informative norton.com/setup and amazing.

  37. I really happy found this website eventually. Really informative and inoperative, Thanks for the post and effort! Please keep sharing more such blog.

    norton.com/setup

    Charter.net

    www.norton.com/setup

    roadrunner email login

  38. We are working as public beneficiaries as we share analyzed and evaluated solutions with people who are looking for a good solution on McAfee login. If you are also looking for such assistance. You can either go to our website or contact us via call.
    visit here: https://mcafeepro.com/

  39. Xperria says:

    Mcafee is an antivirus that provides protection to your device from various online threats. McAfee account login will help you to use various features. You first have to start with the Mcafee login process for getting access to all the information regarding your Mcafee account. The login is simple as well as an easy process.
    https://mcafeepro.com/

  40. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man,Keep it up.
    office.com/setup  || office.com/setup  || office.com/setup 

  41. Really very happy to say,your post is very interesting to read.I never stop myself to say something about it.You’re doing a great job.Keep it up
    mcafee.com/activate | mcafee.com/activate | mcafee.com/activate | mcafee.com/activate

  42. Mcafee is an antivirus that provides protection to your device from various online threats. McAfee Account Login will help you to use various features. You first have to start with the McAfee Login process for getting access to all the information regarding your Mcafee account. The login is simple as well as an easy process.

  43. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man,Keep it up.
    mcafee.com/activate | mcafee.com/activate | mcafee.com/activate

  44. Utilizing the provided controller, press the HOME or MENU button.
    Choose Video, Application, My Apps or Apps, contingent upon your model.
    Choose the Prime Video application.
    Choose Sign In and Start Watching and note the enlistment code that shows up on the system.
    Utilizing the web, go to the Amazon™ sign-in page

    amazon.com/mytv
    www.amazon.com/mytv enter code sign in
    www.amazon.com/mytv
    www.primevideo.com/mytv
    amazon prime login
    www.primevideo.com/mytv register code

  45. Activate amazon mytv login activate by giving the activation link activate primevideo mytv in your tv and enjoy amazon popular shows such as movies & web series.Visit given link primevideo.com mytv..

  46. Activate com amazon mytv activate by giving the activation link primevideo.com/mytv in your tv and enjoy amazon popular shows such as movies & web series.Visit given link primevideo.com/mytv..

  47. Prime Video is a real time feature. Fundamentally it is just for prime clients. The assistance offers a great many Prime Videos at no charge and Prime Members at no charge. In Amazon Prime Video, clients can undoubtedly lease and purchase films and TV shows. Primevideo.com/mytv - Amazon Prime is a help from Amazon that clients need to pay a month to month add up to utilize. Consequently, he can utilize the Amazon Prime help and exploit the entirety of Prime's highlights.
    Read more…

  48. Thanks for sharing this Great post, it is very helpful. If you are facing any difficulties using amazon prime simply visit www.amazon.com/mytv or contact us. Their expert’s team resolve your issues instantly and provide a solution as soon as possible.
    Read more…

Powered by Blogger.

Page Views

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