Change RMAN Retention Policy of a Standby Database !!!

Home »  ORACLE »  Change RMAN Retention Policy of a Standby Database !!!

On November 9, 2015, Posted by , In ORACLE, By , , With 2 Comments

Once a standby database is created, it is not possible to change the backup retention policy on the fly. In this article, we will see how to change RMAN retention policy of a standby database.

======================================================================================================

 

There may be situation where you might need to change RMAN retention policy on standby database different from that of the primary database due to some constraint on standby site. Trying to do so using the configure command would give the following error,

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 07/01/2011 11:25:47
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

You can follow below steps to change the RMAN policy of standby database.

======================================================================================================

 

– On standby DB

• Cancel recovery on standby, get the data files list and shutdown the standby database

SQL> alter database recover managed standby database cancel;

SQL> select name from v$datafile;

SQL> shutdown immediate;

 

– On primary DB

• Change the retention policy to what is required in standby

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;

 

• Create new standby control file in primary server and copy it to the standby server

SQL> alter database create standby controlfile as ‘/home/oracle/standby.ctl’;

$ scp /home/oracle/standby.ctl oracle@standby_server:path

 

– On standby DB

• Rename/Backup existing control files on standby

$ mv o1_mf_5q7xfq1o_.ctl o1_mf_5q7xfq1o_.ctl.bak
$ mv o1_mf_5q7xfq2t_.ctl o1_mf_5q7xfq2t_.ctl.bak

 

• Replace existing control files on standby with the new control files

$ cp /home/oracle/standby.ctl /d03/orcldb/o1_mf_5q7xfq1o_.ctl
$ cp /home/oracle/standby.ctl /d04/orcldb/o1_mf_5q7xfq2t_.ctl

 

• Mount the standby database

$ sqlplus / as sysdba

SQL> startup mount

 

• Update control file with new directory structure on standby

If the source and target directory locations are different and db_file_name_convert/log_file_name_convert is not set, then rename the file location(s) in the new standby control file.

 

• Stop and start flashback if it was enabled

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

 

• Clear log files

If standby log files were not created on primary create them on standby now, if standby log files exist on standby site, clear standby log as well online logs as well.

SQL> select group# from v$log;
SQL> alter database clear logfile group 1;
SQL> alter database clear logfile group 2;

– Do the same for standby log files as well.

SQL> select group# from v$standby_log;

 

• Start recovery on standby

SQL> alter database recover managed standby database disconnect;

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED
———- ———
1820 IN-MEMORY

– Check whether logs are getting applied or not on standby, do a log archive on primary

SQL> alter system archive log current;

and check on standby

SQL> select sequence#,applied from v$archived_log;

 

• Verify the backup retention

Finally verify the backup retention on standby has changed to desired retention policy

$ rman target /
RMAN> show all;

That’s it, this completes our final step.

======================================================================================================

Tags : , ,

2 Comments so far:

  1. Igor G. says:

    You can also do these changes by switching over to Standby first and then on the Standby that became Primary do whatever you like.

    • TJ says:

      Hi,

      Yes, you can but in most of the cases you might never do switchover and switchback activity. In such scenarios, you can perform above steps.

      Thanks,
      TJ

Leave a Reply

Your email address will not be published. Required fields are marked *