Recovering from One Control File Missing

Use

You use this procedure if a copy of the control file cannot be read or written to.

Database activities continue normally until the next update of the control file – that is, until the next checkpoint or redo log file switch – and the database then crashes.

In this case you do not need to perform an explicit recovery of the database. The system can perform a recovery automatically, providing a copy of the control file exists.

Procedure

You have the following two main recovery options after one of the control files is lost:

·        Replace the missing control file

Find out why the control file was lost. For example, you cannot replace the file when the disk is not available.

Check the ALERT and trace files of the database system to analyze the error. The error messages will indicate which control file is missing, and may also indicate how it was lost.

Replace the missing control file as follows:

Ў        Shut down the database and exit SQLPLUS.

Ў        Copy an existing control file to the desired storage location at operating system level (see parameter control_files in the init.ora profile).

Ў        Start SQLPLUS and start up the database.

·        Modify the database system

Check the ALERT and trace files to find out which control file was lost.

Ў        Shut down the database and exit SQLPLUS.

Ў        Delete the missing file from parameter control_files in profile init.ora (default: <ORACLE_HOME>/dbs/init<DBSID>.ora).

Ў        Start SQLPLUS and start up the database. No other actions are required for recovery.

This solution is only acceptable if you have at least two copies of the control file. This guarantees that at least one mirror copy of the file still exists, even when one control file has been deleted. The database should always have at least two control files, original and mirror. In the SAP system, the control files are mirrored in three or more directories. For more information, see Mirroring the Control File.