Shell Script for expdp Automation

 Create a file named daily_full_expdp.sh :

######################################################################

#!/bin/bash

# --- Configuration Variables ---

ORACLE_SID="ORCLDB"         # Your Oracle SID
ORACLE_USER="system"        # Oracle user with EXP_FULL_DATABASE role
ORACLE_PASS="password"      # Password for the Oracle user (consider a wallet or secure storage for production)
DP_DIR_OBJ="DATA_PUMP_DIR"  # Name of your Oracle DIRECTORY object
OS_DP_DIR="/u01/app/oracle/datapump_backups" # Corresponding OS directory
LOG_BASE_DIR="/var/log/oracle/datapump" # Directory for script logs
RETENTION_DAYS=7            # How many days to keep dump files and logs

# --- Derived Variables ---

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="full_db_${ORACLE_SID}_${TIMESTAMP}.dmp"
LOG_FILE="full_db_${ORACLE_SID}_${TIMESTAMP}.log"
SCRIPT_LOG="${LOG_BASE_DIR}/expdp_script_${ORACLE_SID}_${TIMESTAMP}.log"

# --- Environment Setup (Crucial for Non-Interactive Shells) ---

export ORACLE_SID
export ORACLE_HOME=$(grep "^${ORACLE_SID}:" /etc/oratab | cut -d: -f2)
export PATH=$ORACLE_HOME/bin:$PATH
# --- Create OS directories if they don't exist ---
mkdir -p "${OS_DP_DIR}"
mkdir -p "${LOG_BASE_DIR}"

# --- Start Logging ---

echo "--- Starting Oracle Data Pump Full Export ---" | tee -a "${SCRIPT_LOG}"
echo "Start Time: $(date)" | tee -a "${SCRIPT_LOG}"
echo "ORACLE_SID: ${ORACLE_SID}" | tee -a "${SCRIPT_LOG}"
echo "DUMP_FILE: ${OS_DP_DIR}/${DUMP_FILE}" | tee -a "${SCRIPT_LOG}"
echo "LOG_FILE: ${OS_DP_DIR}/${LOG_FILE}" | tee -a "${SCRIPT_LOG}"
echo "-------------------------------------------" | tee -a "${SCRIPT_LOG}"

# --- Run the expdp command ---

expdp ${ORACLE_USER}/${ORACLE_PASS} \
    FULL=Y \
    DIRECTORY=${DP_DIR_OBJ} \
    DUMPFILE=${DUMP_FILE} \
    LOGFILE=${LOG_FILE} \
    PARALLEL=4 \
    COMPRESSION=ALL \
    NOLOGFILE=N 2>&1 | tee -a "${SCRIPT_LOG}"

# --- Check Export Status ---

if [ $? -eq 0 ]; then
    echo "--- Data Pump Export Completed Successfully ---" | tee -a "${SCRIPT_LOG}"
else
    echo "--- Data Pump Export Failed! Check logs for details. ---" | tee -a "${SCRIPT_LOG}"
    # You might add email notification here:
    # mail -s "Oracle expdp failed on ${ORACLE_SID}" your_email@example.com < "${SCRIPT_LOG}"
fi

# --- Clean up old dump files and logs ---

echo "--- Cleaning up old files (older than ${RETENTION_DAYS} days) ---" | tee -a "${SCRIPT_LOG}"
find "${OS_DP_DIR}" -name "full_db_${ORACLE_SID}_*.dmp" -type f -mtime +${RETENTION_DAYS} -exec rm -f {} \; 2>&1 | tee -a "${SCRIPT_LOG}"
find "${OS_DP_DIR}" -name "full_db_${ORACLE_SID}_*.log" -type f -mtime +${RETENTION_DAYS} -exec rm -f {} \; 2>&1 | tee -a "${SCRIPT_LOG}"
find "${LOG_BASE_DIR}" -name "expdp_script_${ORACLE_SID}_*.log" -type f -mtime +${RETENTION_DAYS} -exec rm -f {} \; 2>&1 | tee -a "${SCRIPT_LOG}"
echo "--- Cleanup Complete ---" | tee -a "${SCRIPT_LOG}"
echo "End Time: $(date)" | tee -a "${SCRIPT_LOG}"
echo "--- Script Finished ---" | tee -a "${SCRIPT_LOG}"

#########################################################################