Multimaster replication synchronous doesn't work
626711Mar 10 2008 — edited Mar 10 2008I have a problem with replication. I have Oracle 11g (Enterprise Edition) running in two nodes on Windows XP Pro. I've configured Multimaster replication with synchronous propagation with no errors. Everything looks fine (I've tested links and works perfectly).
My problem is that any change I made in a node is not propagated to another node (I've configured two nodes for replication). It fails in both of the senses (changes in node 1 is not propagated to node 2 and viceversa).
Why doesn't it works? Where can I check what is happening?
Moreover, if I try to stop replication with this code:
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'hr_repg');
END;
I got this error: ORA-23312: not the masterdef according to...
Thanks in advace.
BTW: This is the code I've used to configure and start replication:
/* -------------------------------------------------------- */
/*************** User: SYSTEM ******************/
/*************** Node: oracle1 *****************/
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/*************** User: REPADMIN ****************/
/*************** Node: oracle1 *****************/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/3600',
delay_seconds => 0);
END;
/*************** User: SYSTEM ******************/
/*************** Node: oracle2 *****************/
create user REPADMIN identified by repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/*************** User: REPADMIN ****************/
/*************** Node: oracle2 *****************/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/3600',
delay_seconds => 0);
END;
/*************** User: SYSTEM ******************/
/*************** Node: oracle1 *****************/
CREATE PUBLIC DATABASE LINK orc2.world USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle2)(Port=1521)))(CONNECT_DATA=(SID=orcl2)))';
/*************** User: REPADMIN ****************/
/*************** Node: oracle1 *****************/
CREATE DATABASE LINK orc2.world CONNECT TO repadmin IDENTIFIED BY repadmin;
/*************** User: SYSTEM ******************/
/*************** Node: oracle2 *****************/
CREATE PUBLIC DATABASE LINK orc1.world USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle1)(Port=1521)))(CONNECT_DATA=(SID=orcl1)))';
/*************** User: REPADMIN ****************/
/*************** Node: oracle2 *****************/
CREATE DATABASE LINK orc1.world CONNECT TO repadmin IDENTIFIED BY repadmin;
/*************** User: REPADMIN ****************/
/*************** Node: oracle1 *****************/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'orc2.world',
interval => 'SYSDATE + (1/3600)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1,
delay_seconds => 1);
END;
/*************** User: REPADMIN ****************/
/*************** Node: oracle2 *****************/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'orc1.world',
interval => 'SYSDATE + (1/3600)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1,
delay_seconds => 1);
END;
/*************** User: REPADMIN ****************/
/*************** Node: oracle1 *****************/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'hr_repg');
END;
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'countries',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'departments',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'employees',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'jobs',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'job_history',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'locations',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'regions',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'dept_location_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_manager_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_employee_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'loc_country_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'hr_repg',
master => 'orc2.world',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'SYNCHRONOUS');
END;
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'countries',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'departments',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'employees',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'jobs',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'job_history',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'locations',
type => 'TABLE',
min_communication => TRUE);
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'regions',
type => 'TABLE',
min_communication => TRUE);
END;
/*********************** Start replication ***********************/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'hr_repg');
END;
/* -------------------------------------------------------- */