Managing 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
It is
strongly recommended that you multiplex control files i.e. Have at least two control files one in
one hard disk and another one located in another disk, in a database. In this way if control file becomes corrupt
in one disk the another copy will be available and you
don’t have to do recovery of control file.
You can multiplex control
file at the time of creating a database and later on also. If you have not
multiplexed control file at the time of creating a database you can do it now
by following given procedure.
Steps:
1.
Shutdown
the Database.
SQL>SHUTDOWN
IMMEDIATE;
2.
Copy
the control file from old location to new location using operating system
command. For example.
$cp
/u01/oracle/ica/control.ora
/u02/oracle/ica/control.ora
3.
Now
open the parameter file and specify the new location like this
CONTROL_FILES=/u01/oracle/ica/control.ora
Change
it to
CONTROL_FILES=/u01/oracle/ica/control.ora,/u02/oracle/ica/control.ora
4.
Start
the Database
Now
Oracle will start updating both the control files and, if one control file is
lost you can copy it
from another location.
If you
ever want to change the name of database or want to change the setting of MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS
then you have to create a new control file.
Follow
the given steps to create a new controlfile
Steps
1.
First
generate the create controlfile statement
SQL>alter
database backup controlfile to trace;
After
giving this statement oracle will write the CREATE
CONTROLFILE statement in a trace file. The
trace file will be randomly named something like ORA23212.TRC
and it is created in USER_DUMP_DEST
directory.
2.
Go
to the USER_DUMP_DEST
directory and open the latest trace file in text editor. This file will contain
the CREATE CONTROLFILE
statement. It will have two sets of statement one with RESETLOGS and another without RESETLOGS.
Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE
statement. Now copy and paste the statement in a file. Let it be c.sql
3.
Now
open the c.sql file in text editor and set the database name from ica to prod
shown in an example below
CREATE
CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/ica/redo01_01.log',
'/u01/oracle/ica/redo01_02.log'),
GROUP 2 ('/u01/oracle/ica/redo02_01.log',
'/u01/oracle/ica/redo02_02.log'),
GROUP 3 ('/u01/oracle/ica/redo03_01.log',
'/u01/oracle/ica/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/ica/system01.dbf' SIZE
3M,
'/u01/oracle/ica/rbs01.dbs' SIZE
5M,
'/u01/oracle/ica/users01.dbs' SIZE
5M,
'/u01/oracle/ica/temp01.dbs' SIZE
5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
4.
Start
and do not mount the database.
SQL>STARTUP
NOMOUNT;
5.
Now
execute c.sql script
SQL>
@/u01/oracle/c.sql
6.
Now
open the database with RESETLOGS
SQL>ALTER
DATABASE OPEN RESETLOGS;
You have
a Production database running in one server. The company management wants to
develop some new modules and they have hired some programmers to do that. Now
these programmers require access to the Production database and they want to
make changes to it. You as a DBA can’t give direct access to Production
database so you want to create a copy of this database on another server and
wants to give developers access to it.
Let us
see an example of cloning a database
We have
a database running the production server with the following files
PARAMETER FILE located in
/u01/oracle/ica/initica.ora
CONTROL FILES=/u01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=/u01/oracle/ica/bdump
USER_DUMP_DEST=/u01/oracle/ica/udump
CORE_DUMP_DEST=/u01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=/u01/oracle/ica/arc1”
DATAFILES =
/u01/oracle/ica/sys.dbf
/u01/oracle/ica/usr.dbf
/u01/oracle/ica/rbs.dbf
/u01/oracle/ica/tmp.dbf
/u01/oracle/ica/sysaux.dbf
LOGFILE=
/u01/oracle/ica/log1.ora
/u01/oracle/ica/log2.ora
Now
you want to copy this database to SERVER 2 and in SERVER 2 you don’t have /u01
filesystem. In SERVER 2 you have /d01 filesystem.
To Clone
this Database on SERVER 2 do the following.
Steps
:-
1.
In
SERVER 2 install the same version of o/s and same version Oracle as in SERVER
1.
2.
In
SERVER 1 generate CREATE CONTROLFILE
statement by typing the following command
SQL>alter
database backup controlfile to trace;
Now,
go to the USER_DUMP_DEST
directory and open the latest trace file. This file will contain steps and as
well as CREATE CONTROLFILE
statement. Copy the CREATE CONTROLFILE
statement and paste in a file. Let the filename be cr.sql
The
CREATE CONTROLFILE
Statement will look like this.
CREATE
CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/ica/log1.ora'
GROUP 2 ('/u01/oracle/ica/log2.ora'
DATAFILE '/u01/oracle/ica/sys.dbf' SIZE 300M,
'/u01/oracle/ica/rbs.dbf' SIZE 50M,
'/u01/oracle/ica/usr.dbf' SIZE 50M,
'/u01/oracle/ica/tmp.dbf' SIZE 50M,
‘/u01/oracle/ica/sysaux.dbf’ size 100M;
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
3.
In
SERVER 2 create the following directories
$cd /d01/oracle
$mkdir ica
$mkdir arc1
$cd ica
$mkdir bdump udump
cdump
Shutdown
the database on SERVER 1 and transfer all datafiles, logfiles
and control
file to SERVER 2 in /d01/oracle/ica
directory.
Copy
parameter file to SERVER 2 in /d01/oracle/dbs directory and copy all archive
log files to SERVER 2 in /d01/oracle/ica/arc1
directory. Copy the cr.sql script file to /d01/oracle/ica directory.
4.
Open
the parameter file SERVER 2 and change the following parameters
CONTROL
FILES=//d01/oracle/ica/control.ora
BACKGROUND_DUMP_DEST=//d01/oracle/ica/bdump
USER_DUMP_DEST=//d01/oracle/ica/udump
CORE_DUMP_DEST=//d01/oracle/ica/cdump
LOG_ARCHIVE_DEST_1=”location=//d01/oracle/ica/arc1”
5.
Now,
open the cr.sql file in text editor and change
the locations like this
CREATE
CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('//d01/oracle/ica/log1.ora'
GROUP 2 ('//d01/oracle/ica/log2.ora'
DATAFILE '//d01/oracle/ica/sys.dbf' SIZE 300M,
'//d01/oracle/ica/rbs.dbf' SIZE 50M,
'//d01/oracle/ica/usr.dbf' SIZE 50M,
'//d01/oracle/ica/tmp.dbf' SIZE 50M,
‘//d01/oracle/ica/sysaux.dbf’ size 100M;
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
In
SERVER 2 export ORACLE_SID
environment variable and start the instance
$export
ORACLE_SID=ica
$sqlplus
Enter
User:/ as sysdba
SQL>
startup nomount;
6.
Run
cr.sql script to create the controlfile
SQL>@/d01/oracle/ica/cr.sql
7.
Open
the database
SQL>alter
database open;
| 
