Following are some of the ways alongwith minimum requirement that must be satisfied in order to create database successfully.
These steps have been tested in oracle 10g Enterprise Edition 10.2.0.1.0. on Linux platform.
1. First of all, set ORACLE_SID and ORACLE_HOME to apprpriate values.
Suppose you want to create a new database named TEST. Then , issue
$ORACLE_SID=TEST
$ORACLE_HOME=/oracle/product/10.2.0/db_1
2. Create initialization parameter file $ORACLE_HOME/dbs/initTEST.ora
/*****initTEST.ora******/
control_files=(/oracle/product/10.2.0/db_1/oradata/TEST/control01.ctl,/oracle/product/10.2.0/db_1/oradata/TEST/control02.crl)
undo_management=AUTO
undo_tablespace=UNDOTBS
db_name=TEST
db_block_size=8192
sga_max_size=268000000
sga_target=268000000
user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'
background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'
core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump
/********************/
Specified location must exist. Otherwise, error returns.
3. Now, connect to sqlplus:
$sqlplus /nolog
SQL> conn / as sysdba
4. Start the database in nomount state.
SQL> startup nomount
5.Issue following create database statement:
SQL> create database TEST
logfile group 1('/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log') size 10M,
group 2('/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log') size 10M,
group 3('/oracle/product/10.2.0/db_1/oradata/TEST/redo03.log') size 10M
character set WE8ISO8859P1
national character set AL16UTF16
datafile '/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf' size 10M autoextend on next 10M maxsize unlimited
undo tablespace undotbs datafile '/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf' size 10M
default temporary tablespace temp tempfile '/oracle/product/10.2.0/db_1/oradata/TEST/temp01.dbf' size 10M;
Above procedure would create datafiles in the specified location. It create four tablespaces: system,sysaux,undotbs and temp.
6. run $ORACLE_HOME/rdbms/admin/catalog.sql script.
7. run $ORACLE_HOME/rdbms/admin/catproc.sql script.
Creating database using OMF feature
You can also create database using OMF feature. Change initTEST.ora file as follows:
undo_management=AUTO
sga_max_size=268000000
sga_target=268000000
undo_tablespace=UNDOTBS1
db_name=TEST
db_block_size=8192
db_create_file_dest='/oracle/product/10.2.0/db_1/oradata'
db_create_online_log_dest_1='/oracle/product/10.2.0/db_1/oradata'
user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'
background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'
core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump'
Here, two OMF related parameters have been set,that is, db_create_file_dest and db_create_online_log_dest.
And issue create database statement as:
SQL> create database TEST
character set WE8ISO8859P1
national character set AL16UTF16
undo tablespace undotbs1
default temporary tablespace temp;
No need to specify the name of any of the datafiles and redolog files here. It will create all the datafiles according to the specified OMF related parameters' values.
All the other steps are same except step no.2 and 5.
If creating database using OMF features, controlfile parameter needs to be set in the initialization parameter file after successful creation of the database as Oracle would not identify correct controlfile during next startup of the database. Oracle will locate the controlfile in $ORACLE_HOME/dbs but can not find one. That's why always set controfile parameter that points to the correct location before shutting down the instance.
You can issue even much simple create database statement when using OMF feature. Issue just:
SQL> create database TEST;
But all other steps are same.
