Saturday, November 24, 2012

0 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';

image

How to add control file for the database

Pfile:
  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
spfile:
  • 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';

image

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

image

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';

 

image

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.

image

The sample control file looks like this

image

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

image

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.

0 comments:

Post a Comment

 

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