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

Question

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?

Answer 1

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:

1. Maybe don't run the job once per minute, unless that's what you really meant to do...
2. Think about using Datapump (expdp) instead of export, unless you have a very specific reason to use exp.
3. Think about not putting your username/password in the script. You can use an account with OS authentication, or use Oracle Wallet to hold your credentials: https://pmdba.wordpress.com/2020/01/13/how-to-hide-oracle-passwords-in-a-script/

Answer 2

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

#!/bin/sh
ORAENV_ASK=NO
ORACLE_SID=$1
source oraenv
expdp user/pswd owner=scott file=scott.dmp log=scott.log

Then, in crontab

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

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

-- Edit II Modifying to allow a single script to be used against multiple databases. In the script, instead of hard-coding the value of ORACLE_SID, set it to the value of the first command line parameter, set when calling the script. Then, on the crontab entry (which will have to be hard-coded for each job anyway), set the value for ORACLE_SID. In example above, ORACLE_SID will be set to 'mydb'.