Making User-Managed Backups of the Control File

B ack up the control file of a database after making a structural modification to a database operating in ARCHIVELOG mode. To back up a database's control file, you must have the ALTER DATABASE system privilege.

Backing Up the Control File to a Binary File

The primary method for backing up the control file is to use a SQL statement to generate a binary file. A binary backup is preferable to a trace file backup because it contains additional information such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). Note that binary control file backups do not include tempfile entries.

To back up the control file after a structural change:

Make the desired change to the database. For example, you may create a new tablespace:
```
CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;
```
Back up the database's control file, specifying a filename for the output binary file. The following example backs up a control file to /disk1/backup/cf.bak:
```
ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/backup/cf.bak' REUSE;
```
Specify the REUSE option to make the new control file overwrite one that currently exists.

Backing Up the Control File to a Trace File

To back up the control file to a trace file, mount or open the database and issue the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the resulting trace file contains versions of the control file for both RESETLOGS and NORESETLOGS options. Tempfile entries are included in the output using "ALTER TABLESPACE... ADD TEMPFILE" statements.

Backing Up the Control File to a Trace File: Example

Assume that you want to generate a script that re-creates the control file for the sales database. The database has these characteristics:

Three threads are enabled, of which thread 2 is public and thread 3 is private.
The redo logs are multiplexed into three groups of two members each.
The database has the following datafiles:
- /diska/prod/sales/db/filea.dbf (offline datafile in online tablespace)
- /diska/prod/sales/db/database1.dbf (online in SYSTEM tablespace)
- /diska/prod/sales/db/fileb.dbf (only file in read-only tablespace)

You issue the following statement to create a trace file containing a CREATE CONTROLFILE ... NORESETLOGS statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

You then edit the trace file to create a script that creates a new control file for the sales database based on the control file that was current when you generated the trace file. To avoid recovering offline normal or read-only tablespaces, edit them out of the CREATE CONTROLFILE statement in the trace file. When you open the database with the re-created control file, the dictionary check code will mark these omitted files as MISSING. You can run an ALTER DATABASE RENAME FILE statement renames them back to their original filenames.

For example, you can edit the CREATE CONTROLFILE ... NORESETLOGS script in the trace file as follows, renaming files labeled MISSING:

# The following statements will create a new control file and use it to open the 
# database. Log history and RMAN metadata will be lost. Additional logs may be 
# required for media recovery of offline datafiles. Use this only if the current 
# version of all online logs are available.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
    GROUP 2 
       '/diska/prod/sales/db/log2t1.dbf',
        '/diskb/prod/sales/db/log2t2.dbf'
    ) SIZE 100K,
    GROUP 3 
       '/diska/prod/sales/db/log3t1.dbf',
       '/diskb/prod/sales/db/log3t2.dbf'
    ) SIZE 100K
DATAFILE
    '/diska/prod/sales/db/database1.dbf',
    '/diskb/prod/sales/db/filea.dbf'
;

# This datafile is offline, but its tablespace is online. Take the datafile 
# offline manually.
ALTER DATABASE DATAFILE '/diska/prod/sales/db/filea.dbf' OFFLINE;

# Recovery is required if any datafiles are restored backups,
# or if the most recent shutdown was not normal or immediate.
RECOVER DATABASE;

# All redo logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

# The database can now be opened normally.
ALTER DATABASE OPEN;

# The backup control file does not list read-only and normal offline tablespaces 
# so that Oracle can avoid performing recovery on them. Oracle checks the data 
# dictionary and finds information on these absent files and marks them
# 'MISSINGxxxx'. It then renames the missing files to acknowledge them without 
# having to recover them.
ALTER DATABASE RENAME FILE 'MISSING0002'
     TO '/diska/prod/sales/db/fileb.dbf';