ORACLE : Managing Controlfiles

Managing Controlfiles

Controlfile:

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 datafiles 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 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.
Multiplex controlfile Using PFILE
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.
Example : open the pfile
Edit the controlfile parameter (control_files = ‘ Location1‘ , ‘Location2 ‘ ,’Location3 ‘ we have to give all control files location )
4. Restart the database.
SQL> Startup
Multiplex controlfile Using SPFILE
The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA

1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.

SQL> ALTER SYSTEM SET control_files='c:\control01.ctl'
,'c:\control02.ctl', 'c:\control03.ctl' scope=spfile;

2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.

SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.
Example
copy and paste the control files to new location (Windows)
mv /ua1/control01.ct /ua2 (Unix)

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL>STARTUP;
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.
Displaying Control File Information
View Description
V$DATABASE Displays database information from the control file
V$CONTROLFILE Lists the names of control files
V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections
V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter