http://www.itpub.net/150607.html
http://yangtingkun.itpub.net/post/468/246820
http://cosio.itpub.net/post/10244/467235
1.通过物化视图方式同步两服务器中数据表的两种不同方式的比较
最近在搞Oracle数据库物化视图的数据同步,通过查阅资料,找到两种不同的方式。
表设置:服务器A为主服务器,有一个表test,服务器B需要同步服务器A中的test表数据。服务器B中有个数据库链接LINK_A
方法一:
在服务器A上
CREATE MATERIALIZED VIEW LOG ON TEST;
在服务器B上
CREATE MATERIALIZED VIEW TEST REFRESH FAST ON DEMAND
START WITH TO_DATE('18-09-2008 00:00:00','dd-mm-yyyy hh24:mi:ss') NEXT SYSDATE+1 AS
SELECT * FROM TEST@LINK_A;
优点:代码较少,在服务器B上运行完上述SQL命令后,会自动在服务器B上建立TEST表,并从服务器A将TEST表中的数据完全复制到服务器B中,还自动建立一个JOB,并每隔指定时间用FAST方式同步数据。
缺点:在服务器B上的TEST表完全依赖于物化视图TEST,一旦将物化视图TEST删除,自动建立的JOB、TEST表以及TEST相关的索引、触发器等都会消失。
方法二:
在服务器A上
CREATE MATERIALIZED VIEW LOG ON TEST;
在服务器B上
CREATE TABLE TEST AS SELECT * FROM TEST@LINK_A WHERE 1=2;
CREATE MATERIALIZED VIEW TEST ON PREBUILT TABLE REFRESH FAST ON DEMAND
AS SELECT * FROM TEST@LINK_A;
DBMS_MVIEW.REFRESH(LIST=>'TEST',METHOD=>'COMPLETE');
SYS.DBMS_JOB.SUBMIT(JOB=>:JOB,WHAT=>'DBMS_MVIEW.REFRESH(LIST=>''TEST'',METHOD=>''FAST'');',
NEXT_DATE=>TO_DATE('18-09-2008 00:00:00','dd-mm-yyyy hh24:mi:ss'),
INTERVAL=>'SYSDATE+1');
优点:在服务器B上的TEST表不依赖于物化视图TEST,无论对物化视图做任务操作,都不会影响TEST表中的数据和相关触发器等设置。
缺点:代码较多,得手动建立TEST表结构,手动做一次完全复制,手动建立JOB。
文章出处:DIY部落(http://www.diybl.com/course/7_databases/database_other/2008921/144194.html#)
2.怎么样使用物化视图来同步数据
mv可以用来同步数据,一般采用主键同步或者是ROWID同步,我们这里只讨论主键同步(Rowid同步其实是为Rowid在目标表上创建了一个隐含索引,原理跟主键同步一样)。mv的主要原理就是记录更改的日志,并同步到远程。
一个简单的例子:
代码:--------------------------------------------------------------------------------
create materialized view log on AUCTION_FEEDBACKS;
create materialized view AUCTION_FEEDBACKS [on prebuilt table] refresh fast as
select * from AUCTION_FEEDBACKS@lnk_db1;
exec dbms_mview.refresh('AUCTION_FEEDBACKS',method => 'Complete');
exec dbms_mview.refresh('AUCTION_FEEDBACKS');
declare jobid number;
begin
sys.dbms_job.submit(job => jobid,
what => 'dbms_mview.refresh(''AUCTION_FEEDBACKS'');',
next_date => sysdate,
interval => 'sysdate+5/1440');
commit;
end;
/
.--------------------------------------------------------------------------------
其中,第1步是在主表上创建mv log,
第2步是在远程站点上创建mv,注意,如果选择了选项on prebuild table的话,远程必须存在与主站点结构一样的表,但可以没有数据。
第3步是全同步,如果没有选择on prebuild table,这一步可以省略,
第4步是增量刷新,在以后的情况下,一般都只需要做增量刷新即可,
第5步是创建一个自动刷新的作业来进行刷新,如每5分钟刷新一次,这个操作也可以同crontab来代替。
dba_mviews记录了远程站点上mv的数目与属性,需要在创建MV的远程站点上查询。
sys.mlog$则记录了主站点上的mv的log数目,如果一个master对应到多个站点,也只有一条记录,对应到dba_mview_logs视图,需要在主站点查询。
sys.slog$记录了主站点上已经注册成功的主表信息,如果一个主表被复制到多个站点,则对应多条记录,在主站点查询。
dba_snapshot_logs存放了mv的log日志,如果对应到多个站点,则每个站点都对应一条记录,因为远程站点的snapshot_id是不一样的。其实sys.mlog$与sys.slog$的关联就是组成dba_snapshot_logs的一个部分,通过查询dba_views可以看到其脚本。
dba_registered_snapshots记录了远程站点的注册信息,只记录注册成功的远程站点,通过snapshot_id可以与dba_snapshot_logs关联。如
代码:--------------------------------------------------------------------------------
SQL>select t.log_owner,t.master,t.log_table,t.current_snapshots,
r.owner,r.name,r.snapshot_site
from dba_snapshot_logs t,dba_registered_snapshots r
where t.snapshot_id= r.snapshot_id(+)
.--------------------------------------------------------------------------------
删除mv的时候,需要先删除mv,再删除mv日志
远程站点:drop materialized view AUCTION_FEEDBACKS;
主站点:drop materialized view log on AUCTION_FEEDBACKS;
注意,删除mv的时候,如果主站点需要分发到多个远程站点,只有当所有远程站点的MV删除完成后,才可以删除MV日志。
删除远程站点的MV的时候,要保证与主站点的通信顺畅,如果网络不通,则主站点无法正常Unregister MV,而主站点的mv log又因为其它站点而不删除,将可能引起主站点mlog表的膨胀(因为它不知道这个mv的删除,需要等待这个站点的刷新)。这个时候,可以手工强行解除注册。
exec DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id);
如果mlog因为日志曾经很多而变得很大的时候,对mlog的操作如日志删除将变得很慢,因为mlog上没有任何索引,走的都是全表扫描,这个时候,我们可以move该日志表或者是等日志表没有记录的时候truncate该表。
alter table mlog$_auction_feedbacks move;
如果要对有mv复制的表增加字段,最好在停机情况下进行,因为这样不会产生mv log,而且最好采用prebuild模式,因为这种模式下,删除mv的定义将保留表(也就是不删除表与相关数据)。相关步骤:
停机模式下:
代码:--------------------------------------------------------------------------------
1、远程站点:drop materialized view MV_TABLE;
2、主站点:alter table master_table add new_field number;
3、远程站点:alter table mv_table add new_field number;
4、远程站点:create materialized view mv_table on prebuilt table
refresh fast as
select * from master_table@lnk_db;
.--------------------------------------------------------------------------------
为什么要停机操作,是因为创建快速刷新的mv的时候,将删除主站点上创建mv时间点之前的相关log,所以在删除mv与重新创建mv之前,不能对主表有任何dml产生新的日志,否则将可能引发数据的不一致。