22 Troubleshooting an Oracle Streams Environment

Is Capture Process Creation or Data Dictionary Build Taking a Long Time?

If capture process creation or a data dictionary build is taking an inordinately long time, then it might be because one or more in-flight transactions have not yet committed. An in-flight transaction is one that is active during capture process creation or a data dictionary build.

To determine whether there are in-flight transactions, check the alert log for the following messages:

wait for inflight txns at this scn
Done with waiting for inflight txns at this scn

If you see only the first message in the alert log, then the capture process creation or data dictionary build is waiting for the inflight transactions and will complete after all of the in-flight transactions have committed.

Is the Capture Process Enabled?

A capture process captures changes only when it is enabled.

You can check whether a capture process is enabled, disabled, or aborted by querying the DBA_CAPTURE data dictionary view. For example, to check whether a capture process named capture is enabled, run the following query:

SELECT STATUS FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';

If the capture process is disabled, then your output looks similar to the following:

STATUS
--------
DISABLED

If the capture process is disabled, then try restarting it. If the capture process is aborted, then you might need to correct an error before you can restart it successfully.

To determine why the capture process aborted, query the DBA_CAPTURE data dictionary view or check the trace file for the capture process. The following query shows when the capture process aborted and the error that caused it to abort:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40

SELECT CAPTURE_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_CAPTURE WHERE STATUS='ABORTED';

Is the Capture Process Current?

If a capture process has not captured recent changes, then the cause might be that the capture process has fallen behind. To check, you can query the V$STREAMS_CAPTURE dynamic performance view. If capture process latency is high, then you might be able to improve performance by adjusting the setting of the parallelism capture process parameter.

Are Required Redo Log Files Missing?

When a capture process is started or restarted, it might need to scan redo log files that were generated before the log file that contains the start SCN. You can query the DBA_CAPTURE data dictionary view to determine the first SCN and start SCN for a capture process. Removing required redo log files before they are scanned by a capture process causes the capture process to abort and results in the following error in a capture process trace file:

ORA-01291: missing logfile

If you see this error, then try restoring any missing redo log file and restarting the capture process. You can check the V$LOGMNR_LOGS dynamic performance view to determine the missing SCN range, and add the relevant redo log files. A capture process needs the redo log file that includes the required checkpoint SCN and all subsequent redo log files. You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process.

If you are using the flash recovery area feature of Recovery Manager (RMAN) on a source database in an Oracle Streams environment, then RMAN might delete archived redo log files that are required by a capture process. RMAN might delete these files when the disk space used by the recovery-related files is nearing the specified disk quota for the flash recovery area. To prevent this problem in the future, complete one or more of the following actions:

• Increase the disk quota for the flash recovery area. Increasing the disk quota makes it less likely that RMAN will delete a required archived redo log file, but it will not always prevent the problem.

• Configure the source database to store archived redo log files in a location other than the flash recovery area. A local capture process will be able to use the log files in the other location if the required log files are missing in the flash recovery area. In this case, a database administrator must manage the log files manually in the other location.

RMAN always ensures that archived redo log files are backed up before it deletes them. If RMAN deletes an archived redo log file that is required by a capture process, then RMAN records this action in the alert log.

Is a Downstream Capture Process Waiting for Redo Data?

If a downstream capture process is not capturing changes, then it might be waiting for redo data to scan. Redo log files can be registered implicitly or explicitly for a downstream capture process. Redo log files registered implicitly typically are registered in one of the following ways:

• For a real-time downstream capture process, redo transport services use the log writer process (LGWR) to transfer the redo data from the source database to the standby redo log at the downstream database. Next, the archiver at the downstream database registers the redo log files with the downstream capture process when it archives them.

• For an archived-log downstream capture process, redo transport services transfer the archived redo log files from the source database to the downstream database and register the archived redo log files with the downstream capture process.

If redo log files are registered explicitly for a downstream capture process, then you must manually transfer the redo log files to the downstream database and register them with the downstream capture process.

