How to Create Oracle Database Manually

CREATING THE DATABASE MANUALLY

This method involves typing the create database statement within SQL*DBA.
Using this method allows for more flexibility such as specifying
the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.

Steps for Method I:

1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE)].

http://sabdarsyed.blogspot.com/2008/09/setting-oracle-environment-variable.html

2. Create a new init.ora for your new database by copying the default
   one provided by Oracle:

   % cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora

3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
   the new database name.

4. Startup sql*dba in line mode:

      sqldba lmode=y

5. Connect to the instance, and startup in a 'NOMOUNT' state:

      SQLDBA> connect internal
      Connected.
      SQLDBA> startup nomount
      ORACLE instance started.
      SQLDBA>

6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
   statement syntax - page 4-148.

   Here is a sample create database statement:

      SQLDBA> create database NEW_NAME
           2> logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 500K,
           3>         group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 500K
           4> datafile 'oracle_home/dbs/dbsNEW_NAME.dbf' size 20M
           5> maxdatafiles 50;

7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
   directory. This script must be run under the 'SYS' user or connected
   'internal'.

   NOTE: catproc.sql must also be run if you have the procedural option
         installed.

      SQLDBA>@oracle_home/rdbms/admin/catalog.sql
      SQLDBA>@oracle_home/rdbms/admin/catproc.sql

8. After the database has been created, the SYSTEM tablespace and SYSTEM
   rollback segment will exist. However, a second rollback segment in the
   SYSTEM tablespace must be created and activated before any other
   tablespaces can be created in the database (Refer to SQL Lanuguage
   Reference Manual for full syntax).

      Creating the rollback segment:

        SYNTAX: CREATE ROLLBACK SEGMENT system2
                TABLESPACE SYSTEM
                STORAGE (...);

      Activating the rollback segment:

        SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;

9. Modify the /etc/oratab file by adding the new database name.  This
   is used by dbstart to startup all databases with a 'Y' entry in this
   file.  (See page 4-17 of Oracle for Unix technical Reference Guide).

Source:http://www.fors.com/orasupp/unix/13481_1.HTM

Posted in: oracle DBA, Oracle Tips, Oracle Trick