RelationalDBDesign RelationalDBDesign


Physical Backups  «Prev  Next»
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
  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.

Commands used to back up the 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>

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.
Oracle RMAN Backup and Recovery

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:
  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.