Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bobby.

Asked: February 24, 2002 - 5:29 pm UTC

Last updated: February 25, 2011 - 8:42 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked


Can either or both of these error conditions be generated by sending the TNS listener too many queries, too fast? If so, how fast is too fast?

ORA-12500 TNS:listener failed to start a dedicated server process

ORA 00955 name is already used by an existing object



and Tom said...

Well, in reverse order -- starting with ora-00955:

Error: ORA 955
Text: <name> is already used by an existing object
-------------------------------------------------------------------------------
Cause: An attempt was made to create a database object (such as a table, view,
cluster, index, or synonym) that already exists.
A user's database objects must have distinct names.
Action: Enter a unique name for the database object or modify or drop the
existing object so it can be reused.

that one cannot be caused by hitting the listener, thats a database error after you are already connected.

The ORA-12500 -- it could be caused by hitting the listener too fast, faster then the OPERATING system itself can deal with. If you are using dedicated server configurations on unix for example, each connection is a unix fork/exec. If you overload the OS, it won't be able to spawn any new processes. Its not really that you hit the LISTENER too fast in this case, more that the OS is physically out of resources or is taking so long to get these resources in place that we time out. How fast is too fast -- thats a function of your OS, the amount of ram, how many cpu's you have, how fast they are, what else is going on on the box, etc etc etc...

Rating

  (63 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

listener hangs

Amit Agarwal, February 24, 2002 - 11:55 pm UTC

we have similar problem but on earlier version of Oracle & Sql Net.So the solution you have given above might not help us.

We are working on Oracle 7.3.4 with SCO-Unix with SQL Net v2.
FOr the past 3-4 days, we are infilcted with severe listener problem. Listener is always hanging in the middle of session.On reading the listener.log, it gives following error.

TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
SCO System V/386 Error: 90: Operation would block

Then we have to kill the listener process from OS and again restart it.we shutdown the database every evening but not listener.

Can we have multiple listeners in SQL net V2??can we set Queuesize in this verson of listener.How to change queue size for Sco-unix. I think default is 32 which is too low.

Please tell us the solution.

OS is overloaded and why not our "Database"?

Yogeeraj, February 25, 2002 - 1:01 am UTC

Hello,

OS is overloaded means:
- Memory available is too LOW
- CPU usage is on Average at 100%

In this case, couldn't it be that our Parameters at the Database level is not properly set:
E.g.
mts_dispatchers = "(protocol=TCP)(disp=20)(mul=ON)(con=30)"
mts_max_dispatchers = 30
mts_servers = 20
mts_max_servers = 25

Are we still concerned about the kernel parameters that we changed as specified in the Pre-installation guide?

Is it not better to start tuning at the Database level first?

Regards
Yogeeraj

Tom Kyte
February 25, 2002 - 8:14 am UTC

Well, 20 dispatchers for 20 shared servers is a little overkill. You need a dispatcher for every 1000 or so users. With 20 shared servers, you probably need 1 dispatcher.

If your memory or cpu is exhausted you have two choices

a) figure out why, change your application
b) buy more memory / cpu

without tons more info -- cannot really tell you what to do. You are getting the 12500 because the OS is out of resources.

TNS errors

mak, February 25, 2002 - 7:16 am UTC

what do TNS errors really mean?

Tom Kyte
February 25, 2002 - 8:20 am UTC

they are simply errors from the networking layer.

comes from Dedicated or MTS ?

Eva, April 13, 2003 - 8:24 pm UTC

Dear Tom,

You said TNS-12500 caused OS is overload. So, can TNS-12500 message comes from MTS configuration or only from Dedicated Server ?

How to detected, which OS component is overload ? Memory, Processor or trafic network ?


Tom Kyte
April 13, 2003 - 8:59 pm UTC

If we needed to start up another shared server and we couldn't - that would cause it as well.

you would use OS tools to see which OS component was overwhelmed. Varies by OS.

Multiple Listener

Eva, April 14, 2003 - 8:33 pm UTC

Dear Tom and thanks for your advice..

just to acertain, TNS-12500 can happen on Dedicated server and Shared server (MTS) configuration, if we have OS overload. is it right ?

so, if I have small concurent user (100-200 user) in dedicated server mode. Can I avoid TNS-12500 using multiple listener ? or I create multiple listener on the other machine ?

My database running on windows platform.

please give me your solution...

best regards

Tom Kyte
April 14, 2003 - 8:38 pm UTC

Odds are -- if you are GETTING an ora-12500 it means your system is way over taxed.

Starting another listener would be akin to placing another straw on the camels back (and thus breaking it)...

Do you have processes set correctly. Have you poked about in the logs to see what the underlying issue is exactly.


[tkyte@tkyte-pc-isdn Desktop]$ oerr ora 12500
12500, 00000, "TNS:listener failed to start a dedicated server process"
// *Cause: The process of starting up a dedicated server process failed.
// The executable could not be found or the environment may be set up
// incorrectly.
// *Action: Turn on tracing at the ADMIN level and reexecute the operation.

// Verify that the ORACLE Server executable is present and has execute
// permissions enabled. Ensure that the ORACLE environment is specified
// correctly in LISTENER.ORA. The Oracle Protocol Adapter that is being
// called may not be installed on the local hard drive. Please check that
// the correct Protocol Adapter are successfully linked.
// If error persists, contact Oracle Customer Support.



Turn on tracing at the ADMIN level ?

reader, April 15, 2003 - 4:52 am UTC

tom,

can you please explain me how to trun on tracing at admin level?

is there any different levels of tracing - if so i would like to hear that from you.

thanks as always !

TNS-12535

Pichai Bala, January 07, 2004 - 2:24 pm UTC

Hi Tom,
We are intermittently getting TNS-12535 error say for one of 10 and the log file has grown to nearly 900 M. Even stopping and restarting the listener is not helpful. So now we added a second listener at a different port and running. How can we solve this issue?
Wish you a wonderful New Year.

Thanks
Pichai

Tom Kyte
January 08, 2004 - 11:07 am UTC

