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