#!/bin/ksh
##
## Data Refresh from production!!!
##

date

echo "Refreshing Database data with Production data........"
filetimestamp=$(date +%Y%m%d%H%M%S)
echo " "

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin:/usr/bin:/bin:/usr/bin/X11/:/usr/local/bin:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=DWT1
export LOG_BASE=/home/oracle/DWT1

if [ ! -d "$LOG_BASE" ]; then
echo "creating directory ${LOG_BASE}"
mkdir $LOG_BASE
echo "creating directory ${LOG_BASE}/log"
mkdir $LOG_BASE/log
fi


$ORACLE_HOME/bin/sqlplus /nolog << EOM
    connect wfsdw/wfsdw
    PROMPT Refreshing Data Warehouse Database DIM and FACT Tables with Production Database Data. PLEASE WAIT!
    set termout on echo on serveroutput on
    spool $LOG_BASE/log/DRLogNew$filetimestamp.log
    begin
	WFSDW.PKG_ADMIN.SPMD_LOADDATA;
   end;
   /
   spool off
   set echo off termout off serveroutput off
   exit
EOM

echo " "
echo "DW_T1 Data Refresh completed, please check the DataRefresh.log file......!"
echo " "

# Deletes the first four lines of the log
sed -e '1,4d'  $LOG_BASE/log/DRLogNew$filetimestamp.log >  $LOG_BASE/log/DRLog_$filetimestamp.log
rm $LOG_BASE/log/DRLogNew$filetimestamp.log

#Delete log files older than 30 days
find $LOG_BASE/log/*.log -mtime +30 -exec rm {} \;
date