Regardless of whether the redo log files are registered implicitly or explicitly, the downstream capture process can capture changes made to the source database only if the appropriate redo log files are registered with the downstream capture process. You can query the V$STREAMS_CAPTURE dynamic performance view to determine whether a downstream capture process is waiting for a redo log file. For example, run the following query for a downstream capture process named strm05_capture:

SELECT STATE FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME='STRM05_CAPTURE';

If the capture process state is either WAITING FOR DICTIONARY REDO or WAITING FOR REDO, then verify that the redo log files have been registered with the downstream capture process by querying the DBA_REGISTERED_ARCHIVED_LOG and DBA_CAPTURE data dictionary views. For example, the following query lists the redo log files currently registered with the strm05_capture downstream capture process:

COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 9999999
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A30
COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10

SELECT r.SOURCE_DATABASE,
       r.SEQUENCE#, 
       r.NAME, 
       r.DICTIONARY_BEGIN, 
       r.DICTIONARY_END 
  FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
  WHERE c.CAPTURE_NAME = 'STRM05_CAPTURE' AND 
        r.CONSUMER_NAME = c.CAPTURE_NAME;

If this query does not return any rows, then no redo log files are registered with the capture process currently. If you configured redo transport services to transfer redo data from the source database to the downstream database for this capture process, then ensure that the redo transport services are configured correctly. If the redo transport services are configured correctly, then run the ALTER SYSTEM ARCHIVE LOG CURRENT statement at the source database to archive a log file. If you did not configure redo transport services to transfer redo data, then ensure that the method you are using for log file transfer and registration is working properly. You can register log files explicitly using an ALTER DATABASE REGISTER LOGICAL LOGFILE statement.

If the downstream capture process is waiting for redo, then it also is possible that there is a problem with the network connection between the source database and the downstream database. There also might be a problem with the log file transfer method. Check your network connection and log file transfer method to ensure that they are working properly.

If you configured a real-time downstream capture process, and no redo log files are registered with the capture process, then try switching the log file at the source database. You might need to switch the log file more than once if there is little or no activity at the source database.

Also, if you plan to use a downstream capture process to capture changes to historical data, then consider the following additional issues:

• Both the source database that generates the redo log files and the database that runs a downstream capture process must be Oracle Database 10g or later databases.

• The start of a data dictionary build must be present in the oldest redo log file added, and the capture process must be configured with a first SCN that matches the start of the data dictionary build.

• The database objects for which the capture process will capture changes must be prepared for instantiation at the source database, not at the downstream database. In addition, you cannot specify a time in the past when you prepare objects for instantiation. Objects are always prepared for instantiation at the current database SCN, and only changes to a database object that occurred after the object was prepared for instantiation can be captured by a capture process.

Are You Trying to Configure Downstream Capture Without DBMS_CAPTURE_ADM?

You must use the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a downstream capture process. If you try to create a capture process using a procedure in the DBMS_STREAMS_ADM package and specify a source database name that does not match the global name of the local database, then Oracle returns the following error:

ORA-26678: Oracle Streams capture process must be created first

To correct the problem, use the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create the downstream capture process.

If you are trying to create a local capture process using a procedure in the DBMS_STREAMS_ADM package, and you encounter this error, then ensure that the database name specified in the source_database parameter of the procedure you are running matches the global name of the local database.

Are More Actions Required for Downstream Capture Without a Database Link?

When downstream capture is configured with a database link, the database link can be used to perform operations at the source database and obtain information from the source database automatically. When downstream capture is configured without a database link, these actions must be performed manually, and the information must be obtained manually. If you do not complete these actions manually, then errors result when you try to create the downstream capture process.

Specifically, the following actions must be performed manually when you configure downstream capture without a database link:

• In certain situations, you must run the DBMS_CAPTURE_ADM.BUILD procedure at the source database to extract the data dictionary at the source database to the redo log before a capture process is created.

• You must prepare the source database objects for instantiation.

• You must obtain the first SCN for the downstream capture process and specify the first SCN using the first_scn parameter when you create the capture process with the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

Is a Synchronous Capture Failing to Capture Changes to Tables?

If a synchronous capture is not capturing changes to tables as you expected, then the rules in the synchronous capture rule set might not be configured properly. To avoid problems, always use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package to add rules to a synchronous capture rules set.

