Oracle 11g Streams: RULES (Part 2)

Exclusive offer: get 50% off this eBook here
Oracle 11g Streams Implementer's Guide

Oracle 11g Streams Implementer's Guide — Save 50%

Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook

A$35.99    A$18.00
by Ann L. R. McKinnell | January 2010 | Oracle

In this article series by Ann L.R. McKinnell and Eric Yen, we will learn all about rules in Oracle 11g Streams. We will discuss the rule components, how to go about creating our own rules and rules based transformations. We will also highlight some of the most important things we need to know while working with rules. Read Oracle 11g Streams: RULES (Part 1) here.

Rule based transformation—eat your heart out transformers!

As with all good rules, some are made to be broken; or maybe changed. In some circumstances we need to have rules that govern change. In Advance Replication, a number one rule is that a replicated table must have the same structure at all master sites. The column names and data types have to be identical or the "apply" of a deferred transaction will fail. With Streams, we can now break this rule by adding a new rule that allows the LCR to "morph" to a new structure. We call this ability Rule Based Transformation; and it is done via complex rules and action context.

When you plan your Rule Based Transformation design, you want to remember that Rule Based Transformation rules are only evaluated with positive Rule Sets. If the Rule Set is negative, the Rule Based Transformation is ignored.

Declarative versus User Created

In the real world, there are many ways to accomplish the same thing; just as there are many ways to model data. You may run into a situation where the table structure in one master database may be different from the structure of the table in another master database but data must be replicated between them. It could be that a table column at one master is a VARCHAR2, but is a DATE at another master site. Or perhaps the column does not exist at all. Rule Based Transformation provides the ability to capture the LCR and convert it to the necessary structure needed to apply it at the destination site. This is not to be confused with transformations accomplished via the DBMS_TRANSFORMATION package. That is a different fish (and doesn't swim in this stream).

A special note concerning SUBSET Rules and transformations. A SUBSET Rule has an internal row_migration transformation assigned to it when it is created. This internal transformation will always be the first one executed before any other transformations.

Another thing to keep in mind is the amount of "transformation" that will be applied to the LCR. If extensive transformations need to be made to the LCR, you may wish to consider using a custom DML handler instead to take advantage of the apply parallel capabilities.

The remainder of this section is going to use the premise that we have an LCR that we need to change a column name for, before we send it out from the source site. The LCR is generated on a table which has a different column name than the corresponding table at all the other sites. This being the case, we are going to create the transformation at the Capture process. There are two ways to accomplish this; either by using a declarative transformation or a user created transformation. We will review each, and then apply the method to our LCR that needs a column name change.

Depending on the Transformation type, you can use one of the following views to find information concerning the transformation:

  • Declarative: DBA_STREAMS_TRANSFORMATIONS
  • User Created: DBA_STREAMS_TRANSFORM_FUNCTION

Declarative Transformation

As of 10g, Oracle provides commonly used transformations in the DBMS_STREAMS_ADM package. These transformations are referred to as declarative transformations.

Declarative transformations only work with row LCR's (aka DML LCR's). The row LCR can be a Streams captured LCR (basic or synchronous), or a user created message.

The procedures allow you to add transformation rules to do the following:

  • Add a column (DBMS_STREAMS_ADM.ADD_COLUMN)
  • Delete a column (DBMS_STREAMS_ADM.DELETE_COLUMN)
  • Rename a column (DBMS_STREAMS_ADM.RENAME_COLUMN)
  • Rename a table (DBMS_STREAMS_ADM.RENAME_TABLE)
  • Rename a schema (DBMS_STREAMS_ADM.RENAME_SCHEMA)

Special considerations when DBMS_STREAMS_ADM.ADD_COLUMN

Be aware that the DBMS_STREAMS_ADM.ADD_COLUMN procedure does not support a number of data types. These include:

  • LOBS (BLOB, CLOB, NCLOB, BFILE, and so on)
  • LONG, LONG RAW, and so on
  • ROWID
  • User-defined types (including object types, REFs, varrays, nested tables, and so on)
  • Oracle-supplied types (including ANY types, XML types, spatial types, and media types)

For more information on DBMS_STREAMS_ADM Declarative Transformation subprograms, please refer to the Oracle Database PL/SQL Packages and Types Reference.

For our purposes, we want to use the DBMS_STREAMS_ADM.RENAME_COLUMN to create a declarative transformation. In our example, we will work with the JOB_HISTORY table from the Oracle Example HR Schema. We will assume that at our source database the HR.JOB_HISTORY table has a column named DEPARTMENT_ID, and at the destination database the corresponding column in the HR.JOB_HISTORY is DEPT_ID. Declarative Transformations can only be added to an existing rule. If the rules specifi ed do not exist, an error is raised. Also, the transformation will be owned by STRM_ADMIN so make sure you have explicitly granted all privileges on HR.JOB_HISTORY to STRM_ADMIN.

