流配置异常(ORA-01403)的完整解决过程

最新推荐文章于 2022-08-31 10:39:04 发布

cuishi3166

最新推荐文章于 2022-08-31 10:39:04 发布

阅读量1.3k

点赞数

文章标签：数据库

转自: yangtingkun blog

在检查DBA_APPLY_ERROR视图，发现了很多应用失败的事务，失败错误原因为：ORA-01403: no data found。

数据库的流配置在应用阶段设置DISABLE_ON_ERROR参数为N，应用虽然可以跳过错误的事务继续执行与之不相关的事务，但是受错误影响而报错的事务会越来越多。

系统的流环境不是我搭建的，虽然近来一段时间虽然看了一些流的概念，对流的工作原理大致清楚，但是基本上没有实际动手配置过流环境，流环境的维护操作也不是很熟悉。因此，只能临时抱佛脚，一边查询Oracle的文档，一边解决实际问题。所幸的是，用了两天半的时间（琐碎的事情比较多，不可能把全部时间投在一件事情上，两天半的时间还包括大半天的新系统上线的工作，汗），终于解决了问题。

[@more@]

首先，简单描述一下环境。

数据库版本为Oracle9204 for Solaris，配置流环境的时候打了一个补丁3339368。

流环境比较简单，是单向的流复制。有两个源数据库对于一个应用数据库。出问题的是其中一个源数据库和应用数据库。

问题产生：

前一段时间由于开发人员的疏忽，在更新的时候漏掉了一个WHERE语句，导致系统负荷突然增加，3个小时内产生的REDO日志的数量是平时一天的20多倍。这对流的捕获来说，绝对是一个灾难，虽然被海量更新的表不是需要捕获的表，但是在捕获进程仍然要处理比平时大得多的数据量。

可能是由于压力比较大的原因，导致了后台出现了两个ORA-600错误。

Errors in file /opt/oracle/admin/zjtrade/bdump/zjtrade_cp01_18471.trc:

ORA-00600: internal error code, arguments: [knlcEnqueueLCR250], [3023165202], [0], [3023191099], [0], [], [], []

而后，在应用端则出现了ORA-01403: no data found。

在METALINK上查询了ORA-600错误，发现这个问题似乎是Oracle的bug。

在Metalink上查到的文档信息：https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=2892926.8&blackframe=1

这篇文档是bug2892926的一个支持性描述文档，包含的内容比较简单：

Bug 2892926 Streams may fail with OERI [knlcEnqueueLCR250]

This note gives a brief overview of bug 2892926.

Affects:

*Product (Component)*	Oracle Server (Rdbms)
*Range of versions believed* to be affected**	Versions < 10G
*Versions confirmed* as being affected**	9.2.0.4
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

9.2.0.5 (Server Patch Set)

10g Production Base Release

Symptoms:

Internal Error may occur (ORA-600)

ORA-600 [knlcEnqueueLCR250]

Related To:

Streams / Logical Standby

Description

无论从版本还是问题描述都可以确定是这个问题。但是可惜的是，bug2892926并没有公布。因此，问题的产生原因、导致的问题，以及如何解决全都不明。Oracle提供了一个PATCH，但是似乎是为了解决LOB问题的，而我们系统中没有包含LOB，似乎并不对症。最关键的时，我现在即使给系统打上一个补丁，也没有办法解决现有的问题了。

在METALINK唯一的收获是确认了导致错误的原因是bug，而解决问题则需要靠自己了。

在METALINK上没有什么收获，但是Oracle的官方文档中，记录流应用异常的处理方式。其中就包含了如何处理ORA-01403错误的方法。

Oracle在文档上是这样描述的：

ORA-01403 No Data Found

Typically, this error occurs when an update is attempted on an existing row and the OLD_VALUES in the row LCR do not match the current values at this target site.

To correct this problem, you can update the current values in the row so that the row LCR can be applied successfully. If changes to the row are captured by a capture process at the destination database, then you probably do not want apply this manual change at destination sites.

简单的说，是由于Oracle再更新或删除数据时，WHERE条件中指定的查询条件和数据库中实际的值不匹配。而Oracle给出的解决方法就是手工修改这些值，使得数据库中的数据于LCR中的OLD_VALUE一致。

由于目标数据库没有再设置捕获进程，因此可以省略Oracle给出的设置SET_TAG的步骤，直接UPDATE数据库的值。

