Lesson 9	Backing up the control file
Objective	Explain the methods and commands to back up the control file.

Backing up Control File in Oracle

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

datafile names,
archive mode

and state

log sequence number,
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.

Commands used to back up control file

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>

trace option

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 trace file is a 2K text file, containing 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.

Maintaining multiple current control files

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.

Commands that change database structure

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:

ALTER DATABASE [ADD | DROP] LOGFILE
ALTER DATABASE [ADD | DROP] LOGFILE MEMBER
ALTER DATABASE [ADD | DROP] LOGFILE GROUP
ALTER DATABASE [NOARCHIVELOG | ARCHIVELOG]
ALTER DATABASE RENAME FILE
CREATE TABLESPACE
ALTER TABLESPACE [ADD | RENAME] DATAFILE
ALTER TABLESPACE [READ WRITE | READ ONLY]
DROP TABLESPACE

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

Ad Oracle RMAN Backup and Recovery