Control File

Architecture

Control Files

The Control is a small binary file.
Defines current State of physical database
Maintains integrity of database
Require –At mount stage during database startup
-To operate the database
Every control file linked to a single database
Loss may required recovery
Size initially by created database
Minimum 1 and maximum 8 control files can maintain for a single database

Control Files Contents:

Database name and Identifier
Time stamp of database creation
Tablespace names
Names and Location of datafilesand Online redo Logfiles.
Current Online Redo Logfiles sequence number(LSN)
Checkpoint Information
Begin and end of Undo segments
Redo Log Archive information
Backup Information.

controlfile multiplexing

It useful when storing each copy to a different disc.

pfile – Static
spfile – Dynamic

Control file Multiplexing

Multiplexing using spfile(Dynamic):

To check how many control file in your db

SQL> show parameter control_files
NAME TYPE VALUE
——————– —— —————————————-
control_files string /u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf

SQL> select name from v$controlfile;
NAME
———————————————————–
/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf

SQL> alter system set control_files=’/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf‘,’/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf‘ scope=spfile;
System altered.

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

[oracle@sdbt~]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs/

[oracle@sdbtdbs]$ cp cntrlsdbt1.dbf cntrlsdbt2.dbf
[oracle@sdbtdbs]$ ls cntrlsdbt*
cntrlsdbt1.dbf cntrlsdbt2.dbf

SQL> startup
Database mounted.
Database opened.
Control files

SQL> show parameter control_files
NAME TYPE VALUE
——————– —— ———————
control_files string /u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf,
/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf

SQL> select name from v$controlfile;
NAME
———————————————————
/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf
/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf

SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 239079360 bytes
Database Buffers 583008256 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.

SQL> show parameter spfile;
NAME TYPE VALUE
——————– —— —————————
spfile string /u02/app/oracle/product/12.2.0.1/db_1/dbs/spfilesdbt1.ora

Multiplexing using pfile(static):

SQL> create pfile=’/u01/app/oracle/product/12.2.0.1/db_1/dbs/initsdbt1a.ora’
2 from spfile;
File created.

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

[oracle@acsdbs]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs

[oracle@sdbtdbs]$ cp cntrlsdbt2.dbf cntrlsdbt3.dbf
[oracle@sdbtdbs]$ ls cntrlsdbt*
cntrlsdbt1.dbf cntrlsdbt2.dbf cntrlsdbt3.dbf

Control files modification in pfile
Check control file information in pfile

*.control_files=’/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf’,
‘/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf’

After modification of controlfile parameter in pfile

*.control_files=’/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf’,
‘/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf’,
‘/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt3.dbf’

[oracle@sdbtdbs]$ export ORACLE_SID=sdbt1
[oracle@sdbtdbs]$ sqlplus/nolog
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 15 13:19:34 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> connsys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup pfile=’/u02/app/oracle/product/12.2.0.1/db_1/dbs/initsdbt1a.ora’;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 239079360 bytes
Database Buffers 583008256 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.

SQL> show parameter pfile;
NAME TYPE TYPE VALUE
——————– —— ————————–
spfile string

SQL> show parameter control_files;
NAME TYPE VALUE
——————– —— —————————
spfile string /u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf,
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf,
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt3.dbf

Database startup using pfile

SQL> select name from v$controlfile;
NAME
———————————————————
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt3.dbf

SQL> create spfile
2 from pfile=’/u02/app/oracle/product/12.2.0.1/db_1/dbs/initsdbt1a.ora’;
File created.

SQL> create spfile from pfile;
File created.

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

SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 239079360 bytes
Database Buffers 583008256 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.

SQL> show parameter pfile
NAME TYPE VALUE
——————– —— —————————
spfile string /u02/app/oracle/product/12.2.0.1/db_1/dbs/spfilesdbt1.ora

SQL> select name from v$controlfile;
NAME
———————————————————
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt1.dbf
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt2.dbf
/u02/app/oracle/product/12.2.0.1/db_1/dbs/cntrlsdbt3.dbf

Hint Database startup using spfile

Control files views

show parameter control
v$controlfile
v$controlfile_record_section
v$backup_controlfile_summary
v$session_fix_control
v$backup_controlfile_details
dba_hist_wr_control