虽然Oracle给出了方面，但是Oracle并没有给出具体的实施步骤。要找到数据库中出现错误的数据，以及流应用中LCR中的OLD_VALUE。

考虑使用LOGMNR，但是在目的站点使用LOGMNR无法获得更新0行的DML语句。而源数据站点的日志数据量比较大，很难确定具体是那些数据出现的错误。

因此，只能考虑使用流本身的机制来检查错误。

继续查询流的文档，发现Displaying Detailed Information About Apply Errors这章里面Oracle给出了检查LCR具体数值的方法。

Oracle提供了4个过程，通过调用过程可以将DBA_APPLY_ERROR中指定的或所有的错误信息通过DBMS_OUTPUT包打印到屏幕上。

由于篇幅的限制，Oracle本身提供的4个过程就不列出来了，有兴趣的可以参考Oracle的官方文档。

看起来问题解决了，其实问题才刚刚开始。设置输出的最大值SET SERVEROUTPUT ON SIZE 1000000之后，执行了Oracle提供的PIRNT_TRANSACTION包。

EXEC PRINT_TRANSACTION('13.48.47355')

我还没有直接打印所有的错误信息，而是仅仅对其中一个事务出现的错误进行了打印，但是输出没有结束就报错了。

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

ORA-06512: 在 "SYS.DBMS_OUTPUT", line 32

ORA-06512: 在 "SYS.DBMS_OUTPUT", line 97

ORA-06512: 在 "SYS.DBMS_OUTPUT", line 112

SQL> SELECT ERROR_MESSAGE, MESSAGE_COUNT FROM DBA_APPLY_ERROR WHERE ROWNUM = 1;

ERROR_MESSAGE MESSAGE_COUNT
----------------------------------- --------------------------
ORA-01403: no data found 21815

虽然是一条错误信息，但是里面包含了21815条修改。数据量太大没有办法打印到屏幕上。而且，最重要的是，就算是可以完全打印到屏幕上，如此大的数据量，加上凌乱的格式，我也没办法找到想要的东西。

由于利用Oracle给出的过程没法办法找到问题的原因。因此只能另外想办法。

首先是考虑将得到的数据放到一个表中，因为通过SQL处理表中的数据是轻而易举的。其次是格式化得到的结构，最好将其转化为可以执行的SQL语句，因为这样更有利于找到问题所在。

于是我修改了Oracle提供的三个过程，将LCR中的数据变成执行的SQL语句，并将结果写到我建好的表中。代码如下：

SQL> CREATE SEQUENCE SEQ_MY_REC;

序列已创建。

SQL> CREATE TABLE MY_RECORDS (ID NUMBER, STATMENTS VARCHAR2(4000));

表已创建。

这个表用来保存解析处理的SQL语句。

SQL> CREATE OR REPLACE PROCEDURE MY_PRINT_ANY(P_DATA IN SYS.ANYDATA, P_STR IN OUT VARCHAR2, P_FLAG I
N OUT NUMBER) IS
2 V_TN VARCHAR2(61);
3 V_STR VARCHAR2(4000);
4 V_CHR CHAR(2000);
5 V_NUM NUMBER;
6 V_DAT DATE;
7 V_RW RAW(4000);
8 V_RES NUMBER;
9 BEGIN
10 V_TN := P_DATA.GETTYPENAME();
11 P_FLAG := 0;
12 IF V_TN = 'SYS.VARCHAR2' THEN
13 V_RES := P_DATA.GETVARCHAR2(V_STR);
14 IF V_STR IS NOT NULL THEN
15 P_STR := P_STR || '''' || V_STR || '''';
16 ELSE
17 P_STR := P_STR || ' NULL ';
18 P_FLAG := 1;
19 END IF;
20 ELSIF V_TN = 'SYS.CHAR' THEN
21 V_RES := P_DATA.GETCHAR(V_CHR);
22 IF V_CHR IS NOT NULL THEN
23 P_STR := P_STR || '''' || RTRIM(V_CHR) || '''';
24 ELSE
25 P_STR := P_STR || ' NULL ';
26 P_FLAG := 1;
27 END IF;
28 ELSIF V_TN = 'SYS.NUMBER' THEN
29 V_RES := P_DATA.GETNUMBER(V_NUM);
30 IF V_NUM IS NOT NULL THEN
31 P_STR := P_STR || V_NUM;
32 ELSE
33 P_STR := P_STR || ' NULL ';
34 P_FLAG := 1;
35 END IF;
36 ELSIF V_TN = 'SYS.DATE' THEN
37 V_RES := P_DATA.GETDATE(V_DAT);
38 IF V_DAT IS NOT NULL THEN
39 P_STR := P_STR || '''' || V_DAT || '''';
40 ELSE
41 P_STR := P_STR || ' NULL ';
42 P_FLAG := 1;
43 END IF;
44 ELSIF V_TN = 'SYS.VARCHAR' THEN
45 V_RES := P_DATA.GETVARCHAR(V_STR);
46 IF V_STR IS NOT NULL THEN
47 P_STR := P_STR || '''' || V_STR || '''';
48 ELSE
49 P_STR := P_STR || ' NULL ';
50 P_FLAG := 1;
51 END IF;
52 ELSIF V_TN = 'SYS.RAW' THEN
53 V_RES := P_DATA.GETRAW(V_RW);
54 IF V_RW IS NOT NULL THEN
55 P_STR := P_STR || '''' || V_RW || '''';
56 ELSE
57 P_STR := P_STR || ' NULL ';
58 P_FLAG := 1;
59 END IF;
60 ELSE
61 DBMS_OUTPUT.PUT_LINE('TYPENAME IS ' || V_TN);
62 END IF;
63 END;
64 /

