We moved our test Oracle database to a new server and now some of our SQL Navigator users are getting ORA-12500 TNS Listener failed to start a dedicated server process when trying to connect to the database. Interestingly, other SQL Nav users can connect, however.
The Oracle client is correctly working because everyone can connect to the database using SQLPlus. It’s just SQL Navigator that is having a problem. Does anyone have any suggestions on how to get it to connect?
Thanks,
EllenR
Hi EllenR,
1.It could be some syntax issues in the listener.ora (on server side),
2.You can have a single listener, linking to multiple Oracle instances,
each on a different version
3.Have a look at the listener.log file, typically in your %ORACLE_HOME%\network\log directory on Windows or $ORACLE_HOME/network/log on Unix for more information or error messages, please send me, than i coul know more something about this.
4.Sometimes ORA-12500 is because of a PGA RAM shortage on the target database.
5.On WINDOWS server try :
netsh winsock reset catalog
6.Your instance could be configured in shared server mode, then check which oracle client is used to connection with sqlnav, users could have fev oracle clients, with different configuration of tnsnames, thats why sqlplus works.
Check in tnsnames that parameter:
SERVER = DEDICATED
is not present in description of your database (only if your server is configured as shared server). But remember in shared mode oracle has problems sending BLOBS via dblink…
Regards Piter
Hi EllenR,
Have you been able to resolve this issue guided by Piter’s suggestions??
We’re anxious to help you sort this out… so please update us on your progress.
Hi EllenR,
Did you check whether Windows service had started on those machines which failed to connect?
Rgds,
Andrew
Thanks for the follow up. Unfortunately, we’ve had mixed results.
The DBA made some changes on the database server. We are running four
databases in shared mode on one server and he brought the other
databases down, thinking it was using too much memory. Afterward, my
computer was able to use SQL Navigator to connect for a couple days,
although other users continue to get the error message. He brought the
other databases online one by one and I was still able to connect.
Then, just tonight, my computer suddenly started getting the “TNS
Listener failed to start a dedicated service” error again. I have
re-installed SQL Navigator but that didn’t make any difference. This is
really frustrating since we use SQL Navigator heavily and really need to
be able to connect to this test database.
Is it likely that the problem is a workstation issue rather than on the
server side?
Thanks,
EllenR
Hi EllenR,
Thank you very much for your update on this issue. SQL navigator should not cause any problem with Oracle Networking connections.
For Eg :
Assume that the below entry is your database entry in your tnsnames.ora file.
I102UT16.MELQUEST.DEV.MEL.AU.QSFT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = meldevitaw01.melquest.dev.mel.au.qsft)(PORT = 1522))
(CONNECT_DATA =
(SERVER = SHARED) This should be there.
(SERVICE_NAME = I102UT16)
)
)
If the above mentioned parameter is (SERVER=DEDICATED) then change it to SHARED on your client machine .This is the Oracle requirement. Other wise when you try to make a connection from your client machine, it requests for a dedicated connection and the listener refuses the connection, because the listener is running in shared server mode.
If the listener is running in shared server mode then make sure from your DBA that there is no limit for the number of concurrent connections. If there is a limit then the listener may not accept any more than the specific number of connections, until the established connections are served by database server.
Here I will explain in brief how shared server configuration differs from dedicated server process >>>>
A database server that is configured to allow many user processes to share few server processes. User processes connect to the dispatcher who directs them to a queue from which an idle shared server process picks a request. This implies that a few server processes can server many clients. Contrast this with dedicated server. The request and response queues and other parts that are found in the pga (Program Global Area) as dedicated server are found in the sga (System Global Area) when the server is a shared server. In a shared server environment, a session is not bound to a single server process, rather, multiple processes can serve the session, although there is never more than one process for a server.
Dispatchers: A process that enables many clients to connect to the same server without the need for a dedicated server process for each client. A dispatcher handles and directs multiple incoming network session request to shared server processes.
There are a number of potential issues with the shared server architecture that DBAs should be aware of before even considering implementing this feature.
Long Running SQL
First of all, the shared server architecture is particularly sensitive to inefficient SQL. Poorly tuned SQL statements can easily take many minutes or even hours to complete. This is a problem even with dedicated sessions, of course, but the issue is much more severe in a shared server environment. This is because by definition there are a finite number of shared servers available—as specified by the parameter max_shared_servers. Therefore, if frequently-executed SQL statements take a very long time to complete, then it is very easy to get into a situation where all of the shared servers are busy running those statements—and PMON is not able to spawn any more servers because max_shared_servers has been reached. This situation essentially results in a system-wide “hang” in which many user sessions are not able to do anything.
Shared Server Aborts
Another type of issue that can occur with shared servers is that in some cases a shared server process can completely abort. When this happens, an ORA-00600 [16365] error will be listed in the alert log and a trace file will be generated. PMON will then restart the aborted shared server process automatically.
This issue can occur when a shared server completes executing a SQL statement and tries to write the results of that statement to a virtual circuit—but that circuit has been updated (or “dirtied”) by a dispatcher. In other words the sequence of events is this: A shared server picks up a SQL statement from a virtual circuit and begins executing it. Then a dispatcher places a new message onto that same virtual circuit. When the shared server completes its SQL statement and tries to update the virtual circuit with the results of that statement, the problem occurs. More detailed information on this issue can be found in Metalink Doc ID 106607.1.
In any case, the bottom line on this issue is that the shared server architecture is designed to be used with “serial” applications; i.e., applications that force the user to wait until their current statement has completed before executing a subsequent statement. Therefore, applications that do not enforce such serial access certainly have the potential to encounter the shared server abort issue.
Service Handler Blocked Status
The final type of shared server issue in which all of the service handlers in an environment get into a “blocked” state. In a shared server environment, dispatchers “register” with a listener by establishing service handlers to that listener. Evidently, when the service handlers are under a relatively heavy load, those service handlers can sometimes get into a “blocked” state—such that those service handlers will not accept any new connections to the database. When that happens, users receive errors (usually ORA-12537) when they try to establish new connections to the database. Existing connections are apparently not affected, however. The issue generally seems to last for a few minutes at a time, before appearing to resolve itself.
The following information on this issue is from Oracle Support:
What these symptoms indicate, is the Service Handlers registered with the TNS Listener for the Oracle instance are going into a ‘blocked’ status based on the number service request handled since the last SERVICE_UPDATE from the instance’s PMON process.
This certainly appears to be a type of “threshold” issue; i.e. the service handlers stop accepting requests when the load on them reaches a certain level. As such, it may be possible to reconfigure the listener (or instance) to allow the service handlers to operate under a heavier load in order to increase this threshold.
Please confirm with your DBA that there are no issues with Shared serer configuration.
When ever you get a TNS Listener error, please type in tnsping dbname (service name) in command line and verify whether you get a response or not.IF you did not get a response then definelty some thing wrong with your lietener process.Either it need a reload or restart.
Hi,
One more thing with configuration, when you omit SERVER=… sentence in your tnsnames, you should be able to connect to your shared server. When your listener is configured in mixed mode shared/dedicated the default connection (wthout setting SERVER=… parameter) is shared.
Hi EllenR,
From the info you provided, we have done our investigation here and we are confident that this is NOT an issue with your work station/client environment or SQL Navigator, it is on the server side.
We have checked and discussed with our Oracle Domain expert in our lab, the issue would relate to the server running out of memory/resources, it explains why you could connect b4 then suddenly you got the error again. It would be best if your DBA could look at your test DB server environment to make sure that there would be enough memory/resources to handle the required number of connections…
Unfortunately there is little we can do from here to assist you with this issue .
Please keep us posted.
Thanks and regards,
Bruce