The following are common reasons why a synchronous capture is not capturing changes as expected:

• Global rules or schema rules are being used to try to control the behavior of the synchronous capture. A synchronous capture ignores global rules and schema rules in its rule set. A synchronous capture only captures changes that satisfy table rules and subset rules.

• The the DBMS_RULE_ADM package was used to configure the rules for a synchronous capture. A synchronous capture does not behave correctly when

  • The DBMS_RULE_ADM package is used to create rules that are added to a synchronous capture rule set.

  • The DBMS_RULE_ADM package is used to add rules to a synchronous capture rule set.

If a synchronous capture is not capturing changes to tables as expected, then complete the following steps to identify and correct problems:

1. Query the DBA_SYNC_CAPTURE_TABLES data dictionary view to determine the tables for which a synchronous capture is capturing changes. The synchronous capture captures changes to a table only if the ENABLED column is set to YES for the table.

2. If the DBA_SYNC_CAPTURE_TABLES view does not list tables for which a synchronous capture should capture changes, then use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package to add rules for the tables.

If the DBA_SYNC_CAPTURE_TABLES view shows ENABLED for a table, and a synchronous capture still does not capture changes to the table, then there might be a problem with the rule condition in the rule for the table. In this case, check the rule condition and correct any errors, or drop the rule and re-create it using the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure.

Does the Propagation Use the Correct Source and Destination Queue?

If messages are not appearing in the destination queue for a propagation as expected, then the propagation might not be configured to propagate messages from the correct source queue to the correct destination queue.

For example, to check the source queue and destination queue for a propagation named dbs1_to_dbs2, run the following query:

COLUMN SOURCE_QUEUE HEADING 'Source Queue' FORMAT A35
COLUMN DESTINATION_QUEUE HEADING 'Destination Queue' FORMAT A35

SELECT 
  p.SOURCE_QUEUE_OWNER||'.'||
    p.SOURCE_QUEUE_NAME||'@'||
    g.GLOBAL_NAME SOURCE_QUEUE, 
  p.DESTINATION_QUEUE_OWNER||'.'||
    p.DESTINATION_QUEUE_NAME||'@'||
    p.DESTINATION_DBLINK DESTINATION_QUEUE 
  FROM DBA_PROPAGATION p, GLOBAL_NAME g
  WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2';

Your output looks similar to the following:

Source Queue                        Destination Queue
----------------------------------- -----------------------------------
STRMADMIN.STREAMS_QUEUE@DBS1.NET    STRMADMIN.STREAMS_QUEUE@DBS2.NET

If the propagation is not using the correct queues, then create a new propagation. You might need to remove the existing propagation if it is not appropriate for your environment.

Is the Propagation Enabled?

For a propagation job to propagate messages, the propagation must be enabled. If messages are not being propagated by a propagation as expected, then the propagation might not be enabled.

You can find the following information about a propagation:

• The database link used to propagate messages from the source queue to the destination queue

• Whether the propagation is ENABLED, DISABLED, or ABORTED

• The date of the last error, if there are any propagation errors

• If there are any propagation errors, then the error number of the last error

• The error message of the last error, if there are any propagation errors

For example, to check whether a propagation named streams_propagation is enabled, run the following query:

COLUMN DESTINATION_DBLINK HEADING 'Database|Link'      FORMAT A10
COLUMN STATUS             HEADING 'Status'             FORMAT A8
COLUMN ERROR_DATE         HEADING 'Error|Date'
COLUMN ERROR_MESSAGE      HEADING 'Error Message'      FORMAT A50
 
SELECT DESTINATION_DBLINK,
       STATUS,
       ERROR_DATE,
       ERROR_MESSAGE
  FROM DBA_PROPAGATION
  WHERE PROPAGATION_NAME = 'STREAMS_PROPAGATION';

If the propagation is disabled currently, then your output looks similar to the following:

Database            Error
Link       Status   Date      Error Message
---------- -------- --------- --------------------------------------------------
INST2.NET  DISABLED 27-APR-05 ORA-25307: Enqueue rate too high, flow control
                              enabled

