------------------------------------------configure the first master site orc1-------------------------------------- --step1: connect system/123@orc1 --step2:creating an adimn user to do replication create user repadmin identified by repadmin / --step3:Assign special grant for doing the replication related functions begin DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin'); end; / --Give Grant to repadmin to connect to the replication management tool GRANT SELECT ANY DICTIONARY TO REPADMIN / --step4:Register the propagator of orc1 BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR( username => 'repadmin'); End; / --step5:Register the receiver at orc1 BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL); END; / --step6:Scheduling purge at master site connect repadmin/repadmin@orc1 BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0 ); END; / ------------------------------------------configure the second master site orc2-------------------------------------- --step1: connect system/123@orc2 --step2:creating an admin user to do replication create user repadmin identified by repadmin / --step3:Assign special grant for doing the replication related functions begin DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin'); end; / --Assign proper grants to be able to create materialized view logs --GRANT COMMENT ANY TABLE TO repadmin --/ --GRANT LOCK ANY TABLE TO repadmin --/ --Give Grant to repadmin to connect to the replication management tool GRANT SELECT ANY DICTIONARY TO REPADMIN / --step4:Register the propagator of orc2 BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR( username => 'repadmin'); End; / --step5:Register the receiver at orc2 BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL); END; / --step6:Scheduling purge at master site connect repadmin/repadmin@orc2 BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0 ); END; / ------------------------------------------configure the third master site orc3-------------------------------------- --step1: connect system/123@orc3 --step2:creating an admin user to do replication create user repadmin identified by repadmin / --step3:Assign special grant for doing the replication related functions begin DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin'); end; / --Assign proper grants to be able to create materialized view logs --GRANT COMMENT ANY TABLE TO repadmin --/ --GRANT LOCK ANY TABLE TO repadmin --/ --Give Grant to repadmin to connect to the replication management tool GRANT SELECT ANY DICTIONARY TO REPADMIN / --step4:Register the propagator of orc3 BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR( username => 'repadmin'); End; / --step5:Register the receiver at orc3 BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL); END; / --step6:Scheduling purge at master site connect repadmin/repadmin@orc3 BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0 ); END; / --Creating public link to orc2 and orc3 from orc1 Connect system/123@orc1 CREATE PUBLIC DATABASE LINK orc2 USING 'orc2' / CREATE PUBLIC DATABASE LINK orc3 USING 'orc3' / --Creating a db link between the other replication admins connect repadmin/repadmin@orc1 CREATE DATABASE LINK orc2 CONNECT TO repadmin IDENTIFIED BY repadmin / CREATE DATABASE LINK orc3 CONNECT TO repadmin IDENTIFIED BY repadmin / --Creating public link to orc1 and orc3 from orc2 Connect system/123@orc2 CREATE PUBLIC DATABASE LINK orc1 USING 'orc1' / CREATE PUBLIC DATABASE LINK orc3 USING 'orc3' / --Creating a db link between the other replication admins connect repadmin/repadmin@orc2 CREATE DATABASE LINK orc1 CONNECT TO repadmin IDENTIFIED BY repadmin / CREATE DATABASE LINK orc3 CONNECT TO repadmin IDENTIFIED BY repadmin / --Creating public link to orc1 and orc2 from orc3 Connect system/123@orc3 CREATE PUBLIC DATABASE LINK orc1 USING 'orc1' / CREATE PUBLIC DATABASE LINK orc2 USING 'orc2' / --Creating a db link between the other replication admins connect repadmin/repadmin@orc3 CREATE DATABASE LINK orc1 CONNECT TO repadmin IDENTIFIED BY repadmin / CREATE DATABASE LINK orc2 CONNECT TO repadmin IDENTIFIED BY repadmin / connect repadmin/repadmin@orc1 --Executing SCHEDULE_PUSH for each dblink BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc2', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc3', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / --5/24*60*60 = 1/17280 connect repadmin/repadmin@orc2 --Executing SCHEDULE_PUSH for each dblink BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc1', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc3', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / connect repadmin/repadmin@orc3 --Executing SCHEDULE_PUSH for each dblink BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc1', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH( destination => 'orc2', interval => 'SYSDATE + (1/17280)', next_date => SYSDATE, parallelism => 1, stop_on_error => FALSE, delay_seconds => 0); END; / --------------------------------------Creating the Schema----------------------------- ---------Creating schema for orc1------------- CONNECT system/123@orc1 CREATE USER hr IDENTIFIED BY m123 / grant connect to hr / grant resource to hr / connect hr/m123@orc1 create table test1(a number not null, b varchar2(10) , constraint test1_pk primary key(a) ) / insert into test1 values(1,'maryam') / insert into test1 values(2,'ali') / insert into test1 values(3,'mike') / commit / create table test2(c number not null, a number not null, e varchar2(10) , constraint test2_pk primary key(c,a), constraint test2_fk foreign key (c) references test1(a) ) / insert into test2 values(1,1,'7807106194') / insert into test2 values(2,1,'7807105714') / insert into test2 values(1,2,'7807103564') / commit / create table Locations(Loc_id number(4) not null, Loc_number number(5) not null, Loc_desc varchar2(50) , Loc_Add varchar2(50) , constraint location_pk primary key(loc_id)) / insert into Locations values(1,1,'Calgary','115st') / insert into Locations values(2,2,'Edmonton','117st') / insert into Locations values(3,3,'BC','119st') / commit / create table departments(loc_id number(3) not null, dep_id number(4) not null, dep_dsc varchar2(50), dep_emp_count number(3), dep_device_count number(3), constraint departments_pk primary key(loc_id,dep_id), constraint departments_fk foreign key (loc_id) references locations(loc_id)) / insert into departments values(1,1,'computer',50,100) / insert into departments values(1,2,'marketing',60,120) / insert into departments values(2,1,'computer',20,101) / insert into departments values(2,2,'accounting',20,101) / commit / ---------Creating schema for orc2------------- CONNECT system/123@orc2 CREATE USER hr IDENTIFIED BY m123 / grant connect to hr / grant resource to hr / connect hr/m123@orc2 create table test1(a number not null, b varchar2(10) , constraint test1_pk primary key(a) ) / insert into test1 values(1,'maryam') / insert into test1 values(2,'ali') / insert into test1 values(3,'mike') / commit / create table test2(c number not null, a number not null, e varchar2(10) , constraint test2_pk primary key(c,a), constraint test2_fk foreign key (c) references test1(a) ) / insert into test2 values(1,1,'7807106194') / insert into test2 values(2,1,'7807105714') / insert into test2 values(1,2,'7807103564') / commit / create table Locations(Loc_id number(4) not null, Loc_number number(5) not null, Loc_desc varchar2(50) , Loc_Add varchar2(50) , constraint location_pk primary key(loc_id)) / insert into Locations values(1,1,'Calgary','115st') / insert into Locations values(2,2,'Edmonton','117st') / insert into Locations values(3,3,'BC','119st') / commit / create table departments(loc_id number(3) not null, dep_id number(4) not null, dep_dsc varchar2(50), dep_emp_count number(3), dep_device_count number(3), constraint departments_pk primary key(loc_id,dep_id), constraint departments_fk foreign key (loc_id) references locations(loc_id)) / insert into departments values(1,1,'computer',50,100) / insert into departments values(1,2,'marketing',60,120) / insert into departments values(2,1,'computer',20,101) / insert into departments values(2,2,'accounting',20,101) / commit / ---------Creating schema for orc3------------- CONNECT system/123@orc3 CREATE USER hr IDENTIFIED BY m123 / grant connect to hr / grant resource to hr / connect hr/m123@orc3 create table test1(a number not null, b varchar2(10) , constraint test1_pk primary key(a) ) / insert into test1 values(1,'maryam') / insert into test1 values(2,'ali') / insert into test1 values(3,'mike') / commit / create table test2(c number not null, a number not null, e varchar2(10) , constraint test2_pk primary key(c,a), constraint test2_fk foreign key (c) references test1(a) ) / insert into test2 values(1,1,'7807106194') / insert into test2 values(2,1,'7807105714') / insert into test2 values(1,2,'7807103564') / commit / create table Locations(Loc_id number(4) not null, Loc_number number(5) not null, Loc_desc varchar2(50) , Loc_Add varchar2(50) , constraint location_pk primary key(loc_id)) / insert into Locations values(1,1,'Calgary','115st') / insert into Locations values(2,2,'Edmonton','117st') / insert into Locations values(3,3,'BC','119st') / commit / create table departments(loc_id number(3) not null, dep_id number(4) not null, dep_dsc varchar2(50), dep_emp_count number(3), dep_device_count number(3), constraint departments_pk primary key(loc_id,dep_id), constraint departments_fk foreign key (loc_id) references locations(loc_id)) / insert into departments values(1,1,'computer',50,100) / insert into departments values(1,2,'marketing',60,120) / insert into departments values(2,1,'computer',20,101) / insert into departments values(2,2,'accounting',20,101) / commit / -----------------Finishing the creation of schema-------------------------------- --step1:Connect to repadmin first CONNECT repadmin/repadmin@orc1 --step2:Create the master group named hr_test_repg BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP( gname => 'hr_test_repg'); END; / --step3:add objects to master group --first add tables BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT( gname => 'hr_test_repg', type => 'TABLE', oname => 'test1', sname =>'hr', use_existing_object => TRUE, copy_rows => FALSE); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT( gname => 'hr_test_repg', type => 'TABLE', oname => 'test2', sname =>'hr', use_existing_object => TRUE, copy_rows => FALSE); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT( gname => 'hr_test_repg', type => 'TABLE', oname => 'locations', sname =>'hr', use_existing_object => TRUE, copy_rows => FALSE); END; / BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT( gname => 'hr_test_repg', type => 'TABLE', oname => 'departments', sname =>'hr', use_existing_object => TRUE, copy_rows => FALSE); END; / --orc1 is a master defenition site -- add additional master sites to define other sites that will participate in --the replication environment orc2 & orc3 --it assumes that the schema is already created in all master sites + data BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE( gname => 'hr_test_repg', master => 'orc2', use_existing_objects => TRUE, copy_rows => FALSe, propagation_mode => 'ASYNCHRONOUS'); END; / --Adding orc3 Mastersite BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE( gname => 'hr_test_repg', master => 'orc3', use_existing_objects => TRUE, copy_rows => FALSe, propagation_mode => 'ASYNCHRONOUS'); END; / --Generate replication support BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT( sname => 'hr', oname => 'test1', type => 'TABLE', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT( sname => 'hr', oname => 'test2', 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 => 'departments', type => 'TABLE', min_communication => TRUE); END; / --if the result is empty then continue SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME='HR_TEST_REPG' / BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'hr_test_repg'); END; /