Oracle control files: locate, back up and multiplex
Find the control file locations, back up their definition to a trace or a script, and add a copy by changing control_files in the spfile.
The control file records the physical structure of the database: the datafiles, the redo log files and the current state. Oracle recommends keeping several copies on different disks. This article covers how to find the control files, back up their definition, and add a copy by changing the control_files parameter.
Find the control files
select name from v$controlfile;
show parameter control_files;
The column formatting can be set before show parameter for readable output:
set linesize 400
column NAME_COL_PLUS_SHOW_PARAM format A40
column TYPE format A20
column VALUE_COL_PLUS_SHOW_PARAM format A100
show parameter control_files;
Find the diagnostic trace file, which receives the trace-based backup below:
select * from V$DIAG_INFO where name='Default Trace File';
Back up the control file definition
Write a CREATE CONTROLFILE script to the trace file:
alter database backup controlfile to trace;
Write the same script to a named file, replacing an existing file:
alter database backup controlfile to trace as '/backup/oracle/ctl.txt' reuse;
Add a copy of the control file (multiplexing)
Several copies of the control file in different locations protect against loss by corruption or accidental removal. Multiplexing is set in the control_files parameter.
If the instance uses a text parameter file, edit CONTROL_FILES in $ORACLE_HOME/dbs/init<SID>.ora and replace the bad location with the new one.
With an spfile, list every copy and apply the change at the next restart:
alter system set control_files=
'/u01/app/oracle/oradata/ORCL/control01.ctl',
'/u01/app/oracle/oradata/ORCL/control02.ctl'
scope=spfile;
Recreate a control file
To rebuild a damaged control file set, see how to recreate a control file in an Oracle database.