The CREATE CONTROLFILE Command

It is possible to create a control file, not only at database creation time but also after the database is created and running. It can be created indirectly by using the BACKUP CONTROLFILE TO TRACE command or by the BACKUP CONTROLFILE command, or it can be created directly by the CREATE CONTROLFILE command.

The CREATE CONTROLFILE command allows you to create a control file from scratch. It is important that you have a complete detailed understanding of the database and its contents to use it correctly.

The CREATE CONTROLFILE command is used to re-create a control file whenever all viable copies of the existing control files have been lost because of media failure; whenever you need to change the name of the database; or whenever you need to change the maximum number of redo log file groups, the maximum number of redo log file members, the maximum number of data files, or the current number of instances that can concurrently have the given database mounted and open for operation.

Not only does Oracle recommend backing up the database before you re-create the control files, but it's a good idea to perform a full database backup of all database files before you use the CREATE CONTROLFILE command. This allows you to get back to where you are if anything in the process should fail.

The general syntax for the CREATE CONTROLFILE command follows:

 CREATE CONTROLFILE [REUSE]     [SET] DATABASE database     LOGFILE [GROUP integer] filespec [, [GROUP integer] filespec] ...     {RESETLOGS | NORESETLOGS}     DATAFILE filespec [, filespec] ...     [MAXLOGFILES integer]     [MAXLOGMEMBERS integer]     [MAXLOGHISTORY integer]     [MAXINSTANCES integer]     [ARCHIVELOG | NOARCHIVELOG]

CREATE CONTROLFILE Parameters and Options

The definition of the commands and parameters are as follows:

CREATE CONTROLFILE is the command that allows Oracle to create the new control file.
REUSE specifies to the create command that any existing control files that are identified by the initialization parameter CONTROL_FILES can be reused. This means that these files will be overwritten with the new parameters, so make sure that it is really what you want. Whenever I have to take this step, I execute a BACKUP CONTROLFILE TO TRACE command as it stands, so it is easy to get back to this point. If you omit the REUSE command, and the control files in the initialization file exist, the create command will return an error and fail.
SET DATABASE is an optional parameter and should only be used when you are changing the name of the database. This is useful in cloning one database to another where you want to carry the target's name with the source's data. The name of the database can be up to eight characters.
DATABASE is used when you are creating a control file and using the existing database's name. The value that you provide for this parameter must be the existing database name established either by the previous CREATE DATABASE command or a previously run CREATE CONTROLFILE command.
LOGFILE specifies the redo log file groups and log file members for your database. You must include in this list all the members of all redo log file groups, and all files specified must exist.
RESETLOGS command causes the CREATE CONTROLFILE to ignore the contents of the log files listed in the LOGFILE clause of the initialization file. Each file spec in the LOGFILE clause must specify the SIZE parameter for the CREATE CONTROLFILE command to be successful. After using this option in the CREATE CONTROLFILE command, you must open the database with the RESETLOGS option of the ALTER DATABASE OPEN command.
NORESETLOGS command causes all the files in the LOGFILE clause of the initialization file to be used as they were when the database was last open. This means that the files must be the current redo log files from right before the database was shut down (or crashed) and not ones restored from backup. Oracle reassigns the redo log file groups to the processes to which they were assigned when they were previously open. If you specify the GROUP values, Oracle also verifies these values with the actual values when the database was last open.
DATAFILE specifies all the data files of the database. You must specify all the data files that were in the database when it was last open. The files specified must exist in the operating system. They can be files that have been restored from backup, although if they are files that have been restored, you will have to perform media recovery on these files before the database will open. Media recovery refers to the application (or reapplication) of redo to restore a data file or individual data block to a specified time. Data file media recovery always begins at the lowest SCN recorded in the data file headers and rolls forward to the current SCN.

MAXLOGFILES specifies the maximum number of redo log file groups that can ever be created in the database. The Oracle server uses this value to determine how much space in the control file needs to be allocated for the names of redo log files. The default number assigned to MAXLOGFILES is operating system dependent should the parameter not be included in the CREATE CONTROLFILE command. The value that you assign to this parameter should not be a lower value than the greatest GROUP value for current redo log file groups.

MAXLOGFILES is not the only parameter that governs the number of redo log file groups accessible to the instance. The LOG_FILES parameter in the initialization parameter file helps to govern the number as well because it limits the files created to the files listed in the parameter.

If the maximum number of log files required for your database changes, and MAXLOGFILES is too low, either the database or, minimally, the control file will have to be re-created to reflect the requirements.

MAXLOGMEMBERS is the parameter that specifies the maximum number of copies, or members, of files for a redo log file group. This value is used by Oracle to help determine how much space in the control file to allocate to use for the names of the redo log files. The minimum number is 1; the maximum value for this parameter is operating system dependent.

