Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Connection problems with TNS Listener
Hi There, I have set up a RH machine running oracle 8.1.7. This machine has three instances setup on it. Directly from the RH machine itself I have no problems with these instances. I can connect to them after setting the ORACLE_SID from both server manager and also SQLPlus. However, I am unable to connect to these instances from outside of the Linux box.
As a result I started having a look at the TNSListener setup. I recreated the listener from the Oracle Network Configuration Assistant by running netca. This created and started the listener ok.
>lsnrctl status
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)))
STATUS of the LISTENER
Alias LISTENER Version TNSLSNR for Linux: Version 8.1.7.0.0 -Production
Start Date 10-SEP-2003 10:02:00 Uptime 0 days 0 hr. 6 min. 50 sec Trace Level off Security OFF SNMP OFF
Listener Parameter File
/sda1/oracle/product/8.1.7/network/admin/listener.ora
Listener Log File
/sda1/oracle/product/8.1.7/network/log/listener.log
Services Summary...
PLSExtProc has 1 service handler(s)
The command completed successfully
I then set the SID, by typing ORACLE_SID=TEMPUS1; export ORACLE_SID and start the database via server manager. This starts up correctly. If I then look at the status of the listener again, I notice that there is no service handler created for the Tempus1 database instance that I have just started up. It is the same as outlined above still.
As a result I stop the listener and then restarted it. The log file looks as follows:
TNSLSNR for Linux: Version 8.1.7.0.0 - Production on 10-SEP-2003 10:02:00
(c) Copyright 1998 Oracle Corporation. All rights reserved.
System parameter file is
/sda1/oracle/product/8.1.7/network/admin/listener.ora
Log messages written to
/sda1/oracle/product/8.1.7/network/log/listener.log
Trace information written to
/sda1/oracle/product/8.1.7/network/trace/listener.trc
Trace level is currently 0
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE 10-SEP-2003 10:02:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=tempus)(USER=- oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=135294- 976)) * status * 0
10-SEP-2003 10:08:51 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=tempus)(USER=- oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=135294- 976)) * status * 0
One thing that I have noticed in this log file is the first declaration of HOST=0.0.0.0. I am unsure why this is so? I have looked in our listener.log file for our windows server and it declares it as the correct host. From linux:
#hostname
tempus
#ifconfig
inet addr: 10.0.0.13
So I am not sure why it is seeing the host in this instance as 0.0.0.0? Next I looked at the listner.ora file:
# LISTENER.ORA Network Configuration File: # /sda1/oracle/product/8.1.7/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)) )
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
)
)
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc)
(ORACLE_HOME = /sda1/oracle/product/8.1.7)
(PROGRAM = extproc)
)
)
I have tried putting is another entry in the SID_LIST_LISTENER for the Tempus1 instance, and while this upon restart of the listener creates a handle for it, I was still unable to connect to this database via external connections. As a result I took the entry out for in previous multiple instance setups (in windows, this is first attempt at Linux) I have not had to do this step.
The TNSnames entry looks as follows:
# TNSNAMES.ORA Network Configuration File: # /sda1/oracle/product/8.1.7/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEMPUS2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tempus)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = TEMPUS2.WORLD) )
)
TEMPUS1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tempus)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = TEMPUS1.WORLD) )
)
EXTPROC_CONNECTION_DATA.LOCALDOMAIN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(CONNECT_DATA = (SID = PLSExtProc)
(PRESENTATION = RO) )
)
The error that I am getting when I try and connect externally is as follows:
From cammand prompt (before manually adding a TNSname entry)
tnsping tempus1
tns-03505: failed to resolve name
From cammand prompt (after manually adding a TNSname entry)
TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on 10-SEP-20
03 11:33:47
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=TEMPUS)(PORT=1521))
OK (30 msec)
Z:\>TNSPING tempus1
TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on 10-SEP-20
03 11:33:50
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=TEMPUS)(PORT=1521))
OK (20 msec)
If I add the TNSname entry via Net8 configuration assistant, it will give the error:
Connecting...ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
The test did not succeed.
However, if you select finish so the entry is saved to the tnsnames.ora file, then do a tnsping, you can ping the instance (as outlined above). However you still cannot physically connect to the instance via SQLPlus etc..
SQLPlus:
ORA-12514: TNS: Listener could not resolve SERVICE_NAME given in connect descriptor.
Can anyone offer any advice on what might be wrong, or what other information I can provide to help with this?
Regards
Dan.
-- Posted via http://dbforums.comReceived on Tue Sep 09 2003 - 20:36:02 CDT