SAPDBA Recovery: All Control Files Missing

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.

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 guaranteed to be successful. Please therefore strictly observe the following note, which cannot be repeated often enough:

Follow SAP’s recommendations and back up your database after every structure change! If you do this, you always have a backup of a control file that reflects the current structure of the database.

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 essential that the saved control file always 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 must 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 SVRMGR 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 archived since the last backup (for SAP databases, offline redo log files are usually archived by the BRARCHIVE program) in the local archiving directory (default value: /saparch ). For detailed information on recovery after the loss of redo log or data files, please refer to the relevant parts of this documentation and to your ORACLE documentation.

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

connect internal

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 '' to '';

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.

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

alter database datafile '' online;

See Updating the Control File.

7. Start recovery with the following SVRMGR 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 the following SVRMGR command:

alter database open resetlogs;

or

alter database open noresetlogs;

The RESETLOGS option initializes the existing online redo log files. Therefore, only use this option after a partial recovery. If a full recovery has been performed, you should not use this option.

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. Carry out a complete backup of the database.

The backup is necessary to back up the control file and to guarantee a full recovery of the database if further database problems occur. See also: Actions after a Partial Recovery.

DB 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. Caution: If a structural change to the database has been carried out after this backup, it may be impossible to perform a recovery (e.g. if no backup of the new or changed data files is available).

Proceed as follows during recovery:

1. If the database is still active, shut down all instances with the following SVRMGR 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 archived since the backup of these data files in the archiving directory.
4. Enter the following SVRMGR commands to demount the database:

connect internal

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
logfile ''
noresetlogs|resetlogs
maxlogfiles 10
maxlogmembers
datafile ''
maxdatafiles 254
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 (a recovery is required whenever the control file was generated with the RESETLOGS object or when a data file was restored. Recovery is recommended for security reasons in other cases, as well.):

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

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 read during the recovery. You should enter all the redo log files of all instances, including the current ones.

8. Use the following SVRMGR 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 full recovery (without until cancel ).

– Use alter database open noresetlogs if you created the control file with NORESETLOGS and performed a full 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 partial recovery.

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