Oracle Database - Controlfiles

Control Files are
  • The Control is a small binary file
  • Defines current State of physical database
  • Maintains integrity of database
  • It is Required a t mount stage during database startup - To operate the database
  • Every controlfile linked to a single database
  • Control file loss may required recovery
  • Minimum 1 and maximum 8 control files can maintain for a single database
CONTROL FILE CONTENTS
  1. Database name and Identifier
  2. Time stamp of database creation
  3. Tablespace names
  4. Names and Location of datafiles and Online redo Logfiles
  5. Current Online Redo Logfiles sequence number(LSN)
  6. Checkpoint Information
  7. Begin and end of Undo segments
  8. Redo Log Archive information
  9. Backup Information
multiplex ctrl file

CONTROL FILE
Multiplexing (online-spfile),(offline-pfile)
Trace(control),(control to trace)

MULTIPLEX using spfile
Step 1 : To view list of control files 
	SQL> select name from v$controlfile;

	NAME
	-------------------------------------------------------
	/u01/app/oracle/oradata/TESDB/control01.ctl
	/u01/app/oracle/fast_recovery_area/TESDB/control02.ctl
Step 2 : To view list of control files 
	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 7
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl

	control_management_pack_access	     string	 DIAGNOSTIC+TUNING
Step 3 : Adding new control file 
	Alter system set control_files=
	'/home/oracle/dev/oradata/control/control01.ctl',
	'/home/oracle/dev/oradata/control/control02.ctl',
	'/home/oracle/dev/oradata/control/control03.ctl'

	 scope=spfile
Step 4 : To view list of control files, will not show the newly created control file 
	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 7
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl

	control_management_pack_access	     string	 DIAGNOSTIC+TUNING
Step 5 : Close the database 
	SQL> shut immediate;
	Database closed.
	Database dismounted.
	ORACLE instance shut down.
Step 6 : In the OS level of control file, create new one by 
	copying from first control file
	[oracle@tesdb TESDB]$ cp control01.ctl control03.ctl
Step 7 : Start the database 
	SQL> startup
	ORACLE instance started.

	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes
	Database mounted.
	Database opened.
Step 8 : To view list of control files 
	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 7
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl, /u01/app/oracle/ora
							 data/TESDB/control03.ctl

	control_management_pack_access	     string	 DIAGNOSTIC+TUNING

MULTIPLEXING USING PFILE

Step 1 : Create pfile file from spfile for current update regarding database 
	SQL> create pfile from spfile;
Step 2 : Close database 
	SQL> shut immediate
	Database closed.
	Database dismounted.
	ORACLE instance shut down.
Step 3 : Open the pfile in locatin and add the new control file information 
	[oracle@tesdb dbs]$ vi inittesdb.ora
	Add another control file
	/u01/app/oracle/oradata/TESDB/control04.ctl

	control_files='/u01/app/oracle/oradata/TESDB/control01.ctl',
	'/u01/app/oracle/fast_recovery_area/TESDB/control02.ctl',
	'/u01/app/oracle/oradata/TESDB/control03.ctl',
	'/u01/app/oracle/oradata/TESDB/control04.ctl'
Step 4 : create new control file 
	[oracle@12cserver control]$ cp control03.ctl control04.ctl
Step 5 : Start databse using pfile 
	SQL> startup pfile='$ORACLE_HOME/dbs/inittesdb.ora'
	ORACLE instance started.

	Total System Global Area 1157627168 bytes
	Fixed Size		    8895776 bytes
	Variable Size		  301989888 bytes
	Database Buffers	  838860800 bytes
	Redo Buffers		    7880704 bytes
	Database mounted.
	Database opened.
Step 6 : To view list of control files 
	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 7
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl, /u01/app/oracle/ora
							 data/TESDB/control03.ctl, /u01
							 /app/oracle/oradata/TESDB/cont
							 rol04.ctl

	control_management_pack_access	     string	 DIAGNOSTIC+TUNING
Step 7 : Create spfile file from pfile for current update regarding database 
	SQL> create spfile from pfile;
	File created.
Step 8 : Start the database 
	SQL> startup force;
	ORACLE instance started.

	Total System Global Area  734003200 bytes
	Fixed Size                                                                8625032 bytes
	Variable Size                                                        327156856 bytes
	Database Buffers                                    394264576 bytes
	Redo Buffers                                                         3956736 bytes
	Database mounted.
	Database opened.
Step 9 : Change the CONTROL_FILE_RECORD_KEEP_TIME parameter 
	(7 days (default))

	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 7
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl, /u01/app/oracle/ora
							 data/TESDB/control03.ctl, /u01
							 /app/oracle/oradata/TESDB/cont
							 rol04.ctl
	control_management_pack_access	     string	 DIAGNOSTIC+TUNING

	SQL> alter system set control_file_record_keep_time=10;

	System altered.
Step 10 : To view list of control files 
	SQL> show parameter control

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	control_file_record_keep_time	     integer	 10
	control_files			     string	 /u01/app/oracle/oradata/TESDB/
							 control01.ctl, /u01/app/oracle
							 /fast_recovery_area/TESDB/cont
							 rol02.ctl, /u01/app/oracle/ora
							 data/TESDB/control03.ctl, /u01
							 /app/oracle/oradata/TESDB/cont
							 rol04.ctl
	control_management_pack_access	     string	 DIAGNOSTIC+TUNING
STEP 11 : BACKUP control file and this file will be binary format 
	SQL> alter database backup controlfile to trace;

	Database altered.

	ls[oracle@12cserver trace]$ pwd
	/u01/app/oracle/diag/rdbms/dev/dev/trace
	[oracle@tesdb trace]$ ll -lrt
	-rw-r-----. 1 oracle oinstall  33838 Oct 27 09:56 tesdb_ora_6897.trc
STEP 12 : BACKUP control file in sql format for reading purpose 
	SQL> alter database backup controlfile to trace as '/home/oracle/control.bkp';

	Database altered

	Show controlfile
	vi control.bkp
	text format

	CONTROL file 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


(Oracle Database - OFA Structure)