Oracle Advanced Replication Best Practice

Oracle Advanced Replication Best Practice 1. test environment: 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; 2. Test Procedure: 1. set the initialization parameter to vmoel6u4: db_domain = ORACLE. COM global_names = true job_queue_processes = 10 open_links = 4even HOST: db_domain = ORACLE. COM gl Obal_names = true job_queue_processes = 10 # default value open_links = 4 # default value 2. configure the database connection vmoel5u4 Database Name: PRODeven Database Name: EMR two database domain names are: ORACLE. COMvmoel5u4 database sid: PRODEVEN database sid: EMRListener port number: 1521 make sure that the two databases can access each other in tnsnames. set the database connection string in ora. Vmoel5u4: EMR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp) (HOST = even.oracle.com) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = EMR) (server = dedicated) tnsping EMR test connecting even machine: PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vmoel5u4.oracle.com) (port = 1521) (CONNECT_DATA = (service_name = PROD) (server = dedicated) tnsping PROD test connectivity 3. use the system user to connect to the database, change the global name of the database, and create a public A total of database links. Vmoel5u4: alter database rename global_name to PROD. ORACLE. COM; SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------PROD.ORACLE.COM even machine: alter database rename global_name to EMR. ORACLE. COM; SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------EMR.ORACLE.COM 4, PR Connect system/ORACLE @ prodcreate user repadmin identified by repadmin on the OD database; 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 => 'referadmin'); END;/BEGIN tables (username => 'referadmin', privilege_type => 'Referer', l Ist_of_gnames => NULL); END;/CONNECT repadmin/repadmin @ PRODBEGIN values (next_date => SYSDATE, interval => 'sysdate + 1/1440 ', delay_seconds => 0); END; /connect system/ORACLE @ prodcreate user proxy_mviewadmin identified by proxy_mviewadmin; BEGIN metadata (username => 'proxy _ mviewadmin', privilege_type => 'proxy _ snapadmin', list_of_gnames => NUL L); END;/create user proxy_refresher identified by proxy_refresher; grant create session to proxy_refresher; grant select any table to proxy_refresher; 4, connect system/ORACLE @ emrcreate user mviewadmin identified by mviewadmin on the EMR database; 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 p Ropagator identified by propagator; BEGIN topology (username => 'pagator'); END;/create user refresher identified by refresher; grant create session to refresher; grant alter any materialized view to refresher; connect system/ORACLE @ emrcreate public database link prod. ORACLE. com using 'prod'; CONNECT mviewadmin/mviewadmin @ EMR; create database link prod. ORACLE. COM CONNECT T O proxy_mviewadmin identified by proxy_mviewadmin; CONNECT propagator/propagator @ emrcreate database link prod. ORACLE. com connect to repadmin identified by repadmin; CONNECT mviewadmin/mviewadmin @ EMRBEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE (next_date => SYSDATE, interval => 'sysdate + 1/1440 ', delay_seconds => 0, rollback_segment => ''); END;/CONNECT mviewadmin/mviewadmin @ EMRBEGIN DBMS_DEFER_SYS.SCHEDU LE_PUSH (destination => 'prod. ORACLE. COM ', interval => 'sysdate + 1/1440', next_date => SYSDATE, stop_on_error => FALSE, delay_seconds => 0, parallelism => 0); END;/5, CONNECT repadmin/repadmin @ PRODBEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP (gname => 'hr _ repg '); END;/BEGIN groups (gname => 'hr _ repg ', type => 'table', oname => 'ployees', sname => 'hr', use_exis Ting_object => TRUE, copy_rows => FALSE); END;/BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (sname => 'hr', oname => 'ployees', 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 @ prodcreate materialized view log on hr. employees; 6, connect system/O on EMR RACLE @ emrcreate tablespace demo_mv1 DATAFILE '/u01/app/oracle/oradata/EMR/demo_mv1.dbf' SIZE 100 m autoextend on extent management local autoallocate; create temporary tablespace temp_mv1 TEMPFILE '/u01/app/oracle/oradata/EMR/temp_mv1.dbf' SIZE 50 m 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 te Mp_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 @ emrcreate database link prod. ORACLE. com connect to proxy_refresher identified by proxy_refresher; CONNECT mviewadmin/mviewadmin @ EMRBEGIN DBMS_REPCAT.CREATE_MVIEW_REPG ROUP (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 + 100', implicit_destroy => FALSE, rollback_seg =>'', push_deferred_rpc => TRUE, refresh_after_errors => FALSE); END;/create materialized view hr. employees_mv1 REFRESH FAST WITH PRIMARY K Ey for update as select * FROM hr.employees@PROD.ORACLE.COM; BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (gname => 'hr _ repg ', sname => 'hr', oname => 'ployees _ mv1 ', type => 'snapshot', min_communication => TRUE); END;/BEGIN DBMS_REFRESH.ADD (name => 'mviewadmin. hr_refg ', list => 'hr. employees_mv1 ', lax => TRUE); END;/7, on prod SQL> conn hr/hrConnected. SQL> update employees set salary = 88888 where emp Loyee_id = 107; 1 row updated. SQL> commit; Commit complete. SQL> select salary from employees where employee_id = 107; SALARY ---------- 88888 8, on emr SQL> select salary from employees_mv1 where employee_id = 107; SALARY ---------- 88888 From the above we can see the hr on the PROD library. after the employees table is updated, the materialized view of the EMR database employees_mv1 is updated one minute later.