[20150610]使用物化视图同步数据.txt

[20150610]使用物化视图同步数据.txt

--昨天听别人的一个需求要同步一个表的数据,要求使用golden gate有点小题大作。实际上物化事务就可以了，自己以前做过一些测试，也
--许没做记录，这次做一个记录。

1.建立测试环境：
--源数据库10g  10.2.0.4.0  IP=192.168.100.89
--同步表T。
create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20));
insert into t  select rownum,'test' from dual connect by levelcommit ;

--目的数据库11g 11.2.0.3.0 . IP=192.168.100.40
CREATE  PUBLIC DATABASE LINK "TEST089.COM" CONNECT TO SCOTT  IDENTIFIED BY btbtms USING '192.168.100.89:1521/test';
--建立一样的表：
create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20));

SCOTT@test> select * from t@test089.com where rownum=1;
        ID NAME
---------- --------------------
         1 test

--测试dblink通过。

2.测试同步：
--源数据库10g  10.2.0.4.0 
create materialized view log on t;

--目的数据库11g 11.2.0.3.0 .
create materialized view t on prebuilt table refresh fast start with sysdate next sysdate + 1/24/60 as select * from t@test089.com;

3.开始一次测试：
--源数据库10g  10.2.0.4.0 做一些dml操作看看：

insert into t values (11,'aaa');
commit ;
update t set name='xxx' where id=5;
commit ;
delete from t where id=2;
commit ;

SCOTT@test> select * from t;
          ID NAME
------------ --------------------
           1 test
           3 test
           4 test
           5 xxx
           6 test
           7 test
           8 test
           9 test
          10 test
          11 aaa

10 rows selected.

--等1分钟观察目的数据库情况：
--目的数据库11g 11.2.0.3.0 .

SCOTT@test> select * from t;
        ID NAME
---------- --------------------
        11 aaa
         5 xxx

--搞错，应该copy数据先，重做。

drop  materialized view t ;
delete from t;
commit ;

SCOTT@test> select * from t;
no rows selected

SCOTT@test> insert into t  select * from t@test089.com;
10 rows created.

SCOTT@test> commit ;
Commit complete.

create materialized view t on prebuilt table refresh fast start with sysdate next sysdate + 1/24/60 as select * from t@test089.com;

4.再重复测试：
--源数据库10g  10.2.0.4.0 做一些dml操作看看：
insert into t values (12,'bbb');
commit ;
update t set name='yyy' where id=5;
commit ;
delete from t where id=3;
commit ;

SCOTT@test> select * from t;
          ID NAME
------------ --------------------
           1 test
           4 test
           5 yyy
           6 test
           7 test
           8 test
           9 test
          10 test
          11 aaa
          12 bbb

10 rows selected.

--等1分钟观察目的数据库情况：
--目的数据库11g 11.2.0.3.0 .

SCOTT@test> select * from t;
        ID NAME
---------- --------------------
         1 test
         4 test
         5 yyy
         6 test
         7 test
         8 test
         9 test
        10 test
        11 aaa
        12 bbb

10 rows selected.

SCOTT@test> select * from t minus select * from t@test089.com;
no rows selected

SCOTT@test> select * from t@test089.com  minus select * from t;
no rows selected

通过视图可以观察同步的情况：
--目的数据库11g 11.2.0.3.0 :
SCOTT@test> SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, FAILURES FROM USER_JOBS;
       JOB LOG_USER                       LAST_DATE           NEXT_DATE             FAILURES
---------- ------------------------------ ------------------- ------------------- ----------
       743 SCOTT                          2015-06-10 09:20:29 2015-06-10 09:21:29          0