-
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
sree
-
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
-
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|