Simon Krenger

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.

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.