[Oracle] Oracle Error Case: TNS-00510

TNS-00510 Internal Limit restriction exceeded

Cause Too Many files or sockets open simultaneously (or some other resource has).

Action for further details, trace the operation for protocol details.

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

Cause the process of starting up a dedicated server process failed. The executable could is found or the environment may is set up incorrectly.

Action Turn on tracing in the ADMIN level and re-execute the operation. Verify that's Oracle Server executable is present and has execute permissions enabled. Ensure that's Oracle environment is specified correctly in LISTENER. ORA. The Oracle Protocol Adapter that are being called may isn't installed on the local hard drive. Check that the correct Protocol Adapter are successfully linked. If the error persists, contact Oracle Customer Support.

TNS-12540 tns:internal Limit restriction exceeded

Cause Too many TNS connections open simultaneously.

Action wait for connections to close and retry.

TNS-12560 Tns:protocol Adapter Error

Cause A Generic protocol adapter error occurred.

Action Check addresses used for proper protocol specification. Before reporting this error, look in the error stack and check for lower level transport errors. For further details, turn on tracing and re-execute the operation. Turn off Tracing the operation is complete.

Q: Does the number of processes cause a listener error?

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

Ibm/aix RISC system/6000 Error:11:resource temporarily unavailable

We found the error message as above in the Listener log. The maximum number of processes per user on the server is set to the maximum number of processes in Oracle is 600. The maximum utilization of the process is 482, and the maximum utilization per session is 490. The number of sessions is set to 840.

As I understand it, it seems that there is no problem with the number of processes. We have confirmed this by not building the memory paging space correctly on this machine, however, some people still tell us to increase the number of processes at the beginning. I disagree with this view. Did I miss something?

A: The TNS-510 error message shows that too many files or sockets are open at the same time (or some other resource is exhausted). This is a limitation of the operating system, not Oracle. You need to make sure that your AIX platform can handle so many files that you open on the server. This is a "all" file, not just an Oracle database file. Also, you need to make sure that your AIX server handles all the number of Oracle processes correctly, not just dedicated server processes. Oracle's starting parameter processes limits the number of dedicated server processes. But there are background processes (Smon, Pmon, LGWR, etc.) and processes that support parallel statements, not to mention your listeners. I think you should configure AIX to allow more processes for each UNIX user.

Summary: tns-12500,tns-12540,tns-12560,tns-00510 resolution process

An environment:

1 Platform: IBM ax360,4g Memory windows 2k advserver SP3 + Oracle 816 exclusive mode

2 memory allocation related parameters

.. processes................=.1000

.. shared_pool_size.........=.240000000

.. large_pool_size..........=.614400

.. java_pool_size...........=.32768

.. db_block_buffers.........=.90000

.. db_block_size............=.4096

.. log_buffer...............=.163840

.. Log_checkpoint_interval.. =.10000

.. sort_area_size...........=.65536

.. Sort_area_retained_size.. =.65536

.. open_cursors.............=.100

.. job_queue_processes......=.4

.. job_queue_interval.......=.10

.. max_dump_file_size.......=.10240

3 Maximum concurrent users: 850 or so

Two fault phenomena:

.... When the number of users reached a certain number (more than 700) Client Connection server times ora-12560 error, followed by the report ora-03114 error, can not connect to the server. Users who are connected to the server are now able to access the database normally.

.... First look at the warning log file and see no obvious error message.

.... View the Listner.log file and find the following information (many similar error records, pick two)

.........................

02-jul-2003 10:30:09 * (Connect_data= (SID=ORCL) cid= (program=***. EXE) (host=*******) (user=*****)) * (Address= (PROTOCOL=TCP) (host=*******) (port=1136)) * Establish * ORCL * 12500

Tns-12500:tns:listener failed to start a dedicated server process

. Tns-12540:tns:internal Limit restriction exceeded

.. Tns-12560:tnsrotocol Adapter Error

... Tns-00510:internal Limit restriction exceeded

.... 32-bit Windows error:8: Exec format Error

02-jul-2003 10:30:10 * (Connect_data= (SID=ORCL) cid= (program=***. EXE) (host=*****) (user=***)) * (Address= (PROTOCOL=TCP) (host=******) (port=1203)) * Establish * ORCL * 12500

Tns-12500:tns:listener failed to start a dedicated server process

. Tns-12540:tns:internal Limit restriction exceeded

.. Tns-12560:tnsrotocol Adapter Error

... Tns-00510:internal Limit restriction exceeded

.... 32-bit Windows error:8: Exec format Error

.....................

At this point, the original user connected to the server can also be used normally. View this session to reach more than 760.

.... The number of new users can be connected after restarting the Oracle service, but when the number of concurrent users reaches more than 750, the same error is reported again

Three reasons analysis:

.... The exhaustion of system resources means that the system allocated to Oracle is running out of memory. Although we have 4G of physical memory, it is normal for the system to allocate 2G of memory to Oracle, which includes all the memory that Oracle needs to use for the SGA, PGA, and so on. In exclusive mode, each session allocates about 2M of memory separately. In this case, the SGA is allocated about 600M, each user allocated 2 m memory calculation, the number of connections reached 750, the total allocated memory reached 2G, will no longer increase the number of new connections. To solve this problem, you can reduce the size of the SGA, or reduce the amount of memory allocated for each session, to connect more users, without a big adjustment.

Four-resolution process:

.... Looking at the Oracle documentation, several solutions are presented in the documentation:

1 Reset the Init.ora parameter file and reduce the value of the following four parameters:

... short_area_size

... hash_area_size

... bitmap_merge_area_size

... create_bitmap_area_seze

... open_cursone

2 small size of the SGA

3 Reduce the number of Oracle job queues (job_queue_processes) and concurrent queues (parallel_max_servers)

4 resetting and reducing the stack size used by the session/thread

5 Change Oracle to MTS mode

6 replacement operating system for Windows NT Enterprise Edition

7 using Intel's ESMA hardware support, even with large memory

.. 1 using the/3G switch on Intel systems

.. 2) using PSE36 memory

.... Based on this example, the main goal of the adjustment is to reduce the PGA size of the user.

.... The main components of the PGA are Short_area_size, Hash_area_size, Open_cursone, and Oracle Stacks and TNS Stacks. In this example, the sorted area is 128K (missing value), the number of cursors opened is related to the application and can not be 64k,hash, however, the Oracle stack and the TNS stack are 1M, but there is considerable scope for reduction. Therefore, the goal of the adjustment is to reduce the size of the two stacks.

.... Use the Orastack command to reduce the size of these two stacks:

D:\oracle\ora81\bin>orastack Oracle.exe 500000

Couldn ' t open file with CreateFile ()

GetLastError () = 32

.... Stop Oracle Services and TNS Services, and then run the above command

D:\oracle\ora81\bin>orastack Oracle.exe 500000

Dump of File Oracle.exe

Current Reserved Memory per Thread = 1048576

Current committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

D:\oracle\ora81\bin>orastack Tnslsnr.exe 500000

Dump of File Tnslsnr.exe

Current Reserved Memory per Thread = 1048576

Current committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

Restart the Oracle service and TNS service, open the database, the user connects to the server, after testing, the database still runs normally when the number of users is over 1350, which solves the problem of this example.

Five summary

.... In fact, as Oracle documents point out, there are many ways to increase the number of user connections, in addition to reducing the user stack, you can reduce the SGA, either by changing to MTS, or by using third-party tools to increase the available memory for Oracle. I've summarized earlier how to get Oracle to use more than 2G of memory on 32-bit Windows operating systems (http://www.itpub.net/showthread.php ...). 15&pagenumber=1), in this security application, it is advisable to combine both (reducing the user stack with increased Oracle available memory) to improve database performance. However, in this way, it is equally impossible to increase the number of user connections indefinitely. To achieve a larger number of user connections, you should use the MTS method.