-------------------------------------------------------------------------------------
在使用Streams流复制的过程中，遇到各种错误的时候很常见。在Oracle的文档（Oracle? Streams Concepts and Administration 10g Release 2 ）上提供了一个异常检测方案。
比如在LCR应用过程中出现错误：
select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE
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> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
Grant succeeded
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
Grant succeeded
此后需要建立几个过程
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;
/
2
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;
/
3 现在就可以使用print_errors来打印出详细的错误信息，
但是注意，如果错误事务非常多，那么这个过程可能会非常耗时：
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;
/
3 最后创建一个print_transaction过程可以用来打印输出指定事务的详细信息：
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;
/
4 现在来看看这个失败的事务：
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC print_transaction('5.27.1273')
这几个过程在流复制的故障诊断中非常有用，记录于此。
