Stream 同步错误之解决方案 ORA-00001 ORA-26787 ORA-26786

197次阅读

共计 19111 个字符，预计需要花费 48 分钟才能阅读完成。

stream 是 Oracle 11g 支持的数据同步技术，虽然该技术已经不是什么新技术，但目前国内采用该技术开发的软件不多见。stream 同步软件项目参与近一年，近期软件上线实施，效果不是很理想。同步过程中会偶尔出现 ORA-00001，ORA-26786，ORA-26787 等常见错误。经过几天的的研究，开发了一个守护平台, 采用 Java 平台和存储过程相结合的方式自动处理以上错误。我写的这些算法具有通用性，不需要手工指定删除表的列, 通过实践具有较好的效果。相对于 set_update_conflict_handler 这个方法, 简单易用。java 平台代码便不开源了，其实核心的思想还是在存储过程中，特分享给大家，共同进步。

自定义 type

create or replace type myvarray_list as varray(300) of varchar2(50)

CREATE OR REPLACE PROCEDURE EXECUTE_TRANSACTION_1(applyname IN VARCHAR2,ltxnid IN VARCHAR2) IS
i NUMBER;
x NUMBER;
loopdog NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
rowlcr SYS.LCR$_ROW_RECORD;
typenm VARCHAR2(61);
res NUMBER;
command VARCHAR2(10);
old_values SYS.LCR$_ROW_LIST;
new_values SYS.LCR$_ROW_LIST;
v_code NUMBER;
v_errm VARCHAR2(1024);
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
key_column myvarray_list;
remove_column myvarray_list;
remove_flag NUMBER;
remove_count NUMBER;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE(‘ — Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘ — Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘ —Error in Message: ‘|| msgno);
DBMS_OUTPUT.PUT_LINE(‘ —Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘ —Message Text: ‘||errmsg);
i := msgno;
loopdog :=0;
WHILE i <= msgcnt LOOP
loopdog :=loopdog+1;
DBMS_OUTPUT.PUT_LINE(‘—message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); — gets the LCR
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE(‘type name: ‘ || typenm);
IF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
command := rowlcr.GET_COMMAND_TYPE();
DBMS_OUTPUT.PUT_LINE(‘command type name: ‘ || command);
IF command = ‘INSERT’ THEN
rowlcr.SET_COMMAND_TYPE(‘DELETE’);
new_values := rowlcr.GET_VALUES(‘new’);
— Set the old values in the row LCR to the new values in the row LCR
rowlcr.SET_VALUES(‘old’, new_values);
— Set the old values in the row LCR to NULL
rowlcr.SET_VALUES(‘new’, NULL);
old_values := rowlcr.GET_VALUES(‘old’);
— Apply the row LCR as an DELETE FROM the table
object_name :=rowlcr.GET_OBJECT_NAME();
object_owner :=rowlcr.GET_OBJECT_OWNER();
key_column := myvarray_list(); –init array.
i :=1;
FOR emm IN (select DISTINCT CO.COLUMN_NAME from
DBA_cons_columns CO, dba_constraints PK
where CO.constraint_name = PK.constraint_name
AND CO.OWNER = PK.OWNER
AND PK.constraint_type IN (‘P’,’C’,’U’)
and PK.table_name = object_name
AND PK.OWNER=object_owner) LOOP
DBMS_OUTPUT.PUT_LINE(‘key column ‘ || emm.COLUMN_NAME);
key_column.extend;
key_column(i) := emm.COLUMN_NAME;
i :=i+1;
END LOOP;
remove_column := myvarray_list(); –init array.
remove_count := 1;
FOR i in 1..old_values.count LOOP
IF old_values(i) IS NOT NULL THEN
–DBMS_OUTPUT.PUT_LINE(‘old(‘ || i || ‘): ‘ || old_values(i).column_name);
x :=1;
remove_flag :=1;
WHILE x <= key_column.count AND remove_flag=1 loop
–dbms_output.put_line(‘key_column(‘||x||’)=’||key_column(x));
IF old_values(i).column_name = key_column(x) THEN
remove_flag :=0;
END IF;
x :=x +1;
END loop;
IF remove_flag = 1 then
remove_column.extend;
remove_column(remove_count) := old_values(i).column_name;
remove_count :=remove_count+1;
END IF;
END IF;
END LOOP;
FOR x in 1..remove_column.count loop
dbms_output.put_line(‘remove_column(‘||x||’)=’||remove_column(x));
rowlcr.DELETE_COLUMN(remove_column(x),’old’);
END loop;
rowlcr.EXECUTE(true);
END IF;
BEGIN
–dbms_apply_adm.execute_all_errors(applyname);
dbms_apply_adm.EXECUTE_ERROR(ltxnid);
return;
EXCEPTION when OTHERS then
SELECT MESSAGE_NUMBER INTO i FROM DBA_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid;
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 1024);
DBMS_OUTPUT.PUT_LINE(‘Error message: ‘ || v_errm);
IF loopdog > msgcnt then
RAISE_APPLICATION_ERROR(-20002,’Insert or Delete error. please check your procedure.’);
ELSIF v_code = -1 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-1): ‘ || v_code);
–null;
ELSIF v_code = -26786 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26786): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20786,v_errm);
ELSIF v_code = -26787 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26787): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20787,v_errm);
ELSE
RAISE_APPLICATION_ERROR(-20000,v_errm);
END IF;
END;
END IF;
END LOOP; –loop

