1 XStream Concepts

Licensing XStream

Using the XStream APIs requires purchasing a license for the Oracle GoldenGate product. See the documentation for the Oracle GoldenGate product for more information:

http://download.oracle.com/docs/cd/E15881_01/index.htm

The Outbound Server

With XStream Out, an Oracle Streams apply process functions as an outbound server. An outbound server is an optional Oracle background process that sends database changes to a client application. Specifically, a client application can attach to an outbound server and extract database changes from LCRs. A client application attaches to the outbound server using the OCI or Java interface.

A client application can create multiple sessions. Each session can attach to only one outbound server, and each outbound server can serve only one session at a time. However, different client application sessions can connect to different outbound servers or inbound servers.

In an XStream Out configuration, a capture process captures database changes and sends these changes to an outbound server. A capture process is an optional Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. When a capture process is configured to capture changes from the redo log, the database where the changes were generated is called the source database for the capture process.

Figure 1-1 shows a capture process.

Figure 1-1 Capture Process

Description of "Figure 1-1 Capture Process"

Change capture can be performed on the same database as the outbound server or on a different database. When change capture is performed on a different database from the one that contains the outbound server, a propagation sends the changes from the change capture database to the outbound server database. Downstream capture is also a supported mode to reduce the load on the source database.

When both the capture process and the outbound server are enabled, data changes, encapsulated in row LCRs and DDL LCRs, are sent to the outbound server. The outbound server can publish LCRs in various formats, such as OCI and Java. The client application can process LCRs that are passed to it from the outbound server or wait for LCRs from the outbound server by using a loop.

An outbound server sends LOB, LONG, LONG RAW, and XMLType data to the client application in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.

Figure 1-2 shows an outbound server configuration.

Figure 1-2 XStream Out Outbound Server

Description of "Figure 1-2 XStream Out Outbound Server"

The client application can detach from the outbound server whenever necessary. When the client application re-attaches, the outbound server automatically determines where in the stream of LCRs the client application was when it detached. The outbound server starts sending LCRs from this point forward.

ID Key LCRs

XStream Out does not support the following data types in row LCRs:

• BFILE

• ROWID

• User-defined types (including object types, REFs, varrays, and nested tables)

• XMLType stored object relationally or as binary XML

• The following Oracle supplied types: Any types, URI types, spatial types, and media types

These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.

ID key LCRs enable an XStream client application to process changes to rows that include unsupported data types. ID key LCRs do not contain all of the columns for a row change. Instead, they contain the rowid of the changed row, a group of key columns to identify the row in the table, and the data for the scalar columns of the table that are supported by XStream Out. ID key LCRs do not contain columns for unsupported data types.

An XStream client application can use ID key LCRs in the following ways:

• If the application does not require the data in the unsupported columns, then the application can process the values of the supported columns in the ID key LCRs normally.

• If the application requires the data in the unsupported columns, then the application can use the information in an ID key LCR to query the correct row in the database and consume the unsupported data for the row.

$ORACLE_HOME/rdbms/demo/xstream/idkey

Sequence LCRs

A sequence LCR is a row LCR that includes information about sequence values. Sequence database objects generate sequence values.

You can stream sequence LCRs in the following ways:

• To capture sequence LCRs using a capture process, set the capture process parameter capture_sequence_nextval to Y.

• To construct sequence LCRs using the OCI interface, use the OCILCRNew function and the OCILCRHeaderSet function with the OCI_ROWLCR_SEQ_LCR flag.

• To construct sequence LCRs using the Java interface, use the DefaultRowLCR constructor and setSequenceLCRFlag method.

An apply process or XStream inbound server can use sequence LCRs to ensure that the sequence values at a destination database use the appropriate values. For increasing sequences, the sequence values at the destination are equal to or greater than the sequence values at the source database. For decreasing sequences, the sequence values at the destination are less than or equal to the sequence values at the source database. To instruct an apply process or XStream inbound server to use sequence LCRs, set the apply_sequence_nextval apply process parameter to Y.

Considerations for XStream Outbound Servers

The following are considerations for XStream outbound servers:

• LCRs processed by an outbound server must be LCRs that were captured by a capture process. An outbound server does not support LCRs that were captured by synchronous captures or LCRs that were constructed by applications.

• A single outbound server can process captured LCRs from only one source database. The source database is the database where the changes encapsulated in the LCRs were generated in the redo log.

• The source database for the changes captured by a capture process must be at 10.2.0 or higher compatibility level for these changes to be processed by an outbound server.

• The capture process for an outbound server must be running on an Oracle Database 11g Release 2 (11.2) or later database.

• A single capture process cannot capture changes for both an outbound server and an apply process. However, a single capture process can capture changes for multiple outbound servers.

• An outbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.

• Automatic split and merge of a stream is possible when the capture process and the outbound server for the stream run on different database instances. However, when the capture process and outbound server for a stream run on the same database instance, automatic split and merge of the stream is not possible. See Oracle Streams Replication Administrator's Guide for information about automatic split and merge.

