Assume you work in an ACME company as senior DBA and on your absence your back up DBA has corrupted all the control files while working with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?

@ : Home > > Interview Room

Copying a Multiplexed Control File to a Default Location

If the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. In this case, you do not have to alter the CONTROL_FILES initialization parameter setting.

To replace a damaged control file by copying a multiplexed control file:

  1. If the instance is still running, then shut it down:

    SHUTDOWN ABORT
  2. Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter:

    % cp /oracle/good_cf.f /oracle/dbs/bad_cf.f
    
    
  3. Start a new instance and mount and open the database. For example, enter:

    STARTUP

Restore control file from backup control file

  1. you have to take control file backup when database is good state. by using following command
    • SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
  2. In trace file you will see how to create control file just create SQL file using those commands
  3. Do database shutdown and startup mount state.
  4. Then create control file using SQL file and database start in open mode

VIDEO LINK

Restore control file 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 <your database id>;
restore controlfile;

Manually re-create 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];

 

Alert me