#! /usr/bin/ksh

# @version $Id: createWFSDB_DW.ksh,v 1.1 2007/10/29 19:39:26 dweiner Exp $

#---------------------------------------------------------------------
# Initialization  ------------------------------
#---------------------------------------------------------------------
##
function Initialization
{
export ORACLE_SID=DW_T1
export db_common=/usr/local/n2bb/watchpoint/DW
export OH=/home/oracle
export ora_base=/home/oracle/admin
export db_scripts=/usr/local/n2bb/watchpoint/DW
export ora_data=/oradata
export ora_data1=/oradata1
export ora_data2=/oradata2
export ora_data3=/oradata3
export ora_data4=/oradata4
export ora_data5=/oradata5
export ora_backup=/usr/local/n2bb/backup
export l_log=${db_common}/createDatabase_DW.log
export oraver=`basename $ORACLE_HOME`

if [ "${oraver}" != "10.2.0" ]
 then
  echo "Oracle Database Version ${oraver} is not a tested version for this install.."
  echo "Stopping the installation."
  echo "Oracle Database Version ${oraver} is not a tested version for this install.." >> $l_log
  echo "Stopping the installation." >> $l_log
  exit 1
fi

l_date=`date`

echo "Check to see which oratab to use "
echo "Check to see which oratab to use " > $l_log
if test -f /var/opt/oracle/oratab
then
    ORATAB=/var/opt/oracle/oratab
fi

if test -f /etc/oratab
then
    ORATAB=/etc/oratab
fi

export ORATAB
echo "Using the ${ORATAB} file "
echo "Using the ${ORATAB} file " >> $l_log

} #END Initialization

#---------------------------------------------------------------------
# CREATING the specified TTV database  ------------------------------
#---------------------------------------------------------------------
function createDatabase
{

echo "\tInitializing the Server......"
echo "Initializing the Server....." >> $l_log
sleep 1

##
##  Create all the database datafile directories
##
echo "\tCreating the ${ORACLE_SID} datafile directories "
echo "Creating the ${ORACLE_SID} datafile directories " >> $l_log

sleep 1

if test ! -d ${ora_base}
then
 echo "\tCreating the ${ora_base} directory "
 echo "Creating the ${ora_base} directory " >> $l_log
         mkdir ${ora_base}
         chmod 775 ${ora_base}
fi

sleep 1

if test ! -d ${ora_base}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID} directory "
 echo "Creating the ${ora_base}/${ORACLE_SID} directory " >> $l_log
         mkdir ${ora_base}/${ORACLE_SID}
         chmod 775 ${ora_base}/${ORACLE_SID}
else
 echo "Failure: The ${ora_base}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_base}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

cd ${ora_base}/${ORACLE_SID}

if test ! -d ${ora_base}/${ORACLE_SID}/create
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID}/create directory "
 echo "Creating the ${ora_base}/${ORACLE_SID}/create directory " >> $l_log
         mkdir ${ora_base}/${ORACLE_SID}/create
         chmod 775 ${ora_base}/${ORACLE_SID}/create
fi

sleep 1

if test ! -d ${ora_base}/${ORACLE_SID}/pfile
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID}/pfile directory "
 echo "Creating the ${ora_base}/${ORACLE_SID}/pfile directory " >> $l_log
         mkdir ${ora_base}/${ORACLE_SID}/pfile
         chmod 775 ${ora_base}/${ORACLE_SID}/pfile
fi

sleep 1

if test ! -d ${ora_base}/${ORACLE_SID}/bdump
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID}/bdump directory "
 echo "Creating the ${ora_base}/${ORACLE_SID}/bdump directory " >> $l_log
         mkdir ${ora_base}/${ORACLE_SID}/bdump
         chmod 775 ${ora_base}/${ORACLE_SID}/bdump
fi

sleep 1

if test ! -d ${ora_base}/${ORACLE_SID}/cdump
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID}/cdump directory "
 echo "Creating the ${ora_base}/${ORACLE_SID}/cdump directory " >> $l_log
        mkdir ${ora_base}/${ORACLE_SID}/cdump
        chmod 775 ${ora_base}/${ORACLE_SID}/cdump
fi

sleep 1

