#!/bin/ksh
#*******************************************************************************
#                          KSH PROGRAM
#*******************************************************************************
#
#    PROGRAM ID           : exp_NPRefreshes.ksh
#
#D   DESCRIPTION          : export AC schema for non production refreshes
#D
#
#    INPUT PARAMETERS     : none
#
#    OUTPUT PARAMETERS    : none
#
#
#    INPUT FILES          : none
#
#    OUTPUT FILES         : /orabak2/orasave/DEDWAC01/NPE_Refreshes/expAC_NPRefreshes__v<timestamp>.dmp
#
#    LOG FILES            : /orabak2/orasave/DEDWAC01/NPE_Refreshes/expAC_NPRefreshes__v<timestamp>.log
#
#
#    SPECIAL LOGIC NOTES  : none
#
#*******************************************************************************
#                        MODIFICATION LOG
#
#
#    DATE                               SE
#       DESCRIPTION
#    ---------------------------------------------------------------------------
#    Wed Jan 20 16:24:38 CET 2010       (HRu)
#       added
#          grant all on AC.AMHREMPINPUT  to dpexp;
#          grant all on AC.AMHREMPOUTPUT to dpexp;
#       to prevent ORA-31693 (following Oracle document no. 789566.1)
#    ---------------------------------------------------------------------------
#    Fri Dec 18 15:59:48 CET 2009       (HRu)
#       use datapump in addition to regular export
#    ---------------------------------------------------------------------------
#    Mon Jul  6 15:05:28 CEST 2009      (HRu)
#       added "manual deletion" of old dump files in section
#       "run export script (using SMDB environment)" and removed REDUNDANCY=2
#       as this doesn't work for files > 2GB
#    ---------------------------------------------------------------------------
#    Wed Sep  5 13:12:45 CEST 2007      (HRu)
#       initial version
#    ---------------------------------------------------------------------------
#
#*******************************************************************************
export s_debug=0                                            # initiate debugging toggle (0=nodebug/1=debug)


#*******************************************************************************
# INITIALIZATION
#*******************************************************************************
export s_parmcount=$#
export s_myName=`basename $0`                               # remember my name

export s_sek=`date +'%S'`
export s_min=`date +'%M'`
export s_std=`date +'%H'`
export s_tag=`date +'%d'`
export s_mon=`date +'%m'`
export s_jhr=`date +'%Y'`

export s_now="$s_jhr$s_mon$s_tag$s_std$s_min$s_sek"

export s_parmcountExpected=0                                # initiate expected number of input parameters
export s_rc=0                                               # initiate return code

export ORACLE_SID=DEDWAC01

export PARPATH=/orabak2/orasave/$ORACLE_SID/NPE_Refreshes
export DMPPATH=/orabak2/orasave/$ORACLE_SID/NPE_Refreshes

export PARFILE=$PARPATH/exp_reg_FULL_AC_NPRefreshes.file
export PARFILE_DP=$PARPATH/exp_dp_FULL_AC_NPRefreshes.file
export DMPFILE=$DMPPATH/expAC_NPRefreshes

export s_dpdir=/orabak2/orasave/$ORACLE_SID/datapump
export s_dpdir_transfer=/transfer/DEDWAC01/datapump


#--------------------------------------------------------------------------------
# clear datapump directory
#--------------------------------------------------------------------------------
rm -rf $s_dpdir/* 2>/dev/null


#--------------------------------------------------------------------------------
# get current timestamp
#--------------------------------------------------------------------------------
fun_TimeStamp () {

   s_TS_sek=`date +'%S'`
   s_TS_min=`date +'%M'`
   s_TS_std=`date +'%H'`
   s_TS_tag=`date +'%d'`
   s_TS_mon=`date +'%m'`
   s_TS_jhr=`date +'%Y'`

   #s_TS_nowStrg="$s_TS_jhr$s_TS_mon$s_TS_tag$s_TS_std$s_TS_min$s_TS_sek"
   s_TS_nowDate="$s_TS_jhr-$s_TS_mon-$s_TS_tag $s_TS_std:$s_TS_min:$s_TS_sek"

   echo $s_TS_nowDate

}


#*******************************************************************************
# here we go...
#*******************************************************************************
echo '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
echo `fun_TimeStamp` - Start $s_myName $ORACLE_SID


#--------------------------------------------------------------------------------
# set SMDB internal environment
#--------------------------------------------------------------------------------
. /opt/oracle2/.Application


#--------------------------------------------------------------------------------
# cd to a known directory
#--------------------------------------------------------------------------------
cd /opt/oracle2/bin


#--------------------------------------------------------------------------------
# run datapump export
#--------------------------------------------------------------------------------
sqlplus "/ as sysdba" <<-EOSQL 1>/tmp/exp_NPRefreshes_dp.log 2>&1

set pages 1000
set lines 150

prompt drop directory dpump_dir;
drop directory dpump_dir;

prompt drop directory dpump_log;
drop directory dpump_log;


prompt create or replace directory dpump_dir as '$s_dpdir';
create or replace directory dpump_dir as '$s_dpdir';

prompt create or replace directory dpump_log as '$s_dpdir';
create or replace directory dpump_log as '$s_dpdir';


prompt create tablespace dp_exp
create tablespace dp_exp logging
   datafile '/oradat202/oradata/${ORACLE_SID}/dp_exp.dbf' size 200m autoextend
   on next 100m maxsize 2000m extent management local
   segment space management auto;


prompt drop user dpexp;
drop user dpexp;

prompt create user dpexp identified by dpexp;
create user dpexp identified by dpexp;

grant create table to dpexp;
grant connect to dpexp;
grant exp_full_database to dpexp;

alter user dpexp default role all;
alter user dpexp default tablespace dp_exp temporary tablespace temp account unlock;
alter user dpexp quota unlimited on dp_exp;

grant read, write on directory dpump_dir to dpexp;
grant read, write on directory dpump_log to dpexp;

grant all on AC.AMHREMPINPUT  to dpexp;
grant all on AC.AMHREMPOUTPUT to dpexp;
grant all on AC.AMHISTORY to dpexp;

prompt commit;
commit;

quit
EOSQL


#--------------------------------------------------------------------------------
# PUMP!
#--------------------------------------------------------------------------------
expdp dpexp/dpexp parfile=$PARFILE_DP


#--------------------------------------------------------------------------------
# copy dump files to transfer volume (if mounted on SC)
#--------------------------------------------------------------------------------
ssh oracle1@defrsdfpd301 ls -l $s_dpdir 1>/dev/null 2>&1
s_rc=$?

if [[ $s_rc -ne 0 ]]
then
    ssh oracle1@defrsdfpd301 rm $s_dpdir/* 1>/tmp/exp_NPRefreshes_rm_transfer.log 2>&1
    scp $s_dpdir/*.* oracle1@defrsdfpd301:$s_dpdir_transfer/
fi


#*******************************************************************************
# FINE
#*******************************************************************************
echo `fun_TimeStamp` - End $s_myName $ORACLE_SID
echo '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'