Replication is the process of copying and maintaining database objects of one site or to another site. In the multi master environment the changes made to the database through DML are reflected to the other sites per schedule and all the databases can be kept in sync.

Advantages

(1) Availability Replication improves the availability if the database in case a database is not available for some reason. It thus provides fail-over protection.
(2) Performance Replication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access other servers, thereby reducing the load at all servers.
(3) Snapshot The Snapshot mechanism of oracle creates a sub-set of the data and keeps it available to the users even if the master site is not available. When the master site is up and running the modifications to the data can be pushed to the master site and the database can be brought into sync.
(4) Network Traffic If the database is in a distributed environment thorough the replication, the network traffic to the database can be reduced as the data access can be distributed.

Hierarchy In Replication

(1) Replication Objects

It is a database object existing on multiple servers. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites.

The objects include tables, indexes, views, packages and package bodies, procedures and functions, triggers, sequences and synonyms.

(2) Replication Groups

A replication group is a collection of replication objects that are logically related. The objects in a replication group are administered together. For instance all objects related to one module can be put together as one group. There could be some objects that are used by almost all the objects. The master or static tables, of which all the modules share data, can be grouped together as a separate group. This is all for administrative convenience of the groups.

(3) Replication Sites

A replication group can exist at multiple replication sites. Oracle Replication Sites, depending upon the nature of replication, are of two types.

(1) Master Sites and
(2) Snapshot Sites

1

Types of Replication:

2

3

4

Repeat the Stage One on each server that is participating in any type of replication

Set up of Master Sites Stage -02

5

(Source Oracle Documentation)

Stepwise commands to setup multi master sites, assuming that there are 3 servers/databases participating in the multi master replication and 2 servers participating as 2 servers as snapshot sites. KNOW9I, REPLICA1, REPLICA2 are multi-master sites and SNAP1, SNAP2 are the snapshot site names used in the scripts below.

On Server #1 or database KNOW9I

/*************************************************************************

STEP 1 @ KNOW9I:
CONNECT AS SYSTEM AT MASTER SITE

–Connect as SYSTEM to the database that you want to
–setup for replication. After you setup KNOW9I,
–begin again with STEP 1 for sites REPLICA1 and REPLICA2.
*************************************************************************/

CONNECT SYSTEM/MANAGER@KNOW9I

/************************************************************************
STEP 2 @ KNOW9I:
CREATE REPLICATION ADMINISTRATOR

–The replication administrator must be granted the necessary privileges
–to create and manage a replicated environment. The replication
–administrator must be created at each database that participates
–in the replicated environment.
*************************************************************************/

CREATE USER repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 3 @ KNOW9I:
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

–Executing the GRANT_ADMIN_ANY_SCHEMA API grants the replication
–administrator powerful privileges to create and manage a replicated
–environment.
*************************************************************************/

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
USERNAME => ‘repadmin’);
END;
/

/************************************************************************
–If you want your REPADMIN to be able to create snapshot logs for any
–replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.
**************************************************************************/

grant COMMENT ANY TABLE to REPADMIN;
grant LOCK ANY TABLE to REPADMIN;

/*************************************************************************
STEP 4 @ KNOW9I:
REGISTER PROPAGATOR

–The propagator is responsible for propagating the deferred transaction
–queue to other master sites.

*************************************************************************/

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
USERNAME => ‘repadmin’);
END;
/

/*************************************************************************
STEP 5 @ KNOW9I:
REGISTER RECEIVER

–The receiver receives the propagated deferred transactions sent
–by the propagator from other master sites.

*************************************************************************/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
USERNAME => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

/*************************************************************************
STEP 6 @ KNOW9I:
SCHEDULE PURGE AT MASTER SITE

–In order to keep the size of the deferred transaction queue in check,
–you should purge successfully completed deferred transactions. The
–SCHEDULE_PURGE API automates the purge process for you. You must execute
–this procedure as the replication administrator.

*************************************************************************/

CONNECT repadmin/repadmin@KNOW9I

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘sysdate + 1/24’,
delay_seconds => 0,
rollback_segment => ”);
END;
/

/*************************************************************************

STEP 7:

CREATE MASTER SITE USERS

STEP 7a: CREATE PROXY SNAPSHOT ADMINISTRATOR

–The proxy snapshot administrator performs tasks at the target master
–site on behalf of the snapshot administrator at the snapshot site.
–See “Security Setup for Snapshot Replication” in Oracle8i Replication.

*************************************************************************/
CONNECT system/manager@KNOW9I

CREATE USER proxy_snapadmin IDENTIFIED BY proxy_snapadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘PROXY_SNAPADMIN’,
privilege_type => ‘PROXY_SNAPADMIN’,
list_of_gnames => NULL);
END;
/

/************************************************************************

STEP 7b: CREATE PROXY REFRESHER

–The proxy refresher performs tasks at the master site on behalf of
–the refresher at the snapshot site.

*************************************************************************/
CONNECT system/manager@KNOW9I

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

/*************************************************************************
STEP 7:
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. See
Oracle8i Distributed Database Systems for more information.
*************************************************************************/

–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.

CONNECT system/manager@KNOW9I
CREATE PUBLIC DATABASE LINK REPLICA1 USING ‘REPLICA1’;
CREATE PUBLIC DATABASE LINK REPLICA2 USING ‘REPLICA2’;

CONNECT repadmin/repadmin@KNOW9I
CREATE DATABASE LINK REPLICA1 CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK REPLICA2 CONNECT TO repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 8:
CREATE SCHEDULED LINKS

Create a scheduled link by defining a database link when you execute the
SCHEDULE_PUSH procedure (see “SCHEDULE_PUSH procedure”
for more information).

–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 7. The database link is specified in the DESTINATION parameter
–of the SCHEDULE_PUSH procedure.

*************************************************************************/

CONNECT repadmin/repadmin@KNOW9I

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘REPLICA1’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘REPLICA2’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

On Server #2 or database REPLICA1

/*************************************************************************
STEP 1 @ REPLICA1:
CONNECT AS SYSTEM
*************************************************************************/

–NOTE:
–Multiple master sites (multimaster replication) can only be used with
–Oracle8i Enterprise Edition. If you are not using Oracle8i Enterprise
–Edition, skip to step 8.

–You must connect as SYSTEM to the database that you want to
–set up for replication. After you set up REPLICA1,
–begin again with STEP 1 for site REPLICA2.

connect SYSTEM/MANAGER@REPLICA1

/*************************************************************************
STEP 2 @ REPLICA1:
CREATE REPLICATION ADMINISTRATOR
*************************************************************************/

–The replication administrator must be granted the necessary privileges
–to create and manage a replicated environment. The replication
–administrator must be created at each database that participates
–in the replicated environment.

create user REPADMIN identified by REPADMIN;

/*************************************************************************
STEP 3 @ REPLICA1:
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

For additional information about the GRANT_ADMIN_ANY_SCHEMA API, see “GRANT_
ADMIN_ANY_SCHEMA procedure”.
–Executing the GRANT_ADMIN_ANY_SCHEMA API grants the replication
–administrator powerful privileges to create and manage a replicated
–environment.
*************************************************************************/

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘REPADMIN’);
END;
/

/***********************************************************************
–If you want your REPADMIN to be able to create snapshot logs for any
–replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.

*************************************************************************/

grant COMMENT ANY TABLE to REPADMIN;
grant LOCK ANY TABLE to REPADMIN;

/************************************************************************
STEP 4 @ REPLICA1:
REGISTER PROPAGATOR

For additional information about the REGISTER_PROPAGATOR API, see “REGISTER_
PROPAGATOR procedure”.

–The propagator is responsible for propagating the deferred transaction
–queue to other master sites.

*************************************************************************/

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => ‘REPADMIN’);
END;
/

/*************************************************************************
STEP 5 @ REPLICA1:
REGISTER RECEIVER

For additional information about the REGISTER_USER_REPGROUP API, see “REGISTER_
USER_REPGROUP procedure”.

–The receiver receives the propagated deferred transactions sent
–by the propagator from the other master sites.
*************************************************************************/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

