#!/bin/bash
#
###########################################################################
##  DPEXPIMP - Datapump export/import
##
##  - export database schema using datapump
##  - import database schema using datapump
##  - refresh a database schema with data from another database schema
##
##  ROP/ABS Laundry Business Solutions
###########################################################################

ABS_ROOTDIR=/abs/scripts

if [ -x dpexpimpset.sh ]
then
    . ./dpexpimpset.sh
else
    if [ -x $ABS_ROOTDIR/dpexpimpset.sh ]
    then
        . $ABS_ROOTDIR/dpexpimpset.sh
    else
        echo "DB export/import script - settings not found !  Please check..."
        echo "Aborting action"
        exit 1
    fi
fi

setcurrenttimedate()
{
    ABS_CURRENTDATE=`date +%Y%m%d`
    ABS_CURRENTTIME=`date +%H%M%S`
}

###########################################################################
#### Internal variables /parameters -- DO NOT CHANGE ####################################
SCRIPTRELEASE="1.0"             # script version
SCRIPTDATE="100914"             # date of last change to script (ddmmyy)
SCRIPTUSER=$(whoami)            # user that started the script. 
SCRIPTUSERID=$(id -u)           # userID that started the script. 
LOGDATE=`date +%Y_%V*`
SCRIPTVERSION="$SCRIPTRELEASE"_"$SCRIPTDATE"

DUMPDIRNAME=ABSDUMPDIR
DOZIP=Y
ACTION=
DOLOGVARS=Y
DOAFTERIMPORT=N

export ACTION=X

setcurrenttimedate

ABS_LOGDIR="$ABS_ROOTDIR"/log
[ ! -d "$ABS_LOGDIR" ] && mkdir "$ABS_LOGDIR"
[ ! -d "$ABS_LOGDIR" ] && ABS_LOGDIR="$ABS_ROOTDIR"
DPEXPIMPLOG=`date +"$ABS_LOGDIR"/dpexpimp_%Y_%V.log`

touch "$DPEXPIMPLOG"
echo ---[ START ]------------------------------------------------------------------- >> "$DPEXPIMPLOG"
echo $ABS_CURRENTDATE / $ABS_CURRENTTIME >> "$DPEXPIMPLOG"
echo COMMANDLINE: `basename $0` $* >> "$DPEXPIMPLOG"

###########################################################################
# Logging
writelog()
{
    # $1 = echo text to screen Y/N
    # $2 = text
    [ "$1" == Y ] && echo "$2"
    echo "`date +%H%M%S` $2" >> "$DPEXPIMPLOG"
}

logexpvars()
{
    echo "FROM_SID=$FROM_SID" >> "$DPEXPIMPLOG"
    echo "FROM_USER=$FROM_USER" >> "$DPEXPIMPLOG"
    echo "FROM_PWD=$FROM_PWD" >> "$DPEXPIMPLOG"
    echo "FROM_SERVER=$FROM_SERVER" >> "$DPEXPIMPLOG"
    echo "FROM_SYSUSER=$FROM_SYSUSER" >> "$DPEXPIMPLOG"
    echo "FROM_SYSPWD=$FROM_SYSPWD" >> "$DPEXPIMPLOG"
    echo "FROM_DUMPDIR=$FROM_DUMPDIR" >> "$DPEXPIMPLOG"
    echo "FROM_DUMPFILENAME=$FROM_DUMPFILENAME" >> "$DPEXPIMPLOG"
    echo "EXPORTVERSION=$EXPORTVERSION" >> "$DPEXPIMPLOG"
    echo "EXPORTDEST=$EXPORTDEST" >> "$DPEXPIMPLOG"
    echo "EXPCOPYMOVEDEST=$EXPCOPYMOVEDEST" >> "$DPEXPIMPLOG"
}

logimpvars()
{
    echo "TO_SID=$TO_SID" >> "$DPEXPIMPLOG"
    echo "TO_USER=$TO_USER" >> "$DPEXPIMPLOG"
    echo "TO_PWD=$TO_PWD" >> "$DPEXPIMPLOG"
    echo "TO_SERVER=$TO_SERVER" >> "$DPEXPIMPLOG"
    echo "TO_SYSUSER=$TO_SYSUSER" >> "$DPEXPIMPLOG"
    echo "TO_SYSPWD=$TO_SYSPWD" >> "$DPEXPIMPLOG"
    echo "TO_DUMPDIR=$TO_DUMPDIR" >> "$DPEXPIMPLOG"
    echo "TO_DUMPFILENAME=$TO_DUMPFILENAME" >> "$DPEXPIMPLOG"
    echo "IMPENVIRONMENT=$IMPENVIRONMENT" >> "$DPEXPIMPLOG"
}

