#!/bin/sh

# Create a database and configuration files for the 
# oracle listener and client programs.
#
# This script depends on the following environment variables:
#    ORACLE_HOME
#    ORACLE_SID

DOMAIN_NAME=example.org

WORK_DIR=$ORACLE_HOME/dbs

# If you want data and tablespace files to be elsewhere, change DATA_DIR below
DATA_BASE_DIR=~/oradata
DATA_DIR=$DATA_BASE_DIR/$ORACLE_SID

# If you want log files to be elsewhere, change LOG_DIR below
LOG_DIR=$DATA_DIR

# This is the end of the configuration section, though if you want to 
# customize your listener.ora or tnsnames.ora or the database init file 
# or create statements you can do so below.

HOST_NAME=`hostname`

if [ "x$ORACLE_HOME" == "x" ] || [ "x$ORACLE_SID" == "x" ] || [ ! -d $WORK_DIR ]
then 
    echo "Make sure environment is correctly configured"
    exit 2
fi

if [ ! -d $DATA_DIR ]; then
    mkdir -p $DATA_DIR
fi

if [ ! -d $LOG_DIR ]; then
    mkdir -p $LOG_DIR
fi

# Save a copy of the original file if it exists
save_orig() {
    if [ -f $1 ]; then
        mv $1 ${1}.orig
    fi
}

# Set up your LISTENER.ORA file
echo "Creating listener.ora"
target=$ORACLE_HOME/network/admin/listener.ora
save_orig $target
cat <<__EOF__ >$target
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = ipc)
          (KEY = ${ORACLE_SID})
        )
        # Uncomment this section to listen for TCP requests,
        # but be aware of the security implications
        #(ADDRESS = 
        #  (PROTOCOL = TCP)
        #  (HOST = ${HOST_NAME})
        #  (PORT = 1521)
        #)
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
       (SID_NAME = ${ORACLE_SID})
       (GLOBAL_DBNAME = ${ORACLE_SID}.${DOMAIN_NAME})
       (ORACLE_HOME = ${ORACLE_HOME})
    )
  )
__EOF__

# Setup our TNSNAMES.ORA file
echo "Creating tnsnames.ora"
target=$ORACLE_HOME/network/admin/tnsnames.ora
save_orig $target
cat <<__EOF__ >$target
# IPC service
# Should be the most efficient and secure way to communicate with 
# Oracle for processes running on the same machine.
${ORACLE_SID} =

  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = ipc)
      (KEY = ${ORACLE_SID})
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ${ORACLE_SID}.${DOMAIN_NAME})
    )
  )

# TCP service
# Uncomment this section to listen for TCP requests, but be aware of 
# the security implications.
# Be sure to use 'user/pass@YOURSIDTCP' in SQLPlus or use YOURSIDTCP 
# for the third argument to PHP's OCILogon functions if you need 
# to connect via TCP.
#${ORACLE_SID}TCP = 
#  (DESCRIPTION = 
#    (ADDRESS = 
#      (PROTOCOL = TCP)
#      (HOST = ${HOST_NAME})
#      (PORT = 1521)
#    )
#    (CONNECT_DATA =
#      (SERVICE_NAME = ${ORACLE_SID}.${DOMAIN_NAME})
#    )
#  )

# Add other service names below to connect to remote databases
__EOF__

# Set up your INIT.ORA file
echo "Creating init${ORACLE_SID}.ora"
target=$WORK_DIR/init${ORACLE_SID}.ora
save_orig $target
cat <<__EOF__ >$target
db_name = ${ORACLE_SID}
db_domain = ${DOMAIN_NAME}
instance_name = ${ORACLE_SID}
service_names = ${ORACLE_SID}.${DOMAIN_NAME}

control_files = (${DATA_DIR}/ctl1.dbf, 
                 ${DATA_DIR}/ctl2.dbf,
                 ${DATA_DIR}/ctl3.dbf) 

rollback_segments = (system, rback1)

shared_pool_size = 32M
nls_date_format = "YYYY-MM-DD"
open_cursors = 50
db_create_file_dest = "${DATA_BASE_DIR}"
undo_management=auto

__EOF__

# We need to be here to find .sql files below
cd $ORACLE_HOME

spool_file=`basename ${0}$$.log`
log_file=${LOG_DIR}/${spool_file}
echo "Creating and loading $ORACLE_SID db"
echo "This may take a while; logging to $log_file"

# Actually create the db
sqlplus /nolog <<__EOF__ > /dev/null
connect / as sysdba
shutdown abort
spool ${LOG_DIR}/${spool_file}
set termout off

startup force pfile='${WORK_DIR}/init${ORACLE_SID}.ora' nomount

create database ${ORACLE_SID}
     character set US7ASCII
     maxdatafiles 500
     maxlogmembers 5
     datafile '${DATA_DIR}/system01.dbf' size 500M autoextend on next 10M
     undo tablespace undo
         datafile '${DATA_DIR}/undo01.dbf' size 250M
     default temporary tablespace tempsort
         tempfile '${DATA_DIR}/tempsort01.dbf' size 250M
     logfile group 1 '${LOG_DIR}/redo01a.dbf' size 10M,
             group 2 '${LOG_DIR}/redo02a.dbf' size 10M
;

@?/rdbms/admin/catalog.sql 
@?/rdbms/admin/catproc.sql 

create tablespace tmeda
     datafile '${DATA_DIR}/tmeda01.dbf' size 500M
     autoextend on next 10M;

create tablespace imeda                                       
     datafile '${DATA_DIR}/imeda01.dbf' size 500M
     autoextend on next 10M;

connect system/manager
@?/rdbms/admin/dbmspipe.sql
@?/rdbms/admin/dbmslock.sql
@?/rdbms/admin/dbmsalrt.sql
@?/rdbms/admin/dbmsdesc.sql
@?/rdbms/admin/dbmsjob.sql
@?/rdbms/admin/dbmssql.sql
@?/rdbms/admin/dbmsutil.sql
@?/sqlplus/admin/pupbld.sql
@?/rdbms/admin/utlsampl.sql
@?/sqlplus/admin/help/helpbld.sql
shutdown

connect / as sysdba
startup
__EOF__

lsnrctl start

echo "Database created, see $log_file for details"

exit 0