my notes...

• Control file is a small binary file defining current state of database & maintaining its integrity.

• Linked to a single database, it is required at the database startup & during operation. It is continuously updated during database use. If all copies or control files are lost then the database must be recovered before it can be opened.

• Sized initially during Create Database. The commands in CREATE DATABASE/CREATE CONTROLFILE commands effecting its size are:

MAXLOGFILES	MAXDATAFILES
MAXLOGMEMBERS	MAXINSTANCES
MAXLOGHISTORY

Control files contains the following :

Database name & identifier	Name is taken either from init.ora DB_NAME parameter or name used in CREATE DATABASE command. Identifier is recorded when the database is created.
Time stamp of database creation	recorded at database creation.
Tablespaces names	updated as tablespaces are added or dropped.
Names & locations of datafiles & redo log files	updated as these are added, renamed or dropped from the database.
Current redo log file sequence number	recorded during log switch
Checkpoint information	recorded at checkpoints.
Begin & end of undo segments
Redo log archive information	location & status recorded when archiving occurs.
Backup information	Location & status of backup recorded by Recovery Manager utility.

Sections of a Control file:

Reusable	Stores Recovery manager information such as backup datafile & back redolog file names. These are used in a circular manner & can only be reused by Recovery Man.
Not Reusable

A control file can be multiplexed upto 8 times.

Steps to multiplexing using SPFILE:

• SQL :> ALTER SYSTEM SET control_files='%ORACLE_HOME%\ORADATA\u01\ctrl01',

• '%ORACLE_HOME%\ORADATA\u01\ctrl02' SCOPE=SPFILE.

  • Note : Scope clause has three possible settings : spfile, memory, both.

• Sql :> SHUTDOWN IMMEDIATE

• C:\> COPY %ORACLE_HOME%\ORADATA\u01\ctrl01.ctl %ORACLE_HOME%\ORADATA\u01\ctrl02.ctl

• Sql :> STARTUP

OMF managed control files:

• If CONTROL_FILES parameter is not specified, then Oracle creates OMF (oracle managed files) control files. If OMF created CONTROL FILES are used then do the folowing after database creation:
  

• If pfile is being used then init.ora file needs to be modified to set the CONTROl_FILE parameters to the OMF created control files.

• If spfile is used this parameter is automatically set & saved during the database creation.

• Control file destination used by OMF :

  • specified by DB_CREATE_ONLINE_LOG_DEST_n

  • If above is not specified than location DB_CREATE_FILE_DEST is used.

• If both of these are not set than OMF can't be used. We get an error unless we specify the CONTROL_FILES parameter in the init.ora

• The names of these OMF created control files are logged in alertSID.log.

Views for obtaining Control file information:

V$CONTROLFILE (shows name & status)	SELECT * FROM V$CONTROLFILE;
V$PARAMETER	SELECT name, value FROM V$PARAMETER WHERE name='control_files';
v$CONTROLFILE_RECORD_SECTION (shows information about the control file record sections)	SELECT type, record_size, records_total, records_used FROM v$CONTROLFILE_RECORD_SECTION WHERE TYPE='DATAFILE'; Note: records_total : shows the maximum no. of datafiles in this case.
PARAMETER control_files (shows name,status & location)	SHOW PARAMETER control_files;

Information of several dynamic performance views are derived from control files such as:

v$TABLESPACE	V$LOG	v$ARCHIVE	V$BACKUP
V$DATAFILE	V$LOGFILE	V$ARCHIVED_LOG	V$DATABASE
V$TEMPFILE	V$LOGHIST

File information using Oracle Enterprise Manager Console :

Default username/Password	admin / admin
Navigate to :	Databases -> Storage