CONTROL FILE


Control File

  • It is the heart of the database.
  • It is a small binary format.
  • Maintain in physical level (OS).
  • Read in MOUNT stage.
  • Minimum 1 and maximum 8 control files for a single database.
  • Used for safety purpose.

Control File Contains

  • Database name & ID
  • Timestamp of database creation
  • Tablespace name
  • Datafile & logfile
  • LSN (Log Sequence Number) and SCN (System Change Number)
  • RMAN backup information
  • Archivelog & Redolog information

Two Methods

  1. Multiplexing (SPFILE — online, PFILE — offline)
  2. Trace (SQL format — take backup)

1) Multiplexing — SPFILE (Online)

DB Level
SHOW PARAMETER SPFILE;

CREATE PFILE FROM SPFILE;

SHOW PARAMETER CONTROL;

SELECT NAME FROM V$CONTROLFILE;

ALTER SYSTEM SET CONTROL_FILES=
'/u01/app/oracle/oradata/mac/control01.ctl',
'/u01/app/oracle/fast_recovery_area/mac/control02.ctl',
'/u01/app/oracle/oradata/mac/control03.ctl'
SCOPE=SPFILE;

SHUT IMMEDIATE
OS Level
cd /u01/app/oracle/oradata/mac
cp control01.ctl control03.ctl

STARTUP

SHOW PARAMETER CONTROL;
SELECT NAME FROM V$CONTROLFILE;

2) Multiplexing — PFILE (Offline)

CREATE PFILE FROM SPFILE;
SHUT IMMEDIATE

cd $ORACLE_HOME/dbs
vi initmac.ora

control_files=
'/u01/app/oracle/oradata/mac/control01.ctl',
'/u01/app/oracle/fast_recovery_area/mac/control02.ctl',
'/u01/app/oracle/oradata/mac/control03.ctl',
'/u01/app/oracle/oradata/mac/control04.ctl'

:wq

cd /u01/app/oracle/oradata/mac
cp control01.ctl control04.ctl

STARTUP

CREATE SPFILE FROM PFILE;

3) Trace — SQL Format

Trace method is used to take the Control File backup information in SQL format. The generated trace file contains SQL statements that can be used to recreate the Control File.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Quick DBA Commands

SHOW PARAMETER CONTROL;

SELECT NAME FROM V$CONTROLFILE;

SHOW PARAMETER SPFILE;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Easy Memory

CONTROL FILE
|
+-- Database Name & ID
+-- Tablespace
+-- Datafile & Logfile
+-- SCN / LSN
+-- RMAN Backup Information
+-- Archivelog & Redolog
|
+-- Multiplexing
| +-- SPFILE
| +-- PFILE
|
+-- Trace
+-- SQL Format