If there is a problem, then try the following actions to correct it:

• If a propagation is disabled, then you can enable it using the START_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package, if you have not done so already.

• If the propagation is disabled or aborted, and the Error Date and Error Message fields are populated, then diagnose and correct the problem based on the error message.

• If the propagation is disabled or aborted, then check the trace file for the propagation job process. The query in "Displaying the Schedule for a Propagation Job" displays the propagation job process.

• If the propagation job is enabled, but is not propagating messages, then try stopping and restarting the propagation.

Is Security Configured Properly for the ANYDATA Queue?

ANYDATA queues are secure queues, and security must be configured properly for users to be able to perform operations on them. If you use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to configure a secure ANYDATA queue, then an error is raised if the agent that SET_UP_QUEUE tries to create already exists and is associated with a user other than the user specified by queue_user in this procedure. In this case, rename or remove the existing agent using the ALTER_AQ_AGENT or DROP_AQ_AGENT procedure, respectively, in the DBMS_AQADM package. Next, retry SET_UP_QUEUE.

In addition, you might encounter one of the following errors if security is not configured properly for an ANYDATA queue:

• ORA-24093 AQ Agent not granted privileges of database user

• ORA-25224 Sender name must be specified for enqueue into secure queues

BEGIN
  DBMS_AQADM.ENABLE_DB_ACCESS(
    agent_name  => 'explicit_dq',
    db_username => 'oe');
END;
/

SELECT AGENT_NAME "Agent", DB_USERNAME "User" FROM DBA_AQ_AGENT_PRIVS;

Agent                          User
------------------------------ ------------------------------
EXPLICIT_ENQ                   OE
APPLY_OE                       OE
EXPLICIT_DQ                    OE

Is the Apply Process Enabled?

An apply process applies changes only when it is enabled.

You can check whether an apply process is enabled, disabled, or aborted by querying the DBA_APPLY data dictionary view. For example, to check whether an apply process named apply is enabled, run the following query:

SELECT STATUS FROM DBA_APPLY WHERE APPLY_NAME = 'APPLY';

If the apply process is disabled, then your output looks similar to the following:

STATUS
--------
DISABLED

If the apply process is disabled, then try restarting it. If the apply process is aborted, then you might need to correct an error before you can restart it successfully. If the apply process did not shut down cleanly, then it might not restart. In this case, it returns the following error:

ORA-26666 cannot alter STREAMS process

If this happens then, then run the STOP_APPLY procedure in the DBMS_APPLY_ADM package with the force parameter set to TRUE. Next, restart the apply process.

To determine why an apply process aborted, query the DBA_APPLY data dictionary view or check the trace files for the apply process. The following query shows when the apply process aborted and the error that caused it to abort:

COLUMN APPLY_NAME HEADING 'APPLY|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40

SELECT APPLY_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_APPLY WHERE STATUS='ABORTED';

Is the Apply Process Current?

If an apply process has not applied recent changes, then the problem might be that the apply process has fallen behind. You can check apply process latency by querying the V$STREAMS_APPLY_COORDINATOR dynamic performance view. If apply process latency is high, then you might be able to improve performance by adjusting the setting of the parallelism apply process parameter.

Does the Apply Process Apply Captured LCRs?

An apply process can apply either captured LCRs from its buffered queue, or it can apply messages from its persistent queue, but not both types of messages. Messages in a persistent queue can be persistent LCRs and persistent user messages. An apply process might not be applying messages of a one type because it was configured to apply the other type of messages.

You can check the type of messages applied by an apply process by querying the DBA_APPLY data dictionary view. For example, to check whether an apply process named apply applies captured LCRs or not, run the following query:

COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied' FORMAT A25

SELECT DECODE(APPLY_CAPTURED,
                'YES', 'Captured',
                'NO',  'Messages from Persistent Queue') APPLY_CAPTURED
  FROM DBA_APPLY
  WHERE APPLY_NAME = 'APPLY';

If the apply process applies captured LCRs, then your output looks similar to the following:

Type of Messages Applied
-------------------------
Captured

