Backing up Control File in Oracle

A control file is a small binary file that contains data describing the structure of

1. datafile names,
2. archive mode

and state

1. log sequence number,
2. checkpoint information

of the database. It must be available for writing by the Oracle server whenever the database is open. Without this file, the database cannot be mounted and recovery is impossible. The DBA should maintain a recent copy of the control file after every structural modification on the database.

Use the

ALTER DATABASE BACKUP CONTROLFILE TO <file_name>;

command to create a binary image of the control file. The filename is a fully specified filename that indicates the name of the new control file backup. To provide a binary copy of the control file use the following example:

SQL> ALTER DATABASE BACKUP CONTROLFILE 
  2  'c:\data\database\arch\control.bak';
Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 
Database altered.
SQL>

The trace option of the command helps manage and recover a control file. This command prompts Oracle to write SQL commands to a small trace file. The approximate size of the trace file for the `ALTER DATABASE BACKUP CONTROLFILE TO TRACE` command depends on a number of factors, including the 1) size and complexity of the control file, 2) the number of data files and redo log files in the database, and 3) the database version. However, it is generally a small file, typically in the range of a few kilobytes to a few megabytes. For example, the trace file for a simple database with a single data file and a single redo log file is typically around 10 kilobytes. The trace file for a more complex database with multiple data files and multiple redo log files may be several megabytes in size. You can estimate the size of the trace file by using the following formula:

trace_file_size = (control_file_size + data_file_size + redo_log_file_size) * 2

This formula multiplies the total size of the control file, data files, and redo log files by two to account for the fact that the trace file will contain SQL commands to both create and backup the control file. For example, the trace file for a database with a 100 MB control file, a 1 GB data file, and a 100 MB redo log file would be approximately 2.2 GB in size. Here are some tips for reducing the size of the trace file:

• Use the `ALTER DATABASE CREATE CONTROLFILE REUSE` clause to reuse the existing control file. This will eliminate the need to generate SQL commands to create a new control file.
• Use the `ALTER DATABASE BACKUP CONTROLFILE TO TRACE NOARCHIVELOG` clause to prevent Oracle from generating SQL commands to archive the redo log files. This will reduce the size of the trace file if the redo log files are not being archived.
• Use the `ALTER DATABASE BACKUP CONTROLFILE TO TRACE MINIMAL` clause to prevent Oracle from generating SQL commands to create a backup of the control file with all of its options. This will reduce the size of the trace file, but it may make it more difficult to restore the control file if it is corrupted.

If you are concerned about the size of the trace file, you can use the `USER_DUMP_DEST` parameter to specify a location where the trace file will be saved. This will allow you to monitor the size of the trace file and delete it if necessary.
The trace file contains only enough information to start a database and recreate the required control file. The actual control file is a 2-5M binary file. In case of complete loss of the control file, the DBA can copy the commands from the trace file into a script file, edit them as necessary, and use the script to re-create the control file and start the database. Of course, since the trace file does not contain the current database configuration, recovery is required.
Examine the comments created by Oracle in this trace file

*** SESSION ID:(12.239) 1998.04.16.07.59.28.312
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available. STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "P" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 100
    MAXINSTANCES 16
    MAXLOGHISTORY 1600
LOGFILE
  GROUP 1 'D:\DATA\DATABASE\LOG1P.ORA'  SIZE 5M,
  GROUP 2 'D:\DATA\DATABASE\LOG2P.ORA'  SIZE 5M,
  GROUP 3 'D:\DATA\DATABASE\LOG3P.ORA'  SIZE 5M,
  GROUP 4 'D:\DATA\DATABASE\LOG4P.ORA'  SIZE 5M
DATAFILE
  'D:\DATA\DATABASE\SYSP.ORA',
  'D:\DATA\DATABASE\RBLGP.ORA',
  'D:\DATA\DATABASE\TEMPP.ORA',
  'D:\DATA\DATABASE\USER1P.ORA',
  'D:\DATA\DATABASE\DES1P.ORA',
  'D:\DATA\DATABASE\DEI1P.ORA',
  'D:\DATA\DATABASE\TAS_A_P.ORA',
  'D:\DATA\DATABASE\TAS_B_P.ORA',
  'C:\DATA\DATABASE\RBS_P.ORA',
  'D:\DATA\DATABASE\FEAI_A_P.ORA';

Now you can see the command CREATE CONTROLFILE...in the trace file.

You do not want to lose a control file, so it is essential to protect it by creating more than one active control file. This procedure involves making an operating system copy of the control file with the database shut down. The name of the control file copy is then added to the

init.ora parameter control_files = (file1.ora, file2.ora).

When the database is restarted, Oracle will find and maintain both these control files. They should be located on different disks to protect against media failure.
To back up a database's control file, a DBA must have the ALTER DATABASE system privilege.

A DBA is strongly advised to back up the control file after each structural modification on the database.
Below is a list of commands that cause changes to a database configuration:

1. ALTER DATABASE [ADD | DROP] LOGFILE
2. ALTER DATABASE [ADD | DROP] LOGFILE MEMBER
3. ALTER DATABASE [ADD | DROP] LOGFILE GROUP
4. ALTER DATABASE [NOARCHIVELOG | ARCHIVELOG]
5. ALTER DATABASE RENAME FILE
6. CREATE TABLESPACE
7. ALTER TABLESPACE [ADD | RENAME] DATAFILE
8. ALTER TABLESPACE [READ WRITE | READ ONLY]
9. DROP TABLESPACE

The next lesson shows you how to monitor an open database backup.