9
Streams Heterogeneous Information Sharing

This chapter explains concepts relating to Streams support for information sharing between Oracle databases and non-Oracle databases.

This chapter contains these topics:

• Oracle to Non-Oracle Data Sharing with Streams
• Non-Oracle to Oracle Data Sharing with Streams
• Non-Oracle to Non-Oracle Data Sharing with Streams

Oracle to Non-Oracle Data Sharing with Streams

To share DML changes from an Oracle source database to a non-Oracle destination database, the Oracle database functions as a proxy and carries out some of the steps that would normally be done at the destination database. That is, the events intended for the non-Oracle destination database are dequeued in the Oracle database itself and an apply process at the Oracle database applies the changes to the non-Oracle database across a network connection through a gateway. Figure 9-1 shows an Oracle databases sharing data with a non-Oracle database.

Figure 9-1 Oracle to Non-Oracle Heterogeneous Data Sharing

Text description of the illustration strms009.gif

Change Capture and Staging in an Oracle to Non-Oracle Environment

In an Oracle to non-Oracle environment, the capture process functions the same way as it would in an Oracle-only environment. That is, it finds changes in the redo log, captures them based on capture process rules, and enqueues the captured changes as logical change records (LCRs) in a SYS.AnyData queue. In addition, a single capture process may capture changes that will be applied at both Oracle and non-Oracle databases.

Similarly, the SYS.AnyData queue that stages the captured LCRs functions the same way as it would in an Oracle-only environment, and you can propagate LCRs to any number of intermediate queues in Oracle databases before they are applied at a non-Oracle database.

See Also: Chapter 2, "Streams Capture Process" Chapter 3, "Streams Staging and Propagation"

Change Apply in an Oracle to Non-Oracle Environment

An apply process running in an Oracle database uses Heterogeneous Services and a gateway to apply changes encapsulated in LCRs directly to database objects in a non-Oracle database. The LCRs are not propagated to a queue in the non-Oracle database, as they would be in an Oracle-only Streams environment. Instead, the apply process applies the changes directly through a database link to the non-Oracle database.

See Also: Chapter 4, "Streams Apply Process" for detailed information about the apply process

Apply Process Configuration in an Oracle to Non-Oracle Environment

This section describes the configuration of an apply process that will apply changes to a non-Oracle database.

Database Link to the Non-Oracle Database

When you create an apply process that will apply changes to a non-Oracle database, you must previously have configured Heterogeneous Services, the gateway, and a database link, which will be used by the apply process to apply the changes to the non-Oracle database. The database link must be created with an explicit CONNECT TO clause.

When the database link is created and working properly, create the apply process using the CREATE_APPLY procedure in the DBMS_APPLY_ADM package and specify the database link for the apply_database_link parameter. After you create an apply process, you can use apply process rules to specify which changes are applied at the non-Oracle database.

See Also: Oracle9i Heterogeneous Connectivity Administrator's Guide for more information about Heterogeneous Services and gateways Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the procedures in the DBMS_APPLY_ADM package Chapter 6, "How Rules Are Used In Streams" for information about specifying apply process rules

Substitute Key Columns

If you use substitute key columns for any of the tables at the non-Oracle database, then specify the database link to the non-Oracle database when you run the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package.

See Also: "Substitute Key Columns" "Managing the Substitute Key Columns for a Table"

Parallelism

You must set the parallelism apply process parameter to 1 when the apply process is applying changes to a non-Oracle database. Currently, parallel apply to non-Oracle databases is not supported.

DML Handlers

If you use a DML handler to process row LCRs for any of the tables at the non-Oracle database, then specify the database link to the non-Oracle database when you run the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package.

See Also: "Event Processing Options" "Managing a DML Handler"

Message Handlers

If you want to use a message handler process user-enqueued messages for a non-Oracle database, then specify the database link to the non-Oracle database using the apply_database_link parameter when you run the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

See Also: "Event Processing Options" "Managing the Message Handler for an Apply Process"

