Zurück

Oracle Replication Survival Guide

Martin Zahn, Akadia AG, 25.09.2005

Some Material in this Guide is overtaken from the official Oracle Replication Guide

Content

Overview

Read-Only Materialized Views
    Example

Multi-Master Replication
   Overview
   Concepts and Terminology
   Replication Users

Example for a Multimaster Site
   Create SCOTT Schemas
  Create Password Files
   Setup Oracle Network
   Setup Users on Master CEL1.WORLD and CEL2.WORLD
   Create Master Replication Group on CEL1.WORLD and CEL2.WORLD
   Setup Users on Materialized View Site REP1.WORLD and REP2.WORLD
   Create Materialized View Group on REP1.WORLD and REP2.WORLD

Monitoring Master Sites
 Monitoring the Deferred Transactions Queue
Monitoring Purges of Successfully Propagated Transactions
Monitoring the Error Queue
Monitoring Performance in a Replication Environment

Removing the Replication from the Database

Overview

If you're going to realize the full potential of Oracle's advanced replication facilities and simultaneously avoid the pitfalls, you need to understand the architecture on which they are based.

Note that replicating data is fundamentally different from distributing data. When data is distributed, it may be accessed transparently from multiple locations, but a given table exists in only one location, and that location is responsible for its security and integrity. Replicated data, on the other hand, resides at multiple locations, each of which shares in its maintenance. Data replication implies an increased level of complexity because it introduces issues such as data synchronization and latency. This complexity is the price to pay for continuous operations when a remote data source is unavailable.

 

Types of Replication

Oracle's four basic types of replication. Starting from Oracle9 Read-only Snapshots are so called Read-Only Materialized Views.

Replication Type

Description

Read-only materialized views A master table is copied to one or more databases. Changes in the master table are reflected in the snapshot tables whenever the snapshot refreshes. The snapshot site determines the frequency of the refreshes; data is pulled.
Updateable materialized views Similar to read-only snapshots, except that the snapshot sites are able to modify the data and send their changes back to the master. The snapshot site determines the frequency of the refreshes and the frequency with which updates are sent back to the master.
Multi-master replication A table is copied to one or more databases, and each database has the ability to insert, update, or delete records from it. Modifications are pushed to the other database at an interval that the DBA sets for each replication group. The highest theoretical frequency is once per second.
Procedural replication A call to a packaged procedure or function is replicated to one or more databases.

As you can see, these modes of replication are quite different, and each is suited for specific kinds of uses. A single environment can utilize all of these methods; they are not mutually exclusive.

Read-Only Materialized Views

Read-only snapshot sites can be used to provide local access to remote master tables. Having a local snapshots of the data improves query response time. Updates can only be issued against the master table.

In a basic configuration, materialized views can provide read-only access to the table data that originates from a master site or master materialized view site. Applications can query data from read-only materialized views to avoid network access to the master site, regardless of network availability. However, applications throughout the system must access data at the master site to perform data manipulation language changes (DML). The master tables and master materialized views of read-only materialized views do not need to belong to a replication group.

Read-only materialized views provide the following benefits:

  • Eliminate the possibility of conflicts because they cannot be updated.

  • Support complex materialized views. Examples of complex materialized views are materialized views that contain set operations or a CONNECT BY clause.

Example

In order to create one (or many) read-only snapshot of the master Oracle database tables, the following steps are necessary.

  • Master Site: Oracle 10.1.0.3, Solaris 9, SID=QUO3

  • Snapshot Site: Oracle 8.1.7.4, Solaris 8, SID=DIA1

Check TNSNAMES.ORA

On Master (Host=quorum)

DIA1.WORLD =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1521))
     (CONNECT_DATA =
        (SERVICE_NAME = DIA1)
        (INSTANCE_NAME = DIA1)
        (SRVR = DEDICATED)
     )
  )

DIA1 =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1521))
     (CONNECT_DATA =
        (SERVICE_NAME = DIA1)
        (INSTANCE_NAME = DIA1)
        (SRVR = DEDICATED)
     )
  )

On Snapshot (Host=diamond)

QUO3.WORLD =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT = 1523))
     (CONNECT_DATA =
        (SERVICE_NAME = QUO3)
        (INSTANCE_NAME = QUO3)
        (SRVR = DEDICATED)
     )
  )

QUO3 =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT = 1523))
     (CONNECT_DATA =
        (SERVICE_NAME = QUO3)
        (INSTANCE_NAME = QUO3)
        (SRVR = DEDICATED)
     )
  )

Create DB Links

On Master (Host=quorum)

