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:
expdp5.ksh 12 MYBIGSCHEMA "TABLE:\"LIKE '%$%'\"" MYDB_MYBIGSCHEMA /data/share/exports/exadata/DATAPUMP | |
expdp5.ksh 32 SHEBANG SHEBANG MYDB_THEWHOLESHEBANG /data/share/exports/exadata/DATAPUMP |
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!