#!/bin/sh

    # Create a database and configuration files for the 
    # oracle listener and client programs

    # This script depends on several environment variables set in 
    # ~oracle/.bashrc.oracle, including
    #
    #    ORACLE_HOME
    #    ORACLE_SID
    #    HOST_NAME
    #    DOMAIN_NAME
    #

    WORK_DIR=$ORACLE_HOME/dbs

    # If you want data and tablespace files to be elsewhere, change DATA_DIR below
    DATA_DIR=$WORK_DIR

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

    if [ ! -d $WORK_DIR ]; then 
        echo "Unable to find ${WORK_DIR}"
        echo "Make sure environment is correctly configured" 1>&2
        exit 2
    fi

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

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

    rollback_segments = (system, rback1)

    processes = 50              
    open_cursors = 1000

    __EOF__

    # Set up your LISTENER.ORA file
    cat <<__EOF__ >${ORACLE_HOME}/network/admin/listener.ora
    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
    cat <<__EOF__ >${ORACLE_HOME}/network/admin/tnsnames.ora
    # 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})
    #    )
    #  )

    # You can add other service names below to connect to remote databases
    __EOF__

    ID="$WORK_DIR/${0}$$.log"
    echo "Attempting to create database, logging to ${ID}.stdout"

    # Make sure the db is down
    sqlplus /nolog <<__EOF__ >/dev/null 2>&1
    connect / as sysdba
    shutdown abort
    __EOF__

    # We need to be here to find .sql files below
    pushd $ORACLE_HOME > /dev/null 2>&1

    # Actually create the db
    sqlplus /nolog <<__EOF__ >${ID}.stdout
    connect / as sysdba
    set echo on
    set termout off 
    spool $ID
    !date
    startup force nomount pfile=${WORK_DIR}/init${ORACLE_SID}.ora

    create database controlfile reuse
       datafile '${DATA_DIR}/data.dbf'  size 500M reuse
       logfile  '${LOG_DIR}/log1.dbf'  size 100M reuse,
                '${LOG_DIR}/log2.dbf'  size 100M reuse
       maxinstances 1
       maxdatafiles 32 
       maxlogfiles 32 
       maxlogmembers 2 
       maxarchlogs 0 
       character set "us7ascii";

    create rollback segment rback1 tablespace system
        storage(initial 1M next 1M MAXEXTENTS UNLIMITED);
    alter rollback segment rback1 online;

    create tablespace tmeda
        datafile '${DATA_DIR}/tmeda01.dbf' size 1000M
        extent management local uniform size 160k;

    create tablespace imeda
        datafile '${DATA_DIR}/imeda01.dbf' size 1000M
        extent management local uniform size 160k;

    @?/rdbms/admin/catalog.sql 
    @?/rdbms/admin/catproc.sql 
    !date
    !date
    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
    !date
    shutdown

    connect / as sysdba
    startup pfile=${WORK_DIR}/init${ORACLE_SID}.ora
    __EOF__
    popd >/dev/null 2>&1

    lsnrctl start

    echo "Database created and running, check log file for details"

    exit 0