Wednesday, April 15, 2009

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

13 Comments:

Apun Hiran said...

Hello,
I also tried the PROPAGATION_MODE=> 'SYNCHRONOUS' also. Works fine.
EXEC DBMS_REPCAT.ADD_MASTER_DATABASE (GNAME=> 'REP2', MASTER=>'ORCL.ORACLE.COM',USE_EXISTING_OBJECTS => TRUE,COPY_ROWS=> FALSE, PROPAGATION_MODE=> 'SYNCHRONOUS');

The difference is if we set up PROPAGATION_MODE=> 'SYNCHRONOUS', then we dont need to setup jobs for syncing data, it happens using internal triggers.
Read: http://www.cs.umbc.edu/help/oracle8.bak/server803/A54651_01/ch7.htm#9024
Regards
Apun Hiran

GoldenGate said...

I wonder how much of this became obsolete after Goldengate?
Thanks.

mohanad awad said...

really thank work 100 % after 1 year search best setp by step oracle rep i see .... thanksssssssssssssss :)

mohanad awad

Unknown said...

Hello there, this works 100% the only modification i did was the "using" part of the dblink i used this: '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=ipOtherMaster)(Port=1521)))(CONNECT_DATA=(SID=ORCL)))';

and everything working now, thanks Regards from Guatemala. Leonel Rojas

Anonymous said...

Hi Yaar could please provide me the concepts on replication with views,sysnonyms,procedures.......

Ankush Sharma said...

Hi,
Have one question... For step 4, do we need to create the objects in both the databases or only in Master definition database.


Thanks,
Ankush Sharma

Apun Hiran said...

The schema should be identical in both the master databases. To avoid any data discrepancy between the databases. Before starting any DML or replication make sure the tables are identical in structure and data.

Regards
Apun

Anonymous said...

hi, i want to give samd sid to both servers , and one of my server is on 11gr2 and second server is on 10gr2 please guide me what changes do i have to make to make it work ... thanks.

Apun Hiran said...

Oracle streams uses GLOBAL_NAME and not DB/Instance Name. You should set different GLOBAL names for both databases:
Say if the DB name is ORCL, then on
DB1:
alter system set global_names=TRUE scope=both;
update global_name set global_name='ORCL_DB1';
check
select * from global_name;
on
DB2:
alter system set global_names=TRUE scope=both;
update global_name set global_name='ORCL_DB2';
check
select * from global_name;

Then create DB links as the global name.

I have only tried 10g to 11g replication once. It seems to work. From what I remember the same steps should work. Incase you get any issue do let me know.

Regards
Apun

Anonymous said...

hi,i have 120 table in a schema , all tables contains constraints , and i want to replicate only tables..i create same schema on both servers and same DB version ORACLE 10.2 but from windows server 2003 64bit to RHEL linux 64bit.my problem is that half of tables replication generation is valid and and half tables give error in all_repobject view .. before generating repliction support i disabled constraints on tables on both server , please guide me why tables are giving error saying "NEEDSGEN" ?? please guide me thanks..

Apun Hiran said...

I have not faced this issues before. Please go though the support.oracle.com note:
Designing for Survivability - Advanced Replication : A Practical Example [ID 138181.1]

It talk abt running DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS procedure to resolve this issue.

desc DBMS_OFFLINE_OG
PROCEDURE RESUME_SUBSET_OF_MASTERS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
GNAME VARCHAR2 IN
NEW_SITE VARCHAR2 IN
OVERRIDE BOOLEAN IN DEFAULT

More details on http://docstore.mik.ua/orelly/oracle/bipack/ch15_04.htm

Let me know if this resolves the problem.

Regards
Apun

Unknown said...

Hello,

men one thousand of thanks, you have saved my life haha.

THANKS!!!

Alok'blog said...

Hi Apun,

How we resync one table data from old backup in multi master advance replication through expdp.