| Oracle® Database XStream Guide 11g Release 2 (11.2) Part Number E15874-01 |
|
|
View PDF |
| Oracle® Database XStream Guide 11g Release 2 (11.2) Part Number E15874-01 |
|
|
View PDF |
This chapter contains concepts related to XStream.
This chapter contains these topics:
See Also:
XStream provides application programming interfaces (APIs) that enable client applications to receive data changes from an Oracle database and to send data changes to an Oracle database. These data changes can be shared between Oracle databases and other systems. The other systems include non-Oracle databases, non-RDBMS Oracle products, file systems, third party software applications, and so on. The client application is designed by the user for specific purposes and use cases.
XStream consists of two major features: XStream Out and XStream In. XStream Out provides APIs that enable you to share data changes made to an Oracle database with other systems. XStream In provides APIs that enable you to share data changes made to other systems with Oracle databases.
XStream is built on the infrastructure of Oracle Streams. Therefore, XStream inherits Oracle Streams flexibility and functionality, including:
Filtering of database changes at the database level, schema level, table level, and row/column level
Rules and rule sets that control behavior, including inclusion and exclusion rules
Rule-based transformation that modify captured data changes
Support for the data types supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType
Customized configurations, including multiple inbound streams to a single database instance, multiple outbound streams from a single database instance, multiple outbound streams from a single capture process, and so on
Support for data manipulation language (DML) and data definition language (DDL) changes
Full-featured apply for XStream In, including apply parallelism for optimal performance, SQL generation, conflict detection and resolution, error handling, and customized apply with apply handlers
You can configure XStream using the DBMS_XSTREAM_ADM package.
Using the XStream APIs requires purchasing a license for the Oracle GoldenGate product. See the documentation for the Oracle GoldenGate product for more information:
XStream Out allows a user to receive logical change records (LCRs). After an XStream Out user receives LCRs, the user might save the contents of LCRs to a file or generate the SQL statements to execute the LCRs on a non-Oracle database. XStream In allows a user to update tables in its own schema. XStream does not assume that the connected user to the inbound server or outbound server is trusted.
Java and Oracle Call Interface (OCI) client applications must connect to an Oracle database before attaching to an XStream outbound server created on that database. The connected user must be the same as the connect user configured for outbound server. Otherwise, an error is raised.
Java and Oracle Call Interface (OCI) client applications must connect to an Oracle database before attaching to an XStream inbound server created on that database. The connected user must be the same as the apply user configured for the inbound server. Otherwise, an error is raised.
The XStream Java layer API relies on Oracle JDBC security because XStream accepts the Oracle JDBC connection instance created by client applications in the XStream attach API. The connected user is then validated as an XStream user.
See Also:
"Security Model" for information about the security requirements for configuring and managing XStream
Oracle Streams Concepts and Administration for information about apply users
This chapter describes XStream, a new feature in Oracle Database 11g Release 2 (11.2). XStream enables heterogeneous information sharing with outstanding performance and usability.
Oracle Streams provides other ways to implement heterogeneous information sharing, both in past releases and in the current release. These ways include:
Replicating data changes to a non-Oracle database using an Oracle Database Gateway.
Dequeuing messages from an Oracle database using a Java Message Service (JMS) client.
Enqueuing messages directly into an Oracle database queue with a client application.
XStream Out can capture transactions from the redo log of an Oracle database and send them efficiently to a client application. XStream Out provides a transaction-based interface for streaming these changes to client applications. The client application can interact with other systems, including non-Oracle systems, such as non-Oracle databases or a file systems.
XStream Out has both Oracle Call Interface (OCI) and Java interfaces and supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType.
This section contains these topics:
With XStream Out, an Oracle Streams apply process functions as an outbound server. A client application can attach to an outbound server and extract row changes from logical change records (LCRs). A client application attaches to the outbound server using the OCI or Java interface. Only one client application at a time can attach to an outbound server.
In an XStream Out configuration, an Oracle Streams capture process captures database changes and sends these changes to an outbound server. 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 than 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-1 shows an outbound server configuration.
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.
An Oracle Streams apply process functions as an outbound server, but some apply process features are not applicable to an outbound server. The following sections describe which apply process features are applicable to outbound servers and which are not:
Apply Process Features That Are Applicable to Outbound Servers
Apply Process Features That Are Not Applicable to Outbound Servers
The following apply process features can be used with outbound servers:
Rules and rule sets
Rule-based transformations
When a custom rule-based transformation is specified on a rule used by an outbound server, the user who calls the transformation function is the connect user for the outbound server.
The following apply process parameters:
disable_on_limit
maximum_scn
startup_seconds
time_limit
trace_level
transaction_limit
txn_age_spill_threshold
txn_lcr_spill_threshold
write_alert_log
These apply process parameters control the behavior of outbound servers
Transaction assembly by reader servers
The spilling of unapplied LCRs to hard disk
Instantiation SCN settings
Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object that satisfy its rule sets.
The following apply process features cannot be used with outbound servers:
Apply handlers
You cannot specify an apply handler for an outbound server. The client application can perform custom processing of the logical change records (LCRs) instead if necessary. However, if there are apply processes configured in the same database as the outbound server, then you can specify apply handlers for these apply processes. In addition, you can configure general apply handlers for the database. An outbound server ignores general apply handlers.
The following apply process parameters:
allow_duplicate_rows
commit_serialization
disable_on_error
parallelism
preserve_encryption
rtrim_on_implicit_conversion
Outbound servers ignore the settings for these apply process parameters.
The commit_serialization parameter is always set to FULL for an outbound server, and the parallelism parameter is always set to 1 for an outbound server.
Apply tags
An outbound server cannot set an apply tag for the changes it processes.
Apply database links
Outbound servers cannot use database links.
Conflict detection and resolution
An outbound server does not detect conflicts, and conflict resolution cannot be set for an outbound server.
Dependency scheduling
An outbound server does not evaluate dependencies because its parallelism must be 1.
Substitute key column settings
An outbound server ignores substitute key column settings.
Enqueue directives specified by the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package
An outbound server cannot enqueue changes into an Oracle database queue automatically using the SET_ENQUEUE_DESTINATION procedure.
Execute directives specified by the SET_EXECUTE procedure in the DBMS_APPLY_ADM package
An outbound server ignores execute directives.
Error creation and execution
An outbound server does not create an error transaction when it encounters an error. It records information about errors in the ALL_APPLY and DBA_APPLY views, but it does not enqueue the transaction into the error queue.
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 change encapsulated in the LCR was 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.
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.
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 these two 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 set to 11.2.0 or higher.
See Also:
Oracle Database Administrator's Guide for more information about distributed transactions
Oracle Database Advanced Application Developer's Guide for more information about Oracle XA
XStream In enables a remote client application to send information into an Oracle database from another system, such as a non-Oracle database or a file system. XStream In provides an efficient, transaction-based interface for sending information to an Oracle database from client applications. XStream In can consume the information coming into the Oracle database in several ways, including data replication, auditing, and change data capture. XStream In supports both Oracle Call Interface (OCI) and Java interfaces.
When compared with OCI client applications that make data manipulation language (DML) changes to an Oracle database directly, XStream In is more efficient for near real-time, transaction-based, heterogeneous DML changes to Oracle databases.
XStream In uses the following features of Oracle Streams:
High performance processing of DML changes using an apply process and, optionally, apply process parallelism
Apply process features such as SQL generation, conflict detection and resolution, error handling, and customized processing with apply handlers
Streaming network transmission of information with minimal network round-trips
Rules, rule sets, and rule-based transformations. When a custom rule-based transformation is specified on a rule used by an inbound server, the user who calls the transformation function is the apply user for the inbound server.
XStream In supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType. A client application sends LOB and XMLType data to the inbound server in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.
This section contains these topics:
See Also:
With XStream In, an Oracle Streams apply process functions as an inbound server. A client application can attach to an inbound server and send row changes and DDL changes encapsulated in logical change records (LCRs).
An external client application connects to the inbound server using the Oracle Call Interface (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 attach to only one inbound server at a time, and it can detach from the inbound server whenever necessary.
Figure 1-2 shows an inbound server configuration.
Note:
An inbound server uses a queue that is not shown in Figure 1-2. An inbound server's queue only is used to store error transactions.The following are considerations for XStream inbound servers:
The 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.
The following sections describe the position order in a logical change record (LCR) stream for both XStream Out and XStream In:
Both XStream Out and XStream In use logical change record (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 Oracle Streams components restarts.
The position is not affected by any Oracle Streams 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.
A position identifies the placement of a logical change record (LCR) in a stream of LCRs. The position of each LCR both within a transaction and across transactions is strictly increasing. See "Position Order in an LCR Stream" for more information about positions.
LCRs that were captured by a capture process contain the following additional attributes related to LCR position:
The scn_from_position attribute contains the system change number (SCN) of the LCR.
The commit_scn_from_position attribute contains the commit SCN of the transaction to which the LCR belongs.
Note:
The
scn_from_position and commit_scn_from_position attributes are not present in row LCRs captured by a synchronous capture nor in explicitly captured row LCRsIf the outbound server or the client application stops abnormally, then the connection between the two is broken automatically. In this case, the client application must roll back all incomplete transactions. The client application must maintain its processed low position to recover properly after either it or the outbound server (or both) are restarted. The processed low position is a position below which all transactions have been processed by the client application. The processed low position indicates that the client has processed all LCRs that are less than or equal to this value. The client application can update the processed low position for each transaction that it consumes.
There are three possibilities related to the processed low position when the client application attaches to the outbound server:
The client application can pass a processed low position to the outbound server that is equal to or greater than the outbound server's processed low position. In this case, the outbound server resumes streaming LCRs from the first LCR that has a position greater than the client application's processed low position.
The client application can pass a processed low position to the outbound server that is less than the outbound server's processed low position. In this case, the outbound server raises an error.
The client application can pass NULL to the outbound server. In this case, the outbound server determines the processed low position automatically and starts streaming LCRs from the LCR that has a position greater than this processed low position. When this happens, the client must suppress or discard each LCR with a position less than or equal to the client application's processed low position.
To minimize network latency, the outbound server streams logical change records (LCRs) to the client application with time-based acknowledgments. For example, the outbound server might send an acknowledgment every 30 seconds. This streaming protocol fully utilizes the available network bandwidth, and the performance is unaffected by the presence of a wide area network (WAN) separating the sender and the receiver. The outbound server extends the underlying Streams infrastructure, and the outbound server maintains the streaming performance rate.
Using OCI, you can control the time period of the interval by setting the OCI_ATTR_XSTREAM_ACK_INTERVAL attribute through the OCI client application. The default is 30 seconds. If this value is large, then the outbound server can stream out more LCRs for each acknowledgment interval. However, a longer interval delays how often the client application can send the processed low position to the outbound server. Therefore, a longer interval might mean that the processed low position maintained by the outbound server is not current. In this case, when the outbound server restarts, it must start processing LCRs at an earlier position than the one that corresponds to the processed low position maintained by the client application. In this case, more LCRs might be retransmitted, and the client application must discard the ones that have been applied.
Using Java, you can control the time period of the interval by setting the batchInterval parameter in the attach method in the XStreamOut class. The client application can specify this interval when it invokes the attach method.
A position identifies the placement of a logical change record (LCR) in a stream of LCRs. The position of each LCR within a transaction is strictly increasing, and the position of each LCR across transactions also is strictly increasing. See "Position Order in an LCR Stream" for more information about positions.
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 position 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 less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, it only needs to send LCRs greater than the processed low position because the inbound server discards any LCRs that are less than or equal to the processed low position.
If the client application aborts 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 can send a row LCR with a commit directive to advance the inbound server's processed low position. The activity acts as an acknowledgment so that the inbound server's processed low position can be advanced.
Example 1-1 Advancing the Processed Low Position of an Inbound Server
Consider a client and an external data source. The client application sends change made to the hr.employees table to the inbound server for processing, but the external data source includes many other tables, including the oe.orders table.
Assume that the following changes are made to the external data source:
| Position | Change | Client Application Activity |
|---|---|---|
| 1 | Insert into the hr.employees table |
Send row LCR including the change to the inbound server |
| 2 | Insert into the oe.orders table |
None |
| 3 | Commit | Send a row LCR with a commit directive to inbound server |
| 4 | Insert into the oe.orders table |
None |
| 5 | Update the oe.orders table |
None |
| 6 | Commit | None |
| 7 | Commit | None |
| ... | ... (Activity on the external data source, but no changes to the hr.employees table) |
None |
| 100 | Insert into the oe.orders table |
None |
| 101 | Commit | None |
The client application gets the changes from the external data source, generates appropriate LCRs, and sends the LCRs to the inbound server. Therefore, the inbound server receives the following LCRs:
Row LCR for position 1
Row LCR for position 3
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 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:
| Position | Change | Client Application Activity |
|---|---|---|
| 1 | Insert into the hr.employees table |
Send row LCR including the change to the inbound server |
| 2 | Insert into the oe.orders table |
None |
| 3 | Commit | Send a row LCR with a commit directive to inbound server |
| 4 | Insert into the oe.orders table |
None |
| 5 | Update the oe.orders table |
None |
| 6 | Commit | None |
| 7 | Commit | None |
| ... | ... (Activity on the external data source, but no changes to the hr.employees table) |
Send several row LCR with a commit directives to the inbound server |
| 100 | Insert into the oe.orders table |
None |
| 101 | Commit | Send a row LCR with a commit directive to the inbound server |
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. Search for the word "ping" to find the parts of the applications that include this code.
Table 1-1 compares how the XStream Out outbound server and the XStream In inbound server use positions.
Table 1-1 Position Use in the Outbound Server and the Inbound Server
| XStream Out Outbound Server | XStream In Inbound Server |
|---|---|
|
The outbound server exposes the position. |
The client application sets the position. |
|
If the outbound server or client application stops abnormally, then all LCRs above the processed low position are resent. The processed low position is the apply process low watermark (LWM), and the apply process obtains the oldest SCN value by using this value. |
If the inbound server or client application stops abnormally, then the client application must retransmit all LCRs with a position greater than or equal to the processed low position. The processed low position is the applied low water mark (LWM). |
SQL generation is the ability to generate the SQL statement required to perform the change encapsulated in a row logical change record (row LCR). Apply processes, XStream outbound servers, and XStream inbound servers can generate the SQL statement necessary to perform the insert, update, or delete operation in a row LCR.
This section contains these topics:
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 Oracle Call Interface (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.
See Also:
Oracle Streams XStream Java API Reference for information about the Java interface for XStream
Oracle Database PL/SQL Packages and Types Reference for information about the GET_ROW_TEXT and GET_WHERE_CLAUSE row LCR member procedures
SQL statement 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 about the GET_ROW_TEXT and GET_WHERE_CLAUSE row LCR member procedures in Oracle Database PL/SQL Packages and Types Reference
The documentation about DefaultRowLCR.getBinds() in Oracle Streams XStream Java API Reference
Note:
For generated SQL statements with the values inline, SQL injection is possible. SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database to view or manipulate restricted data. Oracle strongly recommends using bind variables if you plan to execute the generated SQL statement. See Oracle Database PL/SQL Language Reference for more information about SQL injection.XStream outbound servers and inbound servers work the same as apply processes with regards to SQL generation and data types and character sets. For detailed information, see Oracle Streams Concepts and Administration.
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 Oracle Call Interface (OCI) client application or a Java client application to perform SQL generation.
The demo uses SQL generation to replicate data manipulation language (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, queues, and a propagation, send the changes in the form of logical change records (LCRs) to an XStream outbound server that is also running on the source database. The outbound server makes the LCRs available to the 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 data definition language (DDL) changes.
This demo is available in the following location:
$ORACLE_HOME/rdbms/demo/xstream/sqlgen
|
 Copyright © 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|