The Geek Diary

CONCEPTS | BASICS | HOWTO

How to Connect After ORA-20 has Occurred

By

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>

You May Also Like