TNS error - ORA-12154

Printable View

08-23-2001, 05:48 AM
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,
08-23-2001, 06:24 AM
julian

Quote:

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)))';
08-23-2001, 06:28 AM
drain surgeon

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
08-23-2001, 06:35 AM
vinit

Worked..!!

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

Cheers
08-23-2001, 06:40 AM
julian

Re: From the Oracle Manual

Quote:

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 :-)
08-23-2001, 06:45 AM
julian

Re: Worked..!!

Quote:

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 :-)