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=>'MEMBER.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=>'MEMBER.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 valueBEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); dbms_apply_adm.set_table_instantiation_scn ('MEMBER.REP_TEST1','ORCL',iscn, 'MSSQL'); iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); dbms_apply_adm.set_table_instantiation_scn('MEMBER.REP_TEST2','ORCL',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_conditionfrom 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 tracehs_fds_trace_level=debug 通過視圖dba_apply_error可以看streams複製中的錯誤信息。 三、常見錯誤及解決辦法
/********************************************************************/ORA-28509: unable to establish a connection to non-Oracle systemORA-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 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 ServerDriver][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.
沒有留言:
張貼留言