这种应用从前景上来说非常广,尤其是在系统切换的时候,两个系统的表结构都有较大的变化时。但为了实现平滑的切换,一种方案就是实现两个异构系统
之间的双向同步。这样可以随时在两个系统之间进行切换,从而减少了切换的压力。
双向复制的方案很多,可以用程序实现。可以用触发器实现。当然流复制也可以实现。
以下是我用流复制进行many-to-one复制的一种尝试,例子很简单,仅以此抛砖引玉!
数据库版本:ORACLE10.2.0.1
测试环境:
源库:stream76.com
目的库:stream19.com
源表:
SQL> desc test.t11;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
SQL> desc test.t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(30)
目的表:
SQL> desc test.t11_19
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER(38)
NAME VARCHAR2(30)
SQL>
SQL> select * from test.t1;
ID NAME
---------- ------------------------------
444 test444
555 test555
复制规则:
t11.id+t1.name=t11_19.NO+t11_19.NAME
如何实现流复制的transformation就不写了,请见我的前一个贴子。
http://space.itpub.net/9375/viewspace-490762
这里仅简单演示如何利用dml handler进行many-to-one的复制。
一 编写dml handler并在目的库创建此存储过程
CREATE OR REPLACE PROCEDURE t11_dml_handler(in_any IN SYS.ANYDATA) IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
v_name varchar2(20) := ' ';
v_old_id_anydata SYS.ANYDATA;
v_old_id number;
v_dummy PLS_INTEGER;
v_sqlcode varchar2(32);
v_sqlerrm varchar2(255);
--v_typecode PLS_INTEGER;
--v_type SYS.ANYTYPE;
non_null_anytype_for_NUMBER exception;
unknown_typename exception;
v_cnt number;
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
if dmlcommand in ('INSERT') then
v_old_id_anydata := lcr.get_value('NEW', 'NO', 'Y');
elsif dmlcommand in ('DELETE') then
v_old_id_anydata := lcr.get_value('OLD', 'NO');
else
null;
end if;
-- Insert information about the LCR into the history_row_lcrs table
INSERT INTO strmadmin.history_row_lcrs
VALUES
(SYSDATE,
lcr.GET_SOURCE_DATABASE_NAME(),
lcr.GET_COMMAND_TYPE(),
lcr.GET_OBJECT_OWNER(),
lcr.GET_OBJECT_NAME(),
lcr.GET_TAG(),
lcr.GET_TRANSACTION_ID(),
lcr.GET_SCN(),
lcr.GET_COMMIT_SCN,
lcr.GET_VALUES('old'),
lcr.GET_VALUES('new', 'n'));
commit;
v_dummy := v_old_id_anydata.GetNUMBER(v_old_id /* OUT */);
--DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_id) || ': NUMBER = ' || To_Char(v_n));
select count(*) into v_cnt from test.t1@stream76 a where a.id = v_old_id;
if v_cnt = 1 then
select name into v_name from test.t1@stream76 a where a.id = v_old_id;
IF object_owner = 'TEST' and object_name = 'T11_19' and dmlcommand IN ('INSERT' /*, 'DELETE', 'UPDATE'*/
) THEN
-- Add Columns
lcr.add_column('NEW', 'NAME', sys.anydata.convertvarchar2(v_name));
ELSE
lcr.add_column('OLD', 'NAME', sys.anydata.convertvarchar2(v_name));
END IF;
LCR.EXECUTE(TRUE);
elsif v_cnt = 0 then
IF object_owner = 'TEST' and object_name = 'T11_19' and dmlcommand IN ('INSERT' /*, 'DELETE', 'UPDATE'*/
) THEN
-- Add Columns
lcr.add_column('NEW', 'NAME', sys.anydata.convertvarchar2(' '));
ELSE
lcr.add_column('OLD', 'NAME', sys.anydata.convertvarchar2(' '));
END IF;
LCR.EXECUTE(TRUE);
else
null;
end if;
insert into event_log
(id, timestamp, event)
values
(seq_event.nextval,
sysdate,
'succeed! v_id=' || v_old_id || 'cnt=' || v_cnt || ',dmlcommand=' ||
dmlcommand || ',v_name=' || v_name);
commit;
exception
when others then
rollback;
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
insert into event_log
(id, timestamp, event)
values
(seq_event.nextval,
sysdate,
v_sqlcode || '|' || v_sqlerrm || ',v_old_id=' || v_old_id || ',cnt=' ||
v_cnt || ',v_name=' || v_name || ',dmlcommand=' || dmlcommand);
commit;
END;
/
这里简单起见,就写了对insert和delete的处理过程;呵,有点懒呀!
二 在目的库配置应用
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'TEST.T11_19',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.T11_DML_HANDLER',
apply_database_link=> NULL);
END;
/
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'TEST.T11_19',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.T11_DML_HANDLER',
apply_database_link=> NULL);
END;
/
配置好以后,可以进行如下确认:
SELECT object_name,operation_name,user_procedure,
error_handler,apply_database_link
FROM dba_apply_dml_handlers;
这样就OK了!
测试一下:
源库:
SQL> insert into test.t11 values ('333');
1 row created.
SQL> commit;
Commit complete.
目的库:
SQL> select * from test.t11_19;
NO NAME
---------- ------------------------------
333
测试删除:
源库:
SQL> delete from test.t11;
1 row deleted.
SQL> commit;
目的库:
SQL> /
no rows selected
源库:
SQL> insert into test.t11 values ('444');
1 row created.
SQL> commit;
Commit complete.
目的库:
SQL> select * from test.t11_19;
NO NAME
---------- ------------------------------
444 test444
SQL>
源库:
SQL> delete from test.t11;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
目的库:
SQL> /
no rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9375/viewspace-498019/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9375/viewspace-498019/