XStream Out and Distributed Transactions

You can perform distributed transactions using either of the following methods:

• Modify tables in multiple databases in a coordinated manner using database links.

• Use the XA interface, as exposed by the DBMS_XA supplied PL/SQL package or by the OCI or JDBC libraries. The XA interface implements X/Open Distributed Transaction Processing (DTP) architecture.

In an XStream Out configuration, changes made to the source database during a distributed transaction using either of the preceding methods are streamed to an XStream outbound server. The outbound server sends the changes in a transaction to the XStream client application after the transaction has committed.

However, the distributed transaction state is not replicated or sent. The client application does not inherit the in-doubt or prepared state of such a transaction. Also, XStream does not replicate or send the changes using the same global transaction identifier used at the source database for XA transactions.

XA transactions can be performed in two ways:

• Tightly coupled, where different XA branches share locks

• Loosely coupled, where different XA branches do not share locks

XStream supports replication of changes made by loosely coupled XA branches regardless of the COMPATIBLE initialization parameter value. XStream supports replication of changes made by tightly coupled branches on an Oracle RAC source database only if the COMPATIBLE initialization parameter is set to 11.2.0 or higher.

The Inbound Server

With XStream In, an Oracle Streams apply process functions as an inbound server. An inbound server is an optional Oracle background process that receives LCRs from a client application. Specifically, a client application can attach to an inbound server and send row changes and DDL changes encapsulated in LCRs.

An external client application connects to the inbound server using the OCI or the Java interface. After the connection is established, the client application acts as the capture agent for the inbound server by streaming LCRs to it.

A client application can create multiple sessions. Each session can attach to only one inbound server, and each inbound server can serve only one session at a time. However, different client application sessions can connect to different inbound servers or outbound servers. A client application can detach from the inbound server whenever necessary.

Figure 1-3 shows an inbound server configuration.

Figure 1-3 XStream In Inbound Server

Description of "Figure 1-3 XStream In Inbound Server"

Considerations for XStream Inbound Servers

The following are considerations for XStream inbound servers:

• You can control a DML or DDL trigger's firing property using the SET_TRIGGER_FIRING_PROPERTY procedure in the DBMS_DDL package. This procedure lets you specify whether a trigger always fires, fires once, or fires for apply process changes only. When a trigger is set to fire once, it fires for changes made by a user process, but it does not fire for changes made by an apply process or inbound server. A trigger's firing property works the same for apply processes and inbound servers. See Oracle Streams Concepts and Administration.

• An inbound server ignores the setting for the ignore_transaction apply process parameter because LCRs sent to the inbound server by the client application might not have transaction ID values.

  Note:

  Starting with Oracle Database 11g Release 2 (11.2.0.2), the ignore_transaction parameter is available for outbound servers and apply processes.

• An inbound server ignores the setting for the maximum_scn apply process parameter because LCRs sent to the inbound server by the client application might not have SCN values.

• Currently, an inbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.

About Position Order

Both XStream Out and XStream In use LCR streams to share transactions. XStream Out sends LCR streams to a client application. XStream In receives LCR streams from a client application.

Each LCR has a position attribute. The position of an LCR identifies its placement in the stream of LCRs in a transaction. Each LCR position has the following properties:

• The position is unique for each LCR.

• The position is of RAW data type.

• The position is strictly increasing within the LCR stream, within a transaction, and across transactions.

• The position is byte-comparable, and the comparison results for multiple positions determines the ordering of the LCRs in the stream.

• The position of an LCR remains identical when the database, the client application, or an XStream component restarts.

• The position is not affected by any rule changes that might reduce or increase the number of LCRs in the stream.

XStream Out only sends committed data, and XStream In only receives committed data.

The following are the properties related to an LCR stream:

• An LCR stream must be repeatable.

• An LCR stream must contain a list of assembled, committed transactions. LCRs from one transaction are contiguous. There is no interleaving of transactions in an LCR stream.

• Each transaction within an LCR stream must have an ordered list of LCRs and a transaction ID.

• The last LCR in each transaction must be a commit LCR.

• Each LCR must have a unique position.

• The position of all LCRs within a single transaction and across transactions must be strictly increasing.

An LCR stream can batch LCRs from multiple transactions and arrange them in increasing position order. LCRs from one transaction are contiguous, and the position must be increasing in the transaction. Also, the position must be nonzero for all LCRs.

Position of LCRs and XStream Out

An XStream Out outbound server streams LCRs that were captured by a capture process to a client application. This section describes concepts related to the LCR positions for an outbound server.

Position of LCRs and XStream In

A client application streams LCRs to an XStream In inbound server. This section describes concepts related to the LCR positions for an inbound server.

Each position must be encoded in a format (such as base-16 encoding) that supports byte comparison. The position is essential to the total order of the transaction stream sent by client applications using the XStream In interface.

The following positions are important for inbound servers:

