oracle高级复制配置手记

一、准备工作

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;

GLOBAL_NAME
--------------------------------------------------------------------------------
RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM

如果全局数据库名设置不符合规范，可以通过如下语句动态修改。

alter database rename global_name to  RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM;     （主站点）
alter database rename global_name to  TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;      （物化视图站点）

1.3 修改tnsnames.ora 文件

主站点和物化视图站点的tnsnames.ora参数文件中都添加下列内容，注意需要按照实际情况修改HOST的ip地址，以及SERVICE_NAME的值，PORT一般都默认为1521。

RANDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.0.66)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = randy)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.168.0.68)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

二、主体站点配置

2.1 建立复制管理用户并授权

以sys或者system用户登陆到主体站点
conn sys/test 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;

BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE (
                 next_date => SYSDATE,
                 interval => 'SYSDATE + 1/24',
                 delay_seconds => 0);
END;
/
Commit;

2.6 对复制用户授权

conn sys/test as sysdba

create user rbt
identified by rbt
default tablespace users
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 建立复制测试表

conn sys/test as sysdba

create table rbt.jobs
as
select * from hr.jobs;

alter table rbt.jobs
add constraint pk_jobs primary key(job_id);

2.8 在主体站点上建立主体组，将复制对象增加到复制组中

conn repadmin/repadmin

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP (
              gname => 'RBTSYN');
END;
/
Commit;

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
              gname => 'RBTSYN',
              type => 'TABLE',
              oname => 'JOBS',
              sname => 'RBT',
              use_existing_object => TRUE,
              copy_rows => FALSE);
END;
/

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
              gname => 'RBTSYN',
              type => 'INDEX',
              oname => 'PK_JOBS',
              sname => 'rbt',
              use_existing_object => TRUE,
              copy_rows => FALSE);
END;
/

--添加的时候如果一次没有成功，再次添加可能会报ORA-23308或ORA-23309的错误
--使用DBMS_REPCAT.DROP_MASTER_REPOBJECT删除对象后重新添加

2.9 在主体站点上生成复制支持

BEGIN
  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
              sname => 'RBT',
              oname => 'JOBS',
              type => 'TABLE',
              min_communication => TRUE);
END;
/

2.10 在主体站点上开始复制

BEGIN
  DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
              gname =>'RBTSYN');
END;
/

2.11 主体站点上面建立物化视图日志表

conn rbt/rbt

create materialized view log on jobs;


3. 物化视图站点配置

--连接到物化视图站点
conn sys/test as sysdba;

3.1 建立物化视图管理员并授权

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 RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM USING 'randy';

3.5 从物化视图站点建立到主站点上代理物化视图管理员的数据库链

conn mvadmin/mvadmin

create database link RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to repadmin identified by repadmin;

3.6 在物化视图站点设置清除延迟序列的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 => 'RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM',
                 interval => 'sysdate + 10/(60*60*24)',
                 next_date => SYSDATE,
                 stop_on_error => FALSE,
                 delay_seconds => 0,
                 parallelism => 0);
END;
/

3.8 建立复制用户并对其授权

create user rbt identified by rbt;
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

CREATE DATABASE LINK RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM
CONNECT TO repadmin IDENTIFIED BY repadmin;

3.10 在物化视图管理帐号上建立物化视图组

--以物化视图管理员身份登陆物化视图站点
CONNECT mvadmin/mvadmin

--物化视图组必须和复制站点上的复制组名称相同
BEGIN
  DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
              gname => 'RBTSYN',
              master => 'RANDY.REGRESS.RDBMS.DEV.US.ORACLE.COM',
              propagation_mode => 'ASYNCHRONOUS');
END;
/

--创建刷新组，从物化视图上面刷新数据到本地表
--对于只包含只读物化视图的站点，不需要此步骤
BEGIN
  DBMS_REFRESH.MAKE (
               name => 'mvadmin.rbt_refresh',
               list => '',
               next_date => SYSDATE,
               interval => 'sysdate + 10/(60*60*24)',
               implicit_destroy => FALSE,
               rollback_seg => '',
               push_deferred_rpc => TRUE,
               refresh_after_errors => FALSE);
END;
/

--创建物化视图

create table rbt.jobs
as
select * FROM [e-mail]
where 1=2;

CREATE MATERIALIZED VIEW RBT.JOBS
ON PREBUILT TABLE
REFRESH FAST
NEXT sysdate + 5/1440
AS
SELECT * FROM [e-mail];

BEGIN
   DBMS_REFRESH.ADD(
     name => '"MVADMIN"."RBT_REFRESH"',
     list => '"RBT"."JOBS"',
     lax => TRUE);
END;

--将物化视图添加到刷新组
BEGIN
   DBMS_REFRESH.ADD(
     name => '"MVADMIN"."RBT_REFRESH"',
     list => '"RBT"."JOBS"',
     lax => TRUE);
END;

--将物化视图添加到物化视图组
--对于只读物化视图，此步骤可以省略
BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      gname => '"RBTSYN"',
      sname => '"RBT"',
      oname => '"JOBS"',
      type => 'SNAPSHOT',
      min_communication => FALSE);
END;

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      gname => '"RBTSYN"',
      sname => '"RBT"',
      oname => '"PK_JOBS"',
      type => 'INDEX');
END;


--至此，物化视图的配置就全部完成了。


四、生成测试数据，并验证配置

--在主站点执行数据修改
begin
  for i in 1..20
  loop
    insert into jobs values(i,i,i,i);
  end loop;
  commit;
end;
/

--在复制站点验证结果

conn rbt/rbt

select * from jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
1          1                                            1          1
10         10                                          10         10
......
19         19                                          19         19
20         20                                          20         20

5 注意事项

5.1 复制对象的限制

序列不能复制；
主键索引和其他索引，均需要添加到复制对象里面，不能自动复制；
ddl语句，不会自动复制。

5.2 如何使用序列同步

只能对所有的序列，建立一个job，扫描对应表的最大id值，并定时执行，或者在需要使用的时候，手工执行序列同步job。


本文参照网上的一篇文章《oracle物化视图配置指导书》，因为作者没有留下姓名，只有表示精神上的感谢了。