Oracle Full Datapump Export with crontab

So I get many visits for my post on Oracle Full Datapump Export Batch, so here is the same export script for UNIX systems.

Before using this script, please read my advice:

A full export is never a substitute for a RMAN backup. Only use this script to perform other tasks not related to backups, for example to provide regular snapshots of development or test databases.

– Simon Krenger

Using the following script with cron, you can regularly schedule a full export of an Oracle database:

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/10.2.0/db_1
export ORACLE_SID=$1
export EXPORT_FOLDER=/u01/app/oracle/admin/${ORACLE_SID}/datapump

ORACLE_USER=MYDBA
ORACLE_PASSWORD=supersecret
DATE=$(date +"%Y%m%d")

$ORACLE_HOME/bin/expdp $ORACLE_USER/$ORACLE_PASSWORD full=y directory=DATAPUMP_DIR \
dumpfile=$DATE-${ORACLE_SID}_full_export.dmp logfile=$DATE-${ORACLE_SID}_full_export.log \
flashback_time=SYSTIMESTAMP

tar cjf $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.tar.bz2 \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp \
$EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log

rm $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.dmp $EXPORT_FOLDER/$DATE-${ORACLE_SID}_full_export.log

find $EXPORT_FOLDER/*${ORACLE_SID}_full_export.tar.bz2 -mtime +15 -delete

Please note that the directory DATAPUMP_DIR has to exists (check DBA_DIRECTORIES or create a directory using CREATE DIRECTORY). Then, add the following line to your crontab:

0 3 * * * /u01/app/oracle/admin/kdb01/datapump/fullexp.sh kdb01 2>&1

This example will make a full export of the database with the SID “kdb01” every day at 03:00 in the morning.