Step -by-Step Replication (MASTER_MASTER) Asynchromous Replication

Why I am using Asynchronous?

In our scenario, The availability of network is uncertain in a given time. Synchronous uses two side commit. If one site is not available at a time, then the update is not possible. It will raise error.

But in Asynchronous,
Using change queues and materialized queues snapshot logs to store changes and therefore even if single side is not available during some period of time the remained side may be used. Moreover, after detached side will be available again, data will be synchronized [last statement depends on purge period, the purge period constraint defends active master database via to be filled by not propagated changes.]

The first thing to know prior to start with advanced replication is that you should have primary key (or at least the alternative) for all tables you would like to replicate. For instance, you have two instances test1 and test2. On both instances you have created user REPTEST00 with default tablespace pool_data and temporary tablespace temp.


The following statement describes database names and tnsnames aliases of the involved instances:
TEST1 = Global Database name of the Master Definition Site
TEST2 = Global Database name of secondary Master Site

test1 = Net alias to the Master Definition Site
test2 = Net alias to the secondary Master Site
You may check your global database name by running:

select * from global_name
Net alias is an alias from $ORACLE_HOME/network/admin/tnsnames.ora

*Note:
Even all oracle documentation that describes advanced replication features, explicitly suggesting that you are using oracle domains eg.test1.world. However it is not really required. So if you have well designed set of database names, you do not need this feature.
During this session we will avoid to use oracle domains and our Net aliases will be named just test1 and test2 respectively.
In order to work with the oracle advanced replication we should add GLOBAL_NAMES = true to our pfile (or spfile).

Test1 creates user repadmin in both instances with appropriate permissions


CONNECT system/manager@test1
CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE POOL_DATA;
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;
EXECUTE dbms_defer_sys.register_propagator('repadmin');
GRANT execute any procedure TO repadmin;
CREATE PUBLIC DATABASE LINK TEST2 USING 'test2';
CONNECT repadmin/repadmin@test1
CREATE DATABASE LINK TEST2 CONNECT TO repadmin IDENTIFIED BY repadmin;

Test2.
CONNECT system/manager@test2
CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE POOL_DATA;
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;
EXECUTE dbms_defer_sys.register_propagator('repadmin');
GRANT execute any procedure TO repadmin;
CREATE PUBLIC DATABASE LINK TEST1 USING 'test1';
CONNECT repadmin/repadmin@test2
CREATE DATABASE LINK TEST1 CONNECT TO repadmin IDENTIFIED BY repadmin;

Test1 uses ASYNCHRONOUS replication. It tries pushing changes every 10 second, purge changes that not succeed to be propagated during 3 days