First we find the rule to which we wish to add the declarative transformation, logged in as STRM_ADMIN we can look at the USER_RULES view:

SQL> select * from user_rules;
RULE_NAME
------------------------------
RULE_CONDITION
-------------------------------------
RULE_EVALUATION_CONTEXT_OWNER RULE_EVALUATION_CONTEXT_NAME
------------------------------ ------------------------------
RULE_ACTION_CONTEXT(ACTX_LIST(NVN_NAME, NVN_VALUE()))
--------------------------------------------------------------
RULE_COMMENT
--------------------------------------------------------------
HR1
((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name()
= 'STRM1'
)
SYS STREAMS$_EVALUATION_CONTEXT
HR2
((:ddl.get_object_owner() = 'HR' or :ddl.get_base_table_owner() =
'HR') and :ddl
.get_source_database_name() = 'STRM1' )
SYS STREAMS$_EVALUATION_CONTEXT

HR1 is our Row LCR (:dml) rule, so we will add

To create our declarative transformation Rule, we issue the following command:

begin
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'strm_admin.HR1',
table_name => 'HR.JOB_HISTORY',
from_column_name => 'DEPARTMENT_ID',
to_column_name => 'DEPT_ID',
value_type => '*', -- default
step_number => 0, --default
operation => 'ADD' -–default
);
end;
/

We can now check the rule in the USER_RULES view:

SQL> select * from user_rules where rule_name = 'HR1';
RULE_NAME
------------------------------
RULE_CONDITION
-------------------------------------------------------------
RULE_EVALUATION_CONTEXT_OWNER RULE_EVALUATION_CONTEXT_NAME
------------------------------ ------------------------------
RULE_ACTION_CONTEXT(ACTX_LIST(NVN_NAME, NVN_VALUE()))
-------------------------------------------------------------
RULE_COMMENT
-------------------------------------------------------------
HR1
((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name()
= 'STRM1'
)
SYS STREAMS$_EVALUATION_CONTEXT
RE$NV_LIST(RE$NV_ARRAY(RE$NV_NODE('STREAMS$_INTERNAL_TRANS',
ANYDATA())))

Notice that the RULE_COMMENT now has an entry indicating the inclusion of the transformation rule.

We can also look at the DBA_STREAMS_TRANSFORMATION view:

SQL> select rule_owner, rule_name, transform_type,
2 from_column_name, to_column_name, value_type,
3 declarative_type, precedence, step_number
4 from dba_streams_transformations;
RULE_OWNER
------------------------------
RULE_NAME TRANSFORM_TYPE
------------------------------ --------------------------
FROM_COLUMN_NAME TO_COLUMN_NAME VAL
-------------------- -------------------- ---
DECLARATIVE_TYPE PRECEDENCE STEP_NUMBER
-------------------- ---------- -----------
STRM_ADMIN
HR1 DECLARATIVE TRANSFORMATION
DEPARTMENT_ID DEPT_ID *
RENAME COLUMN 2 0

To remove the declarative transformation from the rule, we use the same procedure we used to create the transformation, but set the operation parameter to REMOVE:

begin
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'strm_admin.HR1',
table_name => 'HR.JOB_HISTORY',
from_column_name => 'DEPARTMENT_ID',
to_column_name => 'DEPT_ID',
operation => 'REMOVE' -–default
);
end;
/

Note: Removing the declarative transformation does not clear the RULE_COMMENT we see in the USER_RULES view. However, it does clear the entry from the DBA_STREAMS_TRANSFORMATION view .
For more detailed information on using the DBMS_STREAMS_ADM.RENAME_COLUMN, and other declarative transformation procedures, please refer to the Oracle PL/SQL Packages and Types Reference, and the Oracle Streams Concepts and Administration Guide.

User Created Rule Based Transformations (UCRBT)

You can also create your own Rule Based Transformations. These transformations are referred to as user-created transformations (imagine that).

The steps for creating a UCRBT are pretty basic.

Create the PL/SQL function that performs the transformation.

  • The function should receive the LCR as a SYS.ANYDATA IN parameter
  • The function should return either an LCR a SYS.ANYDATA or STREAMS$_ANYDATA_ARRAY
  • If the function returns a STREAMS$_ANYDATA_ARRAY, it can only be associated with a capture rule

Grant the EXECUTE privilege on the function to the appropriate user as necessary.

Create or locate the rules for which the transformation will be used.

