Notes regarding Backing up and Restoring an Oracle database using the ControlFile as a Repository.

By Edward Stoever

Using a controlfile as the repository for the Oracle database makes sense for many DBA's. This is because using a seperate database as the RMAN repository implies issues of maintenance, backing up, and software licensing.

As long as the DBA is backing up using the controlfile as the repository, it is necessary that he or she understand how to restore the controlfile and the spfile, in the event that all disks are lost.

Multiplexing should be the primary safety net for backing up the controlfiles. Multiplexing is the concept of allowing Oracle database software to maintain multiple copies of the controlfile on seperate physical disks or disk arrays. In the event that a disk is lost, the database administrator replaces the disk, then restores the file system. Restoring the Oracle files stored on that single disk is a simple task with RMAN.

Understanding that the control file is multiplexed on at least one other disk, the DBA knows that the repository is safe. The DBA can use OS commands to copy the controlfile, and likely the online-redo files as well, from the surviving disk to the new disk, then use RMAN to restore the lost database files and recover.

The following figure demonstrates a simple layout for securing an Oracle Database against the loss of a single disk, or single array of disks:

      

To further secure the database from loss, the FLASH RECOVERY AREA, a 10g auto-managed directory and sub-directories for backup sets, would likely be copied to tape once per day.

But suppose the entire server is lost, and all that is left is a tape copy of the Flash Recovery Area, which includes RMAN backup pieces. Assuming that were the case, the DBA would have to rebuild the entire server, reinstall the OS and the Oracle Software, then restore the Flash Recovery Area from tape. At that point, the ControlFile, and thus the RMAN repository is buried in a backup piece in the Flash Recovery Area. If the Archives and online redo are lost, the recovered database will not include all transactions, but it will be current up to the last backup.

Having read Oracle documentation and Oracle University books, it is easy to get the idea that in order to restore the control files, the DBA needs the "DBID", a unique number for a given database. However, knowing the DBID is not necessary. To restore the control files, the DBA first must restore the spfile, which does not require the DBID. There are two methods for restoring the spfile: 
RMAN> RESTORE SPFILE FROM '/u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp';
restore spfile from autobackup db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' db_name='dprod';
With the database started using the restored spfile, all the dba needs to do is to use the command
   RESTORE CONTROLFILE FROM AUTOBACKUP;

Let's run through the two examples above in order. We will start with a restore of the spfile from a specified backup piece.
[oracle@xyx oracle]$ . oraenv
ORACLE_SID = [oracle] ? dprod
[oracle@xyx oracle]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Sep 13 10:33:35 2006

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

connected to target database: DPROD (DBID=589180410)

RMAN> show all;

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

RMAN> backup database;

Starting backup at 13-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/dprod/system01.dbf
input datafile fno=00003 name=/u02/oradata/dprod/sysaux01.dbf
input datafile fno=00002 name=/u02/oradata/dprod/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/dprod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-06
channel ORA_DISK_1: finished piece 1 at 13-SEP-06
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T103349_2jjj7y1g_.bkp tag=TAG20060913T103349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 13-SEP-06