/*************************************************************************
STEP 6 @ REPLICA1:
SCHEDULE PURGE AT MASTER SITE

For additional information about the SCHEDULE_PURGE API, see “SCHEDULE_PURGE
procedure”.

–In order to keep the size of the deferred transaction queue in check,
–you should purge successfully completed deferred transactions. The
–SCHEDULE_PURGE API automates the purge process for you. You must execute
–this procedure as the replication administrator.

*************************************************************************/
CONNECT repadmin/repadmin@REPLICA1

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘sysdate + 1/24’,
delay_seconds => 0,
rollback_segment => ”);
END;
/

/*************************************************************************
STEP 7:
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. See
Oracle8i Distributed Database Systems for more information.
*************************************************************************/

CONNECT system/manager@REPLICA1
CREATE PUBLIC DATABASE LINK KNOW9I USING ‘KNOW9I’;
CREATE PUBLIC DATABASE LINK REPLICA2 USING ‘REPLICA2’;

CONNECT repadmin/repadmin@REPLICA1
CREATE DATABASE LINK KNOW9I CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK REPLICA2 CONNECT TO repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 8:

CREATE SCHEDULED LINKS

Create a scheduled link by defining a database link when you execute the
SCHEDULE_PUSH procedure (see “SCHEDULE_PUSH procedure”
for more information).

–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 7. The database link is specified in the DESTINATION parameter
–of the SCHEDULE_PUSH procedure.

**************************************************************************/

CONNECT repadmin/repadmin@REPLICA1

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘KNOW9I’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘REPLICA2’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

On Server #3 or database REPLICA2

/*************************************************************************
STEP 1 @ REPLICA2:
CONNECT AS SYSTEM
–NOTE:
–Multiple master sites (multimaster replication) can be used only with
–Oracle8i Enterprise Edition. If you are not using Oracle8i Enterprise
–Edition, skip to step 8.

–You must connect as SYSTEM to the database that you want to
–set up for replication.
*************************************************************************/

connect SYSTEM/MANAGER@REPLICA2

/*************************************************************************
STEP 2 @ REPLICA2:
CREATE REPLICATION ADMINISTRATOR
*************************************************************************/

–The replication administrator must be granted the necessary privileges
–to create and manage a replicated environment. The replication
–administrator must be created at each database that participates
–in the replicated environment.

create user REPADMIN identified by REPADMIN;

/*************************************************************************
STEP 3 @ REPLICA2:
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

For additional information about the GRANT_ADMIN_ANY_SCHEMA API, see “GRANT_
ADMIN_ANY_SCHEMA procedure”.

–Executing the GRANT_ADMIN_ANY_SCHEMA API grants the replication
–administrator powerful privileges to create and manage a replicated
–environment.

*************************************************************************/

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘REPADMIN’);
END;
/

/*************************************************************************
–If you want your REPADMIN to be able to create snapshot logs for any
–replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.

*************************************************************************/

grant COMMENT ANY TABLE to REPADMIN;
grant LOCK ANY TABLE to REPADMIN;

/**************************************************************************
STEP 4 @ REPLICA2:
REGISTER PROPAGATOR

For additional information about the REGISTER_PROPAGATOR API, see “REGISTER_
PROPAGATOR procedure”.

–The propagator is responsible for propagating the deferred transaction
–queue to other master sites.

*************************************************************************/

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => ‘REPADMIN’);
END;
/

/*************************************************************************
STEP 5 @ REPLICA2:
REGISTER RECEIVER

For additional information about the REGISTER_USER_REPGROUP API, see “REGISTER_
USER_REPGROUP procedure”.
*************************************************************************/

–The receiver receives the propagated deferred transactions sent
–by the propagator from the other master sites.

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

/*************************************************************************
STEP 6 @ REPLICA2:
SCHEDULE PURGE AT MASTER SITE

For additional information about the SCHEDULE_PURGE API, see “SCHEDULE_PURGE
procedure”.

–In order to keep the size of the deferred transaction queue in check,
–you should purge successfully completed deferred transactions. The
–SCHEDULE_PURGE API automates the purge process for you. You must execute
–this procedure as the replication administrator.

*************************************************************************/

CONNECT repadmin/repadmin@REPLICA2

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘sysdate + 1/24’,
delay_seconds => 0,
rollback_segment => ”);
END;
/

/*************************************************************************
STEP 7:

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. See
Oracle8i Distributed Database Systems for more information.

*************************************************************************/

CONNECT system/manager@REPLICA2

CREATE PUBLIC DATABASE LINK KNOW9I USING ‘KNOW9I’;
CREATE PUBLIC DATABASE LINK REPLICA1 USING ‘REPLICA1’;

CONNECT repadmin/repadmin@REPLICA2
CREATE DATABASE LINK KNOW9I CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK REPLICA1 CONNECT TO repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 8:

CREATE SCHEDULED LINKS

Create a scheduled link by defining a database link when you execute the
SCHEDULE_PUSH procedure (see “SCHEDULE_PUSH procedure”
for more information).

–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 7. The database link is specified in the DESTINATION parameter
–of the SCHEDULE_PUSH procedure.

**************************************************************************/

CONNECT repadmin/repadmin@REPLICA2

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘KNOW9I’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘REPLICA1’,
interval => ‘SYSDATE + 10 / (24 * 60)’,
next_date => SYSDATE);
END;
/

Stage 03

Setup SNAPSHOT SITE ( Means that it is the TARGET SITE)

The flow diagram explains all the steps involved in the setup of snapshot site

/*************************************************************************

STEP 1:
CONNECT AS SYSTEM AT SNAPSHOT SITE
–You must connect as SYSTEM to the database that you want to
–set up as a snapshot site.

*************************************************************************/

CONNECT system/manager@snap1

/*************************************************************************
STEP 2:
CREATE SNAPSHOT SITE USERS
–Several users need to be created at the snapshot site. These users are:
— SNAPSHOT ADMINISTRATOR
— PROPAGATOR
— REFRESHER
**************************************************************************
–STEP 2a: CREATE SNAPSHOT ADMINISTRATOR
–The snapshot administrator is responsible for creating and managing
–the snapshot site. Execute the GRANT_ADMIN_ANY_SCHEMA
–procedure to grant the snapshot administrator the appropriate privileges.
*************************************************************************/

create user SNAPADMIN identified by SNAPADMIN;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘SNAPADMIN’);
END;
/

/*************************************************************************

–STEP 2b: CREATE PROPAGATOR
–The propagator is responsible for propagating the deferred transaction
–queue to the target master site.

**************************************************************************/

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘propagator’);
END;
/

/**************************************************************************
–STEP 2c: CREATE REFRESHER
–The refresher is responsible for “pulling” changes made to the replicated
–tables at the target master site to the snapshot site.

***************************************************************************/

create user REFRESHER identified by REFRESHER;

GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY SNAPSHOT TO refresher;

/*************************************************************************
STEP 3:
CREATE DATABASE LINKS TO MASTER SITE

**************************************************************************

STEP 3A: CREATE PUBLIC DATABASE LINK

*************************************************************************/

CONNECT system/manager@snap1

CREATE PUBLIC DATABASE LINK KNOW9I USING ‘KNOW9I’;

/***********************************************************************
STEP 3b: CREATE SNAPSHOT ADMINISTRATOR DATABASE LINK
You need to create a database link from the snapshot administrator at the
snapshot site to the proxy snapshot administrator at the master site.
This is a private database link for snapadmin
************************************************************************/

CONNECT snapadmin/snapadmin@snap1;

CREATE DATABASE LINK orc1
CONNECT TO proxy_snapadmin
IDENTIFIED BY proxy_snapadmin;

/***************************************************************************

STEP 3c: CREATE PROPAGATOR/RECEIVER DATABASE LINK

You need to create a database link from the propagator at the snapshot site to the receiver at the master site. The receiver was defined when you created the master group.

This is a private database link for snapadmin.

****************************************************************************/

CONNECT propagator/propagator@snap1

CREATE DATABASE LINK KNOW9I
CONNECT TO repadmin
IDENTIFIED BY repadmin;

/*************************************************************************
STEP 4:
SCHEDULE PURGE AT SNAPSHOT SITE

In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE API automates the purge process for you. If your snapshot site only contains “read-only” snapshots, then you do not need to execute this procedure.

The interval is set to one (1) hour

*************************************************************************/

CONNECT snapadmin/snapadmin@snap1;

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘sysdate + 1/24’,
delay_seconds => 0,
rollback_segment => ”);
END;
/

/*************************************************************************
STEP 5:
SCHEDULE PUSH AT SNAPSHOT SITE

The SCHEDULE_PUSH API schedules when the deferred transaction queue should be propagated to the target master site.

The interval is set to 1 hour.

*************************************************************************/

CONNECT snapadmin/snapadmin@snap1
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘ORC1.WORLD’,
interval => ‘sysdate + 1/24’,
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
Stage 03 creating a Master Group and adding Objects

The following flow chart explains all the steps involved in creating a master group:

The following commands are to be executed to complete the enumerated steps in the flowchart

/*************************************************************************
STEP 1:
CREATE SCHEMA AT MASTER SITES
*************************************************************************/

CONNECT system/manager@KNOW9I;

CREATE USER REP_USER IDENTIFIED BY REP_USER;

GRANT CONNECT, RESOURCE TO REP_USER;

CONNECT system/manager@REPLICA2;

CREATE USER REP_USER IDENTIFIED BY REP_USER;

GRANT CONNECT, RESOURCE TO REP_USER;

/*************************************************************************
STEP 2:

CREATE MASTER GROUP

Name the master group.

DBMS_REPCAT.CREATE_MASTER_REPGROUP

IN PARAMETER GNAME STANDS FOR GROUP NAME

*************************************************************************/

CONNECT repadmin/repadmin@KNOW9I

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => ‘REP_USER _MG’ — group_name
);
END;
/

/*************************************************************************
STEP 3:
ADD OBJECTS TO MASTER GROUP
*************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘REP_USER _MG’, –group name
type => ‘TABLE’ –object type
oname => ‘TAB_1’, –object_name
sname => ‘REP_USER’, –schema name
use_existing_object => TRUE,
copy_rows => TRUE
);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘REP_USER _MG’, — group name
type => ‘TABLE’, — object_type
oname => ‘TAB_2’, — object_name
sname => ‘REP_USER’, — schema owner
use_existing_object => TRUE,
copy_rows => TRUE
);
END;
/

/*********************************************************************************
The tables TAB_1 and TAB_2 have a primary keys on them.

Some tables may not have Primary Keys at all.

For replication to work properly, each replicated table either needs
a primary key or to have a “set column.”

DBMS_REPCAT.SET_COLUMNS

procedure is sufficient for multimaster replication only.

To support fast refreshable snapshots,every table is to have a primary key.

It is easier to alter your object before you add it to your master group.

***********************************************************************************/

ALTER TABLE rap_user.tab_3
ADD (CONSTRAINT bonus_pk PRIMARY KEY(ename));

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘REP_USER_MG’, — group name
type => ‘TABLE’, — object type
oname => ‘tab_3 ‘, — object name
sname => ‘rap_user’, — schema owner
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

/***********************************************************************************

Note:

It is better to identify all the table that do not have a primary key on them and add a
primary key to each table that is to participate in the multi master replication and
Snapshot replication with fast refresh condition.

************************************************************************************
After finishing that job go to STEP 04

************************************************************************************
STEP 4:

ADD ADDITIONAL MASTER SITES

After the master group at the MASTERDEF site is defined (the site where the master group
was created becomes the MASTER DEFINITION site by default).

In the example there are two more sites REPLICA1 and REPLICA2 that participate in the replication.

They are to be added as master sites

************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘rep_user_mg’,
master => ‘REPLICA1’,
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => ‘ASYNCHRONOUS’
);
END;
/

/*************************************************************************
NOTE:

You should wait until REPLICA1 appears in the DBA_REPSITES view before continuing.

Execute the following SELECT statement at SQL*Plus

SELECT * FROM dba_repsites WHERE gname = upper(‘rep_user_mg’);

*************************************************************************/

PAUSE Press to continue.

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘rep_user_mg’,
master => ‘REPLICA2’,
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => ‘ASYNCHRONOUS’
);
END;
/

/*************************************************************************
NOTE:

You should wait until REPLICA2 appears in the DBA_REPSITES view before continuing.

Execute the following SELECT statement at SQL*Plus session:

SELECT * FROM dba_repsites WHERE gname = upper(‘rep_user_mg’);

*************************************************************************/

PAUSE Press to continue.

/*************************************************************************

CAUTION:

If you added one or more tables to a master group during creation of the group,
do not resume replication activity immediately.

First consider the possibility of replication conflicts, and configure conflict
resolution for the replicated tables in the group.

*************************************************************************/

PAUSE Press to continue.

/*************************************************************************
STEP 5:
GENERATE REPLICATION SUPPORT
*************************************************************************/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_1’,
type => ‘TABLE’,
min_communication => TRUE
);
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_2’,
type => ‘TABLE’,
min_communication
);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_3’,
type => ‘TABLE’,
min_communication => TRUE
);
END;
/

/*************************************************************************
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:

SELECT * FROM dba_repcatlog WHERE gname = ‘rep_user_mg’;

*************************************************************************/

PAUSE Press to continue.

/*************************************************************************
STEP 6:

RESUME REPLICATION
*************************************************************************

After
(1) creating group is completed,
(2) adding replication objects is completed,
(3) replication support is completed,
(4) and adding additional master databases is completed,

to resume replication activity …..

***************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => ‘REP_USER_MG’);
END;
/
Stage 04 – Creating SNAPSHOT GROUP and ADDING OBJECTS

/*************************************************************************
STEP 1:
CREATE SNAPSHOT LOGS AT MASTER SITE

ON PAGE #3 of this Document KNOW9I, REPLICA1 and REPLICA2 databases are in multi-master
replication environment and SNAP1 and SNAP2 are snapshot databases.

Master Site
Source Site
SOURCE
TARGET
KNOW9I
SNAP1
REPLICA1
SNAP2
REPLICA2
—-

*************************************************************************/
CONNECT REP_USER/REP_USER@KNOW9I

CREATE SNAPSHOT LOG ON REP_USER.TAB_1;
CREATE SNAPSHOT LOG ON REP_USER.TAB_2;
CREATE SNAPSHOT LOG ON REP_USER.TAB_3;

/*************************************************************************
STEP 2:

CREATE REPLICATED SCHEMA AND LINKS

Make sure that the user exsits the snapshot site. If not create that user.
make sure that DB link exists, else create necessary DB Links.

Assuming that there is no user like ‘REP_USER’ THAT USER IS CREATED

*************************************************************************/

CONNECT system/manager@snap1

CREATE USER REP_USER
IDENTIFIED BY REP_USER
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT connect, resource TO REP_USER;

CONNECT REP_USER/REP_USER@snap1

/*************************************************************************

–The owner of the snapshots needs a database link pointing to the
–proxy_refresher that was created when the snapshot site was set up

***************************************************************************/

CREATE DATABASE LINK KNOW9I
CONNECT TO proxy_refresher
IDENTIFIED BY proxy_refresher;

/*************************************************************************
STEP 3:
CREATE SNAPSHOT GROUP
The following procedures must be executed by the snapshot administrator
at the remote snapshot site.

*************************************************************************/

CONNECT snapadmin/snapadmin@snap1

/***************************************************************************

Note:

The master group specified in the GNAME parameter must match the name of the
master group that you are replicating at the target master site.

***************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP (
gname => ‘rep_user_MG’,
master => ‘KNOW9I’,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/

/*************************************************************************
STEP 4:

CREATE REFRESH GROUP

All snapshots that are added to a particular refresh group are refreshed at the same time.
This ensures transactional consistency between the related snapshots in the refresh group.

The interval assumed here, in the example is 1 hour

*************************************************************************/

BEGIN
DBMS_REFRESH.MAKE (
name => ‘SNAPADMIN.SCOTT_RG’,
list => ”,
next_date => SYSDATE,
interval => ‘sysdate + 1/24’,
implicit_destroy => FALSE,
rollback_seg => ”,
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
/

/*************************************************************************
STEP 5:

ADD OBJECTS TO SNAPSHOT GROUP

Whenever a snapshot is created, always specify the schema name of the table
owner in the query for the snapshot.

*************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
gname => ‘REP_USER_MG’,
sname => ‘REP_USER’,
oname => ‘TAB_1’,
type => ‘SNAPSHOT’,
ddl_text => ‘create snapshot REP_USER.TAB_1 refresh fast with primary key
for update as select * from REP_USER.TAB_1@KNOW9I’,
min_communication => TRUE);
END;
/

/*************************************************************************

REPEAT THE SAME FOR ALL THE OBJECT IN THE GROUP

**************************************************************************/

/*************************************************************************
STEP 6:

ADD OBJECTS TO REFRESH GROUP

All of the snapshot group objects that you add to the refresh group are refreshed
at the same time to preserve referential integrity between related snapshots.

*************************************************************************/

BEGIN
DBMS_REFRESH.ADD (
name => ‘SNAPADMIN.REP_USER_RG’,
list => ‘REP_USER.TAB1’,
lax => TRUE);
END;
/

/*************************************************************************

REPEAT THE SAME FOR ALL THE OBJECT IN THE GROUP

**************************************************************************/

The above statements suitably altering the source database name to REPLICA1 and target database name to snap2 are to be implemented at SNAP2 target database and REPLICA1 source database.

The most common data conflict occurs when the same row at two or more different sites were updated at the same time, or before the deferred transaction from one site was successfully propagated to the other sites.

One method to avoid update conflicts is to implement a synchronous replicated environment, though this solution requires large network resource.

The other solution is to use the Oracle conflict resolution methods to deal with update conflicts that may occur when the same row has received two or more updates.

1. UPDATE CONFLICTS

1.1 Overwrite and Discard Methods of Resolving Conflicts

Overwrite and Discard methods ignore the values from either the originating or destination site and therefore can never guarantee convergence with more than one master site. These methods are designed to be used by a single master site and multiple snapshot sites, or with some form of a user-defined notification facility.

The overwrite method replaces the current value at the destination site with the new value from the originating site. Conversely, the discard method ignores the new value from the originating site.

Target Environments:

The overwrite conflict resolution method ensures data convergence for replication environments that have a single master site with any number of materialized views. With this in mind, the overwrite conflict resolution method is ideal for mass deployment environments.

Resolution:

If a conflict is detected, then the value originating from the materialized view site is used, which means that priority is given to the most recently refreshed materialized views.

For the purpose of the Example:

Planet is the Master Site
Snap1 is the snapshot site -1
Snap2 is the snapshot site -2
SNAP_1_MG is the Master Group Name
TAB_1 is the Object Name which has conflict

/********************************************************************************

Step 01: connect as REPADMIN to Master Site and suspend Replication

********************************************************************************/

connect repadmin/repadmin@planet


BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘SNAP_1_MG’);
END;
/


/******************************************************************************************
Step 02:

All Oracle conflict resolution methods are based on logical column groupings called “column groups.” Create a column group for target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

*********************************************************************************************/


BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘rep_user’, — schema owner
oname => ‘tab_1’, — object name
column_group => ‘rep_user_cg1’, –column group name
list_of_column_names => ‘col1,col2,col3’
);
END;
/

/*************************************************************************************************

Step 03:

Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION to define the conflict resolution method for a specified table. This example creates an “Overwrite” conflict resolution method.

*************************************************************************************************/


BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘rep_user’, — schema owner
oname => ‘tab_1’, — object name
column_group => ‘rep_user_cg1’, — column group name
sequence_no => 1, — the order conflict resolutions to be applied
method => ‘OVERWRITE’,
parameter_column_name => ‘col1,col2,col3’
);
END;
/

/************************************************************************************************

Step 04:

After defining conflict resolution method, regenerate replication support for the table that received the conflict resolution method.

*************************************************************************************************/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’, — schema owner
oname => ‘tab_1’, — object name
type => ‘TABLE’, — object type
min_communication => TRUE
);
END;
/


/*************************************************************************************************

Step 05:

After replication support has been regenerated, resume replication activity by using the RESUME_MASTER_ACTIVITY procedure

*************************************************************************************************/


BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘snap1_mg’ — master group name
);
END;
/

1.2 Minimum and Maximum:

When the advanced replication facility detects a conflict with a column group and calls either the minimum or maximum value conflict resolution methods, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you define your conflict resolution method.

If the new value of the designated column is less than or greater than (depending on the method used) the current value, the column group values from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. Otherwise the rows remain unchanged.

In this method of resolution there are two conditions that are to be fulfilled for resolution. They are:

a. Pick the designated column
b. Define the method ‘minimum’ or ‘maximum’ on the column
c. Basing on the defined method compare the current of the source database (master database) and update the destination site (target site).

Target Environments:

If you have defined the minimum conflict resolution method and the target column that is used to resolve the conflict is always decreasing across all sites, then this method guarantees data convergence with any number of master sites and materialized view sites.


/*************************************************************************************************

Step 01:

01. connect to the Originating/Master/Source Site
02. Before you can define any conflict resolution methods, quiesce the master group that contains the table to which you want to apply the conflict resolution method by suspending the replication activity

*************************************************************************************************/

connect REPADMIN/REPADMIN@REP1

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘REP_USER_MG’ – master group name
);
END;
/


/************************************************************************************************

Step 02:

Create the column group for the target table for which the resolution method is to be set up

*************************************************************************************************/
BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘rep_user’, — schema owner
oname => ‘tab_2’, — object name
column_group => ‘tab_2_col_grp’, — column group name
list_of_column_names => ‘col1’); — designated column name
END;
/
/*************************************************************************************************

Step 03:

Define the method on the designated column for the designated table of the designated schema

*************************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘rep_user’, — schema owner
oname => ‘tab_2’, — object name
column_group => ‘tab_2_col_grp’, — column group name
sequence_no => 1, — the order conflict resolutions to be applied
method => ‘MINIMUM’, — method of resolution
parameter_column_name => ‘col1’ – designated column
);
END;
/


/*************************************************************************************************

Step 04:

Regenerate the replication support for the table

*************************************************************************************************/


BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_2’,
type => ‘TABLE’,
min_communication => TRUE
);
END;
/


/*************************************************************************************************

Step 05:

Resume Replication Activity

*************************************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘REP_USER_MG’ – master group
);
END;
/


1.3 Timestamp method of resolution


The earliest timestamp and latest timestamp methods are variations on the minimum and maximum value methods. To use the timestamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.

Several elements are needed to make timestamp conflict resolution work well:

Synchronized Time Settings Between Computers
Timestamp field and trigger to automatically record timestamp.


In this method of resolution the following conditions that are to be fulfilled for resolution. They are:

(1) Identify the Object for Conflict
(2) Add a new column to hold the TIMESTAMP of the insert or update activity.
(3) Create a trigger to insert TIMESTAMP value as that value is not part of insert statement executed by the users.
(4) Create a Column Group
(5) Designate the method of resolution
(6) Designate the Column for Conflict Resolution.

Target Environments

The latest timestamp conflict resolution method works to converge replication environments with two or more master sites. Because time is always increasing, it is one of the few conflict resolution methods that can guarantee data convergence with multiple master sites. This resolution also works well with any number of materialized views.


/********************************************************************************

Step 01: connect as REPADMIN to Master Site and suspend Replication

********************************************************************************/

connect repadmin/repadmin@rep1


BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘SNAP_1_MG’);
END;
/

/*****************************************************************************************

Step 02: Alter the object by adding timestamp column for the target table

******************************************************************************************/


BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE REP_USER.TAB_1 ADD (timestamp DATE)’
);
END;
/

/*************************************************************************************************

Step 03: regenerate the replication support to the table altered

*************************************************************************************************/


BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_1’,
type => ‘TABLE’,
min_communication => TRUE
);
END;
/

/*************************************************************************************************

Step 04: Create Replication Object ‘TRIGGER’ to insert the timestamp value into the table and add that object to the group which has the effected table in it.

*************************************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘REP_USER_MG’,
type => ‘TRIGGER’,
oname => ‘INSERT_TIME’,
sname => ‘REP_USER’,
ddl_text => ‘CREATE TRIGGER REP_USER.INSERT_TIME
BEFORE
INSERT OR UPDATE ON REP_USER.TAB_1 FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
:NEW.TIMESTAMP := SYSDATE;
END IF;
END;’);
END;
/

/*************************************************************************************************

Step 05: Generate replication support for the trigger

*************************************************************************************************/


BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘INSERT_TIME’,
type => ‘TRIGGER’,
min_communication => TRUE);
END;
/

/************************************************************************************************

Step 06: Create Column Group on the target table

************************************************************************************************/


BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘rep_user’,
oname => ‘tab_1’,
column_group => ‘tab_1_col_grp_1’,
list_of_column_names => ‘col_1,col_2,col_3, TIMESTAMP’);
END;
/

