Oracle Stream ORA-26786 ORA-01403 错误的解决

来源:互联网 发布:阿里云企业邮箱桌面版 编辑:程序博客网 时间:2024/05/01 23:33

最近公司开始用stream来做BI的数据同步,开始时还算顺利,做的downstream数据库表级复制,结果过了半个月要往里面加新的表,结果重启stream后destination库的apply进程开始报错,虽然DISABLE_ON_ERROR已经设置为N,但是错误越来越多,没有办法只好开始研究,从开始到最终解决问题过程比较曲折,还经历过重新impdp表,现在记录一下解决过程备忘。

      首先贴上几个过程,当时找了很久,metalink上找了半天,这个过程是用于输出apply的错误信息的,否则在dba_apply_error里面什么都看不到。

      1.print_any

[xhtml] view plaincopy
  1. CREATE OR REPLACE PROCEDURE print_any (DATA IN ANYDATA)  
  2.   IS  
  3.     tn    VARCHAR2 (61);  
  4.     str  VARCHAR2 (4000);  
  5.     CHR  VARCHAR2 (1000);  
  6.     num  NUMBER;  
  7.     dat  DATE;  
  8.     rw    RAW (4000);  
  9.     res  NUMBER;  
  10.  BEGIN  
  11.    IF DATA IS NULL  
  12.    THEN  
  13.        DBMS_OUTPUT.put_line ('NULL value');  
  14.        RETURN;  
  15.    END IF;  
  16.   
  17.    tn :DATA.gettypename ();  
  18.   
  19.    IF tn = 'SYS.VARCHAR2'  
  20.    THEN  
  21.        res :DATA.getvarchar2 (str);  
  22.        DBMS_OUTPUT.put_line (SUBSTR (str, 0, 253));  
  23.    ELSIF tn = 'SYS.CHAR'  
  24.    THEN  
  25.        res :DATA.getchar (CHR);  
  26.        DBMS_OUTPUT.put_line (SUBSTR (CHR, 0, 253));  
  27.    ELSIF tn = 'SYS.VARCHAR'  
  28.    THEN  
  29.        res :DATA.getvarchar (CHR);  
  30.        DBMS_OUTPUT.put_line (CHR);  
  31.    ELSIF tn = 'SYS.NUMBER'  
  32.    THEN  
  33.        res :DATA.getnumber (num);  
  34.        DBMS_OUTPUT.put_line (num);  
  35.    ELSIF tn = 'SYS.DATE'  
  36.    THEN  
  37.        res :DATA.getdate (dat);  
  38.        DBMS_OUTPUT.put_line (dat);  
  39.    ELSIF tn = 'SYS.RAW'  
  40.    THEN  
  41.  -- res :data.GETRAW(rw);  
  42.  -- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));  
  43.        DBMS_OUTPUT.put_line ('BLOB Value');  
  44.    ELSIF tn = 'SYS.BLOB'  
  45.    THEN  
  46.        DBMS_OUTPUT.put_line ('BLOB Found');  
  47.    ELSE  
  48.        DBMS_OUTPUT.put_line ('typename is ' || tn);  
  49.    END IF;  
  50.  END print_any;  

       2.print_errors

