Manually Creating an Oracle DB

Building the database manually... This is definately the more "sporting" way to create
a database, and gives the DBA a bit more control than running dbca. However
this does require a bit more experience on both SQL and Unix. To create a database
manually, follow these steps as the 'oracle' user:

A. Decide on a UNIQUE database SID of to Six characters. Add this name into
the oratab which defines that valid database on the system.
For this example we will use a database SID of SEED7

B. Decide on a location where you want to place all the database files.
For a default Banner Install on 9i you need at least 2GB of disk space.
For this example we will use a directory of /u01/oradata/SEED7

C. Set the environment variable $ORACLE_SID to this database name.
then type '. oraenv' to set the other oracle environment variable
parameters. Verify that the following environment variables are set correctly.

- $ORACLE_HOME
- $LD_LIBRARY_PATH (should be set to $ORACLE_HOME/lib)
- $PATH (should include $ORACLE_HOME/bin at the front)

D. Create the corresponding Instance Parameter file. This file MUST be named
init$ORACLE_SID.ora and be in the $ORACLE_HOME/dbs directory.
A template file exists here which you can use, alternatively, you can use
a custom file available here.

Since our $ORACLE_SID is SEED7, oracle will
expect a file named initSEED7.ora under $ORACLE_HOME/dbs.

E. Using a text editor edit your init$ORACLE_SID.ora parameter file and make
the following changes:

DB_NAME = $ORACLE_SID
INSTANCE_NAME = $ORACLE_SID
CONTROL_FILES to the full path location where you want the control files
DB_BLOCK_SIZE to the size of each database block. Normally this is 8192

In addition if you are using a preset parameter file, verify the following:
for Banner® systems.

UNDO_MANAGEMENT = AUTO
UNDO_RETENTION = 1800
UNDO_TABLESPACE = UNDO1
SHARED_POOL_SIZE = 300000000
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
REMOTE_DEPENDENCIES_MODE = SIGNATURE

Save the parameter file before contining.

F. Decide on a password for the base administrative database user, 'SYS' then
use the utility 'orapwd' to create the password file. The password file
MUST have a name of orapw$ORACLE_SID and MUST also be in $ORACLE_HOME/dbs.
By default this account is usually assigned a password of change_on_install

Type the command below the create the file:

       $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install 
         entries=3

G. At this point, we can attempt to start the database instance. Type 'sqlplus'
then connect as '/ as sysdba' You should then see the message shown below:

      $ sqlplus

      SQL*Plus: Release 10.1.0.3.0 - Production on Sun Oct 22 12:22:25 2006

      Copyright (c) 1982, 2004, Oracle.  All rights reserved.

      Enter user-name: / as sysdba
      Connected to an idle instance.

      SQL> 

H. Now try to start the oracle instance by typing 'startup nomount' You
see the following type of message if all is well.

      SQL> startup nomount
      ORACLE instance started.

      Total System Global Area   83886080 bytes
      Fixed Size                   777864 bytes
      Variable Size              82846072 bytes
      Database Buffers                  0 bytes
      Redo Buffers                 262144 bytes
      SQL> 

Fix any errors before continuing to the next step.
I. Create the database with the following command. You should probably cut the

contents below and save to a file before running. Also you will need to edit
the file locations and database name if they are different. If everything
is ok, the system should churn for a while and return back a prompt of "Database Created."

      CREATE DATABASE SEED7
      MAXINSTANCES 1
      MAXLOGHISTORY 1
      MAXLOGFILES 5
      MAXLOGMEMBERS 3
      MAXDATAFILES 100
      DATAFILE '/u01/oradata/SEED7/system_SEED7_01.dbf' SIZE 1024M REUSE 
      10240K MAXSIZE UNLIMITED
      EXTENT MANAGEMENT LOCAL
      DEFAULT TEMPORARY TABLESPACE TEMP 
       TEMPFILE '/u01/oradata/SEED7/temp_SEED7_01.dbf' SIZE 1024M 
      UNDO TABLESPACE UNDO1 DATAFILE '/u01/oradata/SEED7/undo_SEED7_01.dbf' 
       SIZE 1024M 
      CHARACTER SET AL32UTF8
      NATIONAL CHARACTER SET AL16UTF16
      LOGFILE GROUP 1 ('/u01/oradata/SEED7/redo_SEED7_01.log') SIZE 50M,
      GROUP 2 ('/u01/oradata/SEED7/redo_SEED7_02.log') SIZE 50M,
      GROUP 3 ('/u01/oradata/SEED7/redo_SEED7_03.log') SIZE 50M;

J. At this point, the database should be created. We now need to create the remaining
tablespaces:

     CREATE TABLESPACE XDB LOGGING DATAFILE '/u01/oracle/oradata/SEED7/xdb01.dbf' 
     SIZE 1024M EXTENT MANAGEMENT LOCAL;
     CREATE TABLESPACE USERS LOGGING DATAFILE '/u01/oracle/oradata/SEED7/users01.dbf' 
     SIZE 512M EXTENT MANAGEMENT LOCAL;
     CREATE TABLESPACE DEVELOPMENT LOGGING DATAFILE 
      '/u01/oracle/oradata/SEED7/development01.dbf' 
     SIZE 1024M EXTENT MANAGEMENT LOCAL;

K. Next we now need to install the core database packages. Type the following at the SQL> prompt:

      @?/rdbms/admin/catalog.sql;
      @?/rdbms/admin/catexp7.sql;
      @?/rdbms/admin/catblock.sql;
      @?/rdbms/admin/catproc.sql;
      @?/rdbms/admin/catoctk.sql;
      @?/rdbms/admin/owminst.plb;
      @?/rdbms/admin/catldap.sql;
      @?/javavm/install/initjvm.sql;
      @?/xdk/admin/initxml.sql;
      @?/xdk/admin/xmlja.sql;
      @?/rdbms/admin/catjava.sql;
      connect SYSTEM/manager
      @?/sqlplus/admin/pupbld.sql;
      connect SYSTEM/manager
      @?/sqlplus/admin/help/hlpbld.sql helpus.sql;  

Remember that '?' is shorthand for $ORACLE_HOME inside SQL*PLUS

L. Finally install the XML Database.

      connect SYS/change_on_install as SYSDBA
      @?\rdbms\admin\catqm.sql change_on_install XDB TEMP;
      connect SYS/change_on_install as SYSDBA
      @?\rdbms\admin\catxdbj.sql;