Oracle Database Installation

Database Creation Script

#!/bin/ksh

ORACLE_BASE=/<mount-point-1>/oracle
ORACLE_HOME=$ORACLE_BASE/product/8.1.7
TNS_ADMIN=/<mount-point-1>/oracle/product/8.1.7/network/admin
ORACLE_SID=<SID>
ORACLE_DBF1=/<mount-point-1>/oracle/oradata/$ORACLE_SID
ORACLE_DBF2=/<mount-point-2>/oracle/oradata/$ORACLE_SID
ORACLE_BACKUPS=/<mount-point-2>/backups
ORACLE_SBIN=$ORACLE_HOME/sbin
ORACLE_ALERT=$ORACLE_BASE/admin/$ORACLE_SID/bdump
PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin

USAGE="$0: Incorrect arguments, Usage: $0 <password>"
if [ -z "$1" ]; then
       echo "$USAGE"
       exit 1
fi

if [ `/usr/ucb/whoami` != "oracle" ]; then
	echo "Must be oracle"
	exit 1
fi

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=$1 entries=2
ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora

mkdir $ORACLE_BASE/admin
mkdir $ORACLE_BASE/admin/$ORACLE_SID
mkdir $ORACLE_BASE/admin/$ORACLE_SID/adhoc
mkdir $ORACLE_BASE/admin/$ORACLE_SID/arch
mkdir $ORACLE_BASE/admin/$ORACLE_SID/bdump
mkdir $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir $ORACLE_BASE/admin/$ORACLE_SID/create
mkdir $ORACLE_BASE/admin/$ORACLE_SID/exp
mkdir $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir $ORACLE_BASE/admin/$ORACLE_SID/udump

mkdir $ORACLE_BASE/oradata
mkdir $ORACLE_BASE/oradata/$ORACLE_SID
mkdir $ORACLE_BASE/oradata/$ORACLE_SID/archive
mkdir /<mount-point-2>/oracle/oradata
mkdir /<mount-point-2>/oracle/oradata/$ORACLE_SID
mkdir $ORACLE_BACKUPS/$ORACLE_SID

cp ./listener.ora $TNS_ADMIN
cp ./tnsnames.ora $TNS_ADMIN
cp ./init$ORACLE_SID.ora $ORACLE_BASE/admin/$ORACLE_SID/pfile

$ORACLE_HOME/bin/lsnrctl start

svrmgrl<system.sql
svrmgrl<tablespaces.sql
svrmgrl<admin.sql

System and Logs Creation (system.sql)

spool ../logs/system.log;
connect internal
startup nomount pfile = "/<mount-point-1>/oracle/admin/<SID>/pfile/init<SID>.ora"
CREATE DATABASE "<SID>"
        maxdatafiles 400
        maxinstances 4
        maxlogfiles 16
        maxlogmembers 4
        maxloghistory 200
        character set US7ASCII
        national character set US7ASCII
        archivelog
DATAFILE '/<mount-point-1>/oracle/oradata/TEST/system01.dbf' SIZE 150M AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/<mount-point-1>/oracle/oradata/TEST/redo01a.log','/<mount-point-1>/oracle/oradata/TEST/redo01b.log') SIZE 10M,
        GROUP 2 ('/<mount-point-1>/oracle/oradata/TEST/redo02a.log','/<mount-point-1>/oracle/oradata/TEST/redo02b.log') SIZE 10M,
        GROUP 3 ('/<mount-point-1>/oracle/oradata/TEST/redo03a.log','/<mount-point-1>/oracle/oradata/TEST/redo03b.log') SIZE 10M,
        GROUP 4 ('/<mount-point-1>/oracle/oradata/TEST/redo04a.log','/<mount-point-1>/oracle/oradata/TEST/redo04b.log') SIZE 10M;

create rollback segment rbtemp tablespace system storage (initial 64k next 64k minextents 2 maxextents 300);
alter rollback segment rbtemp online;

disconnect;
spool off;

Tablespace and Rollback Creation (tablespaces.sql)

spool ../logs/admin.log;
connect internal

alter tablespace system default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);
alter tablespace system minimum extent 1M;

--Temporary sort
create temporary tablespace temp tempfile '/<mount-point-1>/oracle/oradata/TEST/temp01.dbf' 
        size 300M autoextend on next 1M maxsize unlimited extent management local uniform size 1M;

-Oracle Enterprise Manager
create tablespace oem datafile '/<mount-point-1>/oracle/oradata/TEST/oem01.dbf'
        size 1000M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

--Main rollbacks
create tablespace rbs datafile '/<mount-point-1>/oracle/oradata/TEST/rbs01.dbf'
        size 300M autoextend ON minimum extent 1M
        default storage (initial 1M next 1M minextents 20 maxextents unlimited pctincrease 0);

