Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Loading...

Thursday, December 29, 2011

Delete archivelogs from Standby

How to delete archivelogs from standby:

There different options in oracle to delete the archivelogs automatically from Primary and standby.

Option-1)
Needs restart of the primary & secondary databases if you are on 10g.

SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile; (add sid='*' if you are on RAC

Refer note
Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

option-2)
set the below in RMAN>
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

The above will only work if you are taking the backup of the database (primary or secondary.)

In my case we are not taking backup of the secondary so I cannot use option-2 and we are in 10g and don't want to restart the primary database and don't have a test system with standby database to test option-1

so decided to develop custom script to delete the archivelogs from the standby.
Remember since my primary database is getting backed up I am using option-2 in primary.

option-3)
Here I am pulling the max sequence numbers of the archivelog for each thread( since I have 3 -node RAC) and doing minus 10 so that I will have at least 10 archivelogs in my disk at any given time for any thread and then passing these sequence numbers to RMAN to delete the archivelogs.

Below is the script.

#!/bin/ksh
export ORACLE_HOME=/fin02/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=STANDBYDBY1
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /fin02/u0001/scripts/logs/1.tmp
$ORACLE_HOME/bin/sqlplus -s / as sysdba<set heading off
set pages 0 echo off feedback off
spool /fin02/u0001/scripts/logs/1.tmp
select thread#,max(sequence#)-10 from v\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;
spool off;
exit;
EOF

seq1=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==1{print $2}'`
seq2=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==2{print $2}'`
seq3=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3
$ORACLE_HOME/bin/rman msglog /fin02/u0001/scripts/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
connect target /
run{
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



Cheers:
Sameer

4 comments:

Don Seiler said...

Nice post, thanks. Saved me a lot of time backing up from a RAC standby.

ㅤㅤjampani said...

Hi Sameer,
From ,

Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

Can i draw that "_log_deletion_policy" is only a 10g setting and not applicable to 11g , i got confused with the vocabulary in the met.Note.Id.

Thanks,
Uday

ㅤㅤjampani said...
This comment has been removed by the author.
Vinay Sridhar said...

Very good script. Thanks Sameer.