Controlfiles in Oracle

What Is a Control File?
Every Oracle Database has a control file, which is a small binary file that records the physical Database file.

The content of controlfiles:-

• The database name and archive log mode information.
• Names and locations of associated datafiles and redo log files
• The timestamp of the database creation
• The current log sequence number(LNS-log sequence Number)
• Checkpoint information(SCN-system change Number).
• Rman Backup Information.

NOTE:-

• Without the control file, the database cannot be mounted and recovery is difficult.
• Oracle database should have at least two control files, each stored on a different disk, we can create more copies of Controlfile.

Multiplexing of a controlfile.

1. Static -using pfile we can multiplex with restart.
2. Dynamic -usign spfile we can multiplex with restart in Mount.

STATIC:-

Step 1:-
Down The database.
Step 2:-
open a pfile and add a below entry. and save the pfile.
CONTROL_FILES = (/home/oracle/prod/control01.ctl,
                 /u02/oracle/prod/control02.ctl,
                 /u03/oracle/prod/control03.ctl)
Step 3:-
copy the controlfile to different mount point.
cp /home/oracle/prod/control01.ctl /u02/oracle/prod/control02.ctl
cp /home/oracle/prod/control01.ctl /u02/oracle/prod/control03.ctl

Step 4:-
start the Database using pfile.
startup pfile='/home/oracle/initprod.ora' nomount;

DYNAMIC:-
Step 1:-
check your database running spfile..
 alter system set control_files='/home/oracle/dev/oradata/ctrl/control01.ctl',
 '/home/oracle/dev/oradata/ctrl/control02.ctl' scope=spfile;
step 2:-
 Copy the controlfile to duplicate.
 cp /home/oracle/dev/oradata/ctrl/control01.ctl /home/oracle/dev/oradata/ctrl/control03.ctl
step 3:-
 shut and startup the instance.
startup.

Parameters of a Controlfiles.

control_file_record_keep_time=7  (this parameter controlfiles how many day maintains information about Database)
control_files = '/ctl/control01.ctl' multiplex the controlfiles.

Toview controlfiles informations;-

V$CONTROLFILE  --Lists the names of control files

V$CONTROLFILE_RECORD_SECTION---Displays information about control file record sections
 get ckpinfo
--- select a.file#,a.checkpoint_change#,b.current_scn from v$datafile_header a,v$database b


To take  backup of a controlfiles:-
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp'; (it is binary backup)
alter database backup controlfile using trace as '/home/oracle/ctl.sql'; (it is text Backup)sql text

Output is sql controlfile backup.. backup
CREATE CONTROLFILE
   SET DATABASE prod
   LOGFILE GROUP 1 ('/home/oracle/prod/redo01_01.log',
                    '/home/oracle/prod/redo01_02.log'),
           GROUP 2 ('/home/oracle/prod/redo02_01.log',
                    '/home/oracle/prod/redo02_02.log'),
           GROUP 3 ('/home/oracle/prod/redo03_01.log',
                    '/home/oracle/prod/redo03_02.log')
   NORESETLOGS
   DATAFILE '/home/oracle/prod/system01.dbf' SIZE 3M,
            '/home/oracle/prod/rbs01.dbs' SIZE 5M,
            '/home/oracle/prod/users01.dbs' SIZE 5M,
            '/home/oracle/prod/temp01.dbs' SIZE 5M
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG;