Controlfile Management

Every database must require atleast one controlfile to start the database instance. But Oracle recommends minimum 2 or 3 controlfiles each 1 different harddisk and maximum 8 controlfiles.

Controlfile contains the following information:

            1. Database name.

            2. Database Creation Time(Time Stamp).

            3. Names and physical locations of datafile and sizes.

            4. Names and physical locations of redolog files and sizes.

            5. System change numbers (every statement generates a SCN number).

            6. It also contains checkpoint numbers.

Note: Checkpoint numbers are useful for faster recovery of database.

How to identify the controlfile:

SQL>select * from v$controlfile;

How to duplex (or) add multiple control file by using pfile :

Sqlplus / as sysdba

SQL>select name from v$controlfile;

SQL>shut immediate

$cd /u01/oradata/orcl

$cp control1.ctl control2.ctl

$cp control1.ctl control3.ctl

$cp control1.ctl control4.ctl

$vi initorcl.ora

Control_files=/u01/PROD/control1.ctl,/u01/PROD/control2.ctl, /u01/PROD/control3.ctl,/u01/PROD/control4.ctl

:wq

SQL>startup

SQL>select name from v$controlfile;

Rename Database:

Take backup of orcl database controlfile.

SQL> alter database backup controlfile to trace as '/disk1/admin/control.sql';

Database altered.

SQL> exit

Edit controlfile trace to create new controlfile with new database name.

[oracle@localhost dbs]$ cd /disk1/admin/
[oracle@localhost admin]$ ls
control.sql  ORCL

remove the unnecessary lines

change the below in control.sql file

CREATE CONTROLFILE RESUE DATABASE
RESUE-->SET

CREATE CONTROLFILE SET DATABASE "ORCL"
ORCL-->"ORCL1"

[oracle@localhost admin]$ vi control.sql

CREATE CONTROLFILE SET DATABASE "ORCL1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/disk1/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/disk1/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/disk1/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/disk1/oradata/orcl/system01.dbf',
  '/disk1/oradata/orcl/sysaux01.dbf',
  '/disk1/oradata/orcl/undotbs01.dbf',
  '/disk1/oradata/orcl/users01.dbf',
  '/disk1/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
Remove old controlfiles

[oracle@localhost orcl]$ rm -rf control01.ctl

[oracle@localhost admin]$ export ORACLE_SID=orcl

Change name in pfile

Vi initorcl.ora

Db_name=orcl  à orcl

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 29 10:25:24 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             171968544 bytes
Database Buffers          658505728 bytes
Redo Buffers                2412544 bytes

SQL> @control.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.