Saturday, September 18, 2010

How control files are important in Oracle database

Each Oracle DBA has ability to secure the database against data loss. The first thing is to protect physical structure of Oracle Database As we know the physical structure of Oracle Database mainly consist of Control Files, Redo Log files, Data files and Archived Redo Log files. So now I’m going to explain you how to protect control files which are important for database, as well as the way to restore and recover control file in case it is lost. So first I want to give small overview of Control Files:

A control file is a small binary file that records the physical structure of the database and includes:

• The database name
• Names and locations of associated datafiles and online redo log files
• The timestamp of the database creation
• The current log sequence number
• Checkpoint information

The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. So without control files we can not recover oracle database.

How to protect control files from corruption:

Because the control file is so important, Oracle allows you to maintain duplicate copies of the control file. First of all, we create new “virtual hard disk”. Then we multiplex Control Files and put the multiplexed Control file into “virtual hard disk”. Then we remove this hard disk and show how the control file is lost and get error during startup of database. Then as the control file has been multiplexed, we retrieve Control file from another hard drive that is safe and open the database.

When control file get corrupted an error occurs:


Error Description: corrupt block detected in control file.

Error Cause: A block header corruption or checksum error was detected on reading the control file.

Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP CONTROLFILE command or Restore control files from backup controlfile.

Restore control files from backup controlfile

In this case you should have created a backup of your control file using, ALTER DATABASE BACKUP CONTROL FILE TO TRACE.

You can also Restore control files 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 ???;
restore controlfile;

In case of no backup you can use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP CONTROLFILE command to recover control file.

Syntax: create controlfile database name-of-database

If we saw practical scenario: In 80% cases we resolve this problem by using above all steps but in 20% cases due to severe corruption we can not resolve this problem so in last you have one option that is use of Oracle database recovery Software. These Software are very easy to use & recover database in most of causes of corruption.

No comments:

Post a Comment