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;
/
?
**************************************************************
?
?
?