1

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
ilwl
  • 35
  • 1
  • 6

2 Answers2

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/
pmdba
  • 6,457
  • 2
  • 6
  • 16
1

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'.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • Hey! Sorry for my ignorance. I've never had to use 'oraenv' command before. Would the cron 'see' this command (`oraenv`) when running the script? Since the environment isn't inherited by cron. And what if my ORACLE_SID name changes according to the system where it was installed on? What could I do to automate it? – ilwl Apr 16 '22 at 14:47
  • the oraenv sets the environment, speifically ORACLE_HOME, PATH, and ORACLE_SID. You should read up on it. It does set the environment when included in the script. As I and others have said, your crontab entry should not try stacking multiple commands on a single line. Instead, just call a shell script and have the script do _all_ of the necessary work, including the call to crontab. – EdStevens Apr 16 '22 at 14:57
  • As for changing ORACLE_SID, and how to change it. In my example I was relying on a local connection, which in turn relies on ORACLE_SID. I did this because I assumed that the cron job was running on the same machine as the database. But you are usng exp, not expdp, and specifying a network connection. Is it safe to assume that your cron job is _not_ running on the db server? If so, why? In any event, how were you planning on making your net service name ('@TNS') dynamic? – EdStevens Apr 16 '22 at 15:01
  • I got it about the `oraenv`; I was just wondering if cron would know where it is located so that it'd execute the command with no mistakes. But as you said, it'd work fine when inserted in a script, so that's ok. – ilwl Apr 16 '22 at 15:06
  • Yes! It is running on an Oracle Database server, however, the script isn't going to run on a unique/singular server, thus the `ORACLE_SID` environment variable could change its name depending on which server the script is running in. This is why I am asking how I could ended up knowing the SID's name without prompting to it from the user. – ilwl Apr 16 '22 at 15:09
  • Well, if it doesn't locate it, all you have to do is either modify PATH to include the directory where it is located (/usr/local/bin) before calling it. or fully qualify it with the directory name. The key point to the whole exercise is that when you start an interactive command session, say by logging on with ssh, some scripts get executed to set up the environment (variables) for that command line process. When cron launches a process, those scripts are not executed, so the script being executed has to take responsibility for setting the environment. – EdStevens Apr 16 '22 at 15:12
  • _"It is running on an Oracle Database server, however, the script isn't going to run on a unique/singular server,"_ So you have multiple db servers, each with its own db, and you will be copying this script? _"This is why I am asking how I could ended up knowing the SID's name without prompting to it from the user"_ Well, how would you know how to set '@TNS', as shown in your original attempt? – EdStevens Apr 16 '22 at 15:17
  • Asking the user, maybe I should do the same. Btw, tyvm for the answer! It was really helpful – ilwl Apr 16 '22 at 15:36
  • I've edited my answer to show how to use command-line parms to set ORACLE_SID. – EdStevens Apr 16 '22 at 16:11