#! /usr/bin/ksh

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

##
## create the database
##

create_db=$1
export ORACLE_SID=$2
export l_log=$3

if [ "$create_db" = "y" ]
then

echo "\tCreating the ${ORACLE_SID} database - (Please Wait)........."
echo "\tCreating the ${ORACLE_SID} database - (Please Wait)........." >> $l_log

echo "
   connect / as sysdba
   PROMPT creating the ${ORACLE_SID} database PLEASE WAIT
   set echo off
   set termout off
   spool /home/oracle/admin/${ORACLE_SID}/create/create_WFSDB.lst
   startup nomount pfile=${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora
   create database ${ORACLE_SID}
     user sys identified by tisysAdmin
     user system identified by tisystemAdmin
     controlfile reuse
     MAXLOGFILES 30
     MAXLOGMEMBERS 5
     MAXDATAFILES 50
     LOGFILE ('/oradata/${ORACLE_SID}/redo${ORACLE_SID}1a.log') size 40M,
             ('/oradata2/${ORACLE_SID}/redo${ORACLE_SID}2a.log') size 40M,
             ('/oradata/${ORACLE_SID}/redo${ORACLE_SID}3a.log') size 40M
     character set UTF8
     datafile '/oradata1/${ORACLE_SID}/system.dbf' size 500M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
     sysaux datafile '/oradata1/${ORACLE_SID}/sysaux01.dbf' SIZE 325M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
     default temporary tablespace TEMP
       tempfile '/oradata1/${ORACLE_SID}/temp01.dbf' size 100M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
     undo tablespace UNDOTBS1
       datafile '/oradata4/${ORACLE_SID}/UNDOTBS1.dbf' size 400M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
       EXTENT MANAGEMENT LOCAL
     noarchivelog;
   spool off
   exit
"

sqlplus -s /nolog << EOF
   connect / as sysdba
   PROMPT creating the ${ORACLE_SID} database PLEASE WAIT
   set echo off
   set termout off
   spool /home/oracle/admin/${ORACLE_SID}/create/create_WFSDB.lst
   startup nomount pfile=${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora
   create database ${ORACLE_SID}
     user sys identified by tisysAdmin
     user system identified by tisystemAdmin
     controlfile reuse
     MAXLOGFILES 30
     MAXLOGMEMBERS 5
     MAXDATAFILES 50
     LOGFILE ('/oradata/${ORACLE_SID}/redo${ORACLE_SID}1a.log') size 40M,
	     ('/oradata2/${ORACLE_SID}/redo${ORACLE_SID}2a.log') size 40M,
	     ('/oradata/${ORACLE_SID}/redo${ORACLE_SID}3a.log') size 40M
     character set UTF8
     datafile '/oradata1/${ORACLE_SID}/system.dbf' size 500M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
     sysaux datafile '/oradata1/${ORACLE_SID}/sysaux01.dbf' SIZE 325M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
     default temporary tablespace TEMP
       tempfile '/oradata1/${ORACLE_SID}/temp01.dbf' size 100M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
     undo tablespace UNDOTBS1
       datafile '/oradata4/${ORACLE_SID}/UNDOTBS1.dbf' size 400M
       autoextend on NEXT 50M MAXSIZE UNLIMITED
       EXTENT MANAGEMENT LOCAL
     noarchivelog;
   spool off
   exit
EOF

##
## create the storage segments and load catalog
##

echo "\tBuilding the database catalog - (Please Wait).................."
echo "\tBuilding the database catalog - (Please Wait).................." >> $l_log

sqlplus -s /nolog  << EOF2
   connect / as sysdba
   set echo off
   set termout off
   PROMPT creating the ${ORACLE_SID} tablespaces PLEASE WAIT
   spool /home/oracle/admin/${ORACLE_SID}/create/create_WFSDB2.lst
   create tablespace WFS_tools datafile
          '/oradata1/${ORACLE_SID}/WFS_tools01.dbf' size  50M
          REUSE
          AUTOEXTEND ON
          NEXT 10M
          MAXSIZE UNLIMITED
          EXTENT MANAGEMENT LOCAL
          SEGMENT SPACE MANAGEMENT AUTO;
   alter user system default tablespace system temporary tablespace temp;
   alter user sys temporary tablespace temp;
   @$ORACLE_HOME/rdbms/admin/catalog.sql
   @$ORACLE_HOME/rdbms/admin/catproc.sql
   connect system/tisystemAdmin
   @$ORACLE_HOME/sqlplus/admin/pupbld.sql
   spool off
   exit
EOF2

##
## create the tablespaces for WFS
##

echo "\tCreating tablespaces..........................................."
echo "\tCreating tablespaces..........................................." >> $l_log

sqlplus -s /nolog  << EOF3
   connect / as sysdba
   PROMPT creating the ${ORACLE_SID} tablespaces PLEASE WAIT
   spool /home/oracle/admin/${ORACLE_SID}/create/createWFS_TS.lst
   CREATE OR REPLACE PROCEDURE createWFS_TS AS
   /*
           Created By:     Larry Roberts 12/14/2006
                           For Oracle 10.1 and 10.2
           Create Reason:  Create WFS tablespaces, users,tables, sequences,triggers,views,etc
                           To create all objects in another schema beside WFS change
                           WFS to another schema name prior to compiling and
                           executing this procedure
   */
           wk_database_path        varchar2(100);
           wk_message              varchar2(500);
           wk_count                number := 0;
           VSQLcode                varchar2(3000);
   BEGIN
           wk_message := '';
   /*
   ---------------------------------------------
           CREATING WFS TABLESPACES
   ---------------------------------------------
   */
   -- dropping WFS tablespaces
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFS_data
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFS_index
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFS_temp
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
   -- Creating WFS_data tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create tablespace WFS_data
                           datafile
                           ''/oradata2/${ORACLE_SID}/wfs_data01.dbf''
                           size 500M
                           AUTOEXTEND ON
                           NEXT 250M
                           MAXSIZE UNLIMITED
                           EXTENT MANAGEMENT LOCAL
                           SEGMENT SPACE MANAGEMENT AUTO';
           EXCEPTION
              WHEN OTHERS THEN
              wk_message := 'ERROR';
              DBMS_OUTPUT.PUT_LINE('ERROR: Unable to create WFS_data Tablespace');
              GOTO bypass_install;
           END;
   -- Creating WFS_index tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create tablespace WFS_index
                           datafile
                           ''/oradata3/${ORACLE_SID}/wfs_index01.dbf''
                           size 250M
                           AUTOEXTEND ON
                           NEXT 100M
                           MAXSIZE UNLIMITED
                        EXTENT MANAGEMENT LOCAL
                        SEGMENT SPACE MANAGEMENT AUTO';
           EXCEPTION
              WHEN OTHERS THEN
              wk_message := 'ERROR';
              DBMS_OUTPUT.PUT_LINE('ERROR: Unable to create WFS_index Tablespace');
              GOTO bypass_install;
           END;
   -- Creating WFS_temp tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create temporary tablespace WFS_temp
                           TEMPFILE
                           ''/oradata4/${ORACLE_SID}/wfs_temp01.dbf''
                           SIZE 100M
                           AUTOEXTEND ON
                           NEXT 50M
                           MAXSIZE UNLIMITED
                           EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M';
           EXCEPTION
              WHEN OTHERS THEN
              wk_message := 'ERROR';
              DBMS_OUTPUT.PUT_LINE('ERROR: Unable to create WFS_temp Tablespace');
              GOTO bypass_install;
           END;
           COMMIT;
           <<bypass_install>>
           COMMIT;
           IF wk_message = 'ERROR'
           THEN
              NULL;
           ELSE
              DBMS_OUTPUT.PUT_LINE('Installation of TABLESPACES completed SUCCESSFULLY');
           END IF;
           COMMIT;
   END;
   /
   set serveroutput on
      PROMPT executing to create the WFS tablespaces PLEASE WAIT
      exec createWFS_TS
      exit
EOF3

##
## restart the database using the init${ORACLE_SID}.ora pfile
##

echo "\tRestarting the ${ORACLE_SID} database and enabling archiving............"
echo "\tRestarting the ${ORACLE_SID} database and enabling archiving............" >> $l_log

sqlplus -s /nolog  << EOF4
   connect / as sysdba
   PROMPT shutting down the WFS database to turn on archiving
   shutdown immediate
   startup mount exclusive
   alter database archivelog;
   alter database open;
   exit
EOF4

fi

##
## Load all our WFS stuff
##

echo "\tCreating the WatchPoint 2.5 database objects........"
echo " "
echo "\tCreating the WatchPoint 2.5 database objects........" >> $l_log

sqlplus -s /nolog <<EOM5
   connect / as sysdba
   spool /home/oracle/admin/${ORACLE_SID}/create/createWFS.log
   PROMPT creating the createWFS procedure PLEASE WAIT
   @createWFS.sql
   PROMPT executing the createWFS procedure PLEASE WAIT
   set serveroutput on
   execute createWFS;
   PROMPT recompiling any invalid objects
   @${ORACLE_HOME}/rdbms/admin/utlrp.sql
   select * From sys.install_table;
   select setting_value as "WatchPoint Version"
     from wfs.system_settings 
     where setting_type = 'RELEASE';
   spool off
   exit
EOM5

echo "--------------------------------------------------------------------"
echo "Check the create_WFSDB2.lst, createWFS_TS.lst, createWFS_TS.lst, and"
echo "createWFS.log logs in the" 
echo "/home/oracle/admin/${ORACLE_SID}/create directory for errors"
echo "--------------------------------------------------------------------"

exit 0

