|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02
Oracle recommends that you perform a full backup of all files in the database before using this statement. For more information, see Oracle9i User-Managed Backup and Recovery Guide.
CONTROLFILE statement to re-create a control file in one of the following cases:
If it is necessary to use the
An alternative to the
"BACKUP CONTROLFILE Clause" of
When you issue a
CONTROLFILE statement, Oracle creates a new control file based on the information you specify in the statement. If you omit any clauses, Oracle uses the default values rather than the values for the previous control file. After successfully creating the control file, Oracle mounts the database in the mode specified by the initialization parameter
CLUSTER_DATABASE. You then must perform media recovery before opening the database. It is recommended that you then shut down the instance and take a full backup of all files in the database.
To create a control file, you must have the
SYSDBA system privilege.
The database must not be mounted by any instance. Oracle leaves the database mounted in
EXCLUSIVE state after successful creation of the control file. If you are using Oracle with Real Application Clusters, the DBA must then shut down and remount the database in
SHARED mode (which is the default if the value of the
CLUSTER_DATABASE initialization parameter is
TRUE) before other instances can start up.
REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to
EXCLUSIVE, Oracle returns an error when you attempt to re-create the control file. To avoid this message, either set the parameter to
SHARED, or re-create your password file before re-creating the control file.
Oracle9i Database Reference for more information about the
REUSE to indicate that existing control files identified by the initialization parameter
CONTROL_FILES can be reused, thus ignoring and overwriting any information they may currently contain. If you omit this clause and any of these control files already exists, Oracle returns an error.
Specify the name of the database. The value of this parameter must be the existing database name established by the previous
DATABASE statement or
DATABASE to change the name of the database. The name of a database can be as long as eight bytes.
logfile_clause to specify the redo log files for your database. You must list all members of all redo log file groups.
Specify the logfile group number. If you specify
GROUP values, Oracle verifies these values with the
GROUP values when the database was last open.
If you omit this clause, Oracle creates logfiles using system default values. In addition, if either the
DB_CREATE_FILE_DEST initialization parameter (or both) has been set, and if you have specified
RESETLOGS, then Oracle creates two logs in the default logfile destination specified in the
DB_CREATE_ONLINE_LOG_DEST_n parameter, and if it is not set, then in the
RESETLOGS if you want Oracle to ignore the contents of the files listed in the
LOGFILE clause. These files do not have to exist. Each
redo_log_file_spec in the
LOGFILE clause must specify the
SIZE parameter. Oracle assigns all online redo log file groups to thread 1 and enables this thread for public use by any instance. After using this clause, you must open the database using the
RESETLOGS clause of the
NORESETLOGS if you want Oracle to use all files in the
LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle reassigns the redo log file groups to the threads to which they were previously assigned and reenables the threads as they were previously enabled.
Specify the datafiles of the database. You must list all datafiles. These files must all exist, although they may be restored backups that require media recovery. See the syntax description in
You should list only datafiles in this clause, not temporary datafiles (tempfiles). Please refer to Oracle9i User-Managed Backup and Recovery Guide for more information on handling tempfiles.
You cannot specify the
data_file_spec in this
Specify the maximum number of online redo log file groups that can ever be created for the database. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The default and maximum values depend on your operating system. The value that you specify should not be less than the greatest
GROUP value for any redo log file group.
Specify the maximum number of members, or identical copies, for a redo log file group. Oracle uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values depend on your operating system.
This parameter is useful only if you are using Oracle in
ARCHIVELOG mode with Real Application Clusters. Specify the maximum number of archived redo log file groups for automatic media recovery of Real Application Clusters. Oracle uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the
MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file.
Specify the initial sizing of the datafiles section of the control file at
CONTROLFILE time. An attempt to add a file whose number is greater than
MAXDATAFILES, but less than or equal to
DB_FILES, causes the control file to expand automatically so that the datafiles section can accommodate more files.
The number of datafiles accessible to your instance is also limited by the initialization parameter
Specify the maximum number of instances that can simultaneously have the database mounted and open. This value takes precedence over the value of the initialization parameter
INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.
ARCHIVELOG to archive the contents of redo log files before reusing them. This clause prepares for the possibility of media recovery as well as instance or system failure recovery.
If you omit both the
ARCHIVELOG clause and
NOARCHIVELOG clause, Oracle chooses
NOARCHIVELOG mode by default. After creating the control file, you can change between
ARCHIVELOG mode and
NOARCHIVELOG mode with the
Use this clause to put the database into
LOGGING mode after control file creation. When the database is in this mode, Oracle logs all changes in the database except changes to temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any
LOGGING settings you specify for individual tablespaces and any
NOLOGGING settings you specify for individual database objects. If you omit this clause, the database will not be in
LOGGING mode after the controlfile is created.
If you specify a character set, Oracle reconstructs character set information in the control file. In case media recovery of the database is required, this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery. This clause is required only if you are using a character set other than the default US7ASCII. Oracle prints the current database character set to the "alert" log in $ORACLE_HOME/log during startup.
If you are re-creating your control file and you are using Recovery Manager for tablespace recovery, and if you specify a different character set from the one stored in the data dictionary, then tablespace recovery will not succeed. (However, at database open, the control file character set will be updated with the correct character set from the data dictionary.)
Oracle9i Recovery Manager User's Guide for more information on tablespace recovery
This statement re-creates a control file. In this statement, database
demo was created with the WE8DEC character set. The example uses the word
path where you would normally insert the path on your system to the appropriate Oracle directories.
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 1 MAXLOGHISTORY 449 LOGFILE GROUP 1 '/path/oracle/dbs/t_log1.f' SIZE 500K, GROUP 2 '/path/oracle/dbs/t_log2.f' SIZE 500K # STANDBY LOGFILE DATAFILE '/path/oracle/dbs/t_db1.f', '/path/oracle/dbs/dbu19i.dbf', '/path/oracle/dbs/tbs_11.f', '/path/oracle/dbs/smundo.dbf', '/path/oracle/dbs/demo.dbf' CHARACTER SET WE8DEC ;