建立可更新的物化视图

环境

 

RAC和单实例建立可更新的物化视图

 

SQL> select * from gv$version;

   INST_ID BANNER
---------- ----------------------------------------------------------------
         2 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         2 PL/SQL Release 10.2.0.4.0 - Production
         2 CORE 10.2.0.4.0      Production
         2 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         2 NLSRTL Version 10.2.0.4.0 - Production
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         1 PL/SQL Release 10.2.0.4.0 - Production
         1 CORE 10.2.0.4.0      Production
         1 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         1 NLSRTL Version 10.2.0.4.0 - Production

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

 

1.配置复制管理用户repadmin(所有节点)

 

create user repadmin identified by repadmin;

 

execute dbms_defer_sys.register_propagator('repadmin');

grant execute any procedure to repadmin;

execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');

grant comment any table to repadmin;

grant lock any table to repadmin;

grant select any dictionary to repadmin;

 

2.启用global_name(所有节点)

 

alter system set global_names=true scope=spfile;

 

alter system set db_domain='lab.com' scope=spfile;

 

alter database rename global_name to gc.lab.com;

 

alter database rename global_name to orcl.lab.com;

 

select * from global_name;

 

3.建立dblink(所有节点)

 

ORCL上

 

CREATE PUBLIC DATABASE LINK "GC.LAB.COM"
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING 'GC';

 

GC上

 

CREATE PUBLIC DATABASE LINK "ORCL.LAB.COM"
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING 'orcl1';

 

通过select * from 来测试

 

创建私有database link,用于拉起和停止节点repgroup的状态

 

repadmin下

 

ORCL

 

CREATE DATABASE LINK "GC.LAB.COM"
 CONNECT TO REPADMIN
 IDENTIFIED BY ;

 

GC

 

CREATE DATABASE LINK "ORCL.LAB.COM"
 CONNECT TO REPADMIN
 IDENTIFIED BY ;

 

通过select * from 来测试

 

4.源表上建立materialized view log(ORCL)

 

CREATE MATERIALIZED VIEW LOG ON TEST.BA_ALCCLS
NOCACHE
LOGGING
NOPARALLEL
WITH PRIMARY KEY;

 

建立复制组

 

SQL> exec dbms_repcat.create_master_repgroup('mv');

把源表添加到复制组中

 

SQL> exec dbms_repcat.create_master_repobject('test','ba_alccls','table',gname =>'mv');

 

为源表添加复制支持

 

SQL>  exec dbms_repcat.generate_replication_support('test','ba_alccls','table');

 

激活复制组

 

SQL> exec dbms_repcat.resume_master_activity('mv');

 

5.在物化视图站点

 

CREATE MATERIALIZED VIEW TEST.BA_ALCCLS
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT "BA_ALCCLS"."ALCZX" "ALCZX", "BA_ALCCLS"."ALCLB" "ALCLB"
  FROM "TEST"."BA_ALCCLS"@orcl.lab.com "BA_ALCCLS";

 

建立物化视图复制组

 

SQL> exec dbms_repcat.create_mview_repgroup('mv','orcl.lab.com');

 

建立物化视图对象

 

SQL> exec dbms_repcat.create_mview_repobject('test','ba_alccls','snapshot',gname =>'mv');

 

建立refresh group

 

DECLARE
  SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  SnapArray(1) := 'TEST.BA_ALCCLS';
  SnapArray(2) := NULL;
  SYS.DBMS_REFRESH.MAKE (
    name => 'TEST.mv_refresh'
    ,tab  => SnapArray
    ,next_date => sysdate
    ,interval  => 'sysdate+10/1440'
    ,implicit_destroy => FALSE
    ,lax => TRUE
    ,job => 0
    ,rollback_seg => NULL
    ,push_deferred_rpc => FALSE
    ,refresh_after_errors => FALSE
    ,purge_option => NULL
    ,parallelism => NULL
    ,heap_size => NULL
  );
Commit;
END;
/