cron

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

23 June, 2009 by 10g 15

Scheduling Job through Cron??

In case you came across this link searching for Cron syntax, then you can refer to following link

http://www.adminschoice.com/docs/crontab.htm

Other’s can continue reading the article …. 😉

This was a problem which I faced yesterday. We had a mview refresh which was lagging on one of the server. On checking master site, I found that there were many pending requests coming from the second materialized view site . Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event.

These mviews were being refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result

 $ps -ef|grep scott|grep -v grep
    oracle 11725 11714  0 13:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 19981 19970  0  Feb  7  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 24794 24781  0 06:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 10538 10527  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 13972 13935  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  2601  2592  0 19:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 17274 17246  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  8308  8294  0 13:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

This was definitely wrong. I killed the database sessions and also killed the OS processes

$ ps -ef|grep Refresh_scott.sh|grep -v grep|awk ‘{print $2}’|xargs kill -9

This issue had occurred as one more site was added for mview refresh and due to locking issues (while deleting records from Mlog$), job could not complete before next refresh schedule. While scheduling scripts through cron, care should be taken to put a check if script is already running or not. This is required as Cron will spawn the new job (as per job schedule) irrespective of fact that whether earlier execution has completed or not. I used following logic to implement the same check

cnt=`ps -ef|grep Refresh_scott.sh|grep -v grep |wc -l`
if [ $cnt -eq 1 ]
then
echo "Running Fast Refresh now " > $DIR/Check_refresh_scott.log
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
else
echo "Fast refresh already running " > $DIR/Check_refresh_scott.log
date >> $DIR/Check_refresh_scott.log
exit
fi

There could be other logics too which can be used to implement the same. e.g Other could be

filename=$DIR/Check_refresh_scott.log
if [ -e $filename  ]
then
echo "Fast Refresh already running " > refresh_runnning.log
exit
else
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
rm $filename
fi

Other way could be to use DBMS_JOB and DBMS_SCHEDULER to schedule the database job.

9 February, 2009 by 10g 11g 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers