Oracle Data pump Full Export script with PDB, Retention and Email Notification

Oracle Data Pump is one of the utilities I frequently use for database migrations, schema refreshes, and logical backups. Running an export manually is straightforward, but when exports need to run daily, there are a few additional things to consider.

I wanted a simple solution that would automatically create export directories, run Data Pump, remove old files based on a retention policy, and notify me if something went wrong.

For this purpose, I use two Bash scripts. The first handles directory creation, retention, and email notifications, while the second generates the Data Pump parameter file based on my requirements and runs the export. Of course, there are plenty of things that could be added or improved, but this one works well enough for my needs.

The scripts provide the following functionality:

• Create a directory for each export.
• Automatically update the Oracle directory object.
• Perform a full Data Pump export with parallel workers.
• Delete old export files based on a configurable retention period.
• Check the export status and log file for errors.
• Send email notifications with the export results.

In this example, I’m using Oracle Database 19c with a pluggable database (PDB). Although Mike Dietrich advises not to use the ORACLE_PDB_SID environment variable (ORACLE_PDB_SID and AutoUpgrade) , I’m using it here to connect directly to the PDB from which I want to perform the export.

1. Export Directory Structure

Each export is stored in a separate timestamped directory under /rman/exports.

/rman/exports/
└── BLTDB/
├── 20261022_020000/
│ ├── BLTDB_01.dmp
│ ├── BLTDB_02.dmp
│ └── BLTDB.log
├── 20261023_020000/
│ ├── BLTDB_01.dmp
│ └── BLTDB.log
└── 20261024_020000/
├── BLTDB_01.dmp
├── BLTDB_02.dmp
└── BLTDB.log

Using timestamps instead of dates allows multiple exports on the same day without overwriting existing files.

2. Main Export Script

The main script, run_export.sh, prepares the export destination, creates the Oracle directory object, runs Data Pump, checks the result and handles retention and email notifications.

#!/bin/bash
BASE_DIRECTORY="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source ~/.bash_profile
export ORACLE_SID=BLTDB1
export ORACLE_PDB_SID=BLTPDB
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export PATH="$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH"
unset PERL5LIB
DB_NAME="${1:?Usage: $0 EXPORT_NAME}"
DB_NAME="${DB_NAME^^}"
if [[ ! "$DB_NAME" =~ ^[A-Z][A-Z0-9_]*$ ]]; then
    echo "Invalid export name: $DB_NAME" >&2
    exit 1
fi
EXPORT_DIR="/rman/exports/$DB_NAME"
TODAY=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="$EXPORT_DIR/$TODAY"
MAIL_TO="dba@bulutdb.com"
RETENTION=30
# Create export directory
mkdir -p "$BACKUP_DIR" || exit 1
# Create Oracle directory in PDB
SQL_OUTPUT=$(sqlplus -s -L "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT FAILURE
SET HEADING OFF FEEDBACK OFF
ALTER SESSION SET CONTAINER=BLTPDB;
CREATE OR REPLACE DIRECTORY EXPORT_BACKUPS AS '$BACKUP_DIR';
EXIT;
EOF
)
SQL_STATUS=$?
if [ "$SQL_STATUS" -ne 0 ]; then
    echo "Database connection or directory creation failed: $SQL_OUTPUT" |
        mail -s "$DB_NAME: EXPORT FAILED" "$MAIL_TO"
    exit 1
fi
# Run export
bash "$BASE_DIRECTORY/export_full.sh" "$DB_NAME"
EXPORT_STATUS=$?
LOG_FILE="$BACKUP_DIR/$DB_NAME.log"
# Collect Oracle errors for failure notifications
if [ -f "$LOG_FILE" ]; then
    ERRORS=$(grep 'ORA-' "$LOG_FILE" || true)
else
    ERRORS="Export log file not found."
