Issues with simple cron job to query database
489554May 13 2011 — edited May 18 2011Hi all,
This is really a mix, of both a database issue and a Linux issue, so I thought I'd post it here to get the most visibility. I have been looking into this, and also had a couple other DBA's at my company help, and we are not able to figure it out, so I thought I'd post it here. Basically, I have a simple shell script that is just doing a sqlplus connection to the database and querying V$INSTANCE. When I run the script from the command line, it runs fine, but for the life of me, I can't get it to run from the crontab.
I'm running Oracle 10.1.0.3.0 on a RedHat ES Linux release 3 (Red Hat Enterprise Linux AS release 3 (Taroon Update 5) server. This is all local on the server - the database is on this server, and this is also where I'm running the script.
I've made the script as simple as possible, like this:
-----
#!/bin/sh
sqlplus / as sysdba <<EOF
Select * from v\$instance;
EOF
-----
When I run this script from the command line, it is running fine. But then I put it in a crontab entry like this:
00 * * * * /home/oracle/scripts/cron_test.sh > /home/oracle/scripts/cron_test.log 2>&1
And I am seeing this in the logfile:
-----
Connected to an idle instance.
SQL> SQL> Select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> SQL> Disconnected
-----
At first glance, it seems like a simple issue with environment variables not being set properly, but I have tested this to exhaustion. I've put in all of the environment variables into the shell script to duplicate what I have on the command line, but it still won't work. I've checked on just about anything else I could think of - looked at the tnsnames and listener config, looked at the profile from the crontab in /etc/profile, nothing works...
And on any other server that I test this on, it all works fine and as expected. So I'm trying to figure out why I'm only having this issue on this one particular server?? Some kind of server config or setting that could be causing it?
I appreciate any comments or tips!!
Thanks,
Brad