sqlplus连接报错ora-20
来源:互联网 发布:怎样多申请几个淘宝号 编辑:程序博客网 时间:2024/06/05 01:53
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
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>
- sqlplus连接报错ora-20
- sqlplus连接oracle12c集群报错:ORA-12537: TNS:connection closed
- sqlplus连接数据库报ORA-21561: OID Generation Failed
- 从windows客户端sqlplus连接Oracle 12c PDB报错ORA-65162: The password has expired
- sqlplus "/ as sysdba"报错ORA-01031: insufficient privileges
- sqlplus / as sysdba 报错:ora-01031 insufficient privileges
- sqlplus可以连接,pl/sql 报ORA-12154: TNS:无法解析指定的连接标志符
- sqlplus可以连接,pl/sql 报ORA-12154: TNS:无法解析指定的连接标志符
- sqlplus连接报ORA-09925 Unable to create audit trail file错误处理
- Linux环境下使用sqlplus登录Oracle,报'ORA-12547: TNS:lost contact’丢失连接错误
- sqlplus登陆报ora-12560错误
- SQLPLUS报ORA-00900: 无效 SQL 语句
- plsql 连接 oracle 报错:ora-12514
- rman连接AUXILIARY报错ORA-12528
- navicat连接oracle报错ora-28547
- rman连接AUXILIARY报错ORA-12528
- c# 连接Oracle报错 ORA-00162
- SSIS连接Oracle报错:ORA-12154
- 短信网关接入程序源代码(SGIP协议)
- Java Spring Tutorial -- 注入Bean
- Codeforces Round #217 (diy.2) B. Berland Bingo
- 在目标板上移植动态库(-/bin/sh: XXX: not found 问题的解决)
- AndroidAnnotations——ClickEvents单击事件
- sqlplus连接报错ora-20
- 永远不要在Linux执行的10个最危险的命令
- 关于数据库中的sysobjects的解释
- ExecuteNonQuery: Connection 属性尚未初始化
- Adroid4.4之PowerManagerService分析
- SharePoint 2010 母版页定制小思路介绍
- Hibernate3.x openSession()与getCurrentSession()得到的连接的区别
- Copy List with Random Pointer
- linux scp基于ssh的网络传输