sqlplus scott/tiger@QUO3
CREATE DATABASE LINK DIA1 CONNECT TO scott IDENTIFIED BY tiger using 'DIA1';
 Database link created.

On Snapshot (Host=diamond)

sqlplus scott/tiger@DIA1
CREATE DATABASE LINK QUO3 CONNECT TO scott IDENTIFIED BY tiger using 'QUO3';
Database link created.

desc emp@QUO3;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Create Snapshot Log

For each table, which should be replicated, a SNAPSHOT LOG must be created (as user, who owns the table).

A materialized view log (Snapshot Log) is a table at the materialized view's master site or master materialized view site that records all of the DML changes to the master table or master materialized view. A materialized view log is associated with a single master table or master materialized view, and each of those has only one materialized view log, regardless of how many materialized views refresh from the master. A fast refresh of a materialized view is possible only if the materialized view's master has a materialized view log. When a materialized view is fast refreshed, entries in the materialized view's associated materialized view log that have appeared since the materialized view was last refreshed are applied to the materialized view.

On Master (Host=quorum)

sqlplus scott/tiger@QUO3
DROP SNAPSHOT LOG ON emp;

CREATE SNAPSHOT LOG ON emp
     TABLESPACE tab
     STORAGE
     (INITIAL       200K
      NEXT          200K
      MINEXTENTS    1
      MAXEXTENTS    UNLIMITED
      PCTINCREASE   0);

Materialized view log created.

Create Snapshot

A snapshot contains on the remote site the data of the master table. All data changes are reflected in the snapshot after a refresh of the snapshot (either triggered manually or automatically).

On Snapshot (Host=diamond)

sqlplus scott/tiger@DIA1

CREATE SNAPSHOT emp
 PCTFREE 15
 STORAGE
 (INITIAL      200K
   NEXT        200K
   PCTINCREASE 0)
   TABLESPACE tab
 USING INDEX
   PCTFREE 0
   STORAGE
   (INITIAL     200K
    NEXT        200K
    PCTINCREASE 0)
    TABLESPACE idx
 REFRESH FORCE
 START WITH SYSDATE NEXT SYSDATE+(1/1440) /* 60 SECONDS */
 AS SELECT * FROM emp@QUO3;

Materialized view created.

Create Synonym

On Snapshot (Host=diamond)

sqlplus scott/tiger@DIA1

CREATE PUBLIC SYNONYM emp FOR scott.emp;
 Synonym created.

Now, you can access the table emp locally which will be automatically refreshed every 60 sec.

Refresh the Snapshot

The snapshot on the remote site must be refreshed regularily. This can be done bullet either by hand after a substantial change on the master site or in regular intervalls.

Manual Refresh

On Snapshot (Host=diamond)

sqlplus scott/tiger@DIA1

execute dbms_snapshot.refresh('scott.emp','F');
 PL/SQL procedure successfully completed.

The first parameter is a list of snapshots to be refreshed. The second describes the method, F stands for FAST refresh (only changes in the master table are propagated, C stands for complete refresh.

There is also a dbms_snapshot.refresh_all routine. It requires some more privileges.

execute dbms_snapshot.refresh_all;

Automatic Refresh

Automatic refresh is realized by parameters for a refresh group or by the definition of the snapshot. In order to run periodoc jobs in the database (as automatic refresh jobs), the ability to run SNP background jobs must be given.

Especially, in the file init<instance>.ora , located the parameter

job_queue_processes = 1

must be included (the default is 0) and the database must be restarted! This parameter allows background processes to be executed in one job queue.

Check Automatic Refresh

You may use the following query to check, if automatically refresh works.

SELECT SUBSTR(job,1,4) "Job", SUBSTR(log_user,1,5) "User",
  SUBSTR(schema_user,1,5) "Schema",
  SUBSTR(TO_CHAR(last_date,'DD.MM.YYYY HH24:MI'),1,16) "Last Date",
  SUBSTR(TO_CHAR(next_date,'DD.MM.YYYY HH24:MI'),1,16) "Next Date",
  SUBSTR(broken,1,2) "B", SUBSTR(failures,1,6) "Failed",
  SUBSTR(what,1,20) "Command"
   FROM dba_jobs;

Job  User  Schem Last Date        Next Date        B Failed Command
---- ----- ----- ---------------- ---------------- - ------ --------------------
2    SCOTT SCOTT 06.09.2005 15:05 06.09.2005 15:06 N 0      dbms_refresh.refresh

Drop Snapshots and Snapshot Logs

On Master (Host=quorum)

sqlplus scott/tiger@QUO3
spool drop_snapshot_logs.sql

select   'PROMPT Dropping Snapshot Log for '||MASTER||chr(10)
       ||'drop snapshot log on '||MASTER||';'
from   USER_SNAPSHOT_LOGS;

spool off
@drop_snapshot_logs.sql

PROMPT Snapshot Logs Dropped

On Snapshot (Host=diamond)

sqlplus scott/tiger@DIA1
spool drop_snapshots.sql

select   'PROMPT Dropping Snapshot for '||NAME||chr(10)
       ||'drop snapshot '||NAME||';'
from   USER_SNAPSHOTS;

@drop_snapshots.sql

PROMPT Snapshots dropped

Refresh Groups

If the snapshot must obey certain integrity rules, like referential integrity, then the refresh of the snapshot tables must be synchronized. This is achieved by creating refresh groups.

dbms_refresh.make(
     name             => 'MY_GRP',
     list             => 'emp,dept,bonus,salgrade',
     next_date        =>  SYSDATE,
     interval         => 'SYSDATE + (1/1440)', /* 60 seconds */
     implicit_destroy => TRUE,    /* delete the group if substracting
                                     the last member */
     lax              => TRUE,    /* delete from other group if already
                                     existing in a group */
     rollback_seg     => 'RB06'
);
commit;

Multi-Master Replication

Overview

You have already seen how to create and use read-only materialized views. They offer the powerful ability to replicate data in tables across separate databases. With multi-master replication, you can replicate more than just database tables. You can replicate:

  • Tables
  • Indexes
  • Procedures, functions, and triggers
  • Packages
  • User-defined types (Oracle9i)

As always, there are plusses and minuses to using multi-master replication.
The positive benefits of MMR include the following:

  • Replicates more objects, including user-defined objects.
     
  • Updates or modifies the objects being replicated. Adding a column to a table at the master definition site can be replicated to other master sites.
     
  • Replicates with any number of other databases. Any master site can replicate with other master sites, updatable Mview sites, and read-only Mview sites.

However, there are some downsides such as:

  • Potentially large network bandwidth requirements. Not only does multi-master push and pull changes between sites, it also sends acknowledgements and quite a bit of administrative data.
     
  • Reduced Performance. Complexity and robustness comes at a price. MMR involves the use of triggers and procedures, and this can result in a database performance hit. Depending on how much data you are replicating, this performance hit can be substantial.
     
  • Significant increases in administration requirements. When problems appear in the database, the DBA must insure that replication is not the cause or that the cause is not replicated to other databases. Database performance tuning and problem resolution becomes more complicated by an order of magnitude.
     
  • Database changes require additional planning. Rolling out a new version of an application can be much more difficult. Each new version will require revisiting the design of the replication.

The considerations above should reinforce not to implement a higher level of replication than you need. Multi-master replication is powerful, and it is complicated to create and monitor the replication environment.

Concepts and Terminology

Deferred Transaction

A deferred transaction is a transaction that is queued for delivery to one or more remote databases. If you use multi-master replication with asynchronous propagation, Oracle creates deferred transactions for all local DML activity against the replicated tables.

Replication Group

A replication group is a collection of one or more replicated objects (typically tables) that are administrated together. Very generally speaking, the objects in a given replication group are logically related; for example, they are often the set of objects that a given application uses. A given replication group can contain objects from multiple schema, and a given schema can have objects in more than one replication group. However, any given object can be in only one replication group.

The most significant property of replication groups is that all objects in a given group are quiesced together. That is, DML activity is enabled and disabled for all group members simultaneously.

Quiescence

Quiescence is the act of suspending DML activity for all tables in a given replication group. This is required in order to perform certain administrative tasks on objects in a replication group, such as altering a table. The Oracle built-in package procedure call that quiesces a replication group is DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY.

Master Definition Site

The master definition site of a replication group is the database instance from which the group is administered. This site is usually, but not necessarily, the site at which the replication group was originally created.

Master Site

A master site is a site that is participating in one or more replication groups but is not the master definition site.

Replication Support

Replication support refers to the packages and triggers that Oracle creates in order to propagate changes to replicated objects, to detect and resolve conflicts, and so on.

Propagation and Conflict

When Oracle propagates an update to destination tables, it expects the current data for the row at the destination to match the data at the originating site prior to the update. If the data is not the same, an update conflict results. Similarly, if an insert fails because of a primary key violation (i.e., a unique constraint violation) the result is a uniqueness conflict or violation. And, if the target row of a delete does not exist at the destination site, a delete conflict results.

Unless you are propagating changes among master sites synchronously, there is a delay between the time a DML change is applied at the originating database and the time the transaction reaches the destination databases. This lag is known as propagation latency.

Replication Users

  • The Administrator maintains the master group, adds or removes objects, etc.
  • The Propagator is responsible for pushing items in the deferred transaction queue to all other master sites.
  • The Receiver takes items that have arrived in the deferred transaction queue and applies them to the local objects.

Oracle recommends that you use the Replication Administrator to perform all three tasks when establishing your replication environment. For additional security, you can establish a separate user as the receiver and propagator.

  • A master site can have only one propagator.
  • A propagator has the "execute any procedure" grant.
  • A master site can have multiple receivers.
  • A master group can have only one receiver per master site.
  • A receiver is not granted "execute any procedure".

Example for a Multimaster Site

Next, we illustrate how to set up both a master site and a materialized view replication site using the replication management API.

Before you build the replication environment, you need to set up the sites that will participate in the replication environment. As illustrated there are separate processes for setting up a master site versus setting up a materialized view site.

We use the following databases:

  • CEL1.WORLD

  • CEL2.WORLD

  • REP1.WORLD

  • REP2.WORLD

Notice that REP1.WORLD and REP2.WORLD are materialized views based on the CEL1.WORLD. The arrows in the Figure represents database links.

Important Note !

If you get an error when creating Replication Groups such as:

ERROR at line 1:
ORA-04052: error occurred when looking up remote object
REPADMIN.SYS@CEL1.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified

then, connect directly to the Database as follows:

export ORACLE_SID=CEL1
sqlplus repadmin/repadmin

Now create the Replication Group.

Create SCOTT Schemas

We use the simple EMP/DEPT schema from SCOTT/TIGER.

sqlplus scott/tiger@CEL1.WORLD
start demobld.sql

ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
   CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
   REFERENCES dept (deptno) ENABLE);

