Oracle Advanced Replication(Master-to-Master)
June 30, 2011 Leave a comment
Oracle Advanced Replication is a technique, which is used to maintain two objects the same. This technique is built-in to the Oracle.
If you want to use this technique, it will be better to consider its limitations:
Oracle Advanced Replication
- has error prone administration.
- has problems with high transaction rates more then 50 – 100 transactions per second, depending on platform or machine .
- not resistant to schema changes.
- has serious performance overhead on both sides. .
Another well-known issue is that sequences could not be replicated.
The following objects can be replicated:
- Tables
- Indexes
- Views and Object Views
- Packages and Package Bodies
- Procedures and Functions
- User-Defined Types and Type Bodies
- Triggers
- Synonyms
- Indextypes
- User-Defined Operators
Master-to-Master Replication means that the transactions in each database will be reflected in another database.
There also exists “Master-to-Master Definition” Replication, which means that the transactions in the first database will be reflected to the second database, BUT NOT vise-versa.
Let’s start Master-to-Master Replication:
I have created two databases:
ORCL-Master Site 1,
TEST-Master Site 2
————————————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;
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);
SQL> CREATE PUBLIC DATABASE LINK test using 'test';
SQL> connect repadmin/repadmin@orcl
CREATE DATABASE LINK test CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'test';
SQL> connect sys/sys_passwdg@test as sysdba
–Create user “REPADMIN”
SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
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);
SQL> CREATE PUBLIC DATABASE LINK orcl using 'orcl';
SQL> connect repadmin/repadmin@test
CREATE DATABASE LINK orcl CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'orcl';
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('REP2');
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOBS', 'TABLE', GNAME=>'REP2');
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOB_ID_PK', 'INDEX', GNAME=>'REP2');
Define alternative key for table that have no primary key:
BEGIN DBMS_REPCAT.SET_COLUMNS( sname => 'HR', oname => 'AAA', column_list => 'A'); END; /
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
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.
“When you use synchronous replication, an update of a table results in the immediate replication of the update at all participating master sites. In fact, each transaction includes all master sites. Therefore, if one master site cannot process a transaction for any reason, then the transaction is rolled back at all master sites.
Although you avoid the possibility of conflicts when you use synchronous replication, it requires a very stable environment to operate smoothly. If communication to one master site is not possible because of a network problem, for example, then users can still query replicated tables, but no transactions can be completed until communication is reestablished. Also, it is possible to configure asynchronous replication so that it simulates synchronous replication.”
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');
—Check the status again
SQL> select status from dba_repgroup where gname='REP2'; STATUS -------- NORMAL
Now DML operations can be performed.
BEGIN dbms_defer_sys.schedule_push( destination => 'ORCL', 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; /
That’s all…
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.
The same will happen if you perform DML on the TEST database first…You can check it!
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
DBA Knowledge Base 