10
Monitoring a Replication Environment

This chapter illustrates how to monitor a replication a replication environment using the data dictionary. This chapter contains these topics:

Monitoring Master Replication Environments
Monitoring Materialized View Sites
Monitoring Administrative Requests
Monitoring the Deferred Transactions Queue
Monitoring the Error Queue
Monitoring Performance in a Replication Environment

Monitoring Performance in a Replication Environment

Note:

The Replication Management tool in Oracle Enterprise Manager is also an excellent way to monitor a replication environment. Most of the information obtained by the queries in this chapter can be found in the reports available in the Replication Management tool. See the Replication Management tool online help for more information.

Monitoring Master Replication Environments

This section contains queries that you can run to display information about a master replication environment. The replication environment can be a multimaster environment, a master materialized view environment, or a hybrid environment that includes multiple master sites and materialized views.

Monitoring Master Sites

This section contains queries that you can run to display information about master sites.

Listing General Information About a Master Site

You can find the following general information about a master site by running the query in this section:

The number of administrative requests
The number of administrative request errors
The number of unpropagated deferred transaction-destination pairs. Each deferred transaction may have multiple destinations to which it will be propagated, and each destination is a single deferred transaction-destination pair.

For example, if there are ten deferred transactions and each one must be propagated to three sites, then there are 30 deferred transaction-pairs returned by this query. After some time, if the first deferred transaction is propagated to two of the three destination sites, then there are still ten deferred transactions, but there are two fewer deferred-transaction pairs, and this query returns 28 unpropagated deferred transaction-pairs. In this case, the first deferred transaction only has one transaction-pair remaining.

The number of deferred transaction errors (error transactions)
The number of successfully propagated transactions that are still in the queue. These transactions should be purged from the queue.

Run the following query to list this information for the current 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;

Your output looks similar to the following:

                                           Def    Def
                            Admin  Admin Trans  Trans Propagated
Database                  Reqests Errors Pairs Errors      Trans
------------------------- ------- ------ ----- ------ ----------
mv4.world                        5      0    37      0         53

Note:

This query can be expensive if you have a large number of transactions in the deferred transactions queue.

Monitoring Master Groups

This section contains queries that you can run to display information about the master groups at a replication site.

Listing the Master Sites Participating in a Master Group

Run the following query to list the master sites for each master group at a replication site and indicate which master site is the master definition site for each master group:

COLUMN GNAME HEADING 'Master Group' FORMAT A20
COLUMN DBLINK HEADING 'Sites' FORMAT A25
COLUMN MASTERDEF HEADING 'Master|Definition|Site?' FORMAT A10

SELECT GNAME, DBLINK, MASTERDEF
    FROM DBA_REPSITES 
    WHERE MASTER = 'Y' 
    AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')    
    ORDER BY GNAME;

The subquery in the SELECT statement ensures that materialized view groups do not appear in the output. Your output looks similar to the following:

                                               Master
                                               Definition
Master_Group         Sites                     Site?
-------------------- ------------------------- ----------
HR_RG                mv4.world                  Y
HR_RG                NY.WORLD                  N

This list indicates that mv4.world is the master definition site for the hr_rg master group.

Listing General Information About Master Groups

You can use the query in this section to list the following general information about the master groups at a master site:

The name of each master group
The number of unpropagated deferred transaction-destination pairs. Each deferred transaction may have multiple destinations to which it will be propagated, and each destination is a single deferred transaction-destination pair.

The number of deferred transaction errors (error transactions) for each master group
The number of administrative requests for each master group
The number of administrative request errors for each master group

Run the following query to list this information:

COLUMN GNAME HEADING 'Master Group' FORMAT A15
COLUMN deftran HEADING 'Number of|Deferred|Transaction|Pairs' FORMAT 9999
COLUMN deftranerror HEADING 'Number of|Deferred|Transaction|Errors' FORMAT 9999
COLUMN adminreq HEADING 'Number of|Administrative|Requests' FORMAT 9999
COLUMN adminreqerror HEADING 'Number of|Administrative|Request|Errors' 
COLUMN adminreqerror FORMAT 9999

SELECT G.GNAME, 
       NVL(T.CNT1, 0) deftran, 
       NVL(IE.CNT2, 0) deftranerror, 
       NVL(A.CNT3, 0) adminreq, 
       NVL(B.CNT4, 0) adminreqerror
    FROM 
    (SELECT DISTINCT GNAME FROM DBA_REPGROUP WHERE MASTER='Y') G, 
    (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT D.DEFERRED_TRAN_ID) CNT1 
        FROM  DBA_REPOBJECT RO, DEFCALL D, DEFTRANDEST TD   
        WHERE RO.SNAME = D.SCHEMANAME 
        AND RO.ONAME = D.PACKAGENAME 
        AND RO.TYPE IN ('TABLE', 'PACKAGE', 'SNAPSHOT')
        AND TD.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID    
        GROUP BY RO.GNAME ) T, 
    (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT E.DEFERRED_TRAN_ID) CNT2 
        FROM DBA_REPOBJECT RO, DEFCALL D, DEFERROR E   
        WHERE RO.SNAME = D.SCHEMANAME 
        AND RO.ONAME = D.PACKAGENAME    
        AND RO.TYPE IN ('TABLE', 'PACKAGE', 'SNAPSHOT') 
        AND E.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID     
        AND E.CALLNO = D.CALLNO   
        GROUP BY RO.GNAME ) IE, 
    (SELECT GNAME, COUNT(*) CNT3 FROM DBA_REPCATLOG GROUP BY GNAME) A, 
    (SELECT GNAME, COUNT(*) CNT4 FROM DBA_REPCATLOG 
        WHERE STATUS = 'ERROR'
        GROUP BY GNAME) B WHERE G.GNAME = IE.GNAME (+) 
        AND G.GNAME = T.GNAME (+) 
        AND G.GNAME = A.GNAME (+) 
        AND G.GNAME = B.GNAME (+) ORDER BY G.GNAME;