sqlplus scott/tiger@CEL2.WORLD
start demobld.sql

ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
   CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
   REFERENCES dept (deptno) ENABLE);

The following sections contain step-by-step instructions for setting up the master sites in the sample replication environment: CEL1.WORLD and CEL2.WORLD. Before you set up the master sites, configure your network and Oracle Net so that all three databases can communicate with each other.

Create Password Files

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database if the instance has not been started up. Therefore, the authentication of the DBA must happen outside of the database.

The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

Default location and file name

The default location for the password file is:

  • $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix

  • %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

Deleting a password file

If password file authentication is no longer needed, the password file can be deleted and the init parameter remote_login_passwordfile set to none.

Password file state

If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up. A password file whose state is shared can only contain SYS.

Creating a password file

Password files are created with the orapwd tool.

orapwd file=orapwCEL1 password=manager entries=5 force=y

Create a Symbolic Link from $ORACLE_HOME/dbs to the Password.

lrwxrwxrwx 1 oracle dba 39 2005-09-13 16:55 initCEL1.ora -> /home/oracle/config/10.1.0/initCEL1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initCEL2.ora -> /home/oracle/config/10.1.0/initCEL2.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 08:20 initREP1.ora -> /home/oracle/config/10.1.0/initREP1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initREP2.ora -> /home/oracle/config/10.1.0/initREP2.ora
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL1 -> /home/oracle/config/10.1.0/orapwCEL1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL2 -> /home/oracle/config/10.1.0/orapwCEL2
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP1 -> /home/oracle/config/10.1.0/orapwREP1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP2 -> /home/oracle/config/10.1.0/orapwREP2

Adding Users to the password file

Users are added to the password file when they're granted the SYSDBA or SYSOPER privilege.

select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

grant SYSDBA to scott;

Grant succeeded.

select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
 SCOTT                          TRUE  FALSE

Setup Oracle Network

The Network must be setup for all Sites:

# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach               listener.ora
# --------------------------------------------------------------------------
# File:        listener.ora
#
# Autor:       Martin Zahn Akadia AG
#
# Purpose:     Configuration file for Net Listener
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.1.0.4 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------

LSNR101 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
  )

SID_LIST_LSNR101 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CEL1.WORLD)
      (ORACLE_HOME = /opt/oracle/product/10.1.0)
      (SID_NAME = CEL1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = CEL2.WORLD)
      (ORACLE_HOME = /opt/oracle/product/10.1.0)
      (SID_NAME = CEL2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = REP1.WORLD)
      (ORACLE_HOME = /opt/oracle/product/10.1.0)
      (SID_NAME = REP1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = REP2.WORLD)
      (ORACLE_HOME = /opt/oracle/product/10.1.0)
      (SID_NAME = REP2)
    )
  )

USE_PLUG_AND_PLAY_LSNR101 = OFF
STARTUP_WAIT_TIME_LSNR101 = 0
LOG_DIRECTORY_LSNR101 = /home/oracle/config/10.1.0
TRACE_FILE_LSNR101 = listener_LSNR101.trc
CONNECT_TIMEOUT_LSNR101 = 10
TRACE_LEVEL_LSNR101 = OFF
SAVE_CONFIG_ON_STOP_LISTENER = OFF

# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach               tnsnames.ora
# --------------------------------------------------------------------------
# File:        tnsnames.ora
#
# Autor:       Martin Zahn Akadia AG
#
# Purpose:     Configuration File for all Net Clients
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#

CEL1.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CEL1.WORLD)
    )
  )

CEL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CEL1)
    )
  )

CEL2.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CEL2.WORLD)
    )
  )

CEL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CEL2)
    )
  )

REP1.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REP1.WORLD)
    )
  )

REP1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REP1)
    )
  )

REP2.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REP2.WORLD)
    )
  )

REP2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REP2)
    )
  )

# Akadia AG, Fichtenweg 10, CH-3672 Oberdiessbach                 sqlnet.ora
# --------------------------------------------------------------------------
# File:        sqlnet.ora
#
# Autor:       Martin Zahn Akadia AG
#
# Purpose:     Configuration File for all Net8 Clients
#
# Location:    $TNS_ADMIN
#
# Certified:   Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#

 TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf
AUTOMATIC_IPC = ON
NAMES.DEFAULT_DOMAIN = WORLD
BEQUEATH_DETACH = NO
SQLNET.EXPIRE_TIME = 10
NAME.DEFAULT_ZONE = WORLD
USE_DEDICATED_SERVER = ON

Setup INIT.ORA Parameters 

global_names = TRUE
db_name = CEL1
db_domain = WORLD
instance_name = CEL1
service_names = CEL1.WORLD
remote_login_passwordfile = exclusive
job_queue_processes = 10
parallel_min_servers = 2
parallel_max_servers = 10

Setup Users on Master CEL1.WORLD and CEL2.WORLD

The following Figure shows the needed Steps to setup the users at the Master Sites.

The next steps shows how to create the following Users and Database Links on the two Master Sites. The commands for CEL2.WORLD are not shown, these are the same as for CEL1.WORLD.

(1) Connect at Master Site CEL1.WORLD

CONNECT sys/manager@CEL1.WORLD as sysdba;

(2) Setup the Replication Administrator

The Replication Administrator on the Master has the following Privileges

  • Administer replication groups and schemas

  • Propagate changes to remote sites

CREATE USER repadmin IDENTIFIED BY repadmin
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA 0 ON system
        PROFILE default;

GRANT CONNECT,RESOURCE TO repadmin;

Authorise the Replication Administrator to administer replication groups and schemas.

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'repadmin');
END;
/

This procedure grants the necessary privileges to the Replication Administrator to administer any replication groups at the current site.

(3) Grant create, lock and select to administrator

GRANT
     LOCK ANY TABLE,
     COMMENT ANY TABLE,
     CREATE SESSION,
     SELECT ANY TABLE,
     CREATE TABLE,
     CREATE PROCEDURE,
     CREATE SEQUENCE,
     CREATE TRIGGER,
     CREATE VIEW,
     CREATE SYNONYM,
     ALTER SESSION,
     CREATE MATERIALIZED VIEW,
     CREATE DATABASE LINK,
     SELECT ANY DICTIONARY
TO repadmin;

(4) Register Propagator

Grant propagate privileges to the Replication Administrator, to propagate changes to remote sites. The propagator is responsible for propagating the deferred transaction queue to other master sites.

BEGIN
   DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
      username=>'repadmin');
END;
/

This procedure registers the specified user as the propagator for the local database. It also grants the following privileges to the specified user (so that the user can create wrappers):

CREATE SESSION
CREATE PROCEDURE
CREATE DATABASE LINK
EXECUTE ANY PROCEDURE

(5) Register Receiver

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;
/

This procedure assigns proxy materialized view administrator or receiver privileges at the master site or master materialized view site for use with remote sites. This procedure grants only the necessary privileges to the proxy materialized view administrator or receiver.

privilege_type

Specifies the privilege type you are assigning. Use the following values for to define your privilege_type:
  • receiver for receiver privileges.
  • proxy_snapadmin for proxy materialized view administration privileges

list_of_gnames

Comma-separated list of replication groups you want a user registered for receiver privileges. There must be no spaces between entries in the list. If you set list_of_gnames to NULL, then the user is registered for all replication groups, even replication groups that are not yet known when this procedure is called.

