The Core of Oracle Database: Control File Backup Procedures

Control Files_en

In an Oracle Database, the Control File acts as both the “family tree” and the “map” of the database, making it one of the most critical binary files. If this file is lost, the database cannot be started, even if the data files remain intact.

This article provides a beginner-friendly explanation of the importance of control files and details specific backup and recovery procedures using SQL*Plus and RMAN (Recovery Manager).

1. What is a Control File? (Role and Importance)

The control file records the physical structure of the database and is constantly referenced during mounting and execution.

Primary Management Information:

  • Names and locations of data files and REDO log files
  • Database name and timestamp of creation
  • Current SCN (System Change Number) information
  • Backup metadata (when using RMAN)
  • Archivelog records

2. How to Back Up the Control File

There are two main methods for backing up the control file: using SQL*Plus or automating with RMAN.

2.1 Backup Using SQL*Plus

SQL*Plus allows you to obtain backups in two formats: “Text (Trace)” and “Binary.”

(1) Output to a Trace File (Recreation Script)

This outputs the SQL statements required to recreate the control file in text format. Even if all control files and backups are lost, you can redefine the structure using this script.

-- Check the current location of the trace file
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';

-- Output the backup to a trace file
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Example of output (excerpt): Inside a file like /u01/app/oracle/diag/.../trace/v19_ora_8896.trc, a CREATE CONTROLFILE statement is recorded as follows:

[oracle@v19single ~]$ cat /u01/app/oracle/diag/rdbms/v19/v19/trace/v19_ora_8896.trc
Trace file /u01/app/oracle/diag/rdbms/v19/v19/trace/v19_ora_8896.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

:
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 2
-- Replace * with correct password.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/backup1/%U'',   ''/u01/app/oracle/backup2/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_1_1153872185.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/V19/temp01.dbf'
     SIZE 165675008  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "V19" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/V19/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/V19/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/V19/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/V19/system01.dbf',
  '/u01/app/oracle/oradata/V19/sysaux01.dbf',
  '/u01/app/oracle/oradata/V19/undotbs01.dbf',
  '/u01/app/oracle/oradata/V19/users01.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 2
-- Replace * with correct password.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/backup1/%U'',   ''/u01/app/oracle/backup2/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_1_1153872185.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/V19/temp01.dbf'
     SIZE 165675008  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

(2) Obtaining a Binary Copy

This copies the current control file exactly as it is to a different location.

-- Copy in binary format to a specified path
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/backup/control01.ctl';

2.2 Backup Using RMAN (Recommended)

Using RMAN significantly reduces operational effort.

(1) Manual Backup

# Back up the current control file individually
RMAN> BACKUP CURRENT CONTROLFILE;

# Include the current control file in a full database backup
RMAN> BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;

(2) Enabling Autobackup

When this is enabled, RMAN automatically backs up the control file whenever a structural change occurs (such as adding a file) or when a backup is executed. This is the most recommended setting.

# Set control file autobackup to ON
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

3. Restore and Recovery of the Control File

These are the recovery procedures for when a control file is corrupted or lost.

3.1 Recovery from a Binary Backup (SQL*Plus)

This is a simple method of moving the backup back to its original location using OS commands.

  1. Stop the instance.
  2. Copy (overwrite) the backup file using the original control file name.
  3. Open the database.
# Example procedure
$ sqlplus / as sysdba
SQL> SHUTDOWN ABORT;
SQL> !cp /backup/control01.ctl /u01/app/oracle/oradata/V19/control01.ctl
SQL> STARTUP;

3.2 Restore Using RMAN (Using Autobackup)

You can recover even if no control files exist (from a NOMOUNT state).

# Start the instance in NOMOUNT mode
RMAN> STARTUP NOMOUNT;

# Restore the control file from an autobackup
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

# Change the database to MOUNT state
RMAN> ALTER DATABASE MOUNT;

# Execute recovery
RMAN> RECOVER DATABASE;

# Open with RESETLOGS
RMAN> ALTER DATABASE OPEN RESETLOGS;

4. Operational Considerations and Risks

  • Multiplexing Check: Control files should typically be multiplexed (copied) across multiple disks (configured via the CONTROL_FILES parameter).
  • Risk: If all copies are destroyed simultaneously, a RESTORE or CREATE CONTROLFILE becomes necessary, increasing downtime.
  • Reverting: If you accidentally change the structure with CREATE CONTROLFILE, perform a RESTORE from the most recent binary backup.

FAQ: Frequently Asked Questions

Q: Which is more important: the trace file (TRACE) or the binary copy? A: Both are important. Usually, a binary copy allows for faster recovery, but a trace file is essential if you need to recreate the file while changing the path configuration.

Q: Will control file corruption appear in the alert.log? A: Yes. I/O errors and other issues will be recorded, and in many cases, the database will terminate abnormally (instance crash).

Q: Where are autobackups (AUTOBACKUP) stored? A: By default, they are saved in the Fast Recovery Area (FRA) or the dbs (database on Windows) directory.

Summary

  • The control file is the most critical file governing the database structure.
  • In SQL*Plus, use TO TRACE for recreation scripts and TO 'path' for binary copies.
  • In RMAN, the AUTOBACKUP ON setting should always be enabled.
  • In an emergency, recover via RESTORE from backup or CREATE via trace.

This article is based on Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database Backup and Recovery Reference, 19c

What is RMAN? A Complete Guide to the Basics of Oracle's Standard Backup & Recovery Tool!
When handling Oracle Database, designing Oracle backup and recovery is unavoidable. The central role in this is played b...
oracle-master00000.com
2025.08.12

コメント

Copied title and URL