fi
# Check Data Pump exit status and completion message
if [ "$EXPORT_STATUS" -eq 0 ] &&
   [ -f "$LOG_FILE" ] &&
   grep -Eq 'Job .* successfully completed' "$LOG_FILE"; then
    # Delete old exports after successful export
    CLEANUP_OUTPUT=$(find "$EXPORT_DIR" \
        -mindepth 2 -maxdepth 2 -type f \
        \( -name "${DB_NAME}*.dmp" -o -name "${DB_NAME}*.log" \) \
        -mmin +"$((RETENTION * 1440))" -print -delete 2>&1)
    CLEANUP_STATUS=$?
    # Remove empty directories
    if [ "$CLEANUP_STATUS" -eq 0 ]; then
        EMPTY_DIR_OUTPUT=$(find "$EXPORT_DIR" \
            -mindepth 1 -maxdepth 1 \
            -type d -empty -delete 2>&1)
        EMPTY_DIR_STATUS=$?
    else
        EMPTY_DIR_OUTPUT="Skipped because file cleanup failed."
        EMPTY_DIR_STATUS=1
    fi
    if [ "$CLEANUP_STATUS" -eq 0 ] &&
       [ "$EMPTY_DIR_STATUS" -eq 0 ]; then
        SUBJECT="$DB_NAME: EXPORT SUCCESSFUL"
        FINAL_STATUS=0
        BODY="Export completed successfully.
Log: $LOG_FILE
Last 5 lines:
$(tail -n 5 "$LOG_FILE")
Directory size: $(du -sh "$BACKUP_DIR" | awk '{print $1}')
Retention: $RETENTION days
Cleanup completed successfully."
    else
        SUBJECT="$DB_NAME: EXPORT SUCCESSFUL - CLEANUP FAILED"
        FINAL_STATUS=1
        BODY="Export completed successfully, but retention cleanup failed.
Log: $LOG_FILE
Cleanup output:
$CLEANUP_OUTPUT
Empty directory cleanup:
$EMPTY_DIR_OUTPUT"
    fi
else
    SUBJECT="$DB_NAME: EXPORT FAILED"
    FINAL_STATUS=1
    BODY="Export failed or completed with errors.
Log: $LOG_FILE
Exit status: $EXPORT_STATUS
Errors:
${ERRORS:-No ORA- errors found. Check the export log.}
Retention cleanup was skipped."
fi
printf '%s\n' "$BODY" | mail -s "$SUBJECT" "$MAIL_TO"
echo "$SUBJECT"
exit "$FINAL_STATUS"

The script takes the db_name as an argument and creates a directory using the current timestamp.

For example:

./run_export.sh BLTDB

This creates a directory similar to :

/rman/exports/BLTDB/20261024_020000

It also creates or replaces the EXPORT_BACKUPS Oracle directory object to point to the new location. The SQL*Plus exit status is checked so the script can stop and send an email if the database connection or directory creation fails.

3. Data Pump Export Script

#!/bin/bash
DB_NAME="${1:?Usage: $0 EXPORT_NAME}"
file_name="${DB_NAME}_%U.dmp"
log_name="${DB_NAME}.log"
BASE_DIRECTORY="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
parameter_file="$BASE_DIRECTORY/${DB_NAME}_full_parameter_file.prm"
# Generate Data Pump parameter file
cat > "$parameter_file" <<EOL
FULL=Y
PARALLEL=4
JOB_NAME=FULL_EXPDP_$(date +%Y%m%d%H%M%S)
CONTENT=ALL
CLUSTER=N
EXCLUDE=audit_trails
DUMPFILE=$file_name
LOGFILE=$log_name
DIRECTORY=EXPORT_BACKUPS
METRICS=YES
ESTIMATE=STATISTICS
EOL
echo "$(date '+%Y-%m-%d %T'): Data Pump export started."
expdp 'userid="/ as sysdba"' parfile="$parameter_file"
EXPORT_STATUS=$?
echo "$(date '+%Y-%m-%d %T'): Data Pump export finished with exit status $EXPORT_STATUS."
exit "$EXPORT_STATUS"

The second script, export_full.sh, which is called by the main script (run_export.sh), generates the parameter file and executes the Data Pump export.

I prefer using a parameter file rather than passing all options directly to expdp. It is easier to read and modify when needed.

EXCLUDE AUDIT TRAILS

