Data Pump Export (expdp) job setup in cron

To set up a full database export using Oracle's Data Pump Export (expdp) utility, you need to:

1. Create a script to perform the export.

2. Schedule the script to run automatically using cron.

Here's a step-by-step guide for setting up a full database export and scheduling it with cron.

Step 1: Create a Directory for Data Pump

Before running the expdp command, you need to ensure that the Oracle database has a directory object for storing the backup.

a. Create a Directory in Oracle

Run the following SQL commands to create a directory for the export files and grant permissions to the user who will run the export.

sql

Copy code

SQL> CREATE DIRECTORY datapump_dir AS '/path/to/export/dir';

SQL> GRANT READ, WRITE ON DIRECTORY datapump_dir TO system;

Replace '/path/to/export/dir' with the actual directory path on your system where you want the dump files to be saved.

Step 2: Create the Export Script

Create a shell script that will perform the full database export using the expdp command.

b. Sample Export Script

Create a script (e.g., full_db_export.sh) using your preferred text editor (like vi, nano, etc.).

bash

Copy code

#!/bin/bash

# Define variables

ORACLE_SID=your_sid

ORACLE_HOME=/path/to/oracle/home

EXP_DIR=/path/to/export/dir

DATE=$(date +%F)

LOG_FILE=${EXP_DIR}/expdp_full_${DATE}.log

DUMP_FILE=${EXP_DIR}/full_db_${DATE}.dmp

USER=system

PASS=your_password

DIRECTORY=datapump_dir

# Export environment variables

export ORACLE_SID

export ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

# Run the Data Pump export

expdp ${USER}/${PASS} FULL=Y DIRECTORY=${DIRECTORY} DUMPFILE=full_db_${DATE}.dmp LOGFILE=expdp_full_${DATE}.log

# Clean up older dump files (optional)

# Find and remove dump files older than 7 days

find ${EXP_DIR} -name "*.dmp" -type f -mtime +7 -exec rm {} \;

find ${EXP_DIR} -name "*.log" -type f -mtime +7 -exec rm {} \;

Explanation:

• ORACLE_SID: The Oracle System ID of your database.

• ORACLE_HOME: The path to your Oracle installation.

• EXP_DIR: The directory where the dump files will be saved.

• USER/PASS: The Oracle user and password used for the export (typically system or another DBA user).

• DIRECTORY: The Oracle directory object you created earlier.

• FULL=Y: Specifies that a full database export should be performed.

• DUMPFILE: The name of the export dump file.

• LOGFILE: The log file to capture the export progress.

• find and remove: Optional commands to clean up older dump and log files (older than 7 days in this example).

c. Make the Script Executable

Give the script execute permissions:

bash

Copy code

$ chmod +x /path/to/script/full_db_export.sh

Step 3: Set Up a Cron Job to Automate the Backup

Now that you have the export script ready, you can set up a cron job to run it at a scheduled time.

d. Edit the Crontab

To schedule the script to run, say every day at 2:00 AM, follow these steps:

Open the crontab editor for the user:
bash
Copy code
$ crontab -e

Add the following cron job:
bash
Copy code
0 2 * * * /path/to/script/full_db_export.sh >> /path/to/script/cron_log.txt 2>&1

2. 
   

   • 0 2 * * *: This means the job will run every day at 2:00 AM.

   • /path/to/script/full_db_export.sh: The full path to the script you created.

   • >> /path/to/script/cron_log.txt 2>&1: This redirects the output (both stdout and stderr) to a log file called cron_log.txt.

e. Save and Exit

Save the crontab file and exit. The script will now run automatically at the specified time.

Step 4: Verify the Setup

After setting up the cron job, verify that it’s working properly by checking the following:

• Cron log: Check the log file specified in the cron job (cron_log.txt) to confirm the export is running as expected.

• Export log: Check the Oracle Data Pump log file (expdp_full_<date>.log) for details of the export operation.

Optional: Monitor the Script with Email Alerts

If you want to receive email alerts in case of failures, you can configure the system to send emails from cron jobs.

Set the environment variable for email notifications by adding the following to the top of your script:
bash
Copy code
MAILTO=your_email@example.com

1. The system will send an email if there are any errors or output generated from the cron job.

Summary

This setup ensures that your Oracle database is regularly backed up using expdp, and the script is automated using cron. You can customize the script to suit your retention policy, adjust the frequency in cron, and monitor the job through logs or email alerts.