well, you can disable the logging (that'll help. do you really need a gigabyte of stuff you probably do not use)

You can set the connect timeout LONGER or disable it all together, but it sounds like an overloaded machine.

TNS-12535

Pichai Bala, January 07, 2004 - 2:24 pm UTC

Hi Tom,
We are intermittently getting TNS-12535 error say for one of 10 and the log file has grown to nearly 900 M. Even stopping and restarting the listener is not helpful. So now we added a second listener at a different port and running. How can we solve this issue?
Wish you a wonderful New Year.

Thanks
Pichai

9i R2 Listener Over Logging

Rahul Sharma, May 07, 2004 - 12:02 am UTC

Hi Tom

I have installed oracle9i R2 with default installation/configuration. No change in any parameter.
In normal scenario only 20-25 users connected simultaneously through application, but my Listener.log file is written blindly. On average in 3 seconds its written by 3bytes. Now the size of Listener.log is around 2Gig.

What is happening inside and what should I do?

Thanks.

Tom Kyte
May 07, 2004 - 7:31 am UTC

if you are speaking of the tns listener, just turn off listener tracing that must be turned on. edit your listener.ora and turn it off.

if you have only 20/25 users and the listener log is growing fast - you must be connecting/disconnecting/connecting/disconnecting rapidly -- that'll be "slow", you might look to change that somehow as well.

Getting TNS:12500

Rahul Sharma, June 11, 2004 - 2:47 am UTC

Hi Tom

I am working on oracle9iR2 on SunOS 5.8 with 1GB of RAM.
We are making connections thru application and that is around 100 connections in database and we have PROCESSES =150 in spfile.
But today somehow we got the "ORA-12500: TNS:listener failed to start a dedicated server process" problem and that is INTERMITTENT problem, some time we get connected to db and sometime do not.
I checked the database is running properly and listener is also fine. I restarted the listener but it didn't work and then i had to shutdown the database and now it's working fine.

What is the actual cause and possible solution for this problem?
Please suggest.

Regards
Rahul.


Tom Kyte
June 11, 2004 - 3:43 pm UTC

sounds like this server is getting a tad overloaded. how big is the sga and what is your pga usage?

if this is intermittent, sounds like you are just hitting the edge of what this machine can handle.

SGA and PGA

Rahul, June 14, 2004 - 3:51 am UTC

Hi Tom 

Below is the information you asked for.

SQL> show sga

Total System Global Area  420971752 bytes
Fixed Size                   730344 bytes
Variable Size             251658240 bytes
Database Buffers          167772160 bytes
Redo Buffers                 811008 bytes
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 524288000
SQL>

How can i check the memory usage of my OS ?
How can i check that my OS is overloaded ?

Regards
 

Tom Kyte
June 14, 2004 - 7:54 am UTC

you would ask your system administrator to monitor the OS but....

you have allowed for 400meg of permanently allocated memory (SGA) and 500meg of PGA memory, on a 1 gig machine, you are "pushing it".

especially if this is a 1 to 2 cpu older machine, you are pushing the edge of that machine memory and probably cpu wise.



NNC-00406

Yogesh, July 23, 2004 - 5:06 am UTC

In listener log I'm repeatedly getting NNC-00406 error ... When I saw the explanation about this error .. it is talking about names / global names ... can you please explain what does it mean ?

Following is a part of .log file

System parameter file is /u01/app/oracle/product/8.0.4.0.0/network/admin/listenr.ora
Log messages written to /u01/app/oracle/product/8.0.4.0.0/network/log/listener.log

Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=6)(KEY=DEV1))
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=10)(KEY=devcon))
Listening on: (ADDRESS=(PROTOCOL=tcp)(DEV=11)(HOST=some IP)(PORT=1521))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

22-JUL-04 14:05:49 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=testbox)(USER=dev1))(
COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=134234112)) * status * 46

NNC-00406: name "LISTENER_testbox" does not exist
NNC-00406: name "extproc" does not exist
NNC-00406: name "TESTBOX" does not exist
NNC-00406: name "TESTBOX" does not exist
NNC-00406: name "TESTBOX" does not exist

22-JUL-04 14:05:59 * (CONNECT_DATA=(SID=DB1)(CID=(PROGRAM=)(HOST=TESTBOX)(USER
=dev1))) * (ADDRESS=(PROTOCOL=ipc)(KEY=DEV1)) * establish * DB1 * 406
NNC-00406: name "LISTENER_testbox" does not exist
NNC-00406: name "extproc" does not exist
NNC-00406: name "TESTBOX" does not exist
NNC-00406: name "TESTBOX" does not exist
NNC-00406: name "TESTBOX" does not exist


Listener.ora

LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= DEV1))
(ADDRESS= (PROTOCOL= IPC)(KEY= devcon))
(ADDRESS= (PROTOCOL= TCP)(Host= testbox)(Port= 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/app/oracle/product/8.0.4.0.0)
(SID_NAME = DEV1)
(GLOBAL_DBNAME = testbox)
)
(SID_DESC =
(ORACLE_HOME= /u01/app/oracle/product/8.0.4.0.0)
(SID_NAME = DB1)
(GLOBAL_DBNAME = testbox)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /u01/app/oracle/product/8.0.4.0.0)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME= /u01/app/oracle/product/8.0.4.0.0)
(SID_NAME = NOLOG)
(GLOBAL_DBNAME = testbox)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
USE_PLUG_AND_PLAY_LISTENER = ON

Tom Kyte
July 23, 2004 - 8:40 am UTC

see support <Note:1075881.6>

Yogesh, July 30, 2004 - 12:43 pm UTC

Got read of NNC-00406 error, but now my log file is full of following messages .. there is no process running which is trying to connect to database ... still these messages are added for each min.

30-JUL-04 17:54:15 * (CONNECT_DATA=(SID=CATDB)(CID=(PROGRAM=)(HOST=mit)(USER
=u1))) * (ADDRESS=(PROTOCOL=tcp)(HOST=145.224.216.48)(PORT=50934)) * establish * CATDB * 0
30-JUL-04 17:54:15 * (CONNECT_DATA=(SID=CATDB)(CID=(PROGRAM=)(HOST=mit)(USER
=u1))) * (ADDRESS=(PROTOCOL=tcp)(HOST=145.224.216.48)(PORT=50935)) * establish * CATDB * 0

How do I check from hwere these messages are coming ? I'm on AIX orcle 8.0.4


Tom Kyte
July 30, 2004 - 5:40 pm UTC

turn off the plug and play listener -- that is the crux of that note.

plug and play listener

Yogesh, August 03, 2004 - 10:45 am UTC

even after removing the plug and play listener entry, I can see the messages in listener.log.

Tom Kyte
August 03, 2004 - 11:06 am UTC

I'll have to refer you to support at this time, I've not observed this myself. They can walk through the configuration files and ensure you have them setup properly.

Why does it fails?

rams, August 23, 2004 - 10:49 am UTC

Hi Tom,
I have been trying to create a new procedure to create synonyms

PROCEDURE create_synonym(username IN VARCHAR2)
IS
CURSOR c1 IS SELECT table_name FROM dba_tables WHERE owner='BVUSER' AND table_name LIKE 'BV%'
OR table_name LIKE 'IR%';
mesg VARCHAR2(255);
new_line VARCHAR2(255);
--addr VARCHAR2(255);

BEGIN

new_line := CHR(13) || CHR(10);
FOR i IN c1 LOOP
EXECUTE IMMEDIATE 'create synonym bvuser.'||i.table_name ||' for irctc.'||i.table_name;
--Execute_Immediate(mesg);
--dbms_output.put_line('create synonym bvuser.'||i.table_name ||' for irctc.'||i.table_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,
'Failed TO CREATE SYNONYM due TO the following error: ' || SQLERRM);
END;
/


But i am getting this error.

*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "BVUSER.CREATE_SYNONYM", line 13
ORA-06512: at line 1


In stead of the execute immedite i gave the dbms_output.put_line and the procedure executes properly.
I tried using dbms_sql package to execute the statements but even then it didn't work out.
Oracle is giving misleading errors, could you help me out.


Thanks rams

Tom Kyte
August 23, 2004 - 10:53 am UTC

sorry-- but that error is not coming from that procedure.  you have a bad "when others" in there.

Your procedure could see:

ops$tkyte@ORA9IR2> exec create_synonym(user);
BEGIN create_synonym(user); END;
 
*
ERROR at line 1:
ORA-20000: Failed TO CREATE SYNONYM due TO the following error: ORA-00955:
name is already used by an existing object
ORA-06512: at "OPS$TKYTE.CREATE_SYNONYM", line 16
ORA-06512: at line 1
 



the it should be obvious why?  you either ran it twice OR you already had some object by that name -- the error message seems sort of "clear" here??? 

thank you

rams, August 23, 2004 - 11:49 am UTC

Hi Tom,
thanks for your quick responce. Actually i was looking for the error in the procedure while the error was wit the create synonym statement for the synonym was already in place.

But about after stopping service

totu, February 22, 2005 - 6:26 am UTC

Dear Tom.
I use Win2000 and Oracle 9iR2. I have 4 database. I stop one of it's service(I mean from services panel of windows) and the using SQL Plus:
SQL> conn sys@testdb1 as sysdba
Enter password:
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process

Why?

Thanks in advance. 

Tom Kyte
February 22, 2005 - 8:42 am UTC

kyte@localhost tkyte]$ oerr ora 12500
12500, 00000, "TNS:listener failed to start a dedicated server process"
// *Cause: The process of starting up a dedicated server process failed.
// The executable could not be found or the environment may be set up
// incorrectly.
// *Action: Turn on tracing at the ADMIN level and reexecute the operation.
// Verify that the ORACLE Server executable is present and has execute
// permissions enabled. Ensure that the ORACLE environment is specified
// correctly in LISTENER.ORA. The Oracle Protocol Adapter that is being
// called may not be installed on the local hard drive. Please check that
// the correct Protocol Adapter are successfully linked.
// If error persists, contact Oracle Customer Support.


sounds like testdb1 is not configured correctly as a tnsnames.ora entry....

or, if the windows service isn't running -- you will get absolutely no where, the SERVICE must be running or there just isn't "anything there"

windows is not like unix at all, on unix nothing special needs to be done to startup a database. On windows you must have that service going first, no way around it.

A reader, March 27, 2005 - 1:37 pm UTC

Hi Tom,

I am facing the following problem. I have a 9i Rel 2 database where the TNS listener service is running. Yet when I open up Net Configuration Assistant->Listener Configuration the only option I have is Add. (No reconfigure or delete). There are no errors in listener.log. Why is this the case? How would you go about tracing the source of the problem.

Pls help.


Tom Kyte
March 27, 2005 - 3:19 pm UTC

could be that whomever installed your system decided to put the files into a non-standard location. did you do the install? do you know where the listener.ora is? you say "service", is this windows? (i don't know too much about windows)

A reader, March 27, 2005 - 4:51 pm UTC

Hi Tom,

A colleague of mine did the install. I can see the listener.ora in the oracle_home\network\admin directory. Are you thinking that this is not the correct listener.ora file? Yes, this is a windows system.

Thanks.

Tom Kyte
March 27, 2005 - 5:23 pm UTC

that is the right place, whats in the file -- perhaps everything is just setup "default" (eg: the listener.ora is basically almost empty and the databases just register with the default listener)

A reader, March 27, 2005 - 7:17 pm UTC

These are the contents of the file listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ENVS=EXTPROC_DLLS=ANY)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = OEMREP)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = OEMREP)
)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = test)
)
)

Thanks.

Tom Kyte
March 28, 2005 - 7:13 am UTC

I'll have to refer you to support, I don't have a windows installation setup to play around with easily right now.

More info...

Mark, March 30, 2005 - 5:24 pm UTC

Oracle 8.1.7.4...

If you are using Microsoft Transaction Server, you need to read this article:

</code> http://support.microsoft.com/kb/193893 <code>

Specifically, setting the QUEUESIZE = 100 resolved our situation. When we migrated to new equipment, we 'forgot' to set this in the Listener.ora file.

We had a client process sending us messages rapid-fire and it seemed like the Listener could not keep up, giving us errors.



A Oracle TNS Listener has been detected on Host

Dawar, June 08, 2005 - 7:12 pm UTC

Tom,

Database version :9.2.0.5.0
OS : Red Hat Advance server 3.0

Prior to go live our security group scan our servers.
And they found following issue on our Database Server.


Description:
A Oracle TNS Listener has been detected on the host with login security disabled (SECURITY=OFF).

Recommendation:

** 1) It is recommended to only allow certain IP's or subnet ranges to access the TNS listener. This can be done by adding a rule in the firewall.

** 2) We also recommend that you enable a password for the TNS listener within Oracle.

I think firewall first recommendation will be done by our security folk who deal with firewall.

I need to number two.

Currently I do not have any password for listener.
So I need to create a password and makes it enable.

How could I accomplish this?

Regards,
Dawar


oracle sid

reader, June 21, 2005 - 1:20 pm UTC

what is the equivalent of echo $ORACLE_SID in DOS prompt? Thanks.

Tom Kyte
June 21, 2005 - 5:21 pm UTC

if you didn't set it, it won't be there, it'll be in the registry.

C:\Documents and Settings\tkyte>echo select instance_name from v$instance; | sqlplus -s tkyte/tkyte

INSTANCE_NAME
----------------
ora9ir2w


would show it too I suppose.

Is this a LISTENER problem?

Puja, July 25, 2005 - 6:07 am UTC

HI..

    I am connected as a sysdba to a database using a net service. I dont face any problem while executing any admin task. I can also issue SHUTDOWN IMMEDIATE. But after that if i try to STARTUP the database, it gives me the following error: 
    ORA-12519: TNS:no appropriate service handler found
     
    I have to then go to that machine (using Remote Desktop utility) and startup the database. What is the reason behind this error, and how to solve it?
     
    I have appended the contents of my tnsnames.ora and sqlnet.ora. Also I have posted the result of LSNRCTL STATUS executed on the database machine(connected using remote desktop utility)
     
    DATABASE : JCREW(spFILE already created)
    MACHINE : SERVBNGJCREW2
     
    Net service name : jcrew
    Port num : 1521
     
    My machine: BDM218
    C:\Documents and Settings\Paudhya>SQLPLUS "SYS/SYS@JCREW AS SYSDBA"
     
    SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jul 22 11:36:43 2005
     
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
     
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
     
    SQL> SELECT STATUS FROM V$INSTANCE;
     
    STATUS
    ------------
    OPEN
     
    SQL> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    ORA-12519: TNS:no appropriate service handler found
    SQL>
     
     
    ---------------------------------------------------------------------------------------------------------------------------------------------
    TNSNAMES ENTRY ON MY MACHINE 
    (i have tried the same with both jcrew and jcrew1)
     
    JCREW =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = srvbngjcrew2)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID = jcrew)
        )
      ) 
     
     
     
    JCREW1 =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = srvbngjcrew2)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = JCREW)
          (SERVER = DEDICATED)
        )
      )
     
    ----------------------------------------------------------------------------------------------------------
    Entries in Sqlnet.ora

    SQLNET.AUTHENTICATION_SERVICES= (NONE)
    NAMES.DIRECTORY_PATH= (TNSNAMES)
    USE_DEDICATED_SERVER = ON
     
     
     
    MACHINE : SERVBNGJCREW2
     
    C:\Documents and Settings\Administrator>LSNRCTL STATUS
     
    LSNRCTL for 32-bit Windows: Version 9.2.0.4.0 - Production on 22-JUL-2005 11:39:
    50
     
    Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
     
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SRVBNGJCREW2)(PORT=1521)
    ))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.4.0 - Produc
    tion
    Start Date                05-JUL-2005 11:02:58
    Uptime                    17 days 0 hr. 36 min. 52 sec
    Trace Level               off
    Security                  OFF
    SNMP                      OFF
    Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
    Listener Log File         C:\oracle\ora92\network\log\listener.log
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=1521)))
     
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=8080))(
    Presentation=HTTP)(Session=RAW))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=2100))(
    Presentation=FTP)(Session=RAW))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=8080))(
    Presentation=HTTP)(Session=RAW))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=2100))(
    Presentation=FTP)(Session=RAW))
    Services Summary...
    Service "JCREWINT.PABNG.COM" has 1 instance(s).
      Instance "JCREWINT", status UNKNOWN, has 1 handler(s) for this service...
    Service "Jcrew" has 1 instance(s).
      Instance "Jcrew", status READY, has 1 handler(s) for this service...
    Service "JcrewInt" has 1 instance(s).
      Instance "JcrewInt", status READY, has 1 handler(s) for this service...
    Service "JcrewIntXDB" has 1 instance(s).
      Instance "JcrewInt", status READY, has 1 handler(s) for this service...
    Service "JcrewXDB" has 1 instance(s).
      Instance "Jcrew", status READY, has 1 handler(s) for this service...
    The command completed successfully
     
     
     
     
     

    
    
     

    Thanks and Regards,
    
    Puja  

