Control file information

From MyWiki
Jump to: navigation, search

V$CONTROLFILE - Lists the name and status of all control files associated with the instance.
V$PARAMETER - Lists status and location of all parameters.
V$CONTROLFILE_RECORD_SECTION - Provides information about the control file record sections.

> show parameters control_file

While control files are small, the size of the file can be significantly influenced by the following CREATE DATABASE or CREATE CONTROLFILE command parameters if they have large values.

  • MAXLOGFILES
  • MAXLOGMEMBERS
  • MAXLOGHISTORY
  • MAXDATAFILES
  • MAXINSTANCES

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.

Backup Control Files and Creating 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 CONTROL file statement to backup control files.

Example: ALTER DATABASE BACKUP CONTROLFILE TO ‘/a02/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;