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 在一分鐘後更新過來了。