What is oracle Control file and how to do Multiplexing control file

What Is a Control File?
As we know, Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.
 

And the control file includes the below points as:
 
* 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 or up. Because without the control file, the database cannot be mounted and recovery is so 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.

We will discuss these points with examples in below points:

1. How to display Name and File Size of the Control Files:

SQL> select name,block_size,FILE_SIZE_BLKS from v$controlfile;

NAME                                                BLOCK_SIZE FILE_SIZE_BLKS
---------------------------------------------       ---------- --------------
/u01/oracle/oradata/ora-data/control01.ctl             16384            430
/u01/oracle/oradata/ora-data/control02.ctl             16384            430
/u01/oracle/oradata/ora-data/control03.ctl             16384            430


2.How to display the initial sizing of the tablespace section in your control file

SQL> SELECT records_total FROM v$controlfile_record_section WHERE type = 'TABLESPACE';

RECORDS_TOTAL
-------------
          100


3.How to do Multiplex the existing 3 Control files to 4 Control files and give OS permission 640 to new multiplexed file control04.ctl

a)Before Shutting Down, Add the new control file to the SPFILE (Server Parameter File)

$sqlplus '/as sysdba'

SQL> ALTER SYSTEM SET CONTROL_FILES='/u01/oracle/oradata/ora-data/control01.ctl','/u01/oracle/oradata/ora-data/control02.ctl','/u01/oracle/oradata/ora-data/control03.ctl','/u01/oracle/oradata/ora-data/control04.ctl' scope=SPFILE;


b) Shutting down the database:

SQL> shutdown  immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

c) Multiplexing the control file and providing permissions, Below steps either you can do on $ prompt or SQL prompt.

SQL>!cp /u01/oracle/oradata/ora-data/control01.ctl /u01/oracle/oradata/ora-data/control04.ctl

SQL>!chmod 640 /u01/oracle/oradata/ora-data/control04.ctl
 

d) Startup the database:

SQL> startup
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2020128 bytes
Variable Size             301993184 bytes
Database Buffers          771751936 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.

e) Now check the Multiplexing number of control file:

SQL> select name from v$controlfile;
NAME
---------------------------------------------
/u01/oracle/oradata/ora-data/control01.ctl
/u01/oracle/oradata/ora-data/control02.ctl
/u01/oracle/oradata/ora-data/control03.ctl
/u01/oracle/oradata/ora-data/control04.ctl

4. Simulate the scenario of starting the Database with No Control Files and check what error you get

a)  $sqlplus '/as sysdba'

SQL> shutdown  immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

b)  Take the backups of all the Control Files

SQL> !cp /u01/oracle/oradata/ora-data/control01.ctl /u01/oracle/oradata/ora-data/control01.ctl.bak

SQL> !rm /u01/oracle/oradata/ora-data/control01.ctl

SQL> !cp /u01/oracle/oradata/ora-data/control02.ctl /u01/oracle/oradata/ora-data/control02.ctl.bak

SQL> !rm /u01/oracle/oradata/ora-data/control02.ctl

SQL> !cp /u01/oracle/oradata/ora-data/control03.ctl /u01/oracle/oradata/ora-data/control03.ctl.bak

SQL> !rm /u01/oracle/oradata/ora-data/control03.ctl

SQL> !cp /u01/oracle/oradata/ora-data/control04.ctl /u01/oracle/oradata/ora-data/control04.ctl.bak

SQL> !rm /u01/oracle/oradata/ora-data/control04.ctl


SQL>  !ls -l /u01/oracle/oradata/ora-data/con*
-rw-r----- 1 oracle oinstall 7061504 Sep 12 21:13 /u01/oracle/oradata/ora-data/control01.ctl.bak
-rw-r----- 1 oracle oinstall 7061504 Sep 12 21:13 /u01/oracle/oradata/ora-data/control02.ctl.bak
-rw-r----- 1 oracle oinstall 7061504 Sep 12 21:14 /u01/oracle/oradata/ora-data/control03.ctl.bak
-rw-r----- 1 oracle oinstall 7061504 Sep 12 21:14 /u01/oracle/oradata/ora-data/control04.ctl.bak

c) SQL> startup
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2020128 bytes
Variable Size             301993184 bytes
Database Buffers          771751936 bytes
Redo Buffers               14753792 bytes
ORA-00205: error in identifying control file, check alert log for more info


d) Shutdown the Database Instance

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.


e) Place back the control files in the original name

SQL>!cp /u01/oracle/oradata/ora-data/control01.ctl.bak /u01/oracle/oradata/ora-data/control01.ctl

SQL>!cp  /u01/oracle/oradata/ora-data/control02.ctl.bak /u01/oracle/oradataora-data/control02.ctl

SQL>!cp  /u01/oracle/oradata/ora-data/control03.ctl.bak /u01/oracle/oradata/ora-data/control03.ctl

SQL>!cp  /u01/oracle/oradataora-data/control04.ctl.bak /u01/oracle/oradata/ora-data/control04.ctl

f) Startup the Database Instace

SQL> startup
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2020128 bytes
Variable Size             301993184 bytes
Database Buffers          771751936 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.

Now, the database has started normally.
These are the steps related to Control file which very useful for oracle database.

Some more useful links:
ORA-609 alert in oracle solution
SQL SCRIPTS
SESSION Information in Oracle
Job Tips
R12.2 Architecture & Concepts

For any queries or doubts, Please comment us in comment box and let us know for any concerns @Contact US
Thanks...