MAXLOGHISTORY provides information on the maximum number of redo log file groups for automatic media recovery. Again, Oracle uses the value of this parameter to help to determine how much space to allocate inside the control file for the names of archived redo log files. The minimum number for this parameter is 0. The maximum value is limited only by the maximum size of the control file. The default value for this parameter is a multiple of the MAXINSTANCES parameter value and varies depending on your operating system.

MAXLOGHISTORY is particularly useful if you are using Oracle with the Parallel Server option in both Parallel and Archivelog mode but can also be beneficial in assisting with recovery in a RAC environment where there are many instances and many log files.

MAXDATAFILES specifies the maximum number of data files that can be created in your database. The minimum value is 1 (however, this in really not a practical number). The maximum and default values for this parameter vary based on your operating system. Whatever value you specify for this parameter must not be lower than the total existing number of data files that have ever been in the database, including those connected to any tablespaces that may have been dropped.

The number of data files accessible to your instance is limited not only by MAXDATAFILES, it is also limited by the initialization parameter DB_FILES.

MAXDATAFILES is the maximum number of data files that can be in a control file. When that number is reached, the control file needs to be re-created. The DB_FILES initialization parameter can be used to set an artificial limit to the number of data files that can be associated with a database. In this case, after the number of files has been reached, it is necessary to change the value of the parameter and restart the database and its instance.

MAXINSTANCES is the maximum number of instances that can simultaneously have the given database mounted and open. This value takes precedence over even the value of the initialization parameter INSTANCES. The minimum value for this parameter is 1 because your database wouldn't be much good if you didn't have at least one instance able to open it. The maximum and default values are, again, operating system dependent.
This parameter is really only applicable in a RAC environment.
ARCHIVELOG parameter signals Oracle that the database should be opened with archiving turned on, meaning that the contents of every redo log file should be archived before reusing them. ARCHIVELOG allows for the more elegant possibility of media recovery and instance recovery.
NOARCHIVELOG tells Oracle that it should allow for the reuse of redo log files without first archiving their contents. NOARCHIVELOG allows for the ability to do instance recovery elegantly, but media recovery becomes more difficult. If you omit both the ARCHIVELOG and NOARCHIVELOG keywords, Oracle chooses to use NOARCHIVELOG mode by default. You can change this behavior after the new control file creation by use of the ALTER DATABASE command.

Before you can run CREATE CONTROLFILE, you need to be logged in with SYSDBA privileges, and the database in question must not be mounted by any instance.

CREATE CONTROLFILE Troubleshooting

After you create your new control file and after you have used it to open the database, always check the alert file (located, remember, in the BACKGROUND_DUMP_DESTINATION) to see whether Oracle detected any inconsistencies between what the data dictionary includes but that does not appear in the control file.

If a data file happens to exist in the data dictionary but does not appear in the new control file, Oracle creates a placeholder entry in the control file with the naming convention MISSINGnnnn, where nnnn is the file number in decimal format. This file, MISSINGnnnn, is flagged in the control file as being offline, because the control file doesn't understand where or what it really is, and is in need of media recovery. The actual data file that corresponds to the MISSINGnnnn file can be made to be accessible by renaming the MISSINGnnnn so that it really points to the data file. This is only possible if the data file was read-only or offline normal at the time of startup.

If the file MISSINGnnnn does not correspond to a data file that was not either read-only or offline normal, the rename operation cannot be used to make the data file accessible. This is because the data file in question requires media recovery. Because there is inconsistency between the RESETLOGS SCN and the RESETLOGS time stamp in the data file and the control file, the file needs to be recovered. This is one of the reasons that you have to be careful when you manually create the control file with the CREATE CONTROLFILE command, because now this tablespace and its file (or files) are no longer viable but have to be offline dropped and re-created.

If, on the other hand, you have created a reference in the control file that is not present in the data dictionary, Oracle removes the references to it from the control file.

In all these cases, Oracle places a message in the alertSID.log file explaining what was found, what was not found, and what it decided to do about it. These files, unless changed in the initSID.ora file, can be found in the $ORACLE_HOME/admin/SID/bdump directory under Unix or the %ORACLE_HOME%/admin/SID/bdump on Windows.

On the other hand, Oracle will often be gentle enough to send you an error when you attempt to mount the database and open it after creating the new control file (typically an ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216). This most often happens because you have omitted a file from the CREATE CONTROLFILE command or have included one that shouldn't be included. If you get one of these errors, you can just go back and alter the DATAFILES section of the CREATE CONTROLFILE command, either adding a missing one or removing an errant one, and then rerun the script.

If you want to be sure that you always get exactly the files and tablespaces found in the data dictionary, you should query the data dictionary to determine what it believes needs to be in the control file. You can get the tablespace name, filename, and sizing information from the DBA_DATA_FILES view that will help you to make sure that you have included exactly what is in the data dictionary when creating the control file.

Because the DBA_DATA_FILES is not one of the dynamic (or V$) views, the information in this view is not available to you unless the database is mounted and open. It is important that you retrieve the information needed from this view before you need to re-create the control file because the database will be inaccessible when the previous control file is gone.