Rebuilding the Control File

This topic describes how to rebuild the control file.

Most adjustments to an Oracle configuration can be made by editing a value in the init<SID>.ora, and then stopping and restarting the DB so that the value is incorporated. However, certain settings can only be modified by recreating the database or by recreating the control files. These settings include: maxdatafiles, maxlogfiles, character_set, db_block_size, and maxlogmembers.

The settings for MAXLOGFILES and MAXDATAFILES are upper limit values. However, actual values may be adjusted on the fly in init<SID>.ora using DB_FILES or MAX_LOG_MEMBERS, but they are clamped by the upper limit set at controlfile/database-creation time.

Note: Before rebuilding the control file, we recommend that you generate a complete backup of the entire system. Also, be sure all users are logged off before proceeding.

For background and more complete information, refer to the Oracle Server Administrator's Guide and the Oracle DBA Handbook.

To reconfigure a control file:

  1. Open SVRMGR, SQL*Plus, or SQL Worksheet and log in as an Oracle database administrator (e.g. system/manager).

  2. Generate the list of current controlfile parameters using the following command: 

    alter database DBNAME backup controlfile to trace;

    where DBNAME is the name in the init<SID>.ora file. For example: 

    alter database MATRIX backup controlfile to trace;

    This results in the most current .trc file in the \orant\rdbms73\trace directory being updated with the exact commands you will need to run. Open this file in a text editor.

  3. Select the section that starts with STARTUP (NO)MOUNT and ends with the ; after the last datafile name. Copy and paste to a text file, adding a ; after the STARTUP MOUNT. (Oracle left it out).

  4. You may modify any of the MAX parameters, for example, MAXDATAFILES. You may also add lines for CHARACTER_SET or DB_BLOCK_SIZE. Save the file.

    Example of a modified file: 

    STARTUP NOMOUNT; 
CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS NOARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 3     
     MAXDATAFILES 200
     MAXINSTANCES 16
     MAXLOGHISTORY 1600 
LOGFILE
   GROUP 1 'E:\ORANT\DATABASE\LOG2ORCL.ORA'  SIZE 10M,
   GROUP 2 'E:\ORANT\DATABASE\LOG1ORCL.ORA'  SIZE 10M
DATAFILE
   'E:\ORANT\DATABASE\SYS1ORCL.ORA',
   'E:\ORANT\DATABASE\USR1ORCL.ORA',
   'E:\ORANT\DATABASE\RBS1ORCL.ORA',
   'E:\ORANT\DATABASE\TMP1ORCL.ORA',
   'E:\ORCLTEST\MATRIX\ORCL1MX.DBF',
   'E:\ORCLTEST\MXDEMO\ORCL1MXD.DBF',
   'E:\ORCLTEST\CUSTTEST\ORCL1MXC.DBF'
   'E:\ORCLTEST\MXJUNK\ORCL1MXJ.DBF'

  5. In the server manager (MS DOS Command Prompt, svrmgr23), connect as the database administrator and do a shutdown immediately.

    Note: Last Chance: At the OS level, make sure you have backups of everything!

  6. At the OS level, delete your control file(s) as referenced in the initialization file.

  7. In the server manager, run the text file that you modified and saved by entering: 

    @bldcontrol.sql

  8. Once "statement processed" comes up, enter: 

    alter database open;

  9. Rerun the alter database backup controlfile command (from Step 2) and verify that the latest trc has any new settings. The changed settings will be appended as the last entry in the latest trc, so look near the end of the file.

  10. Test connecting to Oracle with ENOVIA Live Collaboration and verify that functionality is restored.