Skip Headers

Oracle® Database Advanced Replication Management API Reference
10g Release 1 (10.1)

Part Number B10733-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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

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.

This section contains the following topics:

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:

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.

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
------------------------- ------- ------ ----- ------ ----------
ORC1.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_REPG              ORC1.WORLD                Y
HR_REPG              ORC2.WORLD                N
HR_REPG              ORC3.WORLD                N

This list indicates that orc1.world is the master definition site for the hr_repg master group, which also includes the master sites orc2.world and orc3.world.

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:

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.

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', 'MATERIALIZED VIEW')
        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', 'MATERIALIZED VIEW') 
        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_REPG                  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:

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:

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
MLOG$_REGIONS        HR    REGIONS         NO  YES     NO     NO

See Also:

Oracle Database Advanced 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:

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                           Mview
Log Table            Log Owner  Master                  ID Mview Name
-------------------- ---------- -------------------- ----- --------------------
MLOG$_COUNTRIES      HR         COUNTRIES               21 COUNTRIES_MV1
MLOG$_DEPARTMENTS    HR         DEPARTMENTS             22 DEPARTMENTS_MV1
MLOG$_EMPLOYEES      HR         EMPLOYEES               23 EMPLOYEES_MV1
MLOG$_JOBS           HR         JOBS                    24 JOBS_MV1
MLOG$_JOB_HISTORY    HR         JOB_HISTORY             25 JOB_HISTORY_MV1
MLOG$_LOCATIONS      HR         LOCATIONS               26 LOCATIONS_MV1
MLOG$_REGIONS        HR         REGIONS                 27 REGIONS_MV1

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:

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. This section contains the following topics:

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:

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:

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_REPG    ORC1.WORLD                ASYNCHRONOUS

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?
--------------- ---------- ------------------------------ ----------------
COUNTRIES_MV1   HR         @ORC1.WORLD                    YES
DEPARTMENTS_MV1 HR         @ORC1.WORLD                    YES
EMPLOYEES_MV1   HR         @ORC1.WORLD                    YES
JOBS_MV1        HR         @ORC1.WORLD                    YES
JOB_HISTORY_MV1 HR         @ORC1.WORLD                    YES
LOCATIONS_MV1   HR         @ORC1.WORLD                    YES
REGIONS_MV1     HR         @ORC1.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:

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
--------------- ---------- ---------- ---------- --------- ---------------
COUNTRIES_MV1   HR         FAST       Y          21-OCT-03 FAST
DEPARTMENTS_MV1 HR         FAST       Y          21-OCT-03 FAST
EMPLOYEES_MV1   HR         FAST       Y          21-OCT-03 FAST
JOBS_MV1        HR         FAST       Y          21-OCT-03 FAST
JOB_HISTORY_MV1 HR         FAST       Y          21-OCT-03 FAST
LOCATIONS_MV1   HR         FAST       Y          21-OCT-03 FAST
REGIONS_MV1     HR         FAST       Y          21-OCT-03 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 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       24-OCT-2003 07:18:44 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 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. This section contains the following topics:

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 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  ORC3.WORLD

You can use the DO_DEFERRED_REPCAT_ADMIN procedure in the DBMS_REPCAT package to execute administrative requests.

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 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
------- ---------- ------- ----------------------- --------------------
     24 REPADMIN   N       24-OCT-2003 07:23:48 AM 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_REPG"', 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. This section contains the following topics:

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
--------------------------------------------- ---------
ORC2.WORLD                                            1
ORC3.WORLD                                            1


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 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?
------- ---------- ---------------------------------------- -------
     22 REPADMIN   ORC2.WORLD                               N
     23 REPADMIN   ORC3.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 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
------- ---------------------- ----------------------- -------------------------
     22 ORC2.WORLD             24-OCT-2003 07:23:48 AM SYSDATE + (1/144)
     23 ORC3.WORLD             24-OCT-2003 07:23:48 AM SYSDATE + (1/144)

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 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
------- ---------- ------- ----------------------- -------------------------
     21 REPADMIN   N       24-OCT-2003 07:42:18 AM 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.

This section contains the following topics:

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    ORC2.WORLD      ORC1.WORLD      22-Oct-2003 07:19:14      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', 'ORC1.WORLD');

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

EXECUTE DBMS_DEFER_SYS.DELETE_ERROR('1.8.2470', 'ORC1.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 orc2.world:

SELECT DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions', 
       (TOTAL_ERROR_COUNT/TOTAL_TXN_COUNT)*100) "ERROR PERCENTAGE" 
  FROM DEFSCHEDULE 
  WHERE DBLINK = 'ORC2.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
--------------------------------------------- ----------------
ORC2.WORLD                                                   1
ORC3.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      ORC2.WORLD

Monitoring Performance in a Replication Environment

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

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 for each 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:

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 orc2.world remote master site:

COLUMN AV_BYTES HEADING 'Average Bytes' FORMAT 999999999
COLUMN AV_TRIPS HEADING 'Average Round Trips' FORMAT 9999999

SELECT  
 DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions',
 ((TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT)) AV_BYTES, 
 DECODE(TOTAL_TXN_COUNT, 0, 'No Transactions', 
 (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT)) AV_TRIPS 
 FROM DEFSCHEDULE WHERE DBLINK = 'ORC2.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 orc2.world:

SELECT AVG_LATENCY "Average Latency" 
    FROM DEFSCHEDULE 
    WHERE DBLINK='ORC2.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 orc2.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 = 'ORC2.WORLD'; 

Your output looks similar to the following:

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

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 orc2.world remote master site, run the following procedure:

BEGIN
DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (
   dblink => 'ORC2.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
-----------------
ORC2.WORLD
ORC3.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 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:

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:

Oracle Database Advanced Replication for more information about scheduling continuous push in your replication environment