HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

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

[English]

作者: fuyuncat

来源: www.HelloDBA.com

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

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

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_capturecaptured_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.
 
 

四、总结:

此次测试是使用的9205streams replication9205transparent gateway(期间尝试过)实现的。配置过程中出现过各种问题,并且还发现了一个bug
(已经在metalink上创建bug5191525)。总体感觉要通过这种方式来实现异构数据库实时数据同步还不成熟。本来我们有项目打算采用这种方式来做,但整个测试
做下来,觉得风险太大,最终还是采用自己写中间件的方式来做。不知道在10g后,是否会更成熟一些。

 

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat