MAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;If possible the easiest way to change the retention policy is to do a switchover (making the standby the primary) and change the retention policy and switchover back to original primary. But this may not be possible at all times, especially if the data guard configuration is asymmetric and standby is used more for data protection than for disaster recovery.
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
What is required here is to change the retention policy in primary to that is desired on the standby, created a standby controlfile , revert the retention policy on primary, restore the newly created standby controlfile on standby DB which will have the modified retention policy.
There are two metalink notes that can help with this.
Steps to recreate a Physical Standby Controlfile [ID 459411.1]
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files [ID 734862.1]
This blog will use steps listed in both these notes first on a single instance data guard configuration using file system for datafile storage (OMF) and another blog will follow with RAC data guard configuration using ASM.
On standby database
1. Requirement is to change the retention policy to recovery window of 2 days which cannot be done with the configure command and switchover to standby is not possible at this time
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;2. Cancel log apply on standby and get the datafile name list and shutdown the standby database
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
SQL>alter database recover managed standby database cancel;On primary DB
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf
SQL>shutdown immediate;
3. Change the retention policy to what is required in standby
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 days;4. Create the new standby controlfile in primary db server and copy it to the standby db server
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';5. Backup the current controlfile on standby using OS utility. (only valid if the database is shutdown clearnly).
Database altered.
mv o1_mf_5q7xfq1o_.ctl o1_mf_5q7xfq1o_.ctl.bakReplace the current controlfiles on standby with the new standby controlfiles
mv o1_mf_5q7xfq2t_.ctl o1_mf_5q7xfq2t_.ctl.bak
cp /home/oracle/standby.ctl o1_mf_5q7xfq1o_.ctl6.Mount the standby database
cp /home/oracle/standby.ctl o1_mf_5q7xfq2t_.ctl
7. Metalink note [ID 459411.1] says If the File-Structure is different between Primary and Standby Database and db_file_name_convert/log_file_name_convert is not set, rename the File-Location(s) in the new Standby Controlfile:
In this case above mention initialization parameters were set but since the standby was using OMF the datafile names are different from the primary hence the reason to query them in step 1. This could be observed by runnign following query on standby
SQL> select name from v$datafile;These are the datafile names on primary and would not be same as standby datafile names.
NAME
----------------------------------------------------------------------
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_5q7x746p_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_5q7x746t_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_5q7x747g_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_5q7xg0kk_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_5zb2lltr_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_5zb2lzvc_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_5zb2mf60_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70p7blqh_.dbf
8. Rename the datafile names to their original names
SQL> alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf';To overcome this problem change the standby file management to manual for the duration of the rename
alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is
automatic.
SQL> alter system set standby_file_management='MANUAL' scope=memory;Rename all the files like this and change the standby file management to auto
System altered.
SQL> alter database rename file '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_5q7x745y_.dbf' to '/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf';
Database altered.
SQL> alter system set standby_file_management='AUTO' SCOPE=MEMORY;9. Stop and start flashback if it was enabled
System altered.
SQL>ALTER DATABASE FLASHBACK OFF;10. If standby log files were not created on primary create them on standby now, if standby log exists on standby clear standby log as well online logs
SQL>ALTER DATABASE FLASHBACK ON;
SQL> select group# from v$log;Do the same for other logs as well.
GROUP#
----------
1
2
6
4
5
3
6 rows selected.
SQL> alter database clear logfile group 1;
Database altered.
SQL> select group# from v$standby_log;Do the same for other logs as well.
GROUP#
----------
7
8
9
10
11
SQL> alter database clear logfile group 7;
Database altered.
11. Start log apply on standby and verify log apply is working
SQL> alter database recover managed standby database using current logfile disconnect;Do a log archive on primary
Database altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
1820 IN-MEMORY
SQL> alter system archive log current;and check on standby
SQL> select sequence#,applied from v$archived_log;12. Finally verify the backup retention on standby has changed to desired retention policy
SEQUENCE# APPLIED
---------- ---------
1820 YES
1821 IN-MEMORY
RMAN> show all;If database uses OMF then when log apply is started new online logfiles and standby logfiles would have been created. To save space identify the currently used logfiles with
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ENT11G1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
select group#,member from v$logfile;and remove the logfiles that doesn't appear on the output from the disk. (This is the step 3 B on [ID 734862.1]
Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths. )