Your output looks similar to the following:

                  Number of   Number of                     Number of
                   Deferred    Deferred      Number of Administrative
                Transaction Transaction Administrative        Request
Master Group          Pairs      Errors       Requests         Errors
--------------- ----------- ----------- -------------- --------------
HR_RG                    54           0              0              0
OE_RG                    33           1              5              0

Note:

This query can be expensive if you have a large number of transactions waiting to be propagated.

Monitoring Masters

A master can be either a master site or a master materialized view site. This section contains queries that you can run to display information about masters.

Listing Information About Materialized Views Based on a Master

If you have materialized view sites based on a master, then you can use the query in this section to list the following information about the master:

The number of replication groups at a master. The replication groups can be either master groups or materialized view groups.
The number of registered materialized view groups based on the replication groups at the master
The number of registered materialized views based on objects at the master. The objects can be either master tables or master materialized views.
The number of materialized view logs at the master
The number of deployment templates at the master

Run the following query to list this information:

COLUMN repgroup HEADING 'Number of|Replication|Groups' FORMAT 9999
COLUMN mvgroup HEADING 'Number of|Registered|MV Groups' FORMAT 9999
COLUMN mv HEADING 'Number of|Registered MVs' FORMAT 9999
COLUMN mvlog HEADING 'Number of|MV Logs' FORMAT 9999
COLUMN template HEADING 'Number of|Templates' FORMAT 9999

SELECT A.REPGROUP repgroup, 
       B.MVGROUP mvgroup, 
       C.MV mv, 
       D.MVLOG mvlog, 
       E.TEMPLATE template
    FROM (SELECT COUNT(G.GNAME) REPGROUP 
             FROM DBA_REPGROUP G, DBA_REPSITES S 
             WHERE G.MASTER = 'Y' 
             AND S.MASTER = 'Y' 
             AND G.GNAME = S.GNAME 
             AND S.MY_DBLINK = 'Y') A, 
         (SELECT COUNT(*) MVGROUP 
             FROM DBA_REGISTERED_MVIEW_GROUPS) B, 
         (SELECT COUNT(*) MV 
             FROM DBA_REGISTERED_MVIEWS) C,  
         (SELECT COUNT(*) MVLOG 
             FROM (SELECT 1 FROM DBA_MVIEW_LOGS
             GROUP BY LOG_OWNER, LOG_TABLE)) D,
         (SELECT COUNT(*) TEMPLATE FROM DBA_REPCAT_REFRESH_TEMPLATES) E;

Your output looks similar to the following:

  Number of  Number of
Replication Registered      Number of Number of Number of
     Groups  MV Groups Registered MVs   MV Logs Templates
----------- ---------- -------------- --------- ---------
          1          5             27         6         3

Listing Information About the Materialized View Logs at a Master

A materialized view log enables you to fast refresh materialized views based on a master. A master can be a master table or a master materialized view. If you have materialized view logs based at a master, then you can use the query in this section to list the following information about them:

The name of each log table that stores the materialized view log data
The owner of each materialized view log
The master on which each materialized view log is based
Whether a materialized view log is a row id materialized view log
Whether a materialized view log is a primary key materialized view log
Whether the materialized view log is an object id materialized view log
Whether a materialized view log has filter columns

Run the following query to list this information:

COLUMN LOG_TABLE HEADING 'Log Table' FORMAT A20
COLUMN LOG_OWNER HEADING 'Log|Owner' FORMAT A5
COLUMN MASTER HEADING 'Master' FORMAT A15
COLUMN ROWIDS HEADING 'Row|ID?' FORMAT A3
COLUMN PRIMARY_KEY HEADING 'Primary|Key?' FORMAT A7
COLUMN OBJECT_ID HEADING 'Object|ID?' FORMAT A6
COLUMN FILTER_COLUMNS HEADING 'Filter|Columns?' FORMAT A8

SELECT DISTINCT LOG_TABLE, 
       LOG_OWNER, 
       MASTER, 
       ROWIDS, 
       PRIMARY_KEY, 
       OBJECT_ID,
       FILTER_COLUMNS 
    FROM DBA_MVIEW_LOGS 
    ORDER BY 1;

Your output looks similar to the following:

                     Log                   Row Primary Object Filter
Log Table            Owner Master          ID? Key?    ID?    Columns?
-------------------- ----- --------------- --- ------- ------ --------
MLOG$_COUNTRIES      HR    COUNTRIES       NO  YES     NO     NO
MLOG$_DEPARTMENTS    HR    DEPARTMENTS     NO  YES     NO     NO
MLOG$_EMPLOYEES      HR    EMPLOYEES       NO  YES     NO     NO
MLOG$_JOBS           HR    JOBS            NO  YES     NO     NO
MLOG$_JOB_HISTORY    HR    JOB_HISTORY     NO  YES     NO     NO
MLOG$_LOCATIONS      HR    LOCATIONS       NO  YES     NO     NO

See Also:

Oracle9i Replication for information about materialized view logs

Listing the Materialized Views That Use a Materialized View Log

More than one materialized view can use a materialized view log. If you have materialized view logs based at a master, then you can use the query in this section to list the following the materialized views that use each log:

The name of each log table that stores the materialized view log data
The owner of each materialized view log
The master on which each materialized view log is based
The materialized view identification number of each materialized view that uses the materialized view log
The name of each materialized view that uses the materialized view log

Run the following query to list this information:

