Yet another blog post to create database manually (Oracle version 10.2)

1. Environment variables

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_SID=testdb

2. Required directories

$ mkdir -p /dbdir/testdb/{control,data,redo,arch}

$ mkdir -p /dbdir/testdb/admin/{a,b,c,u}dump

3. Entries in pfile $ORACLE_HOME/dbs/inittestdb.ora

db_name                    = testdb

control_files              = (/dbdir/testdb/control/control.dbf)

log_archive_dest_1         = "LOCATION=/dbdir/testdb/arch"

log_archive_dest_state_1   = enable

sga_target                 = 500M

pga_aggregate_target       = 100M

undo_management            = AUTO

undo_tablespace            = undotbs

audit_file_dest            = /dbdir/testdb/admin/adump

background_dump_dest       = /dbdir/testdb/admin/bdump

core_dump_dest             = /dbdir/testdb/admin/cdump

user_dump_dest             = /dbdir/testdb/admin/udump

4. Creating database

$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

SQL> CREATE DATABASE testdb

   USER SYS IDENTIFIED BY secret1

   USER SYSTEM IDENTIFIED BY secret2

   DATAFILE '/dbdir/testdb/data/system1.dbf' SIZE 200M

      EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/dbdir/testdb/data/sysaux1.dbf' SIZE 50M

   DEFAULT TABLESPACE worktbs

      DATAFILE '/dbdir/testdb/data/work1.dbf' SIZE 50M

   DEFAULT TEMPORARY TABLESPACE temptbs

      TEMPFILE '/dbdir/testdb/data/temptbs1.dbf' SIZE 50M

   UNDO TABLESPACE undotbs

      DATAFILE '/dbdir/testdb/data/undotbs1.dbf' SIZE 50M

   LOGFILE GROUP 1 ('/dbdir/testdb/redo/redo1.log') SIZE 50M,

           GROUP 2 ('/dbdir/testdb/redo/redo2.log') SIZE 50M

   ARCHIVELOG

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

;

5. Post database creation steps

$ sqlplus / as sysdba

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> connect system/secret2

SQL> @?/sqlplus/admin/pupbld.sql

SQL> exit

$ echo "$ORACLE_SID:$ORACLE_HOME:N" >> /etc/oratab