-
Hi guys,
Some days ago, I was asked to check a problem with an Oracle procedure executed by a shell script. Since I don't have DBA access and I don't have the procedure source, I couldn't gather as much info as I wanted to, but I concluded that:
- The shell script is executed by OS user root (cron), with OS user oracle environment variables $ORACLE_HOME and $ORACLE_SID. It exits with ORA-01254 (TNS - Could not resolve service name). When I run the shell with OS user oracle (su - oracle), it works fine.
- I tested setting ALL the oracle user environment to root (NLS, ORACLE_BASE etc) but it didn't work either.
- I found out that the procedure accessed a remote view, and aparently the developer didn't know that, since the view had a public synonym and the db_link was correctly set. That explained the TNS problem.
- Both instances were in tnsnames, responded to tnsping and I could connect to them individually using sqlplus. However, when I tried to access the remote database using the shell script/sqlplus and root on OS, the error was shown. The authentication was on db level (not OS), there is a specific db user different than oracle and root, so that's not the problem.
- I reported the fact to the DBA and asked him to review the db_link configuration. So, he included the tns descriptor (instead of the service name) in the db_link and the procedure worked.
The problem is now considered solved, but I think that solution is a workaround, the problem was not detected. The db_link wasn't completely wrong, since the remote db was accessed using OS user oracle. For some reason, the remote db wasn't seen using db_link when OS user is root.
- I tried to reproduce the problem at home, but everything worked fine. (that was the worst part...).
I know the information may be not enough, but that's all I could check since I don't have DBA access and don't have the procedure source. Can you guys maybe give me some ideas?
thanks in advance.
Obs: Oracle 8.1.6 running on AIX
-
In root's cron put:
su - c oracle path_file to execute
Regards
Angel
-
It could be the way the db link was setted up. If the link had not have the servicename specified, then you have to provide the service name to connect to the database. As far as the user is concerned, I need to read you post again :D
Sam
-
.profile
sometimes you have to invoke your profile at the beginning if your shell scripts. Try adding this to the top of your script and make sure that the user id 's profile has the correct environment variables set:
cd
export HOME=`pwd`
. ./.profile