Tom Kyte
July 25, 2005 - 8:07 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/network.920/a96580/listener.htm#453914 <code>

You need to statically register the database with the listener if you want to be able to remotely start it up (you are using dynamic registration, if the database is not up, it is not registered with the listener, hence you cannot get to it via the listener)

Service already registered with Listener

Puja, July 25, 2005 - 8:40 am UTC

Hi...

The service JCREW is already registered with the Listener. Here are the contents of the listener.ora. And I had then restarted them using LSNRCTL> STOP followed by LSNRCTL> START. I did go through the documentation (the link which you had suggested )as well - but couldn't find out where am I missing something..

# LISTENER.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SRVBNGJCREW2)(PORT = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = JCREWINT.PABNG.COM)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = JCREWINT)
)
(SID_DESC =
(GLOBAL_DBNAME = jcrew)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = jcrew)
)
)



Tom Kyte
July 25, 2005 - 9:07 am UTC

do a lsnrctl services and see what services you see when the database is down. those are ones you can use to connect.

LSNRCTL services

Puja, July 25, 2005 - 9:23 am UTC

Hi...
The JCREW service does appear in this :

SQL> host lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.4.0 - Production on 25-JUL-2005 11:12:
13

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SRVBNGJCREW2)(PORT=1521)
))
Services Summary...
Service "JCREWINT.PABNG.COM" has 1 instance(s).
  Instance "JCREWINT", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "Jcrew" has 1 instance(s).
  Instance "Jcrew", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:19367 refused:0 state:ready
         LOCAL SERVER
Service "JcrewInt" has 1 instance(s).
  Instance "JcrewInt", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:11 refused:0 state:ready
         LOCAL SERVER
Service "JcrewIntXDB" has 1 instance(s).
  Instance "JcrewInt", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: SRVBNGJCREW2, pid: 1324>
         (ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=2122))
Service "JcrewXDB" has 1 instance(s).
  Instance "Jcrew", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: SRVBNGJCREW2, pid: 2712>
         (ADDRESS=(PROTOCOL=tcp)(HOST=SRVBNGJCREW2.PABNG.COM)(PORT=4072))
The command completed successfully

SQL>



Thanks and regards,

Puja
 

Tom Kyte
July 25, 2005 - 11:06 am UTC

seems the database is up and running isn't it.

Puja, July 26, 2005 - 1:33 am UTC

Right said, Tom...

I re-registered the service with the listener (using net manager) and then restarted the listener.

The problem is solved - Thanks


Max Process and Ora-12500

Shiju, August 04, 2005 - 9:37 am UTC

Tom,
We installed Oracle 10g Db and we were able to make only few user connections to the DB (only 20 sessions, including Oracle sessions, were able to connect!) and Oracle was throwing

ORA-12500 TNS:listener failed to start a dedicated server process

Later we found the reason to be the init parameter processes set as 24. By setting it to a higher number(Number of Concurrent session required + 20), we are able to make more connections to the Database.

Would it be better to show ORA-00020 error in this case??
(ORA-00020: maximum number of processes (string) exceeded)




Max Process and Ora-12500: More Details

Shiju, August 05, 2005 - 3:33 am UTC

Server Machine:Windows Server 2003 Standard Edition
Oracle DB Version: 10g Release 10.1.0.2.0


Is it a bug, that we are getting Ora-12500 instead of ORA-00020 error in the above case?

As always, thanks and looking for your opinion.



Tom Kyte
August 05, 2005 - 11:02 am UTC

the error is coming from the listener, the listener is "unable to start a dedicated server process"

JDBC time out

atul, September 30, 2005 - 1:36 am UTC

Hi,

We are getting "JDBC Time out" error.
Exactly "JDBC activity timed out"

No firwall involved in between.

Could you kindly give suggestion for this?

Thanks,
Atul



Tom Kyte
September 30, 2005 - 9:02 am UTC

not really - never heard of it.

"Could not create listener" error

A reader, November 08, 2005 - 10:50 am UTC

Hi Tom,

I am trying to do the following:

Oracle Net Configuration Assistant->Listener Configuration->Add and once I come to the "Finish" screen I am getting an error message "Could not create listener". Can you help me with this please? The thing is I already have a listener service running and when I go to Listener Configuration I dont know why it just gives me the option to Add (not Reconfigure or Test). Please help.

Thanks.

ora-12500 on 8.1.7.4 Windows

A reader, March 16, 2006 - 5:34 am UTC


ORA-12546 TNS: permission denied

Jayesh, September 05, 2006 - 7:51 pm UTC

Hi Tom,

Platform: Win 2000 Advanced Server
RDBMS: Oracle 9.2.0.6

As a part of tighting the server security, the local admin privs has been revoked from all DBA users and granted only Power User rights with full control on all oracle binaries.

But when I try to connect to the local database I am getting the following error:

E:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Sep 6 07:41:48 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12546: TNS:permission denied


Enter user-name:


At the same time, I can connect to my database if i direct it through the listener using the TNS name

Enter user-name: /@ORACLE as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>


Can you provide some insights into this? Is there any other changes which I need to do - file permissions, oracle settings etc - if I want to connect without using listener (say the listener is down)

thanks 

Tom Kyte
September 06, 2006 - 7:48 am UTC

sorry, I don't know enough about windows for this one, please utilize support.

Try This

A reader, September 07, 2006 - 7:02 am UTC

X:\ Set ORACL_SID=<YOUR_SID>
X:\ Sqlplus "/ as sysdba"

It should work.


Sorry..

A reader, September 07, 2006 - 7:04 am UTC

It is ORACLE_SID=UR_SID

Listener File Missing

Chandan Singh, November 16, 2006 - 7:24 am UTC

Hi Tom,

I was just trying to look into listener.ora file to make some changes for hostnaming but when i looked on path $ORACLE_HOME/network/admin there was no file listener.ora file on SunOS v5.9 for database Oracle 9204.

On SunOS, is it maintained on some other path? i tried to find out but didn't get any file apart from samples folder file in admin folder.

I am able to use database and users are also able to connect to the database. I am not able to understand if listerner file is missing how connections can be made?

Can you throw some light on this?

Thanks
Chandan Singh

Tom Kyte
November 16, 2006 - 3:18 pm UTC

you don't need one, the listener will start and databases dynamically register with it.

you could be using the TNS_ADMIN environment variable to "hide" it elsewhere, but you would have set that yourself and presumably would know where it is.

Unable to connect to 'database'

A reader, January 08, 2007 - 8:14 pm UTC

Hi Tom,

Love this website. Been doing oracle for less than a year, reading up material from this site provides me overwhelmingly more new knowledge than actually doing at work. THANK YOU!

That aside, my coldfusion web pages retrieved data directly from the db. Strange thing is recently, a lot of <Unable to connect to 'db' Request Timeout> messages logged at the web server application.log especially during the wee hours.

I monitor the listener.log, but no connection error reported. Wouldn't all connection requests be logged into listener.log, success or otherwise? Does it help to turn on the sqlnet tracing at the web client?


Thanks,
lois
Tom Kyte
January 09, 2007 - 7:34 am UTC

