流复制错误诊断

在网上看到了一个很好的流复制诊断的，摘录到此，做了点修改，以备后来使用。

分为如下步骤：

/*1.首先在sysdba权限下赋予strmadmin用户某些权限*/

sql>conn / as sysdba

SQL> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;

SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;

/*2.需要建立几个过程*/

/*==================print_any=================*/

create or replace procedure print_any(data in anydata) is
  tn  varchar2(61);
  str varchar2(4000);
  chr varchar2(1000);
  num number;
  dat date;
  rw  raw(4000);
  res number;
begin
  if data is null then
    dbms_output.put_line('null value');
    return;
  end if;

  tn := lower(data.gettypename());

  if tn = 'sys.varchar2' then
    res := data.getvarchar2(str);
    dbms_output.put_line(substr(str, 0, 253));
  elsif tn = 'sys.char' then
    res := data.getchar(chr);
    dbms_output.put_line(substr(chr, 0, 253));
  elsif tn = 'sys.varchar' then
    res := data.getvarchar(chr);
    dbms_output.put_line(chr);
  elsif tn = 'sys.number' then
    res := data.getnumber(num);
    dbms_output.put_line(num);
  elsif tn = 'sys.date' then
    res := data.getdate(dat);
    dbms_output.put_line(dat);
  elsif tn = 'sys.raw' then
    -- res := data.getraw(rw);
    -- dbms_output.put_line(substr(dbms_lob.substr(rw),0,253));
    dbms_output.put_line('blob value');
  elsif tn = 'sys.blob' then
    dbms_output.put_line('blob found');
  else
    dbms_output.put_line('typename is ' || tn);
  end if;
end;

/

/*==================print_lcr=================*/

create or replace procedure print_lcr(lcr in anydata) is
  typenm   varchar2(61);
  ddllcr   sys.lcr$_ddl_record;
  rowlcr   sys.lcr$_row_record;
  res      number;
  newlist  sys.lcr$_row_list;
  oldlist  sys.lcr$_row_list;
  ddl_text clob;
  ext_attr anydata;