if test ! -d ${ora_base}/${ORACLE_SID}/udump
then
 echo "\tCreating the ${ora_base}/${ORACLE_SID}/udump directory "
 echo "Creating the ${ora_base}/${ORACLE_SID}/udump directory " >> $l_log
        mkdir ${ora_base}/${ORACLE_SID}/udump
        chmod 775 ${ora_base}/${ORACLE_SID}/udump
fi

sleep 1 

if test ! -d ${ora_data}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data}/${ORACLE_SID} directory "
 echo "Creating the ${ora_data}/${ORACLE_SID} directory " >> $l_log
        mkdir ${ora_data}/${ORACLE_SID}
        chmod 775 ${ora_data}/${ORACLE_SID}
else
 echo "Failure: The ${ora_data}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data1}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data1}/${ORACLE_SID} directory "
 echo "Creating the ${ora_data1}/${ORACLE_SID} directory " >> $l_log
         mkdir ${ora_data1}/${ORACLE_SID}
         chmod 775 ${ora_data1}/${ORACLE_SID}
else
 echo "Failure: The ${ora_data1}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data1}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data2}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data2}/${ORACLE_SID} directory "
 echo "Creating the ${ora_data2}/${ORACLE_SID} directory " >> $l_log
         mkdir ${ora_data2}/${ORACLE_SID}
         chmod 775 ${ora_data2}/${ORACLE_SID}
else
 echo "Failure: The ${ora_data2}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data2}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data3}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data3}/${ORACLE_SID} directory "
 echo "Creating the ${ora_data3}/${ORACLE_SID} directory " >> $l_log
         mkdir ${ora_data3}/${ORACLE_SID}
         chmod 775 ${ora_data3}/${ORACLE_SID}
else
 echo "Failure: The ${ora_data3}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data3}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data4}/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data4}/${ORACLE_SID} directory "
 echo "Creating the ${ora_data4}/${ORACLE_SID} directory " >> $l_log
         mkdir ${ora_data4}/${ORACLE_SID}
         chmod 775 ${ora_data4}/${ORACLE_SID}
else
 echo "Failure: The ${ora_data4}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data4}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data5}/${ORACLE_SID}arch
then
 echo "\tCreating the ${ora_data5}/${ORACLE_SID}arch directory "
 echo "Creating the ${ora_data5}/${ORACLE_SID}arch directory " >> $l_log
         mkdir ${ora_data5}/${ORACLE_SID}arch
         chmod 775 ${ora_data5}/${ORACLE_SID}arch
else
 echo "Failure: The ${ora_data5}/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data5}/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ora_data5}/recovery
then
 echo "\tCreating the ${ora_data5}/recovery directory "
 echo "Creating the ${ora_data5}/recovery directory " >> $l_log
        mkdir ${ora_data5}/recovery
        chmod 775 ${ora_data5}/recovery
fi

sleep 1

if test ! -d ${ora_data5}/recovery/${ORACLE_SID}
then
 echo "\tCreating the ${ora_data5}/recovery/${ORACLE_SID} directory "
 echo "Creating the ${ora_data5}/recovery/${ORACLE_SID} directory " >> $l_log
        mkdir ${ora_data5}/recovery/${ORACLE_SID}
        chmod 775 ${ora_data5}/recovery/${ORACLE_SID}
else
 echo "Failure: The ${ora_data5}/recovery/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ora_data5}/recovery/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

if test ! -d ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID}
then
 echo "\tCreating the ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID} directory "
 echo "Creating the ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID} directory " >> $l_log
        mkdir ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID}
        chmod 775 ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID}
else
 echo "Failure: The ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID} directory already exists. "
 echo "Failure: The ${ORACLE_HOME}/rdbms/audit/${ORACLE_SID} directory already exists. " >> $l_log
 exit 1
fi

sleep 1

echo "\tupdating the .bash_profile to contain database"
echo "updating the .bash_profile to contain database" >> $l_log

## Copy database to the .bash_profile
if grep ${ORACLE_SID} /home/oracle/.bash_profile >/dev/null 2>&1
then
        sed "/.*${ORACLE_SID}.*/d" < /home/oracle/.bash_profile > /tmp/bsh_profile.oracle
        cp /tmp/bsh_profile.oracle /home/oracle/.bash_profile
        rm -f /tmp/bsh_profile.oracle
fi

sleep 1

if grep ORACLE_SID= /home/oracle/.bash_profile >/dev/null 2>&1
then
 echo "\tbash_profile already contains another ORACLE_SID"
 echo "bash_profile already contains another ORACLE_SID" >> $l_log
else
 echo "\texport ORACLE_SID=${ORACLE_SID}" >> /home/oracle/.bash_profile
fi

sleep 1

echo "\tCopying listener.ora file to the $ORACLE_HOME/network/admin directory"
echo "Copying listener.ora file to the $ORACLE_HOME/network/admin directory" >> $l_log

if [ "${HOSTTYPE}" = "sparc" ]
then
 oraclehost=`hostname`

 saveoraclehost=$oraclehost

 echo "# Generated by Oracle configuration tools.
 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = $oraclehost )(PORT = 1521))
      )
    )
  )

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = ${ORACLE_HOME})
      (PROGRAM = extproc)
    )
  )" > ${ORACLE_HOME}/network/admin/listener.ora
        oraclehost=$saveoraclehost
else

 echo "# Generated by Oracle configuration tools.
 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ${trans_hostname} )(PORT = 1521))
      )
    )
  )

 SIif test ! -f $ORATABD_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = ${ORACLE_HOME})
      (PROGRAM = extproc)
      (ENVS = \"EXTPROC_DLLS=ANY\")
    )
  )" > ${ORACLE_HOME}/network/admin/listener.ora

fi

sleep 1

echo "\tCopying the ${ORACLE_SID} to the ${ORATAB} file "
echo "Copying the ${ORACLE_SID} to the ${ORATAB} file " >> $l_log
## Copy all necessary databases to the $ORATAB file
if test ! -f $ORATAB
then
 cp ${db_common}/oratab $ORATAB
fi

echo ${ORACLE_SID}:${ORACLE_HOME}:Y >> $ORATAB

##
##  Create all the backup files
##

export createDB_dir=${ora_base}/${ORACLE_SID}/create

sleep 1

## Copy all necessary database scripts to correct locations
echo "\tCopying the ${ORACLE_SID} database scripts to the ${createDB_dir} directory"
echo "Copying the ${ORACLE_SID} database scripts to the ${createDB_dir} directory" >> $l_log

