Skip Headers
Oracle® Database Concepts
11g Release 1 (11.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

23 Information Integration

This chapter includes the following topics:

See Also:

Oracle Database 2 Day + Data Replication and Integration Guide

Introduction to Oracle Information Integration



See Also:



Oracle Streams is the asynchronous information sharing infrastructure in Oracle Database. Oracle Streams can mine the Oracle Database redo logs to capture data manipulation language (DML) and data definition language (DDL) changes to data, and it makes that changed data available to other applications and databases. Thus, Oracle Streams can provide an extremely flexible asynchronous replication solution, as well as an event notification framework. Because Streams supports applications that explicitly enqueue and dequeue messages, it also provides a complete asynchronous messaging solution. That solution, Oracle Streams Advanced Queuing, can be used to exchange information with customers, partners, and suppliers, and to coordinate business processes.

Both Streams and distributed SQL can access and update data in non-Oracle systems using Oracle Database Gateways, Generic Connectivity, and the Messaging Gateway. Oracle Database can work with non-Oracle data sources, non-Oracle message queuing systems, and non-SQL applications, ensuring interoperability with other vendor's products and technologies. Each of the solutions are described in detail in the following sections.



In order for a company to operate successfully in a distributed environment, it might need to do the following:

Federated Access

A homogeneous distributed database system is a network of two or more Oracle Databases that reside on one or more computers.

This section includes the following topics:

Distributed SQL


An Oracle distributed database system can be transparent to users, making it appear as though it is a single Oracle Database. Companies can use this distributed SQL feature to make all its Oracle Databases look like one and thus reduce some of the complexity of the distributed system.


See Also:

Oracle Database Administrator's Guide for more information on database links

Location Transparency





See Also:

SQL and COMMIT Transparency


Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because Oracle Database must coordinate the committing or undo of the changes in a transaction as a self-contained unit. In other words, the entire transaction commits, or the entire transaction rolls back.


Distributed Query Optimization

Distributed query optimization reduces the amount of data transfer required between sites when a transaction retrieves data from remote tables referenced in a distributed SQL statement. Distributed query optimization uses the Oracle Database optimizer to find or generate SQL expressions that extract only the necessary data from remote tables, process that data at a remote site (or sometimes at the local site), and send the results to the local site for final processing.

This operation reduces the amount of required data transfer when compared to the time it takes to transfer all the table data to the local site for processing. Using various optimizer hints, such as DRIVING_SITE, NO_MERGE, and INDEX, you can control where Oracle Database processes the data and how it accesses the data.

See Also:


Information Sharing





This section includes the following topics:

Oracle Streams


Oracle Streams provides components that allow users to control what information is put into a stream, how the stream flows or is routed from node to node, what happens to messages in the stream as they flow into each node, and how the stream terminates. By specifying the configuration of the elements acting on the stream, a user can address specific requirements, such as message queuing or data replication.

Oracle Streams satisfies the information sharing requirements for a variety of usage scenarios. Oracle Streams Advanced Queuing provides the database-integrated message queuing and event management capabilities. In addition, Oracle includes tools to help users build event notification, replication, and data warehouse loading solutions using Oracle Streams.


This section includes the following topics:

Oracle Streams Architecture


Figure 23-1 Streams Information Flow

Description of Figure 23-1 follows
Description of "Figure 23-1 Streams Information Flow"


Oracle Streams can capture database changes implicitly and explicitly and place these changes in a staging area. Database changes, such as DML and DDL changes, can be implicitly captured. Rules determine which changes are captured. Information representing a captured change is formatted as a logical change record (LCR) and placed in the staging area.

Oracle Streams provides two components for implicit capture: capture processes and synchronous captures. Capture processes mine the redo logs to find database changes. Capture processes support mining the online redo log, as well as mining archived log files. In the case of online redo log mining, redo information is mined for change data at the same time it is written, reducing the latency of capture. Synchronous captures use an internal mechanism to capture DML changes as they are made to specified database objects.

Typically, capture processes are used to capture changes to a large number of database objects and to capture both DML and DDL changes. Synchronous captures are used to capture DML changes to a small number of database objects.

Oracle Streams also supports explicit capture. User applications can explicitly enqueue messages representing events into the staging area. These messages can be formatted as LCRs, which will allow them to be consumed by the apply process, or they can be formatted for consumption by another user application using an explicit dequeue.


Once captured, messages are placed in a staging area. The staging area is a queue that stores and manages captured messages. LCRs and other types of messages are stored in a staging area until subscribers consume them. LCR staging provides a holding area with security, as well as auditing and tracking of LCR data.


If the subscriber is a user application, then the application dequeues the message from the staging area in order to consume the message. If the subscriber is a propagation to another staging area, then the message will be propagated to that staging area. If the subscriber is an apply process, then it will be dequeued and consumed by the apply process.

Messages in the staging area optionally may be propagated to other staging areas in the same database, or to staging areas in remote databases. To simplify network routing and reduce network traffic, messages need not be sent to all databases and applications. Rather, they can be directed through staging areas on one or more systems until they reach the subscribing system. Not all systems need subscribe to the messages, providing flexibility regarding what messages are applied at a particular system. A single staging area can stage messages from multiple databases, simplifying setup and configuration.



Messages in a staging area can be consumed implicitly or explicitly. An apply process implicitly applies database changes encapsulated in messages to a database. An Oracle Streams apply process is flexible. It enables standard or custom apply of messages. A custom apply can manipulate the data or perform other actions during apply. Support for explicit dequeue allows application developers to use Oracle Streams to reliably exchange messages. They can also notify applications of changes to data, by leveraging the change capture and propagation features of Oracle Streams.

Replication with Oracle Streams

Oracle Streams is an information sharing technology that automatically determines what information is relevant and shares that information with those who need it. This active sharing of information includes capturing and managing messages in the database, including messages that encapsulate DML and DDL changes, and propagating those messages to other databases and applications. Data changes can be applied directly to the replica database or can call a user-defined subprogram to perform alternative work at the destination database. For example, such a subprogram can populate a staging table used to load a data warehouse.

This section includes the following topics:

Capturing DML and DDL Changes

Configuring Streams for replication begins with specifying an object or set of objects to be replicated. Using the one or both of the implicit capture mechanisms of Oracle Streams, changes made to these objects can be captured efficiently and replicated to one or more remote systems with little impact to the originating system. When a capture process is used, the capture process can extract both data changes (DML) and structure changes (DDL) from the redo log. When a synchronous capture is used, the synchronous capture uses an internal mechanism to capture DML changes when they occur. The captured changes are published to a staging area.

Log-based capture with a capture process leverages the fact that changes made to tables are logged in the redo log to guarantee recoverability in the event of a malfunction or media failure. Capturing changes directly from the redo log minimizes the overhead on the system. Oracle Database can read, analyze, and interpret redo information, which contains information about the history of activity on a database. Oracle Streams can mine the information and deliver change data to the capture process.

Capturing changes with a synchronous capture is best suited for environments that replicate DML changes to a relatively small number of database objects. Synchronous capture uses an internal mechanism to ensure that DML changes are captured when they happen.

Replicated databases utilizing Oracle Streams technology need not be identical. Participating databases can maintain different data structures, using Streams to transform the data into the appropriate format. Streams provides the ability to transform the stream at multiple points: during change capture, while propagating to another database, or during apply at the destination site. These transformations are user-defined functions registered within the Oracle Streams framework. For example, the transformation can be used to change the datatype representation of a particular column in a table, to change the name of a column in a table, or to change a table name.


Propagating Changes Over a Directed Network




Resolving Conflicts and Applying Changes





Oracle Streams Advanced Queuing


This section includes the following topics:

Asynchronous Application Integration



Advanced Queuing also offers multiple ways to consume a message. Automatic apply lets users invoke a user-specified action for the message. Consuming applications can dequeue a message explicitly. Both blocking and nonblocking dequeue is supported. The consuming applications can choose to receive notifications either procedurally using PL/SQL, OCI, or Java callback functions. Alternatively, they can get notifications in an e-mail or by HTTP post. Consuming applications can also choose to perform automatic apply.

Extensible Integration Architecture


The distributed applications on an Oracle database communicate with queues in the same Oracle database server hub. The Oracle Database extensible framework lets multiple applications share the same queue, eliminating the need to add additional queues to support additional applications.



If the distributed applications are running on different Oracle Databases, business communications can be automatically propagated to the appropriate Oracle Database. The propagation is managed automatically by the Oracle Streams Advanced Queuing system and is transparent to the application.

Heterogeneous Application Integration

Traditionally, different applications had to use a common data model for communication. This data model was further restricted by the limited datatype support of the message-oriented middleware. Oracle Streams Advanced Queuing supports ANYDATA queues that can store messages of multiple datatypes.

Advanced Queuing provides applications with the full power of the Oracle type system. It includes support for scalar datatypes such as NUMBER, DATE, VARCHAR, and so on, Oracle Database object types with inheritance, XMLType with additional operators for XML data, and ANYDATA support. In particular, with XMLType type support, application developers can make use of the full power of XML for extensibility and flexibility in business communications.


Legacy Application Integration


Distributed applications spanning multiple partners can coordinate using the Internet access features of Oracle Streams Advanced Queuing. Using these features, a business partner or application can securely place an order into an Advanced Queuing queue over the Internet. Only authorized and authenticated business partners can perform these operations.


Standard-Based API Support


Similarly, the distributed messages and database changes can be applied to database tables, which can be seen using SQL. The messages can be enqueued and dequeued using industry-standard JMS. Advanced Queuing also has a SOAP-based XML API and supports OCI and OCCI to enqueue and dequeue messages.

Database Change Notification

Client applications can receive notifications when the result set of a registered query changes. For example, if the client registers a query of the hr.employees table, and if a user adds an employee, then the application can receive a database change notification when a new row is added to the table. A new query of hr.employees returns the changed result set. Database Change Notification is relevant in many development contexts, but is particularly useful to mid-tier applications that rely on cached data.

See Also:

Oracle Database Advanced Application Developer's Guide for more information on using Database Change Notification

Change Data Capture

Change Data Capture, a feature built on the Oracle Streams infrastructure, efficiently identifies and captures data that has been added to, updated, or removed from Oracle Database relational tables, and it makes the change data available for use by ETL tools and applications. Using the Change Data Capture capabilities of Oracle Streams, it quickly identifies and processes only the data that has changed, not entire tables.

Heterogeneous Environments

Oracle Streams is an open information sharing solution, supporting heterogeneous replication between Oracle and non-Oracle systems. Using an Oracle Database Gateway, DML changes initiated at Oracle Databases can be applied to non-Oracle databases.

To implement capture and apply of DML changes from an Oracle Database source to a non-Oracle destination, an Oracle Database system functions as a proxy and runs the apply process that would normally be running at an Oracle Database destination site. The Oracle Database system then communicates with the non-Oracle system with an Oracle Database Gateway.

The changes are dequeued in Oracle Database itself and the local apply process applies the changes to a non-Oracle system across a network connection through an Oracle Database Gateway.

Users who want to propagate changes from a non-Oracle database to Oracle Database write an application to capture the changes made to the non-Oracle database. The application can capture the changes by reading from transaction logs or by using triggers. The application is then responsible for assembling and ordering these changes into transactions, converting them into the logical change record (LCR) format, and publishing them into the target Oracle Database staging area. These changes can be applied with a Streams apply process.

Oracle Streams Use Cases



Figure 23-2 illustrates this Streams configuration.

Figure 23-2 Streams Configuration

Description of Figure 23-2 follows
Description of "Figure 23-2 Streams Configuration"

Oracle Streams is used to replicate data in an N-way configuration consisting of three regional sites: New York, London, and Tokyo. At each of these sites, Streams implicit capture collects any changes that occur for subscribed tables in each local region, and stages them locally in the queue. All changes captured in each region are then forwarded to each of the other region's databases. The goal is that all changes made at each database be reflected at every other database, providing complete data for the subscribed objects throughout the world.

Because the updates are applied automatically when received at each regional database, an Oracle Streams apply process is used to apply the changes. As changes are applied, Oracle Streams checks for and resolves any conflicts that are detected. Streams can also be used to exchange data for particular tables with non-Oracle databases. Utilizing the Oracle Database Gateway for Sybase, a Streams apply process applies the changes to a Sybase database using the same mechanisms as it does for Oracle Databases.

The databases for reporting and disaster recovery are hosted from the New York database site. The reporting database is a fully functional Oracle Database that has a read-only copy of the relevant application tables. The reporting site is not configured to capture changes on these application tables. Streams imposes no restrictions on the configuration or usage of this reporting database.


Materialized Views



Because materialized views do not require a dedicated connection, they are ideal for disconnected computing. For example, a company might choose to use updatable materialized views for the members of their sales force. A salesperson could enter orders into his or her laptop throughout the day, then simply connect to the regional sales office at the end of the day to upload these changes and download any updates.

See Also:

Oracle Database Advanced Replication for information about using materialized views for replication

Data Comparison and Convergence at Oracle Databases

Database objects can be shared at two or more Oracle Databases. One way to share database objects is to configure a replication environment that uses the features of Oracle Streams or materialized views. Typically, replication environments share database objects that contain data, such as tables, as well as other types of databases objects, such as indexes. When a change is made to a shared database object at one database, the change is transferred to and made at each of the other databases that share the database object. In this way, the replication environment keeps the shared database object synchronized at each database.

Sometimes, the data in these shared database objects can diverge at the databases that share the database object. Various factors can cause divergence, including network problems, computer system problems, and replication configuration errors.

The DBMS_COMPARISON package enables you to compare database objects at different databases and identify differences in them. This package also enables you converge the database objects so that they are consistent at different databases.

The DBMS_COMPARISON package can compare and converge the following types of database objects:

The results of comparisons are stored in several data dictionary views, including DBA_COMPARISON, DBA_COMPARISION_SCAN, DBA_COMPARISON_COLUMNS, and DBA_COMPARISON_ROW_DIF.

Integrating Non-Oracle Systems

Oracle provides two solutions for integrating Oracle Database with non-Oracle databases--Generic Connectivity and Oracle Database Gateways. These solutions enable Oracle Database clients to access non-Oracle data stores. They translate third party SQL dialects, data dictionaries, and datatypes into Oracle formats, thus making the non-Oracle data store appear to be a remote Oracle Database. These technologies enable companies to integrate seamlessly the different systems and provide a consolidated view of the company as a whole.

Generic Connectivity and Oracle Database Gateways can be used for synchronous access, using distributed SQL. In addition, Oracle Database Gateways can be used for asynchronous access, using Oracle Streams. Introducing an Oracle Database Gateway into an Oracle Streams environment enables replication of data from Oracle Database to a non-Oracle database.

Both Generic Connectivity and Oracle Database Gateways transparently access data in non-Oracle systems from an Oracle Database environment. As with an Oracle distributed database environment, location transparency can be extended to objects residing in non-Oracle systems as well. Therefore, users can create synonyms for the objects in the non-Oracle database and refer to them without having to specify its physical location. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Instead of requiring applications to interoperate with non-Oracle systems using their native interfaces (which can result in intensive application-side processing), applications can be built upon a consistent Oracle Database interface for both Oracle Database and non-Oracle database systems.

This section includes the following topics:

Generic Connectivity


Oracle Database Gateways

In contrast to Generic Connectivity, which is a generic solution, Oracle Database Gateways are tailored solutions, specifically coded for the non-Oracle system. They provide an optimized solution, with more functionality and better performance than Generic Connectivity.

Generic Connectivity relies on industry standards, whereas Oracle Database Gateways access the non-Oracle systems using their native interface. Oracle Database Gateways are also end-to-end certified. Oracle Database has Oracle Database Gateways to many sources, including Sybase, DB2, Informix, and Microsoft SQL Server.