Control Files managing

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.

Multiplexing Control File

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.

Changing the Name of a Database

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.

Creating 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 CONTROLFILEstatement. 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;

 

 

                                                            Introduction:

 

Control files are created by Oracle. The path of a control file is specified in INIT.ORA. Every Oracle database should have at least two control files (recommended); each stored on different disks. If a control file is damaged due to disk failure the assocaited instance must be shutdown. Once the disk drive is reapired, the damaged control file can be restored using an intact copy of the control file and the instance can be restarted. Assume that one control file is located in the path /disk1/oradata/DEMO/control1.ctl, and the other in /disk2/oradata/DEMO/control2.ctl. If one control file 'control1.ctl' is in an unrecoverable state, then issue the following commands

$cp /disk1/oradata/DEMO/control1.ctl \ /disk2/oradata/DEMO/control2.ctl

SQL>startup

Now media recovery is required. By using mirrored control files you avoid unnecessary problems if a disk failure occurs on the database servers.

Managing the size of the control file:

Typical control files are small. The major portion of a control file size depends on the values set for the parameters: MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXINSTANCES of the CREATE DATABASE statement that created the associated database. The maximum control file size is operating system specific:

To check the number of files specified in control files:

SQL>alter database backup controlfile to trace;

$cd /disk1/oradata/DEMO/udump

$cp ora_2065.trc bkup.sql

$cat bkup.sql

If the MAXDATAFILES parameter is set to 5 and if you try to add sixth datafile issuing the command:

SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03.dbf' size 10m;

ORA-1503 create control file failed

ORA-1166 file number 3 larger than MAXDATAFILES (5)

To increase the number of maximum data files supported by your DB edit your trace file.

$vi bkup.sql

     MAX_DATA_FILES 10

:wq

SQL>conn / as sysdba

SQL>@bkup.sql

SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03.dbf' size 10m;

Follow the same steps to increase the parameter values for LOGFILES and LOGMEMBERS.

To create an additional copy of controlfile, issue the following statements. Include the complete path of the new file in 'control_files' parameter in INIT.ORA.

$cd /disk2/oradata/DEMO

$cp control1.ctl control2.ctl

SQL>startup

To drop excessive control files:

-Shutdown the database

-Edit the parameter control_files in INIT.ORA and remove one of the control file entries, leaving atleast one control file to start the database

-Restart the database

-The above steps do not delete the file physically from the disk.

SQL>SHUTDOWN IMMEDIATE

$cat initDEMO.ora             # Here we are only observing 1 line which reads controlfiles

CONTROL_FILES=(/disk2/oradata/DEMO/control2.ctl)

SQL>STARTUP

To Trace the control file to udump destination and generate the create controlfile syntax.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

$cd /disk2/oradata/DEMO/udump

$vi ora_2065.trc

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "DEMO" RESETLOGS NOARCHIVELOG

LOGFILE GROUP 1 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    GROUP 2 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    DATAFILE '/disk1/oradata/DEMO/system01.dbf'

:wq!

SQL>ALTER DATABASE OPEN;

$cp ora_2065.trc orabkup.sql

SQL>SHUTDOWN ABORT

SQL>@orabkup.sql

To rename (change the name of) a database:

-To rename a database follow the following steps:

-Trace the controlfile.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-Edit the trace file as follows, replacing the keyword "REUSE" to "SET"

SQL> CREATE CONTROLFILE SET DATABASE "ACCT" RESETLOGS NOARCHIVELOG

LOGFILE GROUP 1 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    GROUP 2 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    DATAFILE '/disk1/oradata/DEMO/system01.dbf'

SQL>ALTER DATABASE OPEN RESETLOGS;

-Set the parameter db_name to the new name in the init.ora file.

-Remove the existing control files from their destination.

-Finally execute the traced controlfile to create new controlfiles.

Data Dictionary Views you can Query:

-V$CONTROLFILE

-V$CONTROLFILE_RECORD_SECTION

 

                                               Control File

Control File

Smallest file in database.

Crucial information about the database:

Database Name

Creation time of Database

Location of Redo & DBA files

SCN No(System Chane Number)(Logical time stamp for transaction)

Log Sequence Number

To create a database there should be one minimum control file.

Min: 1 Control File

Max: 8 Control Files

Multiplex of Control Files-Availability

Oracle recommends 3 control files.

Demo on control file & redo log file

SQL>startup

SQL>sho parameter control_

SQL>desc v$controlfile

SQL>select * from v$controlfile;

SQL>shutdown immediate

SQL>exit

$cd ORACLE_HOME/dbs

$vi init$ORACLE_SID.ora

control_files=_____,/disk2/oradata/xyz/control2.ctl

$cp control.ctl /disk2/oradata/xyz/control2.ctl

SQL>startup

SQL>sho parameter control_

SQL>sho parameter spfile

SQL>shutdown immediate

SQL>alter system set control_files='/disk3/oradata/earnest3/control.ctl','/disk2/oradata/xyz/control2.ctl' scope=spfile;

SQL>startup force

SQL>sho parameter control_

SQL>select * from v$controlfile;

SQL>shutdown immediate

SQL>exit

$cd $ORACLE_HOME/dbs

$vi init$ORACLE_SID.ora

SQL>startup

SQL>sho parameter spfile

SQL>sho parameter control_

SQL>shutdown immediate

SQL>startup mount

SQL>alter database backup controlfile to trace;

SQL>exit

$cd udump

$cp ___.trc ___/control.trc

$vi control.trc

SQL>@control.trc

SQL>select status from v$instance;

SQL>alter database open;

SQL>sho parameter control_

How to rename the database:

SQL>startup mount

SQL>alter database backup controlfile to trace;

Edit the trace file and select the second set2, remove {reuse} & place set & change the database name.

$vi control.trc

SQL>@control.trc

SQL>select name from v$database;

SQL>alter database open;

SQL>alter database open resetlogs;

SQL>archive log list

How to multiple redo log:

SQL>select * from v$log;

SQL>desc v$log

SQL>desc v$logfile

SQL>select * from v$log;

How to add a group:

SQL>alter database add logfile group 3 '/disk3/oradata/earnest3/redo3a.log' size 5m;

SQL>select * from v$log;

SQL>alter database add logfile member '/disk2/oradata/xyz/redo3b.log' to group 3;

 

************************************************************