export import auto

00 22 * * * /local/app/oracle/dba/scripts/daily_refresh_EMLDSIT.sh > /tmp/daily_refresh_EMLDSIT.sh.log 2>&1
DEV:noc5-grsmkt-ddb1:EMLDDEV:/local/app/oracle
> more /local/app/oracle/dba/scripts/daily_refresh_EMLDSIT.sh
#!/bin/ksh
# IB Core SVCS Oracle
# Created by: Cao Huu Vinh (Vincent).
# Create date: 10 April 2011
PATH=.:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/local/bin
. /local/app/oracle/EMLDSIT_env
DUMP_DIR=/oradumps/PROD_DUMP/
DT=`date '+%Y_%m_%d' `
MAIL_LIST='IB_CORE_SVCS_Oracle@jpmchase.com,Emerald_Dev@jpmchase.com'
LOGFILE=/local/app/oracle/dba/logs/daily_refresh_EMLDSIT.$DT.log
>$LOGFILE
export DUMP_DIR DT LOGFILE
Mail_Alert_Fail()
{
ERRMSG="$1"
/bin/mail -s "`hostname`: ${ORACLE_SID} Nightly Refresh EMLDSIT Failed: `date`" $MAIL_LIST << EOT
$ERRMSG
`cat $LOGFILE`
EOT
exit 1
}

Mail_Alert_Success()
{
ERRMSG="$1"
/bin/mail -s "`hostname`: ${ORACLE_SID} Nightly Refresh EMLDSIT Sucessfully: `date`" $MAIL_LIST << EOT
$ERRMSG
`cat $LOGFILE`
EOT
}
#rsync dumpfile
TODAY=`date '+%d'`
rsync -avz oracle@weh-grstm-pdb01.ny.jpmorgan.com::backup2/MDSPROD/DPDUMP/MDSPROD*${TODAY}*.dmp /oradumps/PROD_DUMP/
echo "`date` : resync completed" >> $LOGFILE
#Tar dumpfile.
FN_CHK=0
for fn in `ls -r ${DUMP_DIR}/MDSPROD*dmp`
do
   echo "++ Trace: File $fn found."
   if [ $FN_CHK -eq 0 ]; then
      FILE_EXP=`echo $fn | cut -f5 -d/`
      FN_CHK=1
   fi
done
if [ $FN_CHK -eq 0 ]; then
   echo "`date`: Export datapump file not Found" >> $LOGFILE
   ERRMSG="Export datapump file  not Found, Refresh $ORACLE_SID failed. Please investigate on logfile below."
   Mail_Alert_Fail "$ERRMSG"
   exit 1
else
   echo "`date`: Export datapump file $FILE_EXP found." >> $LOGFILE
fi
#Step2: recreate schema
sqlplus /nolog <<eof >> $LOGFILE
connect / as sysdba
set timing on
shut immediate;
startup;
drop user mktdata cascade;
CREATE USER mktdata
IDENTIFIED BY sybase99
DEFAULT TABLESPACE GRS_MD_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
grant RESOURCE to MKTDATA;
grant SCHEMA_OWNER to MKTDATA;
-- Grant system privileges to users
grant CREATE SESSION to MKTDATA;
grant UNLIMITED TABLESPACE to MKTDATA;
-- Recreate type
-- @/local/app/oracle/dba/scripts/re_create_type_EMLDSIT.sql
create or replace directory DUMP_DIR_TMP as '$DUMP_DIR';
exit;
eof
#Step3: Import pump
echo "-------------------------------------------" >> $LOGFILE
echo "`date`: Starting impdp " >> $LOGFILE
echo "-------------------------------------------" >> $LOGFILE
IMP_LOG=refresh_EMLDSIT_$DT.log
impdp userid=\"/ as sysdba\" DIRECTORY=DUMP_DIR_TMP DUMPFILE=$FILE_EXP LOGFILE=$IMP_LOG SCHEMAS=mktdata TRANSFORM=oid:n
cat  $DUMP_DIR/$IMP_LOG >> $LOGFILE
echo "-------------------------------------------" >> $LOGFILE
echo "`date`: Impdp on EMLDSIT completed." >> $LOGFILE
echo "-------------------------------------------" >> $LOGFILE
# housekeep old dump file.
/usr/bin/find ${DUMP_DIR} -name 'MDSPROD*dmp' -mtime +2 -exec /bin/rm {} \;
/usr/bin/find /local/app/oracle/dba/logs  -name '*refresh*.log' -mtime +10 -exec /bin/rm {} \;
if [ `/bin/grep -i -c "ORA-" ${LOGFILE}` -gt 12 ]; then
   ERRMSG="Error: Refresh tables on CFLD failed. Please investigate on logfile below: "
   Mail_Alert_Fail "${ERRMSG}"
else
   ERRMSG="REFRESH job successfully."
   Mail_Alert_Success "${ERRMSG}"
fi