Control File

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.

  1. pfile – Static
  2. 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.

[[email protected]~]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs/

[[email protected]]$ cp cntrlsdbt1.dbf cntrlsdbt2.dbf
[[email protected]]$ 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.

[[email protected]]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs

[[email protected]]$ cp cntrlsdbt2.dbf cntrlsdbt3.dbf
[[email protected]]$ 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’

[[email protected]]$ export ORACLE_SID=sdbt1
[[email protected]]$ 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