|
主题: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) |
|