通过streams replication实现Oracle和SQL Server之间异构数据同步(2)

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2006-11-07 14:50:13

分享到 

1.    创建并启动应用 Connect STRMADMIN/STRMADMIN   --创建应用 /*  Create the apply process using the following command */ begin    dbms_apply_adm.create_apply    (    queue_name => 'strmadmin.queue_for_reptest',    apply_name => 'apply_reptest',    apply_captured => TRUE,    apply_database_link => 'MSSQL'    ); end; /   --将创建好的规则加到应用进程上去，来指定复制的表 /*  Add rules to the apply process to specify which table changes */ /*  should be applied */ begin    DBMS_STREAMS_ADM.ADD_TABLE_RULES    (    Table_name=>'ORAREP.REP_TEST1',    streams_type=>'apply',    streams_name=>'APPLY_REPTEST',    queue_name=> 'strmadmin.queue_for_reptest'    ); end; /   begin    DBMS_STREAMS_ADM.ADD_TABLE_RULES    (    Table_name=>'ORAREP.REP_TEST2',    streams_type=>'apply',    streams_name=>'APPLY_REPTEST',    queue_name=> 'strmadmin.queue_for_reptest'    ); end; /   --设置使应用进程在遇到错误时还能继续。 /*  Allow apply process to continue processing even if a data error occurs */ execute dbms_apply_adm.set_parameter('apply_reptest','DISABLE_ON_ERROR','N') ;   --启动应用进程 /*  Start the apply process */ execute dbms_apply_adm.start_apply('apply_reptest') ;     2.    设置表的实例化SCN(系统变化号)给应用 Connect STRMADMIN/STRMADMIN   DECLARE     iscn  NUMBER;         -- Variable to hold instantiation SCN value BEGIN     iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();     dbms_apply_adm.set_table_instantiation_scn     ('ORAREP.REP_TEST1','ORA92',iscn, 'MSSQL');     iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();     dbms_apply_adm.set_table_instantiation_scn     ('ORAREP.REP_TEST2','ORA92',iscn, 'MSSQL'); END; /   3.    在Oracle端开始捕捉 Connect STRMADMIN/STRMADMIN   execute dbms_capture_adm.start_capture ('REPTEST_CAPTURE');   --在Oracle端插入数据： conn orarep/orarep   insert into rep_test1 values (1, 'abcd'); insert into rep_test2 values (1.0, 'abcd', sysdate); commit;   --第一次捕捉可能最长需要等待10-15分钟“预热”时间（需要将两边的初始化数据字典信息传递给双方），通过查看视图 v$streams_capture的captured_message_create_time是否是当前时间确定捕捉是否开始。 select total_messages_captured,capture_message_create_time, total_messages_enqueued, enqueue_message_create_time, enqueue_message_number from v$streams_capture;     --如果v$streams_apply_coordinator=v$streams_capture.enqueue_message_number 则表示所有的变化都已经复制 select total_received,total_assigned,total_applied, total_errors, hwm_message_number  from v$streams_apply_coordinator;   --检查streams的应用是否有错误： select * from dba_apply_error;   --如果没有错误，则表示streams复制设置成功.     一、设置Steams Replication复制到SQLServer的不同schema下   At apply-site create a  transformation function when you want the apply process to apply changes to a different schema at SQL*Server.     In the following example, the transformation function  hs_trans_fun is created to apply changes from local 'TKHOUSER' schema to remote schema 'sqluser'.  This function changes the object owner to 'sqluser' before applying the changes.  When changing this code, remember that SQL*Server object names are case sensitive.  Run the code below, then insert into TKHOUSER.TKHRDT3. The values should be inserted into sqluser.TKHRDT3 on SQL*Server instead of TKHOUSER.TKHRDT3 on SQL*Server.     create or replace function hs_trans_fun(in_any IN sys.anydata) return sys.anydata is       lcr     sys.lcr$_row_record;       ret     pls_integer;       objnm   varchar2(30); begin       ret     := in_any.getobject(lcr);       objnm   := lcr.get_object_name;       lcr.set_object_owner('"sqluser"');       return sys.anydata.convertobject(lcr); end; / show errors; commit;   execute dbms_rule_adm.drop_rule ('strmadmin.r_tran1');   begin        dbms_rule_adm.create_rule_set(              rule_set_name=>'strmadmin.hsapplyruleset',              evaluation_context=>'sys.streams$_evaluation_context') ; end ; /   declare        action_ctx sys.re$nv_list ;        ac_name VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION' ; begin        action_ctx := sys.re$nv_list(sys.re$nv_array()) ;        action_ctx.add_pair(ac_name, sys.anydata.convertvarchar2('STRMADMIN.HS_TRANS_FUN')) ;        dbms_rule_adm.create_rule(             rule_name=>'strmadmin.r_tran1',             condition=>':dml.get_object_owner() = ''TKHOUSER'' AND ' ||     ':dml.is_null_tag() = ''Y''',             evaluation_context=>'sys.streams$_evaluation_context',             action_context => action_ctx) ;          dbms_rule_adm.add_rule(              rule_set_name=>'strmadmin.hsapplyruleset',              rule_name=>'strmadmin.r_tran1') ; end ; / commit ;   select rs.rule_set_name, r.rule_owner, r.rule_name, r.rule_condition from dba_rules r, dba_rule_set_rules rs where rs.rule_name  = r.rule_name and       rs.rule_owner = r.rule_owner order by rs.rule_set_name, r.rule_name;   execute dbms_apply_adm.alter_apply ('apply_to_SQLServer','strmadmin.hsapplyruleset');   二、Debug Trace   通过在tg4msql.ora文件中设置以下行，可以打开透明网关的debug trace hs_fds_trace_level=debug   通过视图dba_apply_error可以看streams复制中的错误信息。   三、常见错误及解决办法   /********************************************************************/ ORA-28509: unable to establish a connection to non-Oracle system ORA-02063: preceding line from TG4MSQL /********************************************************************/ cause:     This indicates a problem with the Oracle configuration files. Action:     Make sure the HOST parameter in the tnsnames.ora file is correct.     Make sure the PORT number is correct.     Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for MS SQL Server] The environment variable <HS_FDS_CONNECT_INFO> is not set. ORA-02063: preceding 2 lines from TG4MSQL /********************************************************************/ cause:     Incorrect parameter settings in the HS init.ora file. Action:     Check HS_FDS_CONNECT_INFO in the TG4MSQL init.ora file.     It might be missing or TG4MSQL is not able to find the correct     initialisation file.     Make sure the HS init.ora file exists in the ORACLE_HOME\tg4msql\admin     directory and has the same name as the SID in the LISTENER.ORA.     Example: If SID=mssql in the listener.ora file, then the nit.ora file     would be named ORACLE_HOME\hs\admin\initmssql.ora /********************************************************************/ ORA-00942: table or view does not exist [Transparent gateway for ODBC]DRV_OpenTable: [Mircosoft ][ODBC SQL Server Driver][SQL Server]Invalid object name '%table%'. (SQL State: S0002; SQL Code: 208) ORA-02063: preceding 2 lines from TG4MSQL /********************************************************************/ cause:        The init.ora file speciffies the wrong MS SQL Server database.     A second cause could be, that MS SQL Server tables are case sensitive     and thus should be surrounded by double quotes.     四、总结： 此次测试是使用的9205的streams replication和9205的transparent gateway（期间尝试过）实现的。配置过程中出现过各种问题，并且还发现了一个bug （已经在metalink上创建bug号5191525）。总体感觉要通过这种方式来实现异构数据库实时数据同步还不成熟。本来我们有项目打算采用这种方式来做，但整个测试 做下来，觉得风险太大，最终还是采用自己写中间件的方式来做。不知道在10g后，是否会更成熟一些。