Part-II Master ReplicationAfter Reading and understand Part-I. Now understand how to setup multimaster replication enviourment. What is Master Replication?Oracle has two types of master replication: single master replication and multimaster replication.
Note: Here I explain only Multimaster replication.1. Multimaster replication includes multiple master sites, where each master site operates as an equal peer.
2. Multimaster replication, also known as peer-to-peer or n-way replication, is comprised of multiple master sites equally participating in an update-anywhere model.
3. Oracle database servers operating as master sites in a multimaster replication environment automatically work to converge the data of all table replicas, and ensure global transaction consistency and data integrity.
4. Conflict resolution is independently handled at each of the master sites.
5. Multimaster replication provides complete replicas of each replicated table at each of the master sites.
Why Use Multimaster Replication?From a very basic point of view, replication is used to make sure that data is available when and where you need it.
But some of possibilities when we use multimaster replication are
FailoverMultimaster replication can be used to protect the availability of a mission critical database. For example, a multimaster replication environment can replicate data in your database to establish a failover site should the primary site become unavailable due to system or network outages. Such a failover site also can serve as a fully functional database to support application access when the primary site is concurrently operational.
Load BalancingMultimaster replication is useful for transaction processing applications that require multiple points of access to database information for the following purposes:
1. Distributing a heavy application load
2. Ensuring continuous availability
3. Providing more localized data access
Multimaster Replication ProcessThere are two types of multimaster replication: asynchronous and synchronous.
asynchronousAsynchronous replication, often referred to as store-and-forward replication, captures any local changes, stores them in a queue, and, at regular intervals, propagates and applies these changes at remote sites. With this form of replication, there is a period of time before all sites achieve data convergence.
synchronousSynchronous replication, also known as real-time replication, applies any changes or executes any replicated procedures at all sites participating in the replication environment as part of a single transaction. If the data manipulation language (DML) statement or procedure fails at any site, then the entire transaction rolls back. Synchronous replication ensures data consistency at all sites in real-time.
Note: You can change the propagation mode from asynchronous to synchronous or vice versa for a master site.How to setup Master sites?Set below parameters
global_namesalter system set global_names=TRUE;
job_queue_processesalter system set job_queue_processes = 1;
Considerations for Replicated TablesPrimary KeysIf possible, each replicated table should have a primary key. Where a primary key is not possible, each replicated table must have a set of columns that can be used as a unique identifier for each row of the table.
Foreign KeysWhen replicating tables with foreign key referential constraints, Oracle Corporation recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Indexes are not replicated automatically. To replicate an index, add it to the master group containing its table using either the Replication Management tool or the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package.
Create DB for replicationHere I created three databases for replication
db01
db02
Setting up DB01 master site
Step 1 Connect as SYSTEM at a master site at DB01
SQL> conn system/oracle@db01
Connected.
Step 2 Create the replication administrator at DB01
SQL> CREATE USER repadmin IDENTIFIED BY repadmin;
User created.
Step 3 Grant privileges to the replication administrator at DB01
SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /
PL/SQL procedure successfully completed.
Note: This privilege to create and manage a replicated enviourments
SQL> GRANT SELECT ANY DICTIONARY TO repadmin;
Grant succeeded.
Note: If you want your repadmin to be able to connect to the Replication Management tool,then grant SELECT ANY DICTIONARY to repadmin:
Step 4 Register the propagator at DB01
SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /
PL/SQL procedure successfully completed.
Note: The propagator is responsible for propagating the deferred transaction queue to other master sites.
Step 5 Register the receiver at DB01
SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /
PL/SQL procedure successfully completed.
Note: The receiver receives the propagated deferred transactions sent by the propagator from other master sites.
Step 6 Schedule purge at master site at DB01
SQL> CONNECT repadmin/repadmin@db01
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /
PL/SQL procedure successfully completed.
Note: In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. You must execute this procedure as the replication administrator.
Note: Repeat above 6 steps in database DB02 master sites.
Step-2 Create Scheduled Links between the Master Sites
Step 1 Create database links between master sites.
The database links provide the necessary distributed mechanisms to allow the different replication sites to replicate data among themselves. Before you create any private database links, you must create the public database links that each private database link will use. You then must create a database link between all replication administrators at each of the master sites that you have set up.
SQL> conn system/oracle@db01
Connected.
SQL> create public database link db02 using 'db02';
Database link created.
SQL> conn repadmin/repadmin@db01
Connected.
SQL> create database link db02 connect to repadmin identified by repadmin;
Database link created.
SQL> conn system/oracle@db02
Connected.
SQL> create public database link db01 using 'db01';
Database link created.
SQL> conn repadmin/repadmin@db02
Connected.
SQL> create database link db01 connect to repadmin identified by repadmin;
Database link created.
Step 2 Define a schedule for each database link to create scheduled links.
Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link that you created in Step 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.
Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. The scheduled links in this example simulate continuous replication.
SQL> conn repadmin/repadmin@db01
Connected.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'DB02',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> conn repadmin/repadmin@db02
Connected.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'DB01',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9* END;
SQL> /
PL/SQL procedure successfully completed.
Step-3 Create Master Group
Here I am using SCOTT schema and their objects for replicate purpose because SCOTT schema exists in Default Oracle installation.
Objects which use for replication in scott schema.
1. DEPT - table
2. EMP - table
Primary key must exists on each table which use for replication.
Note: If DB version is 10g then first unlock scott account;
Step-1 Unlock Scott Schema and reset schema password or Create New Schema for replication.
Step 2 Create the master group
SQL> conn repadmin/repadmin@db01
Connected.
SQL> begin
2 dbms_repcat.create_master_repgroup (
3 gname => 'scott_repg');
4 end;
5 /
PL/SQL procedure successfully completed.
Note: This step must be completed by the replication administrator.
Step 3 Add objects to master group.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'scott_repg',
4 type => 'TABLE',
5 oname => 'EMP',
6 sname => 'scott',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'scott_repg',
4 type => 'TABLE',
5 oname => 'DEPT',
6 sname => 'scott',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9* END;
SQL> /
PL/SQL procedure successfully completed.
Note: Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master group. In most cases, you probably will be adding tables and indexes to your master group, but you can also add procedures, views, synonyms, and so on.
Step 4 Add additional master sites.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE (
3 gname => 'scott_repg',
4 master => 'DB02',
5 use_existing_objects => TRUE,
6 copy_rows => FALSE,
7 propagation_mode => 'ASYNCHRONOUS');
8* END;
SQL> /
PL/SQL procedure successfully completed.
Note: You should wait until db02 appears in the DBA_REPSITES view before continuing. Execute the following SELECT statement in another SQL*Plus session to make sure that db02 has appeared:
SQL> conn repadmin/repadmin@db01
Connected.
SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'SCOTT_REPG';
DBLINK
------------------------------------------------------------------
DB01
DB02
Note:
1. In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the scott schema already exists at all master sites. In other words, it is assumed that the objects in the scott schema are precreated at all master sites. Also, the copy_rows parameter is set to FALSE because it is assumed that the identical data is stored in the tables at each master site.
2. Here I am using replication Process is “ASYNCHRONOUS”.
3. Here I am skip “conflict resolution methods”
Note: Repeat Step-3 at DB02 master site.
Step 5 Generate replication support.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'emp',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'dept',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
SQL> /
PL/SQL procedure successfully completed.
Note: You should wait until the DBA_REPCATLOG view is empty before resuming master activity. Execute the following SELECT statement to monitor your DBA_REPCATLOG view:
SQL> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'SCOTT_REPG';
COUNT(*)
----------
0
Step-6 Start replication.
After creating your master group, adding replication objects, generating replication support, and adding additional master databases, you need to start replication activity. Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the specified master group.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
SQL> /
PL/SQL procedure successfully completed.
Here we complete multimaster replication procedure.
Now let check what we did is correct or not :)
Connect with scott to db01 and insert new rows in dept table and commit;
SQL> conn scott/tiger@db01
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values (50,'DUMMY','DUMMY');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DUMMY DUMMY--- New row
--Wait some time ...and connect scott to db02 database and check that new row replicate in dept table.
SQL> conn scott/tiger@db02
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DUMMY DUMMY --- New row
Note: you can perform any DML operation on Master Definition site “db01” and commit. Changes will update in DB02 site and if you perform any changes in DB02 db then update will changes in DB01 vice versa.
SQL> conn scott/tiger@db02
Connected.
SQL> select count(*) from dept;
COUNT(*)
----------
5
--Delete One row in dept table “db02”.
SQL> delete dept where dname = 'DUMMY';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> disconn
SQL> conn scott/tiger@db01
Connected.
SQL> select count(*) from dept;
COUNT(*)
----------
4
Altering a Replicated Object
Step 1 Connect to the master definition site as the replication administrator.
SQL> conn repadmin/repadmin@db01
Connected.
Step 2 If necessary, then quiesce the master group.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
5 /
PL/SQL procedure successfully completed.
Step 3 In a separate SQL*Plus session, check the status of the master group you are quiescing.
Do not proceed until the group's status is QUIESCED.
SQL> SELECT GNAME, STATUS FROM DBA_REPGROUP;
GNAME STATUS
------------------------------ ---------
SCOTT_REPG QUIESCING
Step 4 Alter the replicated object.
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
3 sname => 'SCOTT',
4 oname => 'dept',
5 type => 'TABLE',
6 ddl_text => 'ALTER TABLE SCOTT.DEPT ADD (timestamp DATE)');
7* END;
SQL> /
PL/SQL procedure successfully completed.
Note: Add Timestamp column in scott.dept@db01 table
Step 5 Regenerate replication support for the altered object.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'dept',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
8 /
PL/SQL procedure successfully completed.
Step 6 In a separate SQL*Plus session, check if DBA_REPCATLOG is empty.
SQL> select count(*) from dba_repcatlog;
COUNT(*)
----------
0
Note: Do not proceed until this view is empty.
Step 7 Resume replication activity.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
SQL> /
PL/SQL procedure successfully completed.
Now check dept table in both database at “db01” or “db02”.
SQL> conn scott/tiger@db01
Connected.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- -------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
TIMESTAMP DATE
SQL> conn scott/tiger@db02
Connected.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- -------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
TIMESTAMP DATE
Listing the Master Sites Participating in a Master Group
Here I created two DB, one is master definition site and other is Master site.
Through below query we can fintout which one is master definition site or which is master site.
SQL> conn repadmin/repadmin@db01
Connected.
SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20
SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25
SQL> COLUMN MASTERDEF HEADING 'Master|Definition|Site?' FORMAT A10
SQL> SELECT GNAME, DBLINK, MASTERDEF
2 FROM DBA_REPSITES
3 WHERE MASTER = 'Y'
4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y'
)
5 ORDER BY GNAME;
Master
Definition
Master Group Sites Site?
-------------------- ------------------------- ----------
SCOTT_REPG DB02 N
SCOTT_REPG DB01 Y
Note: DB02 is master site and DB01 is master definition site.
