David Ghedini

Monday Feb 28, 2011

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.

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:

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
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

Posted at 04:47PM Feb 28, 2011 by David in Oracle | Comments[7] | Tags: oracle bash | Export to: |