Streams Replication Practice: Schema Level
--------------------------------------------------------------------------------------------------------------------
准备工作...
1. 创建队列
in source database: lo.oracle.com
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_streams_adm.set_up_queue
3 ( queue_table => 'capture_queue_table',
4 queue_name => 'capture_queue',
5 queue_user => 'strmadmin');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from capture_queue_table;
no rows selected
in destination database: orcl.oracle.com
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_streams_adm.set_up_queue
3 ( queue_table => 'apply_queue_table',
4 queue_name => 'apply_queue',
5 queue_user => 'strmadmin');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from apply_queue_table;
no rows selected
SQL>
2. LogMiner tablespace Setup (optional)
in source database: lo.oracle.com
SQL> conn / as sysdba
Connected.
SQL> create tablespace logmnr_tbs
2 datafile 'E:\oracle\oradata\lo\logmnr_tbs01.dbf'
3 size 25m autoextend on maxsize unlimited;
Tablespace created.
SQL> begin
2 dbms_logmnr_d.set_tablespace('logmnr_tbs');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
3. 配置catpure stream (add schema rules) 和propagation rule
in source database: lo.oracle.com
SQL> conn strmadmin/strmadmin
Connected.
SQL> begin
2 dbms_streams_adm.add_schema_rules
3 (schema_name => 'scott',
4 streams_type => 'capture',
5 streams_name => 'capture_stream',
6 queue_name => 'capture_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_streams_adm.add_schema_propagation_rules
3 ( schema_name => 'scott',
4 streams_name => 'source_to_target',
5 source_queue_name => 'capture_queue',
6 destination_queue_name => 'apply_queue@orcl.oracle.com',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'lo.oracle.com');
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;
CAPTURE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------ -------- ------------ -----------
CAPTURE_STREAM DISABLED
SQL> select propagation_name, status from dba_propagation;
PROPAGATION_NAME STATUS
------------------------------ --------
SOURCE_TO_TARGET ENABLED
SQL> select job, what from dba_jobs;
JOB
----------
WHAT
--------------------------------------------------------------------------------
215
next_date := sys.dbms_aqadm.aq$_propaq(job);
21
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
SQL>
4. 配置apply stream
in destination database: orcl.oracle.com
SQL> begin
2 dbms_streams_adm.add_schema_rules
3 ( schema_name => 'scott',
4 streams_type => 'apply',
5 streams_name => 'apply_stream',
6 queue_name => 'apply_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'lo.oracle.com');
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select apply_name, status from dba_apply;
APPLY_NAME STATUS
------------------------------ --------
APPLY_STREAM DISABLED
SQL>
5. 给目标数据库设置instantiation scn.
in source database: lo.oracle.com
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
66739793
in destination database: orcl.oracle.com
SQL> begin
2 dbms_apply_adm.set_schema_instantiation_scn
3 ( source_schema_name => 'scott',
4 source_database_name => 'lo.oracle.com',
5 instantiation_scn => 66739793);
6 end;
7 /
PL/SQL procedure successfully completed.
6.给destination database设置一些参数，使得apply process不会因为出现错误而终止运行
SQL> begin
2 dbms_apply_adm.set_parameter
3 ( apply_name => 'apply_stream',
4 parameter => 'disable_on_error',
5 value => 'n');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 (apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
7. 开始capture
source database: lo.oracle.com
SQL> begin
2 dbms_capture_adm.start_capture
3 (capture_name => 'capture_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
----------------------------------------------------------------------------------------------------------------------
开始测试...
注意到source schema 和destination schema的数据不是完全相同的，如表dept
-- lo.oracle.com
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- orcl.oracle.com
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING test
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
这个时候如果在lo.oracle.com中的scott.dept表进行数据更改的话，orcl.oracle.com并不会跟着改变！！！
SQL> insert into scott.dept values(99, 'test', 'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
99 test test
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- orcl.oracle.com
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING test
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
但是如果我是新创建对象的话,是可以正常同步的...
-- lo.oracle.com
SQL> create table scott.test as select * from scott.dept;
Table created.
-- orcl.oracle.com
SQL> select * from scott.test;
DEPTNO DNAME LOC
---------- -------------- -------------
99 test test
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- lo.oracle.com
SQL> delete from scott.test where deptno=99;
1 row deleted.
SQL> commit;
-- orcl.oracle.com
SQL> select * from scott.test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- lo.oracle.com
SQL> drop table scott.test purge;
Table dropped.
-- orcl.oracle.com
SQL> select * from scott.test;
select * from scott.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
--------------------------------------------------------------------------------------------------------------------
之前做了基于表scott.dept的dml/ddl操作，但是都没有被apply到target的数据库上，说明是出了些问题的，可以通过查询apply_queue_table看一下为什么。
SQL> select q_name, retry_count, sender_name, s.user_data.GetTypeName() as lcr from apply_queue_table s;
Q_NAME RETRY_COUNT SENDER_NAME LCR
------------------------------ ----------- ------------------------------ --------------------------------------------------
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_PROCEDURE_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_PROCEDURE_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_ROW_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
AQ$_APPLY_QUEUE_TABLE_E 0 CAPTURE_STREAM SYS.LCR$_DDL_RECORD
18 rows selected.
而且如果这个时候尝试drop apply stream也会遇到如下的错误....
SQL> exec dbms_apply_adm.drop_apply('apply_stream');
BEGIN dbms_apply_adm.drop_apply('apply_stream'); END;
*
ERROR at line 1:
ORA-26663: error queue for apply process APPLY_STREAM must be empty
ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 283
ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 270
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 691
ORA-06512: at line 1
可以看出apply_queue_table中有数据，所以无法drop apply stream. 从上面查询结果可以看出，这些数据都存在于apply_queue_table对应的exception queue, 存的都是些错误信息。那么这些错误信息到底是什么呢，oracle online doc 给出了不少方法，参见这里 http://download.oracle.com/docs/cd/B19306_01/server.102/b14229/strms_apmon.htm#i1007852
最简单的方法是查询视图dba_apply_error
SQL> select error_message from dba_apply_error;
ERROR_MESSAGE
------------------------------------------------------------------------------------------------------------
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-00955: name is already used by an existing object
ORA-00942: table or view does not exist
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."DEPT" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."EMP" in source database "LO.ORACLE.COM"
ORA-26687: no instantiation SCN provided for "SCOTT"."EMP" in source database "LO.ORACLE.COM"
11 rows selected.
另外，我们也可以自己写代码来查询apply_queue_table中的信息， 如下...
SET SERVEROUTPUT ON
DECLARE
v_anydata SYS.ANYDATA;
v_lcr SYS.LCR$_ROW_RECORD;
v_row_list SYS.LCR$_ROW_LIST;
v_result PLS_INTEGER;
BEGIN
for rec in
(
SELECT user_data
FROM apply_queue_table s
WHERE s.user_data.GetTypeName() = 'SYS.LCR$_ROW_RECORD'
)
loop
v_anydata := rec.user_data;
v_result := ANYDATA.GetObject(
self => v_anydata,
obj => v_lcr);
DBMS_OUTPUT.PUT_LINE('Command Type : ' || v_lcr.Get_Command_Type);
DBMS_OUTPUT.PUT_LINE('Object Owner : ' || v_lcr.Get_Object_Owner);
DBMS_OUTPUT.PUT_LINE('Object Name : ' || v_lcr.Get_Object_Name);
DBMS_OUTPUT.PUT_LINE('SCN : ' || v_lcr.Get_SCN);
DBMS_OUTPUT.PUT_LINE('Commit SCN : ' || v_lcr.Get_COMMIT_SCN);
DBMS_OUTPUT.PUT_LINE('Source Database Name : ' || v_lcr.Get_Source_Database_Name);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------');
end loop;
END;
/
通过这些错误信息，可以分析错误原因，解决之后可以通过dbms_apply_adm.delete_all_errors来删除队列中的错误信息，从而就可以成功drop掉apply stream了。
SQL> exec dbms_apply_adm.drop_apply('apply_stream');BEGIN dbms_apply_adm.drop_apply('apply_stream'); END;*
ERROR at line 1:
ORA-26663: error queue for apply process APPLY_STREAM must be empty
ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 283ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 270ORA-06512: at "SYS.DBMS_APPLY_ADM", line 691ORA-06512: at line 1SQL> exec dbms_apply_adm.delete_all_errors('apply_stream');PL/SQL procedure successfully completed.SQL> select * from dba_apply_error;no rows selectedSQL> exec dbms_apply_adm.drop_apply('apply_stream');PL/SQL procedure successfully completed.SQL> exec dbms_aqadm.drop_queue('apply_queue');PL/SQL procedure successfully completed.SQL> exec dbms_aqadm.drop_queue_table('apply_queue_table');PL/SQL procedure successfully completed.SQL>
--------------------------------------
Regards,
FangwenYu
