#! /usr/bin/ksh

# @version $Id: loadWFSDB_DW.ksh,v 1.1 2009/10/22 22:33:42 dweiner Exp $

##
## create the data warehouse
##

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_WFSDW.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_WFSDW.lst
   startup nomount pfile=${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/init${ORACLE_SID}.ora
   create database ${ORACLE_SID}
     user sys identified by n2bbsys
     user system identified by n2bbsys
     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_WFSDW2.lst
   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/n2bbsys
   @$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/createWFSDW_TS.lst
   CREATE OR REPLACE PROCEDURE createWFSDW_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 WFSDW_DATA tablespaces
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFSDW_DATA
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFSDW_INDEX
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
           BEGIN
           EXECUTE IMMEDIATE 'drop tablespace WFSDW_TEMP
                                   including contents and datafiles cascade constraint';
           EXCEPTION
              WHEN OTHERS THEN
                   NULL;
           END;
   -- Creating WFSDW_DATA tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create tablespace WFSDW_DATA
                           datafile
                           ''/oradata2/${ORACLE_SID}/wfsdw_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 WFSDW_DATA Tablespace');
              GOTO bypass_install;
           END;
   -- Creating WFSDW_INDEX tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create tablespace WFSDW_INDEX
                           datafile
                           ''/oradata3/${ORACLE_SID}/wfsdw_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 WFSDW_INDEX Tablespace');
              GOTO bypass_install;
           END;
   -- Creating WFSDW_TEMP tablespace
           BEGIN
           EXECUTE IMMEDIATE
                   'create temporary tablespace WFSDW_TEMP
                           TEMPFILE
                           ''/oradata4/${ORACLE_SID}/wfsdw_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 WFSDW_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 WFSDW tablespaces PLEASE WAIT
      exec createWFSDW_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 Data Warehouse 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 WatchPointDW 2.5 database objects........"
echo " "
echo "\tCreating the WatchPointDW 2.5 database objects........" >> $l_log

sqlplus -s /nolog <<EOM5
   connect / as sysdba
   spool /home/oracle/admin/${ORACLE_SID}/create/createWFSDW.log
   PROMPT creating the createWFSDW procedure PLEASE WAIT
   @createWFS_DW.sql
   PROMPT executing the createWFSDW procedure PLEASE WAIT
   set serveroutput on
   execute createWFS_DW;
   select * From sys.install_table;
   select setting_value as "WatchPointDW Version"
     from wfsdw.system_settings 
     where setting_type = 'RELEASE';
   spool off
   exit
EOM5

sqlplus -s /nolog <<EOM6
   connect / as sysdba
   spool /home/oracle/admin/${ORACLE_SID}/create/createWFSDWprocs.log
   PROMPT creating the WFS_PRODUCTIONDB database link
   Create public database link WFS_PRODUCTIONDB 
    connect to wfs identified by "wfs" using 'WFS_PRODUCTIONDB';
   PROMPT creating the WFSDW.PKG_METADATA package
   @createWFSmd_DW.sql
   PROMPT creating the WFSDW.PKG_DATALOAD package
   @createWFSload_DW.sql
   PROMPT creating the WFSDW.PKG_DATAAGG package
   @createWFSagg_DW.sql
   PROMPT creating the WFSDW.PKG_ADMIN package
   @createWFSadmin_DW.sql
   PROMPT creating the WFSDW.PKG_REPORTS
   @createWFSreport_DW.sql
   set define ~
   PROMPT loading date & time dimensions
   set define &
   @loadWFSdim_DW.sql
   spool off
   exit
EOM6

echo "--------------------------------------------------------------------"
echo "Check the create_WFSDW2.lst, createWFSDW_TS.lst, createWFSDW_TS.lst,"
echo "createWFSDW.log, and createWFSDWprocs.log logs in the" 
echo "/home/oracle/admin/${ORACLE_SID}/create directory for errors"
echo "--------------------------------------------------------------------"

exit 0

