Oracle数据库物化视图配置与测试过程

1 准备工作

1.1 检查初始化参数
确认gloal_names是否为true，job_queue_process大于0。分别在主站点和物化视图站点执行下面两条sqlplus 命令，检查数据库初始化参数是否符合要求。
show parameter global_names
show parameter job
如果初始化参数设置的不满足要求，可以通过下列语句动态修改。
alter system set global_names = true;
alter system set job_queue_processes = 20;

1.2 检查全局数据库名称
两个数据库的db_domain 名称应该相同，只有db_name 不同。
通过下列语句检查主站点和物化视图站点的全局数据库名
select * from global_name;
如果全局数据库名设置不符合规范，可以通过如下语句动态修改。
alter database rename global_name to MAIN.HWTT.NET.COM; （主站点）
alter database rename global_name to MATEVIEW.HWTT.NET.COM; （物化视图站点）

1.3 修改tnsnames.ora 文件
主站点和物化视图站点的tnsnames.ora参数文件中都添加下列内容，注意需要按照实际情况修改HOST的ip地址，以及SERVICE_NAME的值，PORT一般都默认为1521.
MAIN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.2.105)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = portal)
)
)
MATEVIEW =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.2.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = portal)
)
)

2 主体站点配置

2.1 建立复制管理用户并授权
以sys或者system用户登陆到主体站点
Conn sys/mcp@ MAIN as sysdba;
--建立复制管理员，在实际应用中，需要修改repadmin帐号的密码，以确保安全。
CREATE USER repadmin IDENTIFIED BY repadmin;
--授权
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'repadmin');
END;
/
GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

2.2 注册传播者
这里使用了管理用户repadmin，也可以分别建立用户。
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
/

2.3 注册接收者
这里使用了管理用户repadmin，也可以分别建立用户。
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

2.4 建立物化视图站点复制管理员的代理用户
出于简单考虑，这里也使用repadmin 用户
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/
--授权
--对于repadmin 而言，不需要create session 权限
--但是这里如果新建用户的话，create session 权限则是必须的
GRANT CREATE SESSION TO repadmin;
GRANT SELECT ANY TABLE TO repadmin;

2.5 设置清除延迟序列的job
--以复制管理员身份登陆到主站点
CONNECT repadmin/repadmin@ MAIN;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/
Commit;

2.6 对复制用户授权
Conn sys/mcp@ MAIN;
ALTER USER rbt TEMPORARY TABLESPACE temp;
GRANT
CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
ALTER ANY MATERIALIZED VIEW,
CREATE DATABASE LINK,
select any dictionary
TO rbt;

2.7 在主体站点上建立主体组
--以复制管理员身份登陆复制站点
CONNECT mailto:repadmin/repadmin@MAIN]repadmin/repadmin@MAIN;
--建立名为RBTSYN 的复制组
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'RBTSYN');
END;
/
Commit;

2.8 在主体站点上将复制对象增加到复制组中
--主键所用的索引自动复制，其他索引需要明确添加到复制组中，在本指导书中，只举例--t_rbt_operation_logs表的复制
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'RBTSYN',
type => 'TABLE',
oname => 't_rbt_operation_logs',
sname => 'rbt',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
--其他索引添加到复制组语法如下
--作为约束的索引，当表在主站点上被创建的时候，会自动在复制站点上创建，但是对于
--提高性能的索引则不会被自动创建而必须手动指定。对于索引被复制到目标站点之后，
--等同于本地的索引，不需要再添加复制支持。
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'RBTSYN',
type => 'INDEX',
oname => 'PK_OPERATION_LOGS',
sname => 'rbt',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

2.9 在主体站点上生成复制支持
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'rbt',
oname => 't_rbt_operation_logs',
type => 'TABLE',
min_communication => TRUE);
END;
/

2.10 在主体站点上开始复制
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname =>'RBTSYN');
END;
/

2.11 主体站点上面建立物化视图日志表
--以复制用户连接到主站点
Conn rbt/rbt@ MAIN;
--建立物化视图日志表，FAST 刷新方式必须要求建立物化视图日志，COMPLETE 则不需要
CREATE MATERIALIZED VIEW LOG ON RBT.t_rbt_operation_logs;

3 物化视图站点配置

连接到物化视图站点
Conn sys/mcp@ MATEVIEW ;

3.1 建立物化视图管理员并授权
--在实际应用中，需要修改mvadmin帐号的密码，以确保安全。
CREATE USER mvadmin IDENTIFIED BY mvadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'mvadmin');
END;
/
GRANT COMMENT ANY TABLE TO mvadmin;
GRANT LOCK ANY TABLE TO mvadmin;
GRANT SELECT ANY DICTIONARY TO mvadmin;

