Pages

Wednesday, December 19, 2012

Delete archivelogs on standby [ tested ]


RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

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

-r-x------ 1 oracle oinstall  654 Dec 19 06:30 delarch.sh
-r-------- 1 oracle oinstall  240 Dec 19 05:49 Maxseq.sql

[oracle@orastby ~]$ cat delarch.sh
----------------------------------------------------------------
#!/bin/bash

export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
export ORACLE_SID=FIN2
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
 rm -f /home/oracle/log/MAXSEQ.tmp
 $ORACLE_HOME/bin/sqlplus -S /nolog < /home/oracle/Maxseq.sql 
 seq1=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==1{print $2}'`
#seq2=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==2{print $2}'`
#seq3=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==3{print $2}'`
 echo $seq1 
$ORACLE_HOME/bin/rman msglog /home/oracle/log/STANDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
 connect target /
 run{
 crosscheck archivelog all;
 delete noprompt archivelog until sequence $seq1 thread 1;
#delete noprompt archivelog until sequence $seq2 thread 2;
#delete noprompt archivelog until sequence $seq3 thread 3;
 }
 EOF
find /home/oracle/log/ -name "STANDBY_ARC_DEL.*" -mtime +7  -exec rm -rf {}  \;
----------------------------------------------------------------

[oracle@orastby ~]$ cat Maxseq.sql 
----------------------------------------------------------------
connect / as sysdba 
set heading off pages 0 echo off feedback off; 
spool /home/oracle/log/MAXSEQ.tmp; 
select thread#, max(sequence#)-10 from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ; 
spool off; 
exit; 
----------------------------------------------------------------
Note: For RAC please uncomment the lines for thread 2,3,..

2 comments:

  1. thank you very much for your archive deletion script on standby i have succusfully implemented in 2 node rac env and it is going good and i got appreciation as well

    ReplyDelete
    Replies
    1. Glad that you got appreciation.

      I appreciate your effort to comment. :)

      Delete