Control file | Oracle DBA Tutorial pdf

Control file

Control file contains –
=> Database name
=> Database creation timestamp
=> Data files – name, location, on/off line status
=> Redo log files – name, location
=> Redo log archive information
=> Table space names
=> Current log sequence number
=> Most recent checkpoint information
=> Begin and end of undo segments
=> RMAN backup information
Oracle backs up control file after any structural changes in database. LGWR updates control file with current log sequence number. CKPT updates control file with recent checkpoint information. ARCn updates with archiving information.

 Multiplexing control files

Using init.ora
CONTROL_FILES =
(‘/ora/oradata/mydb/control1.ctl’, ‘/ora/oradata/mydb/control1.ctl’)
Using spfile
1. SQL> ALTER SYSTEM SET CONTROL_FILES =
(‘/ora/oradata/mydb/control1.ctl’, ‘/ora/oradata/mydb/control1.ctl’)
SCOPE=SPFILE
2. SQL>SHUTDOWN NORMAL
3. Copy control file to new location
4. SQL>STARTUP
To create OMF control files, don’t specify CONTROL_FILES parameter in initialization file; rather specify DB_CREATE_ONLINE_LOG_DEST_n
parameter n times starting with 1.

Creating new control file

Make sure you have complete list of all data and log files.
In SQL Plus, write (the database should be in STARTUP NOMOUNT)
CREATE CONTROLFILE SET DATABASE “MYDB”
NORESTLOGS NOARCHIVELOG
MAXDATAFILES 30
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXINSTANCES 1
MAXLOGHISTORY 1
DATAFILE
'F:\MYDB\DATAFILES\SYSTEM01.DBF'
'F:\MYDB\DATAFILES\USERS01.DBF'
'F:\MYDB\DATAFILES\UNDO01.DBF'
'F:\MYDB\DATAFILES\TEMP01.DBF'
LOGFILE
GROUP 1 'F:\MYDB\LOG\LOG01.DBF' SIZE 10M,
GROUP 2 'F:\MYDB\LOG\LOG02.DBF' SIZE 10M
/
To back up control file when database is running, use
ALTER DATABASE BACKUP CONTROLFILE TO filename REUSE
Or
ALTER DATABASE BACKUP CONTROLFILE TO TRACE – it places a text
copy of control file to USER_DUMP_DEST directory.
If Oracle can’t update control file, instance crashes.

Control file related data dictionary views

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION