René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Control files [Oracle]

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:
  • Database information (RESETLOGS SCN and their time stamp)
  • Archive log history
  • Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
  • Redo threads (current online redo log)
  • Database's creation date
  • database name
  • current archive log mode
  • Log records (sequence numbers, SCN range in each log)
  • RMAN catalog
  • Database block corruption information
  • Database ID, which is unique to each DB
The location of the control files is specified through the control_files init param.

Backing up Controlfiles

When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.
Because control files keep track of datafiles the control file should be backed up whenever a change is made to the database (alter database).
The command to back the control file up is: alter database backup controlfile to 'filename'.

Recovering database when controlfile is lost

If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated (with create controlfile whose syntax is a bit awkyard.
In order to recover from a lost spfile, the DBID is required.

Size of control files

The size of the control files is governed by the following parameters:

Sections

The controlfile contains the following sections:
  • ARCHIVED LOG (reusable)
  • BACKUP CORRUPTION (reusable)
  • BACKUP DATAFILE (reusable)
  • BACKUP PIECE (reusable)
  • BACKUP REDOLOG (reusable)
  • BACKUP SET (reusable)
  • BACKUP SPFILE
  • CKPT PROGRESS
  • COPY CORRUPTION (reusable)
  • DATABASE
  • DATAFILE
  • DATAFILE COPY (reusable)
  • DATAFILE HISTORY
  • DATABASE INCARNATION
  • DELETED OBJECT (reusable)
  • FILENAME
  • FLASHBACK LOG
  • INSTANCE SPACE RESERVATION
  • LOG HISTORY (reusable)
  • MTTR
  • OFFLINE RANGE (reusable)
  • RECOVERY DESTINATION
  • REMOVABLE RECOVERY FILES
  • RMAN STATUS
  • RMAN CONFIGURATION
  • REDO THREAD
  • REDO LOG
  • TABLESPACE
  • TEMPORARY FILENAME
  • THREAD INSTANCE NAME MAPPING
  • PROXY COPY
The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.
These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.

Misc

SQL*Loader's behaviour is specified with the SQL*Loader control file. Although they sound similar, they have nothing in common.
ASM simplifies the optimal layout of controlfiles.