Backup Options   «Prev  Next»

Recover Lost Control File – Exercise

Objective: Practice recovering a lost Oracle control file and bringing the database back online.

This exercise uses a series of images to simulate the procedure. Examples use Windows paths; use equivalent commands on Linux/Unix.

Background / Overview

A media failure destroyed the control file copies. You previously created a physical backup control file with:

ALTER DATABASE BACKUP CONTROLFILE TO 'C:\ORANT\DATABASEx\BACKUPORCL\CTL1ORCL_f.ora';

Your task: restore that backup, mount, recover, open the database, and re-establish multiplexed control files. Assume ARCHIVELOG mode.

Instructions

Complete the steps below. This page contains no solutions; the result page shows a model walkthrough after submission.

  1. Set instance environment for the target DB (e.g., SET ORACLE_SID=ORCL on Windows).
  2. Restore the control file copy to the working location (Windows example below; adjust for your platform).
  3. Start SQL*Plus and connect as SYSDBA: sqlplus / as sysdba.
  4. Mount the database: STARTUP MOUNT;
  5. Recover the database: RECOVER DATABASE;
  6. Open with RESETLOGS: ALTER DATABASE OPEN RESETLOGS;
  7. Re-multiplex control files (at least two locations on separate disks).
  8. Optional (documentation): ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Set environment for target instance (ORACLE_SID)
1) Prepare environment (set ORACLE_SID to ORCL)

Navigate to database directory
2) Navigate to the database directory

Copy backup control file to working location 

C:\> SET ORACLE_SID=ORCL
C:\> COPY C:\ORANT\DATABASEx\BACKUPORCL\CTL1ORCL_f.ORA C:\ORANT\DATABASE\CTL1ORCL.ORA
        1 file(s) copied.

Start SQL*Plus as SYSDBA and mount database 

C:\> sqlplus / as sysdba
SQL> STARTUP MOUNT;

Recover database 

SQL> RECOVER DATABASE;
Open database with resetlogs 

SQL> ALTER DATABASE OPEN RESETLOGS;
Re-multiplex control files 

-- Example; adjust for your paths and spfile usage
SQL> ALTER SYSTEM SET control_files=
  'C:/ORANT/DATABASE/CTL1ORCL.ORA','D:/ORANT/DATABASE/CTL2ORCL.ORA' SCOPE=SPFILE;
Backup control file to trace (optional) 

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Post-recovery checks 

SQL> SELECT name FROM v$controlfile ORDER BY name;
SQL> SELECT open_mode, log_mode FROM v$database;