well, for example, what would happen if the coldfusion application wasn't able to even contact the listener - there would not be any message in the listener log - it wouldn't know to log one.

Sounds like a problem on the machine coldfusion is on - or on the network between that coldfusion machine and the machine with the listener possibly

Tns No Listener

Bhushan, January 09, 2007 - 9:31 am UTC

Hi Tom,
I have been having this problem since ages but i only ignored since i was able to connect from a different machine,here is the problem(Let me explain you the scenario)
Machine 1:
I have this TNS Entry
Machine1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X.XX.X.XX)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ABCDE)
)
)
I get an error
TNS-12541: TNS:no listener
However i am able to connect to other DB's from the same machine.

I use the same TNS Entry on the other machine and i am able to connect succesfully.

The format/convention for the TNS entries in Machine1 has been consistent so i believe it does not have anything to do with number of brackets or the TNS format.(i have verified that too)

Could you please spare some time and let me know what the issue could be.
Tom Kyte
January 11, 2007 - 9:27 am UTC

if you ping x.xx.x.xx does it work
if you tnsping machine1 does it work

Bhushan, January 11, 2007 - 11:44 am UTC

Yes if i try to ping x.xx.x.xx i do get a reply but when i try a tnsping Machine1 that is when i get the no listener Error...However as specified earlier i can connct to the same DB from another machine... and from the machine where i cannot connect to Machine1 connects fine to rest of the DB's. IF it helps i connect using PL/SQL developer.
Thanks in advance!

seen it before, January 12, 2007 - 4:17 pm UTC

Verify that there is a space between entries in your tnsnames file.

Sorry Cant Get you

Bhushan, January 13, 2007 - 5:05 am UTC

HEre are the Entries in my TNS Files
I can connect to DB1 and DB2 but DB3 and DB4 i cannot connect also as i said before when ever i try to TNSPING DB3 and DB4 i get the error
TNS-12541: TNS:no listener
Also if i try to ping the IP's in teh NTS Entry i get a reply.
Below are the entries from my TNSNAMES.ORA file (Copy Pasted)

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X.XX.XXX.XX)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ABCD)
)
)


DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Y.YY.YYY.YY)(PORT = 1521))
)
(CONNECT_DATA =
(SID = EFGH)
)
)


DB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Z.ZZ.ZZZ.ZZ)(PORT = 1521))
)
(CONNECT_DATA =
(SID = IJKLM)
)
)


DB4 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = A.AA.A.AA)(PORT = 1521))
)
(CONNECT_DATA =
(SID = NOPQR)
)
)

Please let me know if any other details are required to sort this out...it's been killing me to understand the issue :-(

Sorry for the Typos above

Bhushan, January 13, 2007 - 5:07 am UTC

Hey,
Sorry for the Typos above..hope its still readable and gives a clear picture of the issue...

Issue Resolved

Bhushan, January 19, 2007 - 8:19 am UTC

Hi,
The above mentioned issue is resolved.It was kind of complicated in itself 1) had to cahnge the SQLNET.ora for the right domain after doing that i still could not connect then had to change the TNS entry too was ont the correct host... Thanks Anyways

CIAO

What is Best??

Emad Kehail, September 10, 2007 - 3:48 am UTC

Hello Tom,

I hope you help me in this as you usually do.

I have the following situation:

Oracle Database 9i R2 installed on Windows 2000 AS which is running on HP Server with the following specification:
CPU: 4 (32 bit) CPUs Pentium IV 2.6 HT
RAM: 4 GB
Hard Drives: 8 Hard drives with RAID 10

This server is dedicated to the Oracle Database which the users are accessing it locally and from the Internet.

However, once the sessions connected to the server started to reach 600 or little more we start to have the error:
ORA-12500: TNS:listener failed to start a dedicated server process

I have read about the 4GB Tunning and how to allow Oracle Process in Windows to grow more than the 2 GB limit.

I have added the switch /3GB to the boot.ini in Windows and things have improved

The sessions go beyond the 700 even 800 concurrent sessions.

However, now we have reached more than 980 concurrent sessions and the Oracle Database started to display the same error "ORA-12500: TNS:listener failed to start a dedicated server process"

This is in addition when we start to go more than 850 or little more, the users who connect to the database take longer time to connect than before.

I have monitored Oracle from Oracle Enterprise Manager and Statspack reports in peak working hours. The CPU usage is just around 50% and IO seems to be acceptable. I felt it is a listener and RAM problem

1) For the listener, I have added a new listener
I have dedicated the web users to connect only through the new listener while the local users connect through the default listener. The connection is much better and it is fast as before.

2) For "ORA-12500: TNS:listener failed to start a dedicated server process" error, I believe I have reached the threshold of the server. I believe we have used all the RAM dedicated to Oracle (3GB) and Oracle can not allocate more RAM to the clients.

Now, if the previous was correct, I am thinking in of the following:

a) Switch to Shared Server mode instead of Dedicated mode. I will enlarge the Large Pool in order to hold all the client sessions and to avoid allocating session in the shared pool. I am thinking to have 10 dispatchers and 1200 shared servers.

b) Switch to Linux, and therefore we will not have (oracle.exe) only. We will have many processes for users and background processes. But I do not know how much additional users we might have by switching to Linux. Moreover, the DBA know nothing about Linux!

c) Purchase a new hardware with 64 bit CPUs and 64 bit OS, and install 64 bit Oracle Database on this server. This way we can scale to much more users since the oracle.exe process can grow to a much larger size than the 32 bit one


d) Go for RAC. Purchasing a new server and configure the new server with the old in RAC environment, and therefore we can scale for more users. But we still need a shared storage (SAN for example). The cost here seems high!!

Too many options, and I am really confused regarding them. Which one to go???

I hope you help me here and I do hope you have a creative solution

Thanks

Tom Kyte
September 12, 2007 - 10:53 am UTC

your machine seem woefully inadequate for 1,000 sessions don't you thing?

It is tiny.

a) shared server stands little change to reduce memory usage, which is likely your issue in a 32bit windows environment. You in fact will have to preallocate a fixed size large pool for your session memory. doubtful.

b) well, 64bit windows would seem to be a viable path as well.

c) yes, you knew that

d) that would work as well. Define "cost is high", isn't the cost of having a non-functional system pretty darn high as well?

To Emad

Mark, September 13, 2007 - 1:32 pm UTC

You could try enabling Physical Address Extension: http://www.microsoft.com/whdc/system/platform/server/PAE/PAEdrv.mspx Windows 2000 Advanced Server can use up to 8GB of physical ram with PAE. Just an option to consider...I can't personally vouch for it.

RAC as an option

Emad Kehail, September 13, 2007 - 4:59 pm UTC

Thanks Tom for these comments and quick feedback.

I am almost with the 64 bit hardware and OS option. However, regarding the RAC environment, I meant by "high cost" the following:

1. Now we have one server with 32 bit hardware and OS. This server can serve as maximum 1000 users. However, the CPU usage on this server with the 1000 users did not exceed the 60%.

2. Purchasing a new server (32 bit) with 4 GB RAM, will allow us to scale to an additional 1000 users. However, the new server CPU usage will also not exceed the 60% if not less since its CPUs will be better than the older one.

3. Besides the new server, we have to purchase a SAN storage since the Oracle Database has to be stored on a shared media. (Can we store it on both servers HDD????, I do believe - as what the documentation says -we need a shared storage with RAC)

4. We will need a training for the DBA to master RAC environment since they never work on it, or at least dedicate them to read the manuals and allow them to try it and master it.

If I was right in the above, then the RAC configuration will allow me to expand for a maximum of another 1000 users only.

When I have compared the RAC option with 64 bit option. I found it much expensive. With 64 bit option I will need to purchase a new server with a RAM that is more 4GB even more than 8 GB. I will be able to support more than 2000 users.

I am talking here in terms of scalability not availability. I do still believe we need RAC environment or Stand By Database to be much more available to our customers. Anyhow, for the time being, I am so concerned to solve the scalability problem as a first issue.

Mark, I believe the Physical Address Extension will help to expand the Buffer Cache size by using the Indirect Buffers option. I do not have a problem with the buffer cache in my database. Waits on the buffer cache acceptable at the peak usage of the server. I just need more RAM to allow more users to connect to my instance. I hope Tom comment on this point too.

Thanks
Tom Kyte
September 15, 2007 - 8:15 pm UTC

3) you do not need san. you just need shared disk.

RAC goes more than 2 nodes. many more than two nodes, why did you stop at two?


Two were just example

Emad Kehail, September 17, 2007 - 3:27 am UTC

Two server were just as an example. I know I can scale up with many servers, but my concern is as follows:

My current 32 bit server is capable of running 900~980 sessions at most and the server CPU did not exceed the 60%. This shows that I have memory problem not CPU.

If I purchased another 32 bit server and configured RAC, I will be able to scale to another 980 sessions because of the limitations of the 32 bit. We also have to realize the new server will have better CPUs and also I will not utilize it well.

If I need to scale to more sessions, then I have to get another server and I will scale only to another 980 sessions, and also the CPU will not be utilized (maybe to 40% or even less since the CPUs installed on new servers are much better than the one we have now).

Therefore, the two servers were just as an example, I am thinking of:

1) Replacing the 32 bit server with 64 bit. Then I can scale to much more users.

2) If we need to scale to more users, then another 64 bit server can be configured with RAC...

3) The old 32 bit server will be used as Data Guard for the 64 bit one. -- need to be tested, not sure if it will work or not.

Regarding the Mark comments, can I use the PAE with Windows and address more than the 4GB??? Is it for user sessions or the buffer cache only???

I have found this link with PPT presentation about Oracle and Windows memory configurations.

http://www.microsoft-oracle.com/oracledb/pages/workshops.aspx


Tom Kyte
September 18, 2007 - 2:10 pm UTC

well, that doesn't show that - you might be swapping like mad and if you fixed the memory - you would start having serious cpu issues.


You might find that if you backed off the number of sessions - your cpu would go UP. Happens all of the time. Do not expect that as you remove a bottleneck - for all other resources to remain at their current level.


3) you would never use hetergenous hardware for failover, does not make sense.


the second you start playing with 32 bit windows memory - you are going down a slippery unstable slope. You'll spend half of your time figuring out why something isn't working right. I'd opt for either of

a) a platform whereby process memory is separate from the SGA
b) 64bit

Connect to Oracle 8.1.7 Personal Edition

Nazmul Hoque, February 22, 2008 - 5:40 am UTC

Dear TOM,

I am useing Oralce PE of 8.1.7 in a pc os XP, and install developer 6i, on that pc and its working fine. On that PC

Tnsnames is as under
=====================

ORAPE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = global-hoque)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORAPE)
)
)

And LISTENER as under
=====================

# LISTENER.ORA Network Configuration File: D:\orant_PE\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = global-hoque)(PORT = 2481))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\orant_PE)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORAPE)
(ORACLE_HOME = D:\orant_PE)
(SID_NAME = ORAPE)
)
)

I have install client Oracle 8.1.5 on a pc and both pcs are under the same Lan/Workgoup

but I am not able to connect the Oracle PE 8.1.7 from the Oracle 8.1.5 client pc getting error :

" ORA-12535 TNS operation time out "

I have try to connect by belows trnsnames on client pc :

mydata1 =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 192.168.1.11)(Port = 1521))
(CONNECT_DATA = (SID = ORAPE))
)

mydata2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORAPE)
)
)

mydata3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA = (SID = ORAPE))
)

Please advise for the solution.
Tom Kyte
February 22, 2008 - 7:23 am UTC

gosh, this is like a time warp.

Forgetting the unsupportedness of this and the fact that XP didn't even exist then... anyway...


You are using NAT behind a firewall of some sort. Can you even "ping" that machine or tnsping it.

Coneect to Oracle 8.1.7 PE on XP

Nazmul Hoque, February 24, 2008 - 3:20 am UTC

Thanks a Lot Guru, It happen for the windows firewall of xp. Once i off the firewall its working very fine.

Thanks once againg for help to solve the issue


Tnsnames.ora - for VPN

Nazmul Hoque, November 06, 2008 - 2:05 am UTC

Dear tom,

Need your Suggetion to connect Database server 8.1.5 from the VPN. I have server and i can connect server useing LAN the following tnsnames.ora

NTSERVER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gpldhaka)(PORT = 1521))
(CONNECT_DATA = (SID = gpldata))
)

Can i use same if i want to connect the server from a pc with is not in my Lan, but than can access my Lan by VPN. and also accesable the server ips.

Please advise.

Thanks/Nazmul
Tom Kyte
November 11, 2008 - 2:16 pm UTC

a vpn would change nothing, I'm not sure what issues you are hitting.

if you "vpn", you'll be on that same network - it is 'as if' you are right there with all of the other machines on that network.

TNS-12500: TNS:listener failed to start a dedicated server process

Harschil, January 08, 2009 - 9:52 am UTC

Hi Tom,

We are badly being hit by the following error: 

--Listener log says

08-JAN-2009 12:43:05 * (connect_data=(server=dedicated) 
(service_name=XX)(INSTANCE_ROLE=primary)(failover_mode=(type=session) 
(method=basic)(backup=YY)(retries=180)(delay=5))(CID=(PROGRAM=oracle) 
(HOST=ZZ)(USER=oracle))(INSTANCE_NAME=AAAA)) * (ADDRESS=(PROTOCOL=tcp) 
(HOST=<IP>)(PORT=<PORT>)) * establish * BB * 12500 
TNS-12500: TNS:listener failed to start a dedicated server process 
 TNS-12540: TNS:internal limit restriction exceeded 
  TNS-12560: TNS:protocol adapter error 
   TNS-00510: Internal limit restriction exceeded 
    HPUX Error: 11: Resource temporarily unavailable 


Issue seen at the following interval. ( as a sample.).. It is intermittent. 

Start   Time                 End Time
08/01/2009 09:00:49 08/01/2009 09:00:49
08/01/2009 09:06:52 08/01/2009 09:06:52
08/01/2009 09:16:59 08/01/2009 09:16:59
08/01/2009 09:25:03 08/01/2009 09:25:04
08/01/2009 09:27:04 08/01/2009 09:27:04
08/01/2009 09:29:06 08/01/2009 09:29:06
 


In between above intervals connections are successful 


SQL>select * from v$resource_limit; 


RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL  LIMIT_VALU 
------------------------------ ------------------- --------------- ---------- ---------- 
processes                                      135 256       1000       1000 
sessions                                       134 252       1105       1105 
enqueue_locks                                   72 361      14154      14154 
enqueue_resources                               72 72      50000  UNLIMITED 
ges_procs                                      133 253       1001       1001 
ges_ress                                     37791 50079      90367  UNLIMITED 
ges_locks                                    28140 42011      96306  UNLIMITED 
ges_cache_ress                                5361 8522          0  UNLIMITED 
ges_reg_msgs                                   222 250       2230  UNLIMITED 
ges_big_msgs                                    17 28       2230  UNLIMITED 
ges_rsv_msgs                                     0 0       1000       1000 
gcs_resources                                72634 81356    122432     122432 
gcs_shadows                                  26643 26663      67337      67337 
dml_locks                                        0 36       1024  UNLIMITED 
temporary_table_locks                            0 2       UNLIMITED  UNLIMITED 
transactions                                    14 66       1215  UNLIMITED 
branches                                         2 28       1215  UNLIMITED 
cmtcallbk                                        4 29       1215  UNLIMITED 
sort_segment_locks                             134 137     UNLIMITED  UNLIMITED 
max_rollback_segments                           11 11        244        244 
max_shared_servers                               1 1         20         20 
parallel_max_servers                             4 121        241        241 


22 rows selected.


We are using "dedicated Server" connection. 


Q)

a) What could be causing this ?

We have 05 databases running in 02 Node RAC Cluster. 
 All the connections goes to Host-A , where Primary Instance is running and in case of failure of Host-A connections goes to Host-B ( which takes the role of Primary Instance, if Host-A fails). Due to application restrictions we are using Activ-Passive configuration.

b) Can we get rid of above issue by running Primary instances of some of the databases ( say 02 out of 05) in Host-B so that load is evenly distributed?

We are the Master Site and from the Snapshot Site there are many connections being made to refresh the MV. Refresh is based on primary Key and refresh interval is 2 mins.
 They recieve the same TNS:12500 when we recieve the same in listener logs.

We are facing TNS:12500 almost in all the databases at the same time.


Regards




CPU /Mem utlisation is normal. No change seen in the pattern. 


Many Thanks.


Tom Kyte
January 08, 2009 - 11:36 am UTC

HPUX Error: 11: Resource temporarily unavailable

this is an OS error during the creation of a process - unable to create a process.

see Doc ID: Note:146859.1
Subject: New Connections to the Database fail with TNS-12500, TNS-

12540 and HP UX Error 11 When Many Concurrent Users Are Connected

TNS-12500: TNS:listener failed to start a dedicated server process....

Harschil Kaparwan, January 08, 2009 - 2:17 pm UTC

Many thanks Tom,

You pointed me to correct document :)

We will workout with Unix Team how that can be implemented.

Kind Regards,

TNS-12500: TNS:listener failed to start a dedicated server process....

Harschil Kaparwan, January 08, 2009 - 3:29 pm UTC

Hi Tom,

Once observation i have seen when we faced this issue.

Whenever we were running dbms_stat.gather_schema_stat during off peak hours, with degree=2 , we found that connections faling because of "TNS-12500: TNS..."

Then we deferred the stats gathering job by 02 days.

So when stats gathering job is not running we didnt faced any TNS:12500 issues. But during peak hours we faced this issue as stated in my previous post.

I have questions :

a) When we ran stats gathering job, with degree=2 does Oracle creates many processes at OS level under the account of "oracle" user ? How many that *many* actually will be ?

b) How frequently we should gather stats.. We are running it daily with "gather auto" option. Can we gather schema stats weekely for CBO ?.. because Big table will always remain big and small table small.We have OLTP system and there is not much data growth daily.

RDBMS : 9.2.0.6
OS : HP Ux 11.23


Kind Regards


Tom Kyte
January 08, 2009 - 3:57 pm UTC

a) it could be as many as 5 (two readers, two sorters, one query coordinator). degree two isn't very "large"

b) there is no single simple answer for this. If your data is not changing frequently, then the gather auto job won't do anything (it looks for tables that have been modified fairly heavily). Meaning - if they are not changing much, gathering frequently would be the same as gathering infrequently - since most of the times you are not gathering anything (and hence it would not be a problem). But since it seems to "do quite a bit" of work here - maybe the data is more volatile than you believe it to be.

TNS-12500: TNS:listener failed to start a dedicated server process....

Harschil Kaparwan, January 08, 2009 - 7:48 pm UTC

Thanks a lot Tom,

..... But since it seems to "do quite a bit" of work here - maybe the data is more volatile than you believe it to be.

Yes, it took around 30 mins to gather stats for 08 tables ( cascade=TRUE and degree=2), out of these 08 few are very big tables millions of records. And exactly during these 30 mins we got hit by TNS:12500 many a times. Once job completed no more TNS:12500 during off peak hours.


Regards.

Tom Kyte
January 08, 2009 - 8:09 pm UTC

well, the HPUX error might not be "out of processes" then - the machine just might have been totally maxed out and unable to create a new process.

TNS-12500: TNS:listener failed to start a dedicated server process....

Harschil Kaparwan, January 09, 2009 - 1:29 pm UTC

Many thanks Tom,

for enlightening me as always!

...well, the HPUX error might not be "out of processes" then - the machine just might have been totally maxed out and unable to create a new process.

- We have checked the CPU and Mem utilization of the hosts and found it normal.
- CPU/Mem utilization is same as it was during the days when we were not getting these errors.
i.e. Avg CPU Utilization 30-40 % ( we are OLTP ! ) & Memory Utlization 70-75%

Since we have 02 hosts in RAC but using only 1 at a time due to application restriction as i posted earlier. sort of Active-Passive config though both Instances are up and running. We control client connections to go to PRIMARY_INSTANCES using instance_role parameter.


More observations:

Host A: running PRIMARY_INSTANCE

We have 'maxuprc' kernel parameter set to 1200 and i have seen 'oracle' process count hitting in *active* node closer to this value. ( i was lucky enogh to see the value 1186, thinking of scheduling some cronjob to capture these values)

Process distribution at OS level in 02 hosts is as follows:


hostA$ ps -ef | grep ora_ | grep -v grep | wc -l
313
hostA $

hostA$ps -ef | grep oracle | grep -v grep | wc -l
1186

--------------------------------------------------------------------------------
Host B: running SECONDARY_INSTANCE

'maxuprc' : 1200

hostB$ ps -ef | grep ora_ | grep -v grep | wc -l
312
hostB$

hostB$ ps -ef | grep oracle | grep -v grep | wc -l
396
hostB$


-- We are the "Master Site" and have a Snapshot Site
-- There are 05 databases running in 02 hosts RAC
-- Primary Key based refresh is done.
-- Snapshot Site is replicated at every 15 mins from these 05 master databases each having nearly 15 tables to replicate.
-- They, *Snapshot Site* are the real "sufferer" of this issue... i guess sliding interval in dbms_job is also happening... and intermittently during the day snapshot site is requesting many sessions ( processes at OS level) thro db_links.


My questions:

a) it looks that by raising 'maxuprc' by <SOME_FIGURE> SAY 500 will solve the issue ? We will increase it and see if error are re-occuering.

b) can we move all connections ( by changing INSTANCE_ROLE=secondary in the TNS of SNAPSHOT SIte database) coming from Snapshot site to hostB ? That will ease the load ( reduce processes at OS on 'oracle' user account ) on hostA? and hence no need to bump up the 'maxuprc' parameter?

c) Above (b) is feasible in our scenario because this action is independent of application, as snapshot site is uses db_link to connect to the databases and it is not via 'application' ( of course it cannot!). so whether moving connections of snapshot site *alone* from Host-A to Host-B will have any issues..Just raising an concern..?

Kind Regards.


Tom Kyte
January 09, 2009 - 3:08 pm UTC

a) probably, if you are close to it already - running something extra like a stats gather would put you over the limit.

b) but wouldn't that just move the problem from A to B? Not sure what you would accomplish with that.


TNS-12500: TNS:listener failed to start a dedicated server process....

Harschil Kaparwan, January 09, 2009 - 9:59 pm UTC

