Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 19 May 2012

Managing Database Control Files?

The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open.
Control File Contents
The control file gives Oracle a place to store important information about the physical state of the database. Control file information can be modified only by Oracle; no database administrator or user can edit a control file. A control file contains information such as:
Database name, Timestamp of database creation, Names and locations of associated datafiles and redo log files, Datafile offline ranges, Archived log information, Backup set and backup piece information, Datafile copy information, Current log sequence number, Checkpoint information etc. To check the description:
Main function of the control file
Perhaps the most significant use of the control file is to keep track of all the other files that make up the database. When you start an instance and open a database, Oracle reads the initialization file to find the name and location of the control file. Then it reads the control file to find the names and locations of all the datafiles and redo log files. Finally it opens all those files, making the database available for you to use.
Importance of the control file
The control file is so critical to the proper operation of an Oracle database that Oracle encourages you to keep at least three copies of the control file at all times. These should be on separate disks, and preferably separate controllers. 
The Oracle database software always writes the same information to all control files, thus keeping them in sync with one another.
The control file of an Oracle Database is created at the same time as the database. By default, at least two copy of the control file is created during database creation. On some operating systems the default is to create more than two copies. You can also create control files later. Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:
·   Oracle can identify the datafiles and redo log files to open during database startup
·   Oracle can identify files that are required or available in case database recovery is necessary
Therefore, if you make a change to the physical structure of your database (using ALTER DATABASE statements), then you should immediately make a backup of your control file.
Control files also record information about checkpoints. Every three seconds, the checkpoint process (CKPT) records information in the control file about the checkpoint position in the redo log. This information is used during database recovery to tell Oracle that all redo entries recorded before this point in the redo log group are not necessary for database recovery; they were already written to the datafiles.
Multiplex Control Files on Different Disks
Every Oracle Database should have at least two control files, each stored on a different physical disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.
The behavior of multiplexed control files is this:
·   The database writes to all filenames listed for the initialization parameter CONTROL_FILES in the database initialization parameter file.
·   The database reads only the first file listed in the CONTROL_FILES parameter during database operation.
·   If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
Note: Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disks.
Back Up Control Files
It is very important that you back up your control files. Each and every time you change the physical structure of your database. Such structural changes include:
·   Adding, dropping, or renaming datafiles
·   Adding or dropping a tablespace, or altering the read/write state of the tablespace
·   Adding or dropping redo log files or groups
Back up the control file to a binary file (duplicate of existing control file):
ALTER DATABASE BACKUP CONTROLFILE TO 'C:\oracle\backup\control.bkp';
Produce SQL statements that can later be used to re-create your control file:
This command writes a SQL script to a trace file where it can be captured and edited to reproduce the control file. View the alert log to determine the name and location of the trace file.
Creating Control Files
There are three ways to Create Control Files:
1.      Creating Initial Control Files
2.      Creating Additional Copies, Renaming, and Relocating Control Files
3.      Creating New Control Files
For more information on how to create controlfile follow this link:
To add a multiplexed copy of the current control file or to rename a control file:
1.      Shut down the database.
2.      Copy an existing control file to a new location, using operating system commands.
3.      Edit the CONTROL_FILES parameter in pfile to add the new control file name, or to change the existing control filename.
4.      Restart the database.
When to Create New Control Files
It is necessary for you to create new control files in the following situations:
·   All control files for the database have been permanently damaged and you do not have a control file backup.
·   You want to change the database name such as you would change a database name if it conflicted with another database name in a distributed environment.
·   When the Compatibility level is set the value that is earlier than 10.2.0 (Current database version) then you must have to create new control file before using Alter database command to change the parameters such as MAXLOGFILES, MAXLOG MEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary.
Troubleshooting After Creating Control Files: Checking for Missing or Extra Files
After creating a new control file and using it to open the database, check the alert.log to see if the database has detected inconsistencies between the data dictionary and the control file, such as a datafile in the data dictionary includes that the control file does not list. If a datafile exists in the data dictionary but not in the new control file, the database creates a placeholder entry in the control file under the name MISSINGnnnn, where nnnn is the file number in decimal. MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.
If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual datafile. If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename operation to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile.
Conversely, if a datafile listed in the control file is not present in the data dictionary, then the database removes references to it from the new control file. In both cases, the database includes an explanatory message in the alert log to let you know what was found.
Handling Errors During CREATE CONTROLFILE
If Oracle Database sends you an error (usually error ORA-01173ORA-01176ORA-01177, ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE statement or included one that should not have been listed. In this case, you should restore the files you backed up in step 3 and repeat the procedure from step 4, using the correct filenames.
Recovering a Control File Using a Current Copy Control file Corruption
This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file.
1.      With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
     % cp /u03/oracle/prod/control03.ctl   /u02/oracle/prod/control02.ctl
2.      SQL> STARTUP;
Permanent Media Failure
This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media failure and that you have a multiplexed copy of the control file.
1.      With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:
     % cp /u01/oracle/prod/control01.ctl  /u04/oracle/prod/control03.ctl
2.      Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:
     CONTROL_FILES = (/u01/oracle/prod/control01.ctl, /u02/oracle/prod/control02.ctl,
3.      SQL> Startup;
Dropping Control Files
You want to drop control files from the database, for example, if the location of a control file is no longer appropriate. Remember that the database should have at least two control files at all times.
1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name.
3. Restart the database.
Note: This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary file after you have dropped the control file from the database.
Control Files Data Dictionary Views
V$controlfile, V$controlfile_record_section


Post a Comment