An Oracle Database Control file contains crucial information such as:

• The database name
• The timestamp of database creation
• The names and locations of associated datafiles and redo log files
• Tablespace information
• Datafile offline ranges
• The log history
• Archived log information
• Backup set and backup piece information
• Backup datafile and redo log information
• Datafile copy information
• The current log sequence number
• Checkpoint information

 

You can’t start the database to “MOUNT” state unless a control file exists.

Parameter CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused, the default value is 7.

An Auto-Backup control file can be set as follows:

RMAN> SHOW CONTROLFILE AUTOBACKUP;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Autobackup will be triggered whenever a structural changes takes place such as adding new data file, dropping data file, renaming data file. Of course, a control backup will be included with full database backup with datafile 1 that belongs to system tablespace.

 

How to Restore database control file when it’s damaged or removed by mistake:

 

METHOD NO.1: re-creating the control file:

1. Execute the below SQL statement to have a copy definition of the control file

SQL> alter database backup controlfile to trace as ‘C:\app\emodb\diag\rdbms\hero\hero\trace\cntl_aug5_backup.trc’;

2. Gather information:

 

SQL>  spool gather_info

SQL>  SELECT MEMBER FROM V$LOGFILE;

SQL>  SELECT NAME FROM V$DATAFILE;

SQL>  SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files’;

SQL>  spool off

 

3. Shutdown the database: SQL> shutdown immediate;

 

4.  SQL>STARTUP NOMOUNT

 

5. Create a new control file for the database using the CREATE CONTROLFILE statement, When creating a new control file (from trace file), specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs. You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

 

 

6. If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause you can recover the database with complete, closed database recovery.

 

7. If you did not perform recovery, or you performed complete, closed database recovery in step open the database normally.

SQL> ALTER DATABASE OPEN;

 

 

METHOD NO.2 : RMAN

 

RMAN> list backup of controlfile;

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

 

RMAN> restore controlfile from autobackup;

OR

RMAN> restore controlfile;

OR

 

Restore the controlfile from a specific autobackup file to a temporary disk location the replicate the temp controlfile to the respective locations and names given in control_files.

 RMAN> restore controlfile from ‘/tmp/c-1140771490-2008050203’ to ‘/tmp/control.tmp’;

 RMAN> replicate controlfile from ‘/tmp/control.tmp’;