过程已创建。

这个过程用来转化各种类型的数据，并将数据写到输入的字符串后面。

SQL> CREATE OR REPLACE PROCEDURE MY_PRINT_LCR(P_LCR IN SYS.ANYDATA) IS
2 V_TYPENM VARCHAR2(61);
3 V_ROWLCR SYS.LCR$_ROW_RECORD;
4 V_RES NUMBER;
5 V_NEWLIST SYS.LCR$_ROW_LIST;
6 V_OLDLIST SYS.LCR$_ROW_LIST;
7 V_INSERT_STR VARCHAR2(4000);
8 V_FLAG NUMBER DEFAULT 0;
9 V_COUNT NUMBER;
10 BEGIN
11 V_TYPENM := P_LCR.GETTYPENAME();
12 IF (V_TYPENM = 'SYS.LCR$_ROW_RECORD') THEN
13 V_RES := P_LCR.GETOBJECT(V_ROWLCR);
14 IF V_ROWLCR.GET_COMMAND_TYPE = 'DELETE' THEN
15 V_INSERT_STR := 'DELETE ' || V_ROWLCR.GET_OBJECT_OWNER || '.' || V_ROWLCR.GET_OBJECT_NAME ||
' WHERE ';
16 V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
17 FOR I IN 1..V_OLDLIST.COUNT LOOP
18 IF V_OLDLIST(I) IS NOT NULL THEN
19 V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ';
20 MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
21 IF V_FLAG = 1 THEN
22 V_COUNT := INSTR(V_INSERT_STR, '=', -1);
23 V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT -1) || 'IS' || SUBSTR(V_INSERT_STR, V_COU
NT + 1);
24 END IF;
25 V_INSERT_STR := V_INSERT_STR || ' AND ';
26 END IF;
27 END LOOP;
28 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_S
TR) - 4));
29 ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'INSERT' THEN
30 V_INSERT_STR := 'INSERT INTO ' || V_ROWLCR.GET_OBJECT_OWNER || '.' || V_ROWLCR.GET_OBJECT_N
AME || '( ';
31 V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
32 FOR I IN 1..V_NEWLIST.COUNT LOOP
33 IF V_NEWLIST(I) IS NOT NULL THEN
34 V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ', ';
35 END IF;
36 END LOOP;
37 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ') VALUES (';
38 FOR I IN 1..V_NEWLIST.COUNT LOOP
39 IF V_NEWLIST(I) IS NOT NULL THEN
40 MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
41 V_INSERT_STR := V_INSERT_STR || ', ';
42 END IF;
43 END LOOP;
44 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ')';
45 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, V_INSERT_STR);
46 ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'UPDATE' THEN
47 V_INSERT_STR := 'UPDATE ' || V_ROWLCR.GET_OBJECT_OWNER || '.' || V_ROWLCR.GET_OBJECT_NAME |
| ' SET ';
48 V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
49 FOR I IN 1..V_NEWLIST.COUNT LOOP
50 IF V_NEWLIST(I) IS NOT NULL THEN
51 V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ' = ';
52 MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
53 V_INSERT_STR := V_INSERT_STR || ', ';
54 END IF;
55 END LOOP;
56 V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ' WHERE ';
57 V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
58 FOR I IN 1..V_OLDLIST.COUNT LOOP
59 IF V_OLDLIST(I) IS NOT NULL THEN
60 V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ' ;
61 MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
62 IF V_FLAG = 1 THEN
63 V_COUNT := INSTR(V_INSERT_STR, '=', -1);
64 V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT -1) || 'IS' || SUBSTR(V_INSERT_STR, V_COU
NT + 1);
65 END IF;
66 V_INSERT_STR := V_INSERT_STR || ' AND ';
67 END IF;
68 END LOOP;
69 INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_S
TR) - 4));
70 END IF;
71 END IF;
72 END ;
73 /

