Archivelog retention policy changes in RMAN 11g

UPDATE 221103: Oracle 11.2 is effectively out of support, this article is now archived and should not be referred to

In the past, that means at least until 10.2.x it used to be a bit clumsy to set the archivelog deletion policy to “applied on standby” on both primary and standby database. It involved setting an underscore parameter, as per Metalink Note 728053.1 “Configure RMAN to purge archivelogs after applied on standby”. For reference, the parameter was _log_deletion_policy, and you’d have to bounce the database for it to take effect.

SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile;

Only then could you avoid the error message.Now with 11g that’s a lot easier.

Consider the following example, where we set the deletion policy for the standby. Please note that this was entirely possible with 10.2 as well without causing any error (see above note). Connecting to the standby database first.

[oracle@rhel5 ~]$ rman target sys/secretpwd@stdby

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 5 19:40:15 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1225706675)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name STDBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_stdby.f'; # default

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO applied on standby;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

RMAN> exit

Recovery Manager complete.

Let’s try the same with the primary database.

[oracle@rhel5 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 5 19:42:09 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1225706675)

RMAN> configure archivelog deletion policy to applied on standby;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archived log deletion policy

Did you notice the warning? That was supposed to be possible. Now let’s have a closer look at what it says:

RMAN> host;

[oracle@rhel5 ~]$ oerr rman 8591
8591, 3, "WARNING: invalid archived log deletion policy"
// *Cause: An invalid ARCHIVELOG DELETION POLICY was supplied. The archived
//         log deletion policy was APPLIED but there was no mandatory
//         archived log destinations.
// *Action: One of the following:
//          1) Change archived log deletion policy using CONFIGURE command
//          2) Make one or more of standby destination as MANDATORY.

Aha! So maybe all we need is to make the standby database a mandatory target! Let’s try:

[oracle@rhel5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 5 19:43:05 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select value from v$parameter where name = 'log_archive_dest_2';
VALUE
------------------------------------------------------------------------------
service=stdby lgwr async valid _for=(all_logfiles,primary_role)

SQL> alter system set log_archive_dest_2=
 2  'service=stdby lgwr async valid_for=(online_logfiles,primary_role) mandatory';

System altered.

SQL> exit

So that made this destination mandatory.

Update 110823: Some readers have pointed out I should have made the local destination mandatory instead. This did not work (the below test has been performed on 11.2.0.2-my 11.2.0.1 system was scrapped in the meantime). In this test I left the standby destination at its default value (optional) and changed the local destination to be mandatory:

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST mandatory';

System altered.

SQL> select destination,binding from v$archive_dest
  2  where dest_id = 1;

DESTINATION                    BINDING
------------------------------ ---------
USE_DB_RECOVERY_FILE_DEST      MANDATORY

Now back in RMAN I tried to set the deletion policy:

[oracle@rac11gr2drnode1 ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 23 21:04:51 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1279071946)

RMAN> configure archivelog deletion policy to applied on standby;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN>

As you can see there is no point using a mandatory local destination for this archivelog deletion policy. Back to what I was going to explain anyway, I changed the standby destination to “mandatory” and the local destination to “optional” to complete the test.

Be warned though: changing a remote destination from optional to mandatory means the primary shuts down in case it can’t talk to it. Maybe having a different approach to archivelog clearing might be more worth your while.

Just to complete the example-with the standby destination set to mandatory, I can change the retention policy successfully:

RMAN> configure archivelog deletion policy to applied on standby;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

RMAN>

The last message said it all – new configuration successfully stored, and I promise I haven’t touched an underscore parameter. However, be warned again that the primary now has a very strong dependency on the network connection to your standby-this is a risk for a production outage.