oracle11g连接数满导致数据库重启

现象：

版本：oracle11.2.0.3

今天数据库出现连接数满导致数据库重启的现象

PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:23:55 2013
ORA-00020: 超出最大进程数 (600)
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process RSM0 submission failed with error = 20
Mon Jan 21 16:23:55 2013
PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:25:07 2013
PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:25:09 2013
System State dumped to trace file /u01/app/ora11g/diag/rdbms/nticket1/nticket1/trace/nticket1_ora_9012.trc
Mon Jan 21 16:25:39 2013
ORA-00020: maximum number of processes (600) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Jan 21 16:26:02 2013
NOTE: deferred map free for map id 195475
Mon Jan 21 16:20:42 2013
LNS: Standby redo logfile selected for thread 1 sequence 17537 for destination LOG_ARCHIVE_DEST_3
Mon Jan 21 16:20:42 2013
LNS: Standby redo logfile selected for thread 1 sequence 17537 for destination LOG_ARCHIVE_DEST_5
Mon Jan 21 16:20:42 2013
Archived Log entry 87287 added for thread 1 sequence 17536 ID 0xe9b400d dest 1:
Mon Jan 21 16:21:34 2013
Process RSM0, PID = 28381, will be killed
Mon Jan 21 16:21:55 2013
ORA-00020: 超出最大进程数 (600)
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process RSM0 submission failed with error = 20
Mon Jan 21 16:22:55 2013
PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:23:55 2013
ORA-00020: 超出最大进程数 (600)
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process RSM0 submission failed with error = 20
Mon Jan 21 16:23:55 2013
PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:25:07 2013
PMON failed to acquire latch, see PMON dump
Mon Jan 21 16:25:09 2013
System State dumped to trace file /u01/app/ora11g/diag/rdbms/nticket1/nticket1/trace/nticket1_ora_9012.trc
Mon Jan 21 16:25:39 2013
ORA-00020: maximum number of processes (600) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Jan 21 16:26:02 2013
NOTE: deferred map free for map id 195475
Mon Jan 21 16:26:03 2013
NOTE: ASMB terminating
Errors in file /u01/app/ora11g/diag/rdbms/nticket1/nticket1/trace/nticket1_asmb_27856.trc:
ORA-15064: ? ASM ??????
ORA-03135: ??????
?? ID:
?? ID: 200 ???: 25
Errors in file /u01/app/ora11g/diag/rdbms/nticket1/nticket1/trace/nticket1_asmb_27856.trc:
ORA-15064: ? ASM ??????
ORA-03135: ??????
?? ID:
?? ID: 200 ???: 25
ASMB (ospid: 27856): terminating the instance due to error 15064
Mon Jan 21 16:26:03 2013
System state dump requested by (instance=1, osid=27856 (ASMB)), summary=[abnormal instance termination].
Dumping diagnostic data in directory=[cdmp_20130121162603], requested by (instance=1, osid=27856 (ASMB)), summary=[abnormal instance termination].
Mon Jan 21 16:26:03 2013
opiodr aborting process unknown ospid (32224) as a result of ORA-1092
Mon Jan 21 16:26:03 2013
opiodr aborting process unknown ospid (8936) as a result of ORA-1092
Mon Jan 21 16:26:03 2013
opiodr aborting process unknown ospid (9120) as a result of ORA-1092
Mon Jan 21 16:26:03 2013
ORA-1092 : opitsk aborting process
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (29775) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (31323) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (4171) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (4174) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (24379) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (23958) as a result of ORA-1092
Mon Jan 21 16:26:04 2013
opiodr aborting process unknown ospid (23616) as a result of ORA-1092
Mon Jan 21 16:26:05 2013
ORA-1092 : opitsk aborting process
Mon Jan 21 16:26:06 2013
License high water mark = 551
Mon Jan 21 16:26:06 2013
ORA-1092 : opitsk aborting process
Instance terminated by ASMB, pid = 27856
USER (ospid: 10330): terminating the instance
Instance terminated by USER, pid = 10330
Mon Jan 21 16:26:21 2013
Starting ORACLE instance (normal)  --数据库自动重启
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0

 

查找原因：

[oracle@nticket1 ~]$ oerr ora 3135
03135, 00000, "connection lost contact"
// *Cause:  1) Server unexpectedly terminated or was forced to terminate.
//          2) Server timed out the connection.
// *Action: 1) Check if the server session was terminated.
//          2) Check if the timeout parameters are set properly in sqlnet.ora.
[oracle@nticket1 ~]$ oerr ora 15064
15064, 00000, "communication failure with ASM instance"
// *Cause:  There was a failure to communicate with the ASM instance, most
//          likely because the connection went down.
// *Action: Check the accompanying error messages for more information on the
//          reason for the failure.  Note that database instances will always
//          return this error when the ASM instance is terminat

[oracle@nticket1 admin]$ oerr ora 1092
01092, 00000, "ORACLE instance terminated. Disconnection forced"
// *Cause:  The instance this process was connected to was terminated
//          abnormally, probably via a shutdown abort. This process
//          was forced to disconnect from the instance.
// *Action: Examine the alert log for more details. When the instance has been
//          restarted, retry action.

从现象可以看出，是连接数满致ASM instance通信失败而止，然后数据库实例也现终止，11gR2在这种情况下能自动的终断实例而重新启动。

 

最后解决：

1.经过查找连接超出限额都是伴随着大量的并发，是于大量的SQL没有绑定变量，导致出现share_pool不断飙升，最后数据库出现阻塞。让开发人员修改SQL，使用绑定变量，减少SQL硬解析。

2.适当地增加processes，并适当减少SGA。

 

相关文章：

http://yangtingkun.itpub.net/post/468/294181