/***********************************************************************************************

Step 07: Decide on the method of conflict resolution and associate with the designated column

Methods: 1. Latest Timestamp
2. Earliest Timestamp

************************************************************************************************/


BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
column_group => ‘TAB_1_COL_GRP_1’,
sequence_no => 1, — the order conflict resolutions to be applied
method => ‘LATEST TIMESTAMP’,
parameter_column_name => ‘TIMESTAMP’
);
END;
/

/***********************************************************************************************

Step 08: Regenerate replication support for that table

*************************************************************************************************/


BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/*************************************************************************************************

Step 09: Resume replication activity

*************************************************************************************************/


BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘SCOTT_MG’);
END;
/


1.4 Additive and Average Method of Resolving Conflicts

Additive

The additive method works with column groups consisting of a single numeric column only. If a conflict arises, instead of choosing one value over another, the difference of the two values is added to the current value.

The additive method adds the difference between the old and new values at the originating site to the current value at the destination site.

current value = current value + (new value – old value)

The additive conflict resolution method provides convergence for any number of master and snapshot sites.

Target Environments:

The additive conflict resolution method is designed to conserve data rather than choose the most appropriate data. This method might be very useful in a financial environment where deposits and withdraws might happen so frequently that conflicts may arise; with a balance, it is important to conserve data rather than choose one value over another (though we might wish that deposits would always be chosen over withdraws).

Average

Like the additive method, the average method works with column groups consisting of a single numeric column only. Instead of adding the difference to the current value, the average method resolves the conflict by computing the average of the current and the new value.

The average conflict resolution method averages the new column value from the originating site with the current value at the destination site.

current value = (current value + new value)/2

The average method cannot guarantee convergence if your replicated environment has more than one master site.

Target Environments

Because the average method cannot guarantee data convergence for replication environments with more than one master site, the average method is ideally implemented in mass deployment environment with a single master site and any number of updateable snapshots.

The average method might be useful for scientific applications that would rather average two values instead of choosing one value over another (for example, to compute the average temperature or weight).
Support Mechanisms

/*************************************************************************************************

Step 01:
Connect to the Master Site and suspend the replication activity

*************************************************************************************************/

CONNECT repadmin/repadmin@REP1

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘REP_USER_MG’);
END;
/

/************************************************************************************************

Step 02: Create a column group on the target table

************************************************************************************************/


BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘rep_user’,
oname => ‘tab_3’,
column_group => ‘tab_3_CG_1’,
list_of_column_names => ‘col_2’);
END;
/

/**********************************************************************************************

Step 03:

Prescribe the method of resolution basing on the designated column in conflict ( this column which has conflict is to be part of the column group created.

*************************************************************************************************/


BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘rep_user’,
oname => ‘tab_3’,
column_group => ‘tab_3_CG_1’,
sequence_no => 1,
method => ‘ADDITIVE’,
parameter_column_name => ‘col_2’);
END;
/

/**********************************************************************************************

Step 04:
Generate the replication support to the Table on which the column group is created and conflict resolution method has been defined and established.
*************************************************************************************************/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘REP_USER’,
oname => ‘TAB_3’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/*************************************************************************************************

Step 05:

Resume the replication activity that was suspended

*************************************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ”REP_USER_MG”);
END;
/

1.5. Setting priorities to resolve the conflicts:

a. Priority set for the column in a table – PRIORITY GROUPS
b. Priority set to a site – PRIORITY SITE

a. Priority groups allow you to assign a priority level to each possible distinct value of a particular column. If Oracle detects a conflict, Oracle updates the table whose “priority” column has a lower value using the data from the table with the higher priority value. Therefore, a higher value means a higher priority.

Target Environments

The priority group conflict resolution method is useful for replicated environments that have been designed for a work-flow environment.

/*************************************************************************************************

Step 01: connect to the master site to set up priority groups

a. connect to REPADMIN at REP1
b. suspend the replication activity before priority group is set.

************************************************************************************************/

CONNECT repadmin/repadmin@REP1

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘REP_USER_MG’
);
END;
/

/************************************************************************************************

Step 02: Identify the column, which helps determine a VALUE, which can take charge of conflict resolution. The column identified should have distinct values. The column JOB in EMP table is one good example to be chosen as a column that has to be one of the member columns of the column group and the values of that column are eligible for being set with priority.

*************************************************************************************************/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘REP_USER’,
oname => ‘TAB_2’,
column_group => ‘TAB_PRIORITY_CG’,
list_of_column_names => ‘COL_1,COL_2,COL_3,COL_4’);
END;
/

/*************************************************************************************************

Step 4

Before you begin assigning a priority value to the values in your table, create a priority group that holds the values you defined.

*************************************************************************************************/


BEGIN
DBMS_REPCAT.DEFINE_PRIORITY_GROUP (
gname => ‘REP_USER_PG’, — name of the group
pgroup => ‘COL_2_PG’, — name column for priority
datatype => ‘VARCHAR2’ – data type of that column value
);
END;
/


/**********************************************************************************************

Step 5

The DBMS_REPCAT.ADD_PRIORITY_DATATYPE procedure is available in several different versions. There is a version for each available data type (NUMBER, VARCHAR2, and so on). Execute this procedure as often as necessary until you have defined a priority value for all possible table values.

SELECT DISTINCT COL_2 FROM TAB_2 gives all the distinct values that are there in that column in the table.
For each value run the procedure by setting the priority value. The higher the number the higher the priority for the value. Hence sort those values in descending order per their priority and then assign the values in the same order and execute procedure once for each value until the list of values exhaust.


**********************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
gname => ‘REP_USER_PG’,
pgroup => ‘COL_2_PG’,
value => ‘PRESIDENT’,
priority => 100
);
END;
/

BEGIN
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
gname => ‘REP_USER_PG’,
pgroup => ‘COL_2_PG’,
value => ‘CEO’,
priority => 80
);
END;
/

BEGIN
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
gname => ‘REP_USER_PG’,
pgroup => ‘COL_2_PG’,
value => ‘GM’,
priority => 60
);
END;
/

BEGIN
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
gname => ‘REP_USER_PG’,
pgroup => ‘COL_2_PG’,
value => ‘AGM’,
priority => 40
);
END;
/

BEGIN
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
gname => ‘REP_USER_PG’,
pgroup => ‘COL_2_PG’,
value => ‘MANAGER’,
priority => 20
);
END;
/

/************************************************************************************

Step 6:

Add the PRIORITY GROUP resolution method to your replicated table. The following example shows that it is the second conflict resolution method for the specified column group (sequence_no parameter).

***********************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘rep_user’,
oname => ‘tab_2’,
column_group => ‘tab_priority_cg’,
sequence_no => 2,
method => ‘PRIORITY GROUP’,
parameter_column_name => ‘col_2’,
priority_group => ‘col_2_pg’
);
END;
/

/************************************************************************************
Step 7

Regenerate replication support for the table that received the conflict resolution method.

***********************************************************************************************/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘REP_USER’,
oname => ‘TAB_2’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/***********************************************************************************************

Step 8:

Resume replication activity by using the RESUME_MASTER_ACTIVITY procedure.

*************************************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘rep_user_grp’);
END;
/


b. Site Priority method of resolving the update conflicts

Site priority is a specialized form of a priority group. Therefore, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving a conflict based on the priority of a field’s value, the conflict is resolved based on the priority of the sites involved.

For example, if REP2 site is assigned with a higher priority value than REP1 and REP3 and a conflict arises between these two sites, then the value from REP2 is used.

/***********************************************************************************************

Step 1

Connect as the replication administrator. The procedures in the following steps must be executed by
the replication administrator.

************************************************************************************************/


CONNECT repadmin/repadmin@REP1

/***********************************************************************************************

Step 2 Before you define a site priority conflict resolution method, quiesce the master group that contains the table to which you want to apply the conflict resolution method. In a single master replication environment, quiescing the master group may not be required. See “Creating Conflict Resolution Methods and Quiescing” for more information.

***********************************************************************************************/

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘REP_USER_GRP’);
END;
/

/***********************************************************************************************


Step 3 Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*************************************************************************************************/

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE REP_USER.TAB_1 ADD (site VARCHAR2(20))’);
END;
/

