ORACLE Max connections

Oracle users should be familiar with how to view and set the maximum number of connections of Oracle databases. Here we will try again ?? Chaos? /P>

View the current number of connections. You can use select count (*) from v $ process;
Set the maximum number of connections (default value: 150) select value from v $ parameter where name = 'processs ';
Modify the maximum number of connections alter system set processes = 300 scope = spfile;

As we all know, when the maximum number of connections to the database is not enough, the client connection will fail intermittently, the error ORA-12519. Set the value to a greater value. However, some exceptions may occur during large-scale projects. For example, if you set the maximum number of connections to 800, an error will be reported if you connect more than 200 normally, this is what I learned during an interview. When I came back, I checked some information because I didn't install Oracle on my junk machine. I found that this problem still exists, but it is not a problem. It seems that many people have encountered it, it seems that there are too few projects, and at most more than 10 people will be tested at the same time.

Change system connections

Alter system set processses = 1000 scope = spfile;

The procedure and description are as follows:

Problem Description: An error is reported when the client connects to the database.

ORA-12516: TNS: listeners cannot find available handles matching protocol stacks

Solution process:

1. Check the current session count, processes, and sessions values, and find that the number of sessions and the values of the two parameters are very close.
SQL * Plus: Release 10.2.0.1.0-Production on Monday October 9 15:50:21 2006
Copyright (c) 1982,200 5, Oracle. All rights reserved.
SQL> conn/as sysdba
Connected.
SQL> select count (*) from v $ session;
COUNT (*)
----------
45

SQL> show parameter processes
NAME TYPE VALUE
---------------------------------------------------------------------
Aq_tm_processes integer 0
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 50
SQL> show parameter sessions
NAME TYPE VALUE
---------------------------------------------------------------------
Java_max_sessionspace_size integer 0
Java_soft_sessionspace_limit integer 0
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 60
Shared_server_sessions integer

2. Modify the values of processes and sessions

SQL> alter system set processes = 300 scope = spfile;
The system has been changed.
 
SQL> alter system set sessions = 300 scope = spfile;
The system has been changed.

3. View the processes and sessions parameters, but the changes do not take effect.

SQL> show parameter processes
NAME TYPE VALUE
---------------------------------------------------------------------
Aq_tm_processes integer 0
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 50
SQL> show parameter sessions
NAME TYPE VALUE
---------------------------------------------------------------------
Java_max_sessionspace_size integer 0
Java_soft_sessionspace_limit integer 0
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 60
Shared_server_sessions integer

4. Restart the database for the change to take effect

SQL> shutdown immediate

SQL> startup

SQL> show parameter processes
NAME TYPE VALUE
---------------------------------------------------------------------
Aq_tm_processes integer 0
Db_writer_processes integer 1
Gcs_server_processes integer 0
Job_queue_processes integer 10
Log_archive_max_processes integer 2
Processes integer 300
SQL> show parameter sessions
NAME TYPE VALUE
---------------------------------------------------------------------
Java_max_sessionspace_size integer 0
Java_soft_sessionspace_limit integer 0
License_max_sessions integer 0
License_sessions_warning integer 0
Logmnr_max_persistent_sessions integer 1
Sessions integer 335
Shared_server_sessions integer

The last test to increase the number of connections to 50,100 are not reported ORA-12516 error.

Sometimes we need to adjust the maximum number of connections of the oracle database, and the adjustment of the number of connections is the dbs under the oacle.
In the init. ora file.
The number of ORACLE connections (sessions) is related to the number of processes in the parameter file. Their relationships are as follows:
Sessions = (1.1 * process + 5)
However, when we increase the number of processes, the database often cannot be started. This is because we have missed a unix system parameter:
It is the semmns of the core parameter, which is the Semaphore parameter of the unix system. Each process occupies a semaphore. Semmns
After the adjustment, you need to restart the unix operating system to make the parameter take effect. However, its size is subject to hardware memory or ORACLE
SGA. Range: 200--2000.
However, the Processes modification should not only adjust the parameters in the init. ora file, but also adjust the OS kernel.
The parameters, such as AIX, HPUX, Solaris, SCO, and UNIXWare, are the same. OS adjustments must be restarted.
You cannot simply set the number of terminals to connect to the server,
The most important thing is to consider how many sessions will be connected at the same time (when using some middleware with shared connections, it is generally not needed to be too large ),
Of course, we also need to consider some Oracle background processes, and some system maintenance work requires more connections.
When oracle is adjusted on my atmp large front-end machine, it uses the unixware operating system and adjusts the number of links.
You must adjust the core parameters first.
The following figure shows how to adjust the key parameters:

SH Max 1000000000
SHMMIN 1
SHMMNI 200
SHMSEG 15
SEMMNI 1000
SEMMSL 300
SEMMNS 230
SEMOPM 20
Semmni, semmns, and semmsl must be increased, at least 18 larger than processes;

SEMMNI (150;): specifies the number of signals recognized in the core. This is the only method that can be activated at any given time.
1. Set the number of signals. The default value is 150. The maximum value is automatically adjusted by the system.
SEMMSL (25,300; 150): specifies the maximum semaphore value for each signal recognition. The default value is 25.
The sum of the PROCESSES of all the databases except the maximum db of SEMMNS + 2 * the PROCESSES of the maximum db + 10 * The number of instances. For example
If the number of processes is 100, 100, and 200 respectively, then = (100 + 100) + 2*200 + 10*3 = 630
SEMOPM (; 10): specifies the maximum number of signal operations that can be performed in each system call semop.
The default value is 10.
SHMMAX (132132,3741824; 524288): specifies the maximum part size of the shared memory.
Equal to 0.5 x physical memory bytes
SHMMNI (100;): specifies the maximum value of the shared memory identifier within the system range.
SHMSEG (6, 15, 6): specifies the number of shared memory blocks (or identifiers) associated with each process. The default value is 6. And each
The maximum value of shared memory blocks related to processes is related to the unused space of the process. Therefore, although a process has less
The shared memory block of the SHMSEG value. It may not be associated with other processes because of its limited space.
Adjusted:

Processes = 50 # SMALL
# Processes = 100 # MEDIUM
# Processes = 200 # LARGE

Among them, processes is the maximum number of connections to be adjusted. We only need to adjust this parameter. Other parameters can be
To retain the default value.

Note: windows 2003 (32-bit) has a problem.

Windows 2003 (32-bit), so a 64-bit windows 2003, the same version of oracle 10g, after the installation, the number of connections process parameter is configured to 800, then conduct a connection test, and we can always connect up to 800 sessions. So far, we can roughly summarize the crux of the problem, that is, the windows 2003 server (32-bit) operating system, there is a problem when installing oracle 10g (10.2.0.1-10.2.0.3). The number of connections cannot be as large as the system configuration. It can be replaced by 64-bit. After finding the root cause of the problem, you can figure out why windows 2003 server (32-bit) has a limit on the number of 10 GB database connections between the operating system and oracle. Modify the 2003 configuration or the 10 GB configuration of oracle, this problem can be solved.

Solution:

A. Modify Oracle parameters

1. Original parameters
# Pga_aggregate_target = 67108864
# Processes = 500
# Session= 555
# Pga_aggregate_target this parameter is used to increase the memory usage of concurrent connections.
# You can adjust this parameter to zoom in or out, and affect the number of concurrent jobs.
# Formula: approx = number of concurrent threads x 4 M
Pga_aggregate_target = 720 M
# Processes and sessions are used to increase the number of concurrent connections at the same time.
# Formula: sessions = processes * 1.1 + 5
Processes = 600
Sessions = 665
2. Add parameters to the LISTENER. ORA file.
Direct_handoff_ttc_listener = off
3. Restart the database service.

B. Modify windows configurations

1. Modify the Boot. Ini file in Windows.

/3 GB/PAE
Note: modifying the Boot. Ini file in the operating system allows Oracle to use more memory space.
2. Modify the memory page size permission in the user group policy.
Solve the problem after modifying the parameters.
3. Restart the computer.