1.5 How do I…Create a new control file?

Problem

I have an existing Oracle instance for which I want to modify one or more of the permanent database settings, such as the database name or the maximum number of redo log files. How do I create a new control file so that I can change the value of one or more of these permanent parameters?

Technique

Use the alter database command to generate a script summarizing the current state of the control file. Modify the script to reflect the desired changes to the permanent database parameters. Run the modified script to create a new control file.

Steps

1. Use Server Manager or Enterprise Manager to disconnect all user sessions. An easy, if unfriendly, way to do this is to shut down the database in Immediate mode after warning currently connected users.

2. Restart the database in Restricted mode by issuing the startup restrict command from Server Manager.

3. Enter the following alter database command.

alter database backup controlfile to trace;

This command creates a script that will generate a new control file. Under Windows NT, the script resides in a trace file that in turn resides in the subdirectory pointed to by initialization parameter user_dump_dest. The name of the trace file under Windows NT is of the form ORAxxxxx.TRC where xxxxx is a five-digit number equal to the Windows NT thread ID. If you are not using Windows NT, review your operating-system–specific documentation to ascertain the location of this trace file.

4. Open the trace file using any ASCII editor and modify the values of the permanent database parameters in the create controlfile command to the desired new values. Save this modified create controlfile command to another file. An example of a script containing a create controlfile command appears in Listing 1.2. In this case, the command will change the MAXDATAFILES parameter from the default value of 32 to a new value of 100.

Listing 1.2 The create controlfile statement

.......MAXLOGFILES 32
.......MAXLOGMEMBERS 2
.......MAXDATAFILES 100
.......MAXINSTANCES 1
.......MAXLOGHISTORY 449
LOGFILE
..GROUP 1 ‘C:\ORANT\DATABASE\LOG1O8HT.ORA’ SIZE 200K,
..GROUP 2 ‘C:\ORANT\DATABASE\LOG2O8HT.ORA’ SIZE 200K
DATAFILE
..‘C:\ORANT\DATABASE\SYSO8HT.ORA’;

5. Use Server Manager to shut down the database, and then start the instance again but don’t mount it.

6. Execute the script saved in Step 4 to create a new control file (or control files, if the instance uses a multiplexed control file).

7. Open the database by issuing the alter database open command from Server Manager.

How It Works

Steps 1 and 2 disconnect all users from the database and open the database in Restricted mode so that only users with restricted session privilege (presumably, database administrators only) can connect to the instance. This will prevent any database changes that can invalidate the control file script you created in Step 1. Steps 3 and 4 generate a script to create a new version of the control file with new values for some or all of the permanent database parameters. After the script is run in Step 6 and the database is opened in Step 7, the new database parameters are in effect.

Comments

It is difficult and tedious to manually generate create controlfile statements. It is also potentially hazardous because mistakes in this command can result in corrupted data files. If possible, it is a good idea to regularly run the statements in Steps 1 through 3, even if it is not immediately necessary to create a new control file. Some sites run these statements as part of scheduled jobs or database shutdown scripts. The resulting create controlfile script summarizes all the physical files comprising the database and can be useful during a database recovery effort.

This is the simplest example of creating a new control file, particularly because the Oracle8 instance in question is not running in Archive Log mode. If your database is in Archive Log mode, review your documentation, especially with regard to the resetlogs option of the create controlfile command.