How to create an instance

See How to create a database, 
How to install, patch and interim for folders permissions

Preliminary operations

Check whether the OS is well patched and configured, see OPatch and RDA

Create group and user
groupadd dba
groupadd oinstall
useradd -g oinstall -G dba oracle
passwd oracle

#mkdir -p /opt/oracle/product/10.2
mkdir -p /opt/oracle/product/11.1
mkdir /var/opt/oracle
chown oracle.dba /var/opt/oracle
chown -R oracle.dba /opt/oracle/*
chmod 755 /var/opt/oracle

[/etc/sysctl.conf]
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem    = 250 32000 100 128
fs.file-max   = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_rmem = 1048576 1048576 1048576
net.ipv4.tcp_wmem = 262144 262144 262144
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

[/etc/security/limits.conf]
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

[.bash_profile] for user oracle
ulimit -s 32768

[/etc/profile]
ulimit -n 65536

echo 250 32000 100 128 > /proc/sys/kernel/sem
echo 4294967295 > /proc/sys/kernel/shmmax
echo 4096 > /proc/sys/kernel/shmmni
echo 4294967295 > /proc/sys/kernel/shmall
echo 65536 > /proc/sys/fs/file-max
echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range
echo 1048576 1048576 1048576 > /proc/sys/net/ipv4/tcp_rmem
echo 262144 > /proc/sys/net/core/rmem_default
echo 262144 > /proc/sys/net/core/rmem_max
echo 262144 > /proc/sys/net/core/wmem_default
echo 262144 > /proc/sys/net/core/wmem_max
echo 262144 262144 262144 > /proc/sys/net/ipv4/tcp_wmem

10g

Cool sql prompt
echo "set sqlprompt \"_user _privilege'@'_connect_identifier>\"" >> $ORACLE_HOME/sqlplus/admin/glogin.sql

9i

create a file /etc/oratab (Linux)
create a file /var/opt/oracle/oratab (Solaris)

add a line containing the description of the instance added

#*:/opt/oracle/product/9.2.0:N
p1:/opt/oracle/product/9.2.0:Y

Please see Metalink Note:189256.1 to get a script to Verify Installation Requirements for Oracle 8.0.5 to 9.2 versions of RDBMS

Manual way (only for Unix based)

Only Unix based, because Windows requires each instance to have a service

We will use the listener to spawn an instance p2.

Add the information relative to p2 as below to listener.ora, you should find it in $ORACLE_HOME/network/admin. See listener.ora

Next add the information for p2 to our tnsnames.ora

You'll find it in the path specified by TNS_ADMIN environment variable, otherwise in $ORACLE_HOME/network/admin

# TNSNAMES.ORA Network Configuration File: /opt/ora9/product/9.2/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

P2.FADALTI.COM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lo9)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = p2.fadalti.com)

)

)

Now create a password file for p2

Remember that you must specify the instance name in the orapwd file name with same case you specified in the SID_NAME section of the listener.ora.

For example, orapwd file=orapwp2 password=pwd45 entries=5 is different than orapwd file=orapwP2 password=pwd45 entries=5, and you may have:

>sqlplus "sys/pwd45@p2.fadalti.com as sysdba"

ERROR:
ORA-01031: insufficient privileges

unix>cd $ORACLE_HOME/dbs

windows>cd $ORACLE_HOME/database

unix>orapwd file=orapwp2 password=pwd45 entries=5

windows>orapwd file=PWDp2.ora password=pwd45 entries=5

Now in the same directory create a file called initp2.ora, see: init.ora 9i, init.ora 10g

Create the directories necessary
mkdir -p /opt/ora9/admin/p2/bdump
mkdir -p /opt/ora9/oradata/p2 #in the event of a database creation
mkdir -p /opt/ora9/admin/p2/cdump
mkdir -p /opt/ora9/admin/p2/udump
mkdir -p /opt/ora9/admin/p2/create
mkdir -p /opt/ora9/admin/p2/pfile

start the instance
sqlplus "sys/pwd45@p2.fadalti.com as sysdba"
create spfile='spfilep2.ora' from pfile='initp2.ora'
startup nomount
Automatic way
Simple. Use dbca to create a db, shutdown the instance and delete all under oradata/<instance_name>