[xhtml] view plaincopy
  1. CREATE OR REPLACE PROCEDURE print_errors  
  2.   IS  
  3.     CURSOR c  
  4.     IS  
  5.         SELECT  local_transaction_id, source_database, message_number,  
  6.                 message_count, error_number, error_message  
  7.             FROM dba_apply_error  
  8.         ORDER BY source_database, source_commit_scn;  
  9.   
  10.    i        NUMBER;  
  11.    txnid    VARCHAR2 (30);  
  12.    SOURCE  VARCHAR2 (128);  
  13.    msgno    NUMBER;  
  14.    msgcnt  NUMBER;  
  15.    errnum  NUMBER        :0;  
  16.    errno    NUMBER;  
  17.    errmsg  VARCHAR2 (255);  
  18.    lcr      ANYDATA;  
  19.    r        NUMBER;  
  20.  BEGIN  
  21.    FOR r IN c  
  22.    LOOP  
  23.        errnum :errnum + 1;  
  24.        msgcnt :r.message_count;  
  25.        txnid :r.local_transaction_id;  
  26.        SOURCE :r.source_database;  
  27.        msgno :r.message_number;  
  28.        errno :r.error_number;  
  29.        errmsg :r.error_message;  
  30.        DBMS_OUTPUT.put_line  
  31.                          ('*************************************************');  
  32.        DBMS_OUTPUT.put_line ('----- ERROR #' || errnum);  
  33.        DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);  
  34.        DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);  
  35.        DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);  
  36.        DBMS_OUTPUT.put_line ('----Error Number: ' || errno);  
  37.        DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);  
  38.   
  39.        FOR i IN 1 .. msgcnt  
  40.        LOOP  
  41.          DBMS_OUTPUT.put_line ('--message: ' || i);  
  42.          lcr :DBMS_APPLY_ADM.get_error_message (i, txnid);  
  43.          print_lcr (lcr);  
  44.        END LOOP;  
  45.    END LOOP;  
  46.  END print_errors;  

       3.print_lcr

