Martins Blog

Trying to explain complex things in simple terms

Archivelog retention policy changes in RMAN 11g

Posted by Martin Bach on October 8, 2009

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.

About these ads

7 Responses to “Archivelog retention policy changes in RMAN 11g”

  1. rman_oracle said

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

    above statement is not good for production environment. If there is network issue, production will stall.

    you could have changed log_archive_dest_1 as mandatory??

    • Martin said

      Well the oerr output states that a standby destination needs to be mandatory so I don’t think you can set the local destination to mandatory. I’ll run a testcase when I have time to verify.

    • Martin Bach said

      Hi there,

      I finally got back to the test and tried your suggestion-the update is in the blog post. The warning about the production outage still applies though, and the reader should carefully weigh up advantages and disadvantages of using the RMAN retention policy.

  2. Very odd, exactly the same entry, posted on the same date at http://gjilevski.wordpress.com/2009/10/08/archivelog-retention-policy-in-rman-11g-r2/

    There does not seem to be any obvious link betwen the 2 sites

  3. Krish said

    Checkout metalink note 1082886.1

    The Workaround to rman-8591 is to use

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    Works and tested on 11.2.0.2 (PSU3)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,368 other followers

%d bloggers like this: