This is the 14th day of my participation in the August More Text Challenge

1. Questions:

The following error message is displayed when the database server or client is connected to the database:

ORA-00020:maiximun number of process(xxxx)  exceeded.
Copy the code

2. Reasons:

The number of connections to the database exceeds the maximum number set for the database. The default value of this parameter is 150. The value should be adjusted based on service conditions.

3. Solutions

3.1 Stop database listening to prevent new connections.

You can also stop the listening service in Windows.

[oracle@dj ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-Aug -2021 23:08:26 Copyright (C) 1991, 2013, Version 11.2.0.4.0 - Production on 14-Aug -2021 23:08:26 Copyright (C) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) The command completed  successfullyCopy the code

3.2 Stopping database clients or applications that are not needed temporarily releases database connections.

If the connection cannot be released within a short period of time, restart the database service to release the connection and evaluate the impact on the production environment.

3.3 Entering the Database to change the number of connections. (To change the connection number, restart the database.)

- check the database connection number of SQL > show the parameter the processes the NAME, TYPE, VALUE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 - backup parameter file (change the number of connections is too large, the database cannot be started, according to quickly recover the backup database) SQL > create pfile = '/ home/oracle/pfile20210814 ora' from spfile. SQL> ALTER system set processes=500 scope=spfile; SQL> startup force ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2252824 bytes Variable Size 469766120 bytes Database Buffers 771751936 bytes Redo Buffers 8892416 bytes Database Mounted. Database opened. SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 500Copy the code

PS: After 11G R2, session=1.5* PROCESSES +22, which is changed with processes. No manual change is required. If 150 is not enough to expand to 500 or 800, and if not enough to expand to 1000 or 2000, consider whether the application connection pool is set properly and whether it is timely to reclaim and release connections.