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',