/**********************************************************************************************

Step 4 Regenerate replication support for the affected object.


************************************************************************************************/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/**********************************************************************************************

Step 5 Create a trigger that records the global name of the site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the site priority method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.
**********************************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘REP_USER_GRP’,
type => ‘TRIGGER’,
oname => ‘insert_site’,
sname => ‘REP_USER’,
ddl_text => ‘CREATE TRIGGER REP_USER.insert_site
BEFORE
INSERT OR UPDATE ON REP_USER.TAB_1 FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
SELECT global_name INTO :NEW.SITE FROM GLOBAL_NAME;
END IF;
END;’);
END;
/

/**********************************************************************************************

Step 6 Make sure the new column is part of the column group for which your site priority conflict resolution mechanism is used. Use the ADD_GROUPED_COLUMN procedure to add this column to an existing column group. If you do not already have a column group, then you can create a new column group using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.
**********************************************************************************************/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
column_group => ‘TAB_1_sitepriority_cg’,
list_of_column_names => ‘COL_1,COL_2,COL_3’);
END;
/

/***************************************************************************************

Step 7 Before assigning a site priority value to the sites in your replicated environment, create a site priority group that holds the values you defined.
***********************************************************************************************/

BEGIN
DBMS_REPCAT.DEFINE_SITE_PRIORITY (
gname => ‘REP_USER_GRP’,
name => ‘TAB_1_sitepriority_pg’);
END;
/

/**********************************************************************************************

Step 8 Define the priority value for each of the sites in your replication environment using the DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure. Execute this procedure as often as necessary until you have defined a site priority value for each of the sites in our replication environment.
*************************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
gname => ‘REP_USER_GRP’,
name => ‘TAB_1_sitepriority_pg’,
site => ‘REP1’,
priority => 100);
END;
/

BEGIN
DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
gname => ‘REP_USER_GRP’,
name => ‘TAB_1_sitepriority_pg’,
site => ‘REP_2’,
priority => 50);
END;
/

BEGIN
DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
gname => ‘REP_USER_GRP’,
name => ‘TAB_1_sitepriority_pg’,
site => ‘REP_3’,
priority => 25);
END;
/


/*********************************************************************************************

Step 9 After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*********************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
column_group => ‘TAB_1_sitepriority_cg’,
sequence_no => 1,
method => ‘SITE PRIORITY’,
parameter_column_name => ‘site’,
priority_group => ‘TAB_1_sitepriority_pg’);
END;
/

/**********************************************************************************************

Step 10 Regenerate replication support for the table that received the conflict resolution method.
**********************************************************************************************/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘REP_USER’,
oname => ‘TAB_1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/*********************************************************************************************

Step 11 Resume replication activity by using the RESUME_MASTER_ACTIVITY procedure.

***********************************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘REP_USER_GRP’);
END;
/


The following table gives a clear picture of the replication environment for update conflict resolution method setup at the master sites.

Backup Method – This means that there shall be another method of resolution in case of tie with timestamp method. For instance, when you use a timestamp conflict resolution method, you should designate a backup (another) method, such as site priority, to be called if two sites have the same timestamp.

Table listing Update Conflict Resolution Methods (Oracle Pre-built Mechanisms), Target Environment and Support Mechanism to be developed.

Uniqueness Conflict

This type of conflicts is handled by Oracle in three different ways.

(1) By appending the global site name of the originating site to the column value from the originating site.
In this method the table structure is altered and additional column with site name is added and the uniqueness is achieved by adding the site name to already defined unique columns. This is not a resolution of the conflict. The append site name method works by appending the global database name of the site originating the transaction to the replicated column value that is generating a ‘DUP_VAL_ON_INDEX’ exception. Although this method allows the column to be inserted or updated without violating a unique integrity constraint, it does not provide any form of convergence between multiple master sites. The resulting discrepancies must be manually resolved; therefore, this method is meant to be used with some form of a notification facility.
(2) By appending generated sequence number to the column value from the originating site.
This is variance for append site name method resolution.
The append sequence methods works by appending a generated sequence number to the column value that is generating a DUP_VAL_ON_INDEX exception. Although this method allows the column to be inserted or updated without violating a unique integrity constraint, it does not provide any form of convergence between multiple master sites. The resulting discrepancies must be manually resolved; therefore, this method is meant to be used with some form of a notification facility.
(3) By discarding the row value from the originating site.
The discard uniqueness conflict resolution method resolves uniqueness conflicts by simply discarding the row from the originating site that caused the error. This method does not guarantee convergence with multiple master sites and should be used with a notification facility.
In a replication environment, there are situations where a conflict on a unique constraint arises, often resulting from an insert. If business rules allow to delete the duplicate row, then a define a resolution method with Oracle’s pre-built conflict resolution methods other wise a user defined method of handling the conflict is to be evolved meeting the requirements of business rules.

More often, the conflicting value is modified so that it no longer violates the unique constraint. Modifying the conflicting value ensures that there is no loss of important data. Oracle’s pre-built uniqueness conflict resolution method can make the conflicting value unique by appending a site name or a sequence number to the value. This method of Oracle Pre-built Unique Conflict Resolution retains the duplicate rows by making them unique by adding the site name as part of the Primary Key.

An additional component that accompanies the uniqueness conflict resolution method is a notification facility. The conflicting information is modified by Oracle so that it can be inserted into the table, but you should be notified so that you can analyze the conflict to determine whether the record should be deleted, or the data merged into another record, or a completely new value is defined for the conflicting data.

A uniqueness conflict resolution method detects and resolves conflicts encountered on columns with a UNIQUE constraint. The example in this section uses the employees’ table in the REP_USER schema, which has the unique constraint TAB_1_COL_1_uk on the COL_1 column.


/*************************************************************************************************
Step 1

Connect as the replication administrator.

*************************************************************************************************/

CONNECT repadmin/repadmin@rep1


/*************************************************************************************************

Step 2

Before you define a uniqueness conflict resolution method, quiesce the master
group that contains the table to which you want to apply the conflict resolution method.

*************************************************************************************************/


BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘rep_user_gr’);
END;
/

/*************************************************************************************************

Step 3
Create a table that stores the messages received from your notification facility.
In this example, the table name is conf_report.

*************************************************************************************************/
BEGIN
DBMS_REPCAT.EXECUTE_DDL (
gname => ‘rep_user_gr’,
ddl_text => ‘CREATE TABLE rep_user.conf_report (
line NUMBER(2),
txt VARCHAR2(80),
timestamp DATE,
table_name VARCHAR2(30),
table_owner VARCHAR2(30),
conflict_type VARCHAR2(7))’
);
END;
/


/*************************************************************************************************

Step 4

Connect as the owner of the table you created in Step 3.

*************************************************************************************************/

CONNECT rep_user/rep_user@rep1

/*************************************************************************************************

Step 5

Create a package that sends a notification to the conf_report table when a conflict
is detected. In this example, the package name is NOTIFY.


*************************************************************************************************/

CREATE OR REPLACE PACKAGE notify AS
FUNCTION tab_1_unique_violation (COL_1 IN OUT VARCHAR2, discard_new_values IN OUT BOOLEAN)
RETURN BOOLEAN;
END notify;
/

CREATE OR REPLACE PACKAGE BODY notify AS

TYPE message_table IS TABLE OF VARCHAR2 (80) INDEX BY BINARY_INTEGER;

PROCEDURE report_conflict
(conflict_report IN MESSAGE_TABLE,
report_length IN NUMBER,
conflict_time IN DATE,
conflict_table IN VARCHAR2,
table_owner IN VARCHAR2,
conflict_type IN VARCHAR2)
IS
BEGIN
FOR idx IN 1..report_length LOOP
BEGIN
INSERT INTO rep_user.conf_report
(line, txt, timestamp, table_name, table_owner, conflict_type)
VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time,
conflict_table, table_owner, conflict_type);
EXCEPTION WHEN others THEN NULL;
END;
END LOOP;
END report_conflict;

FUNCTION tab_1_unique_violation (COL_1 IN OUT VARCHAR2, discard_new_values IN OUT BOOLEAN)
RETURN BOOLEAN IS

local_node VARCHAR2(128);
conf_report MESSAGE_TABLE;
conf_time DATE := SYSDATE;

