Control file recovery

Described below are the different control file recovery scenarios:

One control file lost

Assuming you have mirrored your control files i.e. using two or more control files:

• If database is up and running, quickly copy the good control file over the bad control file.
• alter system checkpoint;
• If database went down when you accidentally dropped the control file, copy the good copy over the bad one and restart.

Lost all control files

If all control files are lost (or you are only using one control file):

Restore from backup controlfile

In this case you should have created a backup of your control file using, ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

• if database is up and running, shutdown abort
• startup mount
• recover database from commands in controlfile trace
• take a backup

Restore from RMAN backup

Oracle automatically backs up the control file when the DB is backed up. To restore, issue the following RMAN commands.

set dbid ???;
restore controlfile;

Manually recreate the control file

Issue the CREATE CONTROL FILE statement against the DB. Lookup the syntax in the SQL reference guide. Most of the info required to rebuild the control file should be in the alert.log.

Example:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "orcl"
NORESETLOGS [archivelog/noarchivelog]
MAXLOGFILES 5 
MAXLOGMEMBERS 3 
MAXDATAFILES 10 
MAXINSTANCES 1 
MAXLOGHISTORY 113
LOGFILE 
GROUP 1 'D:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 10M,
GROUP 2 'D:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 10M,
GROUP 3 'D:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 10M
DATAFILE 
'D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' SIZE xxx, 
'D:\ORACLE\ORADATA\ORCL\USERS01.DBF'  SIZE xxx, 
...
CHARACTER SET [characater_set];