34 Troubleshooting Rules and Rule-Based Transformations

When a capture process, synchronous capture, propagation, apply process, or messaging client is not behaving as expected, the problem might be that rule sets, rules, or rule-based transformations for the Oracle Streams client are not configured properly. Use the following sections to identify and resolve problems with rule sets, rules, and rule-based transformations:

The following topics describe identifying and resolving common problems with rules and rule-based transformations in an Oracle Streams environment:

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

Schema and global rules in the positive rule set for an Oracle Streams client instruct the Oracle Streams client to perform its task for all of the messages relating to a particular schema or database, respectively. Schema and global rules in the negative rule set for an Oracle Streams client instruct the Oracle Streams client to discard all of the messages relating to a particular schema or database, respectively. If an Oracle Streams client is not behaving as expected, then it might be because schema or global rules are not configured properly for the Oracle Streams client.

For example, suppose a database is running an apply process named strm01_apply, and you want this apply process to apply LCRs containing changes to the hr schema. If the apply process uses a negative rule set, then ensure that there are no schema rules that evaluate to TRUE for this schema in the negative rule set. Such rules cause the apply process to discard LCRs containing changes to the schema. "Displaying the Rules in the Negative Rule Set for an Oracle Streams Client" contains an example of a query that shows such rules.

If the query returns any such rules, then the rules returned might be causing the apply process to discard changes to the schema. If this query returns no rows, then ensure that there are schema rules in the positive rule set for the apply process that evaluate to TRUE for the schema. "Displaying the Rules in the Positive Rule Set for an Oracle Streams Client" contains an example of a query that shows such rules.

Checking Table Rules

Table rules in the positive rule set for an Oracle Streams client instruct the Oracle Streams client to perform its task for the messages relating to one or more particular tables. Table rules in the negative rule set for an Oracle Streams client instruct the Oracle Streams client to discard the messages relating to one or more particular tables.

If an Oracle Streams client is not behaving as expected for a particular table, then it might be for one of the following reasons:

  • One or more global rules in the rule sets for the Oracle Streams client instruct the Oracle Streams client to behave in a particular way for messages relating to the table because the table is in a specific database. That is, a global rule in the negative rule set for the Oracle Streams client might instruct the Oracle Streams client to discard all messages from the source database that contains the table, or a global rule in the positive rule set for the Oracle Streams client might instruct the Oracle Streams client to perform its task for all messages from the source database that contains the table.

  • One or more schema rules in the rule sets for the Oracle Streams client instruct the Oracle Streams client to behave in a particular way for messages relating to the table because the table is in a specific schema. That is, a schema rule in the negative rule set for the Oracle Streams client might instruct the Oracle Streams client to discard all messages relating to database objects in the schema, or a schema rule in the positive rule set for the Oracle Streams client might instruct the Oracle Streams client to perform its task for all messages relating to database objects in the schema.

  • One or more table rules in the rule sets for the Oracle Streams client instruct the Oracle Streams client to behave in a particular way for messages relating to the table.

If you are sure that no global or schema rules are causing the unexpected behavior, then you can check for table rules in the rule sets for an Oracle Streams client. For example, if you expect a capture process to capture changes to a particular table, but the capture process is not capturing these changes, then the cause might be that the rules in the positive and negative rule sets for the capture process do not instruct it to capture changes to the table.

Suppose a database is running a capture process named strm01_capture, and you want this capture process to capture changes to the hr.departments table. If the capture process uses a negative rule set, then ensure that there are no table rules that evaluate to TRUE for this table in the negative rule set. Such rules cause the capture process to discard changes to the table. "Displaying the Rules in the Negative Rule Set for an Oracle Streams Client" contains an example of a query that shows rules in a negative rule set.

If that query returns any such rules, then the rules returned might be causing the capture process to discard changes to the table. If that query returns no rules, then ensure that there are one or more table rules in the positive rule set for the capture process that evaluate to TRUE for the table. "Displaying the Rules in the Positive Rule Set for an Oracle Streams Client" contains an example of a query that shows rules in a positive rule set.

You can also determine which rules have a particular pattern in their rule condition. "Listing Each Rule that Contains a Specified Pattern in Its Condition". For example, you can find all of the rules with the string "departments" in their rule condition, and you can ensure that these rules are in the correct rule sets.

See Also:

"Table Rules Example" for more information about specifying table rules

Checking Subset Rules

A subset rule can be in the rule set used by a capture process, synchronous capture, propagation, apply process, or messaging client. A subset rule evaluates to TRUE only if a DML operation contains a change to a particular subset of rows in the table. For example, to check for table rules that evaluate to TRUE for an apply process named strm01_apply when there are changes to the hr.departments table, run the following query:

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

