ORA-01555 问题记录

发表于 2015-07-09   |   分类于 Oracle   |  

undo_segment_and_consistent_read

产生 ORA-01555 错误

Oracle 在什么情况下会产生 ORA-01555 :
ORA-01555 Snapshot Too Old — Oracle Tips by Burleson Consulting
Oracle ORA-01555快照过旧的错误

问题描述与错误日志

场景还原

对方系统频繁地调用我方的 webservice 方法，
我方将接收到的 xml 文件解析成对象，
然后将对象更新（Update）到 Oracle11gR2 中，
一次调用大概会持续 2~3 小时。数据是以持久化（单条更新）的方式存入到中间库中。

错误日志（ORA-01555）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
2015-07-07 08:58:38.643 [INFO ] FileUtil - 文件写入成功：D:\runtime\receive-xmls\2015-07-07\SendInPatInfo-》3571335（1） 75979646680981.xml
2015-07-07 08:58:41.908 [ERROR] Patient - 4935430(1) 写入体温记录出错:2015-06-26 06:00:00
================================================================================
com.lansle.xdo.sql.SQLOperationException: com.lansle.sql.ESQLException: java.sql.SQLException: ORA-01555: 快照过旧: 回退段号 61 (名称为 "_SYSSMU61_278601726$") 过小

	at com.lansle.xdo.sql.Operator.executeUpdate(Operator.java:808)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:688)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:636)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:1028)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:999)
	at com.xinglintech.nis.dhadapter.Patient.save(Patient.java:527)
	at com.xinglintech.nis.dhadapter.DataReciever.SendInPatInfo(DataReciever.java:65)
	at sun.reflect.GeneratedMethodAccessor195.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:212)
	at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:117)
	at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
	at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
	at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:181)
	at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:172)
	at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:146)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584)
	at java.lang.Thread.run(Thread.java:595)
[   Time   ] 2015-07-07 08:58:41.908
[Attributes] 

	+------+---------------+
	| bean | ${see follow} |
	+------+---------------+

	+---------------------+---------------------+-----------------------------------------------------+
	| bean                | ${see follow}       | com.xinglintech.nis.dhadapter.entities.Temperatures |
	+---------------------+---------------------+-----------------------------------------------------+
	| bean.lastmodifytime | 2015-07-07 02:40:34 | java.lang.String                                    |
	+---------------------+---------------------+-----------------------------------------------------+
	| bean.patientid      | 4935430             | java.lang.String                                    |
	+---------------------+---------------------+-----------------------------------------------------+
	| bean.recodedate     | 2015-06-26 06:00:00 | java.lang.String                                    |
	+---------------------+---------------------+-----------------------------------------------------+
	| bean.value          | 36.4                | double                                              |
	+---------------------+---------------------+-----------------------------------------------------+
	| bean.visitid        | 1                   | java.lang.String                                    |
	+---------------------+---------------------+-----------------------------------------------------+


Caused by:
com.lansle.sql.ESQLException: java.sql.SQLException: ORA-01555: 快照过旧: 回退段号 61 (名称为 "_SYSSMU61_278601726$") 过小

	at com.lansle.sql.SQLUpdate.executeUpdate(SQLUpdate.java:821)
	at com.lansle.sql.SQLBase.executeUpdate(SQLBase.java:353)
	at com.lansle.xdo.sql.Operator.executeUpdate(Operator.java:800)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:688)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:636)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:1028)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:999)
	at com.xinglintech.nis.dhadapter.Patient.save(Patient.java:527)
	at com.xinglintech.nis.dhadapter.DataReciever.SendInPatInfo(DataReciever.java:65)
	at sun.reflect.GeneratedMethodAccessor195.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:212)
	at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:117)
	at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
	at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
	at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:181)
	at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:172)
	at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:146)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584)
	at java.lang.Thread.run(Thread.java:595)
[   Time   ] 2015-07-07 08:58:41.908
[Attributes] 

	+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------+
	| sql | update TEMPERATURES set PATIENTID = ?, VISITID = ?, RECODEDATE = ?, VALUE = ?, LASTMODIFYTIME = ? where PATIENTID = ? and VISITID = ? and RECODEDATE = ? |
	+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------+


Caused by:
java.sql.SQLException: ORA-01555: 快照过旧: 回退段号 61 (名称为 "_SYSSMU61_278601726$") 过小

	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
	at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)
	at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
	at com.lansle.sql.SQLUpdate.executeUpdate(SQLUpdate.java:813)
	at com.lansle.sql.SQLBase.executeUpdate(SQLBase.java:353)
	at com.lansle.xdo.sql.Operator.executeUpdate(Operator.java:800)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:688)
	at com.lansle.xdo.sql.SQLEntity.store(SQLEntity.java:636)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:1028)
	at com.xinglintech.nis.dhadapter.z.SQLUtil.save(SQLUtil.java:999)
	at com.xinglintech.nis.dhadapter.Patient.save(Patient.java:527)
	at com.xinglintech.nis.dhadapter.DataReciever.SendInPatInfo(DataReciever.java:65)
	at sun.reflect.GeneratedMethodAccessor195.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:212)
	at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:117)
	at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
	at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
	at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:181)
	at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:172)
	at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:146)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584)
	at java.lang.Thread.run(Thread.java:595)


================================================================================



2015-07-07 08:59:09.378 [INFO ] FileUtil - 开始写入文件：D:\runtime\receive-xmls\2015-07-07\SendInPatInfo-》3571710（1） 76010384605013.xml

解决方案

查看 alert 日志文件（XML与TXT）

1
2
SQL> select value from v$diag_info where name ='Diag Alert';
SQL> select value from v$diag_info where name ='Diag Trace';

查看 undo tablespace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
C:\Users\Admin>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on 星期三 7月 8 10:57:22 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  
输入口令: 

连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

查看 maxquerylen 最大值

可以通过v$undostat视图的maxquerylen列查询在过去的一段时间内，最长的查询执行的时间（以秒为单位）。UNDO_RETENTION参数中的时间设置应该至少与maxquerylen列中给出的时间一样长。

1
SQL> select max(maxquerylen) from v$undostat

根据 maxquerylen 设置 UNDO_RETENTION

1
2
--设置保留时间为30分钟（1800秒）
SQL> ALTER SYSTEM SET UNDO_RETENTION = 1800;

UNDO_RETENTION 参数的较高值并不保证撤销数据保留 UNDO_RETENTION 参数指定的时间。为保证撤销保留指定的时间，可以使用RETENTION GRARANTEE子句。

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations,
you can enable retention guarantee. If retention guarantee is enabled,
the specified minimum undo retention is guaranteed;
the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.
If retention guarantee is not enabled,
the database can overwrite unexpired undo when space is low,
thus lowering the undo retention for the system. This option is disabled by default.

WARNING:
Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.
Or, you can later specify this clause in an ALTER TABLESPACE statement.
You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace.
A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

说明：

假如数据库中用RETENTION GUARANTEE子句配置了保证保留撤销。如果撤销表空间太小不能满足使用它的所有活动事务，那么会发生以下情况：
如果撤销表空间用完85％，Oracle将发布一个自动表空间警告
当撤销表空间用完97％时，Oracle将发布一个自动表空间严重警告
所有DML语句将不允许，并且会接收到一个空间超出错误
DDL语句允许继续执行

参考文献

#ORA-01555 #Oracle
根据端口号来杀死进程
Excel VBA: 提取最后一连串数字