Re: ORA-12154: TNS:could not resolve service name

Hi Frank,
This very much looks like the tnsnames.ora on the SERVER is lacking the los service name. If this is true, let's shake hands. It took me a while to resolve this a few years ago, when I needed it for the first time. If this is not true, incorrect sqlnet.ora's and or wrong global database names may be involved.

Hth,

Sybrand Bakker, Oracle DBA

Frank Siegel wrote:

> Thanks so much in advance! I don't know what else
> to look for or do? Below, please find a lot of info.
>
> Much Appreciated,
> Frank Siegel
> New Hampshire
>
> *****************
> ** BACKGROUND ***
> *****************
>
> --SQL/NET version 2
> --ORACLE 7.4
> -- Network is okay
> -- tcp/ip is okay
> --
> -- schema d_warehouse is trying to SELECT from a table
> -- called LOAD_CONTROL from the d_los schema on a
> -- machine identified in TNSNAMES as los.
> --
> -- TNSNAMES is setup correctly because I can logon
> -- from a client SQL/PLUS.
> --
> -- A public link called los_frank.world (the ".world"
> -- was autmatically appended to the name los_frank)
> -- was created.
> --
> -- WHAT IS WRONG?? CAN YOU HELP PLEASE. HERE ARE
> -- ALL THE QUERIES I CAN THINK OF! if the LISTENER
> -- was not setup correcly on instance "los" would
> -- sql*plus work?
> --
>
> *** ACTUAL QUERY ***
>
> SQL> select *
> 2 from d_los.load_control_at_los_frank.world;
> from d_los.load_control_at_los_frank.world
> *
> ERROR at line 2:
> ORA-12154: TNS:could not resolve service name
>
> SQL> select *
> from d_los.load_control_at_los_frank
> SQL> /
> from d_los.load_control_at_los_frank
> *
> ERROR at line 2:
> ORA-12154: TNS:could not resolve service name
>
> -- Notice This connection from client machine sql*plus:
> SQL> connect d_los/password_at_los
> Connected.
> SQL> -- *** This works from client SQL/PLUS - it resolves TNSname of los
> okay!!!!
>
> **********************************
> ** Privieges for target table ****
> **********************************
>
> SQL> connect d_los/password_at_los
> Connected.
>
> SQL>
> 1 select substr(GRANTEE,1,10) Grantee,
> 2 substr(OWNER,1,12) owner,
> 3 substr(GRANTOR,1,12) grantor,
> 4 substr(PRIVILEGE,1,15) privilege,
> 5 substr(GRANTABLE,1,10) grantable
> 6 from user_tab_privs
> 7* where table_name = 'LOAD_CONTROL'
> SQL> /
>
> GRANTEE OWNER GRANTOR PRIVILEGE grantable
> ---------- ------------ ------------ --------------- ---
> PUBLIC D_LOS D_LOS ALTER NO
> PUBLIC D_LOS D_LOS DELETE NO
> PUBLIC D_LOS D_LOS INDEX NO
> PUBLIC D_LOS D_LOS INSERT NO
> PUBLIC D_LOS D_LOS SELECT NO
> PUBLIC D_LOS D_LOS UPDATE NO
> PUBLIC D_LOS D_LOS REFERENCES NO
>
> 7 rows selected.
>
> SQL>
>
> **********************
> *** DATABASE LINK ****
> **********************
>
> -- I logged onto the machine (dataprod) that i want to link FROM!
> -- as the user d_warehouse and queried the dictionary for the link
>
> SQL> connect d_warehouse/password_at_dataprod
> Connected.
>
> SQL>
> 1 select substr(OWNER,1,10) owner,
> 2 substr(DB_LINK,1,20) db_link,
> 3 substr(USERNAME,1,15) username,
> 4 substr(HOST,1,10) host,
> 5 CREATEd
> 6 from dba_db_links
> 7* where db_link = 'LOS_FRANK.WORLD'
> SQL> /
>
> OWNER DB_LINK USERNAME HOST CREATED
> ---------- -------------------- --------------- ---------- ---------
> PUBLIC LOS_FRANK.WORLD D_LOS LOS 30-MAR-99
>
> **** Parameter file ****
> NUM NAME TYPE VALUE DESCRIPTION
> --- ----------- ---- --------- -----------------------------
> 182 open_links 3 4 max # open links per process
>
> *** TNSNAMES *****
>
> LOS.world =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS =
> (COMMUNITY = tcp.world)
> (PROTOCOL = TCP)
> (Host = 1111.11.111.111)
> (Port = 1521)
> )
> (ADDRESS =
> (COMMUNITY = tcp.world)
> (PROTOCOL = TCP)
> (Host = 111.11.111.111)
> (Port = 1526)
> )
> )
> (CONNECT_DATA = (SID = LOS)
> )
> )

Received on Tue Mar 30 1999 - 13:56:55 CST