Notice that this query returns any subset condition for the table in the DML_CONDITION column, which is labeled "Subset Condition" in the output. In this example, subset rules are specified for the hr.departments table. These subset rules evaluate to TRUE only if an LCR contains a change that involves a row where the location_id is 1700. So, if you expected the apply process to apply all changes to the table, then these subset rules cause the apply process to discard changes that involve rows where the location_id is not 1700.

Note:

Subset rules must reside only in positive rule sets.

See Also:

Checking for Message Rules

A message rule can be in the rule set used by a propagation, apply process, or messaging client. Message rules pertain only to user messages of a specific message type, not to captured LCRs. A message rule evaluates to TRUE if a user message in a queue is of the type specified in the message rule and satisfies the rule condition of the message rule.

If you expect a propagation, apply process, or messaging client to perform its task for some user messages, but the Oracle Streams client is not performing its task for these messages, then the cause might be that the rules in the positive and negative rule sets for the Oracle Streams client do not instruct it to perform its task for these messages. Similarly, if you expect a propagation, apply process, or messaging client to discard some user messages, but the Oracle Streams client is not discarding these messages, then the cause might be that the rules in the positive and negative rule sets for the Oracle Streams client do not instruct it to discard these messages.

For example, suppose you want a messaging client named oe to dequeue messages of type oe.user_msg that satisfy the following condition:

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

If the messaging client uses a negative rule set, then ensure that there are no message rules that evaluate to TRUE for this message type in the negative rule set. Such rules cause the messaging client to discard these messages. For example, to determine whether there are any such rules in the negative rule set for the messaging client, run the following query:

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';

If this query returns any rules, then the rules returned might be causing the messaging client to discard messages. Examine the rule condition of the returned rules to determine whether these rules are causing the messaging client to discard the messages that it should be dequeuing. If this query returns no rules, then ensure that there are message rules in the positive rule set for the messaging client that evaluate to TRUE for this message type and condition.

For example, to determine whether any message rules evaluate to TRUE for this message type in the positive rule set for the messaging client, run the following query:

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';

If you have message rules that evaluate to TRUE for this message type in the positive rule set for the messaging client, then these rules are returned. In this case, your output looks similar to the following:

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

Examine the rule condition for the rules returned to determine whether they instruct the messaging client to dequeue the proper messages. Based on these results, the messaging client named oe should dequeue messages of oe.user_msg type that satisfy condition shown in the output. In other words, no rule in the negative messaging client rule set discards these messages, and a rule exists in the positive messaging client rule set that evaluates to TRUE when the messaging client finds a message in its queue of the oe.user_msg type that satisfies the rule condition.

See Also:

Resolving Problems with Rules

If you determine that an Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client is not behaving as expected because one or more rules must be added to the rule set for the Oracle Streams client, then you can use one of the following procedures in the DBMS_STREAMS_ADM package to add appropriate rules:

  • ADD_GLOBAL_PROPAGATION_RULES

  • ADD_GLOBAL_RULES

  • ADD_SCHEMA_PROPAGATION_RULES

  • ADD_SCHEMA_RULES

  • ADD_SUBSET_PROPAGATION_RULES

  • ADD_SUBSET_RULES

  • ADD_TABLE_PROPAGATION_RULES

  • ADD_TABLE_RULES

  • ADD_MESSAGE_PROPAGATION_RULE

  • ADD_MESSAGE_RULE

You can use the DBMS_RULE_ADM package to add customized rules, if necessary.

It is also possible that the Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client is not behaving as expected because one or more rules should be altered or removed from a rule set.

If you have the correct rules, and the relevant messages are still filtered out by an Oracle Streams capture process, propagation, or apply process, then check your trace files and alert log for a warning about a missing "multi-version data dictionary", which is an Oracle Streams data dictionary. The following information might be included in such warning messages:

  • gdbnm: Global name of the source database of the missing object

  • scn: SCN for the transaction that has been missed

If you find such messages, and you are using custom capture process rules or reusing existing capture process rules for a new destination database, then ensure that you run the appropriate procedure to prepare for instantiation:

  • PREPARE_TABLE_INSTANTIATION

  • PREPARE_SCHEMA_INSTANTIATION

  • PREPARE_GLOBAL_INSTANTIATION

Also, ensure that propagation is working from the source database to the destination database. Oracle Streams data dictionary information is propagated to the destination database and loaded into the dictionary at the destination database.

See Also:

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