ORA-00018: maximum number of sessions exceeded解决办法

qmf

主题：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 | grep  LOCAL=NO
oracle   32631     1  0 Nov23 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   17880     1  0 01:03 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   17982     1  0 01:04 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18340     1  0 01:09 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18413     1  0 01:10 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18417     1  0 01:10 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18421     1  0 01:10 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18425     1  0 01:10 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle   18429     1  0 01:10 ?        00:00:00 oraclei2kdb (LOCAL=NO)
oracle@I2K155:~>
oracle@I2K155:~> ps -ef | grep ora | grep -v grep | grep  LOCAL=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         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes        integer  0
cell_offload_processing       boolean  TRUE
db_writer_processes       integer  1
gcs_server_processes       integer  0
global_txn_processes       integer  1
job_queue_processes       integer  1000
log_archive_max_processes      integer  30  注：这个设置大了，一般为4
processes        integer  150
SQL> show parameter session
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size      integer  0
java_soft_sessionspace_limit      integer  0
license_max_sessions       integer  0
license_sessions_warning      integer  0
session_cached_cursors       integer  50
session_max_open_files       integer  10
sessions        integer  170    注：(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 Buffers  3623878656 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     1  0 Nov23 ?        00:00:03 oracle+ASM_asmb_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid     11108     1  0 Nov22 ?        00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid     11420     1  0 Nov23 ?        00:00:00 oracle+ASM_o000_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   17457 17440  0 09:39 ?        00:00:00 oraclei2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle@i2ksvr-159:~> sqlplus  system/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     1  0 Nov23 ?        00:00:03 oracle+ASM_asmb_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid     11108     1  0 Nov22 ?        00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid     11420     1  0 Nov23 ?        00:00:00 oracle+ASM_o000_i2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   19636 19635  0 10:42 ?        00:00:00 oraclei2kdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   25774     1  0 10:43 ?        00:00:00 oraclei2kdb (LOCAL=NO)

fengerapple

Thank you very much for sharing!The good man!The good life of peace!