| Oracle® Streams Replication Administrator's Guide 10g Release 1 (10.1) Part Number B10728-01 |
|
|
View PDF |
| Oracle® Streams Replication Administrator's Guide 10g Release 1 (10.1) Part Number B10728-01 |
|
|
View PDF |
This chapter provides information about the static data dictionary views and dynamic performance views related to Streams replication. You can use these views to monitor your Streams replication environment. This chapter also illustrates example queries that you may want to use to monitor your Streams replication environment.
This chapter contains these topics:
See Also:
|
The following sections contain queries that you can run to monitor supplemental logging at a source database:
Supplemental logging places additional column data into a redo log when an operation is performed. The capture process captures this additional information and places it in LCRs. An apply process that applies captured LCRs may need this additional information to schedule or apply changes correctly.
To check whether one or more log groups are specified for the table at the source database, run the following query:
COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20 COLUMN TABLE_NAME HEADING 'Table' FORMAT A15 COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14 COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20 SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS, 'ALWAYS', 'Unconditional', 'CONDITIONAL', 'Conditional') ALWAYS, LOG_GROUP_TYPE FROM DBA_LOG_GROUPS;
Your output looks similar to the following:
Conditional or Log Group Table Unconditional Type of Log Group -------------------- --------------- -------------- -------------------- LOG_GROUP_DEP_PK DEPARTMENTS Unconditional USER LOG GROUP SYS_C002105 REGIONS Unconditional PRIMARY KEY LOGGING SYS_C002106 REGIONS Conditional FOREIGN KEY LOGGING SYS_C002110 LOCATIONS Unonditional ALL COLUMN LOGGING SYS_C002111 COUNTRIES Conditional ALL COLUMN LOGGING LOG_GROUP_JOBS_CR JOBS Conditional USER LOG GROUP
If the output for the type of log group shows how the log group was created:
USER LOG GROUP, then the log group was created using the ADD SUPPLEMENTAL LOG GROUP clause of the ALTER TABLE statement.ADD SUPPLEMENTAL LOG DATA clause of the ALTER TABLE statement.If the type of log group is USER LOG GROUP, then you can list the columns in the log group by querying the DBA_LOG_GROUP_COLUMNS data dictionary view.
To display the database supplemental logging specifications, query the V$DATABASE dynamic performance view, as in the following example:
COLUMN log_min HEADING 'Minimum|Supplemental|Logging?' FORMAT A12 COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging?' FORMAT A12 COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging?' FORMAT A12 COLUMN log_ui HEADING 'Unique Key|Supplemental|Logging?' FORMAT A12 COLUMN log_all HEADING 'All Columns|Supplemental|Logging?' FORMAT A12 SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min, SUPPLEMENTAL_LOG_DATA_PK log_pk, SUPPLEMENTAL_LOG_DATA_FK log_fk, SUPPLEMENTAL_LOG_DATA_UI log_ui, SUPPLEMENTAL_LOG_DATA_ALL log_all FROM V$DATABASE;
Your output looks similar to the following:
Minimum Primary Key Foreign Key Unique Key All Columns Supplemental Supplemental Supplemental Supplemental Supplemental Logging? Logging? Logging? Logging? Logging? ------------ ------------ ------------ ------------ ------------ YES YES YES YES NO
These results show that minimum, primary key, foreign key, and unique key columns are being supplementally logged for all of the tables in the database. However, all columns are not being supplementally logged.
The following sections contain queries that you can run to monitor an apply process in a Stream replication environment:
You can designate a substitute key at a destination database, which is a column or set of columns that Oracle can use to identify rows in the table during apply. Substitute key columns can be used to specify key columns for a table that has no primary key, or they can be used instead of a table's primary key when the table is processed by any apply process at a destination database.
To display all of the substitute key columns specified at a destination database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A20 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20 COLUMN COLUMN_NAME HEADING 'Substitute Key Name' FORMAT A20 COLUMN APPLY_DATABASE_LINK HEADING 'Database Link|for Remote|Apply' FORMAT A15 SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK FROM DBA_APPLY_KEY_COLUMNS ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Database Link for Remote Table Owner Table Name Substitute Key Name Apply -------------------- -------------------- -------------------- --------------- HR DEPARTMENTS DEPARTMENT_NAME HR DEPARTMENTS LOCATION_ID HR EMPLOYEES FIRST_NAME HR EMPLOYEES LAST_NAME HR EMPLOYEES HIRE_DATE
See Also:
|
This section contains queries that display information about apply process DML handlers and DDL handlers.
| See Also:
Oracle Streams Concepts and Administration for more information about DML and DDL handlers |
When you specify a local DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you either can specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally, when appropriate. A specific DML handler takes precedence over a generic DML handler. A DML is run for a specified operation on a specific table.
To display the DML handler for each apply process that applies changes locally in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A11 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10 COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9 COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A25 COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15 SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE, APPLY_NAME FROM DBA_APPLY_DML_HANDLERS WHERE ERROR_HANDLER = 'N' AND APPLY_DATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process Owner Table Name Operation Handler Procedure Name ----------- ---------- --------- ------------------------- -------------- HR LOCATIONS UPDATE "STRMADMIN"."HISTORY_DML"
Because Apply Process Name is NULL for the strmadmin.history_dml DML handler, this handler is a general handler that runs for all of the local apply processes.
To display the DDL handler for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN DDL_HANDLER HEADING 'DDL Handler' FORMAT A40 SELECT APPLY_NAME, DDL_HANDLER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name DDL Handler -------------------- ---------------------------------------- STREP01_APPLY "STRMADMIN"."HISTORY_DDL"
You can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package. When you use this procedure, conflict detection is stopped for the specified columns for all apply processes at a destination database. To display each column for which conflict detection has been stopped, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A15 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20 COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20 COLUMN COMPARE_OLD_ON_DELETE HEADING 'Compare|Old On|Delete' FORMAT A7 COLUMN COMPARE_OLD_ON_UPDATE HEADING 'Compare|Old On|Update' FORMAT A7 SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, COMPARE_OLD_ON_DELETE, COMPARE_OLD_ON_UPDATE FROM DBA_APPLY_TABLE_COLUMNS WHERE APPLY_DATABASE_LINK IS NULL;
Your output should look similar to the following:
Compare Compare Old On Old On Table Owner Table Name Column Name Delete Update --------------- -------------------- -------------------- ------- ------- HR EMPLOYEES COMMISSION_PCT NO NO HR EMPLOYEES EMAIL NO NO HR EMPLOYEES FIRST_NAME NO NO HR EMPLOYEES HIRE_DATE NO NO HR EMPLOYEES JOB_ID NO NO HR EMPLOYEES LAST_NAME NO NO HR EMPLOYEES PHONE_NUMBER NO NO HR EMPLOYEES SALARY NO NO
When you specify an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package, the update conflict handler is run for all apply processes in the database, when a relevant conflict occurs.
The query in this section displays all of the columns for which conflict resolution has been specified using a prebuilt update conflict handler. That is, it shows the columns in all of the column lists specified in the database. This query also shows the type of prebuilt conflict handler specified and the resolution column specified for the column list.
To display information about all of the update conflict handlers in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12 COLUMN METHOD_NAME HEADING 'Method' FORMAT A12 COLUMN RESOLUTION_COLUMN HEADING 'Resolution|Column' FORMAT A13 COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30 SELECT OBJECT_OWNER, OBJECT_NAME, METHOD_NAME, RESOLUTION_COLUMN, COLUMN_NAME FROM DBA_APPLY_CONFLICT_COLUMNS ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;
Your output looks similar to the following:
Table Resolution Owner Table Name Method Column Column Name ----- ------------ ------------ ------------- ------------------------------ HR COUNTRIES MAXIMUM TIME COUNTRY_NAME HR COUNTRIES MAXIMUM TIME REGION_ID HR COUNTRIES MAXIMUM TIME TIME HR DEPARTMENTS MAXIMUM TIME DEPARTMENT_NAME HR DEPARTMENTS MAXIMUM TIME LOCATION_ID HR DEPARTMENTS MAXIMUM TIME MANAGER_ID HR DEPARTMENTS MAXIMUM TIME TIME
A buffered queue includes the following storage areas:
SYS.AnyData queue that contains only captured LCRsSYS.AnyData queue that stores captured LCRs that have spilled from memoryA buffered queue contains only captured LCRs and enables the Oracle database to optimize captured LCRs by buffering them in the SGA instead of always storing them in a queue table. This buffering of captured LCRs happens in any database where captured LCRs are staged in a SYS.AnyData queue, such as a source database, an intermediate database, or a destination database. If you have configured a Streams pool for a database, then buffered queues reside in the Streams pool. If you have not configured a Streams pool for a database, then buffered queues reside in the shared pool.
Captured events are always stored in a buffered queue, but user-enqueued LCR events and user-enqueued non-LCR events are always staged in queue tables, not in buffered queues. Captured events in a buffered queue may spill from memory if they have been staged in the buffered queue for a period of time without being dequeued, or if there is not enough space in memory to hold all of the captured events. Captured events that spill from memory are stored in the appropriate queue table.
The following sections describe queries that enable you to monitor buffered queues:
You cannot access the LCRs in a buffered queue directly, but the V$BUFFERED_QUEUES dynamic performance view enables you to obtain information about the number of LCRs in a buffered queue. You can determine the following information about each buffered queue in a database by running the query in this section:
To display this information, run the following query:
COLUMN QUEUE_SCHEMA HEADING 'Queue Owner' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15 COLUMN MEM_MSG HEADING 'LCRs in Memory' FORMAT 99999999 COLUMN SPILL_MSGS HEADING 'Spilled LCRs' FORMAT 99999999 COLUMN NUM_MSGS HEADING 'Total Captured LCRs|in Buffered Queue' FORMAT 99999999 SELECT QUEUE_SCHEMA, QUEUE_NAME, (NUM_MSGS - SPILL_MSGS) MEM_MSG, SPILL_MSGS, NUM_MSGS FROM V$BUFFERED_QUEUES;
Your output looks similar to the following:
Total Captured LCRs Queue Owner Queue Name LCRs in Memory Spilled LCRs in Buffered Queue --------------- --------------- -------------- ------------ ------------------- STRMADMIN STREAMS_QUEUE 534 21 555
A capture process is a queue publisher that enqueues captured LCRs into a buffered queue. These LCRs may be propagated to other queues subsequently. By querying the V$BUFFERED_PUBLISHERS dynamic performance view, you can display each capture process that captured the LCRs in the buffered queue. These LCRs may have been captured at the local database, or they may have been captured at a remote database and propagated to the queue specified in the query.
The query in this section displays the following information about each of these capture processes:
To display this information, run the following query:
COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A13 COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A27 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15 COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999 COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 99999999 SELECT SENDER_NAME, SENDER_ADDRESS, QUEUE_NAME, CNUM_MSGS, LAST_ENQUEUED_MSG FROM V$BUFFERED_PUBLISHERS;
Your output looks similar to the following:
Number Last Capture of LCRs Enqueued Process Sender Queue Queue Name Enqueued LCR ------------- --------------------------- --------------- --------- --------- CAPTURE_HR "STRMADMIN"."STREAMS_QUEUE" STREAMS_QUEUE 382 844 @MULT3.NET CAPTURE_HR "STRMADMIN"."STREAMS_QUEUE" STREAMS_QUEUE 387 840 @MULT2.NET CAPTURE_HR STREAMS_QUEUE 75 833
This output shows following:
382 LCRs from the capture_hr capture process running on a remote database were propagated from a queue named streams_queue on database mult3.net to the local queue named streams_queue. The message number of the last enqueued LCR from this sender was 844.387 LCRs from the capture_hr capture process running on a remote database were propagated from a queue named streams_queue on database mult2.net to the local queue named streams_queue. The message number of the last enqueued LCR from this sender was 840.75 LCRs from the local capture_hr capture process were enqueued into the local queue named streams_queue. The capture process is local because the Sender Queue column is NULL. The message number of the last enqueued LCR from this capture process was 833.The query in this section displays the following general information about each propagation that sends captured events from a buffered queue in the local database:
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15 COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10 COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15 COLUMN DBLINK HEADING 'Database|Link' FORMAT A10 COLUMN SCHEDULE_STATUS HEADING 'Schedule Status' FORMAT A20 SELECT p.PROPAGATION_NAME, s.QUEUE_SCHEMA, s.QUEUE_NAME, s.DBLINK, s.SCHEDULE_STATUS FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s WHERE p.DESTINATION_DBLINK = s.DBLINK AND p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND p.SOURCE_QUEUE_NAME = s.QUEUE_NAME;
Your output looks similar to the following:
Queue Queue Database Propagation Owner Name Link Schedule Status --------------- ---------- --------------- ---------- -------------------- MULT1_TO_MULT3 STRMADMIN STREAMS_QUEUE MULT3.NET SCHEDULE ENABLED MULT1_TO_MULT2 STRMADMIN STREAMS_QUEUE MULT2.NET SCHEDULE ENABLED
The query in this section displays the number of events and the number of bytes sent by each propagation that sends captured events from a buffered queue in the local database:
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15 COLUMN DBLINK HEADING 'Database|Link' FORMAT A10 COLUMN TOTAL_MSGS HEADING 'Total|Events' FORMAT 99999999 COLUMN TOTAL_BYTES HEADING 'Total|Bytes' FORMAT 99999999 SELECT p.PROPAGATION_NAME, s.QUEUE_NAME, s.DBLINK, s.TOTAL_MSGS, s.TOTAL_BYTES FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s WHERE p.DESTINATION_DBLINK = s.DBLINK AND p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND p.SOURCE_QUEUE_NAME = s.QUEUE_NAME;
Your output looks similar to the following:
Queue Database Total Total Propagation Name Link Events Bytes --------------- --------------- ---------- --------- --------- MULT1_TO_MULT3 STREAMS_QUEUE MULT3.NET 79 71467 MULT1_TO_MULT2 STREAMS_QUEUE MULT2.NET 79 71467
The query in this section displays the amount of time that a propagation sending captured events spends performing various tasks. Each propagation sends events from the source queue to the destination queue. Specifically, the query displays the following information:
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A13 COLUMN DBLINK HEADING 'Database|Link' FORMAT A9 COLUMN ELAPSED_DEQUEUE_TIME HEADING 'Dequeue|Time' FORMAT 99999999.99 COLUMN ELAPSED_PICKLE_TIME HEADING 'Pickle|Time' FORMAT 99999999.99 COLUMN ELAPSED_PROPAGATION_TIME HEADING 'Propagation|Time' FORMAT 99999999.99 SELECT p.PROPAGATION_NAME, s.QUEUE_NAME, s.DBLINK, (s.ELAPSED_DEQUEUE_TIME / 100) ELAPSED_DEQUEUE_TIME, (s.ELAPSED_PICKLE_TIME / 100) ELAPSED_PICKLE_TIME, (s.ELAPSED_PROPAGATION_TIME / 100) ELAPSED_PROPAGATION_TIME FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s WHERE p.DESTINATION_DBLINK = s.DBLINK AND p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND p.SOURCE_QUEUE_NAME = s.QUEUE_NAME;
Your output looks similar to the following:
Queue Database Dequeue Pickle Propagation Propagation Name Link Time Time Time --------------- ------------- --------- ------------ ------------ ------------ MULT1_TO_MULT2 STREAMS_QUEUE MULT2.NET 30.65 45.10 10.91 MULT1_TO_MULT3 STREAMS_QUEUE MULT3.NET 25.36 37.07 8.35
Propagations are queue subscribers that may dequeue captured LCRs from a queue. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the propagations that may dequeue captured LCRs from a queue.
You also can use the V$BUFFERED_SUBSCRIBERS dynamic performance view to determine the performance of a propagation. For example, if a propagation has a high number of spilled LCRs, then that propagation may not be dequeuing LCRs fast enough from the buffered queue. Spilling LCRs to a queue table has a negative impact on the performance of your Streams environment.
Apply processes also are queue subscribers. This query joins with the DBA_PROPAGATION and V$BUFFERED_QUEUES views to limit the output to propagations only and to show the propagation name of each propagation.
The query in this section displays the following information about each propagation that can dequeue captured LCRs from queues:
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15 COLUMN SUBSCRIBER_ADDRESS HEADING 'Destination|Database' FORMAT A11 COLUMN CURRENT_ENQ_SEQ HEADING 'Current|Enqueued|Sequence' FORMAT 99999999 COLUMN LAST_BROWSED_SEQ HEADING 'Last|Browsed|Sequence' FORMAT 99999999 COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999 COLUMN NUM_MSGS HEADING 'Number of|LCRs in|Queue|(Current)' FORMAT 99999999 COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled LCRs|(Cumulative)' FORMAT 99999999 SELECT p.PROPAGATION_NAME, s.SUBSCRIBER_ADDRESS, s.CURRENT_ENQ_SEQ, s.LAST_BROWSED_SEQ, s.LAST_DEQUEUED_SEQ, s.NUM_MSGS, s.TOTAL_SPILLED_MSG FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s, V$BUFFERED_QUEUES q WHERE q.QUEUE_ID = s.QUEUE_ID AND p.SOURCE_QUEUE_OWNER = q.QUEUE_SCHEMA AND p.SOURCE_QUEUE_NAME = q.QUEUE_NAME AND p.DESTINATION_DBLINK = s.SUBSCRIBER_ADDRESS;
Your output looks similar to the following:
Number of Current Last Last LCRs in Number of Destination Enqueued Browsed Dequeued Queue Spilled LCRs Propagation Database Sequence Sequence Sequence (Current) (Cumulative) --------------- ----------- --------- --------- --------- --------- ------------ MULT1_TO_MULT2 MULT2.NET 157 144 129 24 0 MULT1_TO_MULT3 MULT3.NET 98 88 81 53 0
|
Note: If there are multiple propagations using the same database link but to a different queue at the destination, then the statistics returned by this query are approximate rather than accurate. |
The query in this section displays the amount of time that each propagation receiving captured events spends performing various tasks. Each propagation receives the events and enqueues them into the destination queue for the propagation. Specifically, the query displays the following information:
To display this information, run the following query:
COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20 COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A15 COLUMN ELAPSED_UNPICKLE_TIME HEADING 'Unpickle|Time' FORMAT 99999999.99 COLUMN ELAPSED_RULE_TIME HEADING 'Rule|Evaluation|Time' FORMAT 99999999.99 COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Enqueue|Time' FORMAT 99999999.99 SELECT SRC_QUEUE_NAME, SRC_DBNAME, (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME, (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME, (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME FROM V$PROPAGATION_RECEIVER;
Your output looks similar to the following:
Source Rule Queue Source Unpickle Evaluation Enqueue Name Database Time Time Time -------------------- --------------- ------------ ------------ ------------ "STRMADMIN"."STREAMS MULT2.NET 45.65 5.44 45.85 _QUEUE" "STRMADMIN"."STREAMS MULT3.NET 53.35 8.01 50.41 _QUEUE"
Apply processes are queue subscribers that may dequeue captured LCRs from a queue. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the apply processes that may dequeue captured LCRs from a queue.
You also can use the V$BUFFERED_SUBSCRIBERS dynamic performance view to determine the performance of an apply process. For example, if an apply process has a high number of spilled LCRs, then that apply process may not be dequeuing LCRs fast enough from the buffered queue. Spilling LCRs to a queue table has a negative impact on the performance of your Streams environment.
This query joins with the V$BUFFERED_QUEUES views to show the name of the queue. In addition, propagations also are queue subscribers, and this query limits the output to subscribers where the SUBSCRIBER_ADDRESS is NULL to return only apply processes.
The query in this section displays the following information about the apply processes that can dequeue captured LCRs from queues:
To display this information, run the following query:
COLUMN SUBSCRIBER_NAME HEADING 'Apply Process' FORMAT A16 COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10 COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15 COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999 COLUMN NUM_MSGS HEADING 'Number of|LCRs in|Queue' FORMAT 99999999 COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled LCRs' FORMAT 99999999 SELECT s.SUBSCRIBER_NAME, q.QUEUE_SCHEMA, q.QUEUE_NAME, s.LAST_DEQUEUED_SEQ, s.NUM_MSGS, s.TOTAL_SPILLED_MSG FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a WHERE q.QUEUE_ID = s.QUEUE_ID AND s.SUBSCRIBER_ADDRESS IS NULL AND s.SUBSCRIBER_NAME = a.APPLY_NAME;
Your output looks similar to the following:
Last Number of Queue Queue Dequeued LCRs in Number of Apply Process Owner Name Sequence Queue Spilled LCRs ---------------- ---------- --------------- --------- --------- ------------ APPLY_FROM_MULT3 STRMADMIN STREAMS_QUEUE 49 148 0 APPLY_FROM_MULT2 STRMADMIN STREAMS_QUEUE 85 241 1
The following sections contain queries that you can run to display the Streams tag for the current session and the default tag for each apply process:
See Also:
|
You can display the tag value generated in all redo entries for the current session by querying the DUAL view:
SELECT DBMS_STREAMS.GET_TAG FROM DUAL;
Your output looks similar to the following:
GET_TAG -------------------------------------------------------------------------------- 1D
You also can determine the tag for a session by calling the DBMS_STREAMS.GET_TAG function.
You can get the default tag for all redo entries generated by each apply process by querying for the APPLY_TAG value in the DBA_APPLY data dictionary view. For example, to get the hexadecimal value of the default tag generated in the redo entries by each apply process, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30 COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30 SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name Tag Value ------------------------------ ------------------------------ APPLY_FROM_MULT2 00 APPLY_FROM_MULT3 00
A handler or rule-based transformation function associated with an apply process can get the tag by calling the DBMS_STREAMS.GET_TAG function.
The following sections contain queries that you can run to determine which database objects are prepared for instantiation at a source database and the instantiation SCN for database objects at a destination database:
You prepare a database object for instantiation using one of the following procedures in the DBMS_CAPTURE_ADM package:
PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.PREPARE_SCHEMA_INSTANTIATION prepares all of the database objects in a schema for instantiation.PREPARE_GLOBAL_INSTANTIATION prepares all of the database objects in a database for instantiation.To determine which database objects have been prepared for instantiation, query the following corresponding data dictionary views:
For example, to list all of the tables that have been prepared for instantiation, the SCN for the time when each table was prepared, and the time when each table was prepared, run the following query:
COLUMN TABLE_OWNER HEADING 'Table Owner' FORMAT A15 COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15 COLUMN SCN HEADING 'Prepare SCN' FORMAT 99999999999 COLUMN TIMESTAMP HEADING 'Time Ready for|Instantiation' SELECT TABLE_OWNER, TABLE_NAME, SCN, TO_CHAR(TIMESTAMP, 'HH24:MI:SS MM/DD/YY') TIMESTAMP FROM DBA_CAPTURE_PREPARED_TABLES;
Your output looks similar to the following:
Time Ready for Table Owner Table Name Prepare SCN Instantiation --------------- --------------- ----------------- ----------------- HR COUNTRIES 196655 12:59:30 02/28/02 HR DEPARTMENTS 196658 12:59:30 02/28/02 HR EMPLOYEES 196659 12:59:30 02/28/02 HR JOBS 196660 12:59:30 02/28/02 HR JOB_HISTORY 196661 12:59:30 02/28/02 HR LOCATIONS 196662 12:59:30 02/28/02 HR REGIONS 196664 12:59:30 02/28/02
An instantiation SCN is set at a destination database. It controls which captured LCRs for a table are ignored by an apply process and which captured LCRs for a database object are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.
You can set an instantiation SCN using one of the following procedures in the DBMS_APPLY_ADM package:
SET_TABLE_INSTANTIATION_SCN sets the instantiation SCN for a single table.SET_SCHEMA_INSTANTIATION_SCN sets the instantiation SCN for a schema, and, optionally, for all of the database objects in the schema.SET_GLOBAL_INSTANTIATION_SCN sets the instantiation SCN for a database, and, optionally, for all of the database objects in the database.To determine which database objects have a set instantiation SCN, query the following corresponding data dictionary views:
The following query lists each table for which an instantiation SCN has been set at a destination database and the instantiation SCN for each table:
COLUMN SOURCE_DATABASE HEADING 'Source Database' FORMAT A15 COLUMN SOURCE_OBJECT_OWNER HEADING 'Object Owner' FORMAT A15 COLUMN SOURCE_OBJECT_NAME HEADING 'Object Name' FORMAT A15 COLUMN INSTANTIATION_SCN HEADING 'Instantiation SCN' FORMAT 99999999999 SELECT SOURCE_DATABASE, SOURCE_OBJECT_OWNER, SOURCE_OBJECT_NAME, INSTANTIATION_SCN FROM DBA_APPLY_INSTANTIATED_OBJECTS WHERE APPLY_DATABASE_LINK IS NULL;
Your output looks similar to the following:
Source Database Object Owner Object Name Instantiation SCN --------------- --------------- --------------- ----------------- DBS1.NET HR REGIONS 196660 DBS1.NET HR COUNTRIES 196660 DBS1.NET HR LOCATIONS 196660
Oracle Flashback Query enables you to view and repair historical data. You can perform queries on a database as of a certain clock time or system change number (SCN). In a Streams single source replication environment, you can use Flashback Query at the source database and a destination database at a past time when the replicated database objects should be identical.
Running the queries at corresponding SCNS at the source and destination databases can be used to determine whether all of the changes to the replicated objects performed at the source database have been applied at the destination database. If there are apply errors at the destination database, then such a Flashback Query can show how the replicated objects looked at the time when the error was raised. This information could be useful in determining the cause of the error and the best way to correct the error.
Running a Flashback Query at each database can also check whether tables have certain rows at the corresponding SCNs. If the table data does not match at the corresponding SCNs, then there is a problem with the replication environment.
To run queries, the Streams replication environment must have the following characteristics:
DBMS_FLASHBACK package.UNDO_RETENTION initialization parameter at each database must be set to a value that is large enough to perform the Flashback Query.Because Streams replication is asynchronous, you cannot use a past time in the Flashback Query. However, you can use the GET_SCN_MAPPING procedure in the DBMS_STREAMS_ADM package to determine the SCN at the destination database that corresponds to an SCN at the source database.
These instructions assume that you know the SCN for the Flashback Query at the source database. Using this SCN, you can determine the corresponding SCN for the Flashback Query at the destination database. To run these queries, complete the following steps:
GET_SCN_MAPPING procedure requires that this redo log file be available.GET_SCN_MAPPING procedure. In this example, assume that the SCN for the source database is 52073983 and that the name of the apply process that applies changes from the source database is strm01_apply:
SET SERVEROUTPUT ON DECLARE dest_scn NUMBER; start_scn NUMBER; dest_skip DBMS_UTILITY.NAME_ARRAY; BEGIN DBMS_STREAMS_ADM.GET_SCN_MAPPING( apply_name => 'strm01_apply', src_pit_scn => '52073983', dest_instantiation_scn => dest_scn, dest_start_scn => start_scn, dest_skip_txn_ids => dest_skip); IF dest_skip.count = 0 THEN DBMS_OUTPUT.PUT_LINE('No Skipped Transactions'); DBMS_OUTPUT.PUT_LINE('Destination SCN: ' || dest_scn); ELSE DBMS_OUTPUT.PUT_LINE('Destination SCN invalid for Flashback Query.'); DBMS_OUTPUT.PUT_LINE('At least one transaction was skipped.'); END IF; END; /
If a valid destination SCN is returned, then proceed to Step 3.
If the destination SCN was not valid for Flashback Query because one or more transactions were skipped by the apply process, then the apply process parameter commit_serialization was set to none, and non-dependent transactions have been applied out of order. There is at least one transaction with a source commit SCN less than src_pit_scn that was committed at the destination database after the returned dest_instantiation_scn. Therefore, tables may not be the same at the source and destination databases for the specified source SCN. You may choose a different source SCN and restart at Step 1.
See Also:
|
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|