过程已创建。

这个过程将LCR转化为可以执行的SQL语句，并将结果写到刚才的MY_RECORDS表中。

SQL> CREATE OR REPLACE PROCEDURE MY_PRINT_TRANSACTION(P_LTXNID IN VARCHAR2) IS
2 V_TXNID VARCHAR2(30);
3 V_SOURCE VARCHAR2(128);
4 V_MSGCNT NUMBER;
5 V_ERRNO NUMBER;
6 V_ERRMSG VARCHAR2(128);
7 V_LCR SYS.ANYDATA;
8 BEGIN
9 SELECT LOCAL_TRANSACTION_ID,
10 SOURCE_DATABASE,
11 MESSAGE_COUNT,
12 ERROR_NUMBER,
13 ERROR_MESSAGE
14 INTO V_TXNID, V_SOURCE, V_MSGCNT, V_ERRNO, V_ERRMSG
15 FROM DBA_APPLY_ERROR
16 WHERE LOCAL_TRANSACTION_ID = P_LTXNID;
17 DBMS_OUTPUT.PUT_LINE('----- LOCAL TRANSACTION ID: ' || V_TXNID);
18 DBMS_OUTPUT.PUT_LINE('----- V_SOURCE DATABASE: ' || V_SOURCE);
19 DBMS_OUTPUT.PUT_LINE('----ERROR NUMBER: '||V_ERRNO);
20 DBMS_OUTPUT.PUT_LINE('----MESSAGE TEXT: '||V_ERRMSG);
21 FOR I IN 1..V_MSGCNT LOOP
22 DBMS_OUTPUT.PUT_LINE('--MESSAGE: ' || I);
23 V_LCR := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(I, V_TXNID); -- GETS THE V_LCR
24 MY_PRINT_LCR(V_LCR);
25 END LOOP;
26 END;
27 /

过程已创建。

这个过程基本上没有做什么修改，主要目的是确定输出错误的数据。

下面只要简单的执行一下MY_PRINT_TRANSACTION，就可以将造成错误的SQL输出到指定的表中了。改写的过程已经完全贴出来了，考虑到篇幅问题，这里就不解释了。改写这几个过程加上调试大概花了我1/3的时间。

SQL> SELECT LOCAL_TRANSACTION_ID, ERROR_MESSAGE, MESSAGE_COUNT FROM DBA_APPLY_ERROR WHERE ROWNUM = 1;

LOCAL_TRANSACTION_ID ERROR_MESSAGE MESSAGE_COUNT
-------------------- --------------------------------- -------------
18.42.47210 ORA-01403: no data found 21818

SQL> EXEC MY_PRINT_TRANSACTION('20.29.47736')

SQL> SELECT * FROM MY_RECORDS WHERE ROWNUM = 1;

ID STATMENTS
---------- ----------------------------------------------------------
914 UPDATE NDMAIN.ORD_ORDER SET RECEIVE_TOTAL = 134.5, MODIFY_DA
TE = '2006-05-19 16:02:46' WHERE ORDER_ID = '6399C3DE35FD472
C8F59212C' and RECEIVE_TOTAL = 134.5 and MODIFY_DATE = '2006
-05-19 15:29:10'

现在已经得到了一个事务中所有的修改，只需要找到不一致的数据，将其改正过来就可以了，看来问题已经成功了一大半。

