What is Control file, multiplex control file in oracle database 19c | dbatutorial.com

 What is control file?

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:

1. The database name

2. Names and locations of associated datafiles and redo log files

3. The timestamp of the database creation

4. The current log sequence number

5. Checkpoint information

What is control file multiplexing?

Every oracle database should have multiple control files at least two control files should have on a different disk. If control file is damaged the associate instance must be shut down. If oracle database have multiple control file damaged control file can be replaced from intact copy. Oracle database control file multiplexing is the process of maintaining a copy of same control files on different drivers or locations. To multiplex your control files, we copy the control file to multiple locations and change the CONTROL_FILES parameter.

Control file multiplexing steps are given below.

@@ Login using sysdba and check current control file

SQL> SELECT name FROM v$controlfile;

NAME

--------------------------------------------------------------------------------

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\CONTROL01.CTL

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\CONTROL02.CTL

SQL> show parameter control_file

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      E:\APP\MASUD\VIRTUAL\OR

                                                 ADATA\TEST\CONTROL01.CTL, E:\A

                                                 PP\MASUD\VIRTUAL\ORADAT

                                                 A\TEST\CONTROL02.CTL

 

@@ Shutdown the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

@@ startup database nomount mode

SQL> startup nomount

ORACLE instance started.

Total System Global Area 2533359616 bytes

Fixed Size                  8750016 bytes

Variable Size             687868992 bytes

Database Buffers         1828716544 bytes

Redo Buffers                8024064 bytes

@@ Invoke RMAN to copy the control file

RMAN TARGET/

RMAN> restore controlfile to 'E:\app\masud\virtual\CONTROL03.CTL' from 'E:\app\masud\virtual\oradata\TEST\CONTROL01.CTL';

Starting restore at 29-SEP-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=260 device type=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 29-SEP-20

Note: Now we check the new location whether newly control file created or not

@@ Add new control file using below command

SQL> alter system set control_files='E:\app\masud\virtual\oradata\TEST\CONTROL01.CTL','E:\app\masud\virtual\oradata\TEST\CONTROL02.CTL','E:\app\masud\virtual\CONTROL03.CTL' scope=spfile;

System altered.

@@ shutdown database

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

@@ startup database

SQL> startup

ORACLE instance started.

Total System Global Area 2533359616 bytes

Fixed Size                  8750016 bytes

Variable Size             687868992 bytes

Database Buffers         1828716544 bytes

Redo Buffers                8024064 bytes

Database mounted.

Database opened.

@@ check the control file again

SQL> SELECT name FROM v$controlfile;

NAME

--------------------------------------------------------------------------------

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\CONTROL01.CTL

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\CONTROL02.CTL

E:\APP\MASUD\VIRTUAL\CONTROL03.CTL

Note: Control file (CONTROL03.CTL) newly added in different location

Done!!