(6) Schedule a job to delete successfully replicated transactions on Master

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

CONNECT REPADMIN/REPADMIN@CEL1.WORLD

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
       next_date => sysdate,
       interval => 'sysdate+1/24/30',
        delay_seconds => 0);
END;
/

(7a) Setup Proxy Materialized View Administrator

The proxy materialized view administrator performs tasks at the target master site on behalf of the materialized view administrator at the materialized view site.

CONNECT sys/manager@CEL1.WORLD as sysdba;

CREATE USER proxyadmin IDENTIFIED BY proxyadmin
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA 0 ON system
        PROFILE default;

GRANT CONNECT,RESOURCE TO proxyadmin;

Register Proxy Materialized View Administrator

BEGIN
   DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
      username => 'proxyadmin',
      privilege_type => 'proxy_snapadmin',
      list_of_gnames => NULL);
END;
/

GRANT
     CREATE SESSION,
     SELECT ANY TABLE,
     SELECT_CATALOG_ROLE
TO proxyadmin;

(7b) Setup Proxy Refresher

The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.

CREATE USER proxyrefresher IDENTIFIED BY proxyrefresher
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA 0 ON system
        PROFILE default;

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

********** Now, do exactly the same for the Master Site CEL2.WORLD (Not shown here). ***********

(8) Create the DB-Links between the master sites

The database links provide the necessary distributed mechanisms to allow the different replication sites to replicate data among themselves. Before you create any private database links, you must create the public database links that each private database link will use. You then must create a database link between all replication administrators at each of the master sites that you have set up.

CONNECT sys/manager@CEL1.WORLD as sysdba;
DROP PUBLIC DATABASE LINK CEL2.WORLD;
CREATE PUBLIC DATABASE LINK CEL2.WORLD USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL2)))';

CONNECT repadmin/repadmin@CEL1.WORLD;
CREATE DATABASE LINK CEL2.WORLD CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT sys/manager@CEL2.WORLD as sysdba;
DROP PUBLIC DATABASE LINK CEL1.WORLD;
CREATE PUBLIC DATABASE LINK CEL1.WORLD USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL1)))';

CONNECT repadmin/repadmin@CEL2.WORLD;
CREATE DATABASE LINK CEL1.WORLD CONNECT TO repadmin IDENTIFIED BY repadmin;

(9) Define a schedule for each database link

Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.

Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. The scheduled links in this example simulate continuous replication.

The following example schedules a periodic push every minute. Make sure that the refresh rate is not smalled than the time needed to refresh.

CONNECT repadmin/repadmin@CEL1.WORLD

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'CEL2.WORLD',
      interval => 'SYSDATE + (1/24/60)',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      parallelism => 1,
      delay_seconds => 0);
END;
/

CONNECT repadmin/repadmin@CEL2.WORLD

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'CEL1.WORLD',
      interval => 'SYSDATE + (1/24/60)',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      parallelism => 1,
      delay_seconds => 0);
END;
/

Scheduling Continuous Pushes

Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. To do so, use the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure and specify the following settings:

Parameter Value

delay_seconds

1200

interval

Lower than the delay_seconds setting

parallelism

1 or higher

execution_seconds

Higher than the delay_seconds setting

With this configuration, Oracle continues to push transactions that enter the deferred transaction queue for the duration of the entire interval. If the deferred transaction queue has no transactions to propagate for the amount of time specified by the delay_seconds parameter, then Oracle releases the resources used by the job and starts fresh when the next job queue process becomes available.

Important Note !

We observed, that the Database cannot be shutdown with SHUTDOWN IMMEDIATE with these settings. The Database can only be shutdown with SHUTDOWN ABORT, therefore we do NOT recommend to use Continuous Pushes.

The following is an example that simulates continual pushes:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'CEL2.WORLD',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      parallelism => 1,
       execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

Create Master Replication Group on CEL1.WORLD and CEL2.WORLD

After you have set up your master sites, you are ready to build a master group.

(1) Create Schema at Master Sites

It is assumed that the SCOTT schema exists at CEL1.WORLD and CEL2.WORLD.

(2) Create the Master Group

When you add an object to your master group or perform other replication administrative tasks, you reference the master group name defined during this step. This step must be completed by the replication administrator.

CONNECT repadmin/repadmin@CEL1.WORLD

BEGIN
   DBMS_REPCAT.DROP_MASTER_REPGROUP (
   gname => 'REPG');
 END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPGROUP (
   gname => 'REPG');
 END;
/

(3) Add objects to the Replication Group on Master

