select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_apply));
删除指定的rule
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name IN VARCHAR2,
streams_type IN VARCHAR2,
streams_name IN VARCHAR2,
drop_unused_rule IN BOOLEAN DEFAULT TRUE,
inclusion_rule IN BOOLEAN DEFAULT TRUE);
rule_name通过select * from dba_rules查看
为capture,propagation,apply添加rule
dbms_add_schema_rules dbms_add_global_rules
dbms_add_schema_propagation_rules dbms_add_global_propagation_rules
添加schema或table后,为schema,table添加instantiation_scn
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
(
source_schema_name => 'TEST',
source_database_name => 'ORCL.LAB.COM',
instantiation_scn => v_scn,
recursive => true);
END;
/
apply出现error后select * from dba_apply_error查看错误,exec dbms_apply_adm.delete_all_errors('orcl_apply') 删除apply错误队列的对象
exec DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG(source_database =>'gc.lab.com',source_object_name =>'test.a',source_object_type =>'table');--用于在非复制条件下删除复制table后删除catalog
几个有用的过程,用来查看apply发生的错误
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 /
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 /
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 /
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 /
