ORA 12224 - TNS:  No Listener problem - Heterogeneous Connectivity

Hi there,
I have SQL*Plus working on client A (Sun Solaris, 9i), connecting to server C (Windows NT, 7.3.4)
From client B (Sun Solaris, 8.0.6), trying to connect to server C using the same TNSNAMES.ORA, same user & instance, I get the error :
ORA-12224: TNS:no Listener
This error, to me, means that the Oracle listener on server C is not working. Which is not possible, because it does work from client A !
Where did I go wrong ?
Could this mean that client B does not have network access to server C (client B is in DMZ, but should have all required accesses) ?
Thanks in advance !
Best regards,
Jerome 

It could be several things. The message simply indicates the listener did not respond in time.
If you are sure the listener is up on the server and that your TNSNAMES entry is correct, it is most likely a network problem. Make sure you can ping the machine to make sure it is getting through any firewalls. Then use tnsping to see if the descriptor is correct. 

Thank you Mark,
ping works fine, and tnsping as well...
Jerome 

Do you have a firewall between the server and the clients and are allowing only 1521 through? If so, read on. Oracle on NT by default uses a non shared socket impl which essentially means that even though the original request comes in on 1521, the server bequeaths the connection to a thread listening on a different port (a high port) and sends a redirect request back to the client. When the client attempts to connect to this neew port, the firewall blocks it. The way around this is
1. Get a firewall that recognizes SQLNet like checkpoint and allow all SQLNet traffic through
2. Open up all high ports (I can hear the sysadmins wanting to kill you)
3. Get Oracle to use SHARED_SOCKETS (if applicable to your platform/version. Update the registry to set SHARED_SOCKETS = true. This is a feature in Winsock2.0 that allows a socket to be shared through IO completion ports. (There is a great whitepaper on Metalink/Technet. Search for SHARED_SOCKETS)
HTH
Ram

Related

using dynamic port

hi guys Am  trying to do some tests a firewall between a client and oracle database.Note that I am not a DB expert, so apologies that I am not able to describe the Oracle elements correctly and my English skills. first thing to do is find out 'ORACLE Database 11g Enterprise edition' can use dynamic port.which means, initial server port is 1521, and the oracle server will dynamically allocate a different port for the connection to continue on.orclient connect with the server 1521 port and do something else using other port. so my problem is i am getting hard time server to do the dynamic allocation.My server is 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -64bit' and OS is Windows 7.if you guys needs some config files, please let me know. thanks for reading this and sorry about  my English skills.
you are correct in that the listener port is only used for the initial handshake between the client and the listener, and the listener then spawns another process using a 'random high' port, and tells the client to start communicating over that port.  At that point the listener is out of the picture.  If that random, high port is being blocked by the network firewall, then your network (outside of oracle's control) is mis-configured.  It's been years (decades) since I had to deal with that directly, and I'm no network expert, but I believe it has to do with configuring the firewall to allow connections that are initiated from the server.  I just did a cursory check of the Oracle docs and couldn't find anything on it,  but there are a couple of very network savvy regulars in this forum so hopefully one will weigh in.
3370775 wrote: hi guys Am trying to do some tests a firewall between a client and oracle database.Note that I am not a DB expert, so apologies that I am not able to describe the Oracle elements correctly and my English skills. first thing to do is find out 'ORACLE Database 11g Enterprise edition' can use dynamic port.which means, initial server port is 1521, and the oracle server will dynamically allocate a different port for the connection to continue on.orclient connect with the server 1521 port and do something else using other port. so my problem is i am getting hard time server to do the dynamic allocation.My server is 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -64bit' and OS is Windows 7.if you guys needs some config files, please let me know. thanks for reading this and sorry about my English skills.The Firewall must be configure to allow the DB Server to make a new connection request to any/every IP#
Am  trying to do some tests a firewall between a client and oracle database. first thing to do is find out 'ORACLE Database 11g Enterprise edition' can use dynamic port.which means, initial server port is 1521, and the oracle server will dynamically allocate a different port for the connection to continue on.or client connect with the server 1521 port and do something else using other port.It's possible to connect from your client to you database over the network without listener.ora. When you listener starts, your database will register with it.Dynamic relates to your listener. Without creating a listener.ora file, you can start your listener and your database will be dynamically register to it using the default port 1521.Any running database will try to register with the listener with default port 1521 automatically. If the listener.ora has a port different port that defers from the default 1521, then there won’t be dynamic registration. Instead it would be static registration.
Gbenga, you have done it again. You have produced an "answer " that bears no relationship to the question. It is not just spam, it adds confusion. I am not a moderator of this forum and (unlike some members) I try to avoid backseat moderation. However, I am going to report your post as an abuse of the forum.
thanks for your comment .if firewall is not block any port, then how can i configure random high port? 
3370775 wrote: thanks for your comment .if firewall is not block any port, then how can i configure random high port? there is NOTHING to configure.
i'm really sorry, i can't catch what you're saying for an example1. after connect with server by 'sqlplus' using listener(default 1521)2. when client communicate(create tables or insert data) using other port.     but after the connection with server, client stiil use 1521 port...... what can i do to client use other port?i'm really sorry to bother you. 
thanks for your commentbut i don't know how to configure or do something else to use 'random high port'. can you tell me how client use 'random high' port?
but i check that client use 1521 port after connection by WIRESHARK this is response packet(svr -> client)as you can see server use 1521 port.this is not connection packet.and requset use 1521 port too.
3370775 wrote: but i check that client use 1521 port after connection by WIRESHARK this is response packet(svr -> client)as you can see server use 1521 port.this is not connection packet.and requset use 1521 port too.You are wrong, mistaken & confused. You know enough to be dangerous but no where near competent.Client send connection request to listener via port 1521; which includes client IP#Listener sends via BEQ protocol data that includes client IP#The data base sends a responds packet to the client via port 1521 that tells client to continue packet exchange on port#XYZAll subsequent packet exchange between client & database  occurs using only port#XYZ
I did say that there are platform variations. Perhaps you have USE_SHARED_SOCKET enabled, which forces all communication to go through the listener port. That used to be common on Windows.
I am certain that on certain Linuxes the background processes were actually spawned by the listener because they inherited the listeners environment and not pmons. I don't think that's the case anymore but it's better to be cautious when making sweeping statements based on only a few observations. In my experience all database connections continue on the listener port itself. there is no additional high port required on the server side. this works because on Modern OSes multiple attributes including source ip and port destination ip and port and protocol are used for routing packets as any cursory check of the output of netstat should show you.On Linux You can could the number of remote connections by counting the number of sessions to 1521 if that's your listener port. . https://docs.oracle.com/database/121/LADBI/app_port.htm#LADBI7924 Oracle Net Services ListenerEnables Oracle client connections to the database over the Oracle Net Services protocol. You can configure it during installation. To reconfigure this port, use Net Configuration Assistant. Windows has always been an odd fish because it uses threads instead of processes. The documents for windows do tend to indicate that it will try to use multiple ports. 1521
Hi John,I think Gbenga was just trying to explain dynamic registration and static registration. I agree that is not the issue here  but Without knowing the history its you who sounds a little harsh on someone who has been misled by the term used by the original poster.Also kudos on the USE_SHARED_SOCKET parameter . It seems this is windows specific https://docs.oracle.com/database/121/NTQRF/ap_net.htm#NTQRF220 
robinsc wrote: I am certain that on certain Linuxes the background processes were actually spawned by the listener because they inherited the listeners environment and not pmons. I don't think that's the case anymore but it's better to be cautious when making sweeping statements based on only a few observations. In my experience all database connections continue on the listener port itself. there is no additional high port required on the server side. this works because on Modern OSes multiple attributes including source ip and port destination ip and port and protocol are used for routing packets as any cursory check of the output of netstat should show you.On Linux You can could the number of remote connections by counting the number of sessions to 1521 if that's your listener port. . https://docs.oracle.com/database/121/LADBI/app_port.htm#LADBI7924 Oracle Net Services ListenerEnables Oracle client connections to the database over the Oracle Net Services protocol. You can configure it during installation. To reconfigure this port, use Net Configuration Assistant. Windows has always been an odd fish because it uses threads instead of processes. The documents for windows do tend to indicate that it will try to use multiple ports. 1521You can be sure & wrong at the same time.I suggest that you make a couple of sessions & issue some SELECTS, then issue OS command belowlsnrctl stopNext continue to see that the previously connected sessions continue to be able to issue SQL even when the listener no longer exists.Explain how the sessions continue to use listener port when the listener has been shut down & no longer exists.

ODP.Net change notification

I'm using the nice change notification feature for middle-tier caching. It works fine in one environment, but not the other.
Working environment (A)
app server: Windows XP, with ODP.Net 2.111.6.20
Oracle: Windows Server 2003, with Oracle 11
Non-working environment (B)
app server: Windows Server 2003, with ODP.Net 2.111.6.20
Oracle: AIX with Oracle 10.2.0.3.0
The symptom is as follows:
1. The query is registered, from checking the registration table
2. The change notification is never received by the application.
3. The registration was removed after some change was made to the table that would cause change notification. (IsNotificationOnce is set to false.)
In the working environment A, the notification is received, and the query keeps registered until it's explicitly removed.
For A, I did have to drop the firewall to get it working. For B, I've configured the firewall so that the Oracle box can connect to the listening ports on the app server. (Cannot drop the firewall entirely due to corporate policy.)
My questions are:
1. Any suggestion as to how to narrow down the cause of the problem?
2. If it's the firewall setting, how should it be configured to get it working?
Any help is appreciated. 
Hi,
If the database tries 3 times unsuccessfully to reach the client, it will remove the registration. I'd guess there very well may be a firewall issue here. As far as opening your firewall, the port ODP uses for notification is random unless you specificy NotificationPort.
Hope it helps,
Greg 
Thanks for the help, Greg.
We did explicitly set the listening port on the app server, and opened that port in the firewall. TCP communication seems ok for that port, i.e., I used a test client/server, and the client was able to connect to that port and send/receive messages.
Any suggestions on what db trace etc. that I can look for more evidence/hint as to what went wrong? 
As a quick-simple-obvious test, are you able to ping the client machine from the db machine?
The only real suggestion I have at this point is to get your network guys involved and check the network traffic at a packet level to see if the db is having trouble reaching the client. There are free 3rd party tools like Wireshark that can help with that.
Hope it helps,
Greg 
Ok, thanks!

Security Question

Hi Experts,
I have a Question based on ports, recently the network administrator has implemented security policies which is stopping my users to connect to TOAD or local SQL*PLUS when connected from VPN.
My database is 9i and resides on a Unix Box. The only way i can connect is from internal SQL*PLUS from Unix.
Any input or ideas will be appreciated.
Thanks ! 
Am sorry i posted it twice, had a problem with network....Kindly please ignore the other one.
Thanks 
Talk to the Network admin? 
:) .....I already did, earlier he had allowed ANY traffic to pass by FIREWALL, he needs specific port to be open to implement & secure his network from intruders ,,,,,I believe there is no other than LISTENER port which needs to be open? Is it so ....
Thanks 
Yes, only listener port is required to be opened and you can get this port from listener.ora file
Regards 
The most probable reason why you are not getting connected to the network is because for a firewall is not enough just to open the listener port, as this port is one way, once the listener is contacted it spawns and bequeaths connection, the spawned server process is the one which will handle connection from this moment on and the port you will be using will be a port different from 1521 (or whatever the listener is attached to).
Most probably due to recent security enforcement the ports required to perform the call back to your client process were closed and the effect is that you will see a connection from the client to the server but you never get back an answer.
I suggest you to:
# Identify the port range used and request your network administrator to open a port range
# Assuming your server is a Windows machine, you could try setting USE_SHARED_SOCKET = TRUE in the Registry, under \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME<#>, or set it as environment variable. Both methods require a shutdown/startup of the server. If you have access to Metalink, see Note:124140.1
# Consider the use of Connection manager and this way you would only open the cman port and it would take care to handle listener connections.
~ Madrid 
Thanks Madrid for detailed reply.....
Whenever i try to connect, it says TNS connection timed out which most probably is the reason that LISTENER can't respond to initiated connection by client.
I'll ask the Network Administrator to open the ports and will test them by using a VPN Client from outside the network.
Am having a Unix server, Do i need to a restart for Unix server as well ? Or it only applies for Windows as specified in your post?
Your thoughts please. 
This applies for the windows platform where the listener resides. On the test you perform you should be able to connect as before. But your ports won't remain open, so you should consider any of the previous suggestions.
~ Madrid 
the spawned server
process is the one which will handle connection from
this moment on and the port you will be using will be
a port different from 1521 (or whatever the listener
is attached to).Only platform I know that this applies to is Windows (use of random server port numbers). 
I thank you all for your kind support.
The issue was verified & fixed by the network administrator after adding the DB ports in ACL's.
Regards,
~Pointer~

TNSPING falied from client but TELNET to the DB port is available

Hi,
My ORACLE DB (oralce 11g R2 - ruinning in windows2003 Server OS ) and Application ( Windows 2003 server OS ) are running in different machies. Between these machines, i have a firewall. I have modifed the firewall to access the 1521 port of DB machine from application machine. DB is UP and running in 1521 port of DB_Machine. I have installed oracle_11g client in application machine. I am able to telnet to DB_Machine's 1521 port from application machine. But, tnsping to DB machine is not connecting. It is showing the following error:
ORA-12170: TNS:Connect timeout occurred
So, i tried to to connect using sqldeveloper..It is also getting timed out
Please help....
Regards,
jibu 
Jibu  wrote:
Hi,
My ORACLE DB (oralce 11g R2 - ruinning in windows2003 Server OS ) and Application ( Windows 2003 server OS ) are running in different machies. Between these machines, i have a firewall. I have modifed the firewall to access the 1521 port of DB machine from application machine. DB is UP and running in 1521 port of DB_Machine. I have installed oracle_11g client in application machine. I am able to telnet to DB_Machine's 1521 port from application machine. But, tnsping to DB machine is not connecting. It is showing the following error:
ORA-12170: TNS:Connect timeout occurred
So, i tried to to connect using sqldeveloper..It is also getting timed out
Please help....
Regards,
jibuFirewall is blocking new session from DB Server to Application Server on "random" high ports 
sorry..I didn't understand...
My belief is that if 1521 port is open in firewall, then we should get connection from application server rt....I have other application accessing Oracle DB across this same firewall....without any issues...
Can you please explain a bit more....RANDOM hight ports... 
Jibu  wrote:
sorry..I didn't understand...
My belief is that if 1521 port is open in firewall, then we should get connection from application server rt....
I have other application accessing Oracle DB across this same firewall....without any issues...then set same rules for this DB as exist for one that works.
This is error of omission. 
HIHI....
rules are set in same way only.... :)
Regards,
Jibu 
rules are set in same way only... 
Jibu  wrote:
rules are set in same way only...the ERROR indicates otherwise.
same rules produces same results.
since you see different results; therefore something is DIFFERENT.
Firewall lacks rule that allows DB to start new packet exchange with application server. 
Jibu  wrote:
sorry..I didn't understand...
My belief is that if 1521 port is open in firewall, then we should get connection from application server rt....I have other application accessing Oracle DB across this same firewall....without any issues...
Can you please explain a bit more....RANDOM hight ports...1521 is just the port that is used to contact the listener. Once the listener connection request is verified, the listener spawns a seperate server process to service the client, and that communication (between the client and this new server process) occurrs on a 'random high port' .... not on 1521.
And, btw, once the listener hands he connection off to this server process, it (the listener) is no longer involved in the communication between the client and server. The listener can be shut down and all exising connections continue. 
To over come this, i have set USE_SHARED_SOCKET = TRUE registry.....that is enough rt ?
Regards,
Jibu 
The workaround here, suggested in Oracle Metalink Note 125021.1, is a WINSOCK V2 API feature called Shared Sockets. This feature allows a socket to be shared among multiple processes.
To use this functionality in a single Oracle Home enviroment, set USE_SHARED_SOCKET=TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of the registry. Noticeably, as WINSOCK V2 allows a socket to be shared between multiple processes, the listener cannot be restarted without shutting down the database first.
A downfall of this solution is all connetions will stay on the listener port. If the listener is stopped or restarted all the connections will be severed from the database. Furthermore, USE_SHARED_SOCKET could be a performance bottleneck with multiple connections to the database. Please use it deliverately if many simultaneous connections to the database are involved.