logvars()
{
    if [ "$DOLOGVARS" == Y ]
    then
        echo "ACTION=$ACTION" >> "$DPEXPIMPLOG"
        echo "ORACLE_HOME=$ORACLE_HOME" >> "$DPEXPIMPLOG"
        if [ "$ACTION" == E ]
        then
            logexpvars
        else
            if [ "$ACTION" == I ]
            then
                logimpvars
            else
                logexpvars
                logimpvars
            fi
        fi
        echo "DOAFTERIMPORT=$DOAFTERIMPORT" >> "$DPEXPIMPLOG"
        echo "PARFILE=$PARFILE" >> "$DPEXPIMPLOG"
        echo "DOZIP=$DOZIP" >> "$DPEXPIMPLOG"
    fi
}
###########################################################################

show_usage()
{   
    echo "Version $SCRIPTVERSION"
	echo "Usage: `basename $0` -im|-ex|-re [options]"
    echo
    echo "OPTIONS:"
    echo "-ex                  only export"
    echo "-im                  only import"
    echo "-re                  refresh - first export, then import"
    echo
    echo "-zi                  gzip exportfile. Used only with -ex"
    echo
    echo "-fs:xxx              export from this SID"
    echo "-fu:xxx              export schema name"
    echo "-fp:xxx              export schema password"
    echo "-fc:xxx              export from this server"
    echo "-fd:xxx              set directory where dumpfile export is written"
    echo "-fn:xxx              set filename for export dumpfile"
    echo
    echo "-ts:xxx              import in this SID"
    echo "-tu:xxx              import schema name"
    echo "-tp:xxx              import schema password"
    echo "-tc:xxx              import to this server"
    echo "-td:xxx              set directory where import dumpfile read"
    echo "-tn:xxx              set filename for import dumpfile (not used with /B or /E)"
    echo
    echo "-ie:xxx              set database environment after import to xxx (production,"
    echo "                     test, etc)"
    echo
    echo "-u1:xxx              export database SYSDBA username"
    echo "-u2:xxx              import database SYSDBA username"
    echo
    echo "-p1:xxx              export database SYSDBA password"
    echo "-p2:xxx              import database SYSDBA password"
    echo
    echo "-pf:xxx              use parfile "
    echo
    echo "-ev:xxx              version of export file. Can be COMPATIBLE, LATEST or a"
    echo "                     versionstring like 11.2.0 or 10.1. Minimum value is 9.2."
    echo
    echo "-mc:xxx              can be C (copy) or M (move) to select copying or moving"
    echo "                     the (maybe zipped) dumpfile to the destination, or N (no)"
    echo "                     to do nothing"
    echo "-ed:xxx              location to copy or move the (maybe zipped) exportfile to"
    echo
    echo "-da:Y                Run after import script. ONLY use this when refreshing a"
    echo "                     testdatabase with live data !"
    echo
    echo "-ft:www:xxx:yyy:zzz  Send (maybe zipped) dumpfile to ftp after export"
    echo "                     www:ftp server"
    echo "                     xxx:user"
    echo "                     yyy:password"
    echo "                     zzz:location"
    echo
}