将一个事务中的所有SQL语句按照顺序写到MY_RECORDS表中，剩下的问题似乎就简单了。

通过调用下面的匿名块，就可以找到第一个引起ORA-1403错误的数据：

SQL> SET SERVEROUT ON
SQL> BEGIN
2 FOR I IN (SELECT ID, STATMENTS FROM MY_RECORDS ORDER BY ID) LOOP
3 EXECUTE IMMEDIATE I.STATMENTS;
4 IF SQL%ROWCOUNT = 0 THEN
5 DBMS_OUTPUT.PUT_LINE(I.ID);
6 ROLLBACK;
7 RETURN;
8 END IF;
9 END LOOP;
10 ROLLBACK;
11 END;
12 /
1

PL/SQL 过程已成功完成。

目前找到的第一个不匹配的数据就是这个事务的第一条记录，通过比较这条UPDATE语句中WHERE条件和数据库中存在的字段的值，就可以发现造成数据不一致的问题。

通过UPDATE语句，将数据库中的现有的数据的值进行更新，将其更新为LCR中得到的DML语句中WHERE条件后给出的值。这样，就可以保证Oracle在进行流的应用的时候不会报ORA-1043错误了。

将第一条不匹配数据更新后，再次执行这个过程，找到第二条不匹配的数据，按照同样的步骤处理，然后再次需要下一条，直到整个事务处理完成。

需要注意一点，由于所有的DML操作都是针对真实的数据，因此无论是在匿名块中还是在手工处理的时候一定要小心，DML操作后一概回滚，避免修改真正的数据。

在得到一个事务中所有不匹配记录的UPDATE语句后，可以执行这些UPDATE语句，然后调用Oracle提供的过程将错误事务再次执行。

SQL> BEGIN
2 DBMS_APPLY_ADM.EXECUTE_ERROR(
3 LOCAL_TRANSACTION_ID => '18.42.47210',
4 EXECUTE_AS_USER => FALSE);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

到目前为止似乎仍然很顺利。但是马上又有新的问题了。

首先一个问题就是调用MY_PRINT_TRANSACTION过程时间太长，生成一个事务的18000多条的LCR居然需要3、4个小时。这个问题还没来得及解决，就出现了更加严重的问题。

对于第一个事务，完全通过手工比对出不匹配的数据，幸运的是，第一个事务中，只找到5条不匹配的记录。

但是第二个事务中，通过手工的方式找了7、8个不匹配的数据，下一个出错的数据ID才到二十几，按照这个比率，18000多个SQL，需要找到不匹配的大概有几千，这个工作要是手工做不知道要花费多长的时间。

幸运的是，通过第一个事务的处理，已经当前事务中已经找到的不匹配的数据，发现了一个规律。几乎所有造成不匹配的数据都是一个最后修改日期发生了变化。

而且当前流应用站点对于的捕获站点只捕获了三张表的数据。因此可以尝试通过编写一个小的过程，自动获得改正不匹配的更新语句。

由于表的数量有限，且表名确定的话，需要更新的字段名称也确定下来，唯一定位一条记录的主键列也是可以确定的。而且，由于数据是因为不匹配造成的，我虽然很难清楚当前数据库中的值是多少，但是我只有根据LCR也就是MY_RECORDS中语句的WHERE条件中所带的值去更新，就可以解决不匹配的问题。而且，由于设置表级主键列的LOGGING，LCR中必然会包含主键列和相应的值。

根据上面这些条件，就可以编写出获取更新不匹配数据的UPDATE语句的匿名块了。

输入的SQL语句类型于：

UPDATE NDMAIN.ORD_ORDER SET RECEIVE_TOTAL = 9570.7, MODIFY_DATE = '2006-05-18 10:43:18' WHERE ORDER_ID = '2CA50BE524F7113713CD481D' and RECEIVE_TOTAL = 9570.7 and MODIFY_DATE = '2006-05-18 10:32:57'

而我需要得到的输出SQL语句类型于：

UPDATE NDMAIN.ORD_ORDER SET MODIFY_DATE = '2006-05-18 10:32:57' WHERE ORDER_ID = '2CA50BE524F7113713CD481D'

基本上输出语句所有的内容都包含在输入语句中。不过需要注意的是，输入语句可能是UPDATE也可能是DELETE语句。而且，还要根据不同的表名，进行不同的处理。

