KSH Script to Run a One-off EXPDP Export

Oftentimes, I have to run an EXPDP job on a server with no in-house scripts to run EXPDP.  So instead of figuring out how to write my long EXPDP command line, such as this one:

expdp system@mydb directory=DATAPUMP dumpfile=161122MYDBPROD-%U.dmp logfile=161122MYDBPRODexport.log JOB_NAME=MYDBPROD schemas=myprodschema parallel=2 EXCLUDE=STATISTICS METRICS=Y

I just copy/paste a ksh script of mine on the database server, then run it with the appropriate 5 parameters:

Here are the contents of the Korn shell script:

#!/bin/ksh

##set -x

# July 10 2018, for a one-off expdp job. Frank Dernoncourt

# be connected as the oracle Unix user

# October 30, 2018 :  added flashback_time=to_timestamp\(localtimestamp\)

#### 1st  parameter : degree of  parallelism

#### 2nd parameter : schema or SHEBANG for a FULL export          #### to export several schemas, enter a comma-separated list

#### 3rd parameter : EXCLUSIONS or SHEBANG          #### SHEBANG if you don't want to exclude anything.  For exclusions, just enter what you would enter after EXCLUDE=.  For instance, "STATISTICS" for "EXCLUDE=STATISTICS"

#### 4th parameter : dumpfile without .dmp

#### 5th parameter : path for the dump and the log

LOGFILE=expdp--${2}.log

THEDATE=$(date +%Y%m%d_%H%M)

SECONDS=0

echo ""        | tee -a ${LOGFILE}

echo "================== This is the log of $(basename ${0}), which ran at ${THEDATE} =================="        | tee -a ${LOGFILE}

if [ -z ${ORACLE_SID} ]

then

echo "ORACLE_SID not defined"

exit 1

fi

if [ $# -ne 5 ]

then

echo "Wrong number of parameters."

echo "1 : DOP"

echo "2 : schema to export or SHEBANG for FULL"

echo "3 : exclusions or SHEBANG  Exemple: "TABLE:\"LIKE '%BIN$%'\"" "          #### In this example, tables from the RECYCLING BIN will be excluded

echo "4 : dumpfile name without .dmp"

echo "5 : path for the log and dump"

exit 1

fi

if [[ ${1} > 1 ]]

then

echo "You chose a degree of parallelism of ${1}, which will create ${1} dumpfiles."

DOP=${1}

else

DOP="1"

fi

export AE=$(echo ${2} | tr '[:lower:]' '[:upper:]')

if [[ ${AE} = "SHEBANG" ]]

then

echo "You chose to run a FULL export."

AEXPORTER="FULL=Y"

else

echo "You chose to export schema ${AE}."

AEXPORTER="SCHEMAS=${AE}"

fi

export EXCLU=$(echo ${3} | tr '[:lower:]' '[:upper:]')

if [[ ${EXCLU} = "SHEBANG" ]]

then

echo "You chose not to exclude anything from the export."

EXCLUSIONS=""

else

echo "You chose those exclusions: ${EXCLU}."

EXCLUSIONS="EXCLUDE=${EXCLU}"

fi

export FDUMP=${4}

if [[ ${DOP} > 1 ]]

then

DUMPFILE="${FDUMP}%U"

else

DUMPFILE="${FDUMP}"

fi

export CHEMIN=${5}

if [ -d ${CHEMIN} ]

then

DIRECTORY="${CHEMIN}"

else

echo "The chosen path does not exist."

exit 1

fi

sqlplus -s /nolog <<EOF

connect / as sysdba

whenever sqlerror exit

create or replace directory DATA_PUMP_UN as '${DIRECTORY}';          #### For this one-off job, I create a DIRECTORY that I drop when it's over

EOF

echo "The command line to be run is: expdp system flashback_time=to_timestamp(localtimestamp) directory=DATA_PUMP_UN METRICS=Y ${AEXPORTER} ${EXCLUSIONS} JOB_NAME=ex${AE} LOGFILE=exportde${AE}.log DUMPFILE=${DUMPFILE}.dmp PARALLEL=${DOP:-1} "                | tee -a ${LOGFILE}

echo "OK to continue Y/N ? "

read CONFIRMATION

if [ ${CONFIRMATION} = N ]

then exit

fi

expdp system flashback_time=to_timestamp\(localtimestamp\) directory=DATA_PUMP_UN METRICS=Y ${AEXPORTER} ${EXCLUSIONS} JOB_NAME=ex${AE} LOGFILE=exportde${AE}.log DUMPFILE=${DUMPFILE}.dmp PARALLEL=${DOP:-1}  2>>${LOGFILE}          #### Only errors are routed to the main log.  The full EXPDP log will still appear in LOGFILE in the DATA_PUMP_UN directory

RET=$?

echo "return code of the export: $RET" | tee -a ${LOGFILE}

if [ $RET -ne 0 ]

then

echo "==> UNEXPECTED RESULT" | tee -a ${LOGFILE}

else

echo "==> EXPECTED RESULT" | tee -a ${LOGFILE}

fi

##set +x

sqlplus -s /nolog <<EOF

connect / as sysdba

whenever sqlerror exit

drop directory DATA_PUMP_UN ;          #### Once the job is done, I drop the DIRECTORY.

EOF

echo "   duration ${SECONDS} seconds" | tee -a ${LOGFILE}

date "+%n%d/%m/%Y %H:%M:%S%n" | tee -a ${LOGFILE}

echo "View the log that script in ${LOGFILE}"

exit $RET

The first version of my shell script is in French and here is what it looks like when your run it:

If anything is unclear, let me know in the comments below.  Enjoy my script!