How could I make the exp/expdp Oracle CLI works with a cronjob in Linux?-CodePudding

I have the following cronjob defined in my /etc/crontab Linux file:

* * * * * oracle cd "/home/oracle" && exp "user"/"password"@"TNS" owner="user" file="user".dmp log="user".log;

For purposes of exemplification, the asterisk symbol * is being used in all fields to represent any time for the exp command to execute.

When I try to execute this command directly via CLI, it works perfectly fine, that is, the backup and log files are created at the directory where the command is being executed in:

cd "/home/oracle" && exp "user"/"password"@"TNS" owner="user" file="user".dmp log="user".log

However, for some reason, when cron tries to execute it, it does not work. Why? I have already tried to define both PATH and SHELL variables at the top of the file, so that cron environment could see where to find this exp executable. Just like this:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:$ORACLE_HOME/bin

* * * * * oracle cd "/home/oracle" && exp "user"/"password"@"TNS" owner="user" file="user".dmp log="user".log;

I tried to replace the $ORACLE_HOME to its literal directory too, which is /opt/oracle; it didn't work.

I either tried to replace the exp command to where it is located according to its absolute path, just like this: /opt/oracle/bin/exp; it didn't work nevertheless.

Cron is working though, because when I execute a simpler task (for instance, create a file in a directory where cd command took the cron into), it works fine.

* * * * * cd "/home/oracle" && touch text.txt;

Again, why is this happening? Am I missing some relevant information about how cron works?

CodePudding user response：

Write a shell script to perform your export. Define your environment variables within that script; you can't do it in the crontab. Test that your shell script works independently, then use cron to run the shell script.

/home/oracle/export.sh:

#!/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:$ORACLE_HOME/bin
export PATH

/opt/oracle/bin/exp user/password@TNS owner=user file=/home/oracle/user.dmp log=user.log;

crontab:

* * * * * /home/oracle/export.sh

Also, maybe don't run the job once per minute, unless that's what you really meant to do...

CodePudding user response：

processes submitted by cron do not get the .bash_profile script executed, as does on when one logs on to a command-line shell process such as ssh. Thus, your cron job is not inheriting all of the environment that your command line process has.

Rather than try to stack a bunch of commands onto one 'command line' in the crontab entry, you should write a shell script that does everything you want/need - including a call to oraenv to set the environment:

script my_expdp.sh

ORAENV_ASK=NO
ORACLE_SID=mydb
source oraenv
expdp user/pswd owner=scott file=scott.dmp log=scott.log

Then, in crontab

* * * * *  /home/oracle/my_expdp.sh

-- Edit I see that @pmdba posted essentially the same solution as I did, even as I was writing my answer.