| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
This chapter provides information about the static data dictionary views and dynamic performance views related to Streams. You can use these views to monitor your Streams environment. This chapter also illustrates example queries that you may want to use to monitor your Streams environment.
This chapter contains these topics:
|
Note: The Streams tool in Oracle Enterprise Manager is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information. |
| See Also:
Oracle9i Database Reference for information about the data dictionary views described in this chapter |
The following table lists the Streams static data dictionary views.
The following list includes the Streams dynamic performance views
The following sections contain queries that you can run to display information about a capture process:
You can display the following general information about each capture process in a database by running the query in this section:
ENABLE, DISABLED, or ABORTEDTo display this general information about each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A20 COLUMN RULE_SET_NAME HEADING 'Capture|Process|Rule Set' FORMAT A15 COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15 SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, STATUS FROM DBA_CAPTURE;
Your output looks similar to the following:
Capture Capture Capture Capture Process Process Process Process Name Queue Rule Set Status --------------- -------------------- --------------- --------------- CAPTURE STREAMS_QUEUE RULESET$_6 ENABLED
The query in this section displays the following general information about a particular capture process:
cpnn)INITIALIZING, CAPTURING CHANGES, EVALUATING RULE, ENQUEUING MESSAGE, SHUTTING DOWN, or CREATING LCRFor example, to display this information for a capture process named capture, run the following query:
COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7 COLUMN SID HEADING 'Session|ID' FORMAT 9999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999 COLUMN STATE HEADING 'State' FORMAT A17 COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo Entries|Scanned' FORMAT 9999999 COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999 SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, c.SID, c.SERIAL#, c.STATE, c.TOTAL_MESSAGES_CAPTURED, c.TOTAL_MESSAGES_ENQUEUED FROM V$STREAMS_CAPTURE c, V$SESSION s WHERE c.CAPTURE_NAME = 'CAPTURE' AND c.SID = s.SID AND c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Capture Session Total Process Session Serial Redo Entries LCRs Number ID Number State Scanned Enqueued ------- ------- ------- ----------------- ------------ -------- CP01 18 150 CAPTURING CHANGES 56900 7
The number of redo entries scanned may be higher than the number of DML and DDL redo entries that evaluate to TRUE for a capture process rule set. Only DML and DDL redo entries that evaluate to TRUE for a capture process rule set are enqueued into the capture process queue. Also, the total LCRs enqueued includes LCRs that contain transaction control statements. These row LCRs contain directives such as COMMIT and ROLLBACK. Therefore, the total LCRs enqueued is a number higher than the number of row changes and DDL changes enqueued by a capture process.
| See Also:
"Row LCRs" for more information about transaction control statements |
The query in this section displays the current setting for each capture process parameter for a particular capture process.
For example, to display the settings for the capture process parameters of a capture process named capture, run the following query:
COLUMN PARAMETER HEADING 'Parameter' FORMAT A20 COLUMN VALUE HEADING 'Value' FORMAT A20 COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20 SELECT PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS WHERE CAPTURE_NAME = 'CAPTURE';
Your output looks similar to the following:
Parameter Value Set by User? -------------------- -------------------- -------------------- DISABLE_ON_LIMIT N NO MAXIMUM_SCN INFINITE NO MESSAGE_LIMIT INFINITE NO PARALLELISM 3 YES STARTUP_SECONDS 0 NO TIME_LIMIT INFINITE NO TRACE_LEVEL 0 NO WRITE_ALERT_LOG Y NO
The applied system change number (SCN) for a capture process is the SCN of the most recent event dequeued by the relevant apply processes. All changes below this applied SCN have been dequeued by all apply processes that apply changes captured by the capture process. This SCN is important because all redo logs must be kept available to a capture process until all transactions within the redo logs have been applied at all downstream databases.
To display the applied SCN for all of the capture processes in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30 COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 999999 SELECT CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE;
Your output looks similar to the following:
Capture Process Name Applied SCN ------------------------------ ----------- CAPTURE_EMP 177154
You can find the following information about a capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to determine the redo scanning latency for a capture process named capture:
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999 COLUMN CAPTURE_TIME HEADING 'Current|Process|Time' COLUMN CREATE_TIME HEADING 'Event|Creation Time' FORMAT 999999 SELECT ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS, ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS, TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME, TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';
Your output looks similar to the following:
Latency Current in Seconds Since Process Event Seconds Last Status Time Creation Time ------- ------------- ----------------- ----------------- 4 4 12:04:13 03/01/02 12:04:13 03/01/02
The "Latency in Seconds" returned by this query is the difference between the current time (SYSDATE) and the "Event Creation Time." The "Seconds Since Last Status" returned by this query is the difference between the current time (SYSDATE) and the "Current Process Time."
You can find the following information about a capture process by running the query in this section:
The information displayed by this query is valid only for an enabled capture process.
Run the following query to determine the event capturing latency for a capture process named capture:
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN CREATE_TIME HEADING 'Event Creation|Time' FORMAT A20 COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20 COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999 SELECT (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS, TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME, TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME, ENQUEUE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';
Your output looks similar to the following:
Latency in Event Creation Message Seconds Time Enqueue Time Number ------- -------------------- -------------------- ------- 0 10:56:51 03/01/02 10:56:51 03/01/02 253962
The "Latency in Seconds" returned by this query is the difference between the "Enqueue Time" and the "Event Creation Time."
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 'Instantiation SCN' FORMAT 999999 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 Instantiation 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
Supplemental logging places additional column data into a redo log whenever an UPDATE 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 A20 COLUMN ALWAYS HEADING 'Type of Log Group' FORMAT A30 SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS, 'ALWAYS', 'Unconditional', NULL, 'Conditional') ALWAYS FROM DBA_LOG_GROUPS;
Your output looks similar to the following:
Log Group Table Type of Log Group -------------------- -------------------- ------------------------------ LOG_GROUP_DEP_PK DEPARTMENTS Unconditional LOG_GROUP_JOBS_CR JOBS Conditional
To list the columns in a particular log group, query the DBA_LOG_GROUP_COLUMNS data dictionary view. You can also query the V$DATABASE dynamic performance view to display supplemental logging specified at the database level.
The following sections contain queries that you can run to display information about a Streams queue:
Streams queues are of object type SYS.AnyData. To display all of the Streams queues in a database, run the following query:
COLUMN OWNER HEADING 'Owner' FORMAT A10 COLUMN NAME HEADING 'Queue Name' FORMAT A25 COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A20 COLUMN USER_COMMENT HEADING 'Comment' FORMAT A20 SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT FROM DBA_QUEUES q, DBA_QUEUE_TABLES t WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND q.QUEUE_TABLE = t.QUEUE_TABLE AND q.OWNER = t.OWNER;
Your output looks similar to the following:
Owner Queue Name Queue Table Comment ---------- ------------------------- -------------------- -------------------- STRMADMIN AQ$_STREAMS_QUEUE_TABLE_E STREAMS_QUEUE_TABLE exception queue STRMADMIN STREAMS_QUEUE STREAMS_QUEUE_TABLE
An exception queue is created automatically when you create a Streams queue.
To determine the consumer for each user-enqueued event in a queue, query AQ$queue_table_name in the queue owner's schema, where queue_table_name is the name of the queue table. For example, to find the consumers of the user-enqueued events in the oe_queue_table queue table, run the following query:
COLUMN MSG_ID HEADING 'Message ID' FORMAT 9999 COLUMN MSG_STATE HEADING 'Message State' FORMAT A13 COLUMN CONSUMER_NAME HEADING 'Consumer' FORMAT A30 SELECT MSG_ID, MSG_STATE, CONSUMER_NAME FROM AQ$OE_QUEUE_TABLE;
Your output looks similar to the following:
Message ID Message State Consumer -------------------------------- ------------- ------------------------------ 99315B276CFA1872E034080020AE3E0A PROCESSED APPLY_OE 99315B276CFB1872E034080020AE3E0A PROCESSED APPLY_OE 99315B276CFA1872E034080020AE3E0A READY EXPLICIT_DQ 99315B276CFB1872E034080020AE3E0A READY EXPLICIT_DQ
| See Also:
Chapter 19, "Streams Messaging Example" for an example that enqueues the events shown in this example into a Streams queue |
In a Streams queue, to view the contents of a payload that is encapsulated within a SYS.AnyData payload, you query the queue table using the Accessdata_type static functions of the SYS.AnyData type, where data_type is the type of payload to view.
| See Also:
"Wrapping User Message Payloads in a SYS.AnyData Wrapper" for an example that enqueues the events shown in the queries in this section into a Streams queue |
For example, to view the contents of payload of type NUMBER in a queue with a queue table named oe_queue_table, run the following query as the queue owner:
SELECT qt.user_data.AccessNumber() "Numbers in Queue" FROM strmadmin.oe_q_table_any qt;
Your output looks similar to the following:
Numbers in Queue ---------------- 16
Similarly, to view the contents of a payload of type VARCHAR2 in a queue with a queue table named oe_q_table_any, run the following query:
SELECT qt.user_data.AccessVarchar2() "Varchar2s in Queue" FROM strmadmin.oe_q_table_any qt;
Your output looks similar to the following:
Varchar2s in Queue -------------------------------------------------------------------------------- Chemicals - SW
To view the contents of a user-defined datatype, you query the queue table using a custom function that you create. For example, to view the contents of a payload of oe.cust_address_typ, connect as the Streams administrator and create a function similar to the following:
CONNECT oe/oe CREATE OR REPLACE FUNCTION oe.view_cust_address_typ( in_any IN SYS.AnyData) RETURN oe.cust_address_typ IS address oe.cust_address_typ; num_var NUMBER; BEGIN IF (in_any.GetTypeName() = 'OE.CUST_ADDRESS_TYP') THEN num_var := in_any.GetObject(address); RETURN address; ELSE RETURN NULL; END IF; END; / GRANT EXECUTE ON oe.view_cust_address_typ TO STRMADMIN; GRANT EXECUTE ON oe.cust_address_typ TO STRMADMIN;
Then, query the queue table using the function, as in the following example:
CONNECT strmadmin/strmadminpw SELECT oe.view_cust_address_typ(qt.user_data) "Customer Addresses" FROM strmadmin.oe_q_table_any qt WHERE qt.user_data.GetTypeName() = 'OE.CUST_ADDRESS_TYP';
Your output looks similar to the following:
Customer Addresses(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID -------------------------------------------------------------------------------- CUST_ADDRESS_TYP('1646 Brazil Blvd', '361168', 'Chennai', 'Tam', 'IN')
The following sections contain queries that you can run to display information about propagations and propagation jobs:
You can determine the source queue and destination queue for a propagation by querying the DBA_PROPAGATION data dictionary view at the database that contains the source queue.
For example, the following query displays the following information for a propagation named dbs1_to_dbs2:
COLUMN 'Source Queue' FORMAT A35 COLUMN 'Destination Queue' FORMAT A35 SELECT p.SOURCE_QUEUE_OWNER ||'.'|| p.SOURCE_QUEUE_NAME ||'@'|| g.GLOBAL_NAME "Source Queue", p.DESTINATION_QUEUE_OWNER ||'.'|| p.DESTINATION_QUEUE_NAME ||'@'|| p.DESTINATION_DBLINK "Destination Queue" FROM DBA_PROPAGATION p, GLOBAL_NAME g WHERE PROPAGATION_NAME = 'DBS1_TO_DBS2';
Your output looks similar to the following:
Source Queue Destination Queue ----------------------------------- ----------------------------------- STRMADMIN.STREAMS_QUEUE@DBS1.NET STRMADMIN.STREAMS_QUEUE@DBS2.NET
The following query displays the owner and name of a rule set used by a propagation named dbs1_to_dbs2:
COLUMN RULE_SET_OWNER HEADING 'Rule Set Owner' FORMAT A35 COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A35 SELECT RULE_SET_OWNER, RULE_SET_NAME FROM DBA_PROPAGATION WHERE PROPAGATION_NAME = 'DBS1_TO_DBS2';
Your output looks similar to the following:
Rule Set Owner Rule Set Name ----------------------------------- ----------------------------------- STRMADMIN RULESET$_3
The query in this section displays the following information about the propagation schedule for a propagation job used by a propagation named dbs1_to_dbs2:
Run this query at the database that contains the source queue:
COLUMN START_DATE HEADING 'Start Date' COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999 COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8 COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999 COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8 COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8 COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99 SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE, s.PROPAGATION_WINDOW, s.NEXT_TIME, s.LATENCY, DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled', 'N', 'Enabled') SCHEDULE_DISABLED, s.PROCESS_NAME, s.FAILURES FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2' AND p.DESTINATION_DBLINK = s.DESTINATION AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME;
Your output looks similar to the following:
Duration Next Latency Number of Start Date in Seconds Time in Seconds Status Process Failures ----------------- ---------- -------- ---------- -------- -------- --------- 15:23:40 03/02/02 5 Enabled J002 0
This propagation job uses the default schedule for a Streams propagation job. That is, the duration and next time are both NULL, and the latency is five seconds. When the duration is NULL, the job propagates changes without restarting automatically. When the next time is NULL, the propagation job is running currently.
See Also:
|
All propagation jobs from a source queue that share the same database link have a single propagation schedule. The query in this section displays the following information for a propagation schedule associated with a particular propagation job:
For example, to display this information for a propagation job used by a propagation named dbs1_to_dbs2, run the following query at the database that contains the source queue:
COLUMN TOTAL_TIME HEADING 'Total Time Executing|in Seconds' FORMAT 999999 COLUMN TOTAL_NUMBER HEADING 'Total Events Propagated' FORMAT 999999999 COLUMN TOTAL_BYTES HEADING 'Total Bytes Propagated' FORMAT 9999999999999 SELECT s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2' AND p.DESTINATION_DBLINK = s.DESTINATION AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME;
Your output looks similar to the following:
Total Time Executing in Seconds Total Events Propagated Total Bytes Propagated -------------------- ----------------------- ---------------------- 65 71 46536
| See Also:
Oracle9i Application Developer's Guide - Advanced Queuing and Oracle9i Database Reference for more information about the |
The following sections contain queries that you can run to display information about an apply process:
You can display the following general information about each apply process in a database by running the query in this section:
ENABLED, DISABLED, or ABORTEDTo display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15 COLUMN RULE_SET_NAME HEADING 'Apply|Process|Rule Set' FORMAT A15 COLUMN APPLY_CAPTURED HEADING 'Type of|Events|Applied' FORMAT A15 COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A8 SELECT APPLY_NAME, QUEUE_NAME, RULE_SET_NAME, DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO', 'User-Enqueued') APPLY_CAPTURED, STATUS FROM DBA_APPLY;
Your output looks similar to the following:
Apply Apply Apply Type of Apply Process Process Process Events Process Name Queue Rule Set Applied Status --------------- --------------- --------------- --------------- -------- APPLY_OE OE_QUEUE APPLY_OE_RS User-Enqueued ENABLED APPLY OE_QUEUE RULESET$_4 Captured DISABLED
The query in this section displays the current setting for each apply process parameter for a particular apply process.
For example, to display the settings for the apply process parameters of an apply process named strm01_apply, run the following query:
COLUMN PARAMETER HEADING 'Parameter' FORMAT A20 COLUMN VALUE HEADING 'Value' FORMAT A20 COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20 SELECT PARAMETER, VALUE, SET_BY_USER FROM DBA_APPLY_PARAMETERS WHERE APPLY_NAME = 'STRM01_APPLY';
Your output looks similar to the following:
Parameter Value Set by User? -------------------- -------------------- -------------------- COMMIT_SERIALIZATION FULL NO DISABLE_ON_ERROR Y YES DISABLE_ON_LIMIT N NO MAXIMUM_SCN INFINITE NO PARALLELISM 1 NO STARTUP_SECONDS 0 NO TIME_LIMIT INFINITE NO TRACE_LEVEL 0 NO TRANSACTION_LIMIT INFINITE NO WRITE_ALERT_LOG Y NO
This section contains queries that display information about apply process DML handlers, DDL handlers, and error handlers.
When you specify a local DML or error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, the handler is run for all apply processes in the database that apply changes locally, when appropriate. DML and error handlers are run for a specified operation on a specific table.
To display the DML or error handler for each apply process that applies changes locally in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10 COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9 COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A40 COLUMN ERROR_HANDLER HEADING 'Type of|Handler' FORMAT A10 SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE, DECODE(ERROR_HANDLER, 'Y', 'Error', 'N', 'DML') ERROR_HANDLER FROM DBA_APPLY_DML_HANDLERS WHERE APPLY_DATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME, ERROR_HANDLER;
Your output looks similar to the following:
Table Type of Owner Table Name Operation Handler Procedure Handler ----- ---------- --------- ---------------------------------------- ---------- HR LOCATIONS UPDATE STRMADMIN.HISTORY_DML DML HR REGIONS INSERT STRMADMIN.ERRORS_PKG.REGIONS_PK_ERROR Error
To display the DDL handler and message 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 A20 COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20 SELECT APPLY_NAME, DDL_HANDLER, MESSAGE_HANDLER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name DDL Handler Message Handler -------------------- -------------------- -------------------- APPLY oe.ddl_handler APPLY_OE oe.msg_handler
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
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
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.
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 999999 SELECT SOURCE_DATABASE, SOURCE_OBJECT_OWNER, SOURCE_OBJECT_NAME, INSTANTIATION_SCN FROM DBA_APPLY_INSTANTIATED_OBJECTS;
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
The reader server for an apply process dequeues events from the queue. The reader server is a parallel execution server that computes dependencies between LCRs and assembles events into transactions. The reader server then returns the assembled transactions to the coordinator, which assigns them to idle apply servers.
The query in this section displays the following information about the reader server for a particular apply process:
IDLE, DEQUEUE MESSAGES, or SCHEDULE MESSAGESThe information displayed by this query is valid only for an enabled apply process.
For example, to display this information for an apply process named apply, run the following query:
COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22 COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12 COLUMN STATE HEADING 'State' FORMAT A17 COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Events Dequeued' FORMAT 99999999 SELECT DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, r.STATE, r.TOTAL_MESSAGES_DEQUEUED FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap WHERE r.APPLY_NAME = 'APPLY' AND r.SID = s.SID AND r.SERIAL# = s.SERIAL# AND r.APPLY_NAME = ap.APPLY_NAME;
Your output looks similar to the following:
Apply Type Process Name State Total Events Dequeued ---------------------- ------------ ----------------- --------------------- Captured LCRS P000 DEQUEUE MESSAGES 3803
The query in this section displays the following information about the last event dequeued by a particular apply process:
The information displayed by this query is valid only for an enabled apply process.
For example, to display the capture and propagation latency for the last captured event dequeued by an apply process named apply, run the following query:
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999 COLUMN CREATION HEADING 'Event Creation' FORMAT A17 COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20 COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 999999 SELECT (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY, TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION, TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE, DEQUEUED_MESSAGE_NUMBER FROM V$STREAMS_APPLY_READER WHERE APPLY_NAME = 'APPLY';
Your output looks similar to the following:
Latency in Dequeued Seconds Event Creation Last Dequeue Time Message Number ------- ----------------- -------------------- -------------- 36 10:56:51 03/01/02 10:57:27 03/01/02 253962
A coordinator process gets transactions from the reader server and passes these transactions to apply servers. The coordinator process name is apnn, where nn is a coordinator process number.
The query in this section displays the following information about the coordinator process for a particular apply process:
apnn)INITIALIZING, APPLYING, SHUTTING DOWN CLEANLY, or ABORTINGThe information displayed by this query is valid only for an enabled apply process.
For example, to display this information for an apply process named apply, run the following query:
COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11 COLUMN SID HEADING 'Session|ID' FORMAT 9999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999 COLUMN STATE HEADING 'State' FORMAT A21 COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999 COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999 COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999 SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, c.SID, c.SERIAL#, c.STATE, c.TOTAL_RECEIVED, c.TOTAL_APPLIED, c.TOTAL_ERRORS FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s WHERE c.APPLY_NAME = 'APPLY' AND c.SID = s.SID AND c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Coordinator Session Total Total Total Process Session Serial Trans Trans Apply Name ID Number State Received Applied Errors ----------- ------- ------- --------------------- --------- --------- ------ AP01 11 40 APPLYING 78 73 2
This section contains two different queries that show the capture to apply latency for a particular event. That is, for captured events, these queries show the amount of time between when the event was created at a source database and when the event was applied by the apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view, while the other uses the DBA_APPLY_PROGRESS static data dictionary view.
|
Note: These queries assume that the apply process applies captured events, not user-enqueued events. |
The following are the major differences between these two queries:
V$STREAMS_APPLY_COORDINATOR view, while the apply process can be enabled or disabled when you run the query on the DBA_APPLY_PROGRESS view.V$STREAMS_APPLY_COORDINATOR view may show the latency for a more recent transaction than the query on the DBA_APPLY_PROGRESS view.Both queries display the following information about an event applied by a particular apply process:
To display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for an event applied by an apply process named apply, run the following query:
COLUMN 'Latency in Seconds' FORMAT 999999 COLUMN 'Event Creation' FORMAT A17 COLUMN 'Apply Time' FORMAT A17 COLUMN 'Applied Message Number' FORMAT 999999 SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds", TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation", TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time", HWM_MESSAGE_NUMBER "Applied Message Number" FROM V$STREAMS_APPLY_COORDINATOR WHERE APPLY_NAME = 'APPLY';
Your output looks similar to the following:
Latency in Seconds Event Creation Apply Time Applied Message Number ------------------ ----------------- ----------------- ---------------------- 36 10:56:51 03/01/02 10:57:27 03/01/02 253962
To display the capture to apply latency using the DBA_APPLY_PROGRESS view for an event applied by an apply process named apply, run the following query:
COLUMN 'Latency in Seconds' FORMAT 999999 COLUMN 'Event Creation' FORMAT A17 COLUMN 'Apply Time' FORMAT A17 COLUMN 'Applied Message Number' FORMAT 999999 SELECT (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds", TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') "Event Creation", TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time", APPLIED_MESSAGE_NUMBER "Applied Message Number" FROM DBA_APPLY_PROGRESS WHERE APPLY_NAME = 'APPLY';
Your output looks similar to the following:
Latency in Seconds Event Creation Apply Time Applied Message Number ------------------ ----------------- ----------------- ---------------------- 38 10:50:09 03/01/02 10:50:47 03/01/02 253678
An apply process can use one or more apply servers that apply LCRs to database objects as DML statements or DDL statements or pass the LCRs to their appropriate handlers. For non-LCR messages, the apply servers pass the events to the message handler. Each apply server is a parallel execution server.
The query in this section displays the following information about the apply servers for a particular apply process:
IDLE, RECORD LOW-WATERMARK, ADD PARTITION, DROP PARTITION, EXECUTE TRANSACTION, WAIT COMMIT, WAIT DEPENDENCY, or WAIT FOR NEXT CHUNK. See V$STREAMS_APPLY_SERVER in the Oracle9i Database Reference for more information about these states.The information displayed by this query is valid only for an enabled apply process.
For example, to display this information for an apply process named apply, run the following query:
COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22 COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12 COLUMN STATE HEADING 'State' FORMAT A17 COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999 COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|Events|Applied' FORMAT 99999999 SELECT DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, r.STATE, r.TOTAL_ASSIGNED, r.TOTAL_MESSAGES_APPLIED FROM V$STREAMS_APPLY_SERVER R, V$SESSION S, DBA_APPLY AP WHERE r.APPLY_NAME = 'APPLY' AND r.SID = s.SID AND r.SERIAL# = s.SERIAL# AND r.APPLY_NAME = ap.APPLY_NAME ORDER BY r.SERVER_ID;
Your output looks similar to the following:
Total Total Transactions Events Apply Type Process Name State Assigned Applied ---------------------- ------------ ----------------- ------------ --------- Captured LCRs P001 IDLE 94 2141 Captured LCRs P002 IDLE 12 276 Captured LCRs P003 IDLE 0 0
In some environments, an apply process may not use all of the apply servers available to it. For example, apply process parallelism may be set to five, but only three apply servers are ever used by the apply process. In this case, the effective apply parallelism is three.
The following query displays the effective apply parallelism for an apply process named apply:
SELECT COUNT(SERVER_ID) "Effective Parallelism" FROM V$STREAMS_APPLY_SERVER WHERE APPLY_NAME = 'APPLY' AND TOTAL_MESSAGES_APPLIED > 0;
Your output looks similar to the following:
Effective Parallelism --------------------- 2
This query returned two for the effective parallelism. If parallelism is set to three for the apply process named apply, then one apply server has not been used since the last time the apply process was started.
You can display the total number of events applied by each apply server by running the following query:
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99 COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Events Applied' FORMAT 999999 SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED FROM V$STREAMS_APPLY_SERVER WHERE APPLY_NAME = 'APPLY' ORDER BY SERVER_ID;
Your output looks similar to the following:
Apply Server ID Total Events Applied --------------- -------------------- 1 2141 2 276 3 0
In this case, apply server 3 has not been used by the apply process since it was last restarted. If the parallelism setting for an apply process is higher than the effective parallelism for the apply process, then consider lowering the parallelism setting.
To check for apply errors, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A8 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A8 COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11 COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A50 SELECT APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID, ERROR_MESSAGE FROM DBA_APPLY_ERROR;
If there are any apply errors, then your output looks similar to the following:
Apply Local Process Source Transaction Name Database ID Error Message -------- -------- ----------- -------------------------------------------------- APPLY DBS1.NET 5.4.312 ORA-00001: unique constraint (HR.JOB_ID_PK) violated
If there are apply errors, then you can either try to reexecute the transactions that encountered the errors, or you can delete the transactions. If you want to reexecute a transaction that encountered an error, then first correct the condition that caused the transaction to raise an error.
If you want to delete a transaction that encountered an error, then you may need to resynchronize data manually if you are sharing data between multiple databases. Remember to set an appropriate session tag, if necessary, when you resynchronize data manually.
See Also:
|
This section contains SQL scripts that you can use to display detailed information about the error transactions in the exception queues in a database. These scripts are designed to display information about LCR events, but you can extend them to display information about any non-LCR events used in your environment as well.
To use these scripts, complete the following steps:
The user who creates and runs the print_errors and print_transaction procedures described in the following sections must be granted explicit SELECT privilege on the DBA_APPLY_ERROR data dictionary view. This privilege cannot be granted through a role.
SELECT privilege on the DBA_APPLY_ERROR data dictionary view to the appropriate user. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
The following procedure prints the value in a specified SYS.AnyData object for some selected value types.
CREATE OR REPLACE PROCEDURE print_any(data IN SYS.AnyData) IS tn VARCHAR2(61); str VARCHAR2(255); chr CHAR(255); num NUMBER; dat DATE; rw RAW(4000); res NUMBER; BEGIN IF data IS NULL THEN DBMS_OUTPUT.PUT_LINE('NULL value'); RETURN; END IF; tn := data.GETTYPENAME(); IF tn = 'SYS.VARCHAR2' THEN res := data.GETVARCHAR2(str); DBMS_OUTPUT.PUT_LINE(str); ELSIF tn = 'SYS.CHAR' then res := data.GETCHAR(chr); DBMS_OUTPUT.PUT_LINE(chr); ELSIF tn = 'SYS.VARCHAR' THEN res := data.GETVARCHAR(chr); DBMS_OUTPUT.PUT_LINE(chr); ELSIF tn = 'SYS.NUMBER' THEN res := data.GETNUMBER(num); DBMS_OUTPUT.PUT_LINE(num); ELSIF tn = 'SYS.DATE' THEN res := data.GETDATE(dat); DBMS_OUTPUT.PUT_LINE(dat); ELSIF tn = 'SYS.RAW' THEN res := data.GETRAW(rw); DBMS_OUTPUT.PUT_LINE(RAWTOHEX(rw)); ELSE DBMS_OUTPUT.PUT_LINE('typename is ' || tn); END IF; END print_any; /
The following procedure prints a specified LCR. It calls the print_any procedure created in "Create a Procedure That Prints the Value in a SYS.AnyData Object".
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN SYS.ANYDATA) IS typenm VARCHAR2(61); ddllcr SYS.LCR$_DDL_RECORD; proclcr SYS.LCR$_PROCEDURE_RECORD; rowlcr SYS.LCR$_ROW_RECORD; res NUMBER; newlist SYS.LCR$_ROW_LIST; oldlist SYS.LCR$_ROW_LIST; ddl_text CLOB; BEGIN typenm := lcr.GETTYPENAME(); DBMS_OUTPUT.PUT_LINE('type name: ' || typenm); IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN res := lcr.GETOBJECT(ddllcr); DBMS_OUTPUT.PUT_LINE('source database: ' || ddllcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG); DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE); ddllcr.GET_DDL_TEXT(ddl_text); DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text); DBMS_LOB.FREETEMPORARY(ddl_text); ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN res := lcr.GETOBJECT(rowlcr); DBMS_OUTPUT.PUT_LINE('source database: ' || rowlcr.GET_SOURCE_DATABASE_NAME); DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER); DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME); DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG); DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE); oldlist := rowlcr.GET_VALUES('old'); FOR i IN 1..oldlist.COUNT LOOP IF oldlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name); print_any(oldlist(i).data); END IF; END LOOP; newlist := rowlcr.GET_VALUES('new', 'n'); FOR i in 1..newlist.count LOOP IF newlist(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name); print_any(newlist(i).data); END IF; END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm); END IF; END print_lcr; /
The following procedure prints all of the LCRs in all of the exception queues in the database. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_errors IS CURSOR c IS SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY_ERROR ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN; i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgcnt NUMBER; errnum NUMBER := 0; errno NUMBER; errmsg VARCHAR2(128); lcr SYS.AnyData; r NUMBER; BEGIN FOR r IN c LOOP errnum := errnum + 1; msgcnt := r.MESSAGE_COUNT; txnid := r.LOCAL_TRANSACTION_ID; source := r.SOURCE_DATABASE; errmsg := r.ERROR_MESSAGE; errno := r.ERROR_NUMBER; DBMS_OUTPUT.PUT_LINE('*************************************************'); DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum); DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); print_lcr(lcr); END LOOP; END LOOP; END print_errors; /
To run this procedure after you create it, enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_errors
The following procedure prints all the LCRs in an exception queue for a particular transaction. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS i NUMBER; txnid VARCHAR2(30); source VARCHAR2(128); msgcnt NUMBER; errno NUMBER; errmsg VARCHAR2(128); lcr SYS.ANYDATA; BEGIN SELECT LOCAL_TRANSACTION_ID, SOURCE_DATABASE, MESSAGE_COUNT, ERROR_NUMBER, ERROR_MESSAGE INTO txnid, source, msgcnt, errno, errmsg FROM DBA_APPLY_ERROR WHERE LOCAL_TRANSACTION_ID = ltxnid; DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid); DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source); DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno); DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg); FOR i IN 1..msgcnt LOOP DBMS_OUTPUT.PUT_LINE('--message: ' || i); lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR print_lcr(lcr); END LOOP; END print_transaction; /
To run this procedure after you create it, pass it the local transaction identifier of a error transaction. For example, if the local transaction identifier is 1.17.2485, then enter the following:
SET SERVEROUTPUT ON SIZE 1000000 EXEC print_transaction('1.17.2485')
The following sections contain queries that you can run to display information about rules and rule-based transformations:
Streams rules are rules created using the DBMS_STREAMS_ADM package for a capture process, propagation, or apply process. These rules determine behavior of the capture process, propagation, or apply process. For example, if a capture rule evaluates to TRUE for DML changes to the hr.employees table, then the capture process captures DML changes to this table.
You query the following data dictionary views to display Streams rules:
ALL_STREAMS_GLOBAL_RULESDBA_STREAMS_GLOBAL_RULESALL_STREAMS_SCHEMA_RULESDBA_STREAMS_SCHEMA_RULESALL_STREAMS_TABLE_RULESDBA_STREAMS_TABLE_RULES
For example, the following query displays all of the schema rules for an apply process named strm01_apply:
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A10 COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10 COLUMN RULE_TYPE HEADING 'Rule Type' FORMAT A10 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A10 COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10 COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A15 SELECT SCHEMA_NAME, SOURCE_DATABASE, RULE_TYPE, RULE_NAME, RULE_OWNER, INCLUDE_TAGGED_LCR FROM DBA_STREAMS_SCHEMA_RULES WHERE STREAMS_NAME = 'STRM01_APPLY' AND STREAMS_TYPE = 'APPLY';
Your output looks similar to the following:
Apply Schema Tagged Name Source Rule Type Rule Name Rule Owner LCRs? ---------- ---------- ---------- ---------- ---------- --------------- HR DBS1.NET DML HR1 STRMADMIN NO
These results show that the apply process applies LCRs containing DML changes to the hr schema that originated at the dbs1.net database. The rule in the apply process rule set that instructs the apply process to apply these changes is owned by the strmadmin user and is named hr1. Also, the apply process applies these changes only if the tag in the LCR is NULL.
If you know the name and level of a Streams rule, then you can display its rule condition. The level is either global, schema, or table.
For example, consider the rule returned by the query in "Displaying the Streams Rules Used by a Streams Process or Propagation". The name of the Streams schema rule is hr1, and you can display its condition by running the following query:
SELECT RULE_CONDITION "Schema Rule Condition" FROM DBA_STREAMS_SCHEMA_RULES WHERE RULE_NAME = 'HR1' AND RULE_OWNER = 'STRMADMIN';
Your output looks similar to the following:
Schema Rule Condition ----------------------------------------------------------------- (:dml.get_object_owner() = 'HR' and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )
The following query displays the default evaluation context for each rule set in a database:
COLUMN RULE_SET_OWNER HEADING 'Rule Set|Owner' FORMAT A15 COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A15 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27 SELECT RULE_SET_OWNER, RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME FROM DBA_RULE_SETS;
Your output looks similar to the following:
Rule Set Eval Context Owner Rule Set Name Owner Eval Context Name --------------- --------------- ------------ --------------------------- STRMADMIN RULESET$_2 SYS STREAMS$_EVALUATION_CONTEXT STRMADMIN STRM02_QUEUE_R STRMADMIN AQ$_STRM02_QUEUE_TABLE_V STRMADMIN APPLY_OE_RS STRMADMIN OE_EVAL_CONTEXT STRMADMIN OE_QUEUE_R STRMADMIN AQ$_OE_QUEUE_TABLE_V STRMADMIN AQ$_1_RE STRMADMIN AQ$_OE_QUEUE_TABLE_V SUPPORT RS SUPPORT EVALCTX
The following query displays information about the tables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN TABLE_ALIAS HEADING 'Table Alias' FORMAT A20 COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A40 SELECT TABLE_ALIAS, TABLE_NAME FROM DBA_EVALUATION_CONTEXT_TABLES WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Table Alias Table Name -------------------- ---------------------------------------- PROB problems
The following query displays information about the variables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN VARIABLE_NAME HEADING 'Variable Name' FORMAT A15 COLUMN VARIABLE_TYPE HEADING 'Variable Type' FORMAT A15 COLUMN VARIABLE_VALUE_FUNCTION HEADING 'Variable Value|Function' FORMAT A20 COLUMN VARIABLE_METHOD_FUNCTION HEADING 'Variable Method|Function' FORMAT A20 SELECT VARIABLE_NAME, VARIABLE_TYPE, VARIABLE_VALUE_FUNCTION, VARIABLE_METHOD_FUNCTION FROM DBA_EVALUATION_CONTEXT_VARS WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Variable Value Variable Method Variable Name Variable Type Function Function --------------- --------------- -------------------- -------------------- CURRENT_TIME DATE timefunc
The query in this section displays the following information about all of the rules in a rule set:
ADD_RULE procedure when the rule is added to a rule set, then it inherits the evaluation context of the rule setFor example, to display this information for each rule in a rule set named oe_queue_r that is owned by the user strmadmin, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 COLUMN RULE_EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27 COLUMN RULE_EVALUATION_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11 SELECT R.RULE_OWNER, R.RULE_NAME, R.RULE_EVALUATION_CONTEXT_NAME, R.RULE_EVALUATION_CONTEXT_OWNER FROM DBA_RULES R, DBA_RULE_SET_RULES RS WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND RS.RULE_SET_NAME = 'OE_QUEUE_R' AND RS.RULE_NAME = R.RULE_NAME AND RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Eval Contex Rule Owner Rule Name Eval Context Name Owner ---------- -------------------- --------------------------- ----------- STRMADMIN HR1 STREAMS$_EVALUATION_CONTEXT SYS STRMADMIN APPLY_LCRS STREAMS$_EVALUATION_CONTEXT SYS STRMADMIN OE_QUEUE$3 STRMADMIN APPLY_ACTION
The following query displays the condition for each rule in a rule set named hr_queue_r that is owned by the user strmadmin:
SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A45 SELECT R.RULE_OWNER, R.RULE_NAME, R.RULE_CONDITION FROM DBA_RULES R, DBA_RULE_SET_RULES RS WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND RS.RULE_SET_NAME = 'HR_QUEUE_R' AND RS.RULE_NAME = R.RULE_NAME AND RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Rule Owner Rule Name Rule Condition --------------- --------------- --------------------------------------------- STRMADMIN APPLY_ACTION hr.get_hr_action(tab.user_data) = 'APPLY' STRMADMIN APPLY_LCRS :dml.get_object_owner() = 'HR' AND (:dml.get _object_name() = 'DEPARTMENTS' OR :dml.get_object_name() = 'EMPLOYEES') STRMADMIN HR_QUEUE$3 hr.get_hr_action(tab.user_data) != 'APPLY'
To list each rule in a database that contains a specified pattern in its condition, you can query the DBMS_RULES data dictionary view and use the DBMS_LOB.INSTR function to search for the pattern in the rule conditions. For example, the following query lists each rule that contains the pattern 'HR' in its condition:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A30 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30 SELECT RULE_OWNER, RULE_NAME FROM DBA_RULES WHERE DBMS_LOB.INSTR(RULE_CONDITION, 'HR', 1, 1) > 0;
Your output looks similar to the following:
Rule Owner Rule Name ------------------------------ ------------------------------ STRMADMIN DEPARTMENTS4 STRMADMIN DEPARTMENTS5 STRMADMIN DEPARTMENTS6
In Streams, a rule-based transformation is specified in a rule action context that has the name STREAMS$_TRANSFORM_FUNCTION in the name-value pair. The value in the name-value pair is the name of the PL/SQL procedure that performs the transformation.
The following query displays all of the rule-based transformations specified for rules in a rule set named RULESET$_4:
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 COLUMN ACTION_CONTEXT_VALUE HEADING 'Transformation Procedure' FORMAT A40 SELECT r.RULE_NAME, ac.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE FROM DBA_RULES r, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) ac, DBA_RULE_SET_RULES s WHERE ac.NVN_NAME = 'STREAMS$_TRANSFORM_FUNCTION' AND s.RULE_SET_NAME = 'RULESET$_4' AND s.RULE_SET_OWNER = 'STRMADMIN' AND r.RULE_NAME = s.RULE_NAME AND r.RULE_OWNER = s.RULE_OWNER;
If there rule-based transformations specified for rules in the rule set, then your output looks similar to the following:
Rule Name Transformation Procedure -------------------- ---------------------------------------- DEPARTMENTS7 hr.executive_to_management DEPARTMENTS6 hr.executive_to_management DEPARTMENTS5 hr.executive_to_management
The following sections contain queries that you can run to display the Streams tag for the current session and for an 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 can also 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 an 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 tags generated in the redo entries by an apply process named strm01_apply, run the following query:
SELECT APPLY_TAG "Tag Value for strm01_apply" FROM DBA_APPLY WHERE APPLY_NAME = 'STRM01_APPLY';
Your output looks similar to the following:
Tag Value for strm01_apply -------------------------------------------------------------------------------- 00
A handler or transformation function associated with an apply process can get the tag by calling the DBMS_STREAMS.GET_TAG function.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|