Thinking Out Loud

October 12, 2021

How To Change RMAN Config For Standby DB

Filed under: RMAN — mdinh @ 9:19 pm

Here is the typical error when changing RMAN configuration for standby database.

[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> configure retention policy to recovery window of 7 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

There are suggestions found from MOS. Yuck!

Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)

Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)

Thanks to teammate for the easy method.

[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
HAWK      PHYSICAL STANDBY

SQL> select * from v$rman_configuration;

no rows selected

SQL> set serveroutput on
SQL> !vi t.sql

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 1

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME
---------- -----------------------------------------------------------------
VALUE                                                  CON_ID
-------------------------------------------------- ----------
         1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS                                0


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg2 ~]$


[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

IMPORTANT: Delete existing configuration before updating with another.

Here is a demonstration of what happens when configuration is not deleted.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO REDUNDANCY 1');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 2

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>

--------------------------------------------------

SQL> exec DBMS_BACKUP_RESTORE.DELETECONFIG(1);

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

%d bloggers like this: