ORACLE Max connections

Source: Internet
Author: User
Client database connection Error

Client database connection Error

Show parameter processes;
Then
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.