|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
An administrative request is a call to a procedure or a function in Oracle's replication management API.
Requests that resulted in an error:
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;
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';
... 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 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;
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;
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.
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
Use dba_registered_mview_groups to find out about existing materialized view groups.
This is one of the possible types of advanced 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:
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.
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;
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; /
Use dbms_repcat_admin.grant_admin_any_schema to grant the nessessary privileges to the replication administrator.
The replication catalog consists of the following views:
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:
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.
Single master replication