Set the custom rule-based transformation for each rule by running the SET_RULE_TRANSFORM_FUNCTION procedure.

In this example, we will setup a UCRBT that makes the same transformation as the previous declarative transformation. The UCRBT is going to be owned by STRM_ADMIN so make sure you have explicitly granted all privileges on HR.JOB_HISTORY to STRM_ADMIN.

The code for this example can be found in the UCRBT.sql code file.

First we create the PL/SQL function to accomplish the transformation; STRM_ADMIN will be the function owner, so make sure you are logged in as STRM_ADMIN in this example:

CREATE OR REPLACE FUNCTION DEPT_COLNAME_CHANGE (evt IN SYS.AnyData)
RETURN SYS.AnyData IS
lcr SYS.LCR$_ROW_RECORD;
obj_name VARCHAR2(30);
rc NUMBER;
BEGIN
IF evt.GetTypeName='SYS.LCR$_ROW_RECORD' THEN
rc := evt.getObject(lcr);
obj_name := lcr.GET_OBJECT_NAME();
IF obj_name = 'JOB_HISTORY' THEN
lcr.RENAME_COLUMN('DEPARTMENT_ID','DEPT_ID','*');
RETURN SYS.ANYDATA.ConvertObject(lcr);
END IF;
END IF;
RETURN evt;
END;
/

Because STRM_ADMIN is the function owner, we do not need to grant EXECUTE on the function. If the function was created in a different schema, then we would want to explicitly grant execute on the function to STRM_ADMIN.

Next we determine which rule to which to add the transformation function. You can either create a new rule at this point, or use an existing rule. We will use our HR1 rule from above (we can do this because we removed the Declarative RENAME_COLUMN transformation from the rule in our last step of the Declarative Transformation example).

select * from dba_rules;

Then, we use the DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION procedure to add the transformation function to the desired rule:

BEGIN
DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
rule_name => 'HR1',
transform_function => 'strm_admin.DEPT_COLNAME_CHANGE');
END;
/

We will now see the transformation in the DBA/ALL_STREAMS_TRANSFORM_FUNCTION view:

SQL> select * from all_streams_transform_function;
RULE_OWNER
------------------------------
RULE_NAME VALUE_TYPE
------------------------------ --------------------
TRANSFORM_FUNCTION_NAME CUSTOM_TYPE
----------------------------------- -----------
STRM_ADMIN
HR1 SYS.VARCHAR2
"STRM_ADMIN"."DEPT_COLNAME_CHANGE" ONE TO ONE

For more detailed information on UCRBT, please reference the Usage Notes for the DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION procedure in the Oracle PL/SQL Packages and Types Reference, and the Oracle Streams Concepts and Administration Guide.

Transformation Order of Execution

It is possible to have a combination of declarative and user defined transformations assigned to a single rule. This being the case, how do you know which ones get executed when? Especially, if you have not assigned step numbers. There is a default order of execution for transformation that help keep the rule from running amuck.

  • If the rule is a Subset rule, then Row Migration is always executed first
  • Next are Declarative Rule based transformations

These are further ordered by the step number specified for each transformation if they have been assigned. If the step numbers are not assigned, the transformations are executed in the following order:

  • Delete_Column
  • Rename_Column
  • Add_Column
  • Rename_Table
  • Rename_Schema
  • Last (but not the least), the User Created Rule-Based Transformation is executed.
Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: A$35.99
Book Price: A$59.99
See more
Select your format and quantity:

How the transformation is processed

