Master Replication

What is Advance Replication?Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Advanced Replication is a fully integrated feature of the Oracle server; it is not a separate server.Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the employees table resides at only the ny.world database in a distributed database system that also includes the hk.world and la.world databases. Replication means that the same data is available at multiple locations. For example, the employees table is available at ny.world, hk.world, and la.world.Some of the common reasons for using replication are:
AvailabilityReplication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, then users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.PerformanceReplication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.Disconnected computingA materialized view is a complete or partial copy (replica) of a target table from a single point in time. Materialized views enable users to work on a subset of a database while disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) materialized views on demand. When users refresh materialized views, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.Network load reductionReplication can be used to distribute data over multiple regional locations. Then, applications can access various regional servers instead of accessing one central server. This configuration can reduce network load dramatically.Architecture of advance replicationThere are two types of Advance Replication
1. Multimaster Replication2. Materialized view replication1. Multimaster replicationStart --- > set up master site --- > Select object for replication --- > Create Group for selected objects --- > if conflict possible then configure conflict resolution ---> end2. Materialized view replicationStart --- > set up master site -- > set up materialized view site --- > create materialized view group --- > endNote: In Detail explanation is Part-II, III.Replication SitesA replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. One site can be both a master site for one replication group and a materialized view site for a different replication group. However, one site cannot be both the master site and the materialized view site for the same replication group.The differences between master sites and materialized view sites are the following:Replication Group1. A replication group at a master site is more specifically referred to as a master group.2. A replication group at a materialized view site is based on a master group and is more specifically referred to as a materialized view group.Replication Objects1. A master site maintains a complete copy of all objects in a replication group,Example: if the hr_repg master group contains the table’s employees and departments, then all of the master sites participating in a master group must maintain a complete copy of employees and departments.2. Materialized views at a materialized view site can contain all or a subset of the table data within a master group.Example: one materialized view site might contain only a materialized view of the employees table, while another materialized view site might contain materialized views of both the employees and departments tables.Communication with master sites1. All master sites in a multimaster replication environment communicate directly with one another to continually propagate data changes in the replication group2. Materialized view sites contain an image, or materialized view, of the table data from a certain point in time.Replication ObjectsA replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:• Tables • Indexes • Views and Object Views • Packages and Package Bodies • Procedures and Functions • User-Defined Types and Type Bodies • Triggers • Synonyms • Index types • User-Defined Operators Replication GroupsIn a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related.By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. However, replication groups and schemas do not need to correspond with one another. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group.
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 accessMultimaster 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 parametersglobal_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 replicationdb01db02Setting up DB01 master site
Step 1 Connect as SYSTEM at a master site at DB01SQL> conn system/oracle@db01Connected.Step 2 Create the replication administrator at DB01SQL> CREATE USER repadmin IDENTIFIED BY repadmin;User created.Step 3 Grant privileges to the replication administrator at DB01SQL> BEGIN2 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 enviourmentsSQL> 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 DB01SQL> BEGIN2 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 DB01SQL> BEGIN2 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 DB01SQL> CONNECT repadmin/repadmin@db01Connected.SQL>SQL> BEGIN2 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@db01Connected.SQL> create public database link db02 using 'db02';Database link created.SQL> conn repadmin/repadmin@db01Connected.SQL> create database link db02 connect to repadmin identified by repadmin;Database link created.SQL> conn system/oracle@db02Connected.SQL> create public database link db01 using 'db01';Database link created.SQL> conn repadmin/repadmin@db02Connected.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@db01Connected.SQL> edWrote file afiedt.buf1 BEGIN2 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@db02Connected.SQL> edWrote file afiedt.buf1 BEGIN2 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 - table2. EMP - tablePrimary 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 groupSQL> conn repadmin/repadmin@db01Connected.SQL> begin2 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> edWrote file afiedt.buf1 BEGIN2 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> edWrote file afiedt.buf1 BEGIN2 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> edWrote file afiedt.buf1 BEGIN2 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@db01Connected.SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'SCOTT_REPG';DBLINK------------------------------------------------------------------DB01DB02Note: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> edWrote file afiedt.buf1 BEGIN2 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> edWrote file afiedt.buf1 BEGIN2 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(*)----------0Step-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> edWrote file afiedt.buf1 BEGIN2 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@db01Connected.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONSQL> insert into dept values (50,'DUMMY','DUMMY');1 row created.SQL> commit;Commit complete.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 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@db02Connected.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 DUMMY DUMMY --- New rowNote: 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@db02Connected.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(*)----------4SQL> disconnSQL> conn scott/tiger@db01Connected.SQL> select count(*) from dept;COUNT(*)----------4Altering a Replicated Object
Step 1 Connect to the master definition site as the replication administrator.SQL> conn repadmin/repadmin@db01Connected.Step 2 If necessary, then quiesce the master group.SQL> edWrote file afiedt.buf1 BEGIN2 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> EDWrote file afiedt.buf1 BEGIN2 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 tableStep 5 Regenerate replication support for the altered object.SQL> edWrote file afiedt.buf1 BEGIN2 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(*)----------0Note: Do not proceed until this view is empty.Step 7 Resume replication activity.SQL> edWrote file afiedt.buf1 BEGIN2 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@db01Connected.SQL> desc deptName Null? Type----------------------------------------- -------- -------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)TIMESTAMP DATESQL> conn scott/tiger@db02Connected.SQL> desc deptName Null? Type----------------------------------------- -------- -------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)TIMESTAMP DATEListing 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@db01Connected.SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25SQL> COLUMN MASTERDEF HEADING 'MasterDefinitionSite?' FORMAT A10SQL> SELECT GNAME, DBLINK, MASTERDEF2 FROM DBA_REPSITES3 WHERE MASTER = 'Y'4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')5 ORDER BY GNAME;MasterDefinitionMaster Group Sites Site?-------------------- ------------------------- ----------SCOTT_REPG DB02 NSCOTT_REPG DB01 YNote: DB02 is master site and DB01 is master definition site.
What is Advance Replication?Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Advanced Replication is a fully integrated feature of the Oracle server; it is not a separate server.Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the employees table resides at only the ny.world database in a distributed database system that also includes the hk.world and la.world databases. Replication means that the same data is available at multiple locations. For example, the employees table is available at ny.world, hk.world, and la.world.Some of the common reasons for using replication are:
AvailabilityReplication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, then users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.PerformanceReplication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.Disconnected computingA materialized view is a complete or partial copy (replica) of a target table from a single point in time. Materialized views enable users to work on a subset of a database while disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) materialized views on demand. When users refresh materialized views, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.Network load reductionReplication can be used to distribute data over multiple regional locations. Then, applications can access various regional servers instead of accessing one central server. This configuration can reduce network load dramatically.Architecture of advance replicationThere are two types of Advance Replication
1. Multimaster Replication2. Materialized view replication1. Multimaster replicationStart --- > set up master site --- > Select object for replication --- > Create Group for selected objects --- > if conflict possible then configure conflict resolution ---> end2. Materialized view replicationStart --- > set up master site -- > set up materialized view site --- > create materialized view group --- > endNote: In Detail explanation is Part-II, III.Replication SitesA replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. One site can be both a master site for one replication group and a materialized view site for a different replication group. However, one site cannot be both the master site and the materialized view site for the same replication group.The differences between master sites and materialized view sites are the following:Replication Group1. A replication group at a master site is more specifically referred to as a master group.2. A replication group at a materialized view site is based on a master group and is more specifically referred to as a materialized view group.Replication Objects1. A master site maintains a complete copy of all objects in a replication group,Example: if the hr_repg master group contains the table’s employees and departments, then all of the master sites participating in a master group must maintain a complete copy of employees and departments.2. Materialized views at a materialized view site can contain all or a subset of the table data within a master group.Example: one materialized view site might contain only a materialized view of the employees table, while another materialized view site might contain materialized views of both the employees and departments tables.Communication with master sites1. All master sites in a multimaster replication environment communicate directly with one another to continually propagate data changes in the replication group2. Materialized view sites contain an image, or materialized view, of the table data from a certain point in time.Replication ObjectsA replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:• Tables • Indexes • Views and Object Views • Packages and Package Bodies • Procedures and Functions • User-Defined Types and Type Bodies • Triggers • Synonyms • Index types • User-Defined Operators Replication GroupsIn a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related.By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. However, replication groups and schemas do not need to correspond with one another. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group.