The export uses three parallel workers (PARALLEL=4) and uses %U to allow multiple dump files. Audit trails are excluded from the Data Pump export to reduce dump file size and export time, as audit records can grow significantly over time and are generally not needed when restoring application data. Audit retention and backup should be managed separately according to compliance requirements. You may refer to Oracle Data Pump Export – Excluding Audit Trails for more information.

Since the connection is made to a PDB, FULL=Y exports the PDB’s contents rather than the entire CDB. The script also returns the Data Pump exit status to the main script, which is important for detecting failures.

You may also wonder why I use CLUSTER=NO. In an Oracle RAC environment, this parameter ensures that Data Pump runs only on the instance where the export job is started, avoiding the overhead of distributing workers across multiple instances. While CLUSTER=YES can improve performance for large exports, I prefer to keep the job on a single instance if database size is small sized. This can also help avoid issues with shared storage and NFS configurations. I previously encountered a related issue, which I explained in my blog post: ORA-48128: Symbolic Link Error During Data Pump Export.

4. Retention Management

Data Pump exports can consume a significant amount of disk space, particularly when it is scheduled daily. In this example script, The retention period is controlled by a single variable:

RETENTION=30

After a successful export, the following command removes dump and log files older than 30 days:

find "$EXPORT_DIR" \
    -mindepth 2 -maxdepth 2 -type f \
    \( -name "${DB_NAME}*.dmp" -o -name "${DB_NAME}*.log" \) \
    -mmin +"$((RETENTION * 1440))" -print -delete

The important options are:

After deleting the expired files, empty directories are removed as well.

I deliberately placed the retention cleanup after a successful export. If the latest export fails, the script skips cleanup rather than removing older exports that might still be needed.

Note that retention is based on individual file modification times, not on complete export sets.

5. Export Status and Email Notifications

I also wanted to avoid checking Data Pump logs manually every day.

The script considers an export successful only when:

• Data Pump returns exit status 0.
• The log file exists.
• The log contains successfully completed. (Sometimes completed with warnings can be acceptable, it can be modified according to your needs.)

The relevant condition is:

if [ "$EXPORT_STATUS" -eq 0 ] &&
   [ -f "$LOG_FILE" ] &&
   [ -z "$ERRORS" ] &&
   grep -q 'successfully completed' "$LOG_FILE"; then

This is intentionally a strict check. Even if Data Pump completes with nonfatal Oracle errors, I prefer to receive a failure notification and review the log. You may customize it.

Depending on the result, the script sends one of three email subjects:

BLTDB: EXPORT SUCCESSFUL
BLTDB: EXPORT FAILED
BLTDB: EXPORT SUCCESSFUL - CLEANUP FAILED

A successful notification includes the log location, last five log lines, directory size, and retention result.

A failure notification includes the Data Pump exit status and any Oracle errors found in the log.

The Linux mail utility must be installed and configured for email delivery.

6. Scheduling with Cron

Make both scripts executable:

chmod +x run_export.sh export_full.sh

To test the export manually:

./run_export.sh BLTDB

Once everything works, schedule it through cron. For example, to run the export every day at 02:00:

0 2 * * * /home/oracle/scripts/run_export.sh BLTDB >> /home/oracle/scripts/bltdb_export.log 2>&1

The export will run automatically, old files will be removed according to the retention policy, and the result will be sent by email.

This script uses a shared parameter file () and Oracle directory object, so simultaneous executions should be avoided.

7. Conclusion

This is a simple approach I use to automate Oracle Data Pump exports without introducing unnecessary complexity.

With two Bash scripts and a cron job, the entire process, from directory creation to retention cleanup and email notification, can run unattended.

You should also apply Oracle Data Pump Bundle Patches regularly as part of your database patching cycles. I’ve explained why this is important in my blog post, “Oracle Database Patching Tips – Why Should You Apply Data Pump Bundle Patches Without Hesitation?”

Of course, Data Pump exports are logical backups and should not replace RMAN or a proper database recovery strategy. However, they remain useful for migrations, schema refreshes, and object-level recovery scenarios.

Hope it helps.