If an apply process is not applying the expected type of messages, then you might need to create a new apply process to apply the messages.

Is the Apply Process Queue Receiving the Messages to be Applied?

An apply process must receive messages in its queue before it can apply these messages. Therefore, if an apply process is applying messages captured by a capture process or a synchronous capture, then the capture process or synchronous capture that captures these messages must be configured properly. If it is a capture process, then it must also be enabled. Similarly, if messages are propagated from one or more databases before reaching the apply process, then each propagation must be enabled and must be configured properly. If a capture process, a synchronous capture, or a propagation on which the apply process depends is not enabled or is not configured properly, then the messages might never reach the apply process queue.

The rule sets used by all Oracle Streams clients, including capture processes, synchronous captures, and propagations, determine the behavior of these Oracle Streams clients. Therefore, ensure that the rule sets for any capture processes, synchronous capture, or propagations on which an apply process depends contain the correct rules. If the rules for these Oracle Streams clients are not configured properly, then the apply process queue might never receive the appropriate messages. Also, a message traveling through a stream is the composition of all of the transformations done along the path. For example, if a capture process uses subset rules and performs row migration during capture of a message, and a propagation uses a rule-based transformation on the message to change the table name, then, when the message reaches an apply process, the apply process rules must account for these transformations.

In an environment where a capture process or synchronous capture captures changes that are propagated and applied at multiple databases, you can use the following guidelines to determine whether a problem is caused by a capture process, a synchronous capture, or a propagation on which an apply process depends or by the apply process itself:

• If no other destination databases of a capture process or synchronous capture are applying the changes, then the problem is most likely caused by the capture process or synchronous capture, or by a propagation near the capture process. In this case, first ensure that the capture process or synchronous capture is configured properly, and then ensure that the propagations nearest the capture process or synchronous capture are enabled and configured properly. In the case of a capture process, also ensure that the capture process is enabled.

• If other destination databases of a capture process or synchronous capture are applying the changes, then the problem is most likely caused by the apply process itself or a propagation near the apply process. In this case, first ensure that the apply process is enabled and configured properly, and then ensure that the propagations nearest the apply process are enabled and configured properly.

Is a Custom Apply Handler Specified?

You can use apply handlers to handle messages dequeued by an apply process in a customized way. These handlers include DML handlers, DDL handlers, precommit handlers, and message handlers. If an apply process is not behaving as expected, then check the handler procedures used by the apply process, and correct any flaws. You might need to modify a handler procedure or remove it to correct an apply problem.

You can find the names of these procedures by querying the DBA_APPLY_DML_HANDLERS and DBA_APPLY data dictionary views.

Is the AQ_TM_PROCESSES Initialization Parameter Set to Zero?

The AQ_TM_PROCESSES initialization parameter controls time monitoring on queue messages and controls processing of messages with delay and expiration properties specified. In Oracle Database 10g or later, the database automatically controls these activities when the AQ_TM_PROCESSES initialization parameter is not set.

If an apply process is not applying messages, but there are messages that satisfy the apply process rule sets in the apply process queue, then ensure that the AQ_TM_PROCESSES initialization parameter is not set to zero at the destination database. If this parameter is set to zero, then unset this parameter or set it to a nonzero value and monitor the apply process to see if it begins to apply messages.

To determine whether there are messages in a buffered queue, you can query the V$BUFFERED_QUEUES and V$BUFFERED_SUBSCRIBERS dynamic performance views. To determine whether there are messages in a persistent queue, you can query the queue table for the queue.

Does the Apply User Have the Required Privileges?

If the apply user does not have explicit EXECUTE privilege on an apply handler procedure or custom rule-based transformation function, then an ORA-26808 error might result when the apply user tries to run the procedure or function. Typically, this error is causes the apply process to abort without adding errors to the DBA_APPLY_ERROR view. However, the trace file for the apply coordinator reports the error. Specifically, an error similar to the following appears in the trace file:

ORA-26808: Apply process AP01 died unexpectedly

Typically, error messages surround this message, and one or more of these messages contain the name of the procedure or function. To correct the problem, grant the required EXECUTE privilege to the apply user.

