Control file in oracle database:
As
I have already discussed about the database startup process and steps involved
for database to open. You can also get the same from below URL:
While
we start database, first it uses the parameter file and nomount the database,
then using control file mount the database and after checking if recovery is
required and performs the required step and then opens the database.
Control
file is compulsory for every database to mount. Control file is a binary file
which records the physical structure of database. While we think of control
file one question arises in our mind i.e.
What control file contains?
Control
file contains information like:
- The Database name.
- Names and locations of data
files and redo log files.
- The timestamp of database
creation.
- The current log sequence
number.
- Checkpoint information.
- Backup set and backup pieces
information.
Control
file is the critical file, which is required for database to startup. So to
avoid loss of control file, we manage to have multiple copies of control file.
When
we create database at least one control file is created with default values and
for some operating systems, by default multiple copies of control files are
created.
How to get the location of control file?
SQL> Show Parameter Control_Files;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files
string /u02/oracle/prod/control01.ctl
, /u02/oracle/prod/control02.ctl
, /u02/oracle/prod/control03.ctl
Create
control file:
Creating
Additional Copies, Renaming, and Relocating Control Files:
Multiplexing
copy of control file or renaming a control file steps:
1. Shut down the database.
2. Using operating system
command, copy the existing control file to new location.
3. Edit the CONTROL_FILES
parameter in the initialization parameter file to add new control file name or
to change the existing control file name.
4. Restart the database.
Manually
Creating New Control Files:
Create
Control file statement can be used to create a new control file for a database
and also we can use the parameter values as per our requirement. We can give
the database name whatever we want.
CREATE
CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
Back Up Control Files:
Back up of control file is very important and It happens by default when you
change your physical structure of your database. Some examples of structure
changes are:
1. Adding, dropping, or
renaming datafiles.
2. Adding or dropping a
tablespace.
3. Altering the read-write
state of the tablespace.
4. Adding or dropping redo
log files or groups.
Back
up control file using the below command from SQL prompt and stores it into
binary format:
SQL>ALTER
DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
We can
also backup our control file to trace location:
SQL>ALTER DATABASE
BACKUP CONTROLFILE TO TRACE;
When to create
control file?
- When you have lost your all
control files.
- When you want to rename your
database.
Dropping Control
Files:
Sometimes the location of cotrol file is changed, in such situation need to
drop the existing control file and use the correct one. Control file can be
dropped or renamed very easily as below steps.
1. Edit the CONTROL_FILES
parameter in the initialization parameter file to delete old control file name.
2. Shut down the database.
3. Start the database.
Reusable section of control file (control_file_record_keep_time)
:
Control file has reusable section and it tracks ARCHIVE logs and BACKUP sets.
control_file_record_keep_time is the initialization parameter which determines
the reussable data to be stored for number of days. Default value of
control_file_record_keep_time is 7 days. It can also be increased to any value
whatever we want.
We can check the reusable period from SQL prompt as below:
SQL>
show parameter Keep_time;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time
integer 7
Changing the value of control_file_record_keep_time using SQL
command:
SQL>
alter system set control_file_record_keep_time=14 scope=spfile;
System
altered
Now
RMAN can keep records of BACKUPS and ARCHIVE logs for 14 days.
Also
need to configure control file autobackup on.
CONFIGURE
CONTROLFILE AUTOBACKUP ON;