最后需要注意一点，对MY_RECORDS表中记录的DML操作在执行之后要进行回滚，但是用于更新不匹配数据的UPDATE语句还要保留下来，因此这里使用了自治事务。

SQL> CREATE TABLE MY_UPDATE (ID NUMBER, STATEMENTS VARCHAR2(4000));

表已创建。

SQL> CREATE OR REPLACE PROCEDURE P_GET_STATMENT (P_ID IN NUMBER, P_STR IN VARCHAR2) AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO MY_UPDATE VALUES (P_ID, P_STR);
5 COMMIT;
6 END;
7 /

过程已创建。

表MY_UPDATE用于保存得到的更新语句。过程P_GET_STATMENT设置为自治事务，保证得到UPDATE语句的操作和调用这个过程的父操作分开在不同的事务中，当前操作的提交不会影响到父事务的事务状态。

SQL> DECLARE
2 V_NUMBER NUMBER;
3 V_STR VARCHAR2(4000);
4 V_TEMP VARCHAR2(4000);
5 V_TABLENAME VARCHAR2(30);
6 BEGIN
7 FOR I IN (SELECT ID, STATMENTS FROM MY_RECORD ORDER BY ID) LOOP
8 BEGIN
9 EXECUTE IMMEDIATE I.STATMENTS;
10 EXCEPTION
11 WHEN DUP_VAL_ON_INDEX THEN
12 NULL;
13 END;
14
15 IF SQL%ROWCOUNT = 0 THEN
16 IF SUBSTR(I.STATMENTS, 1, 6) != 'INSERT' THEN
17 V_TEMP := I.STATMENTS;
18 V_NUMBER := INSTR(V_TEMP, 'NDMAIN.');
19 V_TEMP := SUBSTR(V_TEMP, V_NUMBER);
20 V_NUMBER := INSTR(V_TEMP, ' ');
21 V_TABLENAME := SUBSTR(V_TEMP, 8, V_NUMBER - 8);
22 V_STR := 'UPDATE NDMAIN.' || V_TABLENAME || ' SET ';
23 V_TEMP := SUBSTR(V_TEMP, INSTR(V_TEMP, 'WHERE') - 1);
24 IF V_TABLENAME = 'ORD_ORDER' THEN
25 V_NUMBER := INSTR(V_TEMP, 'MODIFY_DATE');
26 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 35) || ' WHERE ';
27 V_NUMBER := INSTR(V_TEMP, 'ORDER_ID');
28 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 37);
29 ELSIF V_TABLENAME = 'ORD_ORDER_ITEM' THEN
30 V_NUMBER := INSTR(V_TEMP, 'MODIFY_DATE');
31 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 35) || ' WHERE ';
32 V_NUMBER := INSTR(V_TEMP, 'RECORD_ID');
33 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 38);
34 ELSIF V_TABLENAME = 'ORD_ORDER_RECEIVE' THEN
35 V_NUMBER := INSTR(V_TEMP, 'RECEIVE_DATE');
36 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 36) || ' WHERE ';
37 V_NUMBER := INSTR(V_TEMP, 'ID');
38 V_STR := V_STR || SUBSTR(V_TEMP, V_NUMBER, 31);
39 ELSE
40 DBMS_OUTPUT.PUT_LINE(V_TABLENAME);
41 END IF;
42 P_GET_STATMENT(I.ID, V_STR);
43 END IF;
44 END IF;
45 END LOOP;
46 END;
47 /

PL/SQL 过程已成功完成。

SQL> ROLLBACK;

回退已完成。

SQL> SELECT COUNT(*) FROM MY_UPDATE;

COUNT(*)
----------
18327

得到了匹配的UPDATE语句，就可以尝试执行更新操作后调用Oracle的过程来重新执行错误的事务了。

但是现在仍然不能直接执行，由于目前的自动生成UPDATE语句的工作方式和手工执行的不大一样。自动生成是一个一次性循环操作，而手工执行更类似一个递归操作。因此自动生成的UPDATE语句包含了很多不必要的数据。简单的说，对于每个表的每个主键而言，MY_UPDATE表中只有第一次出现的UPDATE语句是有意义的。

下面通过一个简单的匿名块完成这个工作，然后就可以调用DBMS_APPLY_ADM.EXECUTE_ERROR过程了。