---Batch rollbacks
create tablespace rbsspec datafile '/<mount-point-1>/oracle/oradata/TEST/rbsspec01.dbf'
        size 320M autoextend OFF minimum extent 8M
        default storage (initial 8M next 8M minextents 2 maxextents unlimited pctincrease 0);

--LOBs
create tablespace objects datafile '/<mount-point-1>/oracle/oradata/TEST/objects01.dbf'
        size 96M autoextend on next 8M minimum extent 8M
        default storage (initial 8M next 8M minextents 1 maxextents unlimited pctincrease 0);

--Stored procedures
create tablespace tools datafile '/<mount-point-1>/oracle/oradata/TEST/tools01.dbf'
        size 100M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

--Static datafiles
create tablespace datastat datafile '/<mount-point-1>/oracle/oradata/TEST/datastat01.dbf'
        size 50M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

--Transaction datafiles separated based on expansion not functionality (set pctincrease 1 for functionality)
create tablespace datatrn1 datafile '/<mount-point-1>/oracle/oradata/TEST/datatrn101.dbf'
        size 50M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

create tablespace datatrn2 datafile '/<mount-point-1>/oracle/oradata/TEST/datatrn102.dbf'
        size 100M autoextend on next 2M minimum extent 2M
        default storage (initial 2M next 2M minextents 1 maxextents unlimited pctincrease 0);

create tablespace datatrn4 datafile '/<mount-point-1>/oracle/oradata/TEST/datatrn104.dbf'
        size 200M autoextend on next 4M minimum extent 4M
        default storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0);

create tablespace logs datafile '/<mount-point-1>/oracle/oradata/TEST/logtrn01.dbf'
        size 320M autoextend on next 8M minimum extent 8M
        default storage (initial 8M next 8M minextents 1 maxextents unlimited pctincrease 0);

--Static indexes
create tablespace indxstat datafile '/<mount-point-1>/oracle/oradata/TEST/indxstat01.dbf'
        size 50M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

--Transactional indexes
create tablespace indxtrn1 datafile '/<mount-point-1>/oracle/oradata/TEST/indxtrn101.dbf'
        size 50M autoextend on next 1M minimum extent 1M
        default storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0);

create tablespace indxtrn2 datafile '/<mount-point-1>/oracle/oradata/TEST/indxtrn201.dbf'
        size 100M autoextend on next 2M minimum extent 2M
        default storage (initial 2M next 2M minextents 1 maxextents unlimited pctincrease 0);

create tablespace logsidx datafile '/<mount-point-1>/oracle/oradata/TEST/logtidx01.dbf'
        size 160M autoextend on next 4M minimum extent 4M
        default storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0);

alter user sys temporary tablespace temp;
alter user system temporary tablespace temp;

--
--Create fewer, larger rollback segments to reporting, data warehouse or non-OLTP type databases
--

create public rollback segment rb00 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb01 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb02 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb03 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb04 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb05 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb06 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb07 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb08 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb09 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb10 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb11 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb12 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb13 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb14 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);
create public rollback segment rb15 tablespace rbs storage (initial 256K next 256K optimal 512K minextents 2 maxextents 64);

alter rollback segment rb00 online;
alter rollback segment rb01 online;
alter rollback segment rb02 online;
alter rollback segment rb03 online;
alter rollback segment rb04 online;
alter rollback segment rb05 online;
alter rollback segment rb06 online;
alter rollback segment rb07 online;
alter rollback segment rb08 online;
alter rollback segment rb09 online;
alter rollback segment rb10 online;
alter rollback segment rb11 online;
alter rollback segment rb12 online;
alter rollback segment rb13 online;
alter rollback segment rb14 online;
alter rollback segment rb15 online;

--
--use large00 and large01 for batch-type operations
--
create public rollback segment large00 tablespace rbsspec storage (initial 8M next 8M optimal 16M minextents 2 maxextents 32);
create public rollback segment large01 tablespace rbsspec storage (initial 8M next 8M optimal 16M minextents 2 maxextents 32);
alter rollback segment large00 offline;
alter rollback segment large01 offline;

alter rollback segment rbtemp offline;
drop rollback segment rbtemp;

disconnect;
spool off;

Post-Database Creation Scripts

The scripts to be executed below are both required and optional database creation scripts.

spool ../logs/admin.log;
connect internal;
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/catalog.sql;	--database creation of data dictionary views (runs catsnmp.sql)
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/catproc.sql;	--database creation of PL/SQL
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/dbmspool.sql;	--pinning objects
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/caths.sql;	--heterogeneous services
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/catldr.sql;	--prepare database for direct path SQL*Loader
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/cataudit.sql;	--audit trails
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/catparr.sql;	--parallel server views
@/<mount-point-1>/oracle/product/8.1.7/rdbms/admin/utltkprf.sql;	--tkprof use
disconnect;
connect system/manager;
@/<mount-point-1>/oracle/product/8.1.7/sqlplus/admin/pupbld.sql;	--sqlplus user help files
disconnect;
spool off;