These are notes about creating a database manually. The DBCA can also be used to do this much more easily.

Create the directories

First, create the directories you need for the datafiles. On a non production system, I like to keep all the files for a database under a single directory so it is easy to delete the database later, eg:

mkdir -p /mnt/raid/dborafiles/11gr2/datafiles
mkdir -p /mnt/raid/dborafiles/11gr2/redo

For a production setup, each of these areas is probably a separate mount point on different disks etc.

Create a minimal init.ora

This file should go into $ORACLE_HOME/dbs and be called initSID.ora:

control_files = (/mnt/raid/dborafiles/ora11gr2/datafiles/control01.ora,
                 /mnt/raid/dborafiles/ora11gr2/datafiles/control02.ora,
                 /mnt/raid/dborafiles/ora11gr2/datafiles/control03.ora)

undo_management = auto
db_name = ora11gr2
db_block_size = 8192

# 11G (oracle will create subdir diag and all the required subdirs)
# This is a non-default location for the diag files. Normally they are created
# under $ORACLE_BASE, but for non production setups I like to keep all the files
# for a database instance under a single folder.
diagnostic_dest      = /mnt/raid/dborafiles/ora11gr2

Set the SID for your session

export ORACLE_SID=ora11gr2

Connect to SQLPLUS

$ sqlplus /nolog
SQL11g> connect / as sysdba

Create the SPFILE

$ create SPFILE from PFILE='/dboracle/product/11.2.0/dbhome_1/dbs/init11gr2.ora'

Startup the instance

SQL11g> startup nomount

Create the database

create database ora11gr2
  logfile   group 1 ('/mnt/raid/dborafiles/ora11gr2/redo/redo1.log') size 10M,
            group 2 ('/mnt/raid/dborafiles/ora11gr2/redo/redo2.log') size 10M,
            group 3 ('/mnt/raid/dborafiles/ora11gr2/redo/redo3.log') size 10M
  character set          utf8
  national character set utf8
  datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/system.dbf' 
            size 50M
            autoextend on 
            next 10M
            extent management local
  sysaux datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/sysaux.dbf' 
            size 10M
            autoextend on 
            next 10M 
  undo tablespace undo
            datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/undo.dbf'
            size 10M
            autoextend on
  default temporary tablespace temp
            tempfile '/mnt/raid/dborafiles/ora11gr2/datafiles/temp.dbf'
            size 10M
            autoextend on

( TODO - unsure about setting max files sizes on these files )

Create the catalogue etc:

SQL11G> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL11G> @$ORACLE_HOME/rdbms/admin/catproc.sql

As SYSTEM (not SYS) run the following:

SQL11G> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

(not doing this doesn't cause any harm, but a warning is displayed when logging into SQLPLUS if it is not run)

The database is now basically ready to use, but there no users and no users tablespace. Note it is also NOT in archive log mode, so is certainly not production ready, but may be good enough for a non-backed up dev instance.

Create the users tablespace, local, auto allocate

SQL>CREATE TABLESPACE users DATAFILE '/mnt/raid/dborafiles/ora11gr2/datafiles/users_01.dbf' 
    SIZE 50M
    autoextend on 
    maxsize 2048M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Create a user:

SQL11G> create user sodonnel 
        identified by sodonnel 
        default tablespace users 
        temporary tablespace temp;

SQL11G> alter user sodonnel quota unlimited on users;

SQL11G> grant connect, create procedure, create table, alter session to sodonnel;

Ensure the database comes up at startup time

Add a line to /etc/oratab to tell Oracle about the instance. This is used by the dbstart command, which will start all the database specified in this file:

ora11gr2:/dboracle/product/11.2.0/dbhome_1:Y

To start all instances use dbstart and to stop use dbshut.

TODO - control script to autostart databases when the machine boots.

Setup the listener

At this point, only people on the local machine can connect to the database, so the last step is to setup the listener. All you need to do here is add a file called listener.ora in $ORACLE_HOME/network/admin, and have it contain something like the following:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )

Creating a tnsnames.ora file at this point would be a good idea too. It also goes into $ORACLE_HOME/network/admin:

ora11gr2 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ora11gr2)
 )
)

Further investigation

• Think about using Oracle managed files for datafiles
• Think about ASM.
• Come up with a more complete set of spfile parameters.