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

guocun09發表於2018-02-07
Master Note: Overview of Database ControlFiles (文件 ID 1493674.1)

In this Document

Purpose
Scope
Details
  Concepts
  Controlfile Contents
  Controlfile Structure
  Circular reuse records
  Noncircular reuse records
  ControlFile Parameters
  CONTROL_FILES
  CONTROL_FILE_RECORD_KEEP_TIME
  Controlfile Creation
  CREATE CONTROLFILE
  Troubleshooting after creating Controlfiles
  Checking for Missing or Extra Files
  Handling Errors during CREATE CONTROLFILE
  ControlFile Expansion
  Controlfile Enqueue
  Controlfile Data Dictionary Views
  Controlfile wait Events
  Control file parallel write
  Control file sequential read
  Control file single write
  Controlfile Backups
  RMAN CONTROLFILE AUTOBACKUP
  BACKUP CONTROLFILE Clause
  Guidelines for Controlfiles
  Provide Filenames for the Control Files
  Multiplex Control Files on Different Disks
  Back Up Control Files
  Dropping Controlfiles
  Additional Resources
   Known Issues and Resolutions
References

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.

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 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.

Oracle? Database Concepts 10g Release 1 (10.1)
Part Number B10743-01

http://docs.oracle.com/cd/B14117_01/server.101/b10743/physical.htm#i10135

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.

Oracle? Database Concepts 11g Release 2 (11.2)
Part Number E25789-01

http://docs.oracle.com/cd/E11882_01/server.112/e25789/physical.htm#i10135

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 Oracle Database Utilities 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)

CREATE CONTROLFILE
     SET DATABASE prod
     LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
                                '/u01/oracle/prod/redo01_02.log'),
                 GROUP 2 ('/u01/oracle/prod/redo02_01.log',
                                '/u01/oracle/prod/redo02_02.log'),
                 GROUP 3 ('/u01/oracle/prod/redo03_01.log',
                                '/u01/oracle/prod/redo03_02.log')
     RESETLOGS
     DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
                   '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
                   '/u01/oracle/prod/users01.dbs' SIZE 5M,
                   '/u01/oracle/prod/temp01.dbs' SIZE 5M
     MAXLOGFILES 50
     MAXLOGMEMBERS 3
     MAXLOGHISTORY 400
     MAXDATAFILES 200
     MAXINSTANCES 6
     ARCHIVELOG;

 

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 "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. Refer "Specifying FORCE LOGGING Mode".

 

Oracle? Database Administrator's Guide 11g Release 2 (11.2)
Part Number E25494-02

http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#ADMIN11290

Oracle? Database SQL Language Reference 11g Release 2 (11.2)
Part Number E26088-02

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5003.htm#SQLRF01203

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.

Oracle? Database Administrator's Guide 11g Release 2 (11.2)
Part Number E25494-02

http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#ADMIN11293

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.

Tue Apr 19 16:44:52 2011
Expanded controlfile section 11 from 28 to 109 records
Requested to grow by 81 records; added 3 blocks of records

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 , ospid ' (10.2.0.4 and later)
When the waiting process is unable to kill the holding process, then an ORA-239 error is raised.
Common causes for CF Enqueue Timeout are
  • Very slow I/O subsystem where the Control files are stored. 
  • Frequent log switching - redo logs too small or insufficient logs 
  • Async I/O issue or multiple db_writers, you can't use both of them, back out one.
  • OS / Hardware issues

Note 406191.1 Diagnosing ORA-600 [2103] Issues in a NON RAC environment
Note 779552.1 ORA-00494 Or ORA-600 [2103] During High Load After 10.2.0.4 Upgrade
Note 1272990.1 ORA-00240 Reported In Alert.Log After Startup
Note 1068799.1 Disk I/O Contention/Slow Can Lead to ORA-239 and Instance Crash (Doc ID 1068799.1)

 

Controlfile Data Dictionary Views


The following views display information about control files 

View Description
V$DATABASE Displays database information from the control file
V$CONTROLFILE Lists the names of control files
V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections
V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter

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. 

