The following topics describe monitoring Oracle Streams apply processes:
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
Determining Which Apply Processes Use Combined Capture and Apply
Displaying the Substitute Key Columns Specified at a Destination Database
Note:
The Oracle Streams tool in Oracle Enterprise Manager is also an excellent way to monitor an Oracle Streams environment. See Oracle Database 2 Day + Data Replication and Integration Guide and the online Help for the Oracle Streams tool for more information.See Also:
Chapter 17, "Managing Oracle Streams Information Consumption"
Oracle Database Reference for information about the data dictionary views described in this chapter
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 either can apply either captured LCRs, or an apply process can apply persistent LCRs and persistent user messages.
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 'Applies Captured LCRs?' FORMAT A22 COLUMN APPLY_USER HEADING 'Apply User' FORMAT A20 SELECT APPLY_NAME, APPLY_CAPTURED, APPLY_USER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name Applies Captured LCRs? Apply User -------------------- ---------------------- -------------------- STRM01_APPLY YES STRMADMIN SYNC_APPLY NO STRMADMIN
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 A30
COLUMN VALUE HEADING 'Value' FORMAT A22
COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10
SELECT APPLY_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_APPLY_PARAMETERS;
Your output looks similar to the following:
Apply Process Set by Name Parameter Value User? --------------- ------------------------------ ---------------------- ---------- APPLY$_DB_3 ALLOW_DUPLICATE_ROWS N NO APPLY$_DB_3 APPLY_SEQUENCE_NEXTVAL N NO APPLY$_DB_3 COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS NO APPLY$_DB_3 COMPARE_KEY_ONLY N NO APPLY$_DB_3 DISABLE_ON_ERROR Y NO APPLY$_DB_3 DISABLE_ON_LIMIT N NO APPLY$_DB_3 GROUPTRANSOPS 250 NO APPLY$_DB_3 IGNORE_TRANSACTION NO APPLY$_DB_3 MAXIMUM_SCN INFINITE NO APPLY$_DB_3 MAX_SGA_SIZE INFINITE NO APPLY$_DB_3 PARALLELISM 4 NO APPLY$_DB_3 PRESERVE_ENCRYPTION Y NO APPLY$_DB_3 RTRIM_ON_IMPLICIT_CONVERSION Y NO APPLY$_DB_3 STARTUP_SECONDS 0 NO APPLY$_DB_3 TIME_LIMIT INFINITE NO APPLY$_DB_3 TRACE_LEVEL 0 NO APPLY$_DB_3 TRANSACTION_LIMIT INFINITE NO APPLY$_DB_3 TXN_AGE_SPILL_THRESHOLD 900 NO APPLY$_DB_3 TXN_LCR_SPILL_THRESHOLD 10000 NO APPLY$_DB_3 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 was set by a user and might or might not be set to its default value.See Also:
Oracle Database 2 Day + Data Replication and Integration Guide
The DBMS_APPLY_ADM.SET_PARAMETER procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the apply process parameters
This section contains instructions for displaying information about the apply handlers for apply processes.
This section contains these topics:
The following sections contain instructions for displaying information about DML handlers:
See Also:
You can display the following information about all of the DML handlers in a database, including all statement DML handlers and all procedure DML handlers:
The owner and name of the table for which the DML handler is set
The operation for which the DML handler is set
The name of the DML handler
The type of the DML handler, either statement or procedure
The name of the apply process that uses the DML handler
To display this information for each DML handler in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A7
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A11
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN HANDLER HEADING 'DML Handler' FORMAT A13
COLUMN HANDLER_TYPE HEADING 'Handler|Type' FORMAT A9
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
NVL(USER_PROCEDURE,HANDLER_NAME) Handler,
DECODE(HANDLER_TYPE,'PROCEDURE HANDLER','PROCEDURE','STMT HANDLER',
'STATEMENT','UNKNOWN') HANDLER_TYPE,
APPLY_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE ERROR_HANDLER = 'N' AND
APPLY_DATABASE_LINK IS NULL
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Apply
Table Handler Process
Owner Table Name Operation DML Handler Type Name
------- ----------- --------- ------------- --------- ---------------
HR DEPARTMENTS UPDATE "STRMADMIN"." PROCEDURE
SQL_GEN_DEP"
HR JOBS UPDATE TRACK_JOBS STATEMENT APPLY$_PROD_25
OE ORDERS INSERT MODIFY_ORDERS STATEMENT APPLY$_PROD_25
Because Apply Process Name is NULL for the strmadmin.sql_gen_dep procedure DML handler, this handler is a general handler that runs for all of the local apply processes.
The following sections contain queries that display information about the statement DML handlers in a database:
You can display the following information about the statement DML handlers in a database:
The name of the statement DML handler
The comment for the statement DML handler
The time when the statement DML handler was created
The time when the statement DML handler was last modified
To display this information for each statement DML handler in a database, run the following query:
COLUMN HANDLER_NAME HEADING 'Handler Name' FORMAT A15
COLUMN HANDLER_COMMENT HEADING 'Comment' FORMAT A35
COLUMN CREATION_TIME HEADING 'Creation|Time' FORMAT A10
COLUMN MODIFICATION_TIME HEADING 'Last|Change|Time' FORMAT A10
SELECT HANDLER_NAME,
HANDLER_COMMENT,
CREATION_TIME,
MODIFICATION_TIME
FROM DBA_STREAMS_STMT_HANDLERS
ORDER BY HANDLER_NAME;
Your output looks similar to the following:
Last
Creation Change
Handler Name Comment Time Time
--------------- ----------------------------------- ---------- ----------
MODIFY_ORDERS Modifies inserts into the orders ta 12-MAR-09
ble 07.59.56.9
46180 AM
TRACK_JOBS Tracks updates to the jobs table 11-MAR-09
10.47.52.7
76489 AM
When the MODIFICATION_TIME is NULL, shown in this output by Last Change Time, it indicates that the handler has not been modified since its creation.
When you specify a statement DML handler using the ADD_STMT_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can either specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally. If a statement DML handler for an operation on a table is used by a specific apply process, and another statement DML handler is a general handler for the same operation on the same table, then both handlers are invoked when an apply process dequeues a row LCR with the operation on the table. Each statement DML handler receives the original row LCR, and the statement DML handlers can execute in any order.
You can display the following information about the statement DML handlers used by the apply processes in the database:
The owner and name of the table for which the statement DML handler is set
The operation for which the statement DML handler is set
The name of the apply process that uses the statement DML handler
The name of the statement DML handler
To display this information for the statement DML handlers used by each apply process, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN HANDLER_NAME HEADING 'Statement DML|Handler Name' FORMAT A30
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
APPLY_NAME,
HANDLER_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE HANDLER_TYPE='STMT HANDLER'
ORDER BY OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME;
Your output looks similar to the following:
Table Apply Process Statement DML Owner Table Name Operation Name Handler Name ---------- ---------- --------- --------------- ------------------------------ HR JOBS UPDATE APPLY$_PROD_25 TRACK_JOBS OE ORDERS INSERT APPLY$_PROD_25 MODIFY_ORDERS
When Apply Process Name is NULL for a statement DML handler, the handler is a general handler that runs for all of the local apply processes.
The query in this section displays the following information about the statements in statement DML handlers in a database:
The name of the statement DML handler that includes each statement
The execution order of each statement
The text of each statement
To display this information, run the following query:
COLUMN HANDLER_NAME HEADING 'Statement|Handler' FORMAT A15
COLUMN EXECUTION_SEQUENCE HEADING 'Execution|Sequence' FORMAT 999999
COLUMN STATEMENT HEADING 'Statement' FORMAT A50
SET LONG 8000
SET PAGES 8000
SELECT HANDLER_NAME,
EXECUTION_SEQUENCE,
STATEMENT
FROM DBA_STREAMS_STMTS
ORDER BY HANDLER_NAME, EXECUTION_SEQUENCE;
Your output looks similar to the following:
Statement Execution
Handler Sequence Statement
--------------- --------- --------------------------------------------------
MODIFY_ORDERS 1 INSERT INTO oe.orders(
order_id,
order_date,
order_mode,
customer_id,
order_status,
order_total,
sales_rep_id,
promotion_id)
VALUES(
:new.order_id,
:new.order_date,
:new.order_mode,
:new.customer_id,
DECODE(:new.order_status, 1, 2, :new.
order_status),
:new.order_total,
:new.sales_rep_id,
:new.promotion_id)
TRACK_JOBS 10 :lcr.execute TRUE
TRACK_JOBS 20 INSERT INTO hr.track_jobs(
change_id,
job_id,
job_title,
min_salary_old,
min_salary_new,
max_salary_old,
max_salary_new,
timestamp)
VALUES(
hr.track_jobs_seq.NEXTVAL,
:new.job_id,
:new.job_title,
:old.min_salary,
:new.min_salary,
:old.max_salary,
:new.max_salary,
:source_time)
When you specify a local procedure DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can either specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally, when appropriate. A specific procedure DML handler takes precedence over a generic procedure DML handler. A DML handler is run for a specified operation on a specific table.
To display the procedure DML handler for each apply process in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A15
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A25
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
USER_PROCEDURE,
APPLY_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE ERROR_HANDLER = 'N' AND
USER_PROCEDURE IS NOT NULL
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process Owner Table Name Operation Handler Procedure Name ----------- --------------- --------- ------------------------- --------------- HR DEPARTMENTS UPDATE "STRMADMIN"."SQL_GEN_DEP"
Because Apply Process Name is NULL for the strmadmin.sql_gen_dep procedure DML handler, this handler is a general handler that runs for all of the local apply processes.
To display the DDL handler for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20 COLUMN DDL_HANDLER HEADING 'DDL Handler' FORMAT A40 SELECT APPLY_NAME, DDL_HANDLER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name DDL Handler -------------------- ---------------------------------------- STREP01_APPLY "STRMADMIN"."HISTORY_DDL"
See Also:
"Managing a DDL Handler"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"
You can display the following information about each precommit handler used by an apply process in a database by running the query in this section:
The apply process name.
The owner and name of the precommit handler
The type of messages applied by the apply process. An apply process either can apply either captured LCRs, or an apply process can apply persistent LCRs and persistent user messages.
To display each this information for each precommit handler in the database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A15 COLUMN PRECOMMIT_HANDLER HEADING 'Precommit Handler' FORMAT A30 COLUMN APPLY_CAPTURED HEADING 'Applies Captured|Messages?' FORMAT A20 SELECT APPLY_NAME, PRECOMMIT_HANDLER, APPLY_CAPTURED FROM DBA_APPLY WHERE PRECOMMIT_HANDLER IS NOT NULL;
Your output looks similar to the following:
Applies Captured
Apply Process Name Precommit Handler Messages?
-------------------- ------------------------------ --------------------
STRM01_APPLY "STRMADMIN"."HISTORY_COMMIT" YES
The query in this section displays the following session information about each session associated with a apply process in a database:
The apply process component
The session identifier
The serial number
The operating system process identification number
The process names of the coordinator process, the reader process, and the apply servers
To display this information for each capture process in a database, run the following query:
COLUMN ACTION HEADING 'Apply Process Component' FORMAT A30
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Process|Names' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='Streams' AND
ACTION LIKE '%Apply%';
Your output looks similar to the following:
Session
Serial Operating System Process
Apply Process Component Session ID Number Process Number Names
------------------------------ ---------- --------- ----------------- -------
APPLY$_EMDBB_3 - Apply Coordin 17 3040 9863 AP01
ator
APPLY$_EMDBB_3 - Apply Server 58 52788 9869 AS02
APPLY$_EMDBB_3 - Apply Reader 63 21 9865 AS01
APPLY$_EMDBB_3 - Apply Server 64 37 9872 AS03
APPLY$_EMDBB_3 - Apply Server 67 22 9875 AS04
APPLY$_EMDBB_3 - Apply Server 69 1 9877 AS05
See Also:
"Apply Process Subcomponents"The reader server for an apply process dequeues messages from the queue. The reader server is a process 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. An apply process either can dequeue either captured LCRs, or an apply process can dequeue persistent LCRs and persistent user messages.
The name of the process used by the reader server. The process name is in the form ASnn, where nn can be letters and numbers.
The current state of the reader server
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 'Dequeues Captured|Messages?' FORMAT A17
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,
ap.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 Dequeues Captured Process Total Messages Name Messages? Name State Dequeued --------------- ----------------- ------- ----------------- -------------- APPLY_SPOKE YES AS01 DEQUEUE MESSAGES 54
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 LCRs, 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 any other type of message, 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 LCRs, 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 messages enqueued by an application or apply process, the message creation time is the last time the message was enqueued. A 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 999999
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 9999999999
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, where nn can include letters and numbers
The session identifier of the coordinator's session
The serial number of the coordinator's session
The current state of the coordinator
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_SPOKE AP01 944 5 IDLE
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 complete transactions that the coordinator process has received but has not yet assigned to any apply servers
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 A20
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 UNASSIGNED_COMPLETE_TXNS HEADING 'Total|Unnasigned|Trans' 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,
UNASSIGNED_COMPLETE_TXNS,
TOTAL_IGNORED
FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Total Total Total Total Total Total
Trans Trans Apply Trans Being Unnasigned Trans
Apply Process Name Received Applied Errors Applied Trans Ignored
-------------------- --------- --------- ------ ----------- ---------- ---------
APPLY_FROM_MULT1 81 73 2 6 4 0
APPLY_FROM_MULT2 114 96 0 14 7 4
This section contains two different queries that show the capture to apply latency for a particular message. That is, these queries show the amount of time between when the message was created at a source database and when the message was applied by an apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The other uses the DBA_APPLY_PROGRESS static data dictionary view.
The two queries differ in the following ways:
You can use the query on the V$STREAMS_APPLY_COORDINATOR view to determine capture to apply latency for captured LCRs or persistent LCRs. However, the query on the DBA_APPLY_PROGRESS view only returns results for captured LCRs.
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. Therefore, if the apply process is currently disabled and change capture is performed by a capture process, then run the query on the DBA_APPLY_PROGRESS view to determine the capture to apply latency.
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 LCRs, 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 persistent LCRs, the message creation time is the time when the LCR was constructed.
The time when the message was applied by the apply process
The message number of the message
Note:
These queries do not pertain to persistent user messages.Run the following query to display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for a captured LCR or a persistent LCR for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A13
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 9999999999
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:
Apply Process Message Name Latency in Seconds Message Creation Apply Time Number ------------- ------------------ ----------------- ----------------- ----------- APPLY$_DA_2 2 13:00:10 07/14/10 13:00:12 07/14/10 672733
Run the following query to display the capture to apply latency using the DBA_APPLY_PROGRESS view for a captured LCR 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 9999999999
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 process.
The query in this section displays the following information about the apply servers for each apply process:
The name of the apply process.
The names of the reader server processes, in order. Each process name is in the form ASnn, where nn can be letters and numbers.
The current state of each apply server
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$_DA_2 AS02 IDLE 1012 109190
APPLY$_DA_2 AS03 IDLE 996 107568
APPLY$_DA_2 AS04 IDLE 1006 108648
APPLY$_DA_2 AS05 IDLE 987 10659
6
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
4 0
In this case, apply servers 3 and 4 have not been used by the apply process since it was last started. If the parallelism setting for an apply process is much higher than the effective parallelism for the apply process, then consider lowering the parallelism setting. For example, if the parallelism setting is 6, but the effective parallelism for the apply process is 2, then consider lowering the 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
A combined capture and apply environment is efficient because the capture process acts as the propagation sender that sends logical change records (LCRs) directly to the propagation receiver.
When an apply process uses combined capture and apply, the following columns in the V$STREAMS_APPLY_READER data dictionary view are populated:
PROXY_SID shows the session ID of the propagation receiver
PROXY_SERIAL shows the serial number of the propagation receiver
PROXY_SPID shows the process identification number of the propagation receiver
CAPTURE_BYTES_RECEIVED shows the number of bytes received by the apply process from the capture process since the apply process last started
When an apply process does not use combined capture and apply, the PROXY_SID and PROXY_SERIAL columns are 0 (zero), and the PROXY_SPID and CAPTURE_BYTES_RECEIVED columns are not populated.
To determine whether an apply process uses combined capture and apply, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN PROXY_SID HEADING 'Propagation|Receiver|Session ID' FORMAT 99999999
COLUMN PROXY_SERIAL HEADING 'Propagation|ReceiverSerial|Number' FORMAT 99999999
COLUMN PROXY_SPID HEADING 'Propagation|Receiver|Process ID' FORMAT 99999999999
COLUMN CAPTURE_BYTES_RECEIVED HEADING 'Number of|Bytes Received' FORMAT 9999999999
SELECT APPLY_NAME,
PROXY_SID,
PROXY_SERIAL,
PROXY_SPID,
CAPTURE_BYTES_RECEIVED
FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Propagation Propagation Propagation
Receiver ReceiverSerial Receiver Number of
Apply Process Name Session ID Number Process ID Bytes Received
-------------------- ----------- -------------- ------------ --------------
APPLY_SPOKE1 940 1 22636 4358614
APPLY_SPOKE2 928 4 29154 4310581
This output indicates that the apply_spoke1 apply process uses combined capture and apply. Since it last started, this apply process has received 4358614 bytes from the capture process. The apply_spoke2 apply process also uses combined capture and apply. Since it last started, this apply process has received 4310581 bytes from the capture process.
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. You can use substitute key columns 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
Note:
This query shows the database link in the last column if the substitute key columns are for a remote non-Oracle database. The last column isNULL if a substitute key column is specified for the local destination database.The following sections contain queries that display information about virtual dependency definitions in a database:
See Also:
"Apply Processes and Dependencies" for more information about virtual dependency definitionsTo display the value dependencies in a database, run the following query:
COLUMN DEPENDENCY_NAME HEADING 'Dependency Name' FORMAT A25
COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A15
SELECT DEPENDENCY_NAME,
OBJECT_OWNER,
OBJECT_NAME,
COLUMN_NAME
FROM DBA_APPLY_VALUE_DEPENDENCIES;
Your output should look similar to the following:
Dependency Name Object Owner Object Name Column Name ------------------------- --------------- -------------------- --------------- ORDER_ID_FOREIGN_KEY OE ORDERS ORDER_ID ORDER_ID_FOREIGN_KEY OE ORDER_ITEMS ORDER_ID KEY_53_FOREIGN_KEY US_DESIGNS ALL_DESIGNS_SUMMARY KEY_53 KEY_53_FOREIGN_KEY US_DESIGNS DESIGN_53 KEY_53
This output shows the following value dependencies:
The order_id_foreign_key value dependency describes a dependency between the order_id column in the oe.orders table and the order_id column in the oe.order_items table.
The key_53_foreign_key value dependency describes a dependency between the key_53 column in the us_designs.all_designs_summary table and the key_53 column in the us_designs.design_53 table.
To display the object dependencies in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN PARENT_OBJECT_OWNER HEADING 'Parent Object Owner' FORMAT A20
COLUMN PARENT_OBJECT_NAME HEADING 'Parent Object Name' FORMAT A20
SELECT OBJECT_OWNER,
OBJECT_NAME,
PARENT_OBJECT_OWNER,
PARENT_OBJECT_NAME
FROM DBA_APPLY_OBJECT_DEPENDENCIES;
Your output should look similar to the following:
Object Owner Object Name Parent Object Owner Parent Object Name --------------- --------------- -------------------- -------------------- ORD CUSTOMERS ORD SHIP_ORDERS ORD ORDERS ORD SHIP_ORDERS ORD ORDER_ITEMS ORD SHIP_ORDERS
This output shows an object dependency in which the ord.ship_orders table is a parent table to the following child tables:
ord.customers
ord.orders
ord.order_items
To check for apply errors, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A11
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$_DB_2 DB.EXAMPLE 13.16.334 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
ound
APPLY$_DB_2 DB.EXAMPLE 15.17.540 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
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:
"Considerations for Applying DML Changes to Tables" 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.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:
In SQL*Plus, connect as an administrative user who can grant privileges.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
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.
The following procedure prints the value in a specified ANYDATA object for some selected data types. Optionally, you can add more data types to this procedure.
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.TIMESTAMP' THEN
res := data.GETTIMESTAMP(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
ELSIF tn= 'SYS.TIMESTAMPTZ' THEN
res := data.GETTIMESTAMPTZ(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
ELSIF tn= 'SYS.TIMESTAMPLTZ' THEN
res := data.GETTIMESTAMPLTZ(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
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;
/
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;
/
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(2000);
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
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(2000);
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 an 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')