Oracle Advanced Replication Best Practice(二)
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 PRI