Oracle Advanced Replication Best Practice
Oracle Advanced Replication Best Practice
一、试验环境：
vmoel5u4机：IP：192.168.92.100
OS：Linux version 2.6.18-164.el5
DB：Oracle 10g Enterprise Edition Release 10.2.0.1.0；
even机：IP: 192.168.92.200
OS：Linux version 2.6.18-164.el5
DB：Oracle 10g Enterprise Edition Release 10.2.0.1.0；
二、试验步骤：
1. 初始化参数设置
vmoel6u4机：db_domain=ORACLE.COM
global_names=true
job_queue_processes=10
open_links=4
even机：db_domain=ORACLE.COM
global_names=true
job_queue_processes=10 # 缺省值
open_links=4 # 缺省值
2. 配置数据库连接
vmoel5u4数据库名： PROD
even数据库名：EMR
两个个数据库域名都是： ORACLE.COM
vmoel5u4数据库sid号：PROD
EVEN数据库sid号：EMR
Listener端口号： 1521
确认两个数据库之间可以互相访问，在tnsnames.ora里设置数据库连接字符串。
vmoel5u4机：
EMR=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=EMR)
(server=dedicated)
)
)
tnsping EMR 测试连通
even机：
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmoel5u4.oracle.com)(port=1521))
)
(CONNECT_DATA =
(service_name = PROD)
(server = dedicated)
)
)
tnsping PROD 测试连通
3. 用 system 用户连接数据库，改数据库全局名称，建公共的数据库链接。
vmoel5u4机：alter database rename global_name to PROD.ORACLE.COM;
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
PROD.ORACLE.COM
even机：alter database rename global_name to EMR.ORACLE.COM;
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
EMR.ORACLE.COM
4, PROD 数据库上
CONNECT SYSTEM/ORACLE@PROD
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;
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;
/
CONNECT repadmin/repadmin@PROD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440',
delay_seconds => 0);
END;
/
CONNECT SYSTEM/ORACLE@PROD
CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/
CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;
4, EMR数据库上
CONNECT SYSTEM/ORACLE@EMR
CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'mviewadmin');
END;
/
GRANT COMMENT ANY TABLE TO mviewadmin;
GRANT LOCK ANY TABLE TO mviewadmin;
CREATE USER propagator IDENTIFIED BY propagator;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'propagator');
END;
/
CREATE USER refresher IDENTIFIED BY refresher;
GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;
CONNECT SYSTEM/ORACLE@EMR
CREATE PUBLIC DATABASE LINK PROD.ORACLE.COM USING 'PROD';
CONNECT mviewadmin/mviewadmin@EMR;
CREATE DATABASE LINK PROD.ORACLE.COM
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
CONNECT propagator/propagator@EMR
CREATE DATABASE LINK PROD.ORACLE.COM
CONNECT TO repadmin IDENTIFIED BY repadmin;
CONNECT mviewadmin/mviewadmin@EMR
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440',
delay_seconds => 0,
rollback_segment => '');
END;
/
CONNECT mviewadmin/mviewadmin@EMR
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'PROD.ORACLE.COM',
interval => 'SYSDATE + 1/1440',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
5， 在PROD上
CONNECT repadmin/repadmin@PROD
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'hr_repg');
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
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;
/
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'hr_repg');
END;
/
CONNECT hr/hr@PROD
CREATE MATERIALIZED VIEW LOG ON hr.employees;
6，在EMR上
CONNECT SYSTEM/ORACLE@EMR
CREATE TABLESPACE demo_mv1
DATAFILE '/u01/app/oracle/oradata/EMR/demo_mv1.dbf' SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE temp_mv1
TEMPFILE '/u01/app/oracle/oradata/EMR/temp_mv1.dbf' SIZE 50M AUTOEXTEND ON;
CREATE USER hr IDENTIFIED BY hr;
ALTER USER hr DEFAULT TABLESPACE demo_mv1
QUOTA UNLIMITED ON demo_mv1;
ALTER USER hr TEMPORARY TABLESPACE temp_mv1;
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
TO hr;
CONNECT hr/hr@EMR
CREATE DATABASE LINK PROD.ORACLE.COM
CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;
CONNECT mviewadmin/mviewadmin@EMR
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'PROD.ORACLE.COM',
propagation_mode => 'ASYNCHRONOUS');
END;
/
BEGIN
DBMS_REFRESH.MAKE (
name => 'mviewadmin.hr_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
/
CREATE MATERIALIZED VIEW hr.employees_mv1
REFRESH FAST WITH PRIMARY KEY FOR UPDATE
AS SELECT * FROM hr.employees@PROD.ORACLE.COM;
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 'employees_mv1',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/
BEGIN
DBMS_REFRESH.ADD (
name => 'mviewadmin.hr_refg',
list => 'hr.employees_mv1',
lax => TRUE);
END;
/
7，在PROD上
SQL> conn hr/hr
Connected.
SQL> update employees set salary=88888 where employee_id=107;
1 row updated.
SQL> commit;
Commit complete.
SQL> select salary from employees where employee_id=107;
SALARY
----------
88888
8，在EMR上
SQL> select salary from employees_mv1 where employee_id=107;
SALARY
----------
88888
从上面可以看出PROD库上的hr.employees表被更新后，EMR库的物化视图employees_mv1 在一分钟后更新过来了。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。