Are There Any Apply Errors in the Error Queue?

When an apply process cannot apply a message, it moves the message and all of the other messages in the same transaction into the error queue. You should check for apply errors periodically to see if there are any transactions that could not be applied.

You can check for apply errors by querying the DBA_APPLY_ERROR data dictionary view. Also, you can reexecute a particular transaction from the error queue or all of the transactions in the error queue.

Are Rules Configured Properly for the Oracle Streams Client?

If a capture process, synchronous capture, propagation, apply process, or messaging client is behaving in an unexpected way, then the problem might be that the rules in one or more of the rule sets for the Oracle Streams client are not configured properly. For example, if you expect a capture process to capture changes made to a particular table, but the capture process is not capturing these changes, then the cause might be that the rules in the rule sets used by the capture process do not instruct the capture process to capture changes to the table.

You can check the rules for a particular Oracle Streams client by querying the DBA_STREAMS_RULES data dictionary view. If you use both positive rule sets and negative rule sets in your Oracle Streams environment, then it is important to know whether a rule returned by this view is in the positive or negative rule set for a particular Oracle Streams client.

An Oracle Streams client performs an action, such as capture, propagation, apply, or dequeue, for messages that satisfy its rule sets. In general, a message satisfies the rule sets for an Oracle Streams client if no rules in the negative rule set evaluate to TRUE for the message, and at least one rule in the positive rule set evaluates to TRUE for the message.

"Rule Sets and Rule Evaluation of Messages" contains more detailed information about how a message satisfies the rule sets for an Oracle Streams client, including information about Oracle Streams client behavior when one or more rule sets are not specified.

This section includes the following subsections:

• Checking Schema and Global Rules

• Checking Table Rules

• Checking Subset Rules

• Checking for Message Rules

• Resolving Problems with Rules

COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN RULE_TYPE HEADING 'Rule Type' FORMAT A20
COLUMN DML_CONDITION HEADING 'Subset Condition' FORMAT A30

SELECT RULE_NAME, RULE_TYPE, DML_CONDITION
  FROM DBA_STREAMS_RULES
  WHERE STREAMS_NAME   = 'STRM01_APPLY' AND 
        STREAMS_TYPE   = 'APPLY' AND
        SCHEMA_NAME    = 'HR' AND
        OBJECT_NAME    = 'DEPARTMENTS';

Rule Name            Rule Type            Subset Condition
-------------------- -------------------- ------------------------------
DEPARTMENTS5         DML                  location_id=1700
DEPARTMENTS6         DML                  location_id=1700
DEPARTMENTS7         DML                  location_id=1700

:"VAR$_2".OBJECT_OWNER = 'OE' AND  :"VAR$_2".OBJECT_NAME = 'ORDERS'

COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A30

SELECT RULE_NAME, RULE_CONDITION 
  FROM DBA_STREAMS_RULES
  WHERE STREAMS_NAME       = 'OE' AND
        MESSAGE_TYPE_OWNER = 'OE' AND
        MESSAGE_TYPE_NAME  = 'USER_MSG' AND
        RULE_SET_TYPE      = 'NEGATIVE';

COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A35
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35

SELECT RULE_NAME, RULE_CONDITION 
  FROM DBA_STREAMS_RULES 
  WHERE STREAMS_NAME       = 'OE' AND
        MESSAGE_TYPE_OWNER = 'OE' AND
        MESSAGE_TYPE_NAME  = 'USER_MSG' AND
        RULE_SET_TYPE      = 'POSITIVE';

Rule Name                           Rule Condition
----------------------------------- -----------------------------------
RULE$_3                             :"VAR$_2".OBJECT_OWNER = 'OE' AND
                                    :"VAR$_2".OBJECT_NAME = 'ORDERS'

Are Declarative Rule-Based Transformations Configured Properly?

A declarative rule-based transformation is a rule-based transformation that covers one of a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL. If an Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client is not behaving as expected, then check the declarative rule-based transformations specified for the rules used by the Oracle Streams client and correct any mistakes.

The most common problems with declarative rule-based transformations are:

