This discussion is archived
9 Replies Latest reply: Feb 19, 2013 12:24 PM by 817469 RSS

ORA-12154 with 11g and heterogeneous services

817469 Newbie
Currently Being Moderated
Hello,
I have a procedure that bring some data from a mysql server over a HS service.
In 10G worked just fine, but after I migrated to 11g , some weird things happens.
If I ran the procedure (or the job that calls it) directly from sqlplus it goes just fine, but if I schedule a job to run that procedure it breaks with : ORA-12154: TNS:could not resolve the connect identifier specified.
It's like some environment settings or rights are missing when that job runs automatically.
So what's changed in 11g ???


Any help is appreciated,
Thanks
  • 1. Re: ORA-12154 with 11g and heterogeneous services
    sb92075 Guru
    Currently Being Moderated
    veritas wrote:
    Hello,
    I have a procedure that bring some data from a mysql server over a HS service.
    In 10G worked just fine, but after I migrated to 11g , some weird things happens.
    If I ran the procedure (or the job that calls it) directly from sqlplus it goes just fine, but if I schedule a job to run that procedure it breaks with : ORA-12154: TNS:could not resolve the connect identifier specified.
    It's like some environment settings or rights are missing when that job runs automatically.
    So what's changed in 11g ???


    Any help is appreciated,
    Thanks
    many things changed; like ACLS on network objects.
  • 2. Re: ORA-12154 with 11g and heterogeneous services
    817469 Newbie
    Currently Being Moderated
    But the error is ORA-12154 not ORA-24247(network access denied...)
    Also, I though that ACLS is about restricting UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL programs ...
    Is it about db links too !?

    Edited by: veritas on Feb 19, 2013 5:31 PM
  • 3. Re: ORA-12154 with 11g and heterogeneous services
    sb92075 Guru
    Currently Being Moderated
    ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
    ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
    ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
    TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
    The lookup operation fails because the name provided can NOT be resolved to any remote DB.
    The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
    The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
    The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
    So post the content of the sqlnet.ora file.
    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
    http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
  • 4. Re: ORA-12154 with 11g and heterogeneous services
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to MOS note
    OERR: ORA 12154 "TNS:could not resolve service name". [ID 21321.1]
    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
  • 5. Re: ORA-12154 with 11g and heterogeneous services
    817469 Newbie
    Currently Being Moderated
    Nice post ! It gave me the right ideea !
    What I didn't mention is that I'm in a RAC environment so, as you know from 11g the grid has his own listener. The same listener it's used for db too, so config files (listener.ora, tnsnames.ora, sqlnet.ora, etc ) are located in $GRID_HOME/network/admin. The second I move the tnsnames.ora in $ORACLE_HOME/network/admin my job worked just fine !
    But now I have other questions :

    1. How come that running from sqlplus/worksheet client the oracle "knows" the right location of TNS_ADMIN, and then when running the job automatically
    it doesn't anymore ?
    2. I read about setting env variable with srvctl setenv but I assume that involve the instance restart too ... which I can't afford. So, any way I can set TNS_ADMIN at my job or code level so I don't have to keep 2 tnsnames.ora ?

    Thanks for the link !
  • 6. Re: ORA-12154 with 11g and heterogeneous services
    sb92075 Guru
    Currently Being Moderated
    veritas wrote:
    Nice post ! It gave me the right ideea !
    What I didn't mention is that I'm in a RAC environment so, as you know from 11g the grid has his own listener. The same listener it's used for db too, so config files (listener.ora, tnsnames.ora, sqlnet.ora, etc ) are located in $GRID_HOME/network/admin. The second I move the tnsnames.ora in $ORACLE_HOME/network/admin my job worked just fine !
    But now I have other questions :

    1. How come that running from sqlplus/worksheet client the oracle "knows" the right location of TNS_ADMIN,
    Interactive processes can have their environmental variables established by .bash_profile or similar
    BTW, TNS_ADMIN variable is optional & not required to exist.
    and then when running the job automatically it doesn't anymore ?
    batch jobs do NOT by default invoke .bash_profile when they start.
    So you need to manually establish ones like ORACLE_HOME & PATH before they can be used.
    2. I read about setting env variable with srvctl setenv but I assume that involve the instance restart too ... which I can't afford. So, any way I can set TNS_ADMIN at my job or code level so I don't have to keep 2 tnsnames.ora ?
    a soflink can replace one of the two files; so that only a single copy of tnsnames.ora file exists
  • 7. Re: ORA-12154 with 11g and heterogeneous services
    817469 Newbie
    Currently Being Moderated
    Well, maybe I wasn't very clear. When I said sqlplus/worksheet client I meant "client" as in a different workstation with an sql client that connects to oracle server (which is on a different system). So, .bash_profile or .bashrc or any other os user profile file has no relevance here ... becouse I'm not running it on the server ! Also, my job is not a batch one , it's a dbms_scheduler one, so it's running inside oracle, that's why this thing is puzzling me .
    Yes, softlink is a way to have only one file, I was thinking maybe there is another way, directly from oracle, to point him to the right location for TNS_ADMIN.
  • 8. Re: ORA-12154 with 11g and heterogeneous services
    sb92075 Guru
    Currently Being Moderated
    I am CONFUSED

    I do not understand what is wrong.
  • 9. Re: ORA-12154 with 11g and heterogeneous services
    817469 Newbie
    Currently Being Moderated
    Sorry about that, let me try again :)

    I have a stored plsql procedure that brings some data over a dblink(hs service involve). When I ran that procedure from an sql client (sqlplus, worksheet, etc) from a client workstation (not the server) the procedure runs just fine, which mean oracle server read the right TNS_ADMIN location from the server !
    BUT, when I create a dbms_scheduler job that run the specified procedure, it fails with ora-12154 becouse it just assume that TNS_ADMIN on the server is in $ORACLE_HOME/network/admin (and that's not the case) ! The question is why the difference in determine TNS_ADMIN ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points