Data Guard : archivelog management strategy (Oracle 10g/11g)

This chapter provides clarifications about the standby archivelog management strategy in a Data Guard environment.

The paragraph has been extracted from our Knowledge Management database:

Global Title : Standby archivelog deletion policy, what's different between Oracle 10g and 11g

Symptom/Analysis:

The difference reside in the well known problematic of the archivelog deletion on standby databases.

Until Oracle 11g the RMAN deletion command "delete noprompt archivelog until time 'sysdate -1" did delete all archivelog whether they were applied on the standby or not!

This problem was addressed by a script which were looking at the V$ARCHIVE_LOG view for the last applied archivelog older than a specific number of days.

Now, with Oracle 11g the RMAN delete command behave has been improved ;). With the right archivelog deletion policy (CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;), you can safely remove standby database archivelogs ;)

For details please check Yann Neuhaus's SOUG article about Data Guard archivelog managament strategy available in the DMK_HA package.

As of our investigations if you use the advanced archivelog deletion policy with the "BACKED UP" clause, like "CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO 'DISK';"

The delete operation will probably never occur as the archivelogs aren't backed-up on the standby databases. This is especially dangerous as the policy applies on the database it has been enforced.

Solution:

dbi services recommend the following approach:

Oracle 10g :

Start the script as follows, it check if database is a STANDBY and run automatically the RMAN archivelog deletion script purge_standby_applied_archlog.ksh $ORACLE_SID <number of days to keep archivelog on disk>

Oracle 11g

1 # Set the right policy RMAN> show all;

...

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

...

2 # Launch DMK_DBBACKUP using a simple RMAN archivelog deletion script (Of course this should only start if the database is a STANDBY).

==> Crontab exemple

oracle@chbas0-spa071:/u01/app/oracle/local/dmk_dbbackup/rcv/oracle11/ [MVIPRD1] crontab -l |

grep MVIPRD1

00 2 * * * /u01/app/oracle/local/dmk_ha/bin/check_standby.ksh MVIPRD1 "dmk_rman.ksh -s MVIPRD1 -t del_arc_timed.rcv"

==> RMAN rcv script oracle@chbas0-spa071:/u01/app/oracle/local/dmk_dbbackup/rcv/oracle11/ [MVIPRD1] cat del_arc_timed.rcv

...

run

{

<ALLOCATE_CHANNELS>

delete noprompt archivelog until time 'sysdate -1';

<RELEASE_CHANNELS>

}