Click to edit Master subtitle style
Control Files BY SUNDAR.KM
3/29/12
Managing Control files
What Is a Control File? Guidelines for Control Files Creating Control Files Backing Up Control Files Dropping Control Files Displaying Control File Information
3/29/12
Troubleshooting After Creating Control Files
Recovering a Control File Using a Current Co
What Is a Control File? Binary file. Mandatory 1 control file per DB. Records the Physical structure of the DB. Contents of Control file
The database name Names and locations of associated
datafiles and redo log files
The timestamp of the database creation The current log sequence number Checkpoint information
3/29/12
Provide Filenames for the Control
Files
Parameter CONTROL_FILES in init file. If control_files not specified then, Database creates it own ctl file with
default name.
OMF depends. ASM depends. Multiplex Control Files on Different
3/29/12
Behavior of Multiplexed controlfile
The db writes to all the files listed in C_F
par.
DB reads only the first file listed in the
parameter during database operation.
If any of the Controlfile becomes
unavailable during the db operation, the instance becomes inoperable and the instance must be aborted. 3/29/12
Back Up Control Files Every time you change the physical structure of your database.
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.
3/29/12
Size of Control Files MAXDATAFILES MAXLOGFILES MAXLOGMEMBERS MAXLOGHISTORY and MAXINSTANCES. The values of these parameters specified in the CREATE DATABASE statement increases the size of the controlfile. 3/29/12
Creating Control Files Creating Initial Control Files The control_files parameter values specified in the init file will create the files. Fully qualified names must be specified. If it already exists, REUSE clause can be used but the size of the existing OS files must be same or else Reuse 3/29/12
Additional Copies, Renaming, and
Relocating
Shut down the database. Copy an existing control file to a new
location, using operating system commands.
Edit the CONTROL_FILES parameter in
the database initialization parameter file to add the new control file name, or to change the existing control filename.
Restart the database.
3/29/12
When to Create New Control Files
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.
DBID utility. Compatibility set 10.2.0 or later, file automatically expands to accommodate the changes.
3/29/12 Compatibility set earlier than 10.2.0, Need
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'), ('/u01/oracle/prod/redo03_01.log', '/u01/oracle/prod/redo03_02.log')
GROUP 3
RESETLOGS DATAFILE
3/29/12
Steps for Creating New Control Files 1) Make a list of all datafiles and redo log files of the database.
SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE; SELECT VALUE FROM V$PARAMETER
WHERE NAME = 'control_files';
If the DB is down, then make up a list of
3/29/12
2) Shut down the database normally if possible. (shutdown abort a last resort). 3) Back up all datafiles and redo log files of the database. 4) STARTUP NOMOUNT 5) CREATE CONTROLFILE statement. (Specify resetlogs clause, if you have lost
3/29/12
6) Store a backup of the new control file on offline. 7) Edit Control_files Parameter & DB_NAME only if you change the DB name. 8) Recover the Database. 9) Alter database open; Alter database open resetlogs;
3/29/12
Backing Up Control Files
Binary Backup ALTER DATABASE BACKUP CONTROLFILE
TO '/oracle/backup/control.bkp';
SQL statement Backup. ALTER DATABASE BACKUP CONTROLFILE
TO TRACE;
3/29/12
DDV
V$Database : Displays the DB info from Controlfile. V$Controlfile : List the names of controlfile. V$Controlfile_record_section : Info about records. V$parameter : Control_files
3/29/12
