ORA-12154: TNS:could not resolve service name-Please Help

Hi ,
I am getting a ORA_12154 Listener cannot resolve service name when I try to connect using sql plus.

I have 2 versions of Oracle (8.0.5 and 8.1.7) installed on a windows 2000 machine under different oracle home
The Oracle Home are

Oracle 8.0.5
----------------
Ora805-Home
c:\Ora805 - directory

Oracle 8.1.7
--------------
OracleOra81-Home
c:\Oracle\Ora81 - Directory

Below is the error.Please note that I am able to connect to the Oracle 8.0.5 Database.

SQL> connect scott/tiger@orcl
Connected.

but with oracle 8i database i get the error

SQL> connect scott/tiger@orcl8i
ERROR:
ORA-12154: TNS:could not resolve service name

Below are the tnsnames and listener files for both the databases.

TNSNAMES.ORA for Oracle 8i Database
--------------------------------------------
ORCL8I.HEROLD.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1400))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl8i)
)
)

listener.ora for Oracle 8i Database
----------------------------------------
LISTENER3 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1400))
)
)
)

SID_LIST_LISTENER3 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Orcl8i)
(ORACLE_HOME = c:\oracle\ora81)
(SID_NAME = ORCL8i)
)
)

TNSNAMES.ORA for Oracle 8 database
------------------------------------------
ORCL.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))
)

Listener.Ora for Oracle 8.0.5 database
--------------------------------------------
LISTENER =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1521))


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = aminocha)
(SID_NAME = ORCL)
)
(SID_DESC =
(SID_NAME = extproc)
(PROGRAM = extproc)
)
)

Also note that i am able to do a tnsping on both the databases as seen below


C:\>tnsping80 orcl

TNS Ping Utility for 32-bit Windows: Version 8.0.5.0.0 - Production on 27-APR-01
18:09:09

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=aminocha)(PORT=1521))
OK (60 msec)

C:\>tnsping orcl8i

TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on 27-APR-20
01 18:09:15

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=aminocha)(PORT=1400))
OK (50 msec)


Please Help.

Thanks
Anurag

You can only run 1 listener at a time.
Copy
ORCL8I.HEROLD.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1400))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl8i)
)
)

paste the above entry in tnsnames.ora located in Oracle home (8.0.5) and try connecting using Oracle 8.0.5 sqlplus using connect string name = orcl81 if this does not work then
enter orcl81.herold.com.

Try to create a service from net 8 to oracle 8i.
service name =orcl8i
protocol=TCP
HOST = aminocha
PORT = 1400
sid=orcl8i

It makes you easy. Good Luck

You don't need to mess with your tnsnames files. at the command prompt:

set oracle_sid = orcl8i
set oracle_home=c:\oracle\ora81
c:
cd \
cd oracle\ora81
sqlplus scott/tiger@orcl8i

http://www.dbasupport.com/forums/sho...?threadid=9944

Hi,

I tried set up the Oracle_SID and Oracle Home from the dos prompt and now it connects to Orcl8i. But it no more connects to orcl.

I want the ability to connect to orcl or orcl8i on the fly by spedcifying the @servicename and not change the environment every time.

Also as Mzahid said in this post that you can run only one listener at a time. Is it correct. I think that we you can run more than one listener at a time. Please confirm.

Thanks
Anurag

Also after setting the SID and Home from the dos prompt i am unabme to log in using sqlplusw. It only allows me to login through sqlplus.

Thanks
Anurag

you can run several listeners, the only point is that they must run on different ports.
for your problem, I suggest using only one listener, which listens for both your instances ... take the 8i one and configure the listener.ora file so that this listener knows your 2 instances

I disabled all the listeners in the services and stopped Listener3.
Then I changed the listener file under Oracle Home and this is how it looks after the change

LISTENER3 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1400))
(ADDRESS = (PROTOCOL = TCP)(HOST = aminocha)(PORT = 1521))
)
)
)


SID_LIST_LISTENER3 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Orcl8i)
(ORACLE_HOME = c:\oracle\ora81)
(SID_NAME = ORCL8i)
)
(SID_DESC =
(GLOBAL_DBNAME = aminocha)
(ORACLE_HOME = c:\ora805)
(SID_NAME = ORCL)
)

)


As you can see that now it points to both the instances.
I started the listener using the following command

C:\>lsnrctl start listener3

LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 - Production on 30-APR-2001 10:23:
16

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Production
System parameter file is C:\oracle\ora81\network\admin\listener.ora
Log messages written to C:\oracle\ora81\network\log\listener3.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aminocha.herold.com)(POR
T=1400)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aminocha.herold.com)(POR
T=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aminocha)(PORT=1400)))
STATUS of the LISTENER
------------------------
Alias listener3
Version TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produc
tion
Start Date 30-APR-2001 10:23:20
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:\oracle\ora81\network\admin\listener.ora
Listener Log File C:\oracle\ora81\network\log\listener3.log
Services Summary...
ORCL8i has 1 service handler(s)
ORCL has 1 service handler(s)
The command completed successfully


But still it does not allow me to connect to the orcl8i database using sqlplusw.

Thanks
Anurag



[Edited by anuragmin on 04-30-2001 at 10:30 AM]

Thanks a lot every body. Its working now. The reason I was not able to connect was because i was using plus80 packaged with Oracle 805 to connect to Oracle 8i database. If I use the exe's with the respective versions it connects.

Thanks again
Anurag

I was wondering why cannot i use the sqlplus under Oracle 8i Home to connect to Oracle 805 Home as mentioned above.

How does it matter?

Thanks
Anurag