SQL> BEGIN
2 FOR I IN (SELECT ID, STATEMENTS FROM MY_UPDATE
3 WHERE ID IN
4 (
5 SELECT MIN(ID) FROM MY_UPDATE
6 GROUP BY SUBSTR(STATEMENTS, LENGTH(STATEMENTS) - 26)
7 )
8 ORDER BY ID) LOOP
9 BEGIN
10 EXECUTE IMMEDIATE I.STATEMENTS;
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE(I.ID);
14 DBMS_OUTPUT.PUT_LINE(SQLERRM);
15 END;
16 END LOOP;
17 END;
18 /

PL/SQL 过程已成功完成。

SQL> BEGIN
2 DBMS_APPLY_ADM.EXECUTE_ERROR(
3 LOCAL_TRANSACTION_ID => '18.1.48019',
4 EXECUTE_AS_USER => FALSE);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

至此，最棘手的问题已经基本解决，仅剩下一个性能方面的问题。

剩下一个性能方面的问题，说起来不是很重要，其实不解决也是不行的。

按照4个小时解决一个错误计算，一天最多处理4个。

由于现在已经存在很多事务被跳过，流在应用的时候发现新的相关的事务就会报错，因此，第二天又生成了6个新的错误。

按照这种速度计算，错误只会越处理越多。

通过查询DBA_JOBS_RUNNING视图，找到这个运行3、4个小时的JOB的SESSION信息：

SQL> SELECT SID, JOB FROM DBA_JOBS_RUNNING;

SID JOB
---------- ----------
24 85

检查一下，这个JOB到底在忙什么：

SQL> SELECT SID, SEQ#, EVENT FROM V$SESSION_WAIT WHERE SID = 24;

SID SEQ# EVENT
---------- ---------- --------------------------------------
24 8015 db file scattered read

SQL> SELECT SQL_TEXT FROM V$SQL
2 WHERE (HASH_VALUE, ADDRESS ) = (SELECT SQL_HASH_VALUE, SQL_ADDRESS FROM V$SESSION WHERE SID = 24);

SQL_TEXT
----------------------------------------------------------------------
SELECT qt.user_data FROM sys.apply$_error e, STRMADMIN.STREAMS_QUEUE_TABLE_T31 qt WHERE e.local_transaction_id = :1
AND e.aq_transaction_id = qt.enq_tid AND qt.q_name = :2 AND qt.step_no = :3

检查V$SESSION_WAIT发现会话在等待表扫描，而查询V$SQL视图找到当前运行的SQL语句。

这条SQL并没有出现在我改写的存储过程中，看来应该是Oracle过程中内部调用的SQL语句。观察了一下所有过程中对Oracle提供过程的调用，怀疑最可能是下面这句引起的：V_LCR := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(I, V_TXNID);

从SQL语句上看也基本上符合这个过程的含义。

下面看看这个SQL的执行计划：

SQL> explain plan for
2 SELECT qt.user_data FROM sys.apply$_error e, STRMADMIN.STREAMS_QUEUE_TABLE_T31 qt WHERE e.local_transaction_id = :1
3 AND e.aq_transaction_id = qt.enq_tid AND qt.q_name = :2 AND qt.step_no = :3;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| APPLY$_ERROR | | | |
|* 3 | INDEX UNIQUE SCAN | STREAMS$_APPLY_ERROR_UNQ | | | |
|* 4 | FILTER | | | | |
|* 5 | TABLE ACCESS FULL | STREAMS_QUEUE_TABLE_T31 | | | |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."LOCAL_TRANSACTION_ID"=:Z)
4 - filter("E"."AQ_TRANSACTION_ID"="QT"."ENQ_TID")
5 - filter("QT"."STEP_NO"=TO_NUMBER(:Z) AND "QT"."Q_NAME"=:Z)

Note: rule based optimization

20 rows selected.

数据字典表没有统计信息，采用了RBO进行优化，生成的执行计划性能比较差。

进一步观察发现，sys.apply$_error表中仅有9条记录，而STREAMS_QUEUE_TABLE_T31表中包含近20万条记录，而且，无论是关联列还是查询列上都没有建立索引。

SQL> select count(*) from dba_apply_error;

COUNT(*)
----------
9

SQL> select count(*) from STREAMS_QUEUE_TABLE_T31;

COUNT(*)
----------
196350

