control files in general

control files in general

What Is a Control File?

Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:

n The database name

n Names and locations of associated datafiles and online redo log files

n The timestamp of the database creation

n The current log sequence number

n 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 might also need to create control files later, if you lose control files or want to change particular settings in the control files.

 

MULTIPLEXING CONTROL FILES USING INIT.ORA

1.shutdown the database.

2.copy the control file to move locations by using operating system command.

3.change the initialization parameter file to include new control files name in the CONTROL_FILES.

control_files=(“d:\oracle\oradata\frame\CONTROL01.CTL”, “d:\oracle\oradata\frame\CONTROL02.CTL”, “d:\oracle\oradata\frame\CONTROL03.CTL”,”e:\backup\controlfile\CONTROL01.CTL”)

4.startup database.

 

MULTIPLEXING CONTROL FILES USING SPFILES

1.alter system set control_files=’d:\oracle\oradata\frame\CONTROL01.CTL’,’d:\oracle\oradata\frame\CONTROL02.CTL’,’d:\oracle\oradata\frame\CONTROL03.CTL’,’e:\backup\controlfile\CONTROL01.CTL’ scope=spfile.

2.shutdown database.

3.copy the existing control file to new location.

4.start up instance

 

CREATING NEW CONTROL FILES

You can create new control file by using create controlfile command.you need to create it when

1.if you loose all the control files.

2.if you want to change any of the MAX clauses.

3.if you want to change database name.

You have to know data file names and redo log files name to create control file.

command:-

1.start the database in NOMOUNT mode.

SQL>STARTUP NOMOUNT

 

2.SQL>CREATE CONTROLFILE REUSE DATABASE “FRAME” NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 ‘D:\ORACLE\ORADATA\FRAME\REDO01.LOG’  SIZE 100M,

GROUP 2 ‘D:\ORACLE\ORADATA\FRAME\REDO02.LOG’  SIZE 100M,

GROUP 3 ‘D:\ORACLE\ORADATA\FRAME\REDO03.LOG’  SIZE 100M

DATAFILE

‘D:\ORACLE\ORADATA\FRAME\SYSTEM01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\UNDOTBS01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\CWMLITE01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\DRSYS01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\INDX01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\TOOLS01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\USERS01.DBF’,

‘D:\ORACLE\ORADATA\FRAME\FRAME.DBF’

CHARACTER SET WE8MSWIN1252

;

3.Start the database.

SQL>ALTER DATABASE OPEN.

 

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’ REUSE;

2. Produce SQL statements that can later be used to recreate your control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;