scheduling oracle export process

[YMJ]

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

[Christian Gebhardt · Accepted solution]

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

[Carlos Fernandez Riera]

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

...

[T G Manikandan]

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

[Ravi_8]

Hi,

login as xyz user
$crontab -e

[Balaji N]

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

[YMJ]

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

[YMJ]

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

[Carlos Fernandez Riera]

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

[Christian Gebhardt]

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

[Steven E. Protter]

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

[YMJ]

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

Thank very much Cris.

Thank you all

YMJ