How to Automate a Datapump Export Process

#################################
# Create datapump directory
#################################

CREATE DIRECTORY datapump as '/u01/datapump/datapump_exports';
GRANT READ,WRITE ON DIRECTORY datapump TO SYSTEM;

###############################################
# Create shell script to pass the scp password
###############################################

#!/usr/bin/expect -f
# connect via scp
 spawn scp /u01/datapump/datapump_exports/exp_*.dmp* "oracle@testserver:/u01/datapump/"
 expect {
 -re ".*es.*o.*" {
 exp_send "yes\r"
 exp_continue
 }
 -re ".*sword.*" {
 exp_send "Password\r"
 }
 }
 interact

#############################################################
# Create shell script for the entire datapump export process
#############################################################

#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$PATH:/u01/datapump/datapump_exports
DIR=/u01/datapump/datapump_exports
LOGDIR=/u01/datapump/datapump_exports
EXPLOGDIR=/u01/datapump/datapump_exports
export PATH DIR LOGDIR EXPLOGDIR
. oraenv

ADMIN=`cat /u01/datapump/datapump_exports/bkup_mail_list.lis`
EM_LIST='henndue14@gmail.com'
today=`date '+%m/%d/%y %H:%M:%S'`
HOSTNAME=`hostname`
expdate=`date '+%d%m%Y'`

user=`cat /u01/datapump/datapump_exports/.pwdapp |grep ^$ORACLE_SID:|cut -f2 -d':'`
LOGIN=`cat /u01/datapump/datapump_exports/.pwdapp |grep ^$ORACLE_SID:|cut -f3 -d':'`
TEMP_FILE=$LOGDIR/expdp_log.txt

#################################
# Removing 7 days old dump files
#################################

echo "Start dmp file cleanup $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

find $EXPLOGDIR/exp_*.dmp* -mtime +7 -exec rm {} \;

echo "Finished dmp file cleanup $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Alter undo_retention parameter
#################################

echo "Start UNDO_RETUENTION Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

$ORACLE_HOME/bin/sqlplus -s <<!
/ as sysdba

whenever sqlerror exit 2;

set echo off
set feedback off
spool $LOGDIR/undo.err

alter system set undo_retention=19169 scope=both;
commit;

spool off
!
undo_exit_status=$?

if [ $undo_exit_status -eq 0 ]
then
  echo "undo_retention successfully updated"
else
  mailx -s "Update undo_retention on Database $ORACLE_SID Failed!!! "henndue14@gmail.com < $LOGDIR/undo.err
  exit 1  
fi

echo "Finish UNDO_RETUENTION Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Export Schema
#################################

echo "Start datapump Export $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

expdp $LOGIN PARALLEL=6 dumpfile=exp_$(date +%Y%m%d)_%U directory=datapump logfile=exp_$(date +%Y%m%d).log schemas=hennco EXCLUDE=TABLE:\"IN \(\'TEST_TBL\'\)\" flashback_time=systimestamp

exp_exit_status=$?

if [ $exp_exit_status -eq 0 ]
then
  echo "Export finished Successfully"
else
  cat $EXPLOGDIR/exp_$(date +%Y%m%d).log | egrep -i "ORA-|Failing|error" > $LOGDIR/expdp.err
  mailx -s "Export on Database $ORACLE_SID Failed!!! " henndue14@gmail.com < $LOGDIR/expdp.err
  exit 1  
fi

echo "Finish datapump Export $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Alter undo_retention parameter
#################################

echo "Start UNDO_RETUENTION Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

$ORACLE_HOME/bin/sqlplus -s <<!
/ as sysdba

whenever sqlerror exit 2;

set echo off
set feedback off
spool $LOGDIR/undo.err

alter system set undo_retention=1800 scope=both;
commit;

spool off
!
undo_exit_status=$?

if [ $undo_exit_status -eq 0 ]
then
  echo "undo_retention successfully updated"
else
  mailx -s "Update undo_retention on Database $ORACLE_SID Failed!!! " henndue14@gmail.com < $LOGDIR/undo.err
  exit 1  
fi

echo "Finish UNDO_RETUENTION Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Grant permissions for dmp files
#################################

echo "Start dmp file permission Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

chmod -R 775 $EXPLOGDIR/exp_*.dmp

echo "Finish dmp file permission Update $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Compressing files
#################################

echo "Start compressing dmp files $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

gzip $EXPLOGDIR/exp_*.dmp

echo "Finish compressing dmp files $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# SCP dmp files to lihbg000tpcdu
#################################

echo "Start dmp file scp $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

/u01/datapump/datapump_exports/scp_dump.sh

echo "Finish dmp file scp $(date)" >> $TEMP_FILE
echo "\n" >> $TEMP_FILE

#################################
# Email export process output
#################################

mailx -s "Database Export process has successfully completed" henndue14@gmail.com < $TEMP_FILE

#################################
# Log file cleanup
#################################

rm /u01/datapump/datapump_exports/expdp_log.txt
rm /u01/datapump/datapump_exports/undo.err
rm /u01/datapump/datapump_exports/expdp.err
exit;