现象:
版本: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。
--转自