Change Backup Retention Policy on Standby Database

Problem

Following error occured when try to change backup policies on standby db

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

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 09/08/2022 08:08:11
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file;

Solution

From Primary

1. Change backup polices as per the requirement

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

2. Create standby controlfile and copy to standby database

SQL> alter database create standby controlfile as '/tmp/stby.ctl';
Database altered.

From Standby

1. Cancel redo apply

SQL> alter database recover managed standby database cancel;

2. Backup exisitng control files and replace newly copied controlfiles from primary

cd /u02/oradata/PROD_STBY/controlfile
mv control01.ctl control01.ctl_bkp
mv control02.ctl control02.ctl_bkp
cp /tmp/stby.ctl control01.ctl
cp /tmp/stby.ctl control02.ctl

3. Mount standby database

SQL> alter database mount;

4. Change standby file managemnet to manual inorder change datafiles

alter system set standby_file_management='MANUAL';

5. Rename the datafile names to same as standby datafiles

SQL> select name from v$datafile;
NAME
--------------------------------------
/u02/oradata/PROD/datafile/system01.dbf
/u02/oradata/PROD/datafile/syaux01.dbf
/u02/oradata/PROD/datafile/undo01.dbf
/u02/oradata/PROD/datafile/users01.dbf
/u02/oradata/PROD/datafile/data01.dbf
/u02/oradata/PROD/datafile/index01.dbf

alter database rename file '/u02/oradata/PROD/datafile/system01.dbf' to '/u02/oradata/PROD_STBY/datafile/system01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/syaux01.dbf' to '/u02/oradata/PROD_STBY/datafile/syaux01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/undo01.dbf' to '/u02/oradata/PROD_STBY/datafile/undo01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/users01.dbf' to '/u02/oradata/PROD_STBY/datafile/users01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/data01.dbf' to '/u02/oradata/PROD_STBY/datafile/data01.dbf';
alter database rename file '/u02/oradata/PROD/datafile/index01.dbf' to '/u02/oradata/PROD_STBY/datafile/index01.dbf';

6. Change back standby file managemnet to auto

alter system set standby_file_management='AUTO';

7. Start redo apply

SQL> alter database recover managed standby database disconnect from session;