CREATE CONTROLFILE command

PURPOSE:
To recreate a control file in one of these cases:

* All copies of your existing control files have been lost through
media failure.
* You want to change the name of the database.
* You want to change the maximum number of redo log file groups,
redo log file members, archived redo log files, data files, or
instances that can concurrently have the database mounted and
open.

Warning: Oracle Corporation recommends that you perform a full
backup of all files in the database before using this command.


SYNTAX:---------------
create controlfile REUSE
set database orders_2
logfile group 1 (d:\orawin\orawin\gopal\g.log1)
group 2 (d:\orawin\orawin\gopal\g.log2)
RESETLOGS/NORESETLOGS
Datafile 'c:\windows\gopal\go.data1' size 2m,
'd:\orawin95\database\gopal\go.data2' size 2m
maxlog files 50
maxlog member
maxlog history
max datafiles
max instances
Archivelog/No Archivelog(specify whether the database should b kept in archive log mode or no archive log mode)
character set f7dec;

Note1: you must have OSDBA role enabled. The database must not be mounted.
Note2:database must b in mount mode.
by any instance.


INFORMATION IN CONTROL FILE IS 5 STEPS
--------------------------------------
part1 = Contains information about db. Total no.of Datafiles,logfiles,and
threads are enabled and open (If you are not using parallel server, you
will have only 1 thread)

part2 = Redo log threads -> Privately or publicly enabled. Each log group
and the current log group that "LGWR" is writing

part3 = Each log member of each log group, size of each log file, its
fullpath, name, the log sequence number, low and high scn values and the
threads to whcih each logfile belongs.

part4 = Datafile information,suchas online/offline,
readable,writable,whether media recovery required, stop scn.

part5 = Log History information

LOG-HISTORY : The controlfile can be configured to contain loghistroy
records for every redo logfile that is used by the database.
Each record in this table gives information of one redologfile.
Each history record contains *)The thread no *)Log sequence no *)Low scn
& High scn



REUSE
specifies that existing control files identified by the
initialization parameter CONTROL_FILES can be reused, thus ignoring
and overwriting any and all information they may currently contain.
If you omit this option and any of these control files already
exist, Oracle returns an error.

SET DATABASE
changes the name of the database. The name of a database can be as
long as eight bytes.

DATABASE
specifies the name of the database. The value of this parameter
must be the existing database name established by the previous
CREATE DATABASE statement or CREATE CONTROLFILE statement.

LOGFILE
specifies the redo log file groups for your database. You must list
all members of all redo log file groups. These files must all
exist.

RESETLOGS
ignores the contents of the files listed in the LOGFILE clause.
Each filespec in the LOGFILE clause must specify the SIZE parameter.
Oracle assigns all redo log file groups to thread 1 and enables this
thread for public use by any instance. After using this option, you
must open the database using the RESETLOGS option of the ALTER
DATABASE command.

NORESETLOGS
specifies that all files in the LOGFILE clause should be used as
they were when the database was last open. These files must be the
current redo log files rather than restored backups. Oracle
reassigns the redo log file groups to the threads to which they were
previously assigned and re-enables the threads as they were
previously enabled. If you specify GROUP values, Oracle verifies
these values with the GROUP values when the database was last open.

DATAFILE
specifies the data files of the database. You must list all data
files. These files must all exist, although they may be restored
backups that require media recovery.

MAXLOGFILES
specifies the maximum number of 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.

Note that the number of redo log file groups accessible to your
instance is also limited by the initialization parameter LOG_FILES.

MAXLOGMEMBERS
specifies the maximum number of members, or 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.

MAXLOGHISTORY
specifies the maximum number of archived redo log file groups for
automatic media recovery of the Oracle Parallel Server. 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 varies depending on your operating system. The maximum
value is limited only by the maximum size of the control file. Note
that this parameter is only useful if you are using Oracle with the
Parallel Server option in both parallel mode and archivelog mode.

MAXDATAFILES
specifies the maximum number of data files that can ever be created
for the database. The minimum value is 1. The maximum and default
values depend on your operating system. The value you specify
should not be less than the total number of data files ever in the
database, including those for tablespaces that have been dropped.

Note that the number of data files accessible to your instance is
also limited by the initialization parameter DB_FILES.

MAXINSTANCES
specifies 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
establishes the mode of archiving the contents of redo log files
before reusing them. This option prepares for the possibility of
media recovery as well as instance recovery.

NOARCHIVELOG
establishes the initial mode of reusing redo log files without
archiving their contents. This option prepares for the possibility
of instance recovery but not media recovery.

If you omit both the ARCHIVELOG and NOARCHIVELOG options, Oracle
chooses noarchivelog mode by default. After creating the control
file, you can change between archivelog mode and noarchivelog mode
with the ALTER DATABASE command.

PREREQUISITES:
You must have the OSDBA role enabled. The database must not be
mounted by any instance.

If you are using Trusted Oracle in DBMS MAC mode, your operating
system label must be the equivalent of DBHIGH.



Note3: Oracle recommend that you perform a full backup of all database files before using this statement. (see the Oracle User-Managed Backup and Recovery Guide)

An alternative to CREATE CONTROLFILE is
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
this generates a new SQL script that may be used to re-create the controlfile.