Starting Control File and SPFILE Autobackup at 13-SEP-06
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-06

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.
[oracle@xyx oracle]$ # get rid of all database files, control files, online redo files:
[oracle@xyx oracle]$ mv /u02/oradata/dprod /u02/oradata/dprod_old
[oracle@xyx oracle]$ # recreate the directory, IMPORTANT STEP:
[oracle@xyx oracle]$ mkdir /u02/oradata/dprod
[oracle@xyx oracle]$ # get rid of the spfile:
[oracle@xyx oracle]$ rm $ORACLE_HOME/dbs/*dprod*.ora
[oracle@xyx oracle]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Sep 13 10:35:45 2006

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

connected to target database (not started)

RMAN> startup nomount

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

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

Total System Global Area     159383552 bytes

Fixed Size                     1259672 bytes
Variable Size                 58722152 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7127040 bytes

RMAN> RESTORE SPFILE FROM '/u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp';

Starting restore at 13-SEP-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 13-SEP-06

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    1879048192 bytes

Fixed Size                     1261476 bytes
Variable Size                436207708 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  15515648 bytes

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 13-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DPROD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/dprod/control01.ctl
output filename=/u02/oradata/dprod/control02.ctl
output filename=/u02/oradata/dprod/control03.ctl
Finished restore at 13-SEP-06

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 13-SEP-06
Starting implicit crosscheck backup at 13-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 13-SEP-06

Starting implicit crosscheck copy at 13-SEP-06
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-SEP-06

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601036454_2jjj8px2_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/dprod/system01.dbf
restoring datafile 00002 to /u02/oradata/dprod/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/dprod/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/dprod/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T103349_2jjj7y1g_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T103349_2jjj7y1g_.bkp tag=TAG20060913T103349
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 13-SEP-06

RMAN> recover database;

Starting recover at 13-SEP-06
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/13/2006 10:38:23
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 2286366

RMAN> alter database open resetlogs;

database opened

RMAN>



Next, we will restore the spfile by indicating the db_recovery_file_dest and db_name parameters. 
[oracle@xyx oracle]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Sep 13 10:14:15 2006

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

connected to target database: DPROD (DBID=589180410)

RMAN> backup database;

Starting backup at 13-SEP-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/dprod/system01.dbf
input datafile fno=00003 name=/u02/oradata/dprod/sysaux01.dbf
input datafile fno=00002 name=/u02/oradata/dprod/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/dprod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-06
channel ORA_DISK_1: finished piece 1 at 13-SEP-06
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T101422_2jjh3gps_.bkp tag=TAG20060913T101422 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 13-SEP-06

Starting Control File and SPFILE Autobackup at 13-SEP-06
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601035288_2jjh48bj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-06

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.
[oracle@xyx oracle]$ # clean up previous dbprod_old directory:
[oracle@xyx oracle]$ rm -fr /u02/oradata/dprod_old
[oracle@xyx oracle]$ # get rid of all database files, control files, online redo files:
[oracle@xyx oracle]$ mv /u02/oradata/dprod /u02/oradata/dprod_old
[oracle@xyx oracle]$ # recreate the directory, IMPORTANT STEP:
[oracle@xyx oracle]$ mkdir /u02/oradata/dprod
[oracle@xyx oracle]$ # get rid of the spfile:
[oracle@xyx oracle]$ rm $ORACLE_HOME/dbs/*dprod*.ora
[oracle@xyx oracle]$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Sep 13 10:16:47 2006

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

connected to target database (not started)

RMAN> startup nomount;

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

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

Total System Global Area     159383552 bytes

Fixed Size                     1259672 bytes
Variable Size                 58722152 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7127040 bytes

RMAN> restore spfile from autobackup db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' db_name='dprod';

Starting restore at 13-SEP-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DPROD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601035288_2jjh48bj_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 13-SEP-06

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    1879048192 bytes

Fixed Size                     1261476 bytes
Variable Size                436207708 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  15515648 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 13-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DPROD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601035288_2jjh48bj_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/dprod/control01.ctl
output filename=/u02/oradata/dprod/control02.ctl
output filename=/u02/oradata/dprod/control03.ctl
Finished restore at 13-SEP-06

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 13-SEP-06
Starting implicit crosscheck backup at 13-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 13-SEP-06

Starting implicit crosscheck copy at 13-SEP-06
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-SEP-06

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DPROD/autobackup/2006_09_13/o1_mf_s_601035288_2jjh48bj_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/dprod/system01.dbf
restoring datafile 00002 to /u02/oradata/dprod/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/dprod/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/dprod/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T101422_2jjh3gps_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/DPROD/backupset/2006_09_13/o1_mf_nnndf_TAG20060913T101422_2jjh3gps_.bkp tag=TAG20060913T101422
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 13-SEP-06

RMAN> recover database;

Starting recover at 13-SEP-06
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/13/2006 10:19:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 2284413

RMAN> alter database open resetlogs;

database opened

RMAN>
Oracle documentation states, "Because the repository is not available when you restore the control file, run the SET DBID command to identify the target database."

I think it is a good idea to have the DBID in the event of an actual restore, even though the two methods shown above work perfectly well. To ensure that you have the DBID, log RMAN's activity into the Flash Recovery Area. You will find the DBID in the log files; every time RMAN connects to the target database, it reports the DBID. For example, your login script for RMAN could be built on: 
[oracle@eve oracle]$ ORAENV_ASK=NO
[oracle@eve oracle]$ export ORACLE_SID=dprod
[oracle@eve oracle]$ . oraenv
[oracle@xyx oracle]$ RMAN_LOG=rman_`date +%y-%m-%d`.log
[oracle@xyx oracle]$ rman target / log=/u01/app/oracle/flash_recovery_area/DPROD/logs/$RMAN_LOG @/home/oracle/rman_backup_script.rman


Taken from the Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 1 (10.1)
Part Number B10734-01

Performing Recovery with a Backup Control File and No Recovery Catalog

This section assumes that you have RMAN backups of the control file, but do not use a recovery catalog. Assuming that you enabled the control file autobackup feature for the target database, you can restore an autobackup of the control file. Because the autobackup uses a default format, RMAN can restore it even though it does not have a repository available that lists the available backups. You can restore the autobackup to the default or a new location. RMAN replicates the control file to all CONTROL_FILES locations automatically.

Note:

If you know the backup piece name (for example, from the media manager or because the piece is on disk), then you can specify the piece name using the RESTORE CONTROLFILE FROM 'filename' command. The server records the location of every autobackup in the alert log.

Because you are not connected to a recovery catalog, the control file must have a record of all needed backups. If any backups are not listed in the control file, then RMAN cannot restore them. You can add backup pieces and image copies to the control file repository with the CATALOG command.

Because the repository is not available when you restore the control file, run the SET DBID command to identify the target database. You should only run the SET DBID command in the following specialized circumstances:

  • You are not connected to a recovery catalog and want to restore the control file or server parameter file.
  • You are connected to a recovery catalog and want to restore the control file, but the database name is not unique in the recovery catalog.
  • The server parameter file is lost and you want to restore it.

To recover the database with an autobackup of the control file without a recovery catalog:

  1. Start RMAN and connect to the target database. For example, run: 
    CONNECT TARGET /
  2. Start the target instance without mounting the database. For example: 
    STARTUP NOMOUNT;
  3. Set the database identifier for the target database with SET DBID. RMAN displays the DBID whenever you connect to the target. You can also obtain it by inspecting saved RMAN log files, querying the catalog, or looking at the filenames of control file autobackup. (refer to "Restoring When Multiple Databases in the Catalog Share the Same Name: Example"). For example, run: 
    SET DBID 676549873;
  4. Restore the autobackup control file, then perform recovery. Do the following:
    1. Optionally, specify the most recent backup time stamp that RMAN can use when searching for a control file autobackup to restore.
    2. If a nondefault format was used to create the control file, then specify a nondefault format for the restore of the control file.
    3. If the channel that created the control file autobackup was device type sbt, then you must allocate one or more sbt channels. Because no repository is available, you cannot use preconfigured channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a channel.
    4. Restore the autobackup of the control file, optionally setting the maximum number of days backward that RMAN can search (up to 366) and the initial sequence number that it should use in its search for the first day.
    5. If you know that your control file contained information about configured channels that will be useful to you in the rest of the restore process, you can exit the RMAN client at this point, to clear manually allocated channels from step "c". If you then restart the RMAN client and mount the database those configured channels become available for your use in the rest of the restore and recovery process.

      If you do not care about using configured channels from your control file, then you can simply mount the database at this point.

    6. If the online logs are inaccessible, then restore and recover the database as described in "Performing Database Point-In-Time Recovery". You must terminate recovery by setting the UNTIL clause to a time, log sequence, or SCN before the online redo logs. If the online logs are usable, then perform a complete recovery as described in Oracle Database Backup and Recovery Basics.

    In this example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs recovery of the database to log sequence 13243, which is the most recent archived log:

    RUN 
{
  # Optionally, set upper limit for eligible time stamps of control file 
  # backups

  # SET UNTIL TIME '09/10/2000 13:45:00';
  # Specify a nondefault autobackup format only if required
  # SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK 
  #   TO '?/oradata/%F.bck';
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # allocate manually
  RESTORE CONTROLFILE FROM AUTOBACKUP
    MAXSEQ 100           # start at sequence 100 and count down
    MAXDAYS 180;         # start at UNTIL TIME and search back 6 months
  ALTER DATABASE MOUNT DATABASE;

}
# uses automatic channels configured in restored control file
RESTORE DATABASE UNTIL SEQUENCE 13243;
RECOVER DATABASE UNTIL SEQUENCE 13243; # recovers to latest archived log
  5. If recovery was successful, then open the database and reset the online logs: 
    ALTER DATABASE OPEN RESETLOGS;