Manually creating a new database on 11gR2

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

STEP:-1 

Create the directories

First, create the directories you need for the datafiles, eg:

# Don't need to create the admin directories in 11g since introduction 
# of diag_dest
mkdir -p /u01/oracle/dborafiles/11gr2/admin
mkdir -p /u01/oracle/dborafiles/11gr2/admin/bdump
mkdir -p /u01/oracle/dborafiles/11gr2/admin/cdump
mkdir -p /u01/oracle/dborafiles/11gr2/admin/udump
mkdir -p /u01/oracle/dborafiles/11gr2/datafiles
mkdir -p /u01/oracle/dborafiles/11gr2/redo

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

STEP:-2

Create a minimal init.ora

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

control_files = (/u01/oracle/dborafiles/ora11gr2/datafiles/control01.ora,
                 /u01/oracle/dborafiles/ora11gr2/datafiles/control02.ora,
                 /u01/oracle/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)
diagnostic_dest      = /u01/oracle/dborafiles/ora11gr2

# Pre 11G specifiy these parameters
# background_dump_dest = /u01/oracle/dborafiles/ora11gr2/admin/bdump
# core_dump_dest       = /u01/oracle/dborafiles/ora11gr2/admin/cdump
# user_dump_dest       = /u01/oracle/dborafiles/ora11gr2/admin/udump

STEP:-3

Set the SID for your session

export ORACLE_SID=ora11gr2

Connect to SQLPLUS

$ sqlplus /nolog
SQL11g> connect / as sysdba

STEP:-4

Create the SPFILE

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

STEP:-5

 Startup the instance

SQL> startup nomount

STEP:-6

Create the database

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

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

STEP:-7

Create the catalogue

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

STEP:-8

As SYSTEM (not SYS) run the following:

SQL> @$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.

STEP:-9

Create the users tablespace, local, auto allocate

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

STEP:-10

Create a user

SQL >   create user sonu 
        identified by sonu 
        default tablespace users 
        temporary tablespace temp;

SQL> alter user sonu quota unlimited on users;

SQL> grant connect, create procedure, create table, alter session to sonu;

STEP:-11

Ensure the database comes up at start-up 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:/u01/oracle/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.

STEP:-12

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 = sonu.oracle.com)(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 = sonu.oracle.com)(Port = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ora11gr2)
 )
) 

********************END*****************************************