• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Connect After ORA-20 has Occurred

By admin

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>

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to change the SCAN IP address (SCAN VIP resources) in 11gR2 Grid (CRS) environment
  2. Oracle 11g – New ASM features
  3. CentOS / RHEL : Installing and Configuring ASMLib
  4. What Is Oracle Key Vault
  5. Oracle Interview Questions : Recovery catalog for RMAN backup
  6. Oracle Database – How to recover from a lost datafile with no backup
  7. How to Monitor SGA Memory on Oracle Pluggable Databases
  8. How to move spfile from ASM to filesystem
  9. How to Define PDB Listeners With Different Ports In a Oracle database Multitenant Setup
  10. Oracleasm Service Fails to Start After Upgrade to oracleasm-support-2.1.11-1 RPM Package

You May Also Like

Primary Sidebar

Recent Posts

  • MySQL: how to figure out which session holds which table level or global read locks
  • Recommended Configuration of the MySQL Performance Schema
  • MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
  • MySQL: How to kill a Long Running Query using max_execution_time
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary