Generating Datapump Export Dump with Dumpfile name Containing Current Date and Time

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

15 thoughts on “Generating Datapump Export Dump with Dumpfile name Containing Current Date and Time

  1. Is there anyway to run a script like this and conceal the schema users password? We have are not allowed to store any passwords on the file system. If there was a way to store the passwords that would be great.

  2. Neil,

    I doubt that we can do this without hiding password. But you can schedule DataPump jobs with dbms_scheduler if you have any security risk from OS.

    - Saurabh Sood

  3. Saurabh,

    I got an error, LRM-00112: multiple values not allowed for parameter ‘logfile’, while running the same script:

    #!/bin/ksh

    ################################################################
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
    export ORACLE_SID=hrdmo91
    export PATH=$PATH:$ORACLE_HOME/bin
    expdp sysadm/sysadm@hrdmo91 dumpfile=expdp-’date ‘+%d%m%Y_%H%M%S”.dmp directory=hrdmo91_dir logfile=expdp-’date ‘+%d%m%Y_%H%M%S”.log schemas=sysadm

    Could you shed some lights how to fix it?
    Thank you in advance,
    Lisa

  4. thanks for sharing the script.. but where should i DUMPFILE=expdp-`date ‘+%d%m%y_%H%M%S’`.dmp set it….. pls do reply….

  5. Hi Saurabh Sood,
    i just followed your script but i keep getting the below error… pls solve the below error

    From [email protected] Wed Mar 7 11:00:03 2012
    Return-Path:
    Received: from localhost.localdomain (localhost.localdomain [127.0.0.1])
    by localhost.localdomain (8.13.1/8.13.1) with ESMTP id q275U2Ds007722
    for ; Wed, 7 Mar 2012 11:00:02 +0530
    Received: (from oracle10g@localhost)
    by localhost.localdomain (8.13.1/8.13.1/Submit) id q275U2OO007721;
    Wed, 7 Mar 2012 11:00:02 +0530
    Date: Wed, 7 Mar 2012 11:00:02 +0530
    Message-Id:
    From: [email protected] (Cron Daemon)
    To: [email protected]
    Subject: Cron /home/oracle10g/exp.sh
    X-Cron-Env:
    X-Cron-Env:
    X-Cron-Env:
    X-Cron-Env:
    X-Cron-Env:

    /bin/sh: /home/oracle10g/exp.sh: Permission denied

Leave a Reply