SQL> select index_name, column_name from user_ind_columns where table_name = 'STREAMS_QUEUE_TABLE_T31' order by 1, column_position;

INDEX_NAME COLUMN_NAME
----------------------------- ---------------------------------------
SYS_C003324 MSGID

对于普通的表只需要直接加上索引就可以了，但是表STREAMS_QUEUE_TABLE_T31是高级队列的基表。没有研究过高级队列，不清楚对这个表增加索引是否会对系统或队列造成不良影响，于是继续查询Oracle的高级队列文档，在文档上发现队列表的优化以及索引的建立和普通表没有区别，于是建立索引：

SQL> create index ind_stream_que_tab_t31_enqtid on STREAMS_QUEUE_TABLE_T31(enq_tid);

Index created.

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."LOCAL_TRANSACTION_ID"=:Z)
4 - filter("QT"."STEP_NO"=TO_NUMBER(:Z) AND "QT"."Q_NAME"=:Z)
5 - access("E"."AQ_TRANSACTION_ID"="QT"."ENQ_TID")

Note: rule based optimization

20 rows selected.

发现效果并不明显，查询发现enq_tid的选择性太差：

SQL> SELECT COUNT(DISTINCT ENQ_TID) FROM STRMADMIN.STREAMS_QUEUE_TABLE_T31;

COUNT(DISTINCTENQ_TID)
----------------------
8

SQL>SELECT COUNT(*) FROM (SELECT DISTINCT ENQ_TID, STEP_NO FROM STRMADMIN.STREAMS_QUEUE_TABLE_T31);

COUNT(*)
----------
174532

SQL> create index ind_str_q_tab_t31_enqtid_step on STREAMS_QUEUE_TABLE_T31(enq_tid, step_no);

Index created.

SQL> drop index ind_stream_que_tab_t31_enqtid;

Index dropped.

SQL> explain plan for SELECT qt.user_data FROM sys.apply$_error e, STRMADMIN.STREAMS_QUEUE_TABLE_T31 qt WHERE e.local_transaction_id = :1
2 AND e.aq_transaction_id = qt.enq_tid AND qt.q_name = :2 AND qt.step_no = :3
3 ;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."LOCAL_TRANSACTION_ID"=:Z)
4 - filter("QT"."Q_NAME"=:Z)
5 - access("E"."AQ_TRANSACTION_ID"="QT"."ENQ_TID" AND "QT"."STEP_NO"=TO_NUMBER(:Z))

Note: rule based optimization

20 rows selected.

加了索引之后，性能有了很大提高，原来3、4个小时的JOB，现在大约1分钟就完成了。

解决性能问题后，通过上面的给出方法，将所有的错误的事务再次应用。

至此，流环境中的ORA-01403错误完全的解决了。

前面的回复有人提醒我，流机制的异常处理也可以解决。由于对流机制本身不是很熟悉，所以就没有考虑这个方面，现在考虑，流的异常处理应该也是可以解决的。

这也正好是我这篇文章想要说明的一个问题。对于DBA来说，知识的储备是很重要的。我看到有些人再抱怨自己缺少环境，认为有了好的环境才能促进Oracle的学习。我倒是认为应该反过来，只有储备了足够的知识，才能使自己适应一个真正的环境。或者说，具备了足够的知识存储，自然可以很容易的找到一个期望中的好环境。

很多情况下，一些关键性操作是在产品库上进行，这个时候你只敢进行自己熟悉的操作，而且没有足够的知识储备，可能根本就想不到一些高效的解决方法。

以这个例子来说，由于我对流的环境不是很熟悉，我就不敢在产品库上直接应用流的冲突解决方案，而且由于时间的限制，我不可能有时间来先去学习，或者搭建测试环境，只能在产品库上直接修正错误。

这个时候就使考验DBA的时刻了，心细是第一位的，任何小的失误都可能造成不可预知的后果。足够的知识储备保证你有足够的解决问题的信心，还可以扩展解决问题的思路和方法，是解决实际问题的时候更加得心应手，减少出错的可能。当然，在充分的知识准备也不能预见所有的错误，任何人都会碰到没有见过的问题，快速的学习能力和分析、解决未知问题的能力也是必不可少的。

上面写了很多，不少是自己对DBA工作的一些感悟，不管是对是错，希望能给大家一点启示。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7185924/viewspace-977452/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7185924/viewspace-977452/