BEGIN
BEGIN
SELECT global_name INTO local_node FROM global_name;
EXCEPTION WHEN others THEN local_node := ‘?’;
END;
conf_report(1) := ‘UNIQUENESS CONFLICT DETECTED IN tab_1 ON ‘ || TO_CHAR(conf_time, ‘MM-DD-YYYY HH24:MI:SS’);
conf_report(2) := ‘ AT NODE ‘ || local_node;
conf_report(3) := ‘ATTEMPTING TO RESOLVE CONFLICT USING’ ||’ APPEND SITE NAME METHOD’;
conf_report(4) := ‘COL_1: ‘ || COL_1;
conf_report(5) := NULL;
report_conflict(conf_report, 5, conf_time, ‘tab_1’, ‘rep_user’, ‘UNIQUE’);
discard_new_values := FALSE;
RETURN FALSE;
END tab_1_unique_violation;
END notify;
/

/*************************************************************************************************

Step 6 Connect as the replication administrator.

*************************************************************************************************/

CONNECT repadmin/repadmin@rep1

/************************************************************************************************

Step 7

Replicate the package you created in Step 5 to all of the master sites in your replication
environment, which ensures that the notification facility is available at all master sites.

*************************************************************************************************/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘rep_user_gr’,
type => ‘PACKAGE’,
oname => ‘notify’,
sname => ‘rep_user’);
END;
/


BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘rep_user_gr’,
type => ‘PACKAGE BODY’,
oname => ‘notify’,
sname => ‘rep_user’);
END;
/

/*************************************************************************************************

Step 8
Add the notification facility as one of your conflict resolution methods, even though it only notifies of a conflict. The following example demonstrates adding the notification facility as a USER FUNCTION
*************************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_UNIQUE_RESOLUTION (
sname => ‘rep_user’,
oname => ‘tab_1’,
constraint_name => ‘tab_1_COL_1_uk’,
sequence_no => 1,
method => ‘USER FUNCTION’,
comment => ‘Notify DBA’,
parameter_column_name => ‘COL_1’,
function_name => ‘rep_user.notify.tab_1_unique_violation’);
END;
/


/************************************************************************************************

Step 9

Add the actual conflict resolution method to your table. The following example demonstrates adding the APPEND SITE NAME uniqueness conflict resolution method to your replicated table.

*************************************************************************************************/

BEGIN
DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
sname => ‘rep_user’,
oname => ‘tab_1’,
constraint_name => ‘tab_1_COL_1_uk’,
sequence_no => 2,
method => ‘APPEND SITE NAME’,
parameter_column_name => ‘COL_1’);
END;
/


/*************************************************************************************************

Step 10

Regenerate replication support for the table that received the conflict resolution methods.

************************************************************************************************/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘rep_user’,
oname => ‘tab_1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

/***********************************************************************************************

Step 11

Resume replication activity by using the RESUME_MASTER_ACTIVITY procedure.


************************************************************************************************/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘rep_user_gr’);
END;
/


Resolving Delete Conflicts

Delete conflicts occur when you successfully delete from the local site, but the associated row cannot be found at the remote site (for example, because it had been updated). For delete conflicts, the function should accept old column values in IN OUT mode for the entire row. The final parameter to the conflict resolution method should be a BOOLEAN flag.
If the conflict resolution method can resolve the conflict, it modifies the old column values so that Oracle can delete the current row that matches all old column values. Your function should set the Boolean flag to TRUE if it wants to discard these column values, and FALSE otherwise.

If you perform a delete at the local site and an update at the remote site, the remote site detects the delete conflict, but the local site detects an unresolvable update conflict. This type of conflict cannot be handled automatically. The conflict raises a NO_DATA_FOUND exception and Oracle logs the transaction as an error transaction.

Designing a mechanism to properly handle these types of update/delete conflicts is difficult. It is far easier to avoid these types of conflicts entirely, by simply “marking” deleted rows, and then purging them using procedural replication.

(Source – Oracle8i Replication Management API Reference)

Trouble Shooting

In general the problems are centered on:

(1) Working of database links
(2) Master Sites
(3) Diagnosing Problems with the Deferred Transaction Queue
(4) Diagnosing Problems with Materialized Views
(5) Refresh Problems
(6) RBS Problems
(7) Problems associated with Temp Segments

Working of database links

If the DB Link is working properly drop the existing and recreate the DB Link. While recreating make sure that:

(1) The database link name is the same as the global name of the target database.

The way to make sure that you are using the correct global name of the target database

(a) Connect to the target database from SQLPLUS or SQL WORKSHEET or o any other tool to connect.
(b) Issue the following SQL query against the database
select * from global_name;
Using the created link issue any SQL statement that makes sure that are logged to and querying from the target database.

(2) The scheduled interval is what you want. If the scheduled interval is less than the time required for the process to complete there will be overlaps and resultant failures and hanging processes. The interval should at the minimum match the execution time.

Master Sites

Oracle Docs identify the following problems with Master Sites:

Replicated Objects Not Created at New Master Site

When a new master site is added ensure that the new master site is having a DB link from Originating Master Site and like wise Target Master Site also has a DB Link and is active. All the administrative requests are completed successfully.

DDL Changes Not Propagated to Master Site

When a master group is created or objects are added, those changes are to be propagated to other Master Sites. To ensure that all the changes are propagated to all the master sites look for the pending administrative requests at every master site. In case they are in pending apply them successfully.

DML Changes Not Asynchronously Propagated to Other Sites

If the changes are not propagated even at the scheduled PUSH, test the db link and then manually push the deferred transactions. In case still problems access ‘DEFERROR’ for the errors are do act accordingly to rectify those errors.

DML Cannot be applied to Replicated Table

If bulk updates are applied there may not be any problems except the RBS and TEMP segment extending problems, incase the max extents are set to the RBS segments even though there is abundant space available. For the TEMP there may not space. In such cases alter the RBS segments setting ‘MAXEXTENTS’ to unlimited. In case of TEMP tablespace look for the errors and investigate and also scan through the ALERT LOG file for the instance and identify the problems and them trouble soot them applying suitable remedial measures.

Bulk Updates and Constraint Violations

When BULK Updates take place there is a problem of unique key violations. In Such cases set an order for the updates and then apply the updates in that order.

Recreating a Replicated Object

If you add an object such as a package, procedure, or view to a master group, then the status of the object must be valid. If the status of an object is invalid, then recompile the object or drop and re-create the object before adding it to a master group. Check the DBA_REPOBJECT data dictionary view for the status of replication objects.


Unable to Generate Replication Support for a Table

When you generate replication support for a table, Oracle activates an internal trigger at the local site. EXECUTE privileges for most of the packages involved with replication, such as DBMS_REPCAT and DBMS_DEFER, need to be granted to replication administrators and users that own replicated objects. The Replication Management tool’s Setup Wizard and the DBMS_REPCAT_ADMIN package both perform the grants needed by the replication administrators for many typical replication scenarios. When the owner of a replicated object is not a replication administrator, however, you must explicitly grant EXECUTE privilege on DBMS_DEFER to the object owner.

Problems with Replicated Procedures or Triggers

If there are on the tables that are part of the replicated group of objects, the replication is to switched off at the beginning of the trigger and switched on at the end of the trigger.

The following may give an idea as to how to do that:

CREATE TRIGGER <trigger_name>
BEFORE INSERT OR UPDATE
on <table_name>
FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE <> TRUE
< trigger body >
END IF;
END;

Problems With ON DELETE CASCADE and Integrity Constraints

Go through this example to understand the issue that comes up.

All the FK constraints have the condition ‘on delete cascade’. When deletes are to be propagated they may not successful and may result in this oracle error message.
ORA-02292: integrity constraint (SCOTT.FK_CB) violated – child record found
If you encounter this error, create triggers that are aware of replication to perform this functionality, instead of ON DELETE CASCADE.

Examples:

CREATE OR REPLACE TRIGGER SCOTT.on_del_tab_1
after delete on TEST_DELETE_ON_CASCADE_1
for each row
begin
delete from TEST_DELETE_ON_CASCADE_2
where col_1 = :old.col_1;
end;
/

CREATE OR REPLACE TRIGGER SCOTT.on_del_tab_2
after delete on TEST_DELETE_ON_CASCADE_2
for each row
begin
delete from TEST_DELETE_ON_CASCADE_3
where col_1 = :old.col_1;
end;
/

Diagnosing Problems with the Deferred Transaction Queue

1. Check whether scheduled links are properly working or not.
2. Synchronous replication uses two phase commit. There are two views to look at to find out pending transactions. dba_2pc_pending and dba_2pc_neighbors view give the details of the pending transactions. The user application that commits a distributed transaction is informed of a problem by one of the following error messages:
ORA-02050: transaction ID rolled back,
some remote dbs may be in-doubt
ORA-02051: transaction ID committed,
some remote dbs may be in-doubt
ORA-02054: transaction ID in-doubt

