René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Replication glossary

Administrative request

An administrative request is a call to a procedure or a function in Oracle's replication management API. 
column id      heading 'admin|request|id' format 999999
column request heading 'request'          format a25
column status  heading 'status'           format a15
column master  heading 'master|site'      format a25

select id, request, status, master from dba_repcatlog;
Requests that resulted in an error: 
column id      heading 'admin|request|id' format 999999
column request heading 'request' format a30
column errnum  heading 'error|number' format 999999
column message heading 'error|message' format a32

select 
  id, request, errnum, message 
from 
  dba_repcatlog 
where status = 'error';

Column group

... make it easier to resolve conflicts.

Deferred transaction queue

The deferred transaction queue stores transactions that are bound for another destination in the master group.
This queue is implemented with Oracle's advanced queue mechanisms.
Deferred transactions that result in an error are recored in the error queue. 
column dest  heading 'destination' format a45
column trans heading 'def trans'   format 9999
      
select 
  dblink dest, count(*) trans
from 
  deftrandest d 
group by dblink;

Deployment template

Deployment templates enable you to create multiple materialized view environements quickly. They also enable you to use variables to customize each materialized view environment for its individual needs. 
column refresh_template_name heading 'template|name'                format a10
column owner                 heading 'owner'                        format a10
column public_template       heading 'public?'                      format a7
column instantiated          heading 'number of|instantiated|sites' format 9999
column template_comment      heading 'comment'                      format a35

select distinct rt.refresh_template_name, 
       owner,
       public_template, 
       rs.instantiated, 
       rt.template_comment 
    from dba_repcat_refresh_templates rt,  
    ( select y.refresh_template_name, count(x.status) instantiated  
        from dba_repcat_template_sites x, dba_repcat_refresh_templates y 
        where 
          x.refresh_template_name(+) = y.refresh_template_name 
        group by 
          y.refresh_template_name
     ) rs 
     where 
      rt.refresh_template_name(+) = rs.refresh_template_name 
    order by 1;

Error queue

The error queue records deferred transactions that resulted in an error. 
column deferred_tran_id heading 'deferred|transaction|id' format a11
column origin_tran_db   heading 'origin|database'         format a15
column destination      heading 'destination|database'    format a15
column time_of_error    heading 'time of|error'           format a22
column error_number     heading 'oracle|error|number'     format 999999

select 
  deferred_tran_id, 
  origin_tran_db, 
  destination,
  to_char(start_time, 'dd.mm.yyyy hh24:mi:ss') time_of_error,
  error_number 
from 
  deferror
order by start_time;

Master group

A replication group at a master site is referred to as a master group.
A master group can be created with dbms_repcat.create_master_repgroup.
A master group is a container for objects that are replicated. Those objects are added to the master group with dbms_repcat.create_master_repobject.
Quiescing a master group: A master group can be quiesced which means that all replication activity on that group is turned off. This might be nessessary to perform maintaining operations on the group.
Each master group is associated with a do_deferred_repcat_admin job. 
column job        heading 'job id'           format 999999
column priv_user  heading 'privilege|schema' format a10
column broken     heading 'broken?'          format a7
column next_start heading 'next start'
column interval   heading 'interval'         format a20

select job, 
       priv_user, 
       broken, 
       to_char(next_date,'dd-mon-yyyy hh:mi:ss am') next_start, 
       interval 
    from dba_jobs 
    where what like '%dbms_repcat.do_deferred_repcat_admin%' 
    order by 1;

Materialized view log

Materialized view replication

This is one of the possible types of advanced replication.

Materialized view site

A materialized view site is one of two types of replications sites.

Master replication

There are two types of master replication: multimaster replication and single master replication.

Master site

A master site is one of two types of replications sites.

Master definition site

In a multimaster replication environement, one master site operates as the master definition site for the master group. This is the site where administrative and maintenance tasks are performed.
Changing the master definition site: In order to change the master definition site, use dbms_repcat.relocate_masterdef.

Retrieving information about a master site

column global_name    heading 'database'         format  a25
column admin_requests heading 'admin|reqests'    format 9999
column status         heading 'admin|errors'     format 9999
column tran           heading 'def|trans|pairs'  format 9999
column errors         heading 'def|trans|errors' format 9999
column complete       heading 'propagated|trans' format 9999

select g.global_name, d.admin_requests, e.status, dt.tran, de.errors, c.complete  
    from 
      (select                        global_name from global_name                            ) g, 
      (select count(id)           admin_requests from dba_repcatlog                          ) d, 
      (select count(status)               status from dba_repcatlog where status = 'error'   ) e,     
      (select count(*)                      tran from deftrandest                            ) dt, 
      (select count(*)                    errors from deferror                               ) de,      
      (select count(a.deferred_tran_id) complete from deftran a         
              where a.deferred_tran_id not in ( select b.deferred_tran_id from deftrandest b)) c;

Materialized view group

A replication group at a materialized view group is referred to as a materialized view group.
Use dba_registered_mview_groups to find out about existing materialized view groups.

Multimaster replication

This is one of the possible types of advanced replication.
There are two types of master replication: multimaster replication and single master replication.
Multimaster replication is also called peer to peer or n-way replication.
Each site in a multimaster replication environement is a master site.
There are three types of multimaster replication:
  • Asynchronous replication
    Changes made at a master site will be visible at a later time at the other participating master sites.
    Asynchronous replication is also called store-and-forward replication.
  • Synchronous replication Changes made at a master site will be visible immediately the other participating master sites.
    Synchronous replication is also called real-time replication.
  • Procedural replication

Propagator

The propagator is responsible for propagating the deferred transaction queue to other master sites. (See also receiver.
Use dbms_defer_sys.register_propagator to register a propagator.

Refresh group

Each refresh group at a materialized view site is associated with a refresh job that refreshes the materialized views in the refresh group at a set interval.
Finding the job id for each refresh job a a materialized view site: 
column job       heading 'job id'              format 999999
column priv_user heading 'privilege|schema'    format a10
column rname     heading 'refresh|group|name'  format a10
column rowner    heading 'refresh|group|owner' format a10
column broken    heading 'broken?'             format a7

select j.job, 
       j.priv_user, 
       r.rowner, 
       r.rname, 
       j.broken
from 
  dba_refresh r, 
  dba_jobs j 
where r.job = j.job;

Refresh job

Receiver

The receiver receives the propagated deferred transaction sent by the propagator from other master sites.
Use the following procedure to register a receiver. 
begin
  dbms_repcat_admin.register_user_repgroup (
    username      => 'repadmin',  -- Insert the receiver's username here
    privilege_type=> 'receiver',
    list_of_gnames=> null);
end;
/

Replication adminstrator

Use dbms_repcat_admin.grant_admin_any_schema to grant the nessessary privileges to the replication administrator.

Replication catalog

The replication catalog consists of the following views:

Replication group

A replication group is a "container" of replication objects that allows for easier administration.
The servers on which a replication group is "hosted" are called replication sites.
Use dba_repgroup to find out about replication groups.

Replication management API

The replication management API consists of the following PL/SQL packages:
Using the replication management api. There are three points to consider when using the API:
  • Some procedures or functions can only be called at the master definition site.
  • For some administrative operations, the replication activity must be suspended.
  • The order in which procedures and functions are called is relevant.

Replication object

  • Table
  • Index
  • View
  • Object view
  • Package, Procedure, Function
  • User defined types
  • Trigger
  • Synonym
  • Indextype
  • User defined operator
An object that is part of a replication group should not be altered directly (alter table obj add(foo number)). Instead, dbms_repcat.alter_master_repobject should be used.

Replication site

There are two types of replication sites:

Single master replication

There are two types of master replication: multimaster replication and single master replication.