| Oracle® Database Advanced Replication Management API Reference 10g Release 1 (10.1) Part Number B10733-01 |
|
|
View PDF |
| Oracle® Database Advanced Replication Management API Reference 10g Release 1 (10.1) Part Number B10733-01 |
|
|
View PDF |
This chapter illustrates how to monitor a replication a replication environment using the data dictionary.
This chapter contains these topics:
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:
This section contains queries that you can run to display information about master sites.
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. |
This section contains queries that you can run to display information about the master groups at a replication site.
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.
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. |
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.
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
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 |
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
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.
This section contains queries that you can run to display information about the materialized view sites. This section contains the following topics:
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
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
This section contains queries that you can run to display information about the materialized views at a replication site.
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
You can use the query in this section to list the following information about the materialized views at the current replication site:
COMPLETE, FORCE, FAST, or NEVERRun 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
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.
You can use the query in this section to list the following information about the refresh jobs at a materialized view site:
DBMS_JOBS package is assigned a unique identification number.mviewadmin.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.
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 |
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:
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.
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
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:
do_deferred_repcat_admin job. Each job created by the DBMS_JOBS package is assigned a unique identification number.do_deferred_repcat_admin job, either normal or brokendo_deferred_repcat_admin job will rundo_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 ------- ---------- ------- ----------------------- -------------------- 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.
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);
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:
This section contains queries that you can run to display information about propagation of transactions in the deferred transactions queue.
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. |
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:
DBMS_JOBS package is assigned a unique identification number.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.
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:
DBMS_JOBS package is assigned a unique identification number.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)
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. |
This section contains queries that you can run to display information about purges of successfully propagated transactions from the deferred transactions queue.
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:
DBMS_JOBS package is assigned a unique identification number.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.
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;
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
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
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:
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.
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 |
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
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
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
This section contains queries that you can run to monitor the performance of your replication environment. This section contains the following topics:
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 |
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
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 |
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
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 |
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; /
The V$REPLPROP dynamic performance view provides information about current parallel propagation sessions.
|
Note: The |
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
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 |
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|