3.2 注册传播者，并授权
这里使用mvadmin 用户，也可以建立单独的用户
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'mvadmin');
END;
/
--建立刷新者，并授权，这里使用mvadmin 用户刷新物化视图
--对于mvadmin 而言，不需要create session 权限
--但是这里如果新建用户的话，create session 权限则是必须的
GRANT CREATE SESSION TO mvadmin;
GRANT ALTER ANY MATERIALIZED VIEW TO mvadmin;

3.3 注册接收者，并授权
--注册接受者
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'mvadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

3.4 建立到主站点公用的数据库链接
--通过sys用户，建立公用的数据库链接，注意在正式使用的时候，需要按照需要修改数据--库链接的名字和主站点上面的别名
CREATE PUBLIC DATABASE LINK MAIN.HWTT.NET.COM USING 'MAIN';

3.5 从物化视图站点建立到主站点上代理物化视图管理员的数据库链
Conn mvadmin/mvadmin@ meteview;
--需要根据需要，需要正确填写repadmin帐号对应的密码.
CREATE DATABASE LINK main.hwtt.net.com CONNECT TO repadmin IDENTIFIED BY
repadmin;

3.6 在物化视图站点设置清除延迟序列的job
Conn mvadmin/mvadmin@ meteview;
--设置清除延迟序列的job
--如果物化视图站点只包括只读物化视图，这一步可以省略
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0,
rollback_segment => '');
END;
/

3.7 在物化视图站点上设置将修改推入到主站点的job
--如果物化视图站点只包括只读物化视图，这一步可以省略
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ' MAIN.HWTT.NET.COM',
interval => ' /*10:Secs*/ sysdate + 10/(60*60*24)',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/

3.8 对复制用户授权
Conn sys/mcp@ meteview;
ALTER USER rbt TEMPORARY TABLESPACE temp;
GRANT
CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
ALTER ANY MATERIALIZED VIEW,
CREATE DATABASE LINK,
select any dictionary
TO rbt;

3.9 在目标站点上面建立复制用户到主站点代理刷新者的数据库链
Conn rbt/rbt@ meteview;
CREATE DATABASE LINK main.hwtt.net.com CONNECT TO repadmin IDENTIFIED BY
repadmin;

3.10 在物化视图管理帐号上建立物化试图组
--建立物化视图组
--以物化视图管理员身份登陆物化视图站点
CONNECT mvadmin/mvadmin@ meteview；

--物化视图组必须和复制站点上的复制组名称相同
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'RBTSYN',
master => 'MAIN.HWTT.NET.COM',
propagation_mode => 'ASYNCHRONOUS');
END;
/
--创建刷新组，从物化视图上面刷新数据到本地表
--对于只包含只读物化视图的站点，不需要此步骤
BEGIN
DBMS_REFRESH.MAKE (
name => 'mvadmin.rbt_refresh',
list => '',
next_date => SYSDATE,
interval => '/*10:Secs*/ sysdate + 10/(60*60*24)',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
/

--创建物化视图
CREATE MATERIALIZED VIEW "RBT"."T_RBT_OPERATION_LOGS"
ON PREBUILT TABLE
REFRESH FAST
NEXT sysdate + 5/1440
AS
SELECT * FROM rbt.t_rbt_operation_logs @ main.hwtt.net.com;
--将物化视图添加到刷新组(建议通过企业管理器创建，因为在试验的过程中，好像有问题)
--这个步骤，可以通过企业管理器来添加比较方便
BEGIN
DBMS_REFRESH.ADD(
name => '"MVADMIN"."RBT_REFRESH"',
list => '"RBT"."T_RBT_OPERATION_LOGS"',
lax => TRUE);
END;

--将物化视图添加到物化视图组
--对于只读物化视图，此步骤可以省略
--这个步骤，可以通过企业管理器来添加比较方便
BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
gname => '"RBTSYN"',
sname => '"RBT"',
name => '"T_RBT_OPERATION_LOGS"',
type => 'SNAPSHOT',
min_communication => FALSE);
END;
BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
gname => '"RBTSYN"',
sname => '"RBT"',
name => '"PK_OPERATION_LOGS"',
type => 'INDEX');
END;

生成测试数据
begin
for i in 1..1000
loop
insert into t_rbt_operation_logs values(seq_operation_logs.nextval,'admin',sysdate,'test',null,
null,'success',null);
end loop;
end;
/
4 注意事项
4.1 复制对象的限制
序列不能复制。
主键索引和其他索引，均需要添加到复制对象里面，不能自动复制。
ddl语句，不会自动复制。
4.2 如何使用序列同步
只能对所有的序列，建立一个job，扫描对应表的最大id值，并定时执行，或者在需要使用的时候，手工执行序列同步job；