When the default value is not the same as the default

3 min read

Apr 25, 2016 12:00:00 AM

I was working on a minor problem recently where controlfile autobackups were written to the wrong location during rman backups. Taking controlfile autobackups is generally a good idea, even if you configure controlfile backups yourself. Autobackups also include an spfile backup, though not critical for restore, is still convenient to have. And autobackups are taken not only after backups, but more importantly every time you change the physical structure of your database, like adding or removing datafiles and tablespaces which would make a restore with an older controlfile a lot harder. What happened in this case was that the CONTROLFILE AUTOBACKUP FORMAT parameter was changed from the default '%F' to the value '%F'. Yes, the values are the same. But setting a value and not leaving it at the default changed the behaviour of those autobackups. Where by default '%F' means writing to the flash recovery area, explicitly setting the format parameter to '%F' will save the autobackup to the folder $ORACLE_HOME/dbs/. See for yourself. This shows an autobackup while the parameter is set to the default and as expected, the autobackup is written to the flash recovery area. So that is the correct location but the filename is a bit off. It should be c-DBID-YYYYMMDD-SERIAL. [sourcecode highlight="20"] RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT; RMAN configuration parameters for database with db_unique_name CDB1 are: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default RMAN> backup spfile; Starting backup at 18-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 18-APR-16 channel ORA_DISK_1: finished piece 1 at 18-APR-16 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2016_04_18/o1_mf_nnsnf_TAG20160418T172428_ckb62f38_.bkp tag=TAG20160418T172428 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-APR-16 Starting Control File and SPFILE Autobackup at 18-APR-16 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2016_04_18/o1_mf_s_909509070_ckb62gko_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 18-APR-16[/sourcecode] Now we are setting the to format string to '%F' and observe the autobackup is not written to the FRA but $ORACLE_HOME/dbs. At least it has the filename we were expecting. [sourcecode highlight="21"] RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; new RMAN configuration parameters are successfully stored RMAN> backup spfile; Starting backup at 18-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 18-APR-16 channel ORA_DISK_1: finished piece 1 at 18-APR-16 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2016_04_18/o1_mf_nnsnf_TAG20160418T172447_ckb62z7f_.bkpx tag=TAG20160418T172447 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-APR-16 Starting Control File and SPFILE Autobackup at 18-APR-16 piece handle=/u01/app/oracle/product/12.1.0.2/db_1/dbs/c-863887021-20160418-04 comment=NONE Finished Control File and SPFILE Autobackup at 18-APR-16 RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT; RMAN configuration parameters for database with db_unique_name CDB1 are: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; [/sourcecode] This is like Schrödinger's parameter, where you can either get the correct location or the correct name, but not both. To be fair, not assigning the right name to the autobackup in the FRA does not matter much because the files will be found during a restore anyway. At this point it is good to remember how to use CLEAR to reset a parameter to it's default instead of just setting the default value. [sourcecode]RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR; old RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; RMAN configuration parameters are successfully reset to default value RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT; RMAN configuration parameters for database with db_unique_name CDB1 are: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default[/sourcecode] I have tested this in versions 10g, 11g and 12.1.0.2 with the same result. The behaviour is also not unknown. In fact, bug 4248670 was logged against this in 2005 but has not been resolved so far. My Oracle Support does mention the above workaround of clearing the parameter in note 1305517.1 though.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

When the default value is not the same as the default

Share this

Share this

More resources

Renewing the (Default) PMM Certificate

EBS: R12 Default OACORE Memory Settings Are Not Enough

Oracle 11g -- Audit Enabled by Default, But What About Purging?

Ready to unlock value from your data?