Now, add the replicated Objets (TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY) to the Replication Group.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'REPG',
      type  => 'TABLE',
      oname => 'EMP',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'REPG',
      type  => 'TABLE',
      oname => 'DEPT',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

If Your Tables Don’t Have a Primary Key.

The DBMS_REPCAT.SET_COLUMNS procedure enables you to use an alternate column or group of columns, instead of the primary key, to determine which columns of a table to compare when using row-level replication. You must call this procedure from the master definition site.

BEGIN
   DBMS_REPCAT.SET_COLUMNS (
   sname => 'owner_master',
   oname => 'DEPT',
   column_list => '"COL1","COL2","COL3","COL4"');
END;

(4) Add Additional Master Site CEL2.WORLD

After you have defined your master group at the master definition site = CEL1.WORLD (the site where the master group was created becomes the master definition site by default), you can define the other sites that will participate in the replication environment.

In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the SCOTT schema already exists at all master sites. In other words, it is assumed that the objects in the SCOTT schema are precreated at all master sites. Also, the copy_rows parameter is set to FALSE because it is assumed that the identical data is stored in the tables at each master site.

CONNECT repadmin/repadmin@CEL1.WORLD

If you get an error when connection over SQL*Net for the DBMS_REPCAT.ADD_MASTER_DATABASE command such as:

ERROR at line 1:
ORA-04052: error occurred when looking up remote object
REPADMIN.SYS@CEL1.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified

then, connect directly to the Database:

export ORACLE_SID=CEL1
 sqlplus repadmin/repadmin

BEGIN
   DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'REPG',
      master => 'CEL2.WORLD',
      use_existing_objects => TRUE,
      copy_rows => FALSE,
      propagation_mode => 'ASYNCHRONOUS');
END;
/

Check that Master Site CEL2.WORLD is now in the Replication Group 'REPG'.

Wait until CEL2.WORLD appears in the DBA_REPSITES view before continuing. Execute the following SELECT statement in another SQL*Plus session to make sure that CEL2.WORLD has appeared: 

SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'REPG';

DBLINK
----------------------------------------------
CEL1.WORLD
CEL2.WORLD

(5)  If conflicts are possible, then configure conflict resolution methods.

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

Before you define overwrite or discard conflict resolution methods, 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 might not be required

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'REPG');
END;
/

All Oracle conflict resolution methods are based on logical column groupings called column groups.

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMPCG',
      list_of_column_names => 'ename,job,mgr,hiredate,sal,comm,deptno');
END;
/

This example creates an OVERWRITE conflict resolution method.

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMPCG',
      sequence_no => 1,
      method => 'DISCARD',
      parameter_column_name => 'ename,job,mgr,hiredate,sal,comm,deptno');
END;
/

(6) Activate Replication Support for each added Table on Master

Replication support generates the triggers and packages needed to support replication for a specified object.

CONNECT repadmin/repadmin@CEL1.WORLD

BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      min_communication => TRUE);
END;
/

BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'DEPT',
      type => 'TABLE',
      min_communication => TRUE);
END;
/

min_communication

Set to false if any master site is running Oracle7 release 7.3.
Set to true when you want propagation of new and old values to be
minimized. The default is true.

(7) Start the Replication on the Master

We are now all set up and ready to replicate, so let’s do it. From the master database, start the replication process (track activity on the REPG objects). 

CONNECT repadmin/repadmin@CEL1.WORLD

You should wait until the DBA_REPCATLOG view is empty before resuming master activity.

SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'REPG';

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
     gname => 'REPG');
END;
/

The master site is now waiting for the snapshot site to begin replication. As changes are made to the master tables, they will be propagated as required.

The view dba_repgroup can be used to check the status of the replication groups that you create. After you have created the replication groups and generated the replication support for various tables within that group, you should query the dba_repgroup table to insure that REPG status field is set to "Normal".

select sname, master, status from dba_repgroup;

SNAME                          M STATUS
------------------------------ - ---------
REPG                           Y NORMAL

Setup Users on Materialized View Site REP1.WORLD and REP2.WORLD

The following Figure shows the needed Steps to setup the users at the Materialized View Sites.

The next steps shows how to create the following Users and Database Links on the two Materialized View Sites. The commands for REP2.WORLD are not shown, these are the same as for REP1.WORLD.

(1) Connect as system at Materialized View Site REP1.WORLD

CONNECT sys/manager@REP1.WORLD as sysdba;

(2) Create Materialized View Site Users

Setup the Materialized View Administrator

DROP USER mviewadmin CASCADE;
CREATE USER mviewadmin IDENTIFIED BY mviewadmin
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA 0 ON system
        PROFILE default;
