David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Monday Feb 28, 2011

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:

  1. Export the Scott schema on a daily basis using Data Pump.
  2. Create a log file of the Data Pump export
  3. 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
  4. Use zip or gzip to compress the dump file for easier FTP and/or mailing
  5. Write the contents of the log file into the body of an email.
  6. FTP the file or attach the file to the email and send it.
To begin, you will need to create an Oracle directory to export the file to and then grant read and write on the directory for the schema.
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 mutt
Create 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.com
What is in the script:
In the first part of the script, we set the required environment.
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
In the next bit, we do our export. In this case, I want to import my file into a local XE instance on my laptop so I'm going to specify VERSION=10.2. Additionally, because I will be doing this daily, I am appending the date in the form of YYYYMMDDHH to the file name:
expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2
Create a log file of our export to see if there were any issues:
LOGFILE=scottLOG-$(date +%Y%m%d%H).log
Compress the .dmp file to make it easier to mail:
zip -r scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp 
Finally, we use MUTT to attach our export file to our email as well as write the log file contents to the body of the email:
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  
In the last bit above:
  • 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.
From here, you can set a cron job to automate running the script at whatever interval you require. Now, instead of emailing the zip file, let's FTP the file to a remote server. In this case, we want to FTP the file, so we'll need to do the following:
  1. Export the Scott schema on a daily basis using Data Pump.
  2. Create a log file of the Data Pump export
  3. 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
  4. Use zip or gzip to compress the dump file for easier FTP
  5. Write the contents of the log file into the body of an email notification.
  6. 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
EOT
What is in the script:
In the first part of the script, we set the required environment.
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 
Next, we set our FTP information:
HOST='192.168.0.2'
USER='ftpuser'
PASSWD='password'
In the next bit, we do our export. Again, I want to import my file into a local XE instance on my laptop so I'm going to specify VERSION=10.2. Additionally, because I will be doing this daily, I am appending the date in the form of YYYYMMDDHH to the file name:
expdp scott/tiger DIRECTORY=backdir DUMPFILE=scott-$(date +%Y%m%d%H) VERSION=10.2
Create a log file of our export to see if there were any issues:
LOGFILE=scottLOG-$(date +%Y%m%d%H).log
Compress the .dmp file to make it easier to FTP:
zip -r scott-$(date +%Y%m%d%H).zip /home/app/oracle/admin/orcl/dpdump/scott-$(date +%Y%m%d%H).dmp 
Now, we use MUTT to write the log file contents to the body of an email notification:
cat /home/app/oracle/admin/orcl/dpdump/scottLOG-$(date +%Y%m%d%H).log | mutt -s "Scott Backup"  scott@tiger.com  
In the bit above:
  • 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. 
Finally, we go our backup directory and FTP the file in binary mode.
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
EOT
Again, 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
    You can also execute it on-demand as well via command line: [root@server1]# cd /usr/lib/myscripts [root@server1]# . /scottscript.sh More About Oracle Datapump
      More about MUTT

      Bookmark and Share




      Main Menu

      Built With

      Search

      Pages

      LinkedIn

      Tag Cloud

      Hosted by:

      Navigation

      Visitors

      Sponsors

      petiole Cloud

      Feeds