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:

Every Oracle database has a control file, which is a binary file containing information about the physical structure of the database, such as the names and locations of a database's datafiles and redo log files. Every time an instance of the database is started, its control file is used to identify the database and redo log file that must be open for the database to run properly. Each time the physical structure of the database is changed, for example a new datafile is added, the control file is automatically modified with the change. As you learned in the previous modules, a control file is also used if a database recovery becomes necessary. If you lose all the control files due to a media failure, you need to recreate them. There are, however, other situations that require you to recreate the control file:

• Change Database Name
  1. The current database name needs to be changed
  
  This would happen if the current database becomes a part of the distributed database system and the name for the current database name already exists in the system.
  1. The current settings in the control file need to be changed
  
  Once the control file is created, the value for some of the settings, like MAXDATAFILES or MAXLOGMEMBERS, cannot be changed dynamically. If you need to change these settings, you need to recreate the control file.

Mirrored control file	Backup type	Reconstruction steps
One file intact	Not needed	Restore the control file by copying the mirrored one and open the database
No	You have a control file backup created via ALTER DATABASE BACKUP CONTROLFILE TO TRACE; command.	Create a file containing the CREATE CONTROLFILE statement from the trace file Run the file created in step 1
No	You have a control file backup created via ALTER DATABASE BACKUP CONTROLFILE TO <filename>; command.	Restore the control file backup and mount the database Issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS; command to create the trace file Repeat all the steps for the previous option
No	No control file backup	Start the database in NOMOUNT mode Manually create the control file via CREATE CONTROLFILE command (Composing this statement can be problematic because you will need complete and correct information on your database structure) Mount the database 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 nameCR_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.

1. After a media failure, you try to start the database. Type
   

   STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA
   

   
   at the prompt and press Enter.
2. 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.
3. To recreate the control file, type
   

   START C:\ORANT\DATABASE\CR_CTL_ORCL.SQL
   

   at the prompt and press Enter.
4. 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 completes the Simulation.

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.