高级复制实施手记(5)

5. 创建物化视图组

本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明.

http://husthxd.itpub.net/post/11/112296

1) 在主体站点simis上创建物化视图日志
用户包括biuser和bm
在biuser上创建物化视图日志的时候如果出现因为无主键约束而导致物化视图日志创建不了的情况，无需理会，忽略错误。
创建biuser下面的物化视图日志
connect biuser/biuser@simis
set heading off
set pagesize 0
set linesize 800
spool create_biuser_mv_log.sql

select 'create materialized view log on biuser.'||table_name||';'
from user_tables
/

spool off
spool c:spoolcreate_biuser_mv_log.log
@create_biuser_mv_log.sql
spool off
host del create_biuser_mv_log.sql

创建bm的物化视图日志
connect bm/bm@simis
set heading off
set pagesize 0
set linesize 800
spool create_bm_mv_log.sql

select 'create materialized view log on bm.'||table_name||';'
from user_tables
/

spool off
spool c:spoolcreate_bm_mv_log.log
@create_bm_mv_log.sql
spool off
host del create_bm_mv_log.sql

2) 在物化视图站点上创建存放物化视图的用户
conn system/manager@mv
创建相应的表空间
CREATE TABLESPACE biuser
DATAFILE 'biuser_mv1.dbf' SIZE 2000M AUTOEXTEND ON;
CREATE TABLESPACE biuser_index
DATAFILE 'biuser_index_mv1.dbf' SIZE 2000M AUTOEXTEND ON ;
CREATE TABLESPACE bm DATAFILE 'bm_mv1.dbf' SIZE 200M AUTOEXTEND ON;

创建biuser用户
drop user biuser cascade;
CREATE USER biuser IDENTIFIED BY biuser;
ALTER USER biuser DEFAULT TABLESPACE biuser QUOTA UNLIMITED ON biuser;
GRANT dba to biuser;
创建bm用户
drop user bm cascade;
CREATE USER bm IDENTIFIED BY bm;
ALTER USER bm DEFAULT TABLESPACE bm QUOTA UNLIMITED ON bm;
GRANT connect,resource TO bm;

3) 创建私有数据库链接,连接主体站点上的代理刷新用户
conn biuser/biuser@mv
CREATE DATABASE LINK simis.simis CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;
conn bm/bm@mv
CREATE DATABASE LINK simis.simis CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