COLUMN LOG_TABLE HEADING 'Mview|Log Table' FORMAT A20
COLUMN LOG_OWNER HEADING 'Mview|Log Owner' FORMAT A10
COLUMN MASTER HEADING 'Master' FORMAT A20
COLUMN MVIEW_ID HEADING 'Mview|ID' FORMAT 9999
COLUMN NAME HEADING 'Mview Name' FORMAT A20

SELECT L.LOG_TABLE, L.LOG_OWNER, B.MASTER, B.MVIEW_ID, R.NAME  
FROM ALL_MVIEW_LOGS L, ALL_BASE_TABLE_MVIEWS B, ALL_REGISTERED_MVIEWS R
WHERE B.MVIEW_ID = R.MVIEW_ID
AND B.OWNER = L.LOG_OWNER
AND B.MASTER = L.MASTER;

Your output looks similar to the following:

Mview                                                Mview
Log Table            Owner      Master                  ID Mview Name
-------------------- ---------- -------------------- ----- --------------------
MLOG$_COUNTRIES      HR         COUNTRIES               24 COUNTRIES_MVIEW1
MLOG$_COUNTRIES      HR         COUNTRIES               31 COUNTRIES_MVIEW2
MLOG$_DEPARTMENTS    HR                      DEPARTMENTS             19 DEPARTMENTS_MVIEW1
MLOG$_DEPARTMENTS    HR                      DEPARTMENTS             64 DEPARTMENTS_MVIEW2
MLOG$_DEPARTMENTS    HR                      DEPARTMENTS             15 DEPARTMENTS_MVIEW3

Listing Information About the Deployment Templates at a Master

Deployment templates enable you to create multiple materialized view environments quickly. They also enable you to use variables to customize each materialized view environment for its individual needs. You can use the query in this section to list the following information about the deployment templates at a master:

The name of each deployment template
The owner of each deployment template
Whether a deployment template is public
The number of instantiated materialized view sites based on each deployment template
The comment associated with each deployment template

Run the following query to list this information:

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;

Your output looks similar to the following:

                                 Number of
Template                      Instantiated
Name       Owner      Public?        Sites Comment
---------- ---------- ------- ------------ -----------------------------------
HR_REFG_DT HR         N                  2 Human Resources Deployment Template

The N in the Public? column means that the deployment template is private. Therefore, it can only be instantiated by authorized users. A Y in this column means that the deployment template is public. Any user can instantiate a public deployment template.

Monitoring Materialized View Sites

This section contains queries that you can run to display information about the materialized view sites.

Listing General Information About a Materialized View Site

You can use the query in this section to list the following general information about the current materialized view site:

The number of materialized view groups at the site
The number of materialized views at the site
The number of refresh groups at the site

Run the following query to list this information:

COLUMN MVGROUP HEADING 'Number of|Materialized|View Groups' FORMAT 9999
COLUMN MV HEADING 'Number of|Materialized|Views' FORMAT 9999
COLUMN RGROUP HEADING 'Number of|Refresh Groups' FORMAT 9999

SELECT A.MVGROUP, B.MV, C.RGROUP 
    FROM 
    (SELECT COUNT(S.GNAME) MVGROUP 
        FROM DBA_REPSITES S
        WHERE S.SNAPMASTER = 'Y') A, 
     (SELECT COUNT(*) MV 
        FROM DBA_MVIEWS) B, 
     (SELECT COUNT(*) RGROUP 
        FROM DBA_REFRESH) C;

Your output looks similar to the following:

   Number of    Number of
Materialized Materialized      Number of
 View Groups        Views Refresh Groups
------------ ------------ --------------
           5           25              5

Listing General Information About Materialized View Groups

You can use the query in this section to list the following general information about the materialized view groups at the current materialized view site:

The name of each materialized view group
The master of each materialized view group
The method of propagation to a materialized view group's master, either asynchronous or synchronous
The comment associated with each materialized view group

Run the following query to list this information:

COLUMN GNAME HEADING 'Group Name' FORMAT A10
COLUMN DBLINK HEADING 'Master' FORMAT A25
COLUMN Propagation HEADING 'Propagation|Method' FORMAT A12
COLUMN SCHEMA_COMMENT HEADING 'Comment' FORMAT A30

SELECT S.GNAME, 
       S.DBLINK, 
       DECODE(S.PROP_UPDATES, 
              0, 'ASYNCHRONOUS', 
              1, 'SYNCHRONOUS') Propagation,
       G.SCHEMA_COMMENT 
    FROM DBA_REPSITES S, DBA_REPGROUP G
    WHERE S.GNAME = G.GNAME
    AND S.SNAPMASTER = 'Y';

Your output looks similar to the following:

                                     Propagation
Group Name Master                    Method       Comment
---------- ------------------------- ------------ ------------------------------
HR_RG      mv4.world                  ASYNCHRONOUS Human Resources Group

Listing Information About Materialized Views

This section contains queries that you can run to display information about the materialized views at a replication site.

Listing Master Information For Materialized Views

The following query shows the master for each materialized view at a replication site and whether the materialized view can be fast refreshed:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN MASTER_LINK HEADING 'Master Link' FORMAT A30
COLUMN Fast_Refresh HEADING 'Fast|Refreshable?' FORMAT A16

SELECT MVIEW_NAME, 
       OWNER, 
       MASTER_LINK,  
       DECODE(FAST_REFRESHABLE, 
              'NO', 'NO',
              'DML', 'YES',    
              'DIRLOAD', 'DIRECT LOAD ONLY',
              'DIRLOAD_DML', 'YES',
              'DIRLOAD_LIMITEDDML', 'LIMITED') Fast_Refresh
    FROM DBA_MVIEWS;

Your output looks similar to the following:


Materialized                                              Fast
View Name       Owner      Master Link                    Refreshable?
--------------- ---------- ------------------------------ ----------------
DEPARTMENTS_MV  HR         @mv4.world                      YES
EMPLOYEES_MV    HR         @mv4.world                      YES
JOBS_MV         HR         @mv4.world                      YES
JOB_HISTORY_MV  HR         @mv4.world                      YES
LOCATIONS_MV    HR         @mv4.world                      YES

Listing the Properties of Materialized Views

You can use the query in this section to list the following information about the materialized views at the current replication site:

The name of each materialized view
The owner of each materialized view
The refresh method used by each materialized view: COMPLETE, FORCE, FAST, or NEVER
Whether a materialized view is updatable
The last date on which each materialized view was refreshed

Run the following query to list this information:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN REFRESH_METHOD HEADING 'Refresh|Method' FORMAT A10
COLUMN UPDATABLE HEADING 'Updatable?' FORMAT A10
COLUMN LAST_REFRESH_DATE HEADING 'Last|Refresh|Date'
COLUMN LAST_REFRESH_TYPE HEADING 'Last|Refresh|Type' FORMAT A15

SELECT MVIEW_NAME, 
       OWNER,
       REFRESH_METHOD, 
       UPDATABLE, 
       LAST_REFRESH_DATE, 
       LAST_REFRESH_TYPE
    FROM DBA_MVIEWS;

Your output looks similar to the following:

                                                 Last            Last
Materialized               Refresh               Refresh         Refresh
View Name       Owner      Method     Updatable? Date            Type
--------------- ---------- ---------- ---------- --------------- ---------------
DEPARTMENTS_MV  HR         FORCE      Y          22-JAN-01       FAST
EMPLOYEES_MV    HR         FAST       Y          22-JAN-01       COMPLETE
JOBS_MV         HR         COMPLETE   Y          22-JAN-01       COMPLETE
JOB_HISTORY_MV  HR         FAST       Y          22-JAN-01       FAST
LOCATIONS_MV    HR         FAST       Y          22-JAN-01       FAST

Listing Information About the Refresh Groups at a Materialized View Site

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. You can query the DBA_REFRESH data dictionary view to list the following information about the refresh jobs at a materialized view site:

The name of the refresh group
The owner of the refresh group
Whether the refresh job is broken
The next date and time when the refresh job will run
The current interval setting for the refresh job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7
COLUMN next_refresh HEADING 'Next Refresh'
COLUMN INTERVAL HEADING 'Interval' FORMAT A20

SELECT RNAME, 
           ROWNER, 
       BROKEN, 
       TO_CHAR(NEXT_DATE, 'DD-MON-YYYY HH:MI:SS AM') next_refresh, 
       INTERVAL 
    FROM DBA_REFRESH 
    ORDER BY 1;

Your output looks similar to the following:

Refresh    Refresh
Group      Group
Name       Owner      Broken? Next Refresh            Interval
---------- ---------- ------- ----------------------- --------------------
HR_REFG    MVIEWADMIN N       01-JAN-4000 12:00:00 AM SYSDATE + 1/24

The N in the Broken? column means that the job is not broken. Therefore, the refresh job will run at the next start time. A Y in this column means that the job is broken.

Determining the Job ID for Each Refresh Job at a Materialized View Site

You can use the query in this section to list the following information about the refresh jobs at a materialized view site:

The job identification number of each refresh job. Each job created by the DBMS_JOBS package is assigned a unique identification number.
The privilege schema, which is the schema whose default privileges apply to the job
The schema that owns each refresh job. Typically, the materialized view administrator owns a refresh job. A common username for the materialized view administrator is mviewadmin.
The name of the refresh group that the job refreshes
The status of the refresh job, either normal or broken

The following query displays this information:

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
    ORDER BY 1;

Your output looks similar to the following:

                   Refresh    Refresh
        Privilege  Group      Group
 Job ID Schema     Owner      Name       Broken?
------- ---------- ---------- ---------- -------
     21 MVIEWADMIN MVIEWADMIN HR_REFG    N

The N in the Broken? column means that the job is not broken. Therefore, the job will run at the next start time. A Y in this column means that the job is broken.

Determining Which Materialized Views Are Currently Refreshing

The following query shows the materialized views that are currently refreshing:

COLUMN SID HEADING 'Session|Identifier' FORMAT 9999
COLUMN SERIAL# HEADING 'Serial|Number' FORMAT 999999
COLUMN CURRMVOWNER HEADING 'Owner' FORMAT A15
COLUMN CURRMVNAME HEADING 'Materialized|View' FORMAT A25

SELECT * FROM V$MVREFRESH;

Your output looks similar to the following:

   Session  Serial                 Materialized
Identifier  Number Owner           View
---------- ------- --------------- -------------------------
        19     233 HR              COUNTRIES_MV
         5     647 HR              EMPLOYEES_MV

Note:

The V$MVREFRESH dynamic performance view does not contain information about updatable materialized views when the materialized views' deferred transactions are being pushed to its master.

Monitoring Administrative Requests

This section contains queries that you can run to display information about the administrative requests at a master site.

Listing General Information About Administrative Requests

You can use the query in this section to list the following general information about the administrative requests at a master site:

The identification number of each administrative request
The action requested by each administrative request
The status of each request
The master site where the request is being executed

The following query displays this information:

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;

Your output looks similar to the following:

  Admin
Request                                           Master
     ID Request                   Status          Site
------- ------------------------- --------------- -------------------------
     44 RESUME_MASTER_ACTIVITY    AWAIT_CALLBACK  NY.WORLD

Determining the Cause of Administrative Request Errors

You can determine the cause of an administrative request error by displaying its error message. The following query displays the error message for each administrative request 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';

Your output looks similar to the following:

  Admin
Request                                  Error Error
     ID Request                         Number Message
------- ------------------------------ ------- ------------------------------
     70 CREATE_MASTER_REPOBJECT          -2292 ORA-02292: integrity constrain
                                               t (HR.DEPT_LOC_FK) violated -
                                               child record found
                                               ORA-02266: unique/primary keys
                                                in table referenced by enable
                                               d foreign keys

     71 GENERATE_INTERNAL_PKG_SUPPORT   -23308 ORA-23308: object HR.LOCATIONS
                                                does not exist or is invalid

Listing General Information About the Job that Executes Administrative Requests

Each master group is associated with a do_deferred_repcat_admin job that executes administrative requests. You can query the DBA_JOBS data dictionary view to list the following information about this job at a replication site:

The job identification number of each do_deferred_repcat_admin job. Each job created by the DBMS_JOBS package is assigned a unique identification number.
The privilege schema, which is the schema whose default privileges apply to the job
The status of each do_deferred_repcat_admin job, either normal or broken
The next date and time when each do_deferred_repcat_admin job will run
The current interval setting for each do_deferred_repcat_admin job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

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;

Your output looks similar to the following:

        Privilege
 Job ID Schema     Broken? Next Start              Interval
------- ---------- ------- ----------------------- --------------------
      3 REPADMIN   N       02-FEB-2001 04:34:36 PM SYSDATE + (1/144)

The N in the Broken? column means that the job is not broken. Therefore, the job will run at the next start time. A Y in this column means that the job is broken.

Checking the Definition of Each do_deferred_repcat_admin Job

You can query the DBA_JOBS data dictionary view to show the definition of each do_deferred_repcat_admin job at a replication site. The following query shows the definitions:

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN WHAT HEADING 'Definitions of Admin Req Jobs' FORMAT A70

SELECT JOB, WHAT 
  FROM DBA_JOBS 
  WHERE WHAT LIKE '%dbms_repcat.do_deferred_repcat_admin%' 
  ORDER BY 1;

Your output looks similar to the following:

 Job ID Definitions of Admin Req Jobs
------- ----------------------------------------------------------------------
    321 dbms_repcat.do_deferred_repcat_admin('"HR_RG"', FALSE);
    342 dbms_repcat.do_deferred_repcat_admin('"OE_RG"', FALSE);

Monitoring the Deferred Transactions Queue

This section contains queries that you can run to display information about the deferred transactions queue at a replication site.

Monitoring Transaction Propagation

This section contains queries that you can run to display information about propagation of transactions in the deferred transactions queue.

Listing the Number of Deferred Transactions for Each Destination Master Site

You can find the number of unpropagated deferred transactions for each destination master site by running the query in this section. This query shows each master site to which the current master site is propagating deferred transactions and the number of deferred transactions to be propagated to each destination site.

Run the following query to see the number of deferred and error transactions:

COLUMN DEST HEADING 'Destination' FORMAT A45
COLUMN TRANS HEADING 'Def Trans' FORMAT 9999
      
SELECT DBLINK DEST, COUNT(*) TRANS
    FROM DEFTRANDEST D 
    GROUP BY DBLINK;

Your output looks similar to the following:

Destination                                   Def Trans
--------------------------------------------- ---------
NY.WORLD                                             27
mv4.world                                             44

Note:

This query can be expensive if you have a large number of transactions waiting to be propagated.

Listing General Information About the Push Jobs at a Replication Site

Each scheduled link at a replication site is associated with a push job that propagates deferred transactions in the deferred transaction queue to a destination site. You can use the query in this section to list the following information about the push jobs at a replication site:

The job identification number of each push job. Each job created by the DBMS_JOBS package is assigned a unique identification number.
The privilege schema, which is the schema whose default privileges apply to the job
The destination site where the deferred transactions are pushed
The status of the push job, either normal or broken

The following query displays this information:

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN PRIV_USER HEADING 'Privilege|Schema' FORMAT A10
COLUMN DBLINK HEADING 'Destination' FORMAT A40
COLUMN BROKEN HEADING 'Broken?' FORMAT A7

SELECT J.JOB, 
       J.PRIV_USER, 
       S.DBLINK,
       J.BROKEN      
    FROM DEFSCHEDULE S, DBA_JOBS J 
    WHERE S.DBLINK != (SELECT GLOBAL_NAME FROM GLOBAL_NAME)
    AND S.JOB = J.JOB 
    ORDER BY 1;

Your output looks similar to the following:

        Privilege
 Job ID Schema     Destination                              Broken?
------- ---------- ---------------------------------------- -------
      2 REPADMIN   NY.WORLD                                 N

The N in the Broken? column means that the job is not broken. Therefore, the job will run at the next start time. A Y in this column means that the job is broken.

Determining the Next Start Time and Interval for the Push Jobs

Each scheduled link at a replication site is associated with a push job that propagates deferred transactions in the deferred transaction queue to a destination site. You can query the DEFSCHEDULE and DBA_JOBS data dictionary views to list the following information about the push jobs at a replication site:

The job identification number of each push job. Each job created by the DBMS_JOBS package is assigned a unique identification number.
The destination site where the deferred transactions are pushed
The next date and time when the push job will run
The current interval setting for the push job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN DBLINK HEADING 'Destination' FORMAT A22
COLUMN next_start HEADING 'Next Start'
COLUMN INTERVAL HEADING 'Interval' FORMAT A25

SELECT JOB, 
       DBLINK,
       TO_CHAR(NEXT_DATE, 'DD-MON-YYYY HH:MI:SS AM') next_start, 
       INTERVAL       
    FROM DEFSCHEDULE
    WHERE DBLINK != (SELECT GLOBAL_NAME FROM GLOBAL_NAME)
    AND JOB IS NOT NULL 
    ORDER BY 1;