#### Handle commandline options #########################################################
while getopts d:e:f:i:m:p:r:t:u:z: OPT
do
    unset ABS_PARAM
    unset ABS_PARAMVAL
    if [ ${#OPTARG} -ge 2 ]
    then
        ABS_PARAM=${OPTARG:0:1}
        ABS_PARAMVAL=${OPTARG:2}
    fi
    
	case "$OPT" in
        d)      if [ "$ABS_PARAM" == a ]
                then
                    export DOAFTERIMPORT=$ABS_PARAMVAL
                fi;;
        e)      if [ "$OPTARG" == x ]
                then
                    export ACTION=E
                else
                    if [ "$ABS_PARAM" == d ]
                    then
                        export EXPORTDEST=$ABS_PARAMVAL
                    else
                        if [ "$ABS_PARAM" == v ]
                        then
                            export EXPORTVERSION=$ABS_PARAMVAL
                        else
                            export ACTION=X
                        fi
                    fi
                fi;;
        f)      if [ "$ABS_PARAM" == c ]
                then
                    export FROM_SERVER=$ABS_PARAMVAL
                else
                    if [ "$ABS_PARAM" == d ]
                    then
                        export FROM_DUMPDIR=$ABS_PARAMVAL
                    else
                        if [ "$ABS_PARAM" == n ]
                        then
                            export FROM_DUMPFILENAME=$ABS_PARAMVAL
                        else
                            if [ "$ABS_PARAM" == p ]
                            then
                                export FROM_PWD=$ABS_PARAMVAL
                            else
                                if [ "$ABS_PARAM" == s ]
                                then
                                    export FROM_SID=$ABS_PARAMVAL
                                else
                                    if [ "$ABS_PARAM" == u ]
                                    then
                                        export FROM_USER=$ABS_PARAMVAL
                                    else
                                        if [ "$ABS_PARAM" == t ]
                                        then
                                            export FTPDEFS=$ABS_PARAMVAL
                                        else
                                            export ACTION=X
                                        fi
                                    fi
                                fi
                            fi
                        fi
                    fi
                fi;;
        i)      if [ "$OPTARG" == m ]
                then
                    export ACTION=I
                else
                    if [ "$ABS_PARAM" == e ]
                    then
                        export IMPENVIRONMENT=$ABS_PARAMVAL
                    else
                        export ACTION=X
                    fi
                fi;;
        m)      if [ "$ABS_PARAM" == c ]
                then
                    export EXPCOPYMOVEDEST=$ABS_PARAMVAL
                else
                    export ACTION=X
                fi;;
        p)      if [ "$ABS_PARAM" == 1 ]
                then
                    export FROM_SYSPWD=$ABS_PARAMVAL
                else
                    if [ "$ABS_PARAM" == 2 ]
                    then
                        export TO_SYSPWD=$ABS_PARAMVAL
                    else
                        if [ "$ABS_PARAM" == f ]
                        then
                            export PARFILE=$ABS_PARAMVAL
                        else
                            export ACTION=X
                        fi
                    fi
                fi;;
        r)      [ "$OPTARG" == e ] && export ACTION=B;;
        t)      if [ "$ABS_PARAM" == c ]
                then
                    export TO_SERVER=$ABS_PARAMVAL
                else
                    if [ "$ABS_PARAM" == d ]
                    then
                        export TO_DUMPDIR=$ABS_PARAMVAL
                    else
                        if [ "$ABS_PARAM" == n ]
                        then
                            export TO_DUMPFILENAME=$ABS_PARAMVAL
                        else
                            if [ "$ABS_PARAM" == p ]
                            then
                                export TO_PWD=$ABS_PARAMVAL
                            else
                                if [ "$ABS_PARAM" == s ]
                                then
                                    export TO_SID=$ABS_PARAMVAL
                                else
                                    if [ "$ABS_PARAM" == u ]
                                    then
                                        export TO_USER=$ABS_PARAMVAL
                                    else
                                        export ACTION=X
                                    fi
                                fi
                            fi
                        fi
                    fi
                fi;;
        u)      if [ "$ABS_PARAM" == 1 ]
                then
                    export FROM_SYSUSER=$ABS_PARAMVAL
                else
                    if [ "$ABS_PARAM" == 2 ]
                    then
                        export TO_SYSUSER=$ABS_PARAMVAL
                    else
                        export ACTION=X
                    fi
                fi;;
        z)      if [ "$OPTARG" == i ]
                then
                    export DOZIP=Y
                else
                    export ACTION=X
                fi;;
        ?|h)    show_usage; exit 1;;
    esac
done

# PARAMETER ERROR
if [ "$ACTION" == X ]
then
	show_usage
	writelog Y "Parameter error !"
	writelog Y "See logfile for details"
	writelog Y ""
	logvars
	exit 1
fi

checkexpasks()
{
    [ "$FROM_SID" == "-ask-" ] && read -p "Please enter the export SID: " FROM_SID
    [ "$FROM_USER" == "-ask-" ] && read -p "Please enter the export schema user name: " FROM_USER
    [ "$FROM_PWD" == "-ask-" ] && read -p "Please enter the export schema user password: " FROM_PWD
    [ "$ACTION" == B ] && [ "$FROM_SERVER" == "-ask-" ] && read -p "Please enter the export oracle machine name: " FROM_SERVER
    [ "$FROM_SERVER" == "-ask-" -o "$FROM_SERVER" == "" -o ! "$FROM_SERVER" ] && FROM_SERVER=$(hostname)
    [ "$FROM_SYSUSER" == "-ask-" ] && read -p "Please enter the export database sysdba username: " FROM_SYSUSER
    [ "$FROM_SYSPWD" == "-ask-" ] && read -p "Please enter the export database sysdba password: " FROM_SYSPWD
    [ "$FROM_DUMPDIR" == "-ask-" ] && read -p "Please enter the export directory name: " FROM_DUMPDIR
    [ "$FROM_DUMPFILENAME" == "-ask-" ] && FROM_DUMPFILENAME=dpdump_"$FROM_SID"_"$FROM_USER"_"$ABS_CURRENTDATE"_"$ABS_CURRENTTIME"
    case "$EXPCOPYMOVEDEST" in
        C|M|c|m)    [ "$EXPORTDEST" == "-ask-" ] && read -p "Location to copy or move the exportfile to? " EXPORTDEST;;
        *)          EXPCOPYMOVEDEST=N;;
    esac
    [ "$PARFILE" == "-ask-" ] && unset PARFILE
    [ "$EXPORTVERSION" == "-ask-" ] && unset EXPORTVERSION
}

