Home » Other » General » SQL Report running manually not through cron (11g32 linux(RHEL))
SQL Report running manually not through cron [message #667540] Thu, 04 January 2018 23:39 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi ,

For oracle 11gr2 on linux (RHEL)
I have similar issue ,i have sql report scheduled in cron through shell script,shell script is running properly as per schedule,but when shell script call sql within
giving connection error,i crosschecked things like username,password,sid,file permissions,path etc
also same shell script when i run manually without cron,its running properly without issue and generating report (no issue of oracle connection issue so username ,password,
oracle home,path can be ruled out),i give permission as 777 to check still not working

same script on other server is working,is it issue with linux version

Thanks
Re: SQL Report running manually not through cron [message #667541 is a reply to message #667540] Fri, 05 January 2018 00:00 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Just to troubleshoot i created similar simple script as
select sysdate from dual;

in the sql file,for this script also same error not able to connect to oracle

SP2-0640: Not connected
SP2-0640: Not connected


when i execute same manually from linux prompt it work without issue generate output by connection oracle/db

Pl suggest

Thanks
Re: SQL Report running manually not through cron [message #667542 is a reply to message #667541] Fri, 05 January 2018 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suggest you fix your script.

Re: SQL Report running manually not through cron [message #667543 is a reply to message #667542] Fri, 05 January 2018 01:17 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
script has single sql statement as below

select sysdate from dual;

what to fix in this

its working manually without issue only in cron its showing connection error (TNS sid not found)


Re: SQL Report running manually not through cron [message #667544 is a reply to message #667543] Fri, 05 January 2018 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So the problem is NOT in the SQL script but in the script (or application) that calls it.
Fix this one.

Re: SQL Report running manually not through cron [message #667548 is a reply to message #667540] Fri, 05 January 2018 02:59 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
If you post the shell script and the SQL script and the cron output perhaps someone can assist.
Re: SQL Report running manually not through cron [message #667549 is a reply to message #667548] Fri, 05 January 2018 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I expected he, in the end, by himself, would understand that with no code no actual information we can't debug it. Smile

Re: SQL Report running manually not through cron [message #667553 is a reply to message #667549] Fri, 05 January 2018 04:03 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
I envy someone who cam maintain such a Panglossian image of the world, https://en.wikipedia.org/wiki/Candide
Re: SQL Report running manually not through cron [message #667558 is a reply to message #667540] Fri, 05 January 2018 06:17 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
dba4oracle wrote on Thu, 04 January 2018 23:39
Hi ,

For oracle 11gr2 on linux (RHEL)
I have similar issue ,i have sql report scheduled in cron through shell script,shell script is running properly as per schedule,but when shell script call sql within
giving connection error,i crosschecked things like username,password,sid,file permissions,path etc
also same shell script when i run manually without cron,its running properly without issue and generating report (no issue of oracle connection issue so username ,password,
oracle home,path can be ruled out),i give permission as 777 to check still not working

same script on other server is working,is it issue with linux version

Thanks
Can you debug code that you cannot see?
Neither can anyone else.
I'd say your problem is right here: -->
Re: SQL Report running manually not through cron [message #667560 is a reply to message #667540] Fri, 05 January 2018 07:18 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Thu, 04 January 2018 21:39
Hi ,

For oracle 11gr2 on linux (RHEL)
I have similar issue ,i have sql report scheduled in cron through shell script
When cron job runs, the OS environment is NOT the same as for interactive process.
It is most likely that ORACLE_HOME and/or ORACLE_SID are not defined for cron job.
This can be verified by adding line below to script invoked by cron

env | sort -o /tmp/capture.env

post content of /tmp/capture.env back to this forum
Re: SQL Report running manually not through cron [message #667563 is a reply to message #667558] Fri, 05 January 2018 07:34 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Ok pasting 3 files used as below owned by oracle

3 files as below

1)
connection.ctl
connection.ctl

username = scott
password = tiger
sid = orcl
ORACLE_HOME = /u01/app/oracle

export username
export password
export sid
export ORACLE_HOME
config= /path/connection.ctl


2)sql file
test.sql
select sysdate from dual;

3)shell script
test.sh
. config
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
conn $username/$password@$sid
@test.sql
exit
EOF


cron tab entry of oracle user

*/10 * * * * /home/oracle/scripts/test.sh
Re: SQL Report running manually not through cron [message #667564 is a reply to message #667563] Fri, 05 January 2018 07:54 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
modify test.sh to be as shown below

. config
env | sort -o /tmp/capture.env
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
conn $username/$password@$sid
@test.sql
exit
EOF

after cron job runs test.sh post content of /tmp/capture.env file back here
Re: SQL Report running manually not through cron [message #667565 is a reply to message #667563] Fri, 05 January 2018 08:09 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
even i hard coding in script as
/pathoraclehome/bin/sqlplus /nolog <<EOF
conn scott/tiger@orcl
@test.sql
exit
EOF


with hard coding dependency of env var should not be there
same error . so i doubt if os version or single/double quote to be given (i also tried giving single,doble quote)
Re: SQL Report running manually not through cron [message #667569 is a reply to message #667565] Fri, 05 January 2018 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
Root cause & solution are external to Oracle at OS level.
When OS environment is not correct, then error results when you attempt to connect to Oracle database.
At a minimum ORACLE_HOME environmental variable needs to be set & possibly ORACLE_SID is needed, too.
Re: SQL Report running manually not through cron [message #667570 is a reply to message #667569] Fri, 05 January 2018 09:38 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
yes env setting is cause mostly for connection issue but when hard coding oracle home ,sid it should work
Re: SQL Report running manually not through cron [message #667571 is a reply to message #667570] Fri, 05 January 2018 09:40 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
oracle home is not issue as it is able to execute sqlplus ,while connecting its giving error,somehow it could not find sid when run from cron even after hard coding ,so i doubt if quotes required .also on other server same scripts working without issue
Re: SQL Report running manually not through cron [message #667576 is a reply to message #667571] Fri, 05 January 2018 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
[oracle@vbgeneric ~]$ cd $ORACLE_HOME/bin
[oracle@vbgeneric bin]$ unset ORACLE_HOME
[oracle@vbgeneric bin]$ ./sqlplus scott/tiger
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[oracle@vbgeneric bin]$ pwd
/u01/app/oracle/product/12.2/db_1/bin
[oracle@vbgeneric bin]$ export ORACLE_HOME=/u01/app/oracle/product/12.2/db_1
[oracle@vbgeneric bin]$ ./sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 11:19:21 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Tue Dec 26 2017 18:51:39 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
Re: SQL Report running manually not through cron [message #667578 is a reply to message #667576] Fri, 05 January 2018 21:42 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
for troubleshooting if i am hardcoding oracle home,sid,username,password,path etc
will it matter env has any issue?
Re: SQL Report running manually not through cron [message #667579 is a reply to message #667578] Fri, 05 January 2018 21:54 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
i captured env in file while running in cron,as expected env have all values properly

below is env.capture


HOME=/data/myuser/user1
HOST=IAPL1
LANG=en_US.UTF-8
LOGNAME=user1
MAILTO=myuser@mycompany.com
ORACLE_HOME=/opt/u01/app/oracle/product/11.2.0.4/client
PASSWORD=abc123
PATH=/opt/u01/app/oracle/product/11.2.0.4/client/bin:/usr/bin:/bin
PWD=/data/myuser/user1
SHELL=/bin/sh
SHLVL=2
SID=ORCL
SQL_FILE=/data/myuser/user1/test.sql
USERNAME=user1
USER=user1
_=/usr/bin/env
Re: SQL Report running manually not through cron [message #667580 is a reply to message #667579] Fri, 05 January 2018 21:55 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
this is latest error in log

SQL> ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL> SP2-0640: Not connected
SP2-0640: Not connected


but same is working fine when run manually (outside cron) same sid (TNS entry)
Re: SQL Report running manually not through cron [message #667581 is a reply to message #667580] Sat, 06 January 2018 07:44 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
I can't say what you do wrong is you decided to NOT actually show us exactly what you do do.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: SQL Report running manually not through cron [message #667582 is a reply to message #667581] Sat, 06 January 2018 07:50 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
its simple one line sql code
and shell script calling sql file,using oracle db connection user password,sid

if my code is still confusion,please suggest simple code ,just connecting to db from shell script from cron
Re: SQL Report running manually not through cron [message #667583 is a reply to message #667582] Sat, 06 January 2018 07:55 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=cron+script+to+invoke+oracle+example
Re: SQL Report running manually not through cron [message #667584 is a reply to message #667580] Sat, 06 January 2018 08:09 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
dba4oracle wrote on Fri, 05 January 2018 21:55
this is latest error in log

SQL> ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL> SP2-0640: Not connected
SP2-0640: Not connected


but same is working fine when run manually (outside cron) same sid (TNS entry)
Before "run manually (outside cron)" execute the following:

echo $TNS_ADMIN

Also, inside your script, just before invoking sqlplus, add the same command, 'echo $TNS_ADMIN.

Also, from a command line, execute

find / -name tnsnames.ora

And read this: http://edstevensdba.com/oracle-tns/ora-12154tns-03505/
Re: SQL Report running manually not through cron [message #667590 is a reply to message #667584] Sun, 07 January 2018 05:45 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi,

When run manually $TNS_ADMIN was giving proper output ,but in script it was printing blank,so i exported with path in script,after which its giving output properly
I included echo $TNS_ADMIN in script
output as below

/opt/u01/app/oracle/product/11.2.0.4/client/network/admin/tnsnames.ora
and find also showing same path

one observation user which i am using not having permission on this tns file,may be thats issue ,i asked os admin to grant permission
but again how it is working fine manually ,how it is able to find tns file ,if permission is issue

Thanks
Re: SQL Report running manually not through cron [message #667591 is a reply to message #667590] Sun, 07 January 2018 05:46 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
after exportint TNS_ADMIN ISSUE STILL SAME
Re: SQL Report running manually not through cron [message #667592 is a reply to message #667591] Sun, 07 January 2018 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
tnsnames.ora file is NOT required to log into local database.

is cron job running on DB Server itself?

>SID=ORCL
above is useless & wrong.
it should be
ORACLE_SID=<actual_sid>
on *NIX the SiD is CaSeSeNSTiVe; where "sid" differs from "SID"

post results from OS command below

ls -l $ORACLE_HOME/dbs
Re: SQL Report running manually not through cron [message #667593 is a reply to message #667590] Sun, 07 January 2018 12:45 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
dba4oracle wrote on Sun, 07 January 2018 05:45
Hi,

When run manually $TNS_ADMIN was giving proper output ,but in script it was printing blank,so i exported with path in script,after which its giving output properly
Just as was pointed out earlier, when you submit a job through cron it does not inherit the same enviornment as you have when you log on and work with your shell command line. Any scripts submitted via cron are responsible for setting ALL environment variables that they need.

But as BlackSwan pointed out, if this job is running on the same server as the database, there is no need to involve TNS at all. To that end, all your script needs to do is

ORACLE_HOME=<path to oracle home>
ORACLE_SID=<youroraclesid>
PATH=$ORACLE_HOME/bin:$PATH
sqlplus / @myrptscript

And once you realize that, you don't even need to hard-code those variables, but allow the oraenv utility to do it:
ORACLE_SID=<youroraclesid>
ORAENV_ASK=NO
source oraenv
sqlplus / @myrptscript

Actually, all of my scripts go a few steps further. First, I don't even hard-code ORACLE_SID, but pass it as a command-line variable. This allows me to reuse the script with any database:

ORACLE_SID=$1
ORAENV_ASK=NO
source oraenv
sqlplus / @myrptscript

And since the script doesn't control the value of ORACLE_SID, I also add code to compare the supplied value against the contents of /etc/oratab before invoking oraenv. If there is no match, the script exits with an error message written to the run-time log (that the script is also responsible for).

And if the script is meant for command-line use instead of batch scheduling, I even add code to parse out /etc/oratab and present the list of databases as a numbered menu, requesting the user to select from the menu.

But the key thing for you right now is:
1) cron does not pass any enviornment variables, so the script is responsible for ALL of them.
2) you do not need to involve tns at all for a local database.
Re: SQL Report running manually not through cron [message #667599 is a reply to message #667593] Mon, 08 January 2018 05:23 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Thanks Stevens

Few clarification,database is not on same server ,i scheduled this on app server and app server is connecting to db using tns file ,so tns will be used

and as mentioned by hard coding is not requried we can pass values to the script ,actually i am doing same on other servers,but on this server since i started facing issue of tns,i created this simple script to troubleshoot,and done hard coding just to rule out if env var is actually causing issue,but even after hard coding all values in script it gives tns error
as observed tnsnames.ora file is not having permission for user running cron.i asked os admin to fix that.will update if this fix the issue

Re: SQL Report running manually not through cron [message #667600 is a reply to message #667599] Mon, 08 January 2018 05:35 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
I still have no idea of what you are doing. Can you cat the various scripts and then run crontab -l to show the job? Copy/paste your terminal session where you do that here, and please remember to enclose it with [code] tags.
Re: SQL Report running manually not through cron [message #667602 is a reply to message #667599] Mon, 08 January 2018 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Mon, 08 January 2018 03:23
Thanks Stevens

Few clarification,database is not on same server ,i scheduled this on app server and app server is connecting to db using tns file ,so tns will be used

and as mentioned by hard coding is not requried we can pass values to the script ,actually i am doing same on other servers,but on this server since i started facing issue of tns,i created this simple script to troubleshoot,and done hard coding just to rule out if env var is actually causing issue,but even after hard coding all values in script it gives tns error
as observed tnsnames.ora file is not having permission for user running cron.i asked os admin to fix that.will update if this fix the issue

You can create & SQLPLUS will utilize your own private copy of tnsnmes.ora when named as below

/data/myuser/user1/.tnsnames.ora

Notice that there is a dot (".") preceding and as part of actual filename.
Re: SQL Report running manually not through cron [message #667871 is a reply to message #667602] Tue, 23 January 2018 02:30 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi guys,

Sorry for late update,actually as suspected problem was on read permissions on tns file
so osadmin copied tns file to alternate location (where cron has ownership as changing permission was not feasible)
pointed TNS_ADMIN to this location,and it worked fine

Thanks for your suggestions
Previous Topic: Is there a free unzip utility available from Oracle or elsewhere?
Next Topic: Is there extra thing I need to do to download from edelivery's wget
Goto Forum:
  


Current Time: Fri Jun 05 03:55:27 CDT 2020