RMAN Restore and Recover of a Database When the Repository and spfile/init.ora Files are Lost

Oracle Database RMAN Restore/Recovery without spfile/init.ora Files
You have for whatever reason lost your spfile and your initORACLESID.ora files and you find yourself in a situation where you need to restore and recover your database.
This doesn’t happen very often and hopefully it never happens to you. The situation can arise during clone (duplicating a database using RMAN) but for whatever the reasons may be for the lost of your initialization files (media problems, etc.) here’s an outline of steps that may help to get your database back up and running.
If you only have the physical backuppieces on disk or tape and have lost your database and initialization files this is basically a disaster and or you have a need to restore and recover a database from and old backup that has long since been deleted or aged out of the RMAN Repository (examples would be a disk backup that was copied to tape prior to its deletion from RMAN).
Oracle 8i you don’t have spfiles but the Disk Backuppieces need to be restored or located in their original backup location, if there are backuppieces that are unknown to the restored control file it isn’t possible to catalog these files in this release but you can manually extract the information from the backupieces using PL/SQL.
Oracle 9i you can restore the spfile from an autobackup as well as the controlfile. You can’t catalog backuppieces unknown to the restored controlfile (same as 8i) but you can use PL/SQL to extract the information from the backuppieces. The disk backupieces must be located in their original backup locations.
Oracle 10g and 11g allows you to restore the spfile and controlfile from autobackups. You don’t have to place the original disk backuppieces in the same exact location as the original backup location. You can catalog unknown backuppieces that are missing from the restored controlfile and you can use PL/SQL to extract any missing information that you may need.
The goal is to restore the spfile and controlfile either from an autobackup controlfile or via a manual extraction process from an rman backuppiece so that the instance can be mounted. The restored controlfile then becomes the RMAN repository (no catalog), allowing a ‘normal’ catalog-free restore and recovery of the database to be executed.
The restore controlfile should therefore should also contain the metadata for the database and archivelog backups which will need to be restored and recovered.
The following constraints have to be acknowledged and understood:
It is not possible to restore an init.ora file from a backuppiece as init.ora files are never included in a backup for any Oracle release.
It is your responsibility to identify the required backuppieces required for the restore. This is where RMAN reports/listings come into play, if you generate these after each backup and store somewhere you can refer to these reports or similar type of listings.
The DBID of the target database has to be known
Unless you are using 10g+ all disk backuppieces must reside in the original backup location in case you need to restore to a new host and identical backup directory must be created on that host.
If you have to restore the spfile, controlfiles and datafiles by extracting this information from the backuppieces you will need to contact Oracle Support Services to work with them in order to accomplish these tasks.
Keep the backup logs for at least one complete backup cycle so that the backuppieces and the target DBID can be identified by reviewing these files.
Pick a format for the backuppiece that makes it easy to identify these files by name should the log files no longer be available.
8i Only – backup your init.ora file using any means possible. I have a crontab script that executes nightly that creates copies of my spfiles (10g and 11g) as well as the listener.ora, tnsnames.ora and other files that aid in the recovery process should I find myself in a situation that requires access to these files. I’ll include the script I use at the end of this post.
8i Only – make sure you backup the controlfile after every backup or archivelog backup, this is just good practice and you’ll see why if you have find yourself in a situation where you need this file. You should always have a controlfile backup that contains the latest backup metadata.
9i+ always use an spfile because RMAN can backup this file automatically whenever an operation occurs (within RMAN) that warrants a backup.
9i+ always turn on CONTROLFILE AUTOBACKUP ON
Become as familiar as possible with your RMAN configuration, monitor where the backups are written, review the logs daily, review your backup frequency and most importantly know where the backup of the controlfile is located at.
Restoring When Everything is Gone
Restore the init.ora file – applies for All Releases. RMAN has to connect to a running instance (a database started with an init.ora file in nomount mode). If an init.ora file is used rather than a spfile this needs to be restored from an OS backup or manually recreated in the proper format.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is NOT USED – relevant to Oracle 8i (where controlfile AUTOBACKUP didn’t exist), Oracle 9i+ where the persistent configuration CONTROLFILE AUTOBACKUP was set to OFF (not the default).
Figure out the latest controlfile backuppiece – if at all possible this should contain the latest metadata for the database and archivelog backups that required for the restore/recovery purposes. Use the past RMAN logs, check available backup directories on disk or query the media manager for your tape catalog to identify this information.
Extract the controlfile – Oracle 9i+ you can restore the control file from an explict backuppiece. Start the database in nomount mode via SQL*Plus and issue the command RMAN> restore controlfile from ‘backuppiece’;. If you are having problems identifying which backuppiece may contain a controlfile you will have to try various backuppieces until you locate a controlfile. If you can not locate one you’ll have to call Oracle Support for help and you’ll need to provide them with the following information: Current Schema of the database, files that were backed up, what time they were backed up and names of the backup pieces containing the Database’s files.
Mount the Instance – once the controlfile has been located and extracted you can continue and the next step is to mount the instance so jump down to Step 3.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is ON – Oracle 9i+ where the parameter CONTROLFILE AUTOBACKUP is set to ON (default). The autobackup controlfile feature is there to help ease the pain of recovery when the RMAN repository is gone. Controlfile autobackup formats have to include the %F which makes them easy to find and identify. %F equates to – “c-t-yyyymmdd-” (I’m having issues display the proper syntax due to my inability to figure out how to block quote a block of text so I’ll fix this later when I figure out how to do that) . If the controlfile backups are written to disk, the default location is /dbs (unix) and /database (windows) so that is the locations to look for the autobackup files first. If you don’t find them they were written to a non-default directory and if you don’t know where the non-default directory is you’ll have to do a search of ‘find’ in unix to locate them. If you find the file you can use the ‘SET CONTROLFILE AUTOBACKUP FORMAT’ in the script below to force RMAN to use the correct location for the autobackup file. For autobackups written to tape, no further action is required other than to allocate a channel – check the rman logs to find out what the media manager environment variables if any were specified for the channel and make sure you set up a similar if the same variable configuration.
Startup the Instance in NOMOUNT mode as follows (you can’t use SQL*Plus for this so don’t try):$ rman target /
RMAN> startup nomount;RMAN> set dbid=”dbid#”;RMAN> run {set controlfile autobackup format for device type disk to ‘path/%F’;restore spfile from autobackup;restore controlfile from autobackup;startup force mount;}
Notes: You get the dbid from the name of the controlfile autobackup filename or the rman backup logs. If you can’t find or identify the DBID you’re stuck. The ’set controlfile autobackup format’ is only necessary if the controlfile autobackup is in a non-default location - set ‘path’ accordingly. If you are using tapes for the restore: set controlfile autobackup format for device type sbt to ‘%F’;. ’startup FORCE mount’ is required because the instance was originally started without an spfile and if you try to mount without restarting it will fail.
3. Query the Backup History in the Restored Controlfile – Oracle 8i,9i,10g and 11g – after you’ve restored the controlfile and mounted the instance you now need to confirm that the controlfile actually contains the metadata for the database and the archivelog backups that will be required to restore and recover the database. You can use a command similiar to the following for this purpose:
RMAN> list backup;RMAN> list backup of database completed after ‘date’;RMAN> list backup of database completed between ’start date’ and ‘end date’;
The date must be in the appropriate NLS format (NLS_DATE_FORMAT).
Once you’ve found the database backup(s) you need then get the checkpoint scn of the backup (you’ll need this for the restore). You can see the SCN in the output of the above list commands. Then check that the archivelogs needed for the recovery have been backed up and recorded:
RMAN> list backup of archivelog from scn=xxxx;
Now if all the required metadata required for the restore and recovery are present you can continue on with the restore and recovery steps. You need to pick a archive log sequence number to specify for the restore and recovery process.
RMAN> run {set until sequence = xx thread 1;restore database;recover database;alter database open resetlogs;}
3.1 Restored CONTROLFILE does NOT CONTAIN THE DATABASE BACKUP METADATA – Oracle Releases 8i and 9i – located a later (newer) controlfile and try again. Otherwise, phone Oracle Support Services for assistance.
Once the datafiles and archivelogs have been extracted the recovery can be start via RMAN to recover up to (and including the log sequence xx).
RMAN> run {set until sequence xx thread 1;recover database;alter database open resetlogs;}
Oracle Release 10g+
Catalog the required backuppiece(s) into the restored backuppiece ‘X’;
For Disk Backup Pieces:
RMAN> catalog backuppiece ‘path\X’;
Where ‘X’ is the name of the backuppiece.
All of the information regarding the backuppiece content are in the backuppiece header and once you catalog the backuppiece you can then query the contents (list backupset xx;) and proceed with the restore and recovery of the database as normal.