Streams流复制的异常检测

« wmiprvse.exe进程是什么? | Blog首页 | streams流复制ORA-01403错误解决一则 »

在使用Streams流复制的过程中，遇到各种错误的时候很常见。在Oracle的文档（Oracle® Streams Concepts and Administration 10g Release 2 ）上提供了一个异常检测方案。

比如在LCR应用过程中出现错误：

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> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
Grant succeeded
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
Grant succeeded

此后需要建立几个过程

SQL> connect strmadmin/strmadmin@test97
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as strmadmin

SQL> 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;
51 /

Procedure created

SQL> 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;
151 /

Procedure created

SQL> 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;
47 /

Procedure created

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

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC print_errors

*************************************************
----- ERROR #1
----- 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

最后创建一个print_transaction过程可以用来打印输出指定事务的详细信息：

SQL> CREATE OR REPLACE PROCEDURE print_transaction (ltxnid IN VARCHAR2)
2 IS
3 i NUMBER;
4 txnid VARCHAR2 (30);
5 SOURCE VARCHAR2 (128);
6 msgno NUMBER;
7 msgcnt NUMBER;
8 errno NUMBER;
9 errmsg VARCHAR2 (128);
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;
32 /

Procedure created

现在来看看这个失败的事务：

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