Change RMAN configuration using DBMS_BACKUP_RESTORE

This post is also available in: Português

In this article I will show how to change the RMAN configuration when you are connect inside an Oracle DB, using the package DBMS_BACKUP_RESTORE and its procedures.

Modifying those RMAN attributes is an easy task when you are connected via Oracle RMAN utility. You can simply use the CONFIGURE syntax to change it. However, what a few people know is that you can also do it using the non-documented DBMS package DBMS_BACKUP_RESTORE via SETCONFIG procedure.

This is a specially powerful tool when you have some business or process logic that needs to change it via PL/SQL.

One case where this approach would be useful is, for example, creating a trigger in a Data Guard environment based on the DB_ROLE_CHANGE that will change the value of "ARCHIVELOG DELETION POLICY" depending if the instance is the primary or the standby:

• For Primary: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
• For Standby: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

In this article, I will show you how to use it.

First of all, checking the defaults:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.4/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

The "#default" in the end of each lines tells us that nothing was changed and everything is with out-of-box specs.

First of all, let's change the BACKUP OPTIMIZATION to ON via PL/SQL:

SQL> var a number
SQL> exec :a := DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

PL/SQL procedure successfully completed.

SQL> print :a

         A
----------
         1

SQL>

The number 1 returned by the function is the ID of the configuration inserted. We can check all the configurations modified by querying v$rman_configuration:

SQL> set lines 200
SQL> col name format a40
SQL> col value format a100
SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- -------------------------------------------------------
         1 BACKUP OPTIMIZATION                      ON

SQL>

In RMAN, we can certify that it was correctly modified:

RMAN> show backup optimization;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE BACKUP OPTIMIZATION ON;

RMAN>

Now, I will change the parameter COMPRESSION ALGOTITHM:

SQL> exec :a := DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION ALGORITHM',q'['HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE]');

PL/SQL procedure successfully completed.

SQL> print :a

         A
----------
         2

SQL>

Note now that ID 2 was inserted. Query the configuration table:

SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- -------------------------------------------------------
         1 BACKUP OPTIMIZATION                      ON
         2 COMPRESSION ALGORITHM                    'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE

SQL>

In RMAN, everything is OK.

RMAN> show compression algorithm;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE;

RMAN>

The biggest problem of SETCONFIG procedure is that it does not make any validation. You can simply add any attribute string or value that you want. Obviously this can generate an dictionary incosistency.

In this example, I will change CONTROLFILE AUTOBACKUP to MAYBE (note that this option does not exists).

SQL> exec :a := DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','MAYBE');

PL/SQL procedure successfully completed.

SQL> print :a

         A
----------
         3

SQL>

It inserted without validating or constraining anything. If we check in RMAN, we can see that we corrupted our dictionary,

RMAN> show controlfile autobackup;

RMAN configuration parameters for database with db_unique_name ORCL are:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 12/10/2015 10:35:47
RMAN-06466: error parsing configuration string (CONFIGURE CONTROLFILE AUTOBACKUP MAYBE;)
RMAN-01009: syntax error: found "identifier": expecting one of: "clear, format, off, on"
RMAN-01008: the bad identifier was: MAYBE
RMAN-01007: at line 1 column 34 file: Configuration Row

RMAN>

And the data is inserted in the RMAN configuration table.

SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- -------------------------------------------------------
         1 BACKUP OPTIMIZATION                      ON
         2 COMPRESSION ALGORITHM                    'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE
         3 CONTROLFILE AUTOBACKUP                   MAYBE

SQL>

So to clear this mess, we can simply use the DELETECONFIG procedure passing the ID of the line we want to delete.

SQL> EXEC DBMS_BACKUP_RESTORE.DELETECONFIG(3);

PL/SQL procedure successfully completed.

SQL>

Nice. If we check the configuration table and RMAN, now the inconsistency has gone.

SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- -------------------------------------------------------
         1 BACKUP OPTIMIZATION                      ON
         2 COMPRESSION ALGORITHM                    'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE

SQL>

But what if you want to change the value of a parameter that is already defined on the table? If you simply use SETCONFIG to define a new value, it will generate an extra line. So you would have 2 values for the same parameter that will lead to another inconsistency.

The solution is to remove the parameter before adding the new value to it. Let's, for example, turn off BACKUP OPTIMIZATION.

SQL> exec DBMS_BACKUP_RESTORE.DELETECONFIG(1);

PL/SQL procedure successfully completed.

SQL> exec :a := DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','OFF');

PL/SQL procedure successfully completed.

SQL> print :a

         A
----------
         1

SQL>

Checking on configuration table:

SQL> select * from v$rman_configuration;

     CONF# NAME                                     VALUE
---------- ---------------------------------------- -------------------------------------------------------
         1 BACKUP OPTIMIZATION                      OFF
         2 COMPRESSION ALGORITHM                    'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE

SQL>

You can also use the RESETCONFIG procedure if you wish to clear all the defined configuration. This is specially if you wish to return all the parameters to their default values.

SQL> exec DBMS_BACKUP_RESTORE.RESETCONFIG;

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

no rows selected

SQL>

That's it. Now you can plan and create PL/SQL triggers/procedures/jobs that can check and modify the RMAN configurations.

+7