BEGIN
dbms_defer_sys.schedule_push(
destination => 'test2',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/
BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/
Test2 uses ASYNCHRONOUS replication.

It tries pushing changes each 10 second, purge changes that not succeed to be propagated during 3 days

BEGIN
dbms_defer_sys.schedule_push(
destination => 'test1',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/
BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/

Create master replication group on test1 which is primary master:

CONNECT repadmin/repadmin@test1
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP(
gname => '"REPTEST"',
qualifier => '',
group_comment => '');
END;
/

Add secondary master and suspend master activity. If this replication needed to be SYNCHRONOUS, then just change propagation_mode from ASYNCHRONOUS to SYNCHRONOUS but be aware that SYNCHRONOUS configuration may not be used for High Availability
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE(
gname => '"REPTEST"',
master => 'TEST2',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Let's assume you have tables XXXX with primary key XXXX_PK YYYY without primary key but with unique constraint YYYY_UX contains two columns YYYY_UX_COL1, YYYY_UX_COL2 and additional index YYYY_IX. Also assume there is a trigger on table XXXX namely XXXX_TRIGGER that will be replicated as well.
On test1 which is primary master, create master replication objects:
CONNECT repadmin/repadmin@test1
Now creating master replication object for all tables
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TABLE',
oname => '"XXXX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TABLE',
oname => '"YYYY"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
Create master replication object for trigger:
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TRIGGER',
oname => '"XXXX_TRIGGER"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
Create master replication object for all indexes:
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"YYYY_IX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"YYYY_UX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"XXXX_PK"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/


Define alternative key for table that have no primary key:
BEGIN
DBMS_REPCAT.SET_COLUMNS(
sname => '"REPTEST00"',
oname => '"YYYY"',
column_list => '"YYYY_UX_COL1, YYYY_UX_COL2"');
END;
/

Generate replication support for all the tables to be replicated:
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"XXXX"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
Now wait until all objects from primary master will be copied to secondary master. Afterwards replication can be started by issuing running following statement:
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(gname => '"REPTEST"');
END;
/

Warning!
Oracle Advanced replication is not resistant to schema changes sincevery likely that schema change replication will fail with ORA-23474 afterwards.
This is one of the worst possible cases. Replication may not continue to work until you drop all non-primary masters and recreate all replication support from the scratch.
23474, 0000, "definition of \"%s\".\"%s\" has changed since generation of replication support"
*Cause: The current columns in the specified table and their column types do not match the columns and column types when replication support was last generated.
*Action: Regenerate replication support for the affected table.
All flavors that include the specified table should be checked
for validity. Types for any UDT columns should also be checked
for validity.
You can try to resolving ORA-23474 by regenerating replication support for invalidated objects on primary master:


CONNECT repadmin/repadmin@test1
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Next is regenerate replication support for all affected objects, for instance:
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
Then you may resume replication by issuing the following statement:
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/
The right way to change schema would be using DBMS_REPCAT.EXECUTE_DDL. Example:
BEGIN
DBMS_REPCAT.EXECUTE_DDL (
gname => 'REPTEST',
master_list => 'test1, test2',
DDL_TEXT => 'alter table REPTEST00.XXXX add (add_test
number(8,0))');
END;
/
If one of the master databases to be removed:
BEGIN
DBMS_REPCAT.REMOVE_MASTER_DATABASES (
gname => '"REPTEST"',
master_list => 'test2');
END;
/
If one of master objects to be removed:
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/
BEGIN
DBMS_REPCAT.DROP_MASTER_REPOBJECT (
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
drop_objects => FALSE);
END;
/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Monitoring and troubleshooting of Oracle advanced replication.
Oracle advanced replication uses tables with names such as def$_xxxx in the schema “system”. User schema “sys” holds several useful views on those tables named defxxxx respectively.
For example, if one of the alternative keys that you set was not unique, then you may receive ORA-01422 error.
All errors received during advanced replication activity and not yet purged can be identified from DEFERROR view.
You may use the following script:
set linesize 132
col DESTINATION for a10
col ERROR_MSG for a45
col START_TIME for a21
col DEFERRED_TRAN_ID for a15
col CALLNO for 9999
SELECT deferred_tran_id, callno, SUBSTR (destination, 1, 10) destination,
TO_CHAR (start_time, 'HH24:MI:SS DD/MM/YYYY') start_time,
SUBSTR (error_msg, 1, 45) error_msg
FROM deferror;
Example of inappropriate surrogate key usage result:
DEFERRED_TRAN_I CALLNO DESTINATIO START_TIME ERROR_MSG
--------------- ------ ---------- --------------------- ---------------------------------------------
9.3.865 0 TEST1 08:10:09 14/03/2004 ORA-01422: exact fetch returns more than requ
2.27.874 0 TEST1 08:10:10 14/03/2004 ORA-01422: exact fetch returns more than requ
9.33.864 0 TEST1 08:10:16 14/03/2004 ORA-01422: exact fetch returns more than requ
9.39.1073 0 TEST1 22:02:36 18/03/2004 ORA-01422: exact fetch returns more than requ
1.15.1107 0 TEST1 22:02:48 18/03/2004 ORA-01422: exact fetch returns more than requ
5.36.1089 0 TEST1 22:02:51 18/03/2004 ORA-01422: exact fetch returns more than requ
9.30.1074 0 TEST1 22:10:58 18/03/2004 ORA-01422: exact fetch returns more than requ

The other useful view of this kind is DEFTRAN.
Via this view you may see the currently queued transaction. Please note, transaction that are not propagated due to the error such as in the previous example will remain in this queue forever or until purged.
select DEFERRED_TRAN_ID, DELIVERY_ORDER, DESTINATION_LIST, START_TIME from DEFTRAN;
DEFERRED_TRAN_I DELIVERY_ORDER D START_TIME
--------------- -------------- - ---------------------
9.3.865 4183230 D 14-MAR-04
2.27.874 4183237 D 14-MAR-04
9.33.864 4183406 D 14-MAR-04
9.39.1073 5196085 D 18-MAR-04
1.15.1107 5196094 D 18-MAR-04
5.36.1089 5196100 D 18-MAR-04
9.30.1074 5198042 D 18-MAR-04