How to Automate Oracle Data Pump (expdp) Backups with a Shell Script

In the world of Oracle database administration, ensuring regular backups is crucial for data protection and disaster recovery. Oracle Data Pump (expdp) is a powerful utility that allows you to export data and metadata from Oracle databases.

Shell Script Overview: The shell script presented here is designed to facilitate the expdp backup process for Oracle databases. It includes interactive features for selecting the database, specifying the service name, and choosing between a full database backup or a schema backup. Let’s examine the different sections of the script.

Setting ORATAB: The script begins by setting the ORATAB environment variable, which points to the location of the oratab file. The oratab file contains a list of Oracle database instances and their corresponding environment variables. The script checks for the existence of the oratab file in two common locations: /etc/oratab and /var/opt/oracle/oratab.
Environment Variable Setup: The script sets up the necessary environment variables required for the expdp backup job. These variables include ORACLE_SID, ORACLE_HOME, PATH, LD_LIBRARY_PATH, LD, ML (email recipient for job notifications), CDATE (current date and time), DIR (Data Pump directory), SERVICE_NAME, TY (backup type), and EXL (excluded objects during backup).
Data Pump Directory Creation: Before starting the backup, the script checks if the Data Pump directory exists. If not, it creates the directory using the provided SERVICE_NAME.
Data Pump Directory Validation: The script validates the existence of a specific directory (‘EBKP_DUMP’) within the Oracle database. If the directory is missing, it creates it using the previously created DP_DIRECTORY. The ‘EBKP_DUMP’ directory is used for storing the expdp dump files.
Obtaining the Schema Name: Next, the script uses a SQL query to determine the schema name. It connects to the database specified by the SERVICE_NAME and retrieves the schema name by querying the DBA_USERS view. The obtained schema name is stored in the SCH variable.
Backup Validation and Notification: After the backup completes, the script checks the expdp log file for any ORA- errors. If errors are found, an email notification is sent to the designated recipient using the ‘mailx’ command. On the other hand, if no errors are detected, a success message is displayed.

#!/usr/bin/ksh
# October 2022 Sandeep Reddy Narani
# Interactive database picker
# Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables
# Usage: /home/oracle/get_expdp.sh <ORACLE_SID> <SERVICE_NAME> <full backup=Y | schemabackup=N>

# SETTING ORATAB:
if [ -f /etc/oratab ]; then
  ORATAB=/etc/oratab
  export ORATAB
  ## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]; then
  ORATAB=/var/opt/oracle/oratab
  export ORATAB
fi

export ORACLE_SID=$1
export ORACLE_HOME=$(grep "^${ORACLE_SID}:" ${ORATAB} | cut -d: -f2 -s)
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD=/home/oracle/scripts
export ML=""
export CDATE=$(date '+%Y%m%d_%H%M')
export DIR=DATAPUMP
export SERVICE_NAME=$2
export TY=$3
export EXL=statistics

# Create Data Pump directory if it does not exist
DP_DIRECTORY=/dba/deeds//${SERVICE_NAME}
# Check if the directory exists, create it if necessary
if [ ! -d "${DP_DIRECTORY}" ]; then
  echo "Creating Data Pump directory: ${DP_DIRECTORY}"
  mkdir -p "${DP_DIRECTORY}"
fi

if ! $ORACLE_HOME/bin/sqlplus -S userid/password@${SERVICE_NAME} <<EOF | grep -q 'DIRECTORY_NAME'
set heading off
set feedback off
select DIRECTORY_NAME from dba_directories where DIRECTORY_NAME = 'EBKP_DUMP';
exit;
EOF
then
  echo "Creating Data Pump directory: ${DP_DIRECTORY}"
  $ORACLE_HOME/bin/sqlplus -S userid/password@${SERVICE_NAME} <<EOF
create or replace directory EBKP_DUMP as '${DP_DIRECTORY}';
GRANT READ, WRITE ON DIRECTORY EBKP_DUMP TO system;
exit;
EOF
fi

# Get the schema name using a SQL query
export SCH=$(sqlplus -S userid/password@${SERVICE_NAME} <<EOF
set heading off
set feedback off
select username from dba_users where username like '%DBA%';
exit;
EOF
)

echo "Export ${SCH}"

LOG_FILE=expdp_daily_${SERVICE_NAME}_${CDATE}.log

if [ "${TY}" == "Y" ]; then
  echo "Taking EXPDP Database full=y backup"
  $ORACLE_HOME/bin/expdp userid/password@${SERVICE_NAME} directory=EBKP_DUMP dumpfile=expdp_daily_${SERVICE_NAME}_${CDATE}_%U.dmp logfile=${LOG_FILE} full=y PARALLEL=16 exclude=${EXL} COMPRESSION=ALL metrics=y
else
  echo "Taking EXPDP Database Schema ${SCH} backup"
  $ORACLE_HOME/bin/expdp userid/password@${SERVICE_NAME} directory=EBKP_DUMP dumpfile=expdp_expdp_daily_${SERVICE_NAME}_${CDATE}_%U.dmp logfile=${LOG_FILE} schemas=${SCH} PARALLEL=16 exclude=${EXL} COMPRESSION=ALL metrics=y
fi

# Check if the expdp job failed or encounteredan error
if grep -q 'ORA-' ${LOG_FILE}; then
  echo "EXPDP job failed or encountered an error. Sending email notification."
  mailx -s "EXPDP Job Failed or Errored" ${ML} < ${LOG_FILE}
else
  echo "EXPDP job completed successfully."
fi

DBADEEDS