GRANT CONNECT,RESOURCE TO mviewadmin;

Authorise the Materialized View Administrator to administer replication groups and schemas.

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'mviewadmin');
END;
/

Grant create,lock and select to Materialized View Administrator

GRANT
     LOCK ANY TABLE,
     COMMENT ANY TABLE,
     CREATE SESSION,
     SELECT ANY TABLE,
     CREATE TABLE,
     CREATE PROCEDURE,
     CREATE SEQUENCE,
     CREATE TRIGGER,
     CREATE VIEW,
     CREATE SYNONYM,
     ALTER SESSION,
     CREATE MATERIALIZED VIEW,
     CREATE DATABASE LINK,
     SELECT ANY DICTIONARY
TO mviewadmin;

Create Materialized View Propagator

The propagator is responsible for propagating the deferred transaction queue to the target master site.

DROP USER propagator CASCADE;
CREATE USER propagator IDENTIFIED BY propagator
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp
        QUOTA 0 ON system
        PROFILE default;
GRANT CONNECT,RESOURCE TO propagator;

Grant privs to the Materialized View Propagator, to propagate changes to remote sites

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

This procedure registers the specified user as the propagator for the local database. It also grants the following privileges to the specified user (so that the user can create wrappers):

  • CREATE SESSION
  • CREATE PROCEDURE
  • CREATE DATABASE LINK
  • EXECUTE ANY PROCEDURE

Create the refresher

The refresher is responsible for "pulling" changes made to the replicated tables at the target master site to the materialized view site. This user refreshes one or more materialized views. If you want the mviewadmin user to be the refresher, then this step is not required. The receiver is necessary only if the site will function as a master materialized view site for other materialized views sites.

Register Receiver to Materialized View Administrator

BEGIN
   DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
      username => 'mviewadmin',
      privilege_type => 'receiver',
      list_of_gnames => NULL);
END;
/

(3) Setup Database Links to Master(s)

Create Public Database Link

CONNECT sys/manager@REP1.WORLD as sysdba;
 DROP PUBLIC DATABASE LINK CEL1.WORLD;
 CREATE PUBLIC DATABASE LINK CEL1.WORLD USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL1)))';

Create the materialized view administrator database link.

You need to create a database link from the materialized view administrator at the materialized view site to the proxy materialized view administrator at the master site.

CONNECT mviewadmin/mviewadmin@REP1.WORLD;
 DROP DATABASE LINK CEL1.WORLD;
 CREATE DATABASE LINK CEL1.WORLD
CONNECT TO proxyadmin
IDENTIFIED BY proxyadmin
USING 'CEL1.WORLD';

Create the propagator/receiver database link.

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

CONNECT propagator/propagator@REP1.WORLD
 DROP DATABASE LINK CEL1.WORLD;
 CREATE DATABASE LINK CEL1.WORLD
CONNECT TO repadmin
IDENTIFIED BY repadmin
USING 'CEL1.WORLD';

(4) Schedule Purge at Materialized View Site (Optional)

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

CONNECT mviewadmin/mviewadmin@REP1.WORLD

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

(5 ) Schedule Push at Materialized View site (Optional)

If the materialized view site has a constant connection to its master site, then you optionally can schedule push at the materialized view sites. If the materialized view site is disconnected from its master site for extended periods of time, then it is typically better not to schedule push and refresh on demand, which pushes changes to the master site.

CONNECT mviewadmin/mviewadmin@REP1.WORLD

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'CEL1.WORLD',
      interval => 'SYSDATE + 1/144',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/

(6) Create the proxy materialized view administrator at Materialized View site (Optional)

The proxy materialized view administrator performs tasks at the target master materialized view site on behalf of the materialized view administrator at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

In our example we do not need this step.

********** Now, do exactly the same for the Materialized View Site REP2 (Not shown here). ***********

Create Materialized View Group on REP1.WORLD and REP2.WORLD

The following Figure shows the needed Steps to setup the Materialized View Group at the Materialized View Sites.

(1) Create Materialized View Logs on Master

When DML changes are made to the master table's data, Oracle stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called an incremental or fast refresh.

Without a materialized view log, Oracle must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it.

CONNECT scott/tiger@CEL1.WORLD

CREATE MATERIALIZED VIEW LOG ON emp
  TABLESPACE tab;

CREATE MATERIALIZED VIEW LOG ON dept
  TABLESPACE tab;

(2)  Create the Replicated Schema and its Database Link

Before building your materialized view group, you must make sure that the replicated schema exists at the remote materialized view site and that