跨数据库跨版本跨平台进行高级复制

郑全

物化视图复制的设置

主站点：prod

物化视图站点：emrep

主机名：dbserver

复制用户：hr

检查初始化参数

SQL> connect sys/oracle@prod as sysdba ;

已连接。

SQL> show parameter global_names ;

NAME TYPE VALUE

------------------------------------ ----------- -------------------------

global_names boolean TRUE

SQL> show parameter job;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_PRocesses integer 20

SQL> select * from global_name ;

GLOBAL_NAME

------------------------------------------------------------------------------

prod 

SQL>

SQL> connect sys/oracle@emrep as sysdba ;

已连接。

SQL> show parameter global_names ;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

global_names boolean TRUE

SQL> show parameter job;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes integer 10

SQL> select * from global_name ;

GLOBAL_NAME

-----------------------------------------

emrep

SQL>

检查全局数据库名称

SQL> connect hr/hr@emrep ;

已连接。

SQL> select * from hr.employees@prod ;

...

SQL>

SQL> connect system/oracle@emrep;

已连接。

SQL> select owner,db_link from all_db_links ;

OWNER DB_LINK

-------------------- --------------------

SYS prod

PUBLIC prod

SQL> connect system/oracle@prod;

已连接。

SQL> select owner,db_link from all_db_links ;

OWNER DB_LINK

-------------------- --------------------

SYS emrep

PUBLIC emrep

SQL>

 

建立主体站点

SQL> connect system/oracle@prod ;

已连接。

SQL>

--建立复制管理用户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;

--注册传播用户并授权

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');

END;

/

--注册接收用户

BEGIN

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

username => 'repadmin',

privilege_type => 'receiver',

list_of_gnames => NULL);

END;

/

--建立物化视图站点复制管理员的代理用户

BEGIN

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

username => 'repadmin',

privilege_type => 'proxy_snapadmin',

list_of_gnames => NULL);

END;

/

GRANT CREATE session TO repadmin;

GRANT SELECT ANY TABLE TO repadmin;

--以复制管理员身份登陆到主站点

SQL> CONNECT repadmin/repadmin@prod ;

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PURGE (

next_date => SYSDATE,

interval => 'SYSDATE + 1/24',

delay_seconds => 0);

END;

/

commit;

设置物化视图站点

SQL> connect system/whoami@emrep ;

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

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;

--建立传播者，并授权

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'mvadmin');

END;

/

--建立刷新者，并授权，这里使用mvadmin 用户刷新物化视图

GRANT CREATE SESSION TO mvadmin;

GRANT ALTER ANY MATERIALIZED VIEW TO mvadmin;

--注册接受者

BEGIN

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

username => 'mvadmin',

privilege_type => 'receiver',

list_of_gnames => NULL);

END;

/

--建立PUBLIC 数据库链

CREATE PUBLIC DATABASE LINK db001 USING 'prod';

SQL> CONNECT mvadmin/mvadmin@emrep;

已连接。

SQL>CREATE DATABASE LINK prod CONNECT TO repadmin IDENTIFIED BY repadmin;

--建立到主站点上复制管理员的数据库链

--以传播者身份登陆物化视图站点

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PURGE (

next_date => SYSDATE,

interval => 'SYSDATE + 1/24',

delay_seconds => 0,

rollback_segment => '');

END;

/

--设置将修改推入到主站点的job

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PUSH (

destination => 'prod',

interval => 'SYSDATE + 1/24',

next_date => SYSDATE,

stop_on_error => FALSE,

delay_seconds => 0,

parallelism => 0);

END;

/

commit;

建立主体组

--以复制管理员身份登陆复制站点

CONNECT repadmin/repadmin@prod ;

--建立名为rep_test 的复制组

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPGROUP (

gname => 'reptest');

END;

/

--将复制对象增加到复制组中

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

gname => 'rep_test',

type => 'TABLE',

oname => 'employees',

sname => 'hr',

use_existing_object => TRUE,

copy_rows => FALSE);

END;

/

--生成复制支持

BEGIN

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

sname => 'hr',

oname => 'employees',

type => 'TABLE',

min_communication => TRUE);

END;

/

--开始复制

BEGIN

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

gname => 'reptest');

END;

/

commit;

建立物化视图

CONNECT hr/hr@prod ;

--建立物化视图日志表，FAST 刷新方式必须要求建立物化视图日志

CREATE MATERIALIZED VIEW LOG ON hr.employees;

--建立复制用户到主站点代理刷新者的数据库链

CONNECT hr/hr@emrep;

CREATE DATABASE LINK prod CONNECT TO repadmin IDENTIFIED BY repadmin;

--建立物化视图组

CONNECT mvadmin/mvadmin@emrep ;

--物化视图组必须和复制站点上的复制组名称相同

BEGIN

DBMS_REPCAT.CREATE_MVIEW_REPGROUP (

gname => 'reptest',

master => 'prod',

propagation_mode => 'ASYNCHRONOUS');

END;

/

--创建刷新组

BEGIN

DBMS_REFRESH.MAKE (

name => 'mvadmin.rep_refresh',

list => '',

next_date => SYSDATE,

interval => 'SYSDATE + 1/24',

implicit_destroy => FALSE,

rollback_seg => '',

push_deferred_rpc => TRUE,

refresh_after_errors => FALSE);

END;

/

SQL> connect mviewadmin/mviewadmin@emrep ;

已连接。

SQL> select *from hr.employees@prod ;

SQL>

------------------------------------------------------------------

--创建物化视图

SQL> CREATE MATERIALIZED VIEW hr.emp_up_view

 REFRESH FAST WITH PRIMARY KEY FOR UPDATE

 AS SELECT * FROM hr.employees@prod ;