begin
  typenm := lower(lcr.gettypename());
  dbms_output.put_line('type name: ' || typenm);

  if (typenm = 'sys.lcr$_ddl_record') then
    res := lcr.getobject(ddllcr);
    dbms_output.put_line('source database: ' ||
                         ddllcr.get_source_database_name);
    dbms_output.put_line('owner: ' || ddllcr.get_object_owner);
    dbms_output.put_line('object: ' || ddllcr.get_object_name);
    dbms_output.put_line('is tag null: ' || ddllcr.is_null_tag);
    dbms_lob.createtemporary(ddl_text, true);
    ddllcr.get_ddl_text(ddl_text);
    dbms_output.put_line('ddl: ' || ddl_text);
    -- print extra attributes in ddl lcr
    ext_attr := ddllcr.get_extra_attribute('serial#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('serial#: ' || ext_attr.accessnumber());
    end if;
 
    ext_attr := ddllcr.get_extra_attribute('session#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('session#: ' || ext_attr.accessnumber());
    end if;
 
    ext_attr := ddllcr.get_extra_attribute('thread#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('thread#: ' || ext_attr.accessnumber());
    end if;

    ext_attr := ddllcr.get_extra_attribute('tx_name');
 
    if (ext_attr is not null) then
      dbms_output.put_line('transaction name: ' ||
                           ext_attr.accessvarchar2());
    end if;
 
    ext_attr := ddllcr.get_extra_attribute('username');
 
    if (ext_attr is not null) then
      dbms_output.put_line('username: ' || ext_attr.accessvarchar2());
    end if;
 
    dbms_lob.freetemporary(ddl_text);
  elsif (typenm = 'sys.lcr$_row_record') then
    res := lcr.getobject(rowlcr);
    dbms_output.put_line('source database: ' ||
                         rowlcr.get_source_database_name);
    dbms_output.put_line('owner: ' || rowlcr.get_object_owner);
    dbms_output.put_line('object: ' || rowlcr.get_object_name);
    dbms_output.put_line('is tag null: ' || rowlcr.is_null_tag);
    dbms_output.put_line('command_type: ' || rowlcr.get_command_type);
   
    /*
      GET_VALUES(value_type,use_old)
      取LCR$_ROW_RECORD类型中的所有值,返回SYS.LCR$_ROW_LIST类型值；
      value_type：值的类型，取值为'new'或'old'
      use_old：取'Y'或'N',默认为'Y'。当lalue_type取'new'，而新值不存在时，是否使用旧值
    */
   
    oldlist := rowlcr.get_values('old');
 
    for i in 1 .. oldlist.count loop
      if oldlist(i) is not null then
        dbms_output.put_line('old(' || i || '): ' || oldlist(i)
                             .column_name);
        print_any(oldlist(i).data);
      end if;
    end loop;
  
    newlist := rowlcr.get_values('new', 'N');
 
    for i in 1 .. newlist.count loop
      if newlist(i) is not null then
        dbms_output.put_line('new(' || i || '): ' || newlist(i)
                             .column_name);
        print_any(newlist(i).data);
      end if;
    end loop;
 
    -- print extra attributes in row lcr
    ext_attr := rowlcr.get_extra_attribute('row_id');
 
    if (ext_attr is not null) then
      dbms_output.put_line('row_id: ' || ext_attr.accessurowid());
    end if;
 
    ext_attr := rowlcr.get_extra_attribute('serial#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('serial#: ' || ext_attr.accessnumber());
    end if;
 
    ext_attr := rowlcr.get_extra_attribute('session#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('session#: ' || ext_attr.accessnumber());
    end if;
 
    ext_attr := rowlcr.get_extra_attribute('thread#');
 
    if (ext_attr is not null) then
      dbms_output.put_line('thread#: ' || ext_attr.accessnumber());
    end if;
 
    ext_attr := rowlcr.get_extra_attribute('tx_name');
 
    if (ext_attr is not null) then
      dbms_output.put_line('transaction name: ' ||
                           ext_attr.accessvarchar2());
    end if;
 
    ext_attr := rowlcr.get_extra_attribute('username');
 
    if (ext_attr is not null) then
      dbms_output.put_line('username: ' || ext_attr.accessvarchar2());
    end if;
  else
    dbms_output.put_line('non-lcr message with type ' || typenm);
  end if;
end;
/

/*==================print_errors=================*/

CREATE OR REPLACE PROCEDURE print_errors IS
  txnid  varchar2(30);
  source varchar2(128);
  msgno  number;
  msgcnt number;
  errnum number := 0;
  errno  number;
  errmsg varchar2(255);
  lcr    ANYDATA;
BEGIN
  FOR vcr_r IN (SELECT local_transaction_id,
                       source_database,
                       message_number,
                       message_count,
                       error_number,
                       error_message
                  FROM dba_apply_error
                 ORDER BY source_database, source_commit_scn) LOOP
    errnum := errnum + 1;
    msgcnt := vcr_r.message_count;
    txnid  := vcr_r.local_transaction_id;
    source := vcr_r.source_database;
    msgno  := vcr_r.message_number;
    errno  := vcr_r.error_number;
    errmsg := vcr_r.error_message;

    dbms_output.put_line('******************************');
    dbms_output.put_line('----- ERROR #' || errnum);
    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);
 
    FOR i IN 1 .. msgcnt LOOP
      dbms_output.put_line('--message: ' || i);
      lcr := DBMS_APPLY_ADM.get_error_message(i, txnid);
      print_lcr(lcr);
    end loop;
  end loop;
end;
/

/*=================== print_transaction==============*/

create or replace procedure print_transaction(ltxnid in varchar2) is
  txnid  varchar2(30);
  source varchar2(128);
  msgno  number;
  msgcnt number;
  errno  number;
  errmsg varchar2(128);
  lcr    anydata;
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);

  for i in 1 .. msgcnt loop
    dbms_output.put_line('----message: ' || i);
    lcr := dbms_apply_adm.get_error_message(i, txnid); -- gets the lcr
    print_lcr(lcr);
  end loop;
end;

/*3.开始诊断*/

现在就可以使用print_errors来打印出详细的错误信息，但是注意，如果错误事务非常多，那么这个过程可能会非常耗时：

sql> SET SERVEROUTPUT ON SIZE 1000000

sql>set serverout on
sql> EXEC print_errors

如果嫌打出的错误太多，可以指定具体的事务id来显示该事务的错误信息

1）首先查看错误信息

SQL> select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE
  2  from dba_apply_error;

APPLY_NAME  LOCAL_TRANSACTION_ID  SOURCE_TRANSACTION_ID  ERROR_MESSAGE
----------- ---------------------- ---------------------- -------------------------
APP97_APPLY 5.27.1273              4.46.576              ORA-01403: no data found

2）再显示事务id为"4.46.576"的错误信息

sql>exec print_transaction('4.46.576');