Hi Tom,

...b) but wouldn't that just move the problem from A to B? Not sure what you would accomplish with that.



Connection to db are : JDBC + SQLPlus/TOAD + conn requested by Snapshots sites for MV refresh

Oracle ver : 9.2.0.6
Weblogic : 8.1
Application : Clarify CRM




re-visiting the no of processes statistics gathered earlier in A & B:

Host A: running PRIMARY_INSTANCE

'maxuprc' : 1200

hostA$ ps -ef | grep ora_ | grep -v grep | wc -l
312
hostA $

hostA$ps -ef | grep oracle | grep -v grep | wc -l
1186

--------------------------------------------------------------------------------
Host B: running SECONDARY_INSTANCE

'maxuprc' : 1200

hostB$ ps -ef | grep ora_ | grep -v grep | wc -l
312
hostB$

hostB$ ps -ef | grep oracle | grep -v grep | wc -l
396
hostB$


Suppose - above statistics were gathered at time T1.

Then by moving conn requested by Snapshots sites for MV refresh to B will bring down the figure 1186 by say 'n' and these 'n' will be added to figure 386 above.

Value of n :
No of Master Site databases : 05
No of Master tables in each database : 20
and say in a worst case snapshot site requests to refresh these all MV at the same time.( Slide Time affect!)
Then number of processes created at OS level in Master Host by such connections would be 20*5

So by moving these connections to B will add 20*5 to 396
but at the same time no of processes in A will get reduced by 20*5 . So number of processes in A = 1186 -20*5

So,
a) are we not able to achieve the goal "keeping number of processes below at least by 100 in A" and not significantly increasing the processes in B ? further we are not moving *all the connections* from A to B, but moving *only* connections set up for MV refresh.


Kind Regards




Tom Kyte
January 12, 2009 - 8:27 pm UTC

not sure what you are counting here - why not just look at the job queue processes, those are the only that will be "moving" - but then you will be active active - 100% and totally - so, still not sure what you think you are accomplishing, since you will be changing from active/passive to active/active and why not just go all of the way?

tnsping taking long

Manoj Kaparwan, February 23, 2011 - 11:25 pm UTC

Tom
Thanks for your time.

we are seeing issue while connecting to DB using SQL*Plus.
sometime connection gets established quickly but sometime it takes ages.

we checked the tnsping from client it was responding sometime in msec and sometime in minutes.

then tnsping from DB host ( where DB is hosted)was tried and it was responding sometime in msec and sometime in minutes. ( Network issue rules out)



-- excerpts ( tnsping from DB host)

Slower response
---- Wed Feb 23 14:43:03 GMT 2011 ----

$tnsping aaaaaaa

TNS Ping Utility for HPUX: Version 9.2.0.2.0 - Production on 23-FEB-2011 14:42:45

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = nnnnn))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = aaaaaaa)))
OK (16250 msec)




Quicker response :
---- Wed Feb 23 14:43:25 GMT 2011 ----

TNS Ping Utility for HPUX: Version 9.2.0.2.0 - Production on 23-FEB-2011 14:43:27

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = nnnnn))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = aaaaaaa)))
OK (280 msec)





we are using dedicated server connection type and Operating system is HP Ux 11.23, Database - 9.2.0.8

This our test database. we have Oracle binaries and datafiles on NAS mount. ( Storage team says are no delays in NAS call. this is re-confirmed from the fact that once connection is establised we dont have any delays in TX processing ( COMMIT/write/read etc..)


question/s
a) Looks listerner not responding to the tns calls quickly.?
b) If we tnsping - do listener process creates a dedicated server( process) ? I suppose NO.?
c) how tnsping works - some insight would be useful.?
d) where we should look into to resolve it?



regards



Tom Kyte
February 24, 2011 - 12:37 pm UTC

what about ping/dns name resolution itself - is your network itself reliable. sounds suspiciously like a dns names resolution issue.

tnsping taking long...

Manoj Kaparwan, February 24, 2011 - 7:32 pm UTC

Tom,

we did ping/traceroute from client to DB host - it is always quicker ( but at the same time tnsping from client is intermittently slow)

we did tnsping from db host itself - it is intermittently slow ( response time varies from msec to seconds)

we did direct sqlplus connection test from DB host using OS authentication - that also takes considerable time intermittently.

we kept direct IPs and hence no DNS names to resolve the issue as depicted in the tns entry above - but no luck.


regards

tnsping taking long...

Manoj Kaparwan, February 24, 2011 - 7:36 pm UTC

Tom
adding to above..
looks it is something which is going wrong in the DB host itself ( as tnsping / sqlplus using OS authentication from DB host itself is slow..) , however we checked with the network team and they said found things OK from network side.

regards

Tom Kyte
February 24, 2011 - 7:49 pm UTC

what is the load on the database host machine itself?

tnsping taking long...

Manoj Kaparwan, February 24, 2011 - 8:02 pm UTC

Tom,

Load on the DB machine is normal. ( sorry i could post the stats now as I am not connected ).

it is a 01 CPU dual core system. yesterday we took system stats and user process just eating up around 10% of the CPU throughout when issue was seen. Memory utilization was also normal.

regards



Tom Kyte
February 25, 2011 - 8:28 am UTC

right, but how many things were trying to connect then - if you are having what we refer to as a connection storm (on a small machine like that - it would only take a 'drizzle') you'll get a machine that isn't showing much CPU but is overloaded trying to create processes and get things on the cpu's. Can you say if the machine was basically "idle - no one else is trying to use it" or not?

tnsping taking long...

Manoj Kaparwan, February 24, 2011 - 8:14 pm UTC

Tom,

As we see the issue at 02 points

a) when we do TNSPING from DB host machine
b) when we connect from DB host machine using OS authentication

Root cause might be same for both of the above.

Some signal pointing to location of the oracle binaries? we have installed these on NAS share. would there be call to oracle binaries in scenarios above (a) and (b)?

but again above stuff ( binaries on NAS share) can be ruled out as we have data files also on the same NAS ( though different file system ). and never seen TX delays ( e.g. commit taking forever etc etc)


at one point we moved tnsnames.ora and listener.ora to local disk ( /var/opt/oracle) - but that didnt improved.


Tom Kyte
February 25, 2011 - 8:32 am UTC

Some signal pointing to location of the oracle binaries? we have installed
these on NAS share. would there be call to oracle binaries in scenarios above
(a) and (b)?


Yes, there would be - but - if the NAS is correctly configured it would be as fast or faster than local disk.

what is the contents of your sqlnet.ora? Not listener, not tnsnames - the sqlnet.ora

tnsping taking long...

Manoj Kaparwan, February 25, 2011 - 12:19 am UTC

Tom,

Finally we installed Oracle binaries on Local disk and pointed DB to new binaries solved the issue.

So it was NAS share housing the binaries was causing the issue.

question
a)If there were delays in Oracle binary calls why the same is not faced on data files mount which is still on the same NAS ( commit delays etc )?

regards

Tom Kyte
February 25, 2011 - 8:42 am UTC

There were likely delays on the oracle datafiles I would guess but you don't see them due to the fact that DBWR is the one writing to them and most of your data comes out of the cache in all probability.

but you'd have to ask your OS guys why the system paging in bits of text from a binary might be adversely affected more so than any other write/read request.

tnsping taking long...

Manoj Kaparwan, February 25, 2011 - 3:12 pm UTC

Tom,

yes true.

File system cache on the db host machine might be hiding the delays on the datafiles read/writes.

we will ask our OS guy the same on why binaries were affected more so than any other write/read request.

regards