• The applied low position indicates that the LCRs less than or equal to this value have been applied.

  An LCR is applied by an inbound server when the LCR has either been executed, sent to an apply handler, or moved to the error queue.

• The spill position indicates that the LCRs with positions less than or equal to this value have either been applied or spilled from memory to hard disk.

• The applied high position indicates the highest position of an LCR that has been applied.

  When the commit_serialization apply process parameter is set to DEPENDENT_TRANSACTIONS for an inbound server, an LCR with a higher commit position might be applied before an LCR with a lower commit position. When this happens, the applied high position is different from the applied low position.

• The processed low position is the higher value of either the applied low position or the spill position.

  The processed low position is the position below which the inbound server no longer requires any LCRs. This position corresponds with the oldest SCN for an Oracle Streams apply process that applies changes captured by a capture process.

  The processed low position indicates that the LCRs with positions less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, then it must send only LCRs with positions greater than the processed low position because the inbound server discards any LCRs with positions less than or equal to the processed low position.

If the client application stops abnormally, then the connection between the client application and the inbound server is automatically broken. Upon restart, the client application retrieves the processed low position from the inbound server and instructs its capture agent to retrieve changes starting from this processed low position.

To limit the recovery time of a client application using the XStream In interface, the client application can send activity, such as empty transactions, periodically to the inbound server. Row LCRs can include commit transaction control directives. When there are no LCRs to send to the server, the client application can send a row LCR with a commit directive to advance the inbound server's processed low position. This activity acts as an acknowledgment so that the inbound server's processed low position is advanced.

After position 3, there are no relevant changes to send to the inbound server. If the inbound server restarts when the client application has processed all the changes up to position 101, then, after restarting, the client application must recheck all of the external database changes from position 4 forward. The rechecks are required because the inbound server's processed low position is 3.

Instead, assume that the client application sends commits to the inbound server periodically, even when there are no relevant changes to the hr.employees table:

In this case, the inbound server moves its processed low position to 101 when it has processed all of the row LCRs sent by the client application. If the inbound server restarts, its processed low position is 101, and the client application does not need to check all of the changes back to position 3.

The sample applications in "Sample XStream Client Application" include code that sends a row LCR with a commit directive to an inbound server. These commit directives are sometimes called "ping LCRs." Search for the word "ping" in the sample XStream client applications to find the parts of the applications that include this code.

Summary of Position Use in XStream Out and XStream In

Table 1-1 compares how an XStream Out outbound server and an XStream In inbound server use positions.

Interfaces for Performing SQL Generation

You can use the following interfaces to perform SQL generation:

• The PL/SQL interface, which uses the GET_ROW_TEXT and GET_WHERE_CLAUSE member procedures for row LCRs

• The OCI for XStream

• The Java interface for XStream

The PL/SQL interface generates SQL in a CLOB data type, while the OCI and Java interfaces generate SQL in plain text. In the Java interface, the size of the text is limited by the size of String data type.

SQL Generation Formats

SQL statements can be generated in one of two formats: inline values or bind variables. Use inline values when the returned SQL statement is relatively small. For larger SQL statements, use bind variables. In this case, the bind variables are passed to the client application in a separate list that includes pointers to both old and new column values.

For information about using bind variables with each interface, refer to the following documentation:

• The documentation for the GET_ROW_TEXT and GET_WHERE_CLAUSE row LCR member procedures in Oracle Database PL/SQL Packages and Types Reference

• "OCILCRRowStmtWithBindVarGet()"

• The documentation for DefaultRowLCR.getBinds() in Oracle Database XStream Java API Reference

Data Types and Character Sets

Regarding data types and character sets, SQL generation works the same way for XStream Out outbound servers, XStream In inbound servers, and apply processes. For detailed information, see Oracle Streams Concepts and Administration.

SQL Generation Demo

A demo that performs SQL generation is available. The demo uses the DBMS_XSTREAM_ADM PL/SQL package to configure an XStream Out environment, and it uses either an OCI client application or a Java client application to perform SQL generation.

The demo uses SQL generation to replicate DML changes from a source database to a destination database. Specifically, the demo creates the xsdemosg schema in both the source database and the destination database. It creates various types of tables in the xsdemosg schema at each database, including tables with LOB columns. It executes a set of DML statements on the tables in xsdemosg schema in the source database. Oracle Streams components, such as a capture process and a queue, send the changes in the form of LCRs to an XStream outbound server that is also running on the source database. The outbound server makes the LCRs available to the demo client application.

The demo client application, when run, uses the OCI or Java API to connect to the outbound server and receive the LCRs. The demo client application uses SQL generation to execute the changes that are encapsulated in the LCRs. Therefore, the client application replicates the changes made to xsdemosg schema in the source database to the xsdemosg in the destination database.

You can modify the demo to replicate changes to any schema. Both the schema and the replicated tables must exist on both the source database and the destination database. SQL generation is only possible for DML changes. Therefore, this demo cannot be used to replicate DDL changes.

This demo is available in the following location:

$ORACLE_HOME/rdbms/demo/xstream/sqlgen