END EXECUTE_TRANSACTION_1;
– 需要这两个权限
–grant select on dba_constraints to DWESBSTREAMUSER;
–granT select on DBA_cons_columns to DWESBSTREAMUSER;

ORA-00001 错误是目标端存在数据, 但是存在冲突列, 这里储存储过程的思想是根据唯一列删除目标端的对应行, 并将 LCR 是 old 值插入到目标端.

———————————————————————————————————————————–

CREATE OR REPLACE PROCEDURE EXECUTE_TRANSACTION_26786(applyname IN VARCHAR2,ltxnid IN VARCHAR2) IS
i NUMBER;
x NUMBER;
loopdog NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
rowlcr SYS.LCR$_ROW_RECORD;
typenm VARCHAR2(61);
res NUMBER;
command VARCHAR2(10);
old_values SYS.LCR$_ROW_LIST;
new_values SYS.LCR$_ROW_LIST;
v_code NUMBER;
v_errm VARCHAR2(1024);
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
key_column myvarray_list;
remove_column myvarray_list;
remove_flag NUMBER;
remove_count NUMBER;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE(‘— Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘— Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—Error in Message: ‘|| msgno);
DBMS_OUTPUT.PUT_LINE(‘—Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—Message Text: ‘||errmsg);
i := msgno;
loopdog :=0;
WHILE i <= msgcnt LOOP
loopdog :=loopdog+1;
DBMS_OUTPUT.PUT_LINE(‘—message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); — gets the LCR
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE(‘type name: ‘ || typenm);
IF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
command := rowlcr.GET_COMMAND_TYPE();
DBMS_OUTPUT.PUT_LINE(‘command type name: ‘ || command);
IF command IN (‘DELETE’,’UPDATE’) THEN
rowlcr.SET_COMMAND_TYPE(‘DELETE’);
old_values := rowlcr.GET_VALUES(‘old’);
— Set the new values in the row LCR to NULL
rowlcr.SET_VALUES(‘new’, NULL);
— Apply the row LCR as an DELETE FROM the table —
object_name :=rowlcr.GET_OBJECT_NAME();
object_owner :=rowlcr.GET_OBJECT_OWNER();
—————————————————————————————
key_column := myvarray_list(); –init array.
i :=1;
FOR emm IN (select DISTINCT CO.COLUMN_NAME from
DBA_cons_columns CO, dba_constraints PK
where CO.constraint_name = PK.constraint_name
AND CO.OWNER = PK.OWNER
AND PK.constraint_type IN (‘P’,’C’,’U’)
and PK.table_name = object_name
AND PK.OWNER=object_owner) LOOP
DBMS_OUTPUT.PUT_LINE(‘key column ‘ || emm.COLUMN_NAME);
key_column.extend;
key_column(i) := emm.COLUMN_NAME;
i :=i+1;
END LOOP;
———————————————————————————–
remove_column := myvarray_list(); –init array.
remove_count := 1;
FOR i in 1..old_values.count LOOP
IF old_values(i) IS NOT NULL THEN
–DBMS_OUTPUT.PUT_LINE(‘old(‘ || i || ‘): ‘ || old_values(i).column_name);
x :=1;
remove_flag :=1;
WHILE x <= key_column.count AND remove_flag=1 loop
–dbms_output.put_line(‘key_column(‘||x||’)=’||key_column(x));
IF old_values(i).column_name = key_column(x) THEN
remove_flag :=0;
END IF;
x :=x +1;
END loop;
IF remove_flag = 1 then
remove_column.extend;
remove_column(remove_count) := old_values(i).column_name;
remove_count :=remove_count+1;
END IF;
END IF;
END LOOP;

