26 Monitoring Oracle Streams Apply Processes

The following topics describe monitoring Oracle Streams apply processes:

Note:

The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the Oracle Enterprise Manager Cloud Control online help for the Oracle Streams tool for more information.

Determining the Queue, Rule Sets, and Status for Each Apply Process

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:

The ERROR_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 is ABORTED

Displaying General Information About Each Apply Process

You can display the following general information about each apply process in a database by running the query in this section:

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           

Listing the Parameter Settings for Each Apply Process

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 the Set 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:

Displaying Information About Apply Handlers

This section contains instructions for displaying information about the apply handlers for apply processes.

This section contains these topics:

Displaying Information About DML Handlers

The following sections contain instructions for displaying information about DML handlers:

Displaying Information About All DML Handlers

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.

Displaying Information About Statement DML Handlers

The following sections contain queries that display information about the statement DML handlers in a database:

Displaying 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.

Displaying the Statement DML Handlers Used by Each Apply Process

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.

Displaying All of the Statements in Statement DML Handlers

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)

Displaying Information About Procedure DML Handlers

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.

Displaying the DDL Handler for Each Apply Process

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"

Displaying All of the Error Handlers for Local Apply Processes

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.

Displaying the Message Handler for Each Apply Process

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"

Displaying the Precommit Handler for Each Apply Process

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:

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

Displaying Session Information About Each Apply Process

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

Displaying Information About the Reader Server for Each Apply Process

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

    See "Reader Server States".

  • 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

Monitoring Transactions and Messages Spilled by Each Apply Process

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. The V$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.

Determining Capture to Dequeue Latency for a Message

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

Displaying General Information About Each Coordinator Process

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

    See "Coordinator Process States".

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

Displaying Information About Transactions Received and Applied

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

Determining the Capture to Apply Latency for a Message for Each Apply Process

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.

Example V$STREAMS_APPLY_COORDINATOR Query for Latency

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

Example DBA_APPLY_PROGRESS Query for Latency

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

Displaying Information About the Apply Servers for Each Apply Process

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

    See "Apply Server States".

  • 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

Displaying Effective Apply Parallelism for an Apply Process

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.

Viewing Rules that Specify a Destination Queue on Apply

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"

Viewing Rules that Specify No Execution on Apply

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

Determining Which Apply Processes Use Combined Capture and Apply

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.

Displaying the Substitute Key Columns Specified at a Destination Database

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 is NULL if a substitute key column is specified for the local destination database.

Monitoring Virtual Dependency Definitions

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 definitions

Displaying Value Dependencies

To 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.

Displaying Object Dependencies

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

Checking for Apply Errors

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:

Displaying Detailed Information About Apply Errors

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:

  1. Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View

  2. Create a Procedure that Prints the Value in an ANYDATA Object

  3. Create a Procedure that Prints a Specified LCR

  4. Create a Procedure that Prints All the LCRs in the Error Queue

  5. Create a Procedure that Prints All the Error LCRs for a Transaction

Note:

These scripts display only the first 253 characters for VARCHAR2 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:

  1. 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.

  2. 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;
    
  3. 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;
    
  4. 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 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;
/
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(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
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(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')