No action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a network or system failure. The automatic recovery features of Oracle transparently complete any in-doubt transaction so that the same outcome occurs on all nodes of a session tree (that is, all commit or all roll back) after the network or system failure is resolved.

In other cases a commit can be forced or rollback is done.

3. Incorrect catalog views:
Having the wrong view definitions can lead to erroneous deferred transaction behavior. The DEFCALLDEST and DEFTRANDEST views are defined differently in catdefer.sql and catrepc.sql. The definitions in catrepc.sql should be used whenever replication is used. If catdefer.sql is ever (re)loaded, then ensure that the view definitions in catrepc.sql are subsequently loaded.


Diagnosing Problems with Materialized Views
1.Problems while creating objects at the materialized view /snapshot site can be identified by going through the check list given below:
a. For an updateable materialized view, check that the associated master table or master materialized view has a materialized view log or snapshot log created on it.
b. Make sure that you have the necessary privileges to create the object. For a materialized view, you need SELECT privilege on the master table or master materialized view and its materialized view log.
c. If you are trying to add an existing materialized view to a materialized view group, then try re-creating the materialized view when you add it to the group.
d. If you are trying to create a fast refresh primary key or sub-query materialized view, then make sure that the materialized view log on the master table or master materialized view logs primary keys.
e. If you are trying to create a fast refresh rowid materialized view, then make sure that the materialized view log on the master table logs rowids.
f. Check if the materialized view log has the required columns added for sub-query materialized views.
g. Check if the materialized view log exists for all tables that are involved in a fast refresh materialized view. If the materialized view contains a sub-query, then each table referenced in the sub-query should have a materialized view log.
Refresh Problems
The following general problems are associated with the failure of refreshes:
1. The lack of a job queue process at the materialized view database
2. Network Problem which may or may not require manual intervention by Network Administrators or a problem emanated from the providers.
3. Instance failure or database shutdown problems caused by server shutdowns or other reasons which may require manual intervention to bring back the database to a normal state.
4. If once a refresh fails Oracle tries to refresh it after a gap of 1 minute, then after 2 minutes and then after 4 minutes. Thus the refresh attempt gaps go on doubling the time gap. So the numbers of failures also count. The maximum number for retrials by Oracle is 16. It cannot exceed that limit.
5. Oracle does not allow the retry interval to exceed the refresh interval itself.
6. If after 16 attempts to refresh a refresh group Oracle continues to encounter errors, then Oracle considers the group broken. The General page of the Refresh Group property sheet in Schema Manager indicates when a refresh group is broken. Query the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN data dictionary views to see the current status of a refresh group.
The errors causing Oracle to consider a materialized view refresh group broken are recorded in a trace file. After you correct the problems preventing a refresh group from refreshing successfully, you must refresh the group manually. Oracle then resets the broken flag so that automatic refreshes can happen again. The name of the materialized view trace file is of the form jn, where n is operating system specific.

Fast Refresh Errors at New Materialized View Sites

In some cases, a materialized view log for a master table or master materialized view might be purged during the creation of a materialized view at a new materialized view site. When this happens, you may encounter the following errors:

ORA-12004 REFRESH FAST cannot be used for materialized view materialized_view_name
ORA-12034 materialized view log on materialized_view_name younger than last refresh

Materialized Views Continually Refreshing

If you encounter a situation where Oracle continually refreshes a group of materialized views, then check the group’s refresh interval. Oracle evaluates a group’s automatic refresh interval before starting the refresh. If a group’s refresh interval is less than the amount of time it takes to refresh all materialized views in the group, then Oracle continually starts a group refresh each time the job queue process checks the queue of outstanding jobs.

Materialized View Logs Growing Too Large

If a materialized view log at a master site or master materialized view site is growing too large, then check to see whether a network or site failure has prevented the master site or master materialized view site from becoming aware that a materialized view has been dropped. You may need to purge part of the materialized view log or un-register the unused materialized view site.

Advanced Troubleshooting of Refresh Problems

If you have a problem refreshing a materialized view, then try the following:

1. Check the NEXT_DATE value in the DBA_REFRESH_CHILDREN view to determine if the refresh has been scheduled.
If the refresh interval has passed, then check the DBA_REFRESH view for the associated job number for the materialized view refresh and then diagnose the problem with job queues.

2. Check if there are job queue process running. Check the JOB_QUEUE_PROCESSES initialization parameter, query the DBA_JOBS_RUNNING view, and use your operating system to check if the job queue processes are still running.

3. You may also encounter an error if you attempt to define a master detail relationship between two materialized views. You should define master detail relationships only on the master tables by using declarative referential integrity constraints. The related materialized views should then be placed in the same refresh group to preserve this relationship. However, you can define deferred (or deferrable) constraints on materialized views.

4. If there are any outstanding conflicts recorded at the master site or master materialized view site for the materialized views, then you can only refresh the materialized views by setting the parameter REFRESH_AFTER_ERRORS to true. This parameter can be set when you create or alter a materialized view refresh group. There is a corresponding parameter for the Replication Management tool’s property sheets.

5. Materialized views in the same refresh groups have their rows updated in a single transaction. Such a transaction can be very large, requiring either a large rollback segment at the materialized view site, with the rollback segment specified to be used during refresh, or more frequent refreshes to reduce the transaction size.

6. If Oracle error ORA-12004 occurs, then the master site or master materialized view site may have run out of rollback segments when trying to maintain the materialized view log, or the materialized view log may be out of date. For example, the materialized view log may have been purged or re-created.

7. Complete refreshes of a single materialized view internally use the TRUNCATE feature to increase speed and reduce rollback segment requirements. However, until the materialized view refresh is complete, users may temporarily see no data in the materialized view. Refreshes of multiple materialized views (for example, refresh groups) do not use the TRUNCATE feature.

8. Reorganization of the master table (for example, to reclaim system resources) should TRUNCATE the master table to force rowid-materialized views to do complete refreshes. Otherwise, the materialized views have incorrect references to master table rowids. You use the BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION procedures in the DBMS_MVIEW package to reorganize a master table. See the Oracle9i Replication Management API Reference for more information.

9. If while refreshing you see an ORA-942 (table or view does not exist), then check your database links and make sure you still have the required privileges on the master table or master materialized view and the materialized view log.

10. If a fast refresh was succeeding but then fails, then check whether:

11. The materialized view log was truncated, purged, or dropped. You still have the required privileges on the materialized view log
12. If a force refresh takes an inordinately long time, then check if the materialized view log used by the refresh has been dropped.

13. If the materialized view was created with BUILD DEFERRED, and its first fast refresh fails, then make sure a previous complete refresh was done successfully before checking for other problems.

Rollback Segments

When you have the messages that unable to extend rollback segments number so and so at the session and also logged to alert log file of the SID check the following:

1. what is the max limit set for the tablespace to grow. If there is a set limit for growth, alter the database datafile to set autoextend on.

alter database datafile ‘ <data file name with path ‘
autoextend on NEXT 10M;

2. space availability on the disk. If there is no space available add another datafile to the RBS tablespace which can be located on another disk which has sufficient space.

alter tablespace <tablespace name>
add datafile ‘<file name with path’> size 1000M
autoextend on NEXT 10M;
( prescribe all the storage parameters deem necessary)

3. check the storage parameters for the RBS created in the RBS tablespace. If maxetents are set to a certain number then set that limit to unlimited extents.

alter rollback segment <segment name>
storage(maxextents unlimited);

4. some times this kind of message can be seen
ORA-01552: cannot use system rollback segment for non-system tablespace

In that case issue SQL statement as a DBA

select segment_name,status from dba_rollback_segs

if any of the rollback segments is off line bring them online by issuing the following command. This is to be done at the target site as well as master site.

alter rollback segment < segment name> online;

Temporary Segments

If you receive and error stating that Oracle is unable to initialize the extent in the temporary tablespace when you try to instantiate a deployment template offline, then you may need to adjust the datafile for the temporary database so that it auto extends.
For example, issue the following statement to adjust the datafile:

ALTER DATABASE TEMPFILE ‘<filename with path>’
AUTOEXTEND ON
NEXT 10M;