10 Monitoring a Replication Environment

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 can 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
------------------------- ------- ------ ----- ------ ----------
ORC1.EXAMPLE.COM                5      0    37      0         53

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.EXAMPLE.COM          Y
HR_REPG              ORC2.EXAMPLE.COM          N
HR_REPG              ORC3.EXAMPLE.COM          N

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

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 can 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) 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', '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

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 perform a fast refresh on 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
MLOG$_REGIONS        HR    REGIONS         NO  YES     NO     NO

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

• 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.

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.EXAMPLE.COM              YES
DEPARTMENTS_MV1 HR         @ORC1.EXAMPLE.COM              YES
EMPLOYEES_MV1   HR         @ORC1.EXAMPLE.COM              YES
JOBS_MV1        HR         @ORC1.EXAMPLE.COM              YES
JOB_HISTORY_MV1 HR         @ORC1.EXAMPLE.COM              YES
LOCATIONS_MV1   HR         @ORC1.EXAMPLE.COM              YES
REGIONS_MV1     HR         @ORC1.EXAMPLE.COM              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
--------------- ---------- ---------- ---------- --------- ---------------
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

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

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.EXAMPLE.COM                                      1
ORC3.EXAMPLE.COM                                      1

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

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_JOB 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
------- ---------- ------- ----------------------- -------------------------
     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

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.EXAMPLE.COM
ORC3.EXAMPLE.COM

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.example.com'
   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 processes are pushing the transactions. In this type of environment, the processes do not exist when they are not pushing transactions.

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