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)
)
)
