1.如果是跨库的物化视图,且这些服务器系统时间都不相同,Oracle怎么保证数据的一致性呢?
2.Oracle通过时间列来判断哪些记录需要刷新,而Oracle的SYSDATE时间是来自系统时间的,那么如果系统时间改变,Oracle如何保证刷新了之后不会丢失数据,如何保证不会重复刷新呢?
于是在2013年夏天的某个周六(记忆中),我设计这个实验验证了Oracle的强大!
1.调整服务器时间
设置两个服务器,红和蓝,红色为基表所在和一个物化视图,蓝色为跨库的物化视图,调整其时间必须不一样,最好是差别特别的大。
HZH@MASTER> select sysdate from dual;
SYSDATE
-------------------
2013-06-29 17:30:58
06:25:05 SQL> select sysdate from dual;
SYSDATE
-------------------
2014-06-30 06:25:14
2.创建基表和物化视图
HZH@MASTER> create table test (id number primary key);
Table created.
HZH@MASTER> create materialized view log on test;
Materialized view log created.
HZH@MASTER> create materialized view mv_test_on_master refresh fast as select * from test;
Materialized view created.
06:25:10 SQL> create public database link to_master connect to hzh identified by hzh using 'MASTER';
Database link created.
06:25:14 SQL> create materialized view mv_test_on_client refresh fast
06:25:43 2 as select * from test@to_master;
Materialized view created.
3.插入记录
HZH@MASTER> insert into test values (1);
1 row created.
HZH@MASTER> commit;
Commit complete.
4.查看物化视图日志
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:40:26 1 4000-01-01 00:00:00 I N FE
5.刷新红色服务器的物化视图
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:41:03 1 2013-06-29 17:40:58 I N FE
HZH@MASTER> select MVIEW_NAME,LAST_REFRESH_DATE,STALENESS from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58 FRESH
HZH@MASTER> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
这一切看起来都是那么的正常,没有什么问题,继续往下看
6.查看蓝色服务器的物化视图
它的最后刷新时间应该是创建物化视图的时间,由输出来分析,user_mviews的时间是2014-06-30 06:25:49,返回创建的时候,敲回车之前是06:25:43,也就是说user_mviews所记录的时间是蓝色服务器创建物化视图时候的系统时间(蓝色系统时间);然而再看user_mview_refresh_times,它的时间是2013-06-29 17:38:08,推测,可能是创建物化视图的时候,红色服务器所记录的系统时间(红色时间),红色时间的判定,后面会验证!
06:35:05 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:25:49
06:35:07 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:38:08
7.再插入一条记录
HZH@MASTER> insert into test values (2);
1 row created.
HZH@MASTER> commit;
Commit complete.
8.刷新蓝色物化视图
06:38:46 SQL> exec dbms_mview.refresh('mv_test_on_client','f');
PL/SQL procedure successfully completed.
06:38:50 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:38:50
这个时候就可以通过计算来验证,user_mview_refresh_times的最后刷新时间,是不是红色服务器当时的系统时间,
2014-06-30 06:25:49对应2013-06-29 17:38:08
2014-06-30 06:38:50对应2013-06-29 17:51:08
9.查看物化视图日志
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2013-06-29 17:51:08 I N FE
这里需要详细进行说明:蓝色物化视图刷新的时间,是2014-06-30 06:38:50,而主服务器记录到物化视图日志中的SNAPTIME$$字段的却是红色服务器自己的时间!
设想一下,如果物化视图日志中的SNAPTIME$$字段是2014-06-30 06:38:50,会发生什么样的情况,也就是如下虚拟出来的情况
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2014-06-30 06:38:50 I N FE
这样,红色的物化视图刷新时间为2013-06-29 17:40:58,如下输出所示
HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
假设现在的时间是2013-06-29 19:40:58,我们刷新了红色物化视图,id为2的记录被刷新,红色物化视图的最后刷新时间会变成2013-06-29 19:40:58,而物化视图日志中的时间是不会变的!这样当2013-06-29 21:40:58再刷新的时候,因为SNAPTIME$$列的值还是大于2013-06-29 19:40:58,还会刷新一次(重复刷新),而使得物化视图的最后刷新时间变为2013-06-29 21:40:58,由于还是小于那个2014年,所以下次刷新的时候,还会刷新(多次重复刷新)!然而正常的情况应该是,红色的刷新一次之后,物化视图日志的那条记录就应该清空!
所以,Oracle的开发人员使用了如下的方法记录SNAPTIME$$
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2013-06-29 17:51:08 I N FE
当红色服务器刷新自己的物化视图时,日志中这条记录应该是被更新,然后最后的刷新时间会变成系统时间,大于SNAPTIME$$的值,然而SNAPTIME$$的值小于所有物化视图的最后刷新时间,则日志这条记录会被清空!稍后第11步再验证!
10.再插入记录并刷新
HZH@MASTER> insert into test values (3);
1 row created.
HZH@MASTER> commit;
Commit complete.
06:41:20 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:41:20
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:54:20 2 2013-06-29 17:51:08 I N FE
2013-06-29 17:54:20 3 2013-06-29 17:53:39 I N FE
HZH@MASTER> insert into test values (100);
1 row created.
HZH@MASTER> commit;
Commit complete.
HZH@MASTER> select sysdate from dual;
SYSDATE
-------------------
2013-06-29 17:57:14
2秒后刷新物化视图
06:41:33 SQL> exec dbms_mview.refresh('mv_test_on_client','f');
PL/SQL procedure successfully completed.
//此时主服务器时间约为2013-06-29 17:57:14 + 1至2秒=2013-06-29 17:57:16,即主服务器认为2013-06-29 17:57:16,蓝色的物化视图进行了刷新!
06:44:58 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:44:57
06:45:35 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:58:24 100 2013-06-29 17:57:16 I N FE
2013-06-29 17:58:24 2 2013-06-29 17:51:08 I N FE
2013-06-29 17:58:24 3 2013-06-29 17:53:39 I N FE
11.验证刚才第9步最后所说的那段话
物化视图日志如下
HZH@PROD> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 18:50:34 100 2013-06-29 17:57:16 I N FE
2013-06-29 18:50:34 2 2013-06-29 17:51:08 I N FE
2013-06-29 18:50:34 3 2013-06-29 17:53:39 I N FE
从主服务器角度看时间
HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
06:57:18 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
HZH@PROD> select name,mview_last_refresh_time
2 from user_base_table_mviews a, user_registered_mviews b
3 where a.mview_id = b.mview_id;
NAME MVIEW_LAST_REFRESH_
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
红色物化视图刷新之后,id为2,3,100的都将被刷新,然后更改红色物化视图刷新时间为当前sysdate=2013-06-29 18:53:53,然后,以上三条记录的SNAPTIME$$(2013-06-29 17:57:16,2013-06-29 17:51:08,2013-06-29 17:53:39)都小于等于所有物化视图的最后刷新时间(2013-06-29 18:53:53,2013-06-29 17:57:16),所以日志中的三条记录将全被清空!
18:53:36 HZH@PROD> exec dbms_mview.refresh('mv_test_on_master','f');
PL/SQL procedure successfully completed.
18:53:53 HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 18:53:53
18:54:14 HZH@PROD> select name,mview_last_refresh_time
18:54:28 2 from user_base_table_mviews a, user_registered_mviews b
18:54:34 3 where a.mview_id = b.mview_id;
NAME MVIEW_LAST_REFRESH_
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 18:53:53
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
07:07:05 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
验证日志是否全被清空
19:01:25 HZH@PROD> select sysdate,mlog$_test.* from mlog$_test;
no rows selected
kingsql分享
hongzhuohui@kingsql.com
2014-10-10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28389881/viewspace-1294749/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28389881/viewspace-1294749/