• The declarative rule-based transformation is specified for a table or involves columns in a table, but the schema either was not specified or was incorrectly specified when the transformation was created. If the schema is not correct in a declarative rule-based transformation, then the transformation will not be run on the appropriate LCRs. You should specify the owning schema for a table when you create a declarative rule-based transformation. If the schema is not specified when a declarative rule-based transformation is created, then the user who creates the transformation is specified for the schema by default.

  If the schema is not correct for a declarative rule-based transformation, then, to correct the problem, remove the transformation and re-create it, specifying the correct schema for each table.

• If more than one declarative rule-based transformation is specified for a particular rule, then ensure that the ordering is correct for execution of these transformations. Incorrect ordering of declarative rule-based transformations can result in errors or inconsistent data.

  If the ordering is not correct for the declarative rule-based transformation specified on a single rule, then, to correct the problem, remove the transformations and re-create them with the correct ordering.

Are the Custom Rule-Based Transformations Configured Properly?

A custom rule-based transformation is any modification by a user-defined function to a message when a rule evaluates to TRUE. A custom rule-based transformation is specified in the action context of a rule, and these action contexts contain a name-value pair with STREAMS$_TRANSFORM_FUNCTION for the name and a user-created function name for the value. This user-created function performs the transformation. If the user-created function contains any flaws, then unexpected behavior can result.

If an Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client is not behaving as expected, then check the custom rule-based transformation functions specified for the rules used by the Oracle Streams client and correct any flaws. You can find the names of these functions by querying the DBA_STREAMS_TRANSFORM_FUNCTION data dictionary view. You might need to modify a transformation function or remove a custom rule-based transformation to correct the problem. Also, ensure that the name of the function is spelled correctly when you specify the transformation for a rule.

An error caused by a custom rule-based transformation might cause a capture process, synchronous capture, propagation, apply process, or messaging client to abort. In this case, you might need to correct the transformation before the Oracle Streams client can be restarted or invoked.

Rule evaluation is done before a custom rule-based transformation. For example, if you have a transformation that changes the name of a table from emps to employees, then ensure that each rule using the transformation specifies the table name emps, rather than employees, in its rule condition.

Are Incorrectly Transformed LCRs in the Error Queue?

In some cases, incorrectly transformed LCRs might have been moved to the error queue by an apply process. When this occurs, you should examine the transaction in the error queue to analyze the feasibility of reexecuting the transaction successfully. If an abnormality is found in the transaction, then you might be able to configure a DML handler to correct the problem. The DML handler will run when you reexecute the error transaction. When a DML handler is used to correct a problem in an error transaction, the apply process that uses the DML handler should be stopped to prevent the DML handler from acting on LCRs that are not involved with the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it. Also, correct the rule-based transformation to avoid future errors.

Does a Capture Process Trace File Contain Messages About Capture Problems?

A capture process is an Oracle background process named CPnn, where nn can include letters and numbers. For example, on some operating systems, if the system identifier for a database running a capture process is hqdb and the capture process number is 01, then the trace file for the capture process starts with hqdb_CP01.

Do the Trace Files Related to Propagation Jobs Contain Messages About Problems?

Each propagation uses a propagation job that depends on one or more slave processes named jnnn, where nnn is the slave process number. For example, on some operating systems, if a slave process is 001, then the trace file for the slave process includes j001 in its name. You can check the process name by querying the PROCESS_NAME column in the DBA_QUEUE_SCHEDULES data dictionary view.

Does an Apply Process Trace File Contain Messages About Apply Problems?

An apply process is an Oracle background process named APnn, where nn can include letters and numbers. For example, on some operating systems, if the system identifier for a database running an apply process is hqdb and the apply process number is 01, then the trace file for the apply process starts with hqdb_AP01.

An apply process also uses other processes. Information about an apply process might be recorded in the trace file for one or more of these processes. The process name of the reader server and apply servers is ASnn, where nn can include letters and numbers. So, on some operating systems, if the system identifier for a database running an apply process is hqdb and the process number is 01, then the trace file that contains information about a process used by an apply process starts with hqdb_AS01.