[c-sharp] view plaincopy
  1. CREATE OR REPLACE PROCEDURE print_lcr (lcr IN ANYDATA)  
  2.   IS  
  3.     typenm    VARCHAR2 (61);  
  4.     ddllcr    SYS.lcr$_ddl_record;  
  5.     proclcr    SYS.lcr$_procedure_record;  
  6.     rowlcr    SYS.lcr$_row_record;  
  7.     res        NUMBER;  
  8.     newlist    SYS.lcr$_row_list;  
  9.     oldlist    SYS.lcr$_row_list;  
  10.    ddl_text  CLOB;  
  11.    ext_attr  ANYDATA;  
  12.  BEGIN  
  13.    typenm := lcr.gettypename ();  
  14.    DBMS_OUTPUT.put_line ('type name: ' || typenm);  
  15.   
  16.    IF (typenm = 'SYS.LCR$_DDL_RECORD')  
  17.    THEN  
  18.        res := lcr.getobject (ddllcr);  
  19.        DBMS_OUTPUT.put_line (  'source database: '  
  20.                              || ddllcr.get_source_database_name  
  21.                            );  
  22.        DBMS_OUTPUT.put_line ('owner: ' || ddllcr.get_object_owner);  
  23.        DBMS_OUTPUT.put_line ('object: ' || ddllcr.get_object_name);  
  24.        DBMS_OUTPUT.put_line ('is tag null: ' || ddllcr.is_null_tag);  
  25.        DBMS_LOB.createtemporary (ddl_text, TRUE);  
  26.        ddllcr.get_ddl_text (ddl_text);  
  27.        DBMS_OUTPUT.put_line ('ddl: ' || ddl_text);  
  28.  -- Print extra attributes in DDL LCR  
  29.        ext_attr := ddllcr.get_extra_attribute ('serial#');  
  30.   
  31.        IF (ext_attr IS NOT NULL)  
  32.        THEN  
  33.          DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());  
  34.        END IF;  
  35.   
  36.        ext_attr := ddllcr.get_extra_attribute ('session#');  
  37.   
  38.        IF (ext_attr IS NOT NULL)  
  39.        THEN  
  40.          DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());  
  41.        END IF;  
  42.   
  43.        ext_attr := ddllcr.get_extra_attribute ('thread#');  
  44.   
  45.        IF (ext_attr IS NOT NULL)  
  46.        THEN  
  47.          DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());  
  48.        END IF;  
  49.   
  50.        ext_attr := ddllcr.get_extra_attribute ('tx_name');  
  51.   
  52.        IF (ext_attr IS NOT NULL)  
  53.        THEN  
  54.          DBMS_OUTPUT.put_line (  'transaction name: '  
  55.                                || ext_attr.accessvarchar2 ()  
  56.                                );  
  57.        END IF;  
  58.   
  59.        ext_attr := ddllcr.get_extra_attribute ('username');  
  60.   
  61.        IF (ext_attr IS NOT NULL)  
  62.        THEN  
  63.          DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());  
  64.        END IF;  
  65.   
  66.        DBMS_LOB.freetemporary (ddl_text);  
  67.    ELSIF (typenm = 'SYS.LCR$_ROW_RECORD')  
  68.    THEN  
  69.        res := lcr.getobject (rowlcr);  
  70.        DBMS_OUTPUT.put_line (  'source database: '  
  71.                              || rowlcr.get_source_database_name  
  72.                            );  
  73.        DBMS_OUTPUT.put_line ('owner: ' || rowlcr.get_object_owner);  
  74.        DBMS_OUTPUT.put_line ('object: ' || rowlcr.get_object_name);  
  75.        DBMS_OUTPUT.put_line ('is tag null: ' || rowlcr.is_null_tag);  
  76.        DBMS_OUTPUT.put_line ('command_type: ' || rowlcr.get_command_type);  
  77.        oldlist := rowlcr.get_values ('old');  
  78.   
  79.        FOR i IN 1 .. oldlist.COUNT  
  80.        LOOP  
  81.          IF oldlist (i) IS NOT NULL  
  82.          THEN  
  83.              DBMS_OUTPUT.put_line ('old(' || i || '): '  
  84.                                    || oldlist (i).column_name  
  85.                                  );  
  86.              print_any (oldlist (i).DATA);  
  87.          END IF;  
  88.        END LOOP;  
  89.   
  90.        newlist := rowlcr.get_values ('new''n');  
  91.   
  92.        FOR i IN 1 .. newlist.COUNT  
  93.        LOOP  
  94.          IF newlist (i) IS NOT NULL  
  95.          THEN  
  96.              DBMS_OUTPUT.put_line ('new(' || i || '): '  
  97.                                    || newlist (i).column_name  
  98.                                  );  
  99.              print_any (newlist (i).DATA);  
  100.           END IF;  
  101.         END LOOP;  
  102.   
  103.   -- Print extra attributes in row LCR  
  104.         ext_attr := rowlcr.get_extra_attribute ('row_id');  
  105.   
  106.         IF (ext_attr IS NOT NULL)  
  107.         THEN  
  108.           DBMS_OUTPUT.put_line ('row_id: ' || ext_attr.accessurowid ());  
  109.         END IF;  
  110.   
  111.         ext_attr := rowlcr.get_extra_attribute ('serial#');  
  112.   
  113.         IF (ext_attr IS NOT NULL)  
  114.         THEN  
  115.           DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());  
  116.         END IF;  
  117.   
  118.         ext_attr := rowlcr.get_extra_attribute ('session#');  
  119.   
  120.         IF (ext_attr IS NOT NULL)  
  121.         THEN  
  122.           DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());  
  123.         END IF;  
  124.   
  125.         ext_attr := rowlcr.get_extra_attribute ('thread#');  
  126.   
  127.         IF (ext_attr IS NOT NULL)  
  128.         THEN  
  129.           DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());  
  130.         END IF;  
  131.   
  132.         ext_attr := rowlcr.get_extra_attribute ('tx_name');  
  133.   
  134.         IF (ext_attr IS NOT NULL)  
  135.         THEN  
  136.           DBMS_OUTPUT.put_line (  'transaction name: '  
  137.                                 || ext_attr.accessvarchar2 ()  
  138.                                 );  
  139.         END IF;  
  140.   
  141.         ext_attr := rowlcr.get_extra_attribute ('username');  
  142.   
  143.         IF (ext_attr IS NOT NULL)  
  144.         THEN  
  145.           DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());  
  146.         END IF;  
  147.     ELSE  
  148.         DBMS_OUTPUT.put_line ('Non-LCR Message with type ' || typenm);  
  149.     END IF;  
  150.   END print_lcr;  

       4.print_transaction

