In my previous post I discussed “Master-to-Master” replication configuration.
Master-to-Master Definition Replication implies that the transactions in the first database will be reflected to the second database, BUT NOT vise-versa.
Let’s start Master-to-Master Definition Replication:
I have created two databases:
ORCL-Master Site,
TEST-Master Definition Site
In this case, when there are DML operations on the ORCL site changes ARE reflected on the TEST database, but changes on the TEST site are not reflected on the ORCL database.
————————————ORCL——————————-—
–Connect as sysdba
SQL> connect sys/sys_passwdg@orcl as sysdba
–Create user “REPADMIN”
SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
–Grant necessary privileges to “REPADMIN”
SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');
SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');
SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);
–Create public database link of another database
SQL> CREATE PUBLIC DATABASE LINK test using 'test';
–Connect as REPADMIN
SQL> connect repadmin/repadmin@orcl
–Create private database link in REPADMIN schema
CREATE DATABASE LINK test CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'test';
————————————TEST——————————-—
–Connect as sysdba
SQL> connect sys/sys_passwdg@test as sysdba
–Create user “REPADMIN”
SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
–Grant necessary privileges to “REPADMIN”
SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');
SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');
SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);
–Create public database link of another database
SQL> CREATE PUBLIC DATABASE LINK orcl using 'orcl';
–Connect as REPADMIN
SQL> connect repadmin/repadmin@test
–Create private database link in REPADMIN schema
CREATE DATABASE LINK orcl CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'orcl';
–Create replication group, in which replication objects can be assigned
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('REP2');
–Assign replication object(HR.JOBS table) to the created group
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOBS', 'TABLE', GNAME=>'REP2');
–Assign replication object(Index on HR.JOBS table) to the created group
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOB_ID_PK', 'INDEX', GNAME=>'REP2');
Before declaring master database, I remind you that replicated tables must have primary key or at least unique constraints should be defined on the column(s).
If your table has primary key then continue from “Declare master database” section. Or do the following:
Define alternative key for table that have no primary key:
BEGIN
DBMS_REPCAT.SET_COLUMNS(
sname => 'HR',
oname => 'AAA',
column_list => 'A');
END;
/
Note: JOBS table already has primary key…
If your table has neither primary key nor unique constraint, during generating replication support it will give the following error:
ORA-23308: object HR.AAA does not exist or is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2874
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2849
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2928
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2856
ORA-06512: at "SYS.DBMS_REPCAT", line 766
ORA-06512: at line 2
–Declare master database
SQL> EXEC DBMS_REPCAT.ADD_MASTER_DATABASE (GNAME=>'REP2', MASTER=>'ORCL', USE_EXISTING_OBJECTS =>TRUE, COPY_ROWS=>FALSE,PROPAGATION_MODE=> 'ASYNCHRONOUS');
- ASYNCHRONOUS –With asynchronous replication, changes made at one master site occur at a later time at all other participating master sites.
- SYNCHRONOUS – With synchronous replication, changes made at one master site occur immediately at all other participating master sites.
————————————ORCL——————————-—
–Create job which pushes the deferred transactions to TEST master definition site after 10 seconds
SQL> show user;
User is "repadmin"
BEGIN
dbms_defer_sys.schedule_push(
destination => 'TEST',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/
–Create job which purges the deferred transactions after 3 days(means that deferred transactions were not pushed)
BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/
————————————TEST——————————-—
SQL> show user
User is "repadmin"
—Generate replication support for the replicated object
SQL> exec dbms_repcat.generate_replication_support('HR','JOBS','TABLE');
In this case our repgroup is in QUIESCED mode, in which you cannot perform DML operations on the replicated objects, just SELECT is permitted. To solve this, you should run the following:
SQL> select status from dba_repgroup where gname='REP2';
STATUS
--------
QUIESCED
–Resume master activity
SQL> EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');
If it fails with ORA-23419 error, do this:
SQL> begin
2 dbms_repcat.generate_replication_support('HR','JOBS','TABLE');
3 DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');
4 end;
5 /
—Check the status again
SQL> select status from dba_repgroup where gname='REP2';
STATUS
--------
NORMAL
If you have read my previous post, you should notice that the only difference between previous and recent posts is that: in this post jobs are not created in TEST database(Jobs are created just in ORCL database).
Now let’s test that everything is OK.
–In ORCL database
SQL> show user;
User is "repadmin"
SQL> select * from hr.jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected
–In TEST database
SQL> show user;
User is "repadmin"
SQL> select * from hr.jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected
–In ORCL database
SQL> insert into hr.jobs values('AAA','Aaa',1500,2000);
1 row inserted
SQL> commit;
Commit complete
–In TEST database
SQL> select * from hr.jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected
–Output is the same data, because 10 seconds was not passed yet!
SQL> select * from hr.jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AAA Aaa 1500 2000
AD_PRES President 20000 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
20 rows selected
As you can see the push process was happened after 10seconds, as it is indicated in the pushing job.
Warning!
Replication is not resistant to schema changes. It will fail with ORA-23474 error.
For example, if you change the type of existing columns(Adding columns do not arise any error), during inserting/deleting/updating(DML) there will arise the following error:
ORA-23474: definition of "HR"."AAA" has changed since generation of replication support
To solve this, do the following:
–From TEST database
SQL> exec dbms_repcat.suspend_master_activity(gname => 'REP2');
SQL> exec dbms_repcat.generate_replication_support(sname => 'HR',oname => 'AAA',type => 'TABLE');
SQL> exec dbms_repcat.resume_master_activity(gname => 'REP2');
I mentioned previously that adding columns do not arise an error, but it makes data inconsistency.
Look at this…
I have table HR.AAA on TEST and ORCL databases, HR.AAA is already a repobject and support is also generated for it.
–TEST
SQL> delete from HR.AAA;
SQL> alter table HR.AAA add(B number);
–ORCL
SQL> alter table HR.AAA add(B number);
Do not forget that DDL changes are not replicated, just DML!!!
–TEST
SQL> insert into HR.AAA values(1,1);
SQL> commit;
SQL> select * from HR.AAA;
A B
---------- ----------
1 1
–ORCL
SQL> select * from HR.AAA;
A B
---------- ----------
1
As you can see “B” column was not reflected, to solve this do the following:
–TEST
SQL> exec dbms_repcat.suspend_master_activity(gname => 'REP2');
SQL> exec dbms_repcat.generate_replication_support(sname => 'HR',oname => 'AAA',type => 'TABLE');
SQL> exec dbms_repcat.resume_master_activity(gname => 'REP2');
Now check again:
–TEST
SQL> insert into HR.AAA values(2,1);
SQL> commit;
SQL> select * from hr.aaa;
A B
---------- ----------
1 1
2 1
–ORCL
SQL> select * from HR.AAA;
A B
---------- ----------
1
2 1
Good Luck!
DBA Knowledge Base 