流复制上线已经有段时间了,具体环境及详细配置见之前的blog,一直都没什么问题,今天早上过来的时候发现,apply进程abort掉了,propagation进程也报错,相关入队速度的问题,不过只是个警告,就没多过问,明天就要正式迁移应用到这个环境上了,于是抓紧时间排查,解决,具体过程如下:
查看告警日志发现从主库三个节点传送过来的归档日志,从早上5点多开始就没有应用了,一共差不多70个归档,之后尝试启动apply进程,起来之后没几秒钟又abort了,查询dba_apply_error表发现,里面出现了ORA-01403 no data found错误,查询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一致。
由此看来,现在最关键的就是要找到引起这个错误的那些sql语句,然后进行逆向操作就ok了,思路有了,下面是如何寻找到这些sql语句。
oracle官方提供的解决方法有4个过程,稍作变通,对这几个过程做了下修改,然后把需要的LCR中的OLD_VALUE转换为对应的sql语句,之后问题就好处理了。
首先创建临时表,用来存放用于逆向操作的sql语句:
SQL> CREATE SEQUENCE SEQ_MY_REC;
序列已创建。
SQL> CREATE TABLE MY_RECORDS (ID NUMBER, STATMENTS CLOB);
表已创建。
修改的第一个过程用来转化各种类型的数据,并将数据写到输入的字符串后面,具体脚本如下:
CREATE OR REPLACE PROCEDURE MY_PRINT_ANY(P_DATA IN SYS.ANYDATA,
P_STR IN OUT VARCHAR2,
P_FLAG IN OUT NUMBER) IS
V_TN VARCHAR2(61);
V_STR VARCHAR2(4000);
V_CHR CHAR(2000);
V_NUM NUMBER;
V_DAT DATE;
V_RW RAW(4000);
V_RES NUMBER;
BEGIN
V_TN := P_DATA.GETTYPENAME();
P_FLAG := 0;
IF V_TN = 'SYS.VARCHAR2' THEN
V_RES := P_DATA.GETVARCHAR2(V_STR);
IF V_STR IS NOT NULL THEN
P_STR := P_STR || '''' || V_STR || '''';
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSIF V_TN = 'SYS.CHAR' THEN
V_RES := P_DATA.GETCHAR(V_CHR);
IF V_CHR IS NOT NULL THEN
P_STR := P_STR || '''' || RTRIM(V_CHR) || '''';
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSIF V_TN = 'SYS.NUMBER' THEN
V_RES := P_DATA.GETNUMBER(V_NUM);
IF V_NUM IS NOT NULL THEN
P_STR := P_STR || V_NUM;
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSIF V_TN = 'SYS.DATE' THEN
V_RES := P_DATA.GETDATE(V_DAT);
IF V_DAT IS NOT NULL THEN
P_STR := P_STR || '''' || V_DAT || '''';
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSIF V_TN = 'SYS.VARCHAR' THEN
V_RES := P_DATA.GETVARCHAR(V_STR);
IF V_STR IS NOT NULL THEN
P_STR := P_STR || '''' || V_STR || '''';
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSIF V_TN = 'SYS.RAW' THEN
V_RES := P_DATA.GETRAW(V_RW);
IF V_RW IS NOT NULL THEN
P_STR := P_STR || '''' || V_RW || '''';
ELSE
P_STR := P_STR || ' NULL ';
P_FLAG := 1;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('TYPENAME IS ' || V_TN);
END IF;
END;
第二个过程将LCR转化为可以执行的SQL语句,并将结果写到刚才的MY_RECORDS表中,代码如下:
CREATE OR REPLACE PROCEDURE MY_PRINT_LCR(P_LCR IN SYS.ANYDATA) IS
V_TYPENM VARCHAR2(61);
V_ROWLCR SYS.LCR$_ROW_RECORD;
V_NEWLIST SYS.LCR$_ROW_LIST;
V_OLDLIST SYS.LCR$_ROW_LIST;
V_INSERT_STR CLOB;
V_FLAG NUMBER DEFAULT 0;
V_COUNT NUMBER;
BEGIN
V_TYPENM := P_LCR.GETTYPENAME();
IF (V_TYPENM = 'SYS.LCR$_ROW_RECORD') THEN
IF V_ROWLCR.GET_COMMAND_TYPE = 'DELETE' THEN
V_INSERT_STR := 'DELETE ' || V_ROWLCR.GET_OBJECT_OWNER || '.' ||
V_ROWLCR.GET_OBJECT_NAME || ' WHERE ';
V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
FOR I IN 1 .. V_OLDLIST.COUNT LOOP
IF V_OLDLIST(I) IS NOT NULL THEN
V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ';
MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
IF V_FLAG = 1 THEN
V_COUNT := INSTR(V_INSERT_STR, '=', -1);
V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT - 1) || 'IS' ||
SUBSTR(V_INSERT_STR, V_COUnt + 1);
END IF;
V_INSERT_STR := V_INSERT_STR || ' AND ';
END IF;
END LOOP;
INSERT INTO MY_RECORDS
VALUES
(SEQ_MY_REC.NEXTVAL,
SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_STR) - 4));
ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'INSERT' THEN
V_INSERT_STR := 'INSERT INTO ' || V_ROWLCR.GET_OBJECT_OWNER || '.' ||
V_ROWLCR.GET_OBJECT_NAME || '( ';
V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
FOR I IN 1 .. V_NEWLIST.COUNT LOOP
IF V_NEWLIST(I) IS NOT NULL THEN
V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ', ';
END IF;
END LOOP;
V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ') VALUES (';
FOR I IN 1 .. V_NEWLIST.COUNT LOOP
IF V_NEWLIST(I) IS NOT NULL THEN
MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
V_INSERT_STR := V_INSERT_STR || ', ';
END IF;
END LOOP;
V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ')';
INSERT INTO MY_RECORDS VALUES (SEQ_MY_REC.NEXTVAL, V_INSERT_STR);
ELSIF V_ROWLCR.GET_COMMAND_TYPE = 'UPDATE' THEN
V_INSERT_STR := 'UPDATE ' || V_ROWLCR.GET_OBJECT_OWNER || '.' ||
V_ROWLCR.GET_OBJECT_NAME || ' SET ';
V_NEWLIST := V_ROWLCR.GET_VALUES('NEW', 'N');
FOR I IN 1 .. V_NEWLIST.COUNT LOOP
IF V_NEWLIST(I) IS NOT NULL THEN
V_INSERT_STR := V_INSERT_STR || V_NEWLIST(I).COLUMN_NAME || ' = ';
MY_PRINT_ANY(V_NEWLIST(I).DATA, V_INSERT_STR, V_FLAG);
V_INSERT_STR := V_INSERT_STR || ', ';
END IF;
END LOOP;
V_INSERT_STR := RTRIM(V_INSERT_STR, ', ') || ' WHERE ';
V_OLDLIST := V_ROWLCR.GET_VALUES('OLD');
FOR I IN 1 .. V_OLDLIST.COUNT LOOP
IF V_OLDLIST(I) IS NOT NULL THEN
V_INSERT_STR := V_INSERT_STR || V_OLDLIST(I).COLUMN_NAME || ' = ';
MY_PRINT_ANY(V_OLDLIST(I).DATA, V_INSERT_STR, V_FLAG);
IF V_FLAG = 1 THEN
V_COUNT := INSTR(V_INSERT_STR, '=', -1);
V_INSERT_STR := SUBSTR(V_INSERT_STR, 1, V_COUNT - 1) || 'IS' ||
SUBSTR(V_INSERT_STR, V_COUNT + 1);
END IF;
V_INSERT_STR := V_INSERT_STR || ' AND ';
END IF;
END LOOP;
INSERT INTO MY_RECORDS
VALUES
(SEQ_MY_REC.NEXTVAL,
SUBSTR(V_INSERT_STR, 1, LENGTH(V_INSERT_STR) - 4));
END IF;
END IF;
END;
第三个过程基本上没有做什么修改,主要目的是确定输出错误的数据,代码如下:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-711843/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-711843/