[c-sharp] view plaincopy
  1. CREATE OR REPLACE PROCEDURE print_transaction (ltxnid IN VARCHAR2)  
  2.   IS  
  3.     i        NUMBER;  
  4.     txnid    VARCHAR2 (255);  
  5.     SOURCE  VARCHAR2 (128);  
  6.     msgno    NUMBER;  
  7.     msgcnt  NUMBER;  
  8.     errno    NUMBER;  
  9.     errmsg  VARCHAR2 (255);  
  10.    lcr      ANYDATA;  
  11.  BEGIN  
  12.    SELECT local_transaction_id, source_database, message_number,  
  13.            message_count, error_number, error_message  
  14.      INTO txnid, SOURCE, msgno,  
  15.            msgcnt, errno, errmsg  
  16.      FROM dba_apply_error  
  17.      WHERE local_transaction_id = ltxnid;  
  18.   
  19.    DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);  
  20.    DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);  
  21.    DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);  
  22.    DBMS_OUTPUT.put_line ('----Error Number: ' || errno);  
  23.    DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);  
  24.   
  25.    FOR i IN 1 .. msgcnt  
  26.    LOOP  
  27.        DBMS_OUTPUT.put_line ('--message: ' || i);  
  28.        lcr := DBMS_APPLY_ADM.get_error_message (i, txnid);    -- gets the LCR  
  29.        print_lcr (lcr);  
  30.    END LOOP;  
  31.  END print_transaction;  

 

这4个过程的具体用法就不记录了,简单易懂的。

 

        通过这些输出后发现一个translation里面往往有很多条修改(不只是一张表),我这里最多的一个里面居然有11W条修改,当时真是崩溃。。

        错误越来越多的原因就是一个translation里面那么多修改,而只要有一个出现ora-01403那整个translation都无法执行下去,这样就会造成连锁反应,后面的数据不停的报错,最后整个stream不可用只能重新导入。

 

        发现这个问题的产生是由于很多update导致的,比如将table1中的id=2,name=2的记录修改为name=3,stream会去查找id=2 and name=2的记录,如果找不到就报ORA-01403,这样几乎不可能修复数据(一个表上百个字段不可能一个一个去对比),最后网上搜索了一下,发现oracle的stream提供了一个update的冲突解决过程DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER,对于这个过程中resolution_column的意义我到目前还不是很理解,这里参考了别人的blog,等研究清楚在将记录补齐。设置完这个后update的冲突解决了,附上blog的内容和我用来批量生成脚本的sql

        sql如下:

