David Ghedini
Bash Script for Oracle Data Pump
Red Hat Linux 5.4 or CentOS 5.4 Oracle 11g or 10g SID = orcl This post covers creating a basic bash script to produce a Data Pump export file (.dmp) file which we can then FTP to a remote server or mail the file using MUTT. We'll also generate a log file of the export and write the output of the log file into the body of an email notification. In the first scenario, we want to email the export file as an attachment, so we''ll need to do the following:
- Export the Scott schema on a daily basis using Data Pump.
- Create a log file of the Data Pump export
- Add the date and hour of the export to to the file name and log file in the forms of : scott-yyyymmddhh.dmp. and scottLOG-yyyymmddhh.log
- Use zip or gzip to compress the dump file for easier FTP and/or mailing
- Write the contents of the log file into the body of an email.
- FTP the file or attach the file to the email and send it.
SQL> CREATE OR REPLACE DIRECTORY backdir AS /home/app/oracle/admin/orcl/dpdump SQL> GRANT READ, WRITE ON DIRECTORY backdir TO scott;If not already installed, install MUTT:
[root@server1]# yum install muttCreate your bash script. In the first example, we'll zip the export, attach it to an email and place the export log into the body of the email. I'm creating mine in /usr/lib/myscripts and the file name will be scottscript.sh
[root@server1]# cd /usr/lib/myscripts [root@server1]# vi scottscript.sh
#!/bin/sh ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=orcl; export ORACLE_SID expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2 LOGFILE=scottLOG-$(date +%Y%m%d%H).log zip -r /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "scott Backup" -a /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).zip scott@tiger.comWhat is in the script:
ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=orcl; export ORACLE_SID
expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2
LOGFILE=scottLOG-$(date +%Y%m%d%H).log
zip -r scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp
cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "Scott Backup" -a /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).zip scott@tiger.com
- cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log writes the contents of the log file into the body of the email.
- "Scott Backup" is the email subject line.
- -a /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).zip attaches our file to the email.
- scott@tiger.com is the recipient address.
- Export the Scott schema on a daily basis using Data Pump.
- Create a log file of the Data Pump export
- Add the date and hour of the export to to the file name and log file in the forms of : scott-yyyymmddhh.dmp. and scottLOG-yyyymmddhh.log
- Use zip or gzip to compress the dump file for easier FTP
- Write the contents of the log file into the body of an email notification.
- FTP the file.
#!/bin/sh ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=orcl; export ORACLE_SID HOST='192.168.0.2' USER='ftpuser' PASSWD='password' expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2 LOGFILE=scottLOG-$(date +%Y%m%d%H).log zip -r /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "scott Backup" scott@tiger.com cd /home/app/oracle/admin/orcl/dpdump ftp -n -v $HOST << EOT binary user $USER $PASSWD prompt put scott-$(date +%Y%m%d%H).zip bye EOTWhat is in the script:
ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=orcl; export ORACLE_SIDNext, we set our FTP information:
HOST='192.168.0.2' USER='ftpuser' PASSWD='password'
expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2
LOGFILE=scottLOG-$(date +%Y%m%d%H).log
zip -r scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp
cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "Scott Backup" scott@tiger.com
- cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log writes the contents of the log file into the body of the email.
- "Scott Backup" is the email subject line.
- scott@tiger.com is the recipient address.
cd /home/app/oracle/admin/orcl/dpdump ftp -n -v $HOST << EOT binary user $USER $PASSWD prompt put scott-$(date +%Y%m%d%H).zip bye EOTAgain, you now create a cron job to automate running the script at whatever interval you require. What if I want to use tar/gzip instead of zip? Simply make the substitutions as below:
#!/bin/sh ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=orcl; export ORACLE_SID HOST='192.168.0.2' USER='ftpuser' PASSWD='password' expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2 LOGFILE=scottLOG-$(date +%Y%m%d%H).log tar -cvzf /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).tar.gz home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "scott Backup" scott@tiger.com cd /home/app/oracle/admin/orcl/dpdump ftp -n -v $HOST << EOT binary user $USER $PASSWD prompt put scott-$(date +%Y%m%d%H).tar.gz bye EOT
Posted at 04:47PM Feb 28, 2011 by David in Oracle | Comments[6] | Tags: oracle bash | Export to: |
