Master Note: Overview of Database ControlFiles (文件 ID 1493674.1)

APPLIES TO:

PURPOSE

The purpose of this document is to provide an overview of the Database control file concepts.

SCOPE

 The details in this document initially applies to a non-RAC and non-dataguard Database setup.

DETAILS

Concepts

The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continually by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly. Each control file is associated with only one Oracle database.

The control file serves the following purposes:

• It contains information about data files, online redo log files, and other files that are required to open the database.
• The control file tracks structural changes to the database. For example, when an administrator adds, renames, or drops a data file or online redo log file, the database updates the control file to reflect this change.
• It contains metadata that must be accessible when the database is not open.

Controlfile Contents

A Control file contains information such as

• The database name
• The timestamp of database creation
• The names and locations of associated datafiles and redo log files
• Tablespace information
• Datafile offline ranges
• The log history
• Archived log information
• Backup set and backup piece information
• Backup datafile and redo log information
• Datafile copy information
• The current log sequence number
• Checkpoint information

The database name and timestamp originate from the database creation. The database name is taken from either the name specified by the DB_NAME initialization parameter or the name used in the CREATE DATABASE statement.

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.

Controlfile Structure

Information about the database is stored in different sections of the control file. Each section is a set of records about an aspect of the database. For example, one section in the control file tracks data files and contains a set of records, one for each data file. Each section is stored in multiple logical control file blocks. Records can span blocks within a section.

The control file contains the following types of records:

Circular reuse records

These records contain noncritical information that is eligible to be overwritten if needed. When all available record slots are full, the database either expands the control file to make room for a new record or overwrites the oldest record. Examples include records about:

      LOG HISTORY
      OFFLINE RANGE
      ARCHIVED LOG
      BACKUP SET
      BACKUP PIECE
      BACKUP DATAFILE
      BACKUP REDOLOG
      DATAFILE COPY
      BACKUP CORRUPTION
      COPY CORRUPTION
      DELETED OBJECT
      PROXY COPY

Noncircular reuse records

These records contain critical information that does not change often and cannot be overwritten. Examples of information include tablespaces, data files, online redo log files, and redo threads. Oracle Database never reuses these records unless the corresponding object is dropped from the tablespace. Examples of non-circular controlfile sections (the ones that can only expand)

      DATABASE (info)
      CKPT PROGRESS (Checkpoint progress)
      REDO THREAD, REDO LOG (Logfile)
      DATAFILE (Database File)
      FILENAME (Datafile Name)
      TABLESPACE
      TEMPORARY FILENAME
      RMAN CONFIGURATION

Reading and writing the control file blocks is different from reading and writing data blocks. For the control file, Oracle Database reads and writes directly from the disk to the program global area (PGA). Each process allocates a certain amount of its PGA memory for control file blocks.

ControlFile Parameters

CONTROL_FILES

This parameter is specified in pfile/spfile. This parameter is used to specify the location of controlfiles. CONTROL_FILES specifies one or more names of control files, separated by commas. A instance uses this parameter to locate the controlfiles for the database startup.

CONTROL_FILE_RECORD_KEEP_TIME

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

Note:

• It is not mandatory that all the records will be reused after days specified for CONTROL_FILE_RECORD_KEEP_TIME. It is the MINIMUM number of days that the details are retained.
• This parameter applies only to records in the control file that are circularly reusable (such as archive log records and various backup records). It does not apply to records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.

Controlfile Creation

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. For example, you would change a database name if it conflicted with another database name in a distributed environment.
• You want to change the parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES, when the compatibility is earlier than 10.2.0. 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, to accommodate the new configuration information.

Note:

You can change the database name and DBID (internal database identifier) using the DBNEWID utility. See  for information about using this utility.

You can also create a backup of the Create Controlfile command to a (trace) file using "ALTER DATABASE BACKUP CONTROLFILE TO 'FILENAME' / TRACE".

CREATE CONTROLFILE

To create a control file,

• You must have the SYSDBA system privilege.
• The database should be in Nomount Stage.

You can create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the prod database (a database that formerly used a different database name)

Cautions:

• The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files. 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 "".
• 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. Refer "".

Troubleshooting after creating Controlfiles

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 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-01173, ORA-01176, ORA-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 missing files and recreate the controlfile.

ControlFile Expansion

ControlFile expansion can occur in both circular and non circular sections.

Non-circular reuse (e.g. datafile records).

If there are no empty slots for a datafile, and you add a new datafile to the database, in oracle 8, the datafile record section will expand to allow the new file to be recorded in the controlfile. Non-circular reuse records cannot be overwritten until the object it describes is removed from the database. 

Circular reuse (e.g. archivelog records)

Circular reuse records can be overwritten. Typically, the oldest record will be overwritten whenever there is a need to make room for a new entry, e.g. when a new archivelog is generated and there is no more space in the archivelog section of the controlfile, the oldest archivelog entry will be overwritten by the new archivelog entry. When the CONTROL_FILE_RECORD_KEEP_TIME is set to 0, then the Circular reuse sections cannot expand and are only reused.

The size of the database control file growth may depend on the number of:

• Backups that you perform
• Archived redo logs that the database generates
• Number of Days that this information is stored in the control file 

and many other factors each dependent on corresponding controlfile section.

The Database alert log may contain the entries similar to this.

The above alert log message can be seen when we the archivelog section (section 11) in controlfile expands.This section could expand at times when there is too heavy log switching in the database.

RMAN> CROSSCHECK archivelog all;
RMAN> LIST EXPIRED archivelog all;
RMAN> DELETE EXPIRED archivelog all;