4) 创建物化视图组biuser_repg&bm_repg
CONNECT mviewadmin/mviewadmin@mv
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'biuser_repg',
master => 'simis.simis',
propagation_mode => 'ASYNCHRONOUS');
END;
/
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'bm_repg',
master => 'simis.simis',
propagation_mode => 'ASYNCHRONOUS');
END;
/
5) 创建刷新组
每天下午4点刷新
BEGIN
DBMS_REFRESH.MAKE (
name => 'mviewadmin.biuser_refg',
list => '',
next_date => SYSDATE,
interval => 'trunc(SYSDATE+1) + 16/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE);
END;
/
BEGIN
DBMS_REFRESH.MAKE (
name => 'mviewadmin.bm_refg',
list => '',
next_date => SYSDATE,
interval => 'trunc(SYSDATE + 1) + 16/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE);
END;
/

6) 创建对应于主体站点主表的物化视图
这里的物化视图配置为可更新,如果为只读的话去掉WITH PRIMARY KEY FOR UPDATE
conn bm/bm@mv
set heading off
set linesize 800
set pagesize 0
spool bm_drop_mv.sql

select 'drop materialized view bm.'||table_name||';'
from user_tables
/
spool off

connect mviewadmin/mviewadmin@mv
@bm_drop_mv.sql
conn biuser/biuser@mv
spool biuser_drop_mv.sql
select 'drop materialized view biuser.'||table_name||';'
from user_tables
/
spool off
connect mviewadmin/mviewadmin@mv
@biuser_drop_mv.sql

创建biuser的物化视图
connect biuser/biuser@simis
set heading off
set pagesize 0
set linesize 800

先创建有基表有主键的物化视图(refresh with primary key)
spool create_biuser_mv.sql

select 'create materialized view biuser.'||table_name||' parallel (degree 4) refresh with primary key force as select *from biuser.'||table_name||'@simis.simis;'
from user_tables
where table_name not like '%$%'
/

spool off

再创建有基表没有主键的物化视图(refresh with rowid)
spool create_biuser_rowid_mv.sql

select 'create materialized view biuser.'||table_name||' parallel (degree 4) refresh with rowid force as select *from biuser.'||table_name||'@simis.simis;'
from user_tables
where table_name not like '%$%'
/

spool off

connect mviewadmin/mviewadmin@mv
spool c:spoolcreate_biuser_mv.log
@create_biuser_mv.sql
@create_biuser_rowid_mv.sql

spool off
创建bm的物化视图
connect bm/bm@simis
set heading off
set pagesize 0
set linesize 800
先创建有基表有主键的物化视图(refresh with primary key)
spool create_bm_mv.sql

select 'create materialized view bm.'||table_name||' parallel (degree 4) refresh with primary key force as select *from bm.'||table_name||'@simis.simis;'
from user_tables
where table_name not like '%$%'
/

spool off

再创建有基表没有主键的物化视图(refresh with rowid)
spool create_bm_rowid_mv.sql
select 'create materialized view bm.'||table_name||' parallel (degree 4) refresh with rowid force as select *from bm.'||table_name||'@simis.simis;'
from user_tables
where table_name not like '%$%'
/

spool off
connect mviewadmin/mviewadmin@mv
spool create_bm_mv.log
@create_bm_mv.sql
@create_bm_rowid_mv.sql
spool off

7) 在物化视图组中加入对象
connect biuser/biuser@mv
set heading off
set pagesize 0
set linesize 800
spool add_biuser_mv_object.sql

select 'exec DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (gname => ''biuser_repg'',sname =>''biuser'', oname => '||''''||table_name||''''||',type => ''SNAPSHOT'',min_communication => TRUE);'
from user_tables
where table_name not like '%$%'
/

spool off
connect mviewadmin/mviewadmin@mv
spool c:spooladd_biuser_mv_object.log
@add_biuser_mv_object.sql
spool off
host del add_biuser_mv_object.sql
connect bm/bm@mv
set heading off
set pagesize 0
set linesize 800

spool add_bm_mv_object.sql

select 'exec DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (gname => ''bm_repg'',sname =>''bm'', oname => '||''''||table_name||''''||',type => ''SNAPSHOT'',min_communication => TRUE);'
from user_tables
where table_name not like '%$%'
/

spool off
connect mviewadmin/mviewadmin@mv
spool c:spooladd_bm_mv_object.log
@add_bm_mv_object.sql
spool off
host del add_bm_mv_object.sql

8) 在物化视图刷新组中加入对象
connect biuser/biuser@mv
spool add_biuser_fresh_object.sql

select 'exec DBMS_REFRESH.ADD (name => ''mviewadmin.biuser_refg'',list => '||''''||'biuser.'||table_name||''''||',lax => TRUE);'
from user_tables
/

spool off
connect mviewadmin/mviewadmin@mv
spool c:spooladd_biuser_fresh_object.log
@add_biuser_fresh_object.sql
spool off
connect bm/bm@mv

spool add_bm_fresh_object.sql
select 'exec DBMS_REFRESH.ADD (name => ''mviewadmin.bm_refg'',list => '||''''||'bm.'||table_name||''''||',lax => TRUE);'
from user_tables
/

spool off
connect mviewadmin/mviewadmin@mv
spool c:spooladd_bm_fresh_object.log
@add_bm_fresh_object.sql
spool off
激活复制环境
connect repadmin/repadmin@simis
execute dbms_repcat.resume_master_activity('bm_repg');
execute dbms_repcat.resume_master_activity('biuser_repg');
注意：如果创建主体组只是为了生成物化视图组，可以用下面的命令在没有生成复制支持的情况下启动复制环境
execute dbms_repcat.resume_master_activity('bm_repg',true);
execute dbms_repcat.resume_master_activity('biuser_repg',true);