Oracle 12c – Automatic Control File Backups

By William Sescu

By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons.

RMAN> SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name OCM121 are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. So, if your database is running in NOARCHIVELOG mode, you will never see any impact, independent of controlfile autobackup is on or off.
However, my database is running in ARCHIVELOG mode and at the moment the controlfile autobackup feature is disabled.
But even when the auto backup feature is disabled, RMAN will still back up the current controlfile and the server parameter file whenever any backup command includes datafile 1 from the data files that belong to the target database. In an Oracle database, data file 1 is always part of the system tablespace, which contains the data dictionary.

But I have heard that it is highly recommended to enable automatic controlfile backups. It will ensure that the critical controlfile is backed up regularly following a backup or structural change to the database. Once you configure automatic controlfile backup, RMAN will automatically back up your target database controlfile, as well as the current server parameter file, when any of the following events occurs:

• Successful completion of either a backup or the copy command
• After a create catalog command from the RMAN prompt is successfully completed
• Any structural changes to the database modify the contents of the control file

Any changes to the physical structure of your database, even if they are made through SQL*Plus, will trigger a controlfile auto backup, e.g.

• adding a tablespace or data file
• dropping a data file
• placing a tablespace offline or online
• adding an online redo log, and renaming a data file

So, to follow the recommendation, I will enable the automatic backup of the controlfile. At the moment I have no backup of the controlfile, which is not good at all.

RMAN>  list backup of controlfile;

specification does not match any backup in the repository

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

But nothing happens. What is going on here. Is it a bug, a feature or something else.
Hold on. Then … all of a sudden, the backup of the controlfile popps up.

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23
        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_28/s_926437339.526.926437343
  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19

To understand what is happening here, the answer is like always. It depends. 🙂 In case you are running an Oracle database with 11gR2 or higher, then it is a feature. Anything below would be a bug.

Beginning with Oracle 11gR2, the controlfile autobackup deferral feature has been implemented. In order to increase performance, the controlfile autobackup creation after structural changes, has been deferred. In previous releases, one controlfile autobackup is created with each DDL command that makes structural changes in the database and we can see in the alert.log a message about controlfile autobackup creation after each DDL command executed.
This can provoke serious performance problems when multiple structural changes are made together. Starting from Oracle Database Release 11g Release 2, RMAN takes only one control file autobackup when multiple structural changes contained in a script have been applied (for example, adding tablespaces, altering the state of a tablespace or datafile,
adding a new online redo log, renaming a file, and so on) during a specified time.

But what does time mean here exactly. It is 1 minute, 1 hour, or 1 day?

The deferral time is controlled by an underscore parameter that defaults to 300 seconds (5 minutes). The parameter is the following:

_controlfile_autobackup_delay=300

The minimum value for that parameter is 0, which simulates the behavior before 11gR2. The maximum value in 12c is (1024*1024*1024*2)-1, which is 2147483647 seconds. However, I don’t see any practical value for setting the value that high.

In 11gR2 or higher, the controlfile autobackups are created by MMON slaves after few minutes (5 minutes per default) of the structural changes, which increases performance. It is also expected that no message about controlfile autobackup creation will appear in the alert.log.
However, there will be one MMON slave trace file with the controlfile creation information, that will be a file named: SID__m000_<OS_PID>.trc
Ok. Let’s try to simulate the old behavior by setting the autobackup delay to 0, and to create another tablespace afterwards.

SQL> alter system set "_controlfile_autobackup_delay"=0;

System altered.

SQL> create tablespace unpatient2 datafile size 16M;

Tablespace created.

And now the controlfile autobackup is created immediately.

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23
        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_28/s_926437339.526.926437343
  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
132     Full    9.89M      DISK        00:00:05     28-OCT-2016 17:41:16
        BP Key: 315   Status: AVAILABLE  Compressed: NO  Tag: TAG20161029T113939
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_29/s_926509179.514.926509183
  Control File Included: Ckp SCN: 10271426     Ckp time: 28-OCT-2016 17:41:11

So, do I recommend to set the RMAN controlfile autobackup to ON. Yes absolutely. And do I recommend to set the controlfile autobackup delay to 0. No, probably not. I think, that the 5-minute interval is a quite good compromise. You just need to be aware, that it exists.

Another hint is, that you should not rely too much on the view V$RMAN_BACKUP_JOB_DETAILS. In this view, the autobackups should be populated, whenever a autobackup happend. But in my case the AUTOBACKUP_DONE is always set to NO.

SQL> select start_time,end_time,status,autobackup_done, AUTOBACKUP_COUNT from
2 V$RMAN_BACKUP_JOB_DETAILS where autobackup_done = 'YES';

no rows selected

There is a patch available from Oracle: “Patch 18074513: V$RMAN_BACKUP_JOB_DETAILS VIEWS COLUMN AUTOBACKUP_DONE DOESNOT GET POPULATED”, but it is not available for every platform and every version.

Better use the RMAN “list backup of controlfile;” command. That one is much more reliable.

Cheers,
William