Controlfile Enqueue

Controlfile enqueue is a critical resource that is obtained by a process which performs a controlfile transaction. An update to the controlfile is termed as controlfile transaction.

Few situations when CF Enqueue is acquired are 

• Redo log Switch
• Redo log Archival
• BEGIN / END BACKUP
• Checkpoint
• Performing Crash Recovery

When any process holds the CF enqueue for long time, the other processes which need to perform the Controlfile transaction will be waiting to acquire the enqueue. Holding the enqueue for very long time can lead to database hang. Hence there is a timeout set for holding the controlfile enqueue. The timeout is 900 secs (15 min). If the process exceeds this timeout, then the holder process is killed by killing the session of holding process. The waiting process normally kills the holding process which exceeds the timeout. Then the error is logged in the alert log.

• ORA-00600: internal error code, arguments: [2103]   (10.2.0.3 and Earlier)
• ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst <inst id>, ospid <os pid>' (10.2.0.4 and later)

Controlfile Data Dictionary Views

The following views display information about control files 

12c Related:

In 12c due to the multitenant architecture, additional column CON_ID are added to the above views, which contains any of the below values.

0: This shows data related to entire CDB or related to entire database incase of non-CDB. 
1: This value is for rows which contains data related to only the root
n: Where n is the applicable container ID for the rows containing data

Controlfile wait Events

Control file parallel write

This event occurs while the session is writing physical blocks to all control files. This happens when:

• The session starts a control file transaction (to make sure that the control files are up to date in case the session crashes before committing the control file transaction)

• The session commits a transaction to a control file

• Changing a generic entry in the control file, the new value is being written to all control files

Wait Time: The wait time is the time it takes to finish all writes to all control files. 

Control file sequential read

Reading from the control file. This happens in many cases. For example, while:

• Making a backup of the control files

• Sharing information (between instances) from the control file

• Reading other blocks from the control files

• Reading the header block

Wait Time: The wait time is the elapsed time of the read. 

Control file single write

This wait is signaled while the control file's shared information is written to disk. This is an atomic operation protected by an enqueue (CF), so that only one session at a time can write to the entire database.

Wait Time: The wait time is the elapsed time of the write. 

Controlfile Backups

RMAN CONTROLFILE AUTOBACKUP

We can back up the control file when the database is mounted or open. RMAN uses a snapshot control file to ensure a read-consistent version. If the CONFIGURE CONTROLFILE AUTOBACKUP command is set to ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.

RMAN> configure controlfile autobackup on;

If the database is started with a spfile, a CONTROLFILE AUTOBACKUP will also include the spfile. When CONTROLFILE AUTOBACKUP is not enabled the controlfile and spfile (if the database was started with an spfile) are included automatically when datafile 1 is backed up. 

RMAN> backup datafile 1;

 In the case of a backup of other datafiles, like datafile 2, the controlfile will not be included. The controlfile and spfile can be explicitly specified in the BACKUP-clause.  

RMAN> backup datafile 2 current controlfile spfile;

To make a manual backup, you can either specify INCLUDE CURRENT CONTROLFILE when backing up other files or specify BACKUP CURRENT CONTROLFILE. 

RMAN> BACKUP DEVICE TYPE sbt TABLESPACE users INCLUDE CURRENT CONTROLFILE;

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';    

BACKUP CONTROLFILE Clause

We can use the BACKUP CONTROLFILE clause to back up the current control file. The database must be open or mounted when you specify this clause. 

TO 'filename' Use this clause to specify a binary backup of the control file. You must fully specify the filename using the conventions for your operating system. If the specified file already exists, then you must specify REUSE. In an Oracle RAC environment, filename must be on shared storage.

A binary backup contains information that is not captured if you specify TO TRACE, such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). If the COMPATIBLE initialization parameter is 10.2 or higher, binary control file backups include temp file entries.

ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

TO TRACE Specify TO TRACE if you want Oracle Database to write SQL statements to a trace (text) file rather than making a physical backup of the control file. The trace files are stored in a subdirectory determined by the DIAGNOSTIC_DEST initialization parameter. You can find the name and location of the trace file to which the CREATE CONTROLFILE statements were written by looking in the alert log.

You can use SQL statements written to the trace file to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file. If you issue an ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement while block change tracking is enabled, then the resulting script will contain a command to re-enable block change tracking.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Guidelines for Controlfiles

Provide Filenames for the Control Files

You specify control file names using the CONTROL_FILES initialization parameter in the database initialization parameter file (see "Creating Initial Control Files"). The instance recognizes and opens all the listed files during startup, and the instance writes to and maintains all listed control files during database operation.

In case you do not specify files for CONTROL_FILES before database creation:

• If you are not using Oracle Managed Files, then the database creates a control file and uses a default filename. The default name is operating system specific.
• If you are using Oracle Managed Files, then the initialization parameters you set to enable that feature determine the name and location of the control files.
• If you are using Oracle Automatic Storage Management (Oracle ASM), you can place incomplete Oracle ASM filenames in the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters. Oracle ASM then automatically creates control files in the appropriate places.

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.

One way to multiplex control files is to store a control file copy on every disk drive that stores members of redo log groups, if the redo log is multiplexed. By storing control files in these locations, you minimize the risk that all control files and all groups of the redo log will be lost in a single disk failure.

Back Up Control Files

It is very important that you back up your control files. This is true initially, 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

Dropping Controlfiles

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.

• Shut down the database.
• Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name.
• 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.