Saturday, November 24, 2012

2 Oracle Database Control File

Each Oracle database has at least one control file. The control store the database default configuration, such as database file/log file location and size. If the database has more then 2 control files, both control files are identical. The main purpose for having more than 1 control file is for availability.

How to check the current database control file location 

show parameter control_file
select name from v$controlfile;
select value from v$system_parameter where name = 'control_files';


How to add control file for the database

  1. shutdown the oracle instance.
  2. Make an copy of the control file using OS command such as cp.
  3. update the pfile and add the new control file in the pfile.
  4. startup the oracle instance
  • Update spfile with adding the new control file with scope in spfile
  • shutdown oracle instance
  • Make an copy of the control file using OS command such as cp.
  • startup the oracle instance

V$spparameter shows the spfile content. show parameter and V$system_parameter reflect the memory setting.

show parameter control_file
alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl','/home/oracle/app/oracle/oradata/orcl/control03.ctl' scope=SPFILE;
select value from v$spparameter where name = 'control_files';


shutdown immediate
!cp /home/oracle/app/oracle/oradata/orcl/control01.ctl /home/oracle/app/oracle/oradata/orcl/control03.ctl


How to backup the control file

Offline backup
  • shutdown database
  • Make an copy of the control file using OS command such as cp.
Online backup

This example backup the control file to /tmp/backup.ctl

alter database backup controlfile to '/tmp/backup.ctl';



How to view the control file to the text format ?

The control file is the binary format. However, we can backup the control file as the text format to see.

alter database backup controlfile to '/tmp/backup.ctl';
alter database backup controlfile to trace as '/tmp/backup.ctl';
alter database backup controlfile to trace as '/tmp/backup.ctl' reuse;


ORA-01277 means the file already exist. Use “reuse” to overwrite the existing file.


The sample control file looks like this


How to fix “ORA-00205: Error in identifying control file, check alert log for more info”


This means the oracle instance can not read or find the control file. use show parameter control_file to find out the control file setting and check it on the OS level. The alert log should also provide the some information for diagnostic.


  1. Cartier ha raccomandato molteeconomici orologi serie di orologi classici e di successo, come Cartier Tank, Cartier Santos e Cartier Ronde. replica cartier tank solo orologi replica cartier tank mc orologi Ogni orologio Cartier ha il suo fascino unico e gli orologi Cartier sono il sogno di molte persone. Il nostro negozio offre diversi orologi Cartier a prezzi molto bassi. Adoro fare shopping qui.

  2. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. spy phone app



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates