A shell script to manually create an Oracle 9 database

This shell script is not perfect but, I have used it on several occasions to manually install an Oracle database for development purposes on Red Hata Advanced Server 2.1 ( RHAS 2.1 ). I am only putting it herein the hope that it may save someone the trouble if they ever need to write one. The Oracle install is by no means perfect, persoannly, I hate the Oracle installer, it just seems to go wrong whenever you least expect it.

If you do use this script I would appreciate you letting me know of any improvments or just the fact that you found it useful.

 
#!/bin/bash

echo
echo "
This script will eventually be sufficient to create an oracle database after 
you have used the Oracle installer. I have written it to replace "dbca" due 
to numerous problems that I had with it. 

The database that it creates is not meant to be for production but I am sure 
that there are people out there who could modify it for that purpose. This 
is for development only."
echo 

#exit 0
if [ $# -ne 1 ]; then

    echo
    echo " You are not using this script correctly"
    echo " Usage: $0 \$ORACLE_SID"
    echo
    exit 1
fi

if [ -z $ORACLE_HOME  ]; then

    echo    
    echo "Please check your environment variables and try again."
    echo
    exit 1

fi



USER_NAME=$(id -un)
if [ $USER_NAME != "oracle" ]; then

    echo
    echo " 
You must be logged in as Oracle for this script to work. You are
currently logged in as $USER_NAME."
    echo
    exit 1

fi



echo
echo "
1. I am about to remove/create an entire database. 
   Please be very careful what you do here.
2. This script does not do anything fancy like checking
   your disks for available space etc.
3. This script has been written to be edited by YOU for 
   YOUR install. You can then try it and re-run it as
   many times as you want to get your database the way 
   you want it. It is not ready to run as it is.
4. The main editing will be required to sort out where 
   you are to store the data.
"
echo
echo -n "Please enter y or n to continue. Select n if you are unsure:  ";
read CONFIRMATION

if [ $CONFIRMATION != y ]; then

    exit 0

fi

ORACLE_SID=$1

ORATAB_ENTRY=` cat /etc/oratab | grep "${ORACLE_SID}\:${ORACLE_HOME}\:N"`

INITORACLE_SID=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

DATA=/opt/oracle/oradata

DEV_SDA02=${DATA}/scsi01

DEV_SDA03=$DATA/scsi02
#DEV_SDA05=$DATA/scsi03
#DEV_SDA06=$DATA/scsi04

DEV_HDD1=${DATA}/ide01
#DEV_HDD2=$DATA/ide02

DEV_HDD5=${DATA}/u01
#DEV_HDD6=$DATA/u02

CONTROL1=${DEV_HDD5}/${ORACLE_SID}/control01.ctl
CONTROL2=${DEV_HDD1}/${ORACLE_SID}/control02.ctl
CONTROL3=${DEV_SDA02}/${ORACLE_SID}/control03.ctl

# We need to have at least two redo log groups consisting
# of two or more members. Each member in a group must be 
# on a seperate disk.
# Since I have trhee dsiks I am going to use 3 groups with 
# three members. Please see the Admin guide for more info on
# why I am doing this.

# Group 1
REDO01a=${DEV_HDD5}/${ORACLE_SID}/redo01a.log
REDO01b=${DEV_HDD1}/${ORACLE_SID}/redo01b.log
REDO01c=${DEV_SDA02}/${ORACLE_SID}/redo01c.log


# Group 2 
REDO02a=${DEV_HDD5}/${ORACLE_SID}/redo02a.log
REDO02b=${DEV_HDD1}/${ORACLE_SID}/redo02b.log
REDO02c=${DEV_SDA02}/${ORACLE_SID}/redo02c.log

# Group 3
REDO03a=${DEV_HDD5}/${ORACLE_SID}/redo03a.log
REDO03b=${DEV_HDD1}/${ORACLE_SID}/redo03b.log
REDO03c=${DEV_SDA02}/${ORACLE_SID}/redo03c.log


# These are the archive locations
REDO_LOG_ARCHIVE01=${DATA}/${DEV_HDB05}/REDO_LOG_ARCHIVE01
REDO_LOG_ARCHIVE02=${DATA}/${DEV_HDD01}/REDO_LOG_ARCHIVE02
REDO_LOG_ARCHIVE03=${DATA}/${DEV_SDA03}/REDO_LOG_ARCHIVE03


SYSTEM_FILE=${DEV_SDA02}/${ORACLE_SID}/system01.dbf
TEMP_FILE=${DEV_SDA02}/${ORACLE_SID}/temp01.dbf
UNDOTBS_FILE=${DEV_SDA02}/${ORACLE_SID}/undotbs01.dbf
MAIN_FILE=${DEV_SDA02}/${ORACLE_SID}/main01.dbf
INDEX_FILE=${DEV_SDA02}/${ORACLE_SID}/indx01.dbf
USERS_FILE=${DEV_SDA02}/${ORACLE_SID}/users01.dbf
CATALOG_SCRIPT=${ORACLE_HOME}/rdbms/admin/catalog.sql;
CATPROC_SCRIPT=${ORACLE_HOME}/rdbms/admin/catproc.sql;
BACKGROUND_DUM_DEST=/opt/oracle/admin/${ORACLE_SID}/bdump
CORE_DUMP_DEST=/opt/oracle/admin/${ORACLE_SID}/cdump
USER_DUMP_DEST=/opt/oracle/admin/${ORACLE_SID}/udump


# The redo size will determine how big the archived 
# logs are and will be very dependant on how long you want
# the recovery procedures to take and how much usage your 
# database gets.
# I am using small values here to enable testing of this script.

REDO_SIZE="100K"
#SYSTEM_FILE_SIZE="600M"
SYSTEM_FILE_SIZE="400M"
#TEMP_FILE_SIZE="400M"
TEMP_FILE_SIZE="100M"
#MAIN_FILE_SIZE="600M"
MAIN_FILE_SIZE="400M"
#INDEX_FILE_SIZW="400M"
INDEX_FILE_SIZW="100M"
#UNDOTBS_FILE_SIZE="200M"
UNDOTBS_FILE_SIZE="100M"
#USERS_FILE_SIZE="400M"
USERS_FILE_SIZE="100M"

FILES="
$CONTROL1
$CONTROL2
$CONTROL3
$REDO01a
$REDO01b
$REDO01c
$REDO02a 
$REDO02b 
$REDO02c 
$REDO03a
$REDO03b
$REDO03c
$REDO_LOG_ARCHIVE01
$REDO_LOG_ARCHIVE02
$REDO_LOG_ARCHIVE03
$SYSTEM_FILE
$TEMP_FILE
$UNDOTBS_FILE
$INDEX_FILE
$USERS_FILE
"

FILES2="
$BACKGROUND_DUM_DEST
$CORE_DUMP_DEST
$USER_DUMP_DEST
"

echo " $FILES

We have now decided in the above where all the files etc are 
meant to go so now I am going to create the directories if 
they do not already exist."
echo
echo -n "Hit x to stop any other key to continue: "
read CONTINUE
echo

if [ $CONTINUE = x ]; then
    exit 1
fi

for directory in `echo ${FILES}`
do
    if [ ! -d $(dirname $directory) ]; then

        mkdir -p $(dirname $directory);
        echo
        echo "I have just created the following directory."
        echo " $directory"
        echo
        sleep 1
    fi
done

for directory in `echo ${FILES2}`
do
    if [ ! -d $directory ]; then

        mkdir -p $directory;

        echo
        echo "I have just created the following directory."
        echo " $directory"
        echo
        sleep 1

    fi
done

if [ -z $ORATAB_ENTRY ]; then

    echo
    echo "We are now updating your /etc/oratab file."
    echo
    echo "# The entry below was entered by the script $0" << /etc/oratab
    echo "${ORACLE_SID}:${ORACLE_HOME}:N"  << /etc/oratab
    echo  

fi

PATH=$ORACLE_HOME/bin:/usr/bin:/bin

ORAPWD=/opt/oracle/product/9.2.0/dbs/orapw${ORACLE_SID}

if [ -f $ORAPWD ]; then

    rm $ORAPWD

fi

echo  
echo Creating $ORAPWD
orapwd file=$ORAPWD password=change_on_install entries=20
echo
echo Done
echo
echo 
echo "
We need to shut down the instance before we remove the dbf files.
I am about to check if you have an instance running or not. If 
you have and the control files are missing for it then you will 
need to kill the instance first."
echo

sqlplus /nolog<<SHUTDOWN
connect / as sysdba
shutdown
SHUTDOWN

COUNTER=0

until [ -z "$(ps -ax | grep "ora_...._${ORACLE_SID}")" ];
do
    let COUNTER=COUNTER+1
    echo "Working......"
    sleep 1
    if [ $COUNTER -eq 20 ]; then

        echo 
        echo "I was unable to stop the current Oracle instance"
        echo "Please try to stop it yourself and then try again."
        echo
            PROCESS_TO_KILL=$( ps ax | grep ora_...._${ORACLE_SID} | awk ' { FS = " " ; print $1  } ' );

            for processes in $( echo $PROCESS_TO_KILL )
            do
                echo "The following processes appear to be running."
                echo $processes
            done
    fi
done
for file in `echo ${FILES}`
do
    if [ -e $file ]; then

        echo Removing: "$file"
        rm -rf $file
        echo
    fi
done

if [ -f $INITORACLE_SID ]; then

     rm -f $INITORACLE_SID
fi
touch $INITORACLE_SID
cat > $INITORACLE_SID<<ENDOFINITORACLE_SID
##############################################################################
# HARRY JACKSON 
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=33554432
db_file_multiblock_read_count=16
db_files = 80   

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=${ORACLE_SID}
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=$BACKGROUND_DUM_DEST
core_dump_dest=$CORE_DUMP_DEST
timed_statistics=TRUE
user_dump_dest=$USER_DUMP_DEST
 
###########################################
# File Configuration
###########################################
control_files=("$CONTROL1", 
               "$CONTROL2", 
               "$CONTROL3")
###########################################
# Instance Identification
###########################################
instance_name=${ORACLE_SID}
 
###########################################
# Job Queues
###########################################
job_queue_processes=10
 
###########################################
# MTS
###########################################
dispatchers="(ADDRESS=(PROTOCOL=TCP) (SERVICE=${ORACLE_SID}lXDB))(DISPATCHERS=2)"
max_dispatchers=2

# How many servers to start at startup. I am not expecting much usage so
# 1 should be fine.
shared_servers=1

# Allow 1 shered server for every ten connections 
max_shared_servers=200 

# I have only put this here so that I can use it.
shared_server_sessions=2000 

###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0
 
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE



###########################################
# Pools
###########################################
java_pool_size=104857600   # 
large_pool_size=16777216  #
shared_pool_size=104857600 #
 
###########################################
# Processes and Sessions
###########################################
processes=150
log_checkpoint_interval = 10000
log_buffer = 32768
parallel_max_servers = 5
###########################################
# Redo Log and Recovery
###########################################
# We set the archive destinations to three seperate disks
log_archive_destination_1 = 'LOCATION=${DATA}/${DEV_HDB05}/${REDO_LOG_ARCHIVE01}' 
log_archive_destination_2 = 'LOCATION=${DATA}/${DEV_HDD01}/${REDO_LOG_ARCHIVE02}' 
log_archive_destination_3 = 'LOCATION=${DATA}/${DEV_SDA03}/${REDO_LOG_ARCHIVE03}' 

# This setting stipulates how many archive destinations that 
# must be writable for Oracle to continue archiving.
log_archive_min_succeed_dest=2

# This set the name of the archive file %t is the thread and %s is the log number
log_archive_format=arch%t_%s.arc

# This starts the archiving on starting the database.
log_archive_start=true

# With this set Oracle will write a checksum to the header of the online redo log
# when it gets archived it will then check the two checksums to ensure that there
# has been no corruption
db_block_checksum=true

fast_start_mttr_target=300
 
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
#undo_tablespace=UNDOTBS1
ENDOFINITORACLE_SID

sqlplus -s /nolog<<CREATE_DATABASE
connect / as sysdba
startup nomount
CREATE DATABASE $ORACLE_SID
USER SYS IDENTIFIED BY testing
USER SYSTEM IDENTIFIED BY testing
LOGFILE GROUP 1 ('$REDO01a', '$REDO01b', '$REDO01c') SIZE $REDO_SIZE,
        GROUP 2 ('$REDO02a', '$REDO02b', '$REDO02c') SIZE $REDO_SIZE,
        GROUP 3 ('$REDO03a', '$REDO03b', '$REDO03c') SIZE $REDO_SIZE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
--
DATAFILE '$SYSTEM_FILE' SIZE $SYSTEM_FILE_SIZE REUSE EXTENT MANAGEMENT LOCAL
-- 
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '$TEMP_FILE' SIZE $TEMP_FILE_SIZE  REUSE
--
UNDO TABLESPACE undotbs DATAFILE '$UNDOTBS_FILE'  SIZE $UNDOTBS_FILE_SIZE REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
CREATE_DATABASE

sqlplus -s /nolog<<CREATE_TABLESPACE
connect / as sysdba
CREATE TABLESPACE users LOGGING 
DATAFILE '$USERS_FILE' 
SIZE $USERS_FILE_SIZE REUSE AUTOEXTEND ON NEXT  4000K MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING 
DATAFILE '$INDEX_FILE' 
SIZE $INDEX_FILE_SIZW REUSE AUTOEXTEND ON NEXT  4000K MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE main LOGGING 
DATAFILE '$MAIN_FILE' 
SIZE $MAIN_FILE_SIZE REUSE AUTOEXTEND ON NEXT  4000K MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL;

CREATE_TABLESPACE

sqlplus -s /nolog<<CREATE_DICTIONARY
connect / as sysdba
@$CATALOG_SCRIPT;
@$CATPROC_SCRIPT;
CREATE_DICTIONARY

# You will note here that the rman user is going to be 
# using a recovery catalog contained in the database just created. 
# This is not recommended its just that I have limited resources
# on this machine.

sqlplus -s /nolog<<CREATE_RMAN_USER
connect / as sysdba
CREATE TABLESPACE rman_tablespace
DATAFILE '/opt/oracle/oradata/rman_tablespace01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 4000K;
CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman_tablespace 
QUOTA UNLIMITED ON rman_tablespace;
GRANT CONNECT, RECOVERY_CATALOG_OWNER, SYSDBA TO rman;
CREATE_RMAN_USER

sqlplus -s /nolog<<POST_CREATIONS
connect / as sysdba
ALTER DATABASE FORCE LOGGING;
POST_CREATIONS

# There are a lot more thinga that need to be done now. We could for instance
# create our recovery catalog. 

/opt/oracle/product/9.2.0/bin/rman RCVCAT rman/rman@mynewdb.world<<RMAN_CREATE_CATALOG
CREATE CATALOG;
RMAN_CREATE_CATALOG