The Streams process to which you assign the Rule Based Transformation determines when the transformation is applied to the LCR. The transformation is only applied if the rule belongs to a positive rule set and the LCR evaluates to true for the rule (if it belonged to a negative rule then evaluating to TRUE would mean that we don't send the change—so why transform it?).

At the Capture (basic and synchronous)

  1. The Capture process creates the LCR from the redo log change
  2. The transformation is applied (if the rule evaluates to TRUE)
  3. The transformed LCR is stored in the Capture Queue

At the Propagation

  1. The Propagation process begins the dequeue of the LCR
  2. The transformation is applied to the LCR (if the rule evaluates to TRUE)
  3. The Propagation process completes the dequeue of the LCR
  4. The transformed LCR is sent to the destination queue

At the Apply

  1. The Apply process begins the dequeue of the LCR
  2. The transformation is applied to the LCR (if the rule evaluates to TRUE)
  3. The Apply process completes the dequeue of the LCR
  4. The transformed LCR is applied at the destination

Transformation Errors

If the transformation errors, it has significant ramifications on the overall Streams processes. To protect data integrity between the source and destination databases, stringent rules are put in place. In most cases, the Streams process that is performing the transformation is disabled if there is an error. This means that all Streams confi gurations dependent on that process come to a halt until the error is addressed. The rule of thumb here is to make sure you have very thorough exception handling in your transformation PL/SQL packages.

At the Capture (basic and synchronous)

If the transformation is declarative and the error can be ignored (like removing a column that does not exist), the error is ignored and the process continues.

If the transformation is declarative and the error cannot be ignored, or if the transformation is user created; the LCR is not captured, the error is raised to the Capture process, and the Capture process is disabled.

At the Propagation

The LCR is not dequeued or propagated, and the error is raised to the Propagation process.

At the Apply

The LCR is not dequeued or applied, the error is raised to the Apply process, and the Apply process is disabled.

If some of the messages in the LCR were successfully transformed, the LCR is placed in the Apply Error queue. Those transformations that were completed are retained in the LCR when it is moved to the Apply Error queue. Attempts to execute the error with the DBMS_APPLY_ADM.EXECUTE_ERROR procedure will only process the LCR as-is and not attempt to execute further transformation.

To mitigate a transformation failure and re-enable any disabled Streams processes, you must either fix the problem in the PL/SQL function or remove the Rule Based Transformation.

Things to remember when working with Rules

If you wish to modify a rule created using DBMS_RULE_ADM.CREATE_RULE, it can be modified with DBMS_RULE_ADM.ALTER_RULE procedure.

If you wish to modify a rule created by DBMS_STREAMS_ADM.ADD_*_RULE, you may wish to drop the existing rule and create a new rule with the new parameters to ensure that the rule metadata is updated as expected.

DBMS_RULE_ADM.ALTER_RULE can support rules created using the DBMS_STREAMS_ADM package, but certain metadata may not be updated as expected.

It is recommended that you do not use the DBMS_RULE_ADM.DROP_RULE to drop a rule created using DBMS_STREAMS_ADM as it may not remove all the metadata for the rule. Instead, use the DBMS_STREAMS_ADM.REMOVE_RULE> procedure.

In general, a good rule of thumb is to use the same package to modify, remove or drop a rule that was used to create the rule.

If you are creating your own Rules and Rule Sets, make sure to coordinate the evaluation_context assignments with the client application event generation specified evaluation_context. Otherwise you may receive unexpected results if the wrong evaluation_context is inadvertently used.

Summary

In this article series, we discussed a lot on rules in Oracle 11g Streams. We will discuss the rule components, how to go about creating our own rules and rules based transformations. We will also highlight some of the most important things we need to know while working with rules.

Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: A$35.99
Book Price: A$59.99
See more
Select your format and quantity:

About the Author :


Ann L. R. McKinnell

Ann McKinnell is currently a Senior Principal Consultant with APG Technologies, LLC and has been an OCP since Oracle 7.3.4. She came to APG with over 8 years as a senior technical member of Oracle Global Support, specializing in Replication and Distributed System technologies. She was a recognized global technical expert for Oracle Distributed Systems; earning the internal nickname "The Replication Goddess". Ann has trained Oracle Support and Consulting personnel from many countries in Advanced Replication and Distributed System Internals and Problem Solving techniques. She has authored and co-authored many of the Oracle Advanced Replication notes found on Oracle Metalink, and was a technical reviewer for the Oracle University Pilot 9i Steams course material, as well as various Oracle Replication and Database Administration user manuals. Ann continues to specialize in practical implementation strategies and the development of distributed Oracle database systems, database architecture, and software and database design and integration.

Eric Yen

Eric Yen began working with Oracle Databases at version 7.3.4. Over the following 14 years, he obtained his Oracle DBA Certification starting with version 8, maintaining it up to the current release, and he has also earned the (ISC)2 CISSP certification. Eric began working with Oracle Streams with Oracle 9i Streams beta. As a Senior Principal Consultant with APG Technologies, LLC, Eric's work includes designing and implementing Streams solutions for Government clients using the more recent versions of Streams in Oracle 10 and Oracle 11. During his little spare time, you can find Eric exercising and tinkering around with Oracle products.

Books From Packt

Oracle Application Express 3.2 – The Essentials and More
Oracle Application Express 3.2 – The Essentials and More

Oracle Coherence 3.5
Oracle Coherence 3.5

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

Middleware Management with Oracle Enterprise Manager Grid Control 10g R5
Middleware Management with Oracle Enterprise Manager Grid Control 10g R5

Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial
Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial

Oracle User Productivity Kit 3.5
Oracle User Productivity Kit 3.5

Oracle Warehouse Builder 11g: Getting Started
Oracle Warehouse Builder 11g: Getting Started

Oracle Application Express Forms Converter
Oracle Application Express Forms Converter

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software