Question: After an ORA-20 “maximum number of processes (%s) exceeded” occurs … no new connections (even SYSDBA or SYSOPER) may be made to the instance. How to connect Oracle Database in that case?
An ORA-20 “maximum number of processes (%s) exceeded” occurs when the number of OS processes for the instance exceeds the PROCESSES parameter
WORKAROUNDS
UNIX:
kill -9 one or more of the client connections.
WINDOWS:
using ORAKILL … kill one or more of the client connections.
Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and
SQL> alter system kill session 'SID, SERAL#';
CASE STUDY
GET A STABLE NUMBER OF PROCESSES AGAINST WHICH TO RUN THE TEST
SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 04:44:37 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> STARTUP ORACLE instance started. Total System Global Area 631914496 bytes Fixed Size 1301756 bytes Variable Size 331350788 bytes Database Buffers 293601280 bytes Redo Buffers 5660672 bytes Database mounted. Database opened.
SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 5 log_archive_max_processes integer 4 processes integer 150
SQL> select count(*) from v$process; COUNT(*) ---------- 24
SET PROCESSES TO COUNT + 1
SQL> alter system set processes = 25 scope = spfile; System altered.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup ORACLE instance started. Total System Global Area 631914496 bytes Fixed Size 1301756 bytes Variable Size 331350788 bytes Database Buffers 293601280 bytes Redo Buffers 5660672 bytes Database mounted. Database opened.
SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 5 log_archive_max_processes integer 4 processes integer 25
SQL> select count(*) from v$process; COUNT(*) ---------- 23
CONNECT WITH A NEW SESSION
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from v$process; COUNT(*) ---------- 24
This brings our session count up to PROCESSES -1
ATTEMPT TO CONNECT WITH A 3RD SESSION
$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 04:50:48 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ERROR: ORA-00020: maximum number of processes (%s) exceeded
$ sqlplus / as sysoper SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 04:51:43 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ERROR: ORA-00020: maximum number of processes (%s) exceeded
$ sqlplus -prelim / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 04:55:26 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. SQL> select count(*) from v$process; select count(*) from v$process * ERROR at line 1: ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 SQL> EXIT
RESOLVE THE CONDITION
$ ps -ef | grep KBCOOK oracle 17555 1 0 04:48 ? 00:00:00 ora_pmon_KBCOOK oracle 17557 1 0 04:48 ? 00:00:00 ora_vktm_KBCOOK oracle 17561 1 0 04:48 ? 00:00:00 ora_diag_KBCOOK oracle 17563 1 0 04:48 ? 00:00:00 ora_dbrm_KBCOOK oracle 17565 1 0 04:48 ? 00:00:00 ora_psp0_KBCOOK oracle 17569 1 0 04:48 ? 00:00:00 ora_dia0_KBCOOK oracle 17571 1 0 04:48 ? 00:00:00 ora_mman_KBCOOK oracle 17573 1 0 04:48 ? 00:00:00 ora_dbw0_KBCOOK oracle 17575 1 0 04:48 ? 00:00:00 ora_lgwr_KBCOOK oracle 17577 1 0 04:48 ? 00:00:00 ora_ckpt_KBCOOK oracle 17579 1 0 04:48 ? 00:00:00 ora_smon_KBCOOK oracle 17581 1 0 04:48 ? 00:00:00 ora_reco_KBCOOK oracle 17583 1 0 04:48 ? 00:00:00 ora_mmon_KBCOOK oracle 17585 1 0 04:48 ? 00:00:00 ora_mmnl_KBCOOK oracle 17587 1 0 04:48 ? 00:00:00 ora_d000_KBCOOK oracle 17589 1 0 04:48 ? 00:00:00 ora_s000_KBCOOK oracle 17608 1 0 04:48 ? 00:00:00 ora_smco_KBCOOK oracle 17610 1 0 04:48 ? 00:00:00 ora_fbda_KBCOOK oracle 17612 1 0 04:48 ? 00:00:00 ora_qmnc_KBCOOK oracle 17641 1 0 04:48 ? 00:00:00 ora_q000_KBCOOK oracle 17671 1 0 04:49 ? 00:00:00 ora_q001_KBCOOK oracle 17761 1 0 04:50 ? 00:00:00 oracleKBCOOK (LOCAL=NO) oracle 18480 1 0 04:58 ? 00:00:00 ora_w000_KBCOOK oracle 18608 17126 0 05:00 pts/1 00:00:00 grep KBCOOK
$ kill -9 17761
$ ps -ef | grep KBCOOK oracle 17555 1 0 04:48 ? 00:00:00 ora_pmon_KBCOOK oracle 17557 1 0 04:48 ? 00:00:00 ora_vktm_KBCOOK oracle 17561 1 0 04:48 ? 00:00:00 ora_diag_KBCOOK oracle 17563 1 0 04:48 ? 00:00:00 ora_dbrm_KBCOOK oracle 17565 1 0 04:48 ? 00:00:00 ora_psp0_KBCOOK oracle 17569 1 0 04:48 ? 00:00:00 ora_dia0_KBCOOK oracle 17571 1 0 04:48 ? 00:00:00 ora_mman_KBCOOK oracle 17573 1 0 04:48 ? 00:00:00 ora_dbw0_KBCOOK oracle 17575 1 0 04:48 ? 00:00:00 ora_lgwr_KBCOOK oracle 17577 1 0 04:48 ? 00:00:00 ora_ckpt_KBCOOK oracle 17579 1 0 04:48 ? 00:00:00 ora_smon_KBCOOK oracle 17581 1 0 04:48 ? 00:00:00 ora_reco_KBCOOK oracle 17583 1 0 04:48 ? 00:00:00 ora_mmon_KBCOOK oracle 17585 1 0 04:48 ? 00:00:00 ora_mmnl_KBCOOK oracle 17587 1 0 04:48 ? 00:00:00 ora_d000_KBCOOK oracle 17589 1 0 04:48 ? 00:00:00 ora_s000_KBCOOK oracle 17608 1 0 04:48 ? 00:00:00 ora_smco_KBCOOK oracle 17610 1 0 04:48 ? 00:00:00 ora_fbda_KBCOOK oracle 17612 1 0 04:48 ? 00:00:00 ora_qmnc_KBCOOK oracle 17641 1 0 04:48 ? 00:00:00 ora_q000_KBCOOK oracle 17671 1 0 04:49 ? 00:00:00 ora_q001_KBCOOK oracle 18480 1 0 04:58 ? 00:00:00 ora_w000_KBCOOK
$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 05:01:48 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>