Oracle exceeds maximum number of connections and resolves

Oracle should be familiar with how to view and set the maximum number of connections for an Oracle database. Let's go over it again.

To view the current number of connections, you can use SELECT COUNT (*) from v$process;
Maximum number of connections set (default is $) Select value from v$parameter where name = ' processes ';
Modify the maximum number of connections alter system set processes = SPFile;

All know that when the maximum number of database connections is insufficient, client connection intermittent failure, error ORA-12519. Set a large point is generally possible. However, when doing large-scale projects will encounter some abnormal problems, such as: Set the maximum number of connections 800, but the normal connection of more than 200 will be an error, which I learned in an interview. Back because of their own garbage machine did not install Oracle, looked up some information, found that there is really this problem, but not a problem, seemingly a lot of people have met, it seems that we really do project too little, at most at the same time also tested more than 10 people.

A description of such problems is collected online, as follows.

Server IBM Xserver, Memory: 4G
The main parameters of the Oracle database configuration are as follows:

Processes integer 500
Sessions integer 585
Pga_aggregate_target Big Integer 350M
Sga_max_size Big Integer 1256M
Sga_target Big Integer 1152M
Shared_pool_size Big Integer 400M
Large_pool_size Big Integer 16M

Looks pretty rough configuration, but the default is that there will be insufficient number of connections. The number of connections to the database configuration is 500, but now when the number of Oracle connections reaches 120, the other clients will no longer be able to connect to the Oracle server and make a mistake: Ora-12518:tns: The listener cannot distribute the client; But has been connected to the application of the database can also be normal application, that is, the other is not connected to the same, unless the existing connection has exited, the other can be linked, that is, the total number of connections is 120;

A netizen said is in the Listener.ora can add a line: Direct_handoff_ttc_ =off, tested this method is still not;

During this time I tried to change the pga_aggregate_target to 200 or 500, and the result was the same. Also try to change the sga_target to 800m, the results of the database will not come up, suggesting that it should not be less than 1024m.

Later found that there may be Oracle 10g for Win32 a bug, the Internet patch, after the release of the patch is: 10.2.0.3; Also suspected that the Windows 2003 TCP connection is not enough, Internet search said it seems to have this problem, under a 2003 patch, The number of TCP connections is extended to 1000, the result can be reached 250 concurrent connections, but no more are not connected.

It is not clear exactly what parameter configuration is inappropriate (either oracle10g or Windows 2003), and it will not be able to connect when the maximum number of connections is actually not reached in Oracle settings.

Finally, the suspect is not a Windows 2003 (32-bit) problem, so installed a 64-bit Windows 2003, the same version of Oracle 10g, installed after the connection process parameters configured to 800, and then do the connection test, can have 800 sessions connected up, until now, can roughly summarize the crux of the problem is that the Windows 2003 server (32-bit) operating system, when installing Oracle 10g (10.2.0.1-10.2.0.3) problems, The number of connections cannot be as large as the system configuration and can be replaced by 64-bit. Finding the source of the problem, you can see why the Windows 2003 server (32-bit) operating system and Oracle 10g database connections will be limited, modify the configuration of 2003 or Oracle 10g configuration, can solve this problem.

Solution:
A. Modifying Oracle Parameters

1, the original parameters
#pga_aggregate_target =67108864
#processes =500
#sessions =555
# Pga_aggregate_target This parameter is to enlarge the number of concurrent connections that consume memory size.
# You can adjust this parameter, zoom in or out, and affect the number of concurrent.
# formula: approx = can follow the number of concurrent *4m
pga_aggregate_target=720m
# processes, Sessions is to expand the number of concurrent connections, is used at the same time.
# formula: Sessions = Processes *1.1 +5
processes=600
sessions=665
2. Add parameters to the Listener.ora file of the listening parameter file
Direct_handoff_ttc_listener = Off
3. Restart the database service.

B. Modifying the Windows configuration

1. Modify the boot. ini file in Windows system
/3gb/pae
Description: Modifying the boot. ini file in the operating system allows Oracle to use more memory space.
2. Modify the lock memory page size permission in user Group Policy.
Parameter modification complete problem solved.
3. Restart the computer.

Summary of the problem, from the network