Your output looks similar to the following:

 Job ID Destination            Next Start              Interval
------- ---------------------- ----------------------- -------------------------
      2 NY.WORLD               02-FEB-2001 04:44:39 PM SYSDATE + 10 / (24 * 60)

Determining the Total Number of Transactions Queued for Propagation

Run the following query to display the total number of transactions in the deferred transaction queue that are waiting to be propagated:

SELECT COUNT(DISTINCT DEFERRED_TRAN_ID) "Transactions Queued" 
    FROM DEFTRANDEST;

Your output looks similar to the following:

Transactions Queued
-------------------
                 37

Note:

This query can be expensive if you have a large number of transactions waiting to be propagated.

Monitoring Purges of Successfully Propagated Transactions

This section contains queries that you can run to display information about purges of successfully propagated transactions from the deferred transactions queue.

Listing General Information About the Purge Job

During standard setup of a replication site, you configure a purge job to remove successfully propagated transactions from the deferred transactions queue. You can query the DBA_JOBS data dictionary view to list the following information about the purge job at a replication site:

The job identification number of the purge job. Each job created by the DBMS_JOBS package is assigned a unique identification number.
The privilege schema, which is the schema whose default privileges apply to the job
The status of the job, either normal or broken
The next date and time when the purge job will run
The current interval setting for the purge job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

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 A25

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_defer_sys.purge%' 
    ORDER BY 1;

Your output looks similar to the following:

        Privilege
 Job ID Schema     Broken? Next Start              Interval
------- ---------- ------- ----------------------- -------------------------
      1 REPADMIN   N       02-FEB-2001 05:06:43 PM SYSDATE + 1/24

The N in the Broken? column means that the job is not broken. Therefore, the job will run at the next start time. A Y in this column means that the job is broken.

Checking the Definition of the Purge Job

You can query the DBA_JOBS data dictionary view to show the definition of the purge job at a replication site. The following query shows the definition:

SELECT WHAT "Definition of the Purge Job" 
    FROM DBA_JOBS 
    WHERE WHAT LIKE '%dbms_defer_sys.purge%' ORDER BY 1;

Your output looks similar to the following:

Definition of the Purge Job
--------------------------------------------------------------------------------
declare rc binary_integer; begin rc := sys.dbms_defer_sys.purge( delay_seconds=>
0); end;

Determining the Amount of Time Since the Last Purge

The following query shows the total amount of time, in minutes, since the successfully propagated transactions were purged from the deferred transactions queue:

SELECT ((SYSDATE - LAST_PURGE_TIME) / 60) "Minutes Since Last Purge" 
    FROM V$REPLQUEUE;

Your output looks similar to the following:

Minutes Since Last Purge
------------------------
              13.43333

Determining the Total Number of Purged Transactions

The following query shows the total number of successfully propagated transactions that have been purged from the deferred transaction queue since the instance was last started:

SELECT TXNS_PURGED "Transactions Purged" 
    FROM V$REPLQUEUE;

Your output looks similar to the following:

Transactions Purged
-------------------
               6541

Monitoring the Error Queue

This section contains queries that you can run to display information about the error queue at a replication site. The error queue contains deferred transactions that resulted in an error at the destination site. These error transactions are placed in the error queue at the destination site.

Listing General Information About the Error Transactions at a Replication Site

The following query lists the general information about the error transactions at a replication site:

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-Mon-YYYY hh24:mi:ss') TIME_OF_ERROR,
       ERROR_NUMBER 
    FROM DEFERROR ORDER BY START_TIME;

Your output looks similar to the following:

Deferred                                                            Oracle
Transaction Origin          Destination     Time of                  Error
ID          Database        Database        Error                   Number
----------- --------------- --------------- ---------------------- -------
1.8.2470    mv4.world        NY.WORLD        25-Jan-2001 17:11:17      1403

You can use the deferred transaction ID and the destination database to either attempt to rerun the transaction that caused the error or to delete the error.

For example, to attempt to rerun the transaction in the previous example, enter the following:

EXECUTE DBMS_DEFER_SYS.EXECUTE_ERROR('1.8.2470', 'NY.WORLD');

To delete the error in the previous example, enter the following:

EXECUTE DBMS_DEFER_SYS.DELETE_ERROR('1.8.2470', 'NY.WORLD');

Typically, you should delete an error only if you have resolved it manually.

Determining the Percentage of Error Transactions

When propagating transactions to a remote master site, some transactions are propagated and applied successfully while other transactions may result in errors at the remote master site. Transactions that result in errors are called error transactions.

Run the following query to display the percentage of error transactions that resulted from propagation to the remote master site mv4.world:

SELECT DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions', 
       (TOTAL_ERROR_COUNT/TOTAL_TXN_COUNT)*100) "ERROR PERCENTAGE" 
  FROM DEFSCHEDULE 
  WHERE DBLINK = 'mv4.world';

Your output looks similar to the following:

Error Percentage
----------------
           3.265

Note:

If this query returns 'No transactions', then no transactions have been propagated to the specified remote site since the statistics were last cleared.

Listing the Number of Error Transactions from Each Origin Master Site

You can find the number of transaction errors resulting from pushes by each origin master site by running the query in this section.

Run the following query to see the number of deferred and error transactions:

COLUMN SOURCE HEADING 'Origin' FORMAT A45
COLUMN ERRORS HEADING 'Def Trans Errors' FORMAT 9999

SELECT E.ORIGIN_TRAN_DB SOURCE, COUNT(*) ERRORS 
    FROM DEFERROR E 
    GROUP BY E.ORIGIN_TRAN_DB;

Your output looks similar to the following:

