| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
|
|
View PDF |
This chapter provides sample queries that you can use to monitor your Streams apply processes.
This chapter contains these topics:
Determining the Queue, Rule Sets, and Status for Each Apply Process
Displaying Information About the Reader Server for Each Apply Process
Monitoring Transactions and Messages Spilled by Each Apply Process
Displaying General Information About Each Coordinator Process
Displaying Information About Transactions Received and Applied
Determining the Capture to Apply Latency for a Message for Each Apply Process
Displaying Information About the Apply Servers for Each Apply Process
Note:
The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.See Also:
Oracle Database Reference for information about the data dictionary views described in this chapter
Oracle Streams Replication Administrator's Guide for information about monitoring a Streams replication environment
You can determine the following information for each apply process in a database by running the query in this section:
The apply process name
The name of the queue used by the apply process
The name of the positive rule set used by the apply process
The name of the negative rule set used by the apply process
The status of the apply process, either ENABLED, DISABLED, or ABORTED
To 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 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15
SELECT APPLY_NAME,
QUEUE_NAME,
RULE_SET_NAME,
NEGATIVE_RULE_SET_NAME,
STATUS
FROM DBA_APPLY;
Your output looks similar to the following:
Apply Apply Apply Process Process Positive Negative Process Name Queue Rule Set Rule Set Status --------------- --------------- --------------- --------------- --------------- STRM01_APPLY STREAMS_QUEUE RULESET$_36 ENABLED APPLY_EMP STREAMS_QUEUE RULESET$_16 DISABLED APPLY STREAMS_QUEUE RULESET$_21 RULESET$_23 ENABLED
If the status of an apply process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_APPLY data dictionary view to determine the error. These columns are populated when an apply process aborts or when an apply process is disabled after reaching a limit. These columns are cleared when an apply process is restarted.
Note:
TheERROR_NUMBER and ERROR_MESSAGE columns in the DBA_APPLY data dictionary view are not related to the information in the DBA_APPLY_ERROR data dictionary view.See Also:
"Checking for Apply Errors" to check for apply errors if the apply process status isABORTEDYou can display the following general information about each apply process in a database by running the query in this section:
The apply process name.
The type of messages applied by the apply process. An apply process can apply either messages that were captured by a capture process or messages that were enqueued by a user or application.
The apply user.
To display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied' FORMAT A25
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A30
SELECT APPLY_NAME,
DECODE(APPLY_CAPTURED,
'YES', 'Captured',
'NO', 'User-Enqueued') APPLY_CAPTURED,
APPLY_USER
FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name Type of Messages Applied Apply User -------------------- ------------------------- ------------------------------ STRM01_APPLY Captured STRMADMIN APPLY_OE User-Enqueued STRMADMIN APPLY Captured HR
The following query displays the current setting for each apply process parameter for each apply process in a database:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A25
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15
SELECT APPLY_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_APPLY_PARAMETERS;
Your output looks similar to the following:
Apply Process Name Parameter Value Set by User? --------------- ------------------------- -------------------- --------------- APPLY_HR ALLOW_DUPLICATE_ROWS N NO APPLY_HR COMMIT_SERIALIZATION FULL NO APPLY_HR DISABLE_ON_ERROR Y NO APPLY_HR DISABLE_ON_LIMIT N NO APPLY_HR MAXIMUM_SCN INFINITE NO APPLY_HR PARALLELISM 1 NO APPLY_HR STARTUP_SECONDS 0 NO APPLY_HR TIME_LIMIT INFINITE NO APPLY_HR TRACE_LEVEL 0 NO APPLY_HR TRANSACTION_LIMIT INFINITE NO APPLY_HR TXN_LCR_SPILL_THRESHOLD 5000 YES APPLY_HR WRITE_ALERT_LOG Y NO
Note:
If theSet by User? column is NO for a parameter, then the parameter is set to its default value. If the Set by User? column is YES for a parameter, then the parameter might or might not be set to its default value.This section contains instructions for displaying information about apply process message handlers and error handlers.
See Also:
Oracle Streams Replication Administrator's Guide for information about monitoring DML handlers and DDL handlers
When you specify a local error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can specify either 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 an error is raised by an apply process. A specific error handler takes precedence over a generic error handler. An error handler is run for a specified operation on a specific table.
To display the 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 A10
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A30
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 = 'Y'
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process
Owner Table Name Operation Handler Procedure Name
----- ---------- ---------- ------------------------------ --------------
HR REGIONS INSERT "STRMADMIN"."ERRORS_PKG"."REGI
ONS_PK_ERROR"
Apply Process Name is NULL for the strmadmin.errors_pkg.regions_pk_error error handler. Therefore, this handler is a general handler that runs for all of the local apply processes.
See Also:
"Managing an Error Handler"To display each message handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20 SELECT APPLY_NAME, MESSAGE_HANDLER FROM DBA_APPLY WHERE MESSAGE_HANDLER IS NOT NULL;
Your output looks similar to the following:
Apply Process Name Message Handler -------------------- -------------------- STRM03_APPLY "OE"."MES_HANDLER"
To display each precommit handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN PRECOMMIT_HANDLER HEADING 'Precommit Handler' FORMAT A30
COLUMN APPLY_CAPTURED HEADING 'Type of|Messages|Applied' FORMAT A15
SELECT APPLY_NAME,
PRECOMMIT_HANDLER,
DECODE(APPLY_CAPTURED,
'YES', 'Captured',
'NO', 'User-Enqueued') APPLY_CAPTURED
FROM DBA_APPLY
WHERE PRECOMMIT_HANDLER IS NOT NULL;
Your output looks similar to the following:
Type of
Messages
Apply Process Name Precommit Handler Applied
-------------------- ------------------------------ ---------------
STRM01_APPLY "STRMADMIN"."HISTORY_COMMIT" Captured
The reader server for an apply process dequeues messages from the queue. The reader server is a parallel execution server that computes dependencies between LCRs and assembles messages 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 each apply process:
The name of the apply process
The type of messages dequeued by the reader server, either captured messages or user-enqueued messages
The name of the parallel execution server used by the reader server
The current state of the reader server, either INITIALIZING, IDLE, DEQUEUE MESSAGES, SCHEDULE MESSAGES, SPILLING, or PAUSED
The total number of messages dequeued by the reader server since the last time the apply process was started
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Messages|Dequeued' FORMAT 99999999
SELECT r.APPLY_NAME,
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.SID = s.SID AND
r.SERIAL# = s.SERIAL# AND
r.APPLY_NAME = ap.APPLY_NAME;
Your output looks similar to the following:
Apply Process Process Total Messages Name Apply Type Name State Dequeued --------------- ---------------------- ------- ----------------- -------------- APPLY$_STM2_14 Captured LCRS P000 DEQUEUE MESSAGES 5650
See Also:
"Reader Server States"If the txn_lcr_spill_threshold apply process parameter is set to a value other than infinite, then an apply process can spill messages from memory to hard disk when the number of messages in a transaction exceeds the specified number.
The first query in this section displays the following information about each transaction currently being applied for which the apply process has spilled messages:
The name of the apply process
The transaction ID of the transaction with spilled messages
The system change number (SCN) of the first message in the transaction
The number of messages currently spilled in the transaction
To display this information for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A20
COLUMN 'Transaction ID' HEADING 'Transaction ID' FORMAT A15
COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999
COLUMN MESSAGE_COUNT HEADING 'Message Count' FORMAT 99999999
SELECT APPLY_NAME,
XIDUSN ||'.'||
XIDSLT ||'.'||
XIDSQN "Transaction ID",
FIRST_SCN,
MESSAGE_COUNT
FROM DBA_APPLY_SPILL_TXN;
Your output looks similar to the following:
Apply Name Transaction ID First SCN Message Count -------------------- --------------- --------- ------------- APPLY_HR 1.42.2277 2246944 100
The next query in this section displays the following information about the messages spilled by the apply processes in the local database:
The name of the apply process
The total number of messages spilled by the apply process since it last started
The amount of time the apply process spent spilling messages, in seconds
To display this information for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A15
COLUMN TOTAL_MESSAGES_SPILLED HEADING 'Total|Spilled Messages' FORMAT 99999999
COLUMN ELAPSED_SPILL_TIME HEADING 'Elapsed Time|Spilling Messages' FORMAT 99999999.99
SELECT APPLY_NAME,
TOTAL_MESSAGES_SPILLED,
(ELAPSED_SPILL_TIME/100) ELAPSED_SPILL_TIME
FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Total Elapsed Time Apply Name Spilled Messages Spilling Messages --------------- ---------------- ----------------- APPLY_HR 100 2.67
Note:
The elapsed time spilling messages is displayed in seconds. TheV$STREAMS_APPLY_READER view displays elapsed time in centiseconds by default. A centisecond is one-hundredth of a second. The query in this section divides each elapsed time by one hundred to display the elapsed time in seconds.The query in this section displays the following information about the last message dequeued by each apply process:
The name of the apply process.
The latency. For captured messages, the latency is the amount of time between when the message was created at a source database and when the message was dequeued by the apply process. For user-enqueued messages, the latency is the amount of time between when the message enqueued at the local database and when the message was dequeued by the apply process.
The message creation time. For captured messages, the message creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data at the source database for the message. For user-enqueued messages, the message creation time is the last time the message was enqueued. A user-enqueued message can be enqueued one or more additional times by propagations before it reaches an apply process.
The time when the message was dequeued by the apply process.
The message number of the message that was last dequeued by the apply process.
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 999999
SELECT APPLY_NAME,
(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;
Your output looks similar to the following:
Latency Apply Process in Dequeued Name Seconds Message Creation Last Dequeue Time Message Number ----------------- ------- ----------------- -------------------- -------------- APPLY$_STM1_14 1 15:22:15 06/13/05 15:22:16 06/13/05 502129
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 each apply process:
The apply process name
The number of the coordinator in the process name (apnn)
The session identifier of the coordinator's session
The serial number of the coordinator's session
The current state of the coordinator, either INITIALIZING, APPLYING, SHUTTING DOWN CLEANLY, or ABORTING
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
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
SELECT c.APPLY_NAME,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
c.SID,
c.SERIAL#,
c.STATE
FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Coordinator Session Apply Process Process Session Serial Name Name ID Number State ----------------- ----------- ------- ------- --------------------- APPLY_FROM_MULT1 A001 16 1 APPLYING APPLY_FROM_MULT2 A002 18 1 APPLYING
See Also:
"Coordinator Process States"The query in this section displays the following information about the transactions received, applied, and being applied by each apply process:
The apply process name
The total number of transactions received by the coordinator process since the apply process was last started
The total number of transactions successfully applied by the apply process since the apply process was last started
The number of transactions applied by the apply process that resulted in an apply error since the apply process was last started
The total number of transactions currently being applied by the apply process
The total number of transactions received by the coordinator process but ignored because the apply process had already applied the transactions since the apply process was last started
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_NAME HEADING 'Apply Process Name' FORMAT A25
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
COLUMN BEING_APPLIED HEADING 'Total|Trans Being|Applied' FORMAT 99999999
COLUMN TOTAL_IGNORED HEADING 'Total|Trans|Ignored' FORMAT 99999999
SELECT APPLY_NAME,
TOTAL_RECEIVED,
TOTAL_APPLIED,
TOTAL_ERRORS,
(TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED,
TOTAL_IGNORED
FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Total Total Total Total Total
Trans Trans Apply Trans Being Trans
Apply Process Name Received Applied Errors Applied Ignored
------------------------- --------- --------- ------ ----------- ---------
APPLY_FROM_MULT1 81 73 2 6 0
APPLY_FROM_MULT2 114 96 0 14 4
This section contains two different queries that show the capture to apply latency for a particular message. That is, for captured messages, these queries show the amount of time between when the message was created at a source database and when the message was applied by the apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The other uses the DBA_APPLY_PROGRESS static data dictionary view.
Note:
These queries assume that the apply process applies captured messages, not user-enqueued messages.The two queues differ in the following ways:
The apply process must be enabled when you run the query on the 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.
The query on the V$STREAMS_APPLY_COORDINATOR view can show the latency for a more recent transaction than the query on the DBA_APPLY_PROGRESS view.
Both queries display the following information about a message applied by each apply process:
The apply process name.
The capture to apply latency for the message.
The message creation time. For captured messages, the message creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data at the source database for the message.
The time when the message was applied by the apply process.
The message number of the message.
Run the following query to display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for a message for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999
SELECT APPLY_NAME,
(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
HWM_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Applied Apply Process Message Name Latency in Seconds Message Creation Apply Time Number ----------------- ------------------ ----------------- ----------------- ------- APPLY$_STM1_14 4 14:05:13 06/13/05 14:05:17 06/13/05 498215
Run the following query to display the capture to apply latency using the DBA_APPLY_PROGRESS view for a message for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999
SELECT APPLY_NAME,
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER
FROM DBA_APPLY_PROGRESS;
Your output looks similar to the following:
Applied Apply Process Message Name Latency in Seconds Message Creation Apply Time Number ----------------- ------------------ ----------------- ----------------- ------- APPLY$_STM1_14 33 14:05:13 06/13/05 14:05:46 06/13/05 498215
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 messages 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 each apply process:
The name of the apply process.
The process names of the parallel execution servers, in order.
The current state of each apply server:
IDLE
RECORD LOW-WATERMARK
ADD PARTITION
DROP PARTITION
EXECUTE TRANSACTION
WAIT COMMIT
WAIT DEPENDENCY
WAIT FOR NEXT CHUNK
TRANSACTION CLEANUP
The total number of transactions assigned to each apply server since the last time the apply process was started. A transaction can contain more than one message.
The total number of messages applied by each apply server since the last time the apply process was started.
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display information about the apply servers for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process Name' 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|Messages|Applied' FORMAT 99999999
SELECT r.APPLY_NAME,
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
WHERE r.SID = s.SID AND
r.SERIAL# = s.SERIAL#
ORDER BY r.APPLY_NAME, r.SERVER_ID;
Your output looks similar to the following:
Total Total
Transactions Messages
Apply Process Name Process Name State Assigned Applied
---------------------- ------------ ----------------- ------------ ----------
APPLY P001 IDLE 94 2141
APPLY P002 IDLE 12 276
APPLY P003 IDLE 0 0
See Also:
"Apply Server States"In some environments, an apply process might not use all of the apply servers available to it. For example, apply process parallelism can 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 messages 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 Messages 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 Messages 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 started. If the parallelism setting for an apply process is higher than the effective parallelism for the apply process, then consider lowering the parallelism setting.
You can specify a destination queue for a rule using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package. If an apply process has such a rule in its positive rule set, and a message satisfies the rule, then the apply process enqueues the message into the destination queue.
To view destination queue settings for rules, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN DESTINATION_QUEUE_NAME HEADING 'Destination Queue' FORMAT A30 SELECT RULE_OWNER, RULE_NAME, DESTINATION_QUEUE_NAME FROM DBA_APPLY_ENQUEUE;
Your output looks similar to the following:
Rule Owner Rule Name Destination Queue --------------- --------------- ------------------------------ STRMADMIN DEPARTMENTS17 "STRMADMIN"."STREAMS_QUEUE"
You can specify an execution directive for a rule using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package. An execution directive controls whether a message that satisfies the specified rule is executed by an apply process. If an apply process has a rule in its positive rule set with NO for its execution directive, and a message satisfies the rule, then the apply process does not execute the message and does not send the message to any apply handler.
To view each rule with NO for its execution directive, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20 SELECT RULE_OWNER, RULE_NAME FROM DBA_APPLY_EXECUTE WHERE EXECUTE_EVENT = 'NO';
Your output looks similar to the following:
Rule Owner Rule Name -------------------- -------------------- STRMADMIN DEPARTMENTS18
To check for apply errors, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;
If there are any apply errors, then your output looks similar to the following:
Apply Local Messages in Process Source Transaction Error Name Database ID Error Number Error Message Transaction ---------- ---------- ----------- ------------ -------------------- ----------- APPLY_FROM MULT3.NET 1.62.948 1403 ORA-01403: no data f 1 _MULT3 ound APPLY_FROM MULT2.NET 1.54.948 1403 ORA-01403: no data f 1 _MULT2 ound
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 might 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:
Oracle Streams Replication Administrator's Guide for information about the possible causes of apply errors
Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session
This section contains SQL scripts that you can use to display detailed information about the error transactions in the error queue in a database. These scripts are designed to display information about LCRs, but you can extend them to display information about any non-LCR messages used in your environment as well.
To use these scripts, complete the following steps:
Create a Procedure that Prints the Value in an ANYDATA Object
Create a Procedure that Prints All the LCRs in the Error Queue
Create a Procedure that Prints All the Error LCRs for a Transaction
Note:
These scripts display only the first 253 characters forVARCHAR2 values in LCRs.Step 1 Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View
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. Running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package on a user grants this privilege to the user.
To grant this privilege to a user directly, complete the following steps:
Connect as an administrative user who can grant privileges.
Grant 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;
Grant EXECUTE privilege on the DBMS_APPLY_ADM package. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
Connect to the database as the user to whom you granted the privilege in Step 2 and 3.
Step 2 Create a Procedure that Prints the Value in an ANYDATA Object
The following procedure prints the value in a specified ANYDATA object for some selected datatypes. You can add more datatypes to this procedure if you wish.
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
tn VARCHAR2(61);
str VARCHAR2(4000);
chr VARCHAR2(1000);
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(SUBSTR(str,0,253));
ELSIF tn = 'SYS.CHAR' then
res := data.GETCHAR(chr);
DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
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(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
DBMS_OUTPUT.PUT_LINE('BLOB Value');
ELSIF tn = 'SYS.BLOB' THEN
DBMS_OUTPUT.PUT_LINE('BLOB Found');
ELSE
DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
END IF;
END print_any;
/
Step 3 Create a Procedure that Prints a Specified LCR
The following procedure prints a specified LCR. It calls the print_any procedure created in "Create a Procedure that Prints the Value in an ANYDATA Object".
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN 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;
ext_attr ANYDATA;
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);
-- Print extra attributes in DDL LCR
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
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;
-- Print extra attributes in row LCR
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm);
END IF;
END print_lcr;
/
Step 4 Create a Procedure that Prints All the LCRs in the Error Queue
The following procedure prints all of the LCRs in all of the error queues. 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_NUMBER,
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);
msgno NUMBER;
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2(255);
lcr 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;
msgno := r.MESSAGE_NUMBER;
errno := r.ERROR_NUMBER;
errmsg := r.ERROR_MESSAGE;
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 in Message: '|| msgno);
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
Step 5 Create a Procedure that Prints All the Error LCRs for a Transaction
The following procedure prints all the LCRs in the error 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);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(128);
lcr ANYDATA;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, 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 in Message: '|| msgno);
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 to 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')
