AskDba.org Weblog

Few days back I got a request from development team to generate hourly export dumps of few schemas.

Following were the requirements:

1. Dumpfile name should contain current date and timestamp information.
2. The generated dumpfile should be moved to a specific location.
3. All users should have read privileges on the export dumpfile.
4. The export dump should be taken on hourly basis.

To accomplish this task I generated a shell script and scheduled it in crontab:

#!/bin/ksh
#Script to Perform Datapump Export Every Hour
################################################################
#Change History
#================
#DATE         AUTHOR                       cHANGE
#---------   -----------------------  -------------------
#23-jUN-2009 SAURABH SOOD        New Script Created
#
#
#
################################################################
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
expdp username/password@orcl dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=DATA_PUMP_DIR logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log schemas=SCHEMA_A,SCHEMA_B
mv /tmp/expdp*.dmp /u01/backup/daily_export_orcl/
mv /tmp/expdp*.log /u01/backup/daily_export_orcl/
chmod o+r /s01/backup/daily_export_orcl/*

This script will do the following:

1. Set the ORACLE_HOME,ORACLE_SID and PATH in the environment settings.
2. Taken the datapump export to /tmp location as DATA_PUMP_DIR points to /tmp location.
3. Move the dump and log file to location /u01/backup/daily_export_orcl/
4. Change the permissions of the dumpfile so that any user can read the file.

The main thing here is to set the dumpfile name format. The following syntax is used for that:

DUMPFILE=expdp-`date ‘+%d%m%y_%H%M%S’`.dmp

The dumpfiles will be generated as expdp-23062009_090000.dmp, means that the export dump was taken on 23rd June 2009 at 9AM.

To schedule it on hourly basis crontab was modified as:
$ crontab -e

##############################################################
#Script Used To Create Hourly Exports Of orcl database Schemas
###############################################################
00 09-18 * * 1-6 /u01/backup/daily_export_orcl/export.sh >/dev/null

It will taken the export at 9AM,10AM,11AM,12AM,13PM,14PM,15PM,16PM,17PM,18PM on everyday except sunday.

Cheers!!!

- Saurabh Sood

Written by Saurabh Sood

Visit Saurabh Sood's Website