[xhtml] view plaincopy
  1. select 'DECLARE   
  2.    cols DBMS_UTILITY.NAME_ARRAY;   
  3.  BEGIN   
  4.    cols(1) :''' || column_name || ''';  
  5.  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name => ''schema_name.' ||  
  6.         table_name ||  
  7.         ''', method_name => ''OVERWRITE'',resolution_column => ''' ||  
  8.         column_name || ''',column_list => cols);  
  9.  END;  
  10. /'  
  11.   from user_tab_columns  
  12.  where table_name in ();  

        blog的内容如下:

 

单向流环境配置好以后,增加update 冲突解决方式;
 
[xhtml] view plaincopy
  1. SQL> declare  
  2.   2   cols dbms_utility.name_array;  
  3.   3  begin  
  4.   4   cols(1):='ID';  
  5.   5   cols(2):='VNAME';  
  6.   6   cols(3):='QTY';  
  7.   7   cols(4):='VADDR';  
  8.   8   cols(5):='VSEX';  
  9.   9  dbms_apply_adm.set_update_conflict_handler(  
  10.  10    object_name => 'HZ.T1',  
  11.  11    method_name => 'overwrite',  
  12.  12    resolution_column => 'VNAME',  
  13.  13    column_list => cols);  
  14.  14  end;  
  15.  15  /  
  16.    
  17. PL/SQL procedure successfully completed  
 
目标库修改记录2的vname值为111,如下:
目标库T1的记录。
SQL> SELECT * FROM  HZ.T1;
 
        ID VNAME                       QTY VADDR      VSEX
---------- -------------------- ---------- ---------- ----------
         1 guangzhou                   100 111        333
         2 111                         200 111        333
 
原库更新T1表记录2的vname 值为333,如下:
SQL> SELECT * FROM  T1;
 
        ID VNAME                       QTY VADDR      VSEX
---------- -------------------- ---------- ---------- ----------
         1 guangzhou                100 111        333
         2 333                            200 111        333
 
这时目标库的应用进程中止了。
SQL>  select status from dba_apply;
 
STATUS
--------
ABORTED
 
应用错误记录表显示
SQL> select * from dba_apply_error;
 
APPLY_NAME      LOCAL_TRANSACTION_ID   ERROR_MESSAGE                                                                    
------------ ---------------------- -------------------------------------------------------------------------------- -
APPLY_STANDY     5.13.848             ORA-26786: A row with key ("ID") = (2) exists but has conflicting column(s) "VNA
 
 
 
3.执行过程,提示错误,
SQL>  EXEC DBMS_APPLY_ADM.execute_error('5.13.848');
 
begin DBMS_APPLY_ADM.execute_error('5.13.848'); end;
 
ORA-23460: 列 "QTY" 的值丢失, 在解析方法 "OVERWRITE" 中用于 "HZ"."T1"."REP_UPDATE"
ORA-26786: 键为 ("ID") = (2) 的行存在, 但具有冲突列 "VNAME" (在表 HZ.T1 中)
ORA-01403: 未找到任何数据
ORA-06512: 在 "SYS.DBMS_APPLY_ERROR", line 151
ORA-06512: 在 "SYS.DBMS_APPLY_ERROR", line 265
ORA-06512: 在 "SYS.DBMS_APPLY_ADM", line 467
ORA-06512: 在 line 2
 

 
4.删除现有的冲突定义 
[xhtml] view plaincopy
  1. SQL> declare  
  2.   2   cols dbms_utility.name_array;  
  3.   3  begin  
  4.   4   cols(1):='ID';  
  5.   5   cols(2):='VNAME';  
  6.   6   cols(3):='QTY';  
  7.   7   cols(4):='VADDR';  
  8.   8   cols(5):='VSEX';  
  9.   9  dbms_apply_adm.set_update_conflict_handler(  
  10.  10    object_name => 'HZ.T1',  
  11.  11    method_name => null,  
  12.  12    resolution_column => 'VNAME',  
  13.  13    column_list => cols);  
  14.  14  end;  
  15.  15  /  
  16.    
  17. PL/SQL procedure successfully completed  
 
 
5.重新定义新的冲突方式,字段值列出vname字段看效果
[xhtml] view plaincopy
  1. SQL>   
  2. SQL> declare  
  3.   2   cols dbms_utility.name_array;  
  4.   3  begin  
  5.   4   cols(1):='VNAME';  
  6.   5  dbms_apply_adm.set_update_conflict_handler(  
  7.   6    object_name => 'HZ.T1',  
  8.   7    method_name => 'overwrite',  
  9.   8    resolution_column => 'VNAME',  
  10.   9    column_list => cols);  
  11.  10  end;  
  12.  11  /  
  13.    
  14. PL/SQL procedure successfully completed  
 
 
6.再次执行错误的事务号,执行成功了。
SQL> EXEC DBMS_APPLY_ADM.execute_error('5.13.848');
 
PL/SQL procedure successfully completed
 
7.查看目标库的记录,已经更新了
SQL> select * from hz.t1;
 
        ID VNAME                       QTY VADDR      VSEX
---------- -------------------- ---------- ---------- ----------
         1 guangzhou                   100 111        333
         2 333                         200 111        333
 
继续试验:

1.目标库更新:
SQL> update hz.t1 set vaddr='guangzhou',qty=1 where id=1;
 
1 row updated
 
SQL> commit;
 
Commit complete

SQL> SELECT * FROM  hz.T1;
 
        ID VNAME                       QTY VADDR      VSEX
---------- -------------------- ---------- ---------- ----------
         1 666                           1 guangzhou  333
         2 333                         200 111        333
2.原库更新
SQL> update hz.t1 set vaddr='aaa' where id=1;
 
1 row updated
 
SQL> commit;
 
Commit complete

3.应用出错.
SQL> select status from dba_apply;
 
STATUS
--------
ABORTED
SQL> select ERROR_NUMBER,ERROR_MESSAGE from dba_apply_error;
ERROR_NUMBER ERROR_MESSAGE                                                                  
------------ --------------------------------------------------------------------------------
      26786  ORA-26786: A row with key ("ID") = (1) exists but has conflicting column(s) "VAD

SQL> exec dbms_apply_adm.execute_error('2.12.797');
 
begin dbms_apply_adm.execute_error('2.12.797'); end;
 
ORA-26786: 键为 ("ID") = (1) 的行存在, 但具有冲突列 "VADDR" (在表 HZ.T1 中)
ORA-01403: 未找到任何数据
ORA-06512: 在 "SYS.DBMS_APPLY_ERROR", line 151
ORA-06512: 在 "SYS.DBMS_APPLY_ERROR", line 265
ORA-06512: 在 "SYS.DBMS_APPLY_ADM", line 467
ORA-06512: 在 line 2
可以看到刚才只定义的vname 字段的冲突显然不能满足现在的情况。尝试一并加上其它字段的方式,执行错误事务号时还是报错。
 

4.只好把所有更新的字段一个个写入冲突方式中,
[xhtml] view plaincopy
  1. SQL> declare  
  2.   2   cols dbms_utility.name_array;  
  3.   3  begin  
  4.   4   cols(1):='QTY';  --定义QTY的冲突方式  
  5.   5  dbms_apply_adm.set_update_conflict_handler(  
  6.   6    object_name => 'HZ.T1',  
  7.   7    method_name =>  'overwrite',  
  8.   8    resolution_column => 'QTY',  
  9.   9    column_list => cols);  
  10.  10  end;  
  11.  11  /  
  12.    
  13. PL/SQL procedure successfully completed  
  14.    
  15. SQL> declare  
  16.   2   cols dbms_utility.name_array;  
  17.   3  begin  
  18.   4   cols(1):='VADDR';  --定义VADDR的冲突方式  
  19.   5  dbms_apply_adm.set_update_conflict_handler(  
  20.   6    object_name => 'HZ.T1',  
  21.   7    method_name =>  'overwrite',  
  22.   8    resolution_column => 'VADDR',  
  23.   9    column_list => cols);  
  24.  10  end;  
  25.  11  /  
  26.    
  27. PL/SQL procedure successfully completed  
  
5.再次执行成功.
SQL> exec dbms_apply_adm.execute_error('2.12.797');
 
PL/SQL procedure successfully completed
6.查看目标库记录,和原库的一样了。
SQL> SELECT * FROM  hz.T1;
 
        ID VNAME                       QTY VADDR      VSEX
---------- -------------------- ---------- ---------- ----------
         1 666                           1   aaa        333
         2 333                         200 111        333

7. dba_apply_conflict_columns 表的记录。
SQL> select OBJECT_OWNER,OBJECT_NAME,METHOD_NAME,RESOLUTION_COLUMN,COLUMN_NAME from dba_apply_conflict_columns WHERE OBJECT_NAME='T1';
 
OBJECT_OWNER  OBJECT_NAME   METHOD_NAME   RESOLUTION_COLUMN       COLUMN_NAME
------------- ------------- ------------- ----------------------- ------------------------------
HZ            T1            OVERWRITE     QTY                       QTY
HZ            T1            OVERWRITE     VADDR                   VADDR
HZ            T1            OVERWRITE     VNAME                   VNAME
0 0
原创粉丝点击