Changing Backup Policy on Standby DB - 2

The previous post is a mixture of steps on metalink notes [ID 734862.1] and [ID 459411.1]. This blog is based on [ID 734862.1] and the same data guard configuration used in the previous post is used here as well. Again the requirment is to change the backup retention policy.

On Primary

1. Change the backup retention policy on primary to the desired retention policy on standby

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

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

2. Create a new standby controlfile

RMAN> backup current controlfile for standby format '/tmp/standby.ctl';

copy the above file to standby server.

On standby

3. Stop the log apply and start the standby DB in nomount mode

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

4. Restore the controlfile using the newly created standby controlfile

rman target /
RMAN> restore standby controlfile from '/home/oracle/standby.ctl';

Starting restore at 01-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1088 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/STDAUX/controlfile/o1_mf_5q7xfq1o_.ctl
output file name=/opt/app/oracle/oradata/STDAUX/controlfile/o1_mf_5q7xfq2t_.ctl
Finished restore at 01-JUL-11

5. Mount the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

6. Catalog the datafiles

RMAN> catalog start with '/opt/app/oracle/oradata/STDAUX/datafile';

Starting implicit crosscheck backup at 01-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1070 device type=DISK
Finished implicit crosscheck backup at 01-JUL-11

Starting implicit crosscheck copy at 01-JUL-11
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 01-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1822_70vb948l_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1817_70v8hn1w_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1820_70v994l1_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1816_70v7bz26_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1818_70v8vn7k_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1823_70vcb25c_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1815_70tj299j_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1819_70v93sg2_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1821_70v9rxmb_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1817_70v8wc4x_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_07_01/o1_mf_1_1814_70t0vmyk_.arc
File Name: /opt/app/oracle/flash_recovery_area/STDAUX/archivelog/2011_06_30/o1_mf_1_1813_70scm1p0_.arc

searching for all files that match the pattern /opt/app/oracle/oradata/STDAUX/datafile

List of Files Unknown to the Database
=====================================
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_temp_70v7btqh_.tmp
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_temp_70v7btqh_.tmp
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf
File Name: /opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf

7. Switch datafile to copy

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_system_70sbx6nk_.dbf"
datafile 2 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_sysaux_70sbx6nd_.dbf"
datafile 3 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_undotbs1_70sbx6n4_.dbf"
datafile 4 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_users_70sbx6nq_.dbf"
datafile 5 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_example_70sbx6ns_.dbf"
datafile 6 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_travelbo_70sbx6mx_.dbf"
datafile 7 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxindex_70sbx6n7_.dbf"
datafile 8 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbxlobs_70sbx6nb_.dbf"
datafile 9 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_tbx2ktbs_70sc6qw8_.dbf"
datafile 10 switched to datafile copy "/opt/app/oracle/oradata/STDAUX/datafile/o1_mf_test_70sbxko9_.dbf"

8. Stop and start flashback if it was enabled

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

9. Clear logfiles

SQL> select group# from v$log;

    GROUP#
----------
         1
         2
         6
         4
         5
         3

6 rows selected.

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 6;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 3;

SQL> select group# from v$standby_log;

    GROUP#
----------
         7
         8
         9
        10
        11
  
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;
alter database clear logfile group 11;

10. Start log apply service

SQL> alter database recover managed standby database using current logfile disconnect;

and verify log apply with

SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
      1814 NO
      1813 NO
      1825 YES
      1826 YES
      1827 YES
      1828 IN-MEMORY

11. Confirm that backup retention policy has changed

RMAN> show all;

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;

Once everything is confirm removed the duplicate standby logs and online logs from the disk using OS utility. Although metalink note states to do this step early on, having done this early one resulted in getting the following error

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 06/23/2011 14:01:05
RMAN-06571: datafile 1 does not have recoverable copy

It is possible the old logs may contain some information needed for recovery. So it's best to remove the duplicate log files at the end rather than early on.