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
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.
Hi Niel,
There is solution for your password problem.
It is named as “Secure External Password Store” and is implemented by using “Oracle Wallet”.
It is described by Amit. Check the following link:
http://askdba.org/weblog/2009/09/using-oracle-wallet-to-execute-shell-scriptcron-without-hard-coded-oracle-database-password/
Hope this will help you..
Cheers!!!
Saurabh Sood
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
Thanks. That is a good idea.
thanks..it is very helpful
hi
dear sir
plz. check ur code there is some prob..
i cant understand where is prob.
plz. reply me my id
thz.
awaiting ur reply
Hi Ghulam,
I cannot find any problem with the code.
Let me know what issue you are facing.
Cheers!!!
Saurabh Sood
@ Neil,
For password Hiding, Oracle Wallet can be used:
Following post explain that:
http://askdba.org/weblog/2009/09/using-oracle-wallet-to-execute-shell-scriptcron-without-hard-coded-oracle-database-password/
Cheers!!!
Saurabh Sood
Neil,
For password Hiding, Oracle Wallet can be used:
Following post explain that:
http://askdba.org/weblog/2009/09/using-oracle-wallet-to-execute-shell-scriptcron-without-hard-coded-oracle-database-password/
Cheers!!!
Saurabh Sood
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
Lisa,
I think while specifying logfile parameter, u ended with double quote “. Actaully it is single quote and then `
-Amit
thanks for sharing the script.. but where should i DUMPFILE=expdp-`date ‘+%d%m%y_%H%M%S’`.dmp set it….. pls do reply….
Hi Saurabh Sood,
i just followed your script but i keep getting the below error… pls solve the below error
From oracle10g@localhost.localdomain 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: root@localhost.localdomain (Cron Daemon)
To: oracle10g@localhost.localdomain
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
Prashanth,
I think that you don’t have execute permissions for the user running this script
Regards
Amit
please re write for me this command for windows environment
Thanks a lot