cp ${db_scripts}/* ${createDB_dir}

sleep 1

echo "\tCreating the init${ORACLE_SID} to the ${ora_base}/$ORACLE_SID/pfile directory"
echo "Creating the init${ORACLE_SID} to the ${ora_base}/$ORACLE_SID/pfile directory" >> $l_log

echo "##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
db_block_size              = 8192
db_domain=\"\"
db_name=${ORACLE_SID}
control_files=(\"/oradata/${ORACLE_SID}/control01.ctl\", \"/oradata1/${ORACLE_SID}/control02.ctl\")
###########################################
# Archive
###########################################
log_archive_format = \"${ORACLE_SID}_%t%s%r.ARC\"
log_archive_dest           = \"/oradata5/${ORACLE_SID}arch\"
NLS_LENGTH_SEMANTICS=char
NLS_SORT=BINARY_AI
pga_aggregate_target       = 200M
processes                  = 150
sessions                   = 300
open_cursors               = 1000
undo_management            = AUTO
undo_retention             = 1800
undo_tablespace            = UNDOTBS1
#shared_servers             = 3
compatible                 = 10.1.0.0.0
sga_target                 = 300M
STATISTICS_LEVEL           = TYPICAL
sga_max_size               = 500M
#db_recovery_file_dest      = \"/oradata5/recovery/${ORACLE_SID}/\"
#db_recovery_file_dest_size = 2G
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=${ORACLE_BASE}/admin/${ORACLE_SID}/bdump
core_dump_dest=${ORACLE_BASE}/admin/${ORACLE_SID}/cdump
user_dump_dest=${ORACLE_BASE}/admin/${ORACLE_SID}/udump
audit_file_dest=${ORACLE_HOME}/rdbms/audit/${ORACLE_SID}
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE" > ${ora_base}/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora

sleep 1

echo "\tCreating symbolic link to the init${ORACLE_SID}.ora file"
echo "Creating symbolic link to the init${ORACLE_SID}.ora file" >> $l_log
ln -s ${ora_base}/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

sleep 1

echo "\tCreating oracle password file orapw${ORACLE_SID} "
echo "Creating oracle password file orapw${ORACLE_SID} " >> $l_log
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=n2bbsys

sleep 2

chmod 775 ${ora_base}/${ORACLE_SID}/create/*.ksh

sleep 1

echo "\tCreate the tnsnames.ora file"
echo "Create the tnsnames.ora file" >> $l_log

echo "
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

WFS_PRODUCTIONDB =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ${trans_hostname})(PORT = 1521))
    )
    (CONNECT_DATA = 
      (SID = ${trans_database_name})
      (SERVER = DEDICATED)
    )
  )" > $ORACLE_HOME/network/admin/tnsnames.ora

#
# Create DataRefresh scripts
#
echo "\tCreating the DataRefresh scripts"
echo "Creating the DataRefresh scripts" >> $l_log

mkdir $ora_base/$ORACLE_SID/DataRefresh
mkdir $ora_base/$ORACLE_SID/DataRefresh/Log
mkdir $ora_base/$ORACLE_SID/DataRefresh/DRLog
cp $db_scripts/DataRefresh.ksh $ora_base/$ORACLE_SID/DataRefresh

## Create the database schema and all its objects

cd ${ora_base}/${ORACLE_SID}/create

tstmp=`date +'%m%d%Y%H%M%S'`
outputlog="/usr/local/n2bb/sql/${ORACLE_SID}-$tstmp.log"

./loadWFSDB_DW.ksh y ${ORACLE_SID} ${l_log} 2>&1 | tee -a $outputlog

sleep 1

#
# If the listener is running have it reload otherwise start
#
echo "\tStarting the listener if its not running otherwise reload it"
echo "Starting the listener if its not running otherwise reload it" >> $l_log
STATUS=`ps -A | grep tnslsnr `
if [ $? != 0 ]; then
        lsnrctl start
else
        lsnrctl reload
fi

# COMMENTED OUT DUE TO WFS DB CREATE NOT SETTING UP /usr/local/n2bb/backup/script/crontab FILE
#
# Schedule DataRefresh in the Crontab
#
#echo "\tScheduling the DataRefresh in the Crontab"
#echo "Scheduling the DataRefresh in the Crontab" >> $l_log

#echo "0,15,30,45 * * * * ${ora_base}/${ORACLE_SID}/DataRefresh/DataRefresh.ksh >> ${ora_base}/${ORACLE_SID}/DataRefresh/Log/DataRefresh.log" >> $ora_backup/scripts/crontab

#crontab $ora_backup/scripts/crontab


} #END createDatabase


#---------------------------------------------------------------------
# MAIN  --------------------------------------------------------------
#---------------------------------------------------------------------

Initialization

typeset trans_database_name
typeset trans_hostname
typeset hostname

clear
print ""
print "\t Tandberg Television WatchPoint Data Warehouse Creation Wizard"
print "\t=======================================================\n"
read xxx?"     The WatchPoint Data Warehouse will be created with the default name of DW_T1 [enter to continue] "

print " "
read trans_database_name?"     What is the name of the WatchPoint transactional database? [WP_P1] "

if [[ -z $trans_database_name ]]
then
  trans_database_name=WP_P1
fi

hostname=`hostname -a |cut -d " " -f 1`

print " "
read trans_hostname?"     What is the hostname of the machine that the WatchPoint transaction database is installed [${hostname}] " 

if [[ -z $trans_hostname ]]
then
  trans_hostname=$hostname
fi

if test -d ${ora_base}/${ORACLE_SID}
 then
  echo ""
  echo "\tFailure: The ${ORACLE_SID} database or files from a previous install exists. "
  echo "\tIf you would like to recreate the database remove the old files and try again. "
  echo "Failure: The ${ORACLE_SID} database or files from a previous install exists. " >> $l_log
  echo "If you would like to recreate the database remove the old files and try again. " >> $l_log
  exit 1
fi

  echo ""

createDatabase

echo "Installation Complete..."
echo "Installation Complete..." >> $l_log

exit 0
