sqlplus连接报错ora-20

我就是我 2022-09-19 00:21 224阅读 0赞

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

Cause  
An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter  
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

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

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>