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

[anuragmin]

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

[mzahid]

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.

[sree_sri]

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

[kmesser]

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

[kmesser]

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

[anuragmin]

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

[anuragmin]

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

[pipo]

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

[anuragmin]

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]

[anuragmin]

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