Skip to content

How to Connect After ORA-00020 has Occured

SYMPTOMS

 

After an ORA-20 “maximum number of processes (%s) exceeded” occurs … no new connections (even SYSDBA or SYSOPER) may be made to the instance

CHANGES

 

CAUSE

An ORA-20 “maximum number of processes (%s) exceeded” occurs when the number of OS processes for the instance exceeds the PROCESSES parameter

This is related to unpublished Bug 8518197: LOGINS NOT POSSIBLE AFTER ORA-20
and unpublished Bug 8518126: LOGINS NOT POSSIBLE AFTER ORA-20

SOLUTION

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (an enhancement reqeust has been filed)

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
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

[oracle@joshowar-lnx ~]$ 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>

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: