Install Oracle Multimaster Replication

Master Site 1(Master Definition Site)

DB Name=CRODB1
Schema=HR
System password=manager


Master Site 2(Master Definition Site)

DB Name=CRODB2
Schema=HR
System password=manager

Pre-installation Steps:

1-Check that the 2 databases are running the same Oracle version with the same patch installed.
2-Drop HR Schema in CRODB2 database and recreate it empty.

Step1:Create replication users at CRODB1

Set instance CRODB1

CONNECT system/manager

CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE REP_TS;
ALTER USER repadmin TEMPORARY TABLESPACE temp;

GRANT connect, resource TO repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin');
GRANT comment any table TO repadmin;
GRANT lock any table TO repadmin;
Grant select any dictionary to repadmin;
CREATE USER repprop IDENTIFIED BY repprop1;
ALTER USER repprop DEFAULT TABLESPACE REP_TS;
ALTER USER repprop TEMPORARY TABLESPACE temp;

EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repprop');

CREATE USER reprecv IDENTIFIED BY reprecv1;
ALTER USER reprecv DEFAULT TABLESPACE REP_TS ;
ALTER USER reprecv TEMPORARY TABLESPACE temp;

BEGIN
dbms_repcat_admin.register_user_repgroup(
username => 'reprecv',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CREATE USER repdba IDENTIFIED BY repdba1;
ALTER USER repdba DEFAULT TABLESPACE REP_TS;
ALTER USER repdba TEMPORARY TABLESPACE temp;
GRANT connect, resource TO repdba;


Step2:Create replication users at CRODB2


Set instance CRODB2

CONNECT system/manager

CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE REP_TS;
ALTER USER repadmin TEMPORARY TABLESPACE temp;

GRANT connect, resource TO repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin');
GRANT comment any table TO repadmin;
GRANT lock any table TO repadmin;
Grant select any dictionary to repadmin;
CREATE USER repprop IDENTIFIED BY repprop2;
ALTER USER repprop DEFAULT TABLESPACE REP_TS;
ALTER USER repprop TEMPORARY TABLESPACE temp;

EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repprop');

CREATE USER reprecv IDENTIFIED BY reprecv2;
ALTER USER reprecv DEFAULT TABLESPACE REP_TS;
ALTER USER reprecv TEMPORARY TABLESPACE temp;

BEGIN
dbms_repcat_admin.register_user_repgroup(
username => 'reprecv',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CREATE USER repdba IDENTIFIED BY repdba2;
ALTER USER repdba DEFAULT TABLESPACE REP_TS;
ALTER USER repdba TEMPORARY TABLESPACE temp;
GRANT connect, resource TO repdba;


Step3:Create database links at CRODB1


set instance CRODB1
create public database link "CRODB2" using 'CRODB2';

CONNECT repadmin/repadmin
CREATE DATABASE LINK "CRODB2" CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT repprop/repprop1
CREATE DATABASE LINK "CRODB2" CONNECT TO reprecv IDENTIFIED BY reprecv2;

CONNECT repadmin/repadmin
BEGIN
dbms_defer_sys.schedule_push(
destination => 'CRODB2',
interval => '/*10:sec*/ sysdate + 10/(60*60*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/

BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*10:sec*/ sysdate + 10/(60*60*24)',
delay_seconds => 0,
rollback_segment => '');
END;
/



Step4:Create database links at CRODB2


set instance CRODB2

CREATE PUBLIC DATABASE LINK "CRODB1" USING 'CRODB1';

CONNECT repadmin/repadmin
CREATE DATABASE LINK "CRODB1" CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT repprop/repprop2
CREATE DATABASE LINK "CRODB1" CONNECT TO reprecv IDENTIFIED BY reprecv1;

CONNECT repadmin/repadmin
BEGIN
dbms_defer_sys.schedule_push(
destination => 'CRODB1',
interval => '/*10:sec*/ sysdate + 10/(60*60*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/

BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*10:sec*/ sysdate + 10/(60*60*24)',
delay_seconds => 0,
rollback_segment => '');
END;
/

Step5:Create Master Replication Group

set instance CRODB1

CONNECT repadmin/repadmin

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP(
gname => '"hr_repg"',
qualifier => '',
group_comment => '');
END;
/



Step6:Create HR Schema Tables Repobject

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'countries',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'departments',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'employees',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'jobs',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'job_history',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'locations',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'regions',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'dept_location_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_manager_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_employee_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'loc_country_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/


Step7:Add Master database

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'hr_repg',
master => 'CRODB2',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
/

Step8:Conflict Resolution
If conflicts are possible, then configure conflict resolution methods.

You can use SEQUENCES to create primary key values for the tables but you can create a sequence in CRODB1 database starts with a value like 20 and increment by 2
create sequence dept_seq start with 20 increment by 2;
so the values will be 20,22,24,………

and the CRODB2 you can create the same sequence but start with 21 and increment by 2 so the values will be 21,23,25,………
create sequence dept_seq start with 2 increment by 2;

so this way you will never have a conflict resolution in your replication environment.


Step9:Generate HR Replication Support

conn repadmin/repadmin

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'countries',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'departments',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'employees',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'jobs',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'job_history',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'locations',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'regions',
type => 'TABLE',
min_communication => TRUE);
END;
/

Step10:Resume Activity in Master Replication Group

Conn repadmin/repadmin

select count(*) from DBA_REPCATLOG;

if no rows are returned then issue the command:

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"hr_repg"');
END;
/