Error and Conflict Handlers

Currently, error handlers and conflict handlers are not supported when sharing data from an Oracle database to a non-Oracle database. If an apply error occurs, then the transaction containing the LCR that caused the error is moved into the error queue in the Oracle database.

Datatypes Applied at Non-Oracle Databases

When applying changes to a non-Oracle database, an apply process applies changes made to columns of only the following datatypes:

• CHAR
• VARCHAR2
• NCHAR
• NVARCHAR2
• NUMBER
• DATE
• RAW
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

The apply process does not apply changes in columns of the following datatypes to non-Oracle databases: CLOB, NCLOB, BLOB, BFILE, LONG, LONG RAW, ROWID, UROWID, and user-defined type (including object types, REFs, varrays, and nested tables). The apply process raises an error when an LCR contains a datatype that is not listed, and the transaction containing the LCR that caused the error is moved to the error queue in the Oracle database.

Each transparent gateway may have further limitations regarding datatypes. For a datatype to be supported in an Oracle to non-Oracle environment, the datatype must be supported by both Streams and the gateway being used.

See Also: Oracle9i SQL Reference for more information about these datatypes Your Oracle supplied gateway-specific documentation for information about transparent gateways

Types of DML Changes Applied at Non-Oracle Databases

When you specify that DML changes made to certain tables should be applied at a non-Oracle database, an apply process can apply only the following types of DML changes:

• INSERT
• UPDATE
• DELETE

  Note: The apply process cannot apply DDL changes at non-Oracle databases.

Instantiation in an Oracle to Non-Oracle Environment

Before you start an apply process that applies changes to a non-Oracle database, complete the following steps to instantiate each table at the non-Oracle database:

1. Use the DBMS_HS_PASSTHROUGH package or the tools supplied with the non-Oracle database to create the table at the non-Oracle database.
2. Populate the table writing a PL/SQL block (or a C program) that fetches row by row from the table at the Oracle database and then does a row by row INSERT into the table at the non-Oracle database. All fetches should be done at the same SCN.
3. Use the SET_TABLE_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package to instruct the apply process to skip all LCRs with changes that occurred before the SCN you used in Step 2. Make sure you set the apply_database_link parameter to the database link for the remote non-Oracle database.

   See Also: Oracle9i Heterogeneous Connectivity Administrator's Guide and your Oracle supplied gateway-specific documentation for more information about Heterogeneous Services and transparent gateways "Setting Instantiation SCNs at a Destination Database" and Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the SET_TABLE_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package

Transformations in an Oracle to Non-Oracle Environment

In an Oracle to non-Oracle environment, you can specify rule-based transformations during capture or apply the same way as you would in an Oracle-only environment. In addition, if your environment propagates LCRs to one or more intermediate Oracle databases before they are applied at a non-Oracle database, then you can specify a rule-based transformation during propagation from a queue at an Oracle database to another queue at an Oracle database.

See Also: "Rule-Based Transformations"

Messaging Gateway

Messaging Gateway is a feature of the Oracle database that provides propagation between Oracle queues and non-Oracle message queuing systems. Messages enqueued into an Oracle queue are automatically propagated to a non-Oracle queue, and the messages enqueued into a non-Oracle queue are automatically propagated to an Oracle queue. It provides guaranteed message delivery to the non-Oracle messaging system and supports the native message format for the non-Oracle messaging system. It also supports specification of user-defined transformations that are invoked while propagating from an Oracle queue to the non-Oracle messaging system or from the non-Oracle messaging system to an Oracle queue.

See Also: Oracle9i Application Developer's Guide - Advanced Queuing for more information about the Messaging Gateway

Error Handling in an Oracle to Non-Oracle Environment

If the apply process encounters an unhandled error when it tries to apply an LCR at a non-Oracle database, then the transaction containing the LCR is placed in the error queue in the Oracle database that is running the apply process. The apply process detects data conflicts in the same way as it does in an Oracle-only environment, but automatic conflict resolution is not supported currently in an Oracle to non-Oracle environment. Therefore, any data conflicts encountered are treated as apply errors.

