module7-control_file.htm; updated June 1, 2013; Some figures shown in these notes are from Oracle document D11321GC11; Reference: Oracle® Database Administrator's Guide 11g Release 2 (11.2) E25494-03
These notes explain how:
· a control file is used.
· to examine control files contents.
· to multiplex control files.
· to manage control files with an Oracle Managed Files (OMF) approach.
As you've learned from thus far in the course, a Control File is a small binary file that stores information needed to startup an Oracle database and to operate the database.
· A control file belongs to only one database.
· A control file(s) is created at the same time the database is created based on the CONTROL_FILES parameter in the PFILE.
· If all copies of the control files for a database are lost/destroyed, then database recovery must be accomplished before the database can be opened.
· An Oracle database reads only the first control file listed in the PFILE; however, it writes continuously to all of the control files (where more than one exists).
· You must never attempt to modify a control file as only the Oracle Server should modify this file.
· While control files are small, the size of the file is affected by the following CREATE DATABASE or CREATE CONTROLFILE command parameters if they have large values.
Contents of a Control File
Control files record the following information:
· Database name – recorded as specified by the initialization parameter DB_NAME or the name used in the CREATE DATABASE statement.
· Database identifier – recorded when the database is created.
· Time stamp of database creation.
· Names and locations of datafiles and online redo log files. This information is updated if a datafile or redo log is added to, renamed in, or dropped from the database.
· Tablespace information. This information is updated as tablespaces are added or dropped.
· Redo log history – recorded during log switches.
· Location and status of archived logs – recorded when archiving occurs.
· Location and status of backups – recorded by the Recovery Manager utility.
· Current log sequence number –recorded when log switches occur.
· Checkpoint information – recorded as checkpoints are made.
Multiplexing Control Files
Control files should be multiplexed – this means that more than one identical copy is kept and each copy is stored to a separate, physical disk drive – of course your Server must have multiple disk drives in order to do this. Even if only one disk drive is available, you should still multiplex the control files.
o This eliminates the need to use database recovery if a copy of a control file is destroyed in a disk crash or through accidental deletion.
o You can keep up to eight copies of control files – the Oracle Server will automatically update all control files specified in the initialization parameter file to a limit of eight.
o More than one copy of a control file can be created by specifying the location and file name in the CONTROL_FILES parameter of the PFILE when the database is created.
o During database operation, only the first control file listed in the CONTROL_FILES parameter is read, but all control files listed are written to in order to maintain consistency.
o One approach to multiplexing control files is to store a copy to every disk drive used to multiplex redo log members of redo log groups.
You can also add additional control files. When using a PFILE, this is accomplished by shutting down the database, copying an existing control file to a new file on a new disk drive, editing the CONTROL_FILES parameter of the PFILE, then restarting the database.
If you are using an SPFILE, you can use the steps specified in the figure shown here. The difference is you name the control file in the first step and create the copy in step 3.
Create New Control Files Command
A DBA will create new control files in these 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.
o For example, you would change a database name if it conflicted with another database name in a distributed environment.
o Note: You can change the database name and DBID (internal database identifier) using the DBNEWID utility. See Oracle Database Utilities for information about using this utility.
SET DATABASE USER350
LOGFILE GROUP 1 ('/u01/oradata/prod/redo01_01.log',
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
DATAFILE '/u01/student/dbockstd/oradata/USER350system01.dbf' SIZE 350M,
'/u01/student/dbockstd/oradata/USER350sysaux01.dbf' SIZE 350M,
'/u02/student/dbockstd/oradata/USER350undo01.dbf' SIZE 64M,
'/u02/student/dbockstd/oradata/USER350users01.dbf' SIZE 5M
'/u02/student/dbockstd/oradata/USER350temp01.dbf' SIZE 64M
· The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files.
· It is only issued as a command in NOMOUNT stage.
· Omitting a filename can cause loss of the data in that file, or loss of access to the entire database.
· Use caution when issuing this statement and be sure to follow the instructions in "Steps for Creating New Control Files".
· If the database had forced logging enabled before creating the new control file, and you want it to continue to be enabled, then you must specify the FORCE LOGGING clause in the CREATE CONTROLFILE statement.
Steps to use when a control file must be recreated:
1. Make a list of all datafiles and redo log files of the database.
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that comprise the SYSTEM tablespace, you might not be able to recover the database.
2. Shut down the database.
If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.
7. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.
8. Recover the database if necessary. If you are not recovering the database, skip to step 9.
If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.
If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.
· If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
ALTER DATABASE OPEN;
· If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
What if a Disk Drive Fails? Recovering a Control File
Use the following steps to recover from a disk drive failure that has one of the database’s control files located on the drive.
· Shut down the instance.
· Replace the failed drive.
· Copy a control file from one of the other disk drives to the new disk drive – here we assume that u02 is the new disk drive and control02.ctl is the damaged file.
$ cp /u01/oracle/oradata/control01.ctl /u02/oracle/oradata/control02.ctl
· Restart the instance. If the new media (disk drive) does not have the same disk drive name as the damaged disk drive or if you are creating a new copy while awaiting a replacement disk drive, then alter the CONTROL_FILES parameter in the PFILE prior to restarting the database.
· No media recovery is required.
· If you are awaiting a new disk drive, you can alter the CONTROL_FILES parameter to remove the name of the control file on the damaged disk drive – this enables you to restart the database.
Backup Control Files and Create Additional Control Files
Oracle recommends backup of control files every time the physical database structure changes including:
· Adding, dropping, or renaming datafiles.
· Adding or dropping a tablespace, or altering the read/write state of a tablespace.
· Adding or dropping redo log files or groups.
Use the ALTER DATABASE BACKUP CONTROLFILE statement to backup control files.
ALTER DATABASE BACKUP CONTROLFILE TO ‘/u02/oradata/backup/control.bkp’;
Now use an SQL statement to produce a trace file (write a SQL script to the trace file) that can be edited and used to reproduce the control file.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
To create additional control files follow the steps specified earlier for multiplexing control files.
Dropping a Control File
Control files are dropped when a location is no longer appropriate, e.g., a disk drive has been eliminated from use for a database.
1. Shut down the database.
2. Edit the init.ora file CONTROL_FILES parameter by removing the old control file name.
3. Restart the database.
Oracle Managed Files Approach
Control files are automatically created with the Oracle Managed Files (OMF) approach during database creation even if you do not specify file locations/names with the CONTROL_FILES parameter—it is preferable to specify file locations/names.
With OMF, if you wish to use the init.ora file to manage control files, you must use the filenames generated by OMF.
· The locations are specified by the DB_CREATE_ONLINE_LOG_DEST_n parameter.
· If the above parameter is not specified, then their location is defined by the DB_CREATE_FILE_DEST parameter.
Control file names generated with OMF can be found within the alertSID.log that is automatically generated by the CREATE DATABASE command and maintained by the Oracle Server.
Control File Information
Several dynamic performance views and SQL*Plus commands can be used to obtain information about control files.
· V$CONTROLFILE – gives the names and status of control files for an Oracle Instance.
· V$DATABASE – displays database information from a control file.
· V$PARAMETER – lists the status and location of all parameters.
· V$CONTROLFILE_RECORD_SECTION – lists information about the control file record sections.
· SHOW PARAMETER CONTROL_FILES command – lists the name, status, and location of control files.
The queries shown here were executed against the DBORCL database used for general instruction in our department.
CONNECT / AS SYSDBA
SELECT name, value FROM v$parameter
Name Null? Type
--------------------- -------- ----------------------------
SELECT type, record_size, records_total, records_used
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE 520 25 4
The RECORDS_TOTAL shows the number of records allocated for the section that stores information on data files.
Several dynamic performance views display information from control files including:
END OF NOTES