Recovering from All Control Files Missing

Use

You use this procedure if you have lost all copies of the control file.

The normal database activities continue until the next update of the control file. When this happens (during the next checkpoint or redo log file switch, at the latest), the database system crashes.

Prerequisites

A complete recovery of the database is possible provided one of the following conditions is fulfilled:

·        A current backup copy of the control file, that is, a copy with the current structure of the database, exists.

·        A current log of the files in the database exists, enabling you to create the control file again.

If all the control files (even the backups) are lost, you must first reconstruct the control file before you can start the recovery process. This procedure is much more complicated and not always successful.

Procedure

Recovery Using the Backed-Up Control File

In the procedure described in the following, we assume that you are able to restore the control file from your last database backup.

To update the database, the appropriate redo log files must exist.

It is helpful that the saved control file reflects the current structure of the database. The paths for the data and log files and the status of the log sequence numbers are not important, but the control file should have the exact information about the number of files and - indirectly - the number of tablespaces in the database.

Proceed as follows for recovery:

...

       1.      If the database system is still operating, shut down all instances with the following SQLPLUS command:

shutdown abort

ABORT is generally necessary because the control files are no longer available to include a checkpoint during the shutdown.

       2.      Use the ALERT and trace files to analyze the error.

Check whether other damage has occurred to the database: Find out whether all data files and redo log files are readable.

Back up the online redo log files of all instances (if they exist in readable form) so that you can repeat the recovery process if an error occurs.

       3.      Place the backup copies of the control file in the directories or on the raw devices specified in the control_files parameter in the init.ora profile.

If further files were damaged, restore the backup copies of these files. You do not need to restore undamaged files from the backup. If you have to restore data files, you will also have to restore all the offline redo log files of all instances that have been backed up since the last backup (for SAP databases, offline redo log files are usually backed up by the BRARCHIVE program) in the local backup directory (default value: <SAPDATA_HOME>/oraarch). For more information on recovery after the loss of redo log or data files, see the relevant parts of this documentation and your Oracle documentation.

       4.      Enter the following SQLPLUS commands to mount the local instance:

connect / as sysdba

startup mount

       5.      If you could not load backed up files into their original directories or had to change file name, you must update the control file.

Change path or group names with the following command:

alter database rename file '<file name>' to '<file name>';

See Updating the Control File.

       6.      If the data files of the database were set to status OFFLINE during the shutdown, change the status of the files in the control file to ONLINE.

To find OFFLINE files, search for “offline” in the ALERT file or check the v$datafile view:

select * from v$datafile

To change the status of a data file in the control file, use the following command:

alter database datafile '<file name>' online;

See Updating the Control File.

       7.      Start recovery with the following SQLPLUS command:

recover database until cancel using backup controlfile;

       8.      If you are prompted to do so, enter the full path name for the redo log files required for recovery, including the active online redo log file.

       9.      When all redo log files are processed, end the recovery process with the command cancel.

   10.      After the message recovery canceled, you can reopen the database by using one of the following SQLPLUS commands:

alter database open resetlogs;

alter database open noresetlogs;

The resetlogs option initializes the existing online redo log files.  Therefore, only use this option after an incomplete recovery. Do not use this option after a complete recovery.

The noresetlogs option causes the online redo log files to be used in their current form.  A complete recovery is required to use this option.

The database system resumes operations with the log sequence number following the number of the last current online redo log file.

   11.      Perform a complete backup of the database.

The backup is necessary to back up the control file and to guarantee a recovery of the database if further database problems occur.

For more information, see Finishing an Incomplete Recovery.

Database Recovery Using a New Control File

If you do not have a valid copy of the control file, a full recovery is still possible by reconstructing the control file. To do this, you need a current log of all the database files, for example, the BRBACKUP log.

Proceed as follows during recovery:

...

       1.      If the database is still active, shut down all instances with the following SQLPLUS command:

shutdown abort

ABORT is generally necessary because the control files are no longer available to include a checkpoint during the shutdown.

       2.      Use the ALERT and trace files to analyze the error.

Make sure no further damage has occurred in the database, and find out whether all data files and online redo log files exist in readable form.

Back up the online redo log files of all instances (if they exist in readable form) so that you can repeat the recovery process if an error occurs.

       3.      If other files were damaged, restore the backup copies of these files. You do not need to restore undamaged files from the backup. If you have to recover data files, also restore all the offline redo log files of all instances that have been backed up since the backup of these data files in the backup directory.

       4.      Enter the following SQLPLUS commands to demount the database: 

connect / as sysdba

startup nomount

       5.      Use the following command to create the control file (for detailed syntax information, please refer to your Oracle documentation):

create controlfile
database <name>
logfile '<online redo log groups>'
noresetlogs|resetlogs
maxlogfiles 10
maxlogmembers <your value>
datafile '<names of all data files>'
maxdatafiles 1022
archivelog;

Path names: The path names of the online redo log files and data files can be found in the last detail log from BRBACKUP.

Noresetlogs/Resetlogs: Only select resetlogs when an online redo log group was lost in addition to the control file. You should otherwise always use noresetlogs.

       6.      Mount the database.

alter database mount;

       7.      Start the recovery with the following command:

recover database [until cancel] [using backup controlfile];

A recovery is required whenever the control file was generated with resetlogs or when a data file was restored. Recovery is also recommended for security reasons in other cases.

You must select the option using backup controlfile when you used the resetlogs option to create the control file. If you select until cancel, you can interactively decide how many files of the existing redo log files you want to apply during the recovery. You should enter all the redo log files of all instances, including the current ones.

       8.      Use the following SQLPLUS command to start up the database:

alter database open [noresetlogs/resetlogs];

Ў        Use alter database open if you created the control file with noresetlogs and have performed no recovery or a complete recovery (without until cancel).

Ў        Use alter database open noresetlogs if you created the control file with noresetlogs and performed a complete recovery despite the use of the until cancel option.

Ў        Use alter database open resetlogs if you created the control file with resetlogs or when you performed a incomplete recovery.

       9.      After the recovery, be sure to perform a complete backup to save the newly created control file and to make sure that a recovery of the database in the event of failure is possible.