Example Oracle to Non-Oracle Streams Environment

"Single Source Database in a Heterogeneous Environment" contains a detailed example that includes sharing data in an Oracle to non-Oracle Streams environment.

Non-Oracle to Oracle Data Sharing with Streams

To capture and propagate changes from a non-Oracle database to an Oracle database, a custom application is required. This application gets the changes made to the non-Oracle database by reading from transaction logs, by using triggers, or by some other method. The application must assemble and order the transactions and must convert each change into a logical change record (LCR). Then, the application must enqueue the LCRs into a queue in an Oracle database using the DBMS_AQ package. The application must commit after enqueuing all LCRs in each transaction. Figure 9-2 shows a non-Oracle databases sharing data with an Oracle database.

Figure 9-2 Non-Oracle to Oracle Heterogeneous Data Sharing

Text description of the illustration strms014.gif

Change Capture and Staging in a Non-Oracle to Oracle Environment

Because the custom user application is responsible for assembling changes at the non-Oracle database into LCRs and enqueuing the LCRs into a queue at the Oracle database, the application is completely responsible for change capture. This means that the application must construct LCRs that represent changes at the non-Oracle database and then enqueue these LCRs into the queue at the Oracle database. The application must enqueue transactions serially in the same order as the transactions committed on the non-Oracle source database.

If you want to ensure the same transactional consistency at both the Oracle database where changes are applied and the non-Oracle database where changes originate, then you must use a transactional queue to stage the LCRs at the Oracle database. For example, suppose a single transaction contains three row changes, and the custom application enqueues three row LCRs, one for each change, and then commits. With a transactional queue, a commit is performed by the apply process after the third row LCR, retaining the consistency of the transaction. If you use a nontransactional queue, then a commit is performed for each row LCR by the apply process. The SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package creates a transactional queue automatically.

See Also: "Constructing and Enqueuing LCRs" "SYS.AnyData Queues and User Messages"

Change Apply in a Non-Oracle to Oracle Environment

In a non-Oracle to Oracle environment, the apply process functions the same way as it would in an Oracle-only environment. That is, it dequeues each event from its associated queue based on apply process rules, performs any rule-based transformation, and either sends the event to a handler or applies it directly. Error handling and conflict resolution also function the same as they would in an Oracle-only environment. So, you can specify a prebuilt update conflict handler or create a custom conflict handler to resolve conflicts.

See Also: Chapter 4, "Streams Apply Process" Chapter 5, "Rules" Chapter 7, "Streams Conflict Resolution" "Rule-Based Transformations"

Instantiation from a Non-Oracle Database to an Oracle Database

There is no automatic way to instantiate tables that exist at a non-Oracle database at an Oracle database. However, you can perform the following general procedure to instantiate a table manually:

1. At the non-Oracle database, use a non-Oracle utility to export the table to a flat file.
2. At the Oracle database, create an empty table that matches the table at the non-Oracle database.
3. At the Oracle database, use SQL*Loader to load the contents of the flat file into the table.

Non-Oracle to Non-Oracle Data Sharing with Streams

Streams supports data sharing between two non-Oracle databases through a combination of non-Oracle to Oracle data sharing and Oracle to non-Oracle data sharing. Such an environment would use Streams in an Oracle database as an intermediate database between two non-Oracle databases.

For example, a non-Oracle to non-Oracle environment may consist of the following databases:

• A non-Oracle database named het1.net
• An Oracle database named dbs1.net
• A non-Oracle database named het2.net

A user application assembles changes at het1.net and enqueues them into a queue in dbs1.net. Then, the apply process at dbs1.net applies the changes to het2.net using Heterogeneous Services and a gateway. Another apply process at dbs1.net could apply some or all of the changes in the queue locally at dbs1.net. One or more propagation jobs at dbs1.net could propagate some or all of the changes in the queue to other Oracle databases.