DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TNS error - ORA-12154

  1. #1
    Join Date
    Oct 2000
    Posts
    467
    Hi,
    I'm using Oracle 8.1.7 with Solaris 2.6. I am not able to select data using a db link. Heres what i've done - Using SQL client for WinNT.

    SQL> conn system/pw@srv
    connected.
    SQL> create databae link etslink connect to system
    identified by pw using 'ETS';

    SQL> /

    Database link created.

    SQL> select * from dba_tables@etsllink;
    select * from tab@etsvollink
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name


    I have both the DB settings set up in TNSNAMES.ORA and am not using SQLNET.ORA. Secondly, I'm able to ping to the other server as well. What could b the problem ? I can also connect to both the DBs without DB link.


    Cheers,
    Vinit

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by vinit
    Hi,
    I'm using Oracle 8.1.7 with Solaris 2.6. I am not able to select data using a db link. Heres what i've done - Using SQL client for WinNT.

    SQL> conn system/pw@srv
    connected.
    SQL> create databae link etslink connect to system
    identified by pw using 'ETS';

    SQL> /

    Database link created.

    SQL> select * from dba_tables@etsllink;
    select * from tab@etsvollink
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name


    I have both the DB settings set up in TNSNAMES.ORA and am not using SQLNET.ORA. Secondly, I'm able to ping to the other server as well. What could b the problem ? I can also connect to both the DBs without DB link.


    Cheers,
    Well-known problem :-) Here is the solution. I assume that the databses are called DB1 (on HOST1) and DB2 (on HOST1) and user is called USER and that you want to create a DB link from DB1 to DB2.

    CREATE DATABASE LINK "DB2.WORLD" CONNECT TO "USER" IDENTIFIED BY "passsword" USING '(DESCRIPTION = (ADDRESS_LIST = (
    ADDRESS = (PROTOCOL = TCP) (HOST = HOST1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB2)))';


  3. #3
    Join Date
    Jun 2001
    Posts
    87

    From the Oracle Manual

    Take a closer look @ your TNS Ora file


    ORA-12154: TNS:could not resolve service name
    Cause: The service name specified is not defined correctly in the TNSNAMES.ORA file.

    Action: Make the following checks and correct the error:
    - Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. See the operating system specific manual for details on the required name and location.
    - Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary.
    - Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. Any error in a TNSNAMES.ORA file makes it unusable. See Chapter 4 in the SQL*Net V2 Administrator's Guide. If possible, regenerate the configuration files using the Oracle Network Manager


  4. #4
    Join Date
    Oct 2000
    Posts
    467

    Worked..!!

    Thanx Julian..it did work. But i would like to know the reason. any inputs ?

    Cheers
    Vinit

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: From the Oracle Manual

    Originally posted by drain surgeon
    Take a closer look @ your TNS Ora file


    ORA-12154: TNS:could not resolve service name
    Cause: The service name specified is not defined correctly in the TNSNAMES.ORA file.

    Action: Make the following checks and correct the error:
    - Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. See the operating system specific manual for details on the required name and location.
    - Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary.
    - Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. Any error in a TNSNAMES.ORA file makes it unusable. See Chapter 4 in the SQL*Net V2 Administrator's Guide. If possible, regenerate the configuration files using the Oracle Network Manager

    Did you get that in Metalink? I have found at least a dozen of "BAD" problem solutions in Metalink :-)



  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Worked..!!

    Originally posted by vinit
    Thanx Julian..it did work. But i would like to know the reason. any inputs ?

    Cheers
    That's good! It took me a couple of hours to figure that out when I created 2 databases on an Oracle Parallel Server and I needed to create the DB links. I found nothing on Metalink (besides the wrong suggestions). Then I decided to try all posible that I could come up with.

    The reason: You need to have the right syntax :-)



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