ORA-00018: maximum number of sessions exceeded解决办法
主题：ORA-00018: maximum number of sessions exceeded发表于：2012-11-24 10:23 浏览 （20 ） 标签： Oracle session
#session 设置过小导致的问题：
#ORA-01075: you are currently logged on
#ORA-00018: maximum number of sessions exceeded
#Oracel默认Process的值为150，导致实际的Session值会超过Oracle的设置值（Session的数量是Process*1.1+5），所以会出错。
#解决方法：
# 将Process的值设置大一点
oracle@I2K155:~> lsnrctl
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-NOV-2012 09:43:18
Copyright (c) 1991, 2008, Oracle.All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "dr_i2kdb" has 1 instance(s).
Instance "i2kdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1052 refused:0 state:ready
LOCAL SERVER
Service "dr_i2kdb_XPT" has 1 instance(s).
Instance "i2kdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1052 refused:0 state:ready
LOCAL SERVER
Service "i2kdb" has 1 instance(s).
Instance "i2kdb", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:93730 refused:0
LOCAL SERVER
The command completed successfully
LSNRCTL>
oracle@I2K155:~> sqlplus system/h1w2D3B4@i2kdb
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Nov 24 09:41:56 2012
Copyright (c) 1982, 2008, Oracle.All rights reserved.
ERROR:
ORA-00018: maximum number of sessions exceeded
Enter user-name:
oracle@I2K155:~> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Nov 24 09:58:29 2012
Copyright (c) 1982, 2008, Oracle.All rights reserved.
ERROR:
ORA-01075: you are currently logged on
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
oracle@I2K155:~>
oracle@I2K155:~> oerr ora 00018
00018, 00000, "maximum number of sessions exceeded"
// *Cause:All session state objects are in use.
// *Action: Increase the value of the SESSIONS initialization parameter.
oracle@I2K155:~> ps -ef | grep ora | grep -v grep | grep -c LOCAL=NO
81
oracle@I2K155:~> ps -ef | grep ora | grep -v grep | grepLOCAL=NO
oracle 32631 10 Nov23 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 17880 10 01:03 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 17982 10 01:04 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18340 10 01:09 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18413 10 01:10 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18417 10 01:10 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18421 10 01:10 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18425 10 01:10 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle 18429 10 01:10 ? 00:00:00 oraclei2kdb (LOCAL=NO)
oracle@I2K155:~>
oracle@I2K155:~> ps -ef | grep ora | grep -v grep | grepLOCAL=NO | awk '{print $2}' | xargs kill -9
oracle@I2K155:~> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Nov 24 10:21:28 2012
Copyright (c) 1982, 2008, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> set pagesize 200 linesize 200
SQL> show parameter process
NAME TYPEVALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer0
cell_offload_processing booleanTRUE
db_writer_processes integer1
gcs_server_processes integer0
global_txn_processes integer1
job_queue_processes integer1000
log_archive_max_processes integer30注：这个设置大了，一般为4
processes integer150
SQL> show parameter session
NAME TYPEVALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer0
java_soft_sessionspace_limit integer0
license_max_sessions integer0
license_sessions_warning integer0
session_cached_cursors integer50
session_max_open_files integer10
sessions integer170 注：(processes *1.1 + 5)
shared_server_sessions integer
SQL> alter system set processes=500 scope=spfile;
System altered.
SQL> alter system set log_archive_max_processes=4 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 8351150080 bytes
Fixed Size 2176320 bytes
Variable Size 4714400448 bytes
Database Buffers3623878656 bytes
Redo Buffers 10694656 bytes
Database mounted.
Database opened.
SQL>
分享0 收藏0
转发到微博(0) | 转载 | 引用 |
吴述华
等级:
2 楼 回复于 2012-11-24 10:47
LOCAL=YES和LOCAL=NO 的区别：
oracle@i2ksvr-159:~> sqlplus/ as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 24 09:39:28 2012
Copyright (c) 1982, 2011, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
SQL>
i2ksvr-159:~ # ps -ef|grep ora | grep LOCAL
grid 1349 10 Nov23 ? 00:00:03 oracle+ASM_asmb_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid 11108 10 Nov22 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid 11420 10 Nov23 ? 00:00:00 oracle+ASM_o000_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 17457 174400 09:39 ? 00:00:00 oraclei2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle@i2ksvr-159:~> sqlplussystem/h1w2D3B4@//10.137.97.159:1521/i2kdb
SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 24 10:43:48 2012
Copyright (c) 1982, 2011, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
SQL>
i2ksvr-159:~ # ps -ef|grep ora | grep LOCAL
grid 1349 10 Nov23 ? 00:00:03 oracle+ASM_asmb_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid 11108 10 Nov22 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid 11420 10 Nov23 ? 00:00:00 oracle+ASM_o000_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19636 196350 10:42 ? 00:00:00 oraclei2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 25774 10 10:43 ? 00:00:00 oraclei2kdb (LOCAL=NO) Thank you very much for sharing!The good man!The good life of peace!
页:
[1]