TNS Timed out error when connecting from internet

All,
Apologies if I am missing something simple...
I have set up a test 9i database on a Win 2003 Server, and I can connect to it fine from within my LAN. I am trying to connect to the same database from the internet, but am getting a TNS:Operation timed out error.
I believe I have set everything up correctly:
-Port Forwarding port 1521 on my simple Linksys router/firewall
-TNSPing gives me a sub-second OK (from the internet)
-I can get a TNS:listener could not resolve SERVICE_NAME error if I purposely screw up the service name in the tnsnames.ora file (proving, I think, that I can connect to the Listener from the outside internet)
Am I missing something simple? Why can I ping the listener but not get a response when trying to connect to the database (database is working, I can connect from within my LAN)?
Do I need to download and install the latest release for Win Server 2003?
Thanks,
Chris 
Well it is not so simple.
After the connection to listener the client must connect with a server or a dispatcher and in these two cases another port is used.
A solution could be found using connection manager product from Oracle but then your database must be in MTS configuration (shared servers).
Thanks for your response, Michael..
Is this something new in 9i? The reason I ask is that I swear that while working on another project last year I was able to set up a development 8i database in my home office and was able to allow my client hit this database from their own location (in Peru, via the internet) using a vb.net app that I had built. The only thing I did on my home router was to forward port 1521.
If my memory is wrong or things have changed since then, then what other ports other than 1521 are used to connect? Security is not really an issue, as I am just trying to set up a test database in order to Q/A an application I'm building.
Thanks in Advance,
Chris 
Yes but then doesn't it hand off to another random port ? after connection initialization.
You might want to download Ethereal off the web and have a look at the tcp traffic.
Otherwise - Metalink Note 125021.1 is what you want to look at.
. try opening the firewall and then trying.

Categories

Resources