| 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 #667541 is a reply to message #667540] |
Fri, 05 January 2018 00:00  |
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 #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 #667563 is a reply to message #667558] |
Fri, 05 January 2018 07:34 |
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 #667565 is a reply to message #667563] |
Fri, 05 January 2018 08:09 |
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 #667579 is a reply to message #667578] |
Fri, 05 January 2018 21:54 |
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 #667590 is a reply to message #667584] |
Sun, 07 January 2018 05:45 |
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 #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 #667599 is a reply to message #667593] |
Mon, 08 January 2018 05:23 |
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 #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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
