8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Replication (Advanced)
Advanced replication allows the simultaneous transfer of data between two or more Master Sites. Each Master Site is fully functional and doesn't depend on pulling updates from a central site. In this example replication is setup between two Master Sites, TSH1 and TSH2, with the TSH1 instance as the Master Definition site, making it responsible for the coordination of the replication process.
- Assumptions
- Setup Master Sites
- Setup Master Definition Site
- Start/Stop Replication
- Remove Replication Support
- Issues
Assumptions
- Advanced Replication is installed on all servers. If this is not the case, connect as SYS and run the ORACLE_HOME/rdbms/admin/catrep.sql script.
- The init.ora parameters job_queue_processes, job_queue_interval and global_name parameters have been set. The job_queue_processes parameter should be set to at least 1 with the job_queue_interval being, at most, the same as your smallest job interval.
- The SCOTT user is present as the example replicates the data in the EMP table.
Setup Master Sites
The following setup is required for each replication Master Site.
CONNECT sys@tsh1 -- Create public DB links to all replication sites CREATE PUBLIC DATABASE LINK TSH2.WORLD USING 'TSH2.WORLD'; -- Create replication administrator / propagator / receiver CREATE USER repadmin IDENTIFIED BY repadmin DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; -- Grant privs to the propagator, to propagate changes to remote sites EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN'); -- Grant privs to the receiver to apply deferred transactions GRANT EXECUTE ANY PROCEDURE TO repadmin; -- Authorise the administrator to administer replication groups and schemas EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN'); EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN'); -- Authorise the administrator to lock and comment tables GRANT LOCK ANY TABLE TO repadmin; GRANT COMMENT ANY TABLE TO repadmin; -- Connect to the replication administrator CONNECT repadmin/repadmin@tsh1 -- Create private db links for all repadmin users CREATE DATABASE LINK TSH2.WORLD CONNECT TO repadmin IDENTIFIED BY repadmin USING 'TSH2.WORLD'; -- Schedule job to push transactions to all master sites with appropriate intervals EXECUTE Dbms_Defer_Sys.Schedule_Push( - destination => 'TSH2.WORLD', - interval => 'sysdate+1/24/60', - next_date => sysdate+1/24/60, - stop_on_error => FALSE, - delay_seconds => 0, - parallelism => 1); -- Schedule job to delete successfully replicated transactions EXECUTE Dbms_Defer_Sys.Schedule_Purge( - next_date => sysdate+1/24, - interval => 'sysdate+1/24');
Setup Master Definition Site
On the Master Definition Site define replication groups and assign destination sites to them. Each object to be replicated is defined and replication support is generated.
CONNECT repadmin/repadmin@tsh1 -- Create replication group for MASTERDEF site EXECUTE Dbms_Repcat.Create_Master_Repgroup('MYREPGRP'); -- Add master desination sites EXECUTE Dbms_Repcat.Add_Master_Database('MYREPGRP', 'TSH2.WORLD'); -- Wait until TSH2.WORLD appears in the DBA_REPSITES view SELECT * FROM dba_repsites WHERE gname = 'MYREPGRP'; -- Register objects within the group EXECUTE Dbms_Repcat.Create_Master_Repobject('SCOTT', - 'EMP', 'TABLE', gname=>'MYREPGRP'); -- Register columns for confilct resolution EXECUTE Dbms_Repcat.Make_Column_Group( - sname => 'SCOTT', - oname => 'EMP', - column_group => 'EMP_COLGRP', - list_of_column_names => 'EMPNO'); -- Define conflict resolution for the registered columns EXECUTE Dbms_Repcat.Add_Update_Resolution( - sname => 'SCOTT', - oname => 'EMP', - column_group => 'EMP_COLGRP', - sequence_no => 1, - method => 'OVERWRITE', - parameter_column_name => 'EMPNO'); -- Generate replication support for objects within the group EXECUTE Dbms_Repcat.Generate_Replication_Support('SCOTT', 'EMP', 'table'); -- Wait until generation is complete (DBA_REPCATALOG is empty) SELECT * FROM dba_repcatlog WHERE gname = 'MYREPGRP';
Start/Stop Replication
Once replication support has been generated for all objects relevant objects replication can be started or stopped as follows.
-- Start Replication EXECUTE Dbms_Repcat.Resume_Master_Activity(gname => 'MYREPGRP'); -- Stop Replication EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname => 'MYREPGRP');
Remove Replication Support
To remove replication perform the following on the Master Definition Site.
CONNECT repadmin/repadmin@tsh1 -- Stop replication EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname=>'MYREPGRP'); -- Delete replication groups EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE'); EXECUTE Dbms_Repcat.Remove_Master_Databases('MYREPGRP', 'TSH2.WORLD');
Next do the following on all Master Sites.
CONNECT repadmin/repadmin@tsh1 -- Remove private databse links to other master databases EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYREPGRP'); DROP DATABASE LINK TSH2.WORLD; -- Remove any leftover jobs (see DBA_JOBS for job numbers) EXECUTE Dbms_Job.Remove(62); EXECUTE Dbms_Job.Remove(63); CONNECT sys@tsh1 -- Remove the REPADMIN user EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN'); EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN'); DROP USER repadmin CASCADE; -- Drop public database links to other master databases DROP PUBLIC DATABASE LINK TSH2.WORLD;
Issues
Conflicts can occur when sequences are used to generate primary keys on each site. These can be prevented in several ways:
- Add a SITE column to the current primary key.
- Concatinate a site identifier to the sequence number.
- Set the starting and maximum values of the sequence such that each site has a specific zone.
Hope this helps. Regards Tim...