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;

ORA-30554: function-based index is disabled

Problem

Following error occured from DB when access the table

Rejected - Error on table GPS.MAIN_PROD.
ORA-30554: function-based index GPS.FN_IDX_MAIN_PROD_1 is disabled

Solution

Check the table and index status

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'MAIN_PROD';

OBJECT_NAME  OBJECT_TYPE  OWNER   STATUS
-----------  -----------  ------- ---------
MAIN_PROD      TABLE       GPS     VALID

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'FN_IDX_MAIN_PROD_1';

OBJECT_NAME         OBJECT_TYPE  OWNER  STATUS
------------       ------------ ------ -------
FN_IDX_MAIN_PROD_1    INDEX      GPS     VALID

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     DISABLED

Re-build index which was disabled

SQL> alter index gps.FN_IDX_MAIN_PROD_1 rebuild;

Index altered.

Check the index status again confirm whether issue is sorted

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     ENABLED