Expdp shell script example Oracle DB

When migrating or creating a backup of oracle database schema we usually use the expdp tool to export oracle database schema. We had  a requirement to take regular backups or export oracle database schema. To do same we created expdp shell script example which takes few arguments like export directory, DB username, DB password , Oracle SID and schema name to export and store the file at a specific location.

This expdp shell script example script is helpful if you want to generate backup dumps in regular intervals through some scheduled jobs like Cron or manually. This export oracle database schema script also gzip’s the final exported dump file to save disk space. Below is the exp.sh script for your reference and sample outputs for the same.

#!/bin/sh
#-x
#Usage:
#scriptfile dump_location DB_Username password sid schema_name
export EXPDIR=$1
export DBUSERNAME=$2
export DBPASSWORD=$3
export ORACLE_SID=$4
export SCHEMA_NAME=$5
export TFILE=`echo /tmp/nohup.$$.tmp`
export STARTTIME=`date`
export DATEFORMAT=`date +%Y%m%d_%Hh%Mm%Ss`
export ORACLE_HOME=`cat /etc/oratab|grep ^${ORACLE_SID}:|cut -d':' -f2`
export EXPLOG=expdp_`echo $ORACLE_SID`_`echo $DATEFORMAT`.log
export PATH=$PATH:$ORACLE_HOME/bin

if [[ $# -lt 5 ]] ; then
 echo "Wrong number of arguments..."
 echo "Usage:"
 echo
 echo "./scriptfile.sh dump_location DB_Username DB_Password DB_SID Schema_Name";
 echo
 exit 0
fi
if [[ ! -d "${EXPDIR}" ]]; then
mkdir -p ${EXPDIR}
echo -e "`date` :${EXPDIR} Directory Created.";
else
echo -e "`date` :${EXPDIR} directory found on system.";
fi;
if [ "$?" != 0 ]; then
echo "`date` :Command Failed To check ${EXPDIR} Properly";
exit 1;
fi

sqlplus -s ""${DBUSERNAME}"/"${DBPASSWORD}"@"${ORACLE_SID}"" <<EOF
CREATE OR REPLACE DIRECTORY exp_dir AS '$EXPDIR';
grant read,write on DIRECTORY exp_dir to $SCHEMA_NAME;
quit;
EOF

# Data Pump export

nohup expdp $DBUSERNAME/$DBPASSWORD@$ORACLE_SID schemas=$SCHEMA_NAME DIRECTORY=exp_dir DUMPFILE=expdp_`echo $ORACLE_SID`_`echo $DATEFORMAT`.dmp LOGFILE=$EXPLOG > ${TFILE} 2>&1 &

sleep 4s;
#if [[ -f "${EXPDIR}/${LOGFILE}" && -f "${EXPDIR}/${DUMPFILE}" ]]; then
if [[ -f ${TFILE} ]]; then
echo "`date` : $SCHEMA_NAME export started..";
export count="0";
while [ $count -lt 120 ]; do
status=`cat ${TFILE} | grep -E 'Job.*SYSTEM.*SYS_EXPORT_SCHEMA.*successfully' | wc -l`
if [ "$status" == "1" ]; then
echo "`date` :$SCHEMA_NAME schema export completed successfully.";
rm -f ${TFILE};
#exit 0;
break
else
echo "`date` : Export Still in progress...";
a=`expr $a + 1`
fi
sleep 1m;
done
fi
if [[ -f '${EXPDIR}/${DUMPFILE}' ]]; then
gzip "${EXPDIR}/${DUMPFILE}"
echo "`date` :${DUMPFILE} gzipped successfully.";
else
echo "`date` :${DUMPFILE} file not found, some issue in exporting..";
exit 1;
fi

The above script takes five arguments for exporting:

1. Export directory location

2. Database User Name

3. Database Password

4. Database SID

5. Database schema name to export

 

Sample outputs of the script run:

Usage:

Run Output:

Note: Here I have used system account for export and import and tested the script with same. If you are doing with some other non administrative account then you might need to update the below line in script to the expected output of export completion message.For example you can use below line update to fix the issue but make sure you have grants on the exp folder too before exporting else safe is to export using some administrative account only.

status=`cat ${TFILE} | grep -E 'Job.*SYSTEM.*SYS_EXPORT_SCHEMA.*successfully' | wc -l`

Update To

status=`cat ${TFILE} | grep -E 'Job.*SYS_EXPORT_SCHEMA.*successfully' | wc -l`

Please like, share and update your opinion about the post.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.