Control file recovery
From Oracle FAQ
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.
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];