Origin                                        Def Trans Errors
--------------------------------------------- ----------------
NY.WORLD                                                     1
mv4.world                                                     3

Listing the Error Messages for the Error Transactions at a Replication Site

The following query lists the error messages for the error transactions at a replication site:

COLUMN DEFERRED_TRAN_ID HEADING 'Deferred|Transaction|ID' FORMAT A11
COLUMN ERROR_MSG HEADING 'Error Messages' FORMAT A68

SELECT DEFERRED_TRAN_ID, ERROR_MSG 
    FROM DEFERROR;

Your output looks similar to the following:

Deferred
Transaction
ID          Error Messages
----------- --------------------------------------------------------------------
1.8.2470    ORA-01403: no data found

Determining the Error Operations at a Replication Site

The following query lists the type of operation that was attempted for each call that caused an error at a replication site:

COLUMN CALLNO HEADING 'Call|Number' FORMAT 9999
COLUMN DEFERRED_TRAN_ID HEADING 'Deferred|Transaction|ID' FORMAT A11
COLUMN PACKAGENAME HEADING 'Package|Name' FORMAT A20
COLUMN PROCNAME HEADING 'Operation' FORMAT A15
COLUMN ORIGIN_TRAN_DB HEADING 'Origin|Database' FORMAT A15

SELECT /*+ ORDERED */ 
       C.CALLNO, 
       C.DEFERRED_TRAN_ID, 
       C.PACKAGENAME,
       C.PROCNAME, E.ORIGIN_TRAN_DB
    FROM DEFERROR E, DEFCALL C
    WHERE C.DEFERRED_TRAN_ID = E.DEFERRED_TRAN_ID
    AND C.CALLNO = E.CALLNO
    ORDER BY E.START_TIME;

Your output looks similar to the following:

       Deferred
  Call Transaction Package                              Origin
Number ID          Name                 Operation       Database
------ ----------- -------------------- --------------- ---------------
     0 1.8.2470    EMPLOYEES$RP         REP_UPDATE      mv4.world

Monitoring Performance in a Replication Environment

This section contains queries that you can run to monitor the performance of your replication environment.

Tracking the Average Number of Row Changes in a Replication Transaction

The following query shows the average number of row changes in a replication transaction since instance startup:

SELECT DECODE(TXNS_ENQUEUED, 0, 'No Transactions Enqueued',
       (CALLS_ENQUEUED / TXNS_ENQUEUED)) "Average Number of Row Changes" 
  FROM V$REPLQUEUE;

Your output looks similar to the following:

Average Number of Row Changes
-----------------------------
                        56.16

Note:

If this query returns 'No Transactions Enqueued', then no transactions have been enqueued since the start of the instance.

Tracking the Rate of Transactions Entering the Deferred Transactions Queue

The following query shows the average number of transactions per second entering at the deferred transactions queue at the current site since instance startup:

SELECT (R.TXNS_ENQUEUED / ((SYSDATE - I.STARTUP_TIME)*24*60*60)) "Average TPS" 
    FROM V$REPLQUEUE R, V$INSTANCE I;

Your output looks similar to the following:

Average TPS
-----------
        150

Determining the Average Network Traffic Created To Propagate a Transaction

Propagation of deferred transactions creates a certain amount of traffic on your network. Here, the network traffic created by a transaction is the number of bytes being sent and received and the number of network round trips needed to propagate the transaction.

A round trip is one or more consecutively sent messages followed by one or more consecutively received messages. For example, both of the following scenarios constitute only one round trip:

Site A sends one message to site B and then site B sends one message to site A.
Site A sends 20 messages to site B and then site B sends one message to site A.

These scenarios illustrate that the number of messages is irrelevant when evaluating the number of round trips, because the number of round trips is the number of back and forth communications between sites.

The following query shows the average network traffic created when propagating a transaction to the mv4.world remote master site:

SELECT  
 DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions',
 ((TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT)) "Average Bytes", 
 DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions', 
 (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT)) "Average Round Trips" 
 FROM DEFSCHEDULE WHERE DBLINK = 'mv4.world';

Your output looks similar to the following:

Average Bytes Average Round Trips
------------- -------------------
      69621.5                   5

Note:

If this query returns 'No transactions' in both columns, then no transactions have been propagated to the specified remote site since the statistics were last cleared.

Determining the Average Amount of Time to Apply Transactions at Remote Sites

Average latency is the average number of seconds between the first call of a transaction on the current site and the confirmation that the transaction was applied at the remote site. The first call begins when the user makes the first data manipulation language (DML) change, not when the transaction is committed.

The following query shows the average latency for applying transactions at the remote master site mv4.world:

SELECT AVG_LATENCY "Average Latency" 
    FROM DEFSCHEDULE 
    WHERE DBLINK='mv4.world';

Your output looks similar to the following:

Average Latency
---------------
           25.5

Determining the Percentage of Time the Parallel Propagation Job Spends Sleeping

When the parallel propagation coordinator is inactive, it is sleeping. You control the amount of time that the propagation coordinator sleeps using the delay_seconds parameter in the DBMS_DEFER_SYS.PUSH procedure.

The following query shows the percentage of time that the parallel propagation coordinator spends sleeping when propagating transactions to the mv4.world remote master site:

SELECT DECODE(AVG_THROUGHPUT, 0, NULL, 
       ((TOTAL_SLEEP_TIME / (TOTAL_TXN_COUNT / AVG_THROUGHPUT)) * 100)) 
       "Percent Sleep Time" 
  FROM DEFSCHEDULE WHERE DBLINK = 'mv4.world';

Your output looks similar to the following:

Percent Sleep Time
------------------
                 2

Of course, in this case, the parallel propagation coordinator is active 98% of the time.

Note:

