现象：

版本：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-1092Mon 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 = 10330Mon 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。

相关文章：