—————————————————————————–
FOR x in 1..remove_column.count loop
dbms_output.put_line(‘remove_column(‘||x||’)=’||remove_column(x));
rowlcr.DELETE_COLUMN(remove_column(x),’old’);
END loop;
rowlcr.EXECUTE(true);
DBMS_OUTPUT.PUT_LINE(‘DELETE target old new ‘);
— INSERT NEW DATA–
rowlcr.SET_COMMAND_TYPE(‘INSERT’);
rowlcr.SET_VALUES(‘new’,old_values);
rowlcr.SET_VALUES(‘old’,NULL);
rowlcr.EXECUTE(true);
DBMS_OUTPUT.PUT_LINE(‘insert source old new’);
END IF;
BEGIN
–dbms_apply_adm.execute_all_errors(applyname);
dbms_apply_adm.EXECUTE_ERROR(ltxnid);
return;
EXCEPTION when OTHERS then
SELECT MESSAGE_NUMBER INTO i FROM DBA_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid;
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 1024);
DBMS_OUTPUT.PUT_LINE(‘Error message: ‘ || v_errm);
IF loopdog > msgcnt then
RAISE_APPLICATION_ERROR(-20002,’Insert or Delete error. please check your procedure.’);
ELSIF v_code = -26786 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26786): ‘ || v_code);
ELSIF v_code = -26787 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26787): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20787,v_errm);
ELSIF v_code = -1 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-1): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20001,v_errm);
ELSE
RAISE_APPLICATION_ERROR(-20000,v_errm);
END IF;
END;
END IF;
END LOOP;

END EXECUTE_TRANSACTION_26786;

ORA-26786 思想与 ORA-00001 思想类似, 不再赘述.

———————————————————————————————————————————————————————————-

CREATE OR REPLACE PROCEDURE EXECUTE_TRANSACTION_26787(applyname IN VARCHAR2,ltxnid IN VARCHAR2) IS
i NUMBER;
loopdog NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
rowlcr SYS.LCR$_ROW_RECORD;
typenm VARCHAR2(61);
res NUMBER;
command VARCHAR2(10);
old_values SYS.LCR$_ROW_LIST;
new_values SYS.LCR$_ROW_LIST;
v_code NUMBER;
v_errm VARCHAR2(1024);
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE(‘— Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘— Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—Error in Message: ‘|| msgno);
DBMS_OUTPUT.PUT_LINE(‘—Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—Message Text: ‘||errmsg);
i := msgno;
loopdog :=0;
WHILE i <= msgcnt LOOP
loopdog :=loopdog+1;
DBMS_OUTPUT.PUT_LINE(‘–message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); — gets the LCR
–print_lcr(lcr);
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE(‘type name: ‘ || typenm);
IF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
command := rowlcr.GET_COMMAND_TYPE();
DBMS_OUTPUT.PUT_LINE(‘command type name: ‘ || command);
IF command = ‘DELETE’ THEN
— Set the command_type in the row LCR to INSERT
rowlcr.SET_COMMAND_TYPE(‘INSERT’);
old_values := rowlcr.GET_VALUES(‘old’);
— Set the old values in the row LCR to the new values in the row LCR
rowlcr.SET_VALUES(‘new’, old_values);
— Set the old values in the row LCR to NULL
rowlcr.SET_VALUES(‘old’, NULL);
— Apply the row LCR as an INSERT into the hr.emp_del table
rowlcr.EXECUTE(true);
ELSIF command = ‘UPDATE’ THEN
BEGIN
old_values := rowlcr.GET_VALUES(‘old’);
new_values := rowlcr.GET_VALUES(‘new’);
rowlcr.EXECUTE(true);
rowlcr.SET_VALUES(‘new’, old_values);
rowlcr.SET_VALUES(‘old’, new_values);
rowlcr.EXECUTE(true);
EXCEPTION when OTHERS then
rowlcr.SET_COMMAND_TYPE(‘INSERT’);
rowlcr.SET_VALUES(‘new’, old_values);
— Set the old values in the row LCR to NULL
rowlcr.SET_VALUES(‘old’, NULL);
rowlcr.EXECUTE(true);
END;
END IF;
BEGIN
–dbms_apply_adm.execute_all_errors(applyname);
dbms_apply_adm.execute_error(ltxnid);
return;
EXCEPTION when OTHERS then
SELECT MESSAGE_NUMBER INTO i FROM DBA_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid;
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 1024);
DBMS_OUTPUT.PUT_LINE(‘Error message: ‘ || v_errm);
IF loopdog > msgcnt then
RAISE_APPLICATION_ERROR(-20002,’Insert or Delete error. please check your procedure.’);
ELSIF v_code = -26787 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26787): ‘ || v_code);
–null;
ELSIF v_code = -26786 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-26786): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20786,v_errm);
ELSIF v_code = -1 then
DBMS_OUTPUT.PUT_LINE(‘Error code(-1): ‘ || v_code);
RAISE_APPLICATION_ERROR(-20001,v_errm);
ELSE
RAISE_APPLICATION_ERROR(-20001,v_errm);
END IF;
END;
END IF;
END LOOP;