If this query returns a NULL, then no transactions have been propagated to the specified remote site since the statistics were last cleared or since the last database startup.

Clearing the Statistics for a Remote Master Site in the DEFSCHEDULE View

To clear the propagation statistics in the DEFSCHEDULE view for a particular remote master site, use the CLEAR_PROP_STATISTICS procedure in the DBMS_DEFER_SYS package. For example, to clear the propagation statistics for the mv4.world remote master site, run the following procedure:

BEGIN
  DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (
   dblink => 'mv4.world');
END;
/

Monitoring Parallel Propagation of Deferred Transactions Using V$REPLPROP

The V$REPLPROP dynamic performance view provides information about current parallel propagation sessions.

Note:

The V$REPLPROP dynamic performance view is only relevant if you are using parallel propagation of deferred transactions. If you are using serial propagation, then this view is empty.

Determining the Databases to Which You Are Propagating Deferred Transactions

Run the following query to list the database link of each database to which you are currently propagating deferred transactions using parallel propagation:

SELECT DBLINK "Database Link"
    FROM V$REPLPROP 
    WHERE NAME LIKE '%Coordinator%';

Your output looks similar to the following:

Database Link
-----------------
mv4.world
NY.WORLD
HK.WORLD

Determining the Transactions Currently Being Propagated to a Remote Master

You can list the following information about the transactions that are currently being propagated to a specified remote master site using parallel propagation:

The transaction identification number of each transaction
The number of calls in each transaction
The percentage of processed calls in each transaction. The number in this column becomes larger as the calls in the transaction are processed. When the number reaches 100, all of the calls are processed.

The following query displays this information:

SELECT /*+ ORDERED */ P.XID "Tran Being Propagated",
       (MAX(C.CALLNO) + 1) "Number of Calls in Tran",
       (P.SEQUENCE/MAX(C.CALLNO) + 1) * 100 "% Processed Calls"
   FROM V$REPLPROP P, DEFCALL C
   WHERE P.NAME LIKE '%SLAVE%'
   AND P.DBLINK = 'mv4.world'
   AND C.DEFERRED_TRAN_ID = P.XID
   GROUP BY P.XID, P.SEQUENCE;

Your output looks similar to the following:

Tran Being Propagated  Number of Calls in Tran % Processed Calls
---------------------- ----------------------- -----------------
1.11.4264                                43357               78
1.15.4256                                23554               49

The transaction identification numbers should change as existing transactions are pushed and new transactions are processed. This query can be particularly useful if the any of the following conditions apply to your replication environment:

You push a large number of transactions on a regular basis.
You have some transactions that are very large.
You are simulating continuous push using asynchronous propagation.

If the first two bullets apply to your replication environment, then you can run this query to check if the slave processes are pushing the transactions. In this type of environment, the slave processes do not exist when they are not pushing transactions.

In replication environments that are simulating continuous push, the slave processes exist whenever there are transactions to push in the deferred transactions queue. When there are no transactions to push, the slave processes may not exist. So, when there are transactions to push, you can use this query to make sure the slave processes exist and are processing the transactions.

See Also:

Oracle9i Replication for more information about scheduling continuous push in your replication environment

10 Monitoring a Replication Environment

Monitoring Master Replication Environments

Monitoring Master Sites

Listing General Information About a Master Site

Monitoring Master Groups

Listing the Master Sites Participating in a Master Group

Listing General Information About Master Groups

Monitoring Masters

Listing Information About Materialized Views Based on a Master

Listing Information About the Materialized View Logs at a Master

Listing the Materialized Views That Use a Materialized View Log

Listing Information About the Deployment Templates at a Master

Monitoring Materialized View Sites

Listing General Information About a Materialized View Site

Listing General Information About Materialized View Groups

Listing Information About Materialized Views

Listing Master Information For Materialized Views

Listing the Properties of Materialized Views

Listing Information About the Refresh Groups at a Materialized View Site

Determining the Job ID for Each Refresh Job at a Materialized View Site

Determining Which Materialized Views Are Currently Refreshing

Monitoring Administrative Requests

Listing General Information About Administrative Requests

Determining the Cause of Administrative Request Errors

Listing General Information About the Job that Executes Administrative Requests

Checking the Definition of Each do_deferred_repcat_admin Job

Monitoring the Deferred Transactions Queue

Monitoring Transaction Propagation

Listing the Number of Deferred Transactions for Each Destination Master Site

Listing General Information About the Push Jobs at a Replication Site

Determining the Next Start Time and Interval for the Push Jobs

Determining the Total Number of Transactions Queued for Propagation

Monitoring Purges of Successfully Propagated Transactions

Listing General Information About the Purge Job

Checking the Definition of the Purge Job

Determining the Amount of Time Since the Last Purge

Determining the Total Number of Purged Transactions

Monitoring the Error Queue

Listing General Information About the Error Transactions at a Replication Site

Determining the Percentage of Error Transactions

Listing the Number of Error Transactions from Each Origin Master Site

Listing the Error Messages for the Error Transactions at a Replication Site

Determining the Error Operations at a Replication Site

Monitoring Performance in a Replication Environment

Tracking the Average Number of Row Changes in a Replication Transaction

Tracking the Rate of Transactions Entering the Deferred Transactions Queue

Determining the Average Network Traffic Created To Propagate a Transaction

Determining the Average Amount of Time to Apply Transactions at Remote Sites

Determining the Percentage of Time the Parallel Propagation Job Spends Sleeping

Clearing the Statistics for a Remote Master Site in the DEFSCHEDULE View

Monitoring Parallel Propagation of Deferred Transactions Using V$REPLPROP

Determining the Databases to Which You Are Propagating Deferred Transactions

Determining the Transactions Currently Being Propagated to a Remote Master

10
Monitoring a Replication Environment