DB link issue - What could be happening?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DB link issue - What could be happening?

  1. #1
    Join Date
    Feb 2001
    Posts
    295
    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

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    In root's cron put:

    su - c oracle path_file to execute

    Regards

    Angel

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Jan 2001
    Posts
    515

    Lightbulb .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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width