Parameter Description
files The number of control files to which the session is writing
blocks The number of blocks that the session is writing to the control file
requests The number of I/O requests which the session wants to write

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. 

Parameter  Description
file# The control file from which the session is reading
block# Block number in the control file from where the session starts to read. The block size is the physical block size of the port (usually 512 bytes, some UNIX ports have 1 or 2 Kilobytes).
blocks The number of blocks that the session is trying to 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. 

Parameter Description
file# This identifies the control file to which the session is currently writing
block# Block number in the control file where the write begins. The block size is the as the physical block size of the port (usually 512 bytes, some UNIX ports have 1 or 2 Kilobytes).
blocks The number of blocks that the session is trying to read

 

Oracle? Database Reference 11g Release 2 (11.2)
Part Number E25513-02

http://docs.oracle.com/cd/E11882_01/server.112/e25513/waitevents003.htm#autoId19

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';    

 

Oracle? Database Backup and Recovery User's Guide 11g Release 2 (11.2)
Part Number E10642-05

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#i1009642

Note 272408.1 RMAN BACKUP AUTOMATICALLY INCLUDES CONTROLFILE AND SPFILE

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;

 

Oracle? Database SQL Language Reference 11g Release 2 (11.2)
Part Number E26088-02

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1004.htm#i2078526

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

Oracle? Database Administrator's Guide 11g Release 2 (11.2)
Part Number E25494-02

http://docs.oracle.com/cd/E11882_01/server.112/e25494/control.htm#i1106212

 

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.

Additional Resources


Note 735106.1 How to Recreate a Controlfile
Note 881082.1 Unable to Restore Controlfile From RMAN Autobackup
Note 580414.1 RMAN - How to restore the controlfile using RMAN
Note 345180.1 How to duplicate a controlfile when ASM is involved
Note 329981.1 10gR2 New Feature: Eliminate Control File Re-Creation
Note 305565.1 Persistent Controlfile configurations for RMAN in 9i and 10g
Note 1056057.1 HOW TO MONITOR CONTROLFILE SECTION EXPANSION OVER TIME
Note 1086496.1 HOW DO CIRCULAR CONTROLFILE SECTIONS EXPAND? (LOG HISTORY / ARCHIVED LOG ETC)
Note 470463.1 How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies
Note 403883.1 How To Restore Controlfile From A Backupset Without A Catalog Or Autobackup
Note 406191.1 Diagnosing ORA-600 [2103] Issues in a NON RAC environment

 

 Known Issues and Resolutions


Note 429943.1 Summary Of Bugs On ORA-00600 [2103] Error
Note 1084048.6 ORA-01503, ORA-01161: on Create Controlfile
Note 881082.1 Unable to Restore Controlfile From RMAN Autobackup
Note 1072417.1 Performance Degradation as a Result of 'enq: CF - contention'
Note 1058851.6 EXCESSIVE I/O CONTROLFILE PERFORMANCE PROBLEMS
Note 265599.1 ORA-600 [2130] REPORTED With RMAN RESTORE CONTROLFILE VALIDATE
Note 849071.1 10.2.0.3 Instance Crash With ORA-600 [2103] Error
Note 28045.1 ORA-600 [2103] "Timeout on 'Control file' or 'Checkpoint Progress' Enqueue"
Note 1308378.1 After Upgrade To 11.2.0.2 We Recieve Ora-00245 During Autobackup Of The Controlfile
Note 10317487.8 Bug 10317487 - RMAN controlfile backup fails with ODM error ORA-17500 or ORA-245
Note 9145541.8 Bug 9145541 - OERI[25027]/OERI[4097]/OERI[4000]/ORA-1555 in plugged datafile after CREATE CONTROLFILE in 11g
Note 342696.1 Rman-20033: Control File Sequence# Too Low Rman-20033
Note 1265103.1 RMAN DUPLICATE fails with ORA-600 [KGEADE_IS_0] while creating controlfile

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1062278/,如需轉載,請註明出處,否則將追究法律責任。

相關文章