Note: This article is very useful, it is very good, I managed the database has also appeared in a similar problem, and the method of this article is successfully solved by a surprise: 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 ..lage_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 user number: 850 left and right secondary faults: .... When the number of users reaches a certain number (more than 700) client Connecting the server Times ORA-12560 error, followed by ORA-03114 errors and cannot be connected to the server. The user who is connected to the server can access the database. .... First check the warning log file and not see the obvious error message.
.... View listner.log files, find the following information (many similar error records, pick two up) ........................ 02-JUL-2003 10:30:09 * (connection_data = (sID = orcl) (CID = ***. EXE) (Host = *******) (user = **** *)) * (Address = (protocol = tcp) (host = ******) (port = 1136)) * ESTABLISH * Orcl * 12500 TNS-12500: TNS: Listener Faled 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 = ***. EXE) (HOST = *****) (user = ***))) * (address = Protocol = TCP) (host = ******) (port = 1203)) * ESTABLISH * ORCL * 12500 TNS-12500: TNS: Listener Faled 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 time, users who have been connected to the server can be used normally. See that session reaches more than 760. .... After restarting the Oracle service, you can connect the number of new users, but when the number of concurrent users reaches more than 750, the same error is reported again: .... System resource exhaustion, means that the system is assigned to Oracle The memory is exhausted. Although we have 4G physical memory, the system can only assign 2G memory to Oracle under normal circumstances. In this 2G memory, including SGA, PGA and other Oracle need to use all memory. In exclusive mode, each session will separately assign about 2m memory. In this example, the SGA is assigned about 600M, assigns 2M memory calculations in each user, and when the number of connections reaches 750, the total allocation memory has reached 2G, and the number of new connections will not be added. If you want to solve this problem, if you don't make a big adjustment, you either decrease the SGA size, or reduce the memory size assigned to each session to connect to more users.
Four Solution Process: .... Review the Oracle documentation, a few solutions have been raised in the document: 1 Reset the init.ora parameter file, adjust the value of the following four parameters: .... Short_Area_size ... .hash_area_size .... bitmap_mege_area_size .... crete_bitmap_area_seze .... Open_CURSONE 2 Tune Size SGA Size 3 Reduce Oracle Job Queue Quantity (JOB_QUE_PROCESSES) and concurrent queues (paradel_max_servers) 4 Reset and reduce session / thread Stack Size 5 Replace Oracle to MTS Mode 6 Replace the operating system for Windows NT Enterprise Edition 7 Use Intel's ESMA hardware support, ie, use large memory ..) Use / 3G switch on the Intel system .. .... Combined with the specific situation of the present example, the main target of the adjustment is determined to reduce the user's PGA size. .... The main content of the PGA has short_area_size, hash_area_size, open_cursone, and Oracle stack and TNS stack. In this example, the sort zone is 64K, the HASH region is 128K (missing value), the number of open cursors is related to the application, but the Oracle stack and the TNS stack are 1m, but there is a big reduction Open. Therefore, the adjustment target is determined to reduce the size of these two stacks. .... Using the ORASTACK command to reduce the size of these two stacks: D: / Oracle / ORA81 / BIN> Orstack Oracle.exe 500000 Couldn't Open File with createfile () getLastError () == 32 .... oracle TNS service and stop the service, 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 and restart the service oracle TNS service Open the database, the user is connected to the server, and the database is still running normally when the number of users to 1350 or more, and the problem exists in this instance. Five Singer ... In fact, as indicated by Oracle documentation, there is a lot of way to increase the number of users, in addition to reducing the user stack, can also reduce SGA, or change into MTS mode, or use Third-party tools increase Oracle available memory.
