How TO: Setup Multi-Master\Advanced Replication with Oracle 10g, 11g

As per the oracle documentation there are 3 main processes involved in replication between masters:
1.) Replication Administration: Create replication groups, defines replication objects and other replication administrative functions.
2.) Replication Propagation: Makes sure that the changes are sent from the database in which they have happened to all the other masters.
3.) Replication Receiver: Applies all the received changes to the destination master databases.

These three functions can be assigned to 3 different schema in the oracle database, or can be done by one schema.
I am going to use the same schema to perform all the above functions. The name of the schema, used most popularly is REPADMIN.

Init parameter JOB_QUEUE_PROCESSES should have a non zero value.

Enable GLOBAL_NAMES
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> alter system set global_names=TRUE;

(Master 1)
SQL> select * from global_name;
GLOBAL_NAME
-----------------
TEST.ORACLE.COM

(Master 2)
SQL> select * from global_name;
GLOBAL_NAME
----------------
ORCL.ORACLE.COM

Incase you want to change the global name:
alter database rename global_name to ;

In my case the name of the database are test (Linux) and orcl (windows) and the setup is replication between 2 databases, one is a Linux installation and the other is windows.

In a multi-master replication setup, one DB acts as the master definition database. This database is used for running administration and maintenance tasks. In our case we will use TEST DB as the master definition database.

Step 1:
Create REPADMIN schema in all the master databases.

SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

Step 2:
Granting admin permissions to REPADMIN schema. DBMS_REPCAT_ADMIN package is used to perform any administrative activity related to replication.

SQL> EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('REPADMIN');

PL/SQL procedure successfully completed.

Permission to allow REPADMIN to propogate changes.

SQL> EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');

PL/SQL procedure successfully completed.

Permission to allow REPADMIN to be a receiver.

SQL> EXEC DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (USERNAME => 'REPADMIN', PRIVILEGE_TYPE => 'RECEIVER', LIST_OF_GNAMES => NULL);

PL/SQL procedure successfully completed.

Step 3:
Create database links between the various master sites. The name of the DB Link should be same as the global name of the target database.
Need to create a public database link first:

(Master 1)
SQL> CREATE PUBLIC DATABASE LINK ORCL.ORACLE.COM USING 'ORCL';

Database link created.
(Master 2)
SQL> CREATE PUBLIC DATABASE LINK TEST.ORACLE.COM USING 'TEST';

Database link created.

Now create a private database link in REPADMIN schema.

(Master 1)
SQL> conn repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK ORCL.ORACLE.COM CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'ORCL';

Database link created.

(Master 2)
SQL> conn repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK TEST.ORACLE.COM CONNECT TO REPADMIN IDENTIFIED BY repadmin USING 'TEST';

Database link created.

Step 4:
Defining or creating replication objects.
I am using the HR schema and the tables present under it for replication purpose.
SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME
------------------------------
LOCATIONS
BLOB_TEST
EMPLOYEES
REGIONS
JOBS
JOB_HISTORY
DEPARTMENTS

7 rows selected.

Step 5:
Creation of replication group.
We need to create this group in the master definition DB or test DB in our case as REPADMIN.

SQL> CONN REPADMIN/REPADMIN
Connected.
SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP('REP2');

PL/SQL procedure successfully completed.

You can create multiple replication groups depending on our requirement, for different objects or set of objects.

Step 6:
Add objects/tables to the replication group REP2. Need to be executed only at the master definition DB (test).

SQL> EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'BLOB_TEST', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'DEPARTMENTS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'EMPLOYEES', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOBS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'JOB_HISTORY', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'LOCATIONS', 'TABLE', GNAME=>'REP2');
EXEC DBMS_REPCAT.CREATE_MASTER_REPOBJECT('HR', 'REGIONS', 'TABLE', GNAME=>'REP2');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.

Step 7:
Define the other master database in the replication setup at the master definition database i.e. add ORCL database as the other master in the replication.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> EXEC DBMS_REPCAT.ADD_MASTER_DATABASE (GNAME=> 'REP2', MASTER=>'ORCL.ORACLE.COM',USE_EXISTING_OBJECTS => TRUE,COPY_ROWS=> FALSE, PROPAGATION_MODE=> 'ASYNCHRONOUS');

PL/SQL procedure successfully completed.


Step 8:
Setup replication support for the tables marked for replication in the Step 6.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> show user
USER is "REPADMIN"
SQL> exec dbms_repcat.generate_replication_support('HR','BLOB_TEST','TABLE');
exec dbms_repcat.generate_replication_support('HR','DEPARTMENTS','TABLE');
exec dbms_repcat.generate_replication_support('HR','EMPLOYEES','TABLE');
exec dbms_repcat.generate_replication_support('HR','JOBS','TABLE');
exec dbms_repcat.generate_replication_support('HR','JOB_HISTORY','TABLE');
exec dbms_repcat.generate_replication_support('HR','LOCATIONS','TABLE');
exec dbms_repcat.generate_replication_support('HR','REGIONS','TABLE');

PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.

Step 9:
Enable replication between these databases.
Need to be executed only at the master definition DB (test). Run this as REPADMIN.

SQL> EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP2');
PL/SQL procedure successfully completed.

Step 10:
Start/create the data push job from all the master databases.
Define the time delay in the replication, what oracle needs to do when it encounters an error, do you need trace files for error, and the degree of parallelism.

(Master 1)
SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PUSH(DESTINATION =>'ORCL.ORACLE.COM', INTERVAL=>'SYSDATE + 1/60/24',NEXT_DATE=> SYSDATE,STOP_ON_ERROR => FALSE, DELAY_SECONDS => 0, WRITE_TRACE=>TRUE, PARALLELISM => 2);
PL/SQL procedure successfully completed.

(Master 2)
SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PUSH(DESTINATION =>'TEST.ORACLE.COM', INTERVAL=>'SYSDATE + 1/60/24',NEXT_DATE=> SYSDATE,STOP_ON_ERROR => FALSE, DELAY_SECONDS => 0, WRITE_TRACE=>TRUE, PARALLELISM => 2);
PL/SQL procedure successfully completed.

Step 11:
Define purge job, this purges the data\transaction which has been replicated. Need to run on all the masters.

SQL> EXEC DBMS_DEFER_SYS.SCHEDULE_PURGE(NEXT_DATE => SYSDATE,INTERVAL=>'SYSDATE + 1/24',DELAY_SECONDS=>0, ROLLBACK_SEGMENT => '');
PL/SQL procedure successfully completed.

Step 12:
Do checks:
Check for DBMS_JOBS in both masters:
SQL> @jobs
Job User Schema Last Date Next Date B F What
----------- ---------- ---------- ---------------- ---------------- ---- - -----------------------------------------------------------------
0: 66 REPADMIN REPADMIN 15.04.2009 09:18 15.04.2009 09:28 N 0 dbms_repcat.do_deferred_repcat_admin('"REP2"', FALSE);
0: 67 REPADMIN REPADMIN 15.04.2009 09:25 15.04.2009 09:26 N 0 declare rc binary_integer; begin rc := sys.dbms_defer_sys.pu
0: 68 REPADMIN REPADMIN 15.04.2009 09:25 15.04.2009 09:26 N 0 declare rc binary_integer; begin rc := sys.dbms_defer_sys.pu

Queries that can we used to check for the replication setup:

SELECT SNAME,ONAME,STATUS,GENERATION_STATUS FROM ALL_REPOBJECT; --> Status and other details of the replicating objects.
COL SOURCE FORMAT A10
COL SNAME FORMAT A10
COL ONAME FORMAT A10
SELECT ID,SOURCE,STATUS,TIMESTAMP,SNAME,ONAME,ERRNUM,MESSAGE FROM DBA_REPCATLOG; --> Status, any error details of the replication objects.
SELECT * FROM DBA_REPSITES; --> Master and Slaves site details.
SELECT * FROM DBA_REPSCHEMA; --> Schema's being replicated.
SELECT * FROM DBA_REPGROUP; --> The details of the replication groups.
SELECT * FROM DBA_REPGENERATED;
SELECT * FROM DBA_REPDDL; --> DDL of the changes that need to be sent to the other masters and slaves.
SELECT * FROM DBA_REPCONFLICT; --> Details of any replication conflicts.

Test to see if the replication is working fine:
(Master 1)
SQL> SELECT * FROM REGIONS;
REGION_ID REGION_NAME
------------ -------------------------
5 Antartica
6 Artic
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> INSERT INTO REGIONS VALUES (7,'CHINA');
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM REGIONS@ORCL.ORACLE.COM;
REGION_ID REGION_NAME
------------ -------------------------
5 Antartica
6 Artic
7 China
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

You can see that the replicaiton works fine from one master to other. Lets try the other way around also.
(Master 2)
SQL> SELECT * FROM REGIONS;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
7 China
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
7 rows selected.

SQL> INSERT INTO REGIONS VALUES (8,'US');
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM REGIONS@TEST.ORACLE.COM;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
7 China
8 US
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
8 rows selected.

While trying to setup this replication setup, I came across some other weblinks also. Please refer to them too as they have a lot of great information.
Setup Multi-master Replication with Oracle

Multimaster replication in Oracle

Advanced Replication FAQ