Managing Control Files Contents What Is a Control

Managing Control Files

Contents What Is a Control File? Creating Control Files Backing Up Control Files Dropping Control Files

What Is a Control File? Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes: The database name Names and locations of associated data files and redo log files The timestamp of the database creation The current log sequence number Checkpoint information The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

Creating Control Files Creating Initial Control Files Creating Additional Copies, Renaming, and Relocating Control Files Creating New Control Files

Creating Initial Control Files The initial control files of an Oracle Database are created when you issue the CREATE DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified and are operating system specific. The following is an example of a CONTROL_FILES initialization parameter: CONTROL_FILES = (/u 01/oracle/prod/control 01. ctl, /u 02/oracle/prod/control 02. ctl, /u 03/oracle/prod/control 03. ctl)

Creating Additional Copies, Renaming, and Relocating Control Files You can create an additional control file copy for multiplexing by copying an existing control file to a new location and adding the file name to the list of control files. Similarly, you rename an existing control file by copying the file to its new name or location, and changing the file name in the control file list. In both cases, to guarantee that control files do not change during the procedure, shut down the database before copying the control file. To add a multiplexed copy of the current control file or to rename a control file: 1. Shut down the database. 2. Copy an existing control file to a new location, using operating system commands. 3. Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename. 4. Restart the database.

Creating New Control Files When to Create New Control Files It is necessary for you to create new control files in the following situations: All control files for the database have been permanently damaged and you do not have a control file backup. You want to change the database name. For example, you would change a database name if it conflicted with another database name in a distributed environment.

The CREATE CONTROLFILE Statement You can create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the prod database (a database that formerly used a different database name):

Steps for Creating New Control Files 1. Make a list of all data files and redo log files of the database. SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE; SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files'; 2. Shut down the database. If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last option. 3. Back up all data files and redo log files of the database. 4. Start up a new instance, but do not mount or open the database: STARTUP NOMOUNT 5. Create a new control file for the database using the CREATE CONTROLFILE statement. 6. Store a backup of the new control file on an offline storage device. 7. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file).

8. Recover the database if necessary. If you are not recovering the database, skip to step 9. 9. Open the database using one of the following methods: If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally. ALTER DATABASE OPEN; If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS. ALTER DATABASE OPEN RESETLOGS;

Backing Up Control Files Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two options: Back up the control file to a binary file (duplicate of existing control file) using the following statement: ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control. bkp'; Produce SQL statements that can later be used to re-create your control file: ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Dropping Control Files You want to drop control files from the database, for example, if the location of a control file is no longer appropriate. Remember that the database should have at least two control files at all times. 1. Shut down the database. 2. Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name. 3. Restart the database.