topic Re: scheduling oracle export process in Operating System - HP-UX

scheduling oracle export process

YMJ — Mon, 03 Mar 2003 10:55:09 GMT

GREETINGS

Objective:
Daily backup for oracle database tables using cron.

Overview:
Normaly to export tables iuse this command:
exp username/password full=y file=/backup/ora.dmp

this command will export all database tables to a dump file called ora.dmp

I want to scheduled this command by cron. so, I added the oracle account called xyz to cron.allow

then,
crontab -e xyz

added:
00 18 * * * exp username/password full=y file=/backup/ora.dmp

Problem:
Nothing happen, the cron daemon can't read the above task. I tried another task " ps -ef > file" and it did read it.
IS there any problem with syntax?

Thanks

Re: scheduling oracle export process

Christian Gebhardt — Mon, 03 Mar 2003 11:01:33 GMT

hi

using cron you do not have a login-shell, so try this:

00 18 * * * (. /etc/profile; . ./.profile; exp username/password full=y file=/backup/ora.dmp)

HINT:
"exp username/password ..." will show you the password of this user in the ps-command, better:
"echo username/passwword@SID | exp ..."

Chris

Re: scheduling oracle export process

Carlos Fernandez Riera — Mon, 03 Mar 2003 11:07:20 GMT

1- su - xyz
crontab -l
# See if the cron have read teh crontab file

2- /sbin/init.d/cron stop
/sbin/init.d/cron start

# RE start cron process

3- mailx -f /var/mail/xyz

# see user mail for cron notifications

4- vi /usr/lib/cron/log

# search for commands started foe user xyz

...

Re: scheduling oracle export process

T G Manikandan — Mon, 03 Mar 2003 11:09:23 GMT

The problem could be like the profile of the user not properly setup.
My crontab looks like

40 * * * * /usr/bin/expdat

# vi expdat

#this script is used to export the database
#!/usr/bin/sh
ORACLE_HOME=/opt/app/oracle/product/8.1.7;export ORACLE_HOME
TERM=vt100;export TERM
rm /tmp/database
$ORACLE_HOME/bin/givedb >> /tmp/database
x=`date "+.dmp%d%m%y"`;
for i in `cat /tmp/database`
do
#echo "enter the database name for export :\c"
#read ORACLE_SID;export ORACLE_SID
$ORACLE_HOME/bin/exp system/@$i file=/home/oradmp/$i$x full=y
done

this is the givedb script

ps -ef|grep ora_reco|grep -v grep|awk '{print $9}'| cut -c 10- |egrep 'DB'

thanks

Re: scheduling oracle export process

Ravi_8 — Mon, 03 Mar 2003 11:10:08 GMT

Hi,

login as xyz user
$crontab -e

Re: scheduling oracle export process

Balaji N — Mon, 03 Mar 2003 11:50:38 GMT

hi,
are the variables etc, required for export to run available.

the best way to do is to check the mails for the user with which cron is run and also the cron log.

hth
-balaji

Re: scheduling oracle export process

YMJ — Mon, 03 Mar 2003 11:53:25 GMT

Cris comment:
Doesn't work.

Carlos Comment:
when I view mail for xyz, I notice the following:

sh: /etc/profile: execute permission denied

sh: ./profile: execute permission denied

sh: exp: not found

then, I wrote the whole path for exp, and still nout found.

Other Comments I'll go through it now

Re: scheduling oracle export process

YMJ — Mon, 03 Mar 2003 11:53:53 GMT

Cris comment:
Doesn't work.

Carlos Comment:
when I view the mail for xyz, I notice the following:

sh: /etc/profile: execute permission denied

sh: ./profile: execute permission denied

sh: exp: not found

then, I wrote the whole path for exp, and still nout found.

Other Comments I'll go through it now

Re: scheduling oracle export process

Carlos Fernandez Riera — Mon, 03 Mar 2003 12:12:01 GMT

Hi:

I didnt write ( /etc/.profile) ....

Substitute your exp .... line by a filename.sh.

Inside filename.sh set all VARIABLES you need to run the export - ORACLE_SID, PATH ...-

Include also the line

set -x

so the mail will contain all commands executed.

set execution perms to filename.sh

Re: scheduling oracle export process

Christian Gebhardt — Mon, 03 Mar 2003 12:31:54 GMT

Hi

/etc/profile is not a shell-skript, you cannot execute it, you have to "source" it.
Please copy and paste my answer and you'll find:
. /etc/profile
and
. ./.profile

(dot-space-/etc/profile)

Chris

Re: scheduling oracle export process

Steven E. Protter — Mon, 03 Mar 2003 17:35:10 GMT

Proper permissions on /etc/profile

-r--r--r-- 1 bin bin 2539 Dec 30 08:36 /etc/profile

We don't want to execute it.

As user xyz do you get a schedule when you run crontab -e ?

I didn't see that.

We are going this way currently oracle backups are done by root with this cron syntax.

5 22 * * 1-5 /usr/contrib/bin/backup.oracle.sh 2>&1 | mailx -s "Nighlty Oracle C
old Backup" HPSysCronLog@juf.org

Inside the job....
/usr/bin/su - oracle -c "/usr/contrib/bin/nightly.backup.oracle.sh"

This handles making sure the backup is done by oracle user not root.

SEP

Re: scheduling oracle export process

YMJ — Tue, 04 Mar 2003 06:51:06 GMT

YES it works with Cris comment. I didn't notice the spaces between dots.

Thank very much Cris.

Thank you all

YMJ