The Orphan of Backups

Recently I was privileged to participate in a major storage outage where not only did an
entire network storage appliance fail, but the data located on it was lost as well. While this
may sound a nightmare to most IT professionals, it was also a once-in-a-lifetime
opportunity to verify our various recovery strategies. Too often backup procedures
are put in place and not tested.

The majority of the data lost was designated for test and development databases and
in most cases, our recovery strategies worked. But, one of the items we were missing in
most of the recovery strategies was a backup copy of the database spfile. We had stored our spfiles in an ASM diskgroup and it was lost with the rest of the ASM data. The
Oracle Recovery Manager (RMAN) has the capability to backup the spfile with the
rest of the database files, but it needs some settings other than the defaults.

So, while I am snowed-in at home this weekend, I thought I would share my
experience in setting up spfile backups. According to Oracle documentation,
to backup the spfile automatically, all you need to do is enable controlfile
autobackup, like this:

[oracle@rhel1 scripts]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 28 11:33:30 2015

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

connected to target database: JON11G (DBID=856759094)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name JON11G 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 '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backups/JON11G/snapcf_LVL0_JON11G.f';

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

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

RMAN> show all;

RMAN configuration parameters for database with db_unique_name JON11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
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 '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backups/JON11G/snapcf_LVL0_JON11G.f';

RMAN>

I use these RMAN commands to backup my database:

run  {
configure snapshot controlfile name to '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
allocate channel dsk_1 type disk;
allocate channel dsk_2 type disk;
backup incremental level=0 filesperset=1
database format '$BACKUPDIR/%d_LVL0_%T_s%s_p%p_%u';
sql 'alter system archive log current';
backup
archivelog all
format '$BACKUPDIR/%d_ARCH_%T_s%s_p%p_%u'
delete input;
backup current controlfile format '$BACKUPDIR/%d_cntrl_LVL0_%T_s%s_p%p_%u';
release channel dsk_1;
release channel dsk_2;
}

After executing a backup, I tried to recover my spfile from my latest backup:

[oracle@rhel1 scripts]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 28 11:43:56 2015

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

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initJON11G.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2224584 bytes
Variable Size                 92278328 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

RMAN> set DBID=856759094;

executing command: SET DBID

(Setting the DBID is a MUST since there is no controlfile in play yet)

RMAN> restore spfile;

Starting restore at 28-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2015 11:45:17
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

(Ok, I will use ‘from autobackup’)

RMAN> restore spfile from autobackup;

Starting restore at 28-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150228
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150227
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150226
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150225
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150224
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150223
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150222
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2015 11:45:28
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>

What? I set the controlfile autobackup to ‘ON’ and the spfile backup is
not in my backup set? As a side note, unless you specify MAXDAYS during the
restore spfile command, it will go back seven days by default.

So, where did my spfile backup go? The backup goes to the AUTOBACKUP
location, which in this case was the db_recovery_file_dest value from
my spfile. In the absence of this value, the spfile backup will go
to the $ORACLE_HOME/dbs location. I looked in my FRA location and
found these files:

[oracle@rhel1 autobackup]$ ls
2015_02_26  2015_02_27  2015_02_28
[oracle@rhel1 autobackup]$ cd 2015_02_28
[oracle@rhel1 2015_02_28]$ ll
total 19232
-rw-r----- 1 oracle oinstall 9830400 Feb 28 11:43 o1_mf_s_872854987_bh3zkd9r_.bkp
-rw-r----- 1 oracle oinstall 9830400 Feb 28 11:43 o1_mf_s_872854996_bh3zknpg_.bkp

I plugged one of these file names into my restore command and it worked:

RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/JON11G/autobackup/2015_02_28/o1_mf_s_872854987_bh3zkd9r_.bkp';

Starting restore at 28-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/JON11G/autobackup/2015_02_28/o1_mf_s_872854987_bh3zkd9r_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-FEB-15

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area    2071076864 bytes

Fixed Size                     2228032 bytes
Variable Size               1191182528 bytes
Database Buffers             872415232 bytes
Redo Buffers                   5251072 bytes

RMAN>

Wanting to keep all my backups in the same place, especially since that location has
built-in redundancy, I need to include my spfile backup commands with my other backup
commands:

run  {
configure snapshot controlfile name to '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
allocate channel dsk_1 type disk;
allocate channel dsk_2 type disk;
backup incremental level=0 spfile format '$BACKUPDIR/spfile_%d_%s_%T.bak'filesperset=1
database format '$BACKUPDIR/%d_LVL0_%T_s%s_p%p_%u';
sql 'alter system archive log current';
backup
archivelog all
format '$BACKUPDIR/%d_ARCH_%T_s%s_p%p_%u'
delete input;
backup current controlfile format '$BACKUPDIR/%d_cntrl_LVL0_%T_s%s_p%p_%u';
release channel dsk_1;
release channel dsk_2;
}

These changes worked to create an spfile backup in my designated backup location
with a name of my choosing:

[oracle@rhel1 2015_02_28]$ ll /backups/JON11G/
total 1102460
drwxr-xr-x 2 oracle oinstall      4096 Feb 28 12:09 HIST
-rw-r----- 1 oracle oinstall    459776 Feb 28 12:09 JON11G_ARCH_20150228_s66_p1_22q0devf
-rw-r----- 1 oracle oinstall      2560 Feb 28 12:09 JON11G_ARCH_20150228_s67_p1_23q0devf
-rw-r--r-- 1 oracle oinstall      5434 Feb 28 12:09 JON11G_backup.log
-rw-r----- 1 oracle oinstall   9797632 Feb 28 12:09 JON11G_cntrl_LVL0_20150228_s68_p1_24q0devh
-rw-r----- 1 oracle oinstall   9748480 Feb 28 12:09 JON11G_controlfile.trc
-rw-r----- 1 oracle oinstall 698966016 Feb 28 12:08 JON11G_LVL0_20150228_s62_p1_1uq0deuk
-rw-r----- 1 oracle oinstall 398934016 Feb 28 12:08 JON11G_LVL0_20150228_s63_p1_1vq0deuk
-rw-r--r-- 1 oracle oinstall      1776 Feb 28 12:09 JON11G_restore.cmd
-rw-r----- 1 oracle oinstall   9748480 Feb 28 12:09 snapcf_LVL0_JON11G.f
-rw-r----- 1 oracle oinstall     98304 Feb 28 12:09 spfile_JON11G_64_20150228.bak

Simple and effective

Advertisements

One thought on “The Orphan of Backups

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s