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  |
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 #667558 is a reply to message #667540] |
Fri, 05 January 2018 06:17  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
dba4oracle wrote on Thu, 04 January 2018 23:39Hi ,
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 #667593 is a reply to message #667590] |
Sun, 07 January 2018 12:45 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
dba4oracle wrote on Sun, 07 January 2018 05:45Hi,
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 #667602 is a reply to message #667599] |
Mon, 08 January 2018 07:59 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
dba4oracle wrote on Mon, 08 January 2018 03:23Thanks 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 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Jun 08 10:55:31 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
