Managing Control Files
The Control File can be considered to be the most important file in a database, it is the identity of the database.
Every database instance has at least 1 control file, the recommended practice is to have at least 2 control files located on separate physical disks. 3 control files are created by default.
The contents of a database’s Control File include:
• The names and locations (paths) of Data Files
• The names and locations of Redo Logs
• Current Log sequence numbers
• Backup Set information
& most importantly
• System Change Number (SCN) information
Users cannot view or alter a Control File as it is in binary format.
Making copies of Control Files
Control Files can be copied in two ways.
1. While the database is open
If a Control File’s location hasn’t been specified while creating the database, all 3 control files will be in the same path (/u01/app/oracle/oradata/SID_NAME/)
|
1 2 3 4 5 6 7 |
ALTER SYSTEM SET control_files = '/u01/app/oracle/oradata/smp/control01.ctl', '/u01/app/oracle/oradata/smp/control02.ctl', '/u01/app/oracle/oradata/smp/control03.ctl', '/u02/IndexData/smp/control04.ctl' scope=spfile; |
|
1 |
shutdown immediate |
|
1 |
[oracle@rhel ~]$ cp /u01/app/oracle/oradata/smp/control01.ctl /u02/IndexData/smp/control04.ct |
|
1 |
startup |
Checking the validity of the changes that have been made:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/smp/control01.ctl /u01/app/oracle/oradata/smp/control02.ctl /u01/app/oracle/oradata/smp/control03.ctl /u02/IndexData/smp/control04.ctl |
Changes can also be checked with:
|
1 |
SQL> show parameter control_files ; |
2. Using the Pfile (Parameter file) to make copies
|
1 |
shutdown immediate |
The following code creates a pfile.
|
1 |
create pfile from spfile; |
“initSID_NAME.ora” in “/u01/app/oracle/product/10.2.0/db_1/dbs/”. In this example we have named the pfile initSMP.ora.
|
1 |
[oracle@rhel ~]$ cp /u01/app/oracle/oradata/smp/control01.ctl /u03/smp/control05.ctl |
Using Vi or WinSCP’s text editor we can create the following configuration according our requirements:
|
1 |
*.control_files='/u01/app/oracle/oradata/smp/control01.ctl','/u01/app/oracle/oradata/smp/control02.ctl','/u01/app/oracle/oradata/smp/control03.ctl','/u02/IndexData/smp/control04.ctl','/u03/smp/control05.ctl' |
Create an spfile from the pfile after finishing working on the pfile.
|
1 2 3 |
create spfile from pfile; startup |
The database can be opened and checked using the following syntax.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/smp/control01.ctl /u01/app/oracle/oradata/smp/control02.ctl /u01/app/oracle/oradata/smp/control03.ctl /u02/IndexData/smp/control04.ctl /u03/smp/control05.ctl |
If the dbms is unable to open a control file then it cannot open the database. Suggested solutions to this type of issue:
• Delete the problematic control file, replace it with another functioning one and rename it.
• If the other control files are in working order, delete the control file from the spfile (by using the same method as creating a pfile).
Accessing Controlfile information
V$CONTROLFILE : Contains the names and statuses of controlfiles.
V$PARAMETER : Statuses and locations/paths.
V$CONTROLFILE_RECORD_SECTION : Datafiles and the objects that they contain (such as record_size, records_total)
The performance view information listed below can be accessed through a control file:
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATABASE
