Oracle- CONTROL FILES

CONTROL FILES

Every Oracle Database has a control file, which is a small binary file that records the

physical structure of the database. The control file includes:

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

The control file must be available for writing by the Oracle Database server whenever

the database is open. Without the control file, the database cannot be mounted and

recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By

default, at least one copy of the control file is created during database creation. On

some operating systems the default is to create multiple copies. You should create two

or more copies of the control file during database creation. You can also create control

files later, if you lose control files or want to change particular settings in the control

files.

Back Up Control Files

It is very important that you back up your control files. This is true initially, and every

time you change the physical structure of your database. Such structural changes

include:

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

The CREATE CONTROLFILE Statement

CREATE CONTROLFILE

SET DATABASE prod

LOGFILE GROUP 1 ('E:\SNSD0809\redo01_01.log',

'E:\SNSD0809\redo01_02.log'),

GROUP 2 ('E:\SNSD0809\redo02_01.log',

'E:\SNSD0809\redo02_02.log'),

GROUP 3 ('E:\SNSD0809\redo03_01.log',

'E:\SNSD0809\redo03_02.log')

RESETLOGS

DATAFILE 'E:\SNSD0809\system01.dbf' SIZE 3M,

'E:\SNSD0809\rbs01.dbs' SIZE 5M,

'E:\SNSD0809\users01.dbs' SIZE 5M,

'E:\SNSD0809\temp01.dbs' SIZE 5M

MAXLOGFILES 50

MAXLOGMEMBERS 3

MAXLOGHISTORY 400

MAXDATAFILES 200

MAXINSTANCES 6

ARCHIVELOG;

============================TESTED====================

CREATE CONTROLFILE REUSE DATABASE "SNS0506" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 671

LOGFILE

GROUP 1 'D:\SNSD0506\REDO01.ORA' SIZE 50M,

GROUP 2 'D:\SNSD0506\REDO02.ORA' SIZE 50M,

GROUP 3 'D:\SNSD0506\REDO03.ORA' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'D:\SNSD0506\SYSTEM01.ORA',

'D:\SNSD0506\UNDOTBS01.ORA',

'D:\SNSD0506\SYSAUX01.ORA',

'D:\SNSD0506\INDX01.ORA',

'D:\SNSD0506\USERS01.ORA'

CHARACTER SET WE8MSWIN1252;

==================================================

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 you have no such lists and your control file has been damaged so that the

database cannot be opened, try to locate all of the datafiles and redo log files that

constitute the database.

2. Shut down the database.

3. Back up all datafiles and redo log files of the database.

4. Start up a new instance, but do not mount or open the database:

STARTUP NOMOUNT

5. Create a new control file for the database using the CREATE CONTROLFILE

statement.

6. Store a backup of the new control file on an offline storage device.

7. Edit the CONTROL_FILES initialization parameter for the database to indicate all

of the control files now part of your database as created in step 5 (not including the

backup control file).

8. Recover the database if necessary. If you are not recovering the database, skip to

step 9.

9. Open the database using one of the following methods:

If you did not perform recovery, or you performed complete, closed database

recovery in step 8, open the database normally.

ALTER DATABASE OPEN;

If you specified RESETLOGS when creating the control file, use the ALTER

DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use.

Backing Up Control Files

1. Back up the control file to a binary file (duplicate of existing control file) using the

following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

2. Produce SQL statements that can later be used to re-create your control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Location: C:\oracle\product\10.2.0\admin\orcl\udump

This command writes a SQL script to the database trace file where it can be

captured and edited to reproduce the control file.

Recovering from Control File Corruption Using a Control File Copy

1. With the instance shut down, use an operating system command to overwrite the

bad control file with a good copy:

% cp E:\SNSD0809/control03.ctl / D:\SNSD0809/control02.ctl

2. Start SQL*Plus and open the database:

SQL> STARTUP

Recovering from Permanent Media Failure Using a Control File Copy

1. With the instance shut down, use an operating system command to copy the

current copy of the control file to a new, accessible location:

% cp E:\SNSD0809\control01.ctl D:\SNSD0809/control03.ctl

2. Edit the CONTROL_FILES parameter in the initialization parameter file to replace

the bad location with the new location:

CONTROL_FILES = (E:\SNSD0809\control01.ctl,

/ E:\SNSD0809/control02.ctl,

E:\SNSD0809/control03.ctl)

3. Start SQL*Plus and open the database:

SQL> STARTUP

Dropping Control Files

1. Shut down the database.

2. Edit the CONTROL_FILES parameter in the database initialization parameter file

to delete the old control file name.

3. Restart the database.

Displaying Control File Information

V$DATABASE

V$CONTROLFILE

V$CONTROLFILE_RECORD_SECTION

V$PARAMETER

SQL> select name from v$datafile;

NAME

------------------------------------

E:\SNSD0809\SYSTEM01.ORA

E:\SNSD0809\UNDOTBS01.ORA

E:\SNSD0809\SYSAUX01.ORA

E:\SNSD0809\INDX01.ORA

E:\SNSD0809\USERS01.ORA

SQL> select name from v$controlfile;

NAME

----------------------------------------

E:\SNSD0809\CONTROL01.ORA

E:\SNSD0809\CONTROL02.ORA

E:\SNSD0809\CONTROL03.ORA

SQL> select member from v$logfile;

MEMBER

----------------------------------------

E:\SNSD0809\REDO01.ORA

E:\SNSD0809\REDO02.ORA

E:\SNSD0809\REDO03.ORA

E:\SNSD0809\REDO04.ORA

E:\SNSD0809\REDO05.ORA