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
- Multiplexing (SPFILE — online, PFILE — offline)
- 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