内外网数据的简单单向同步

由于测试需要，需要把外网数据定时同步到内网中，并且需要内网数据可以进行DML操作，且需要在下次同步时内网变化的数据需要全部丢弃。由于是单向同步，可以用可写物化视图来实现这个功能。

下面是主要步骤

1、在内网test用户下建立DBLINK
create database link WAI.SOUCHANG.COM
connect to SOUCHANG2 identified by password
using 'wai1';

2、在外网souchang2用户下建立物化视图日志
住：由于用的是主键物化视图，且无主键的表都是一些临时表，故需要将这些表排除在复制队列外

/* 批量生成建立物化视图日志

select 'CREATE MATERIALIZED VIEW LOG ON '||table_name||';' from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/

3、在内网test用户下建立刷新组
BEGIN
DBMS_REFRESH.MAKE (
name => 'test.wai_refresh',
list => '',
next_date => TRUNC(SYSDATE)+4/24,
interval => 'TRUNC(SYSDATE)+4/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => FALSE,
refresh_after_errors => FALSE);
END;

4、在内网test用户下建立物化视图

/*批量建立物化视图
select 'CREATE MATERIALIZED VIEW '||table_name||
' REFRESH FAST WITH PRIMARY KEY for update AS SELECT * FROM '||table_name||'@wai.souchang.com;' CREATE_MATERIALIZED_VIEW
from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/

5、在内网test用户下将物化视图添加到刷新组

/*批量将物化视图添加到刷新组

select 'BEGIN DBMS_REFRESH.ADD (name =>''test.wai_refresh'',list => ''test.'||table_name||',lax => TRUE); END;' ADD_REFRESH
from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/
6、在内网test用户下添加索引（用TOAD导出来的）

由于物化视图是可写的的，故必须要同步内外网的序列，以免出错

souchang2_indexes.sql

7、添加同步序列job

create or replace procedure p_sync_sequence is
l_drop_seq varchar2(512);
l_create_seq varchar2(512);
l_seq_val number;
l_sql_seqval varchar2(512);

l_procedure_name varchar2(100):='p_sync_sequence';
l_sqlcode number;
l_sqlerrm varchar2(4000);
begin
for c_drop_seq in ( select sequence_name from user_sequences@wai.souchang.com) loop
begin
l_drop_seq:='drop sequence '|| c_drop_seq.sequence_name;
execute immediate l_drop_seq;
Exception
When Others Then
null;
end;

begin
l_sql_seqval:='select '||c_drop_seq.sequence_name||'.nextval@wai.souchang.com@firedrake from dual';
execute immediate l_sql_seqval into l_seq_val;
Exception
When Others Then
null;
end;
begin
l_create_seq:='CREATE SEQUENCE '|| c_drop_seq.sequence_name||' INCREMENT BY 1 START WITH '||l_seq_val||' MAXVALUE 999999999999 MINVALUE 1 nocache';
execute immediate l_create_seq;
Exception
When Others Then
null;
end;
end loop;

exception
when others then
rollback;
l_sqlcode:=sqlcode;
l_sqlerrm:=sqlerrm;
insert into PROCEDURE_ERR_RECORD values(seq_PROCEDURE_ERR_RECORD.Nextval,l_procedure_name,l_sqlcode,l_sqlerrm,sysdate);
commit;
dbms_output.put_line(l_sqlcode||':'||l_sqlerrm);

end p_sync_sequence;

var v_job number;
begin
dbms_job.submit(:v_job,'p_sync_sequence;',trunc(sysdate)+6/1440,'trunc(sysdate)+1+6/24');
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63705/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63705/