Backup Options   «Prev  Next»

Lesson 5 Reconstructing a lost or damaged control file
ObjectiveDescribe how to recover a control file.

Reconstructing Lost | Damaged (Control file)

Let us say a media failure destroyed the control file of a database. As long as you have the archived logs and online redo logs intact, you can still recover your database by recreating the control file, opening the database with the new control file and then performing a recovery. Depending on the status of your database configuration and the availability of a control file backup, you can recreate the control file in the ways listed in the following table:
Mirrored control fileBackup typeReconstruction steps
One file intactNot needed
  1. Restore the control file by copying the mirrored one and open the database
NoYou have a control file backup created via ALTER DATABASE BACKUP CONTROLFILE TO TRACE; command.
  1. Create a file containing the CREATE CONTROLFILE statement from the trace file
  2. Run the file created in step 1
NoYou have a control file backup created via ALTER DATABASE BACKUP CONTROLFILE TO <filename>; command.
  1. Restore the control file backup and mount the database
  2. Issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS; command to create the trace file
  3. Repeat all the steps for the previous option
NoNo control file backup
  1. Start the database in NOMOUNT mode
  2. Manually create the control file via CREATE CONTROLFILE command (Composing this statement can beproblematic because you will need complete and correct information on your database structure)
  3. Mount the database
  4. Perform a recovery


Note: As a DBA, you should never experience a total loss of a control file if you protect your database with proper procedures. You can multiplex the control files and spread them out on different disks or even on different machines. You should also routinely back up the control file.
The following table outlines several points you need to pay attention to when you use the above options to recover the control file.
STARTING STATUS ACTIONS TO TAKE POINTS TO CONSIDER
Need to create a file containing the CREATE CONTROLFILE statement from the trace file Eliminate the introductory part of the trace file Save it with an SQL extension Note that all commands (such as start the database in the proper mode and perform recovery) are already included in the trace file.
Control file backup made before last structural change Edit the trace file before using it to create a new control file If a new datafile was added to the database but it was not included in the control file backup, add the datafile to the DATAFILE clause of the CREATE CONTROLFILE command.
Manually recreate the control file Use the alert log to get information about database structure If there are any discrepancies, you will get errors every time you use this control file to start your database.

Note: If you manually recreate the control file, you have to be very familiar with your database structure. You can use the alert log to help you find the information about the database structure but this could by very time-consuming!
View the Code below to see an example of a trace file. Please note that only the highlighted lines are needed to create the new control file.
View the Code below to see an example of manually creating a control file

Dump file C:\orant\RDBMS80\trace\ORA00209.TRC
Wed Feb 16 11:16:38 2000
ORACLE V8.0.5.0.0 - Production vsnsta=0
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.1.0 - Production
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 11
pid: d1

*** SESSION ID:(9.52) 2000.02.16.11.16.38.114
*** 2000.02.16.11.16.38.114
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1630
LOGFILE
GROUP 1 'C:\ORANT\DATABASE\LOG4ORCL.ORA'  SIZE 1M,
GROUP 2 'C:\ORANT\DATABASE\LOG3ORCL.ORA'  SIZE 1M,
GROUP 3 'C:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 1M,
GROUP 4 'C:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 1M
DATAFILE
'C:\ORANT\DATABASE\SYS1ORCL.ORA',
'C:\ORANT\DATABASE\USR1ORCL.ORA',
'C:\ORANT\DATABASE\RBS1ORCL.ORA',
'C:\ORANT\DATABASE\TMP1ORCL.ORA',
'C:\ORANT\DATABASE\STUDENT.ORA',
'C:\ORANT\DATABASE\USER_TEMP.ORA',
'C:\ORANT\DATABASE\OEM.ORA'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;


The following simulation walks you through the steps of recovering a control file. In this simulation, suppose you have created a file containing the
CREATE CONTROLFILE
statement from the trace file and saved it with the name CR_CTL_ORCL.ORA.
  1. After a media failure, you try to start the database. Type |||(S1)STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA|||(S0) at the prompt and press Enter.
  2. STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA
  3. Please check your entry and try again.
  4. The error message shows that something went wrong with the control file. Suppose that you checked the alert log and found out that the control file was totally corrupted. In addition, you do not have a mirrored copy. However, you do have a backup that you made two days ago via ALTER DATABASE BACKUP CONTROLFILE TO TRACE;. You edited the trace file and saved it with the name CR_CTL_ORCL.SQL. Since your database is mounted, you have to shut it down before you can run the file.
    Type SHUTDOWN IMMEDIATE at the prompt and press Enter.
  5. To recreate the control file, type
    START C:\ORANT\DATABASE\CR_CTL_ORCL.SQL
    
    at the prompt and press Enter.
  6. START C:\ORANT\DATABASE\CR_CTL_ORCL.SQL
  7. If you do not receive an error message, you have successfully recreated the control file and started the database with the new control file. This is the end of the Simulation. Click the Exit button.

Reconstructing Control File
View the Code below to look at the content of the CR_CTL_ORCL.SQL file.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1630
LOGFILE
GROUP 1 'C:\ORANT\DATABASE\LOG4ORCL.ORA'  SIZE 1M,
GROUP 2 'C:\ORANT\DATABASE\LOG3ORCL.ORA'  SIZE 1M,
GROUP 3 'C:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 1M,
GROUP 4 'C:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 1M
DATAFILE
'C:\ORANT\DATABASE\SYS1ORCL.ORA',
'C:\ORANT\DATABASE\USR1ORCL.ORA',
'C:\ORANT\DATABASE\RBS1ORCL.ORA',
'C:\ORANT\DATABASE\TMP1ORCL.ORA',
'C:\ORANT\DATABASE\STUDENT.ORA',
'C:\ORANT\DATABASE\USER_TEMP.ORA',
'C:\ORANT\DATABASE\OEM.ORA'
;

RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;

The next lesson describes how to recover a read-only tablespace.