checkimpasks()
{
    [ "$ACTION" == B ] && TO_DUMPFILENAME="$FROM_DUMPFILENAME"

    [ "$TO_SID" == "-ask-" ] && read -p "Please enter the import SID: " TO_SID
    [ "$TO_USER" == "-ask-" ] && read -p "Please enter the import schema user name: " TO_USER
    [ "$TO_PWD" == "-ask-" ] && read -p "Please enter the import schema user password: " TO_PWD
    [ "$ACTION" == B ] && [ "$TO_SERVER" == "-ask-" ] && read -p "Please enter the import oracle machine name: " TO_SERVER
    [ "$TO_SERVER" == "-ask-" -o "$TO_SERVER" == "" -o ! "$TO_SERVER" ] && TO_SERVER=$(hostname)
    [ "$TO_SYSUSER" == "-ask-" ] && read -p "Please enter the import database sysdba username: " TO_SYSUSER
    [ "$TO_SYSPWD" == "-ask-" ] && read -p "Please enter the import database sysdba password: " TO_SYSPWD
    [ "$TO_DUMPDIR" == "-ask-" ] && read -p "Please enter the import directory name: " TO_DUMPDIR
    
    [ "$ACTION" == B ] && TO_DUMPFILENAME="$FROM_DUMPFILENAME"
    [ "$TO_DUMPFILENAME" == "-ask-" ] && read -p "Please enter the import file name (without .dmp): " TO_DUMPFILENAME

    [ "$ACTION" == i ] && [ "$FROM_USER" == "-ask-" ] && read -p "Please enter the export schema user name: " FROM_USER
    if [ "$FROM_SID" == "-ask-" ]
    then
    	case "$ACTION" in
            B)  FROM_SID="$TO_SID";;
            I)  read -p "Please enter the source dump SID: " FROM_SID;;
        esac
    fi
    [ "$FROM_SID" == "-ask-" ] && unset FROM_SID
    [ "$IMPENVIRONMENT" == "-ask-" ] && read -p "Please enter the database environment after import: " IMPENVIRONMENT
    [ "$IMPENVIRONMENT" == "-ask-" ] && unset IMPENVIRONMENT
}

case "$ACTION" in
    E)  checkexpasks;;
    I)  checkimpasks;;
    *)  checkexpasks
        checkimpasks;;
esac

setcurrenttimedate

# check if all needed options are filled
checkexppar()
{
    [ "$FROM_SID" == "" ] && ABSERRORFLAG[4]=1 && ABSERRORFLAG[0]=4
    [ "$FROM_USER" == "" ] && ABSERRORFLAG[5]=1 && ABSERRORFLAG[0]=5
    [ "$FROM_PWD" == "" ] && ABSERRORFLAG[6]=1 && ABSERRORFLAG[0]=6
    [ "$FROM_DUMPDIR" == "" ] && ABSERRORFLAG[8]=1 && ABSERRORFLAG[0]=8
    if [ "$EXPCOPYMOVEDEST" != N ]
    then
        [ "$EXPORTDEST" == "" ] && ABSERRORFLAG[17]=1 && ABSERRORFLAG[0]=17
        [ ! -e "$EXPORTDEST" ] && ABSERRORFLAG[17]=1 && ABSERRORFLAG[0]=17
        MCOK=N
        case "$EXPCOPYMOVEDEST" in
            C|c)    MCOK=Y;;
            M|m)    MCOK=Y;;
            N|n)    MCOK=Y;;
            *)      MCOK=N;;
        esac
        [ "$MCOK" == N ] && ABSERRORFLAG[18]=1 && ABSERRORFLAG[0]=18
    fi
}

checkimppar()
{
    [ "$FROM_SID" == "" ] && ABSERRORFLAG[4]=1 && ABSERRORFLAG[0]=4
    [ "$TO_SID" == "" ] && ABSERRORFLAG[9]=1 && ABSERRORFLAG[0]=9
    [ "$TO_USER" == "" ] && ABSERRORFLAG[10]=1 && ABSERRORFLAG[0]=10
    [ "$TO_PWD" == "" ] && ABSERRORFLAG[11]=1 && ABSERRORFLAG[0]=11
    [ "$TO_DUMPDIR" == "" ] && ABSERRORFLAG[13]=1 && ABSERRORFLAG[0]=13
    [ "$TO_DUMPFILENAME" == "" ] && ABSERRORFLAG[14]=1 && ABSERRORFLAG[0]=14
    [ "$DOZIP" == Y ] && ABSERRORFLAG[19]=1 && ABSERRORFLAG[0]=19
}

declare -a ABSERRORFLAG=(0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0)

[ "$ORACLE_HOME" == "" ] && ABSERRORFLAG[1]=1 && ABSERRORFLAG[0]=1
[ ! -e "$ORACLE_HOME" ] && ABSERRORFLAG[1]=1 && ABSERRORFLAG[0]=1

case "$ACTION" in
    E)  [ "$FROM_SYSUSER" == "" ] && ABSERRORFLAG[2]=1 && ABSERRORFLAG[0]=2
        [ "$FROM_SYSPWD" == "" ] && ABSERRORFLAG[3]=1 && ABSERRORFLAG[0]=3
        checkexppar;;
    I)  [ "$TO_SYSUSER" == "" ] && ABSERRORFLAG[15]=1 && ABSERRORFLAG[0]=15
        [ "$TO_SYSPWD" == "" ] && ABSERRORFLAG[16]=1 && ABSERRORFLAG[0]=16
        checkimppar;;
    B)  [ "$FROM_SYSUSER" == "" ] && ABSERRORFLAG[2]=1 && ABSERRORFLAG[0]=2
        [ "$FROM_SYSPWD" == "" ] && ABSERRORFLAG[3]=1 && ABSERRORFLAG[0]=3
        [ "$TO_SYSUSER" == "" ] && ABSERRORFLAG[15]=1 && ABSERRORFLAG[0]=15
        [ "$TO_SYSPWD" == "" ] && ABSERRORFLAG[16]=1 && ABSERRORFLAG[0]=16
        [ "$TO_DUMPDIR" == "" ] && TO_DUMPDIR="$FROM_DUMPDIR"
        checkimppar
        checkexppar
        [ "$FROM_SERVER" == "" ] && ABSERRORFLAG[7]=1 && ABSERRORFLAG[0]=7
        [ "$TO_SERVER" == "" ] && ABSERRORFLAG[12]=1 && ABSERRORFLAG[0]=12;;
esac

if [ ${ABSERRORFLAG[0]} -gt 0 ]
then
	[ ${ABSERRORFLAG[1]} == 1 ] && writelog Y "ORACLE_HOME not ok. Please set in config batchfile"
	[ ${ABSERRORFLAG[2]} == 1 ] && writelog Y "FROM_SYSUSER not ok [sysdba user, -U1:xxx]"
	[ ${ABSERRORFLAG[3]} == 1 ] && writelog Y "FROM_SYSPWD not ok [sysdba password, -P1:xxx]"
	[ ${ABSERRORFLAG[4]} == 1 ] && writelog Y "FROM_SID not ok [-FS:xxx]"
	[ ${ABSERRORFLAG[5]} == 1 ] && writelog Y "FROM_USER not ok [source schema user name, -FU:xxx]"
	[ ${ABSERRORFLAG[6]} == 1 ] && writelog Y "FROM_PWD not ok [source schema user password, -FP:xxx]"
	[ ${ABSERRORFLAG[7]} == 1 ] && writelog Y "FROM_SERVER not ok [source server machine, -FC:xxx]"
	[ ${ABSERRORFLAG[8]} == 1 ] && writelog Y "FROM_DUMPDIR not ok [source dump directory, -FD:xxx]"
	[ ${ABSERRORFLAG[9]} == 1 ] && writelog Y "TO_SID not ok [-TS:xxx]"
	[ ${ABSERRORFLAG[10]} == 1 ] && writelog Y "TO_USER not ok [destination schema user name, -TU:xxx]"
	[ ${ABSERRORFLAG[11]} == 1 ] && writelog Y "TO_PWD not ok [destination schema user password, -TP:xxx]"
	[ ${ABSERRORFLAG[12]} == 1 ] && writelog Y "TO_SERVER not ok [destination server machine, -TC:xxx]"
	[ ${ABSERRORFLAG[13]} == 1 ] && writelog Y "TO_DUMPDIR not ok [destination dump directory, -TD:xxx]"
	[ ${ABSERRORFLAG[14]} == 1 ] && writelog Y "TO_DUMPFILENAME not ok [filename of dumpfile to be imported, -TN:xxx]"
	[ ${ABSERRORFLAG[15]} == 1 ] && writelog Y "TO_SYSUSER not ok [sysdba user, -U2:xxx]"
	[ ${ABSERRORFLAG[16]} == 1 ] && writelog Y "TO_SYSPWD not ok [sysdba password, -P2:xxx]"
	[ ${ABSERRORFLAG[17]} == 1 ] && writelog Y "EXPORTDEST not ok [Export copy/move destination, -ED:xxx]"
	[ ${ABSERRORFLAG[18]} == 1 ] && writelog Y "EXPCOPYMOVEDEST not ok [copy or move export file, -MC:xxx]"
	[ ${ABSERRORFLAG[19]} == 1 ] && writelog Y "Zip only allowed while exporting dumpfiles [-zi, only used with -ex]"
	writelog Y ""
	writelog Y "Please set up the batch file standard parameters first, or use correct commandline options."
	writelog Y ""
	logvars
    exit 1
fi


# Now configure and do something
FROM_DUMPNAME=$DUMPDIRNAME:$FROM_DUMPFILENAME.dmp
FROM_LOGNAME=$DUMPDIRNAME:"$FROM_DUMPFILENAME"_exp.log
TO_DUMPNAME=$DUMPDIRNAME:$TO_DUMPFILENAME.dmp
TO_LOGNAME=$DUMPDIRNAME:"$TO_DUMPFILENAME"_imp.log

if [ "$FROM_SERVER" == "$TO_SERVER" -o "$FROM_SERVER" == "" -o "$TO_SERVER" == "" -o ! "$FROM_SERVER" -o ! "$TO_SERVER" ]
then
    # same server -- local copy of dumpfile when action=B
	CPYFROM=$FROM_DUMPDIR/$FROM_DUMPFILENAME.*
	CPYTO=$TO_DUMPDIR
else
    # different server -- UNC copy of dumpfile when action=B
	CPYFROM=$FROM_SERVER:/$FROM_DUMPDIR/$FROM_DUMPFILENAME.*
	CPYTO=$TO_SERVER:/$TO_DUMPDIR
fi



###########################################################################
#####  WORKER FUNCTIONS  ##################################################
###########################################################################

#---[ Make dumpfile ]------------------------------------------------------
expdump()
{
    writelog "$LOCALECHO" "*** START EXPORT ***"

    writelog "$LOCALECHO" "Setting up user rights and dumpdir..."
    $ORACLE_HOME/bin/sqlplus -S $FROM_SYSUSER/$FROM_SYSPWD@$FROM_SID AS SYSDBA 2> /dev/null <<-EOF
GRANT CREATE ANY TABLE TO $FROM_USER;
GRANT EXP_FULL_DATABASE TO $FROM_USER;
GRANT IMP_FULL_DATABASE TO $FROM_USER;
CREATE OR REPLACE DIRECTORY $DUMPDIRNAME AS '$FROM_DUMPDIR';
GRANT READ, WRITE ON DIRECTORY $DUMPDIRNAME TO $FROM_USER;
EXIT
EOF

    writelog "$LOCALECHO" "Getting current scn number..."
    CURRENTSCN=$($ORACLE_HOME/bin/sqlplus -S $FROM_SYSUSER/$FROM_SYSPWD@$FROM_SID AS SYSDBA 2> /dev/null <<-EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGES 0
SET NUMFORMAT 999999999999999
SELECT CURRENT_SCN FROM V\$DATABASE;
EXIT
EOF
)
    CURRENTSCN=$(echo $CURRENTSCN)
    writelog "$LOCALECHO" "-- CURRENT_SCN=$CURRENTSCN"

    writelog "$LOCALECHO" "Exporting dumpfile. This may take a while..."
    unset EXTRAPARFILE
    unset PAR_SCN
    unset EXPVERSION
    [ "$PARFILE" ] && EXTRAPARFILE="PARFILE=$PARFILE"
    [ "$CURRENTSCN" ] && PARSCN="FLASHBACK_SCN=$CURRENTSCN"
    [ "$EXPORTVERSION" ] && EXPVERSION="VERSION=$EXPORTVERSION"
    if [ "$FROM_SERVER" == "$TO_SERVER" ]
    then
        [ -e "$CPYFROM" ] && rm -f "$CPYFROM"
    fi

    $ORACLE_HOME/bin/expdp "$FROM_USER"/"$FROM_PWD"@"$FROM_SID" SCHEMAS="$FROM_USER" DUMPFILE="$FROM_DUMPNAME" LOGFILE="$FROM_LOGNAME" "$EXPVERSION" "$PARSCN" "$EXTRAPARFILE"

    CMSOURCENAME="$FROM_DUMPDIR/$FROM_DUMPFILENAME.dmp"

    if [ "$ACTION" == E ]
    then
        if [ "$DOZIP" == Y ]
        then
            writelog "$LOCALECHO" "Zipping dumpfile..."
            gzip "$CMSOURCENAME"
            CMSOURCENAME="$FROM_DUMPDIR/$FROM_DUMPFILENAME.dmp.gz"
        fi
    fi
    
    if [ "$EXPCOPYMOVEDEST" != N ]
    then
        if [ "$FROM_SERVER" == "$TO_SERVER" ]
        then
            case "$EXPCOPYMOVEDEST" in
                M|m)    writelog $LOCALECHO "Moving dumpfile to target location"; mv "$CMSOURCENAME" "$EXPORTDEST";;
                C|c)    writelog $LOCALECHO "Copying dumpfile to target location"; cp "$CMSOURCENAME" "$EXPORTDEST";;
            esac
        fi
    fi
    
    if [ "$FTPDEFS" ]
    then
        FTPSERVER=`echo $FTPDEFS | sed 's/\(.*\):\(.*\):\(.*\):\(.*\)/\1/'`
        FTPUSER=`echo $FTPDEFS | sed 's/\(.*\):\(.*\):\(.*\):\(.*\)/\2/'`
        FTPPWD=`echo $FTPDEFS | sed 's/\(.*\):\(.*\):\(.*\):\(.*\)/\3/'`
        FTPLOCATION=`echo $FTPDEFS | sed 's/\(.*\):\(.*\):\(.*\):\(.*\)/\4/'`

        writelog $LOCALECHO "Copying dumpfile to ftp server $FTPSERVER, directory $FTPLOCATION"

        ftp -n -i $FTPSERVER <<-EOF
user $FTPUSER $FTPPWD
cd $FTPLOCATION
lcd $FROM_DUMPDIR
put $CMSOURCENAME
quit
EOF
    fi
    
    echo
    writelog "$LOCALECHO" "*** EXPORT DONE ***"
}
#--------------------------------------------------------------------------


#---[ Read dumpfile ]------------------------------------------------------
impdump()
{
    echo
    writelog "$LOCALECHO" "*** START IMPORT ***"
    writelog "$LOCALECHO" "Drop Oracle schema $TO_USER. This may take some time......"
    $ORACLE_HOME/bin/sqlplus -S $TO_SYSUSER/$TO_SYSPWD@$TO_SID AS SYSDBA 2> /dev/null <<-EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGES 0
DROP USER $TO_USER CASCADE;
EXIT
EOF

    echo
    writelog "$LOCALECHO" "Create and set up Oracle schema $TO_USER"
    $ORACLE_HOME/bin/sqlplus -S $TO_SYSUSER/$TO_SYSPWD@$TO_SID AS SYSDBA 2> /dev/null <<-EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGES 0
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
CREATE USER $TO_USER IDENTIFIED BY $TO_PWD
DEFAULT TABLESPACE ABS_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON ABS_DATA
QUOTA UNLIMITED ON INDX;
GRANT UNLIMITED TABLESPACE TO $TO_USER;
GRANT CONNECT TO $TO_USER;
GRANT RESOURCE TO $TO_USER;
GRANT ALTER ANY SEQUENCE TO $TO_USER;
GRANT CREATE ANY SEQUENCE TO $TO_USER;
GRANT CREATE PUBLIC SYNONYM TO $TO_USER;
GRANT DROP PUBLIC SYNONYM TO $TO_USER;
GRANT CREATE ANY TYPE TO $TO_USER;
GRANT CREATE ANY PROCEDURE TO $TO_USER;
GRANT CREATE ANY TRIGGER TO $TO_USER;
GRANT ALTER ANY TRIGGER TO $TO_USER;
GRANT CREATE ANY VIEW TO $TO_USER;
GRANT EXECUTE ANY PROCEDURE TO $TO_USER;
GRANT DEBUG ANY PROCEDURE TO $TO_USER;
GRANT DEBUG CONNECT SESSION TO $TO_USER;
GRANT EXECUTE ANY TYPE TO $TO_USER;
GRANT CREATE ANY DIRECTORY TO $TO_USER;
GRANT DROP ANY DIRECTORY TO $TO_USER;
GRANT SELECT ON DBA_DATA_FILES TO $TO_USER;
GRANT SELECT ON V_\$MYSTAT TO $TO_USER;
GRANT SELECT ON V_\$SESSION TO $TO_USER;
GRANT SELECT ON V_\$SESSTAT TO $TO_USER;
GRANT SELECT ON V_\$STATNAME TO $TO_USER;
GRANT EXECUTE ON SYS.DBMS_LOCK TO $TO_USER;
GRANT CREATE ANY TABLE TO $TO_USER;
GRANT EXP_FULL_DATABASE TO $TO_USER;
GRANT IMP_FULL_DATABASE TO $TO_USER;
CREATE OR REPLACE DIRECTORY $DUMPDIRNAME AS '$TO_DUMPDIR';
GRANT READ, WRITE ON DIRECTORY $DUMPDIRNAME TO $TO_USER;
EXIT
EOF

    echo
    writelog "$LOCALECHO" "Import dumpfile. This may take some time..."
    $ORACLE_HOME/bin/impdp "$TO_USER"/"$TO_PWD"@"$TO_SID" DUMPFILE="$TO_DUMPNAME" LOGFILE="$TO_LOGNAME" SCHEMAS="$FROM_USER" REMAP_SCHEMA="$FROM_USER":"$TO_USER" TRANSFORM=oid:n

    writelog "$LOCALECHO" "Try to recompile invalid objects..."
    for i in 1 2 3
    do
        writelog "$LOCALECHO" "-- pass $i"
        [ -e "$ABS_LOGDIR/recompl.lst" ] && rm -f $ABS_LOGDIR/recompl.lst
        $ORACLE_HOME/bin/sqlplus -S $TO_SYSUSER/$TO_SYSPWD@$TO_SID AS SYSDBA 2> /dev/null <<-EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF PAGES 0
spool $ABS_LOGDIR/recompl.lst
select   'alter '||decode(object_type, 'PACKAGE BODY', 'package', object_type)||' "'||object_name||'" compile'||decode(object_type, 'PACKAGE BODY', ' body;', ';')
from     user_objects
where    status = 'INVALID'
order by object_type;
spool off
EXIT
EOF
        echo EXIT >> "$ABS_LOGDIR"/recompl.lst
        "$ORACLE_HOME"/bin/sqlplus -S "$TO_USER"/"$TO_PWD"@"$TO_SID" @"$ABS_LOGDIR"/recompl.lst
    done
    rm -f "$ABS_LOGDIR"/recompl.lst

    $ORACLE_HOME/bin/sqlplus -S $TO_USER/$TO_PWD@$TO_SID 2> /dev/null <<-EOF
delete peakusers;
commit;
EXIT
EOF

    if [ "$DOAFTERIMPORT" == "Y" -o "$DOAFTERIMPORT" == "y" ]
    then
        echo
        writelog "$LOCALECHO" "Execute after import stuff"
        if [ "$IMPENVIRONMENT" != "" ]
        then
            $ORACLE_HOME/bin/sqlplus -S $TO_USER/$TO_PWD@$TO_SID 2> /dev/null <<-EOF
update databasesettings set environment='$IMPENVIRONMENT';
commit;
EXIT
EOF
        fi
        $ORACLE_HOME/bin/sqlplus -S $TO_USER/$TO_PWD@$TO_SID 2> /dev/null <<-EOF
update reportservercluster set active = 'N';
delete reportserver;
update PLANTPROCESS set active = 'N';
update systemsetting st
set ST.value = replace(ST.value, '$FROM_SID', '$TO_SID')
where st.value like '%$FROM_SID%';
update customerbillinginfo c set c.senddirectinvoicebyemail = 'N';
update supplier c set c.sendpurchaseorder = '1';
commit;
EXIT
EOF
    fi
    
    echo
    writelog "$LOCALECHO" "*** IMPORT DONE ***"
}

#--------------------------------------------------------------------------

case $ACTION in
    B)  expdump
        impdump;;
    E)  expdump;;
    I)  impdump;;
    *)  echo "Something went wrong......"
        echo
        show_usage
        exit 1
        ;;
esac

###########################################################################


