Streams流复制的异常检测
本文为转载，但基本上属于常识类知识，自己修改一下，附上转载地址：
在使用Streams流复制的过程中，遇到各种错误的时候很常见。在Oracle的文档(Oracle? Streams Concepts and Administration 10g Release 2)上提供了一个异常检测方案。即可以通过Oracle自己的数据字典，来确定具体的传输内容，以便作出相应的调整：
首先在创建这几个过程时，要记得给stradmin用户赋权，使其对dba_apply_error和dbms_apply_adm包有使用权限，即便是stradmin已经具有dba权限，也还是需要进行赋权后才能顺利得创建这几个包，赋权的语句如下所示：
GRANT SELECT ON DBA_APPLY_ERROR TO stradmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO stradmin;
生成的主要包括四个过程(具体代码见附录)：
print_any
print_lcr
print_errors
print_transaction
前面两个是中间的过程，就不需要了解了，关键是后面的两个过程，用来打印错误的信息。print_errors是用来打印所有的错误信息，但是当错误非常多时，会造成output的溢出，即便设置了很大的值，输出也会很慢，所以又提供了print_transaction过程，用以显示某个单独的错误信息的对应错误说明：
print_transaction的用法是这样的：
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
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC print_transaction('5.27.1273')
----- Local Transaction ID: 5.27.1273
----- Source Database: TEST201.EYGLE.COM
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: TEST201.EYGLE.COM
owner: SCOTT
object: DEPT
is tag null: Y
command_type: UPDATE
old(1): DEPTNO
50
old(2): LOC
CHINA
new(1): LOC
CHINA
PL/SQL procedure successfully completed
关于详细的用法，可以参见下面这个地址，就不转贴了：
附：Check脚本
**************************************************************
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 := 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_any;
/
CREATE OR REPLACE PROCEDURE print_lcr (lcr IN ANYDATA)
IS
typenm VARCHAR2 (61);
ddllcr SYS.lcr$_ddl_record;
proclcr SYS.lcr$_procedure_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 := 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);
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_lcr;
/
CREATE OR REPLACE PROCEDURE print_errors
IS
CURSOR c
IS
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;
i NUMBER;
txnid VARCHAR2 (30);
SOURCE VARCHAR2 (128);
msgno NUMBER;
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2 (255);
lcr ANYDATA;
r NUMBER;
BEGIN
FOR r IN c
LOOP
errnum := errnum + 1;
msgcnt := r.message_count;
txnid := r.local_transaction_id;
SOURCE := r.source_database;
msgno := r.message_number;
errno := r.error_number;
errmsg := 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_errors;
/
CREATE OR REPLACE PROCEDURE print_transaction (ltxnid IN VARCHAR2)
IS
i NUMBER;
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 print_transaction;
/
**************************************************************