• Data warehouse / Data marts
• Data Mining and Predictive    Analysis
• Master Data Management
• Oracle Stream's new feature

Overview Oracle Streams

Oracle Stream's new feature oracle9i release 2 provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events, propagation and management of data, event, and transactions within oracle databases or between oracle and non-oracle databases and applications. Oracle Streams enables the propagation and management of data, transactions and events in a data stream either within a database, or from one database to another. The stream works on the publisher/subscriber pattern, by routing published information to subscribed destinations. Streams is an information-sharing technology.

Oracle Streams provides a mechanism to allow users to control what information flows, how the stream flow is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates.

The architecture of Oracle Streams is very flexible. As shown in the above diagram, Streams contains three basic elements.

• Capture
• Staging
• Apply

The apply process

The apply process is responsible for applying the changes to the target database. This is possible in two ways namely- Default consumption (implicit) and customized consumption (explicit). In default consumption, the apply engine is used to apply the changes to the database directly. Per adventure if a conflict occurs, the apply engine resolves it by invoking resolution (data transmission) routines.

In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively.

Capture Process

Oracle Streams supports capture of events (database changes, and application generated messages) into a designated staging area. We can have either implicit capture in which the server captures DML and DDL events at a source database using oracle's default rules or explicit capture allows applications to explicitly generate events and place them in the staging area. Explicit capture allows to setup a customized configuration to capture data using procedures. With implicit capture, the capture process retrieves change data extracted from the redo log, either by hot mining the online redo log or, if necessary, by mining archived log files. After retrieving the data, the capture process formats it into a Logical Change Record (LCR) and places it in a staging area for further processing. Logical change records are of two types- DDL LCR and row LCR. DDL LCR refers to changes made in the database objects by issuing ALTER, RENAME, CREATE or DROP commands. Row LCR on the other hand, refers to the modification of a single row of a table by a single DML statement. User applications can explicitly enqueue user messages representing events into the staging area. These messages can be formatted as LCRs, which will allow them to be consumed by the apply engine, or they can be formatted for consumption by another user application.

Staging Process

Once captured, events are placed in a staging area. Staging provides a holding area with security, as well as auditing and tracking of LCR data. The staging area is a queue and acts as a temporary repository for logical change records (LCR) until they are subscribed to. The subscriber has control over the contents of the staging area. Subscribers examine the contents of the staging area and determine whether or not they have an interest in an event. A subscriber can either be a user application, another staging area, usually on another system, or the default apply process.

Therefore, the subscriber can decide which records are propagated or consumed from the queue. For events propagation from a queue to take place, a user must be the owner of the queue and appropriate privileges are needed, not only on the source queue but also on the target queue. Moreover, a particular target queue can accept events from more than one source queues

Event Propagation

If the subscriber is another staging area, the event is propagated to the other staging area, either within the same database or in a remote database, as appropriate. To simplify network routing and reduce WAN traffic, events need not be sent to all databases and applications. For example, an event may propagate via a hub database that does not actually apply the event. A single staging area can stage events from multiple databases, simplifying setup and configuration.

Apply or Consumption Process

The apply process is responsible for applying the changes to the target database. Oracle Streams includes a flexible apply engine, that allows use of a standard or custom apply function. This is possible in two ways namely- Default consumption (implicit) and customized consumption (explicit). In default consumption, the apply engine is used to apply the changes to the database directly.

Default Apply

The default apply engine applies DML changes and DDL changes represented by implicitly or explicitly captured LCRs. The default apply engine will detect conflicts where the destination row has been changed and does not contain the expected values. If a conflict is detected, then a resolution routine may be invoked.

Custom (User-Defined Function) Apply

In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. This provides the greatest amount of flexibility in processing an event. A typical application of a user-defined function would be to reformat the data represented by the LCR before applying it to a local table, for example, field format, object name and column name mapping transformations.

If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively. A user-defined function could also be used to perform column subsetting, or to update other objects that may not be present in the source database. If a conflict occurs, the apply engine resolves it by invoking resolution (data transmission) routines.

Explicit Dequeue

User applications can explicitly dequeue LCRs or user messages from the receiving staging area. Support for explicit dequeue allows application developers to use Oracle Streams to notify applications of changes to data, while still leveraging the change capture and propagation features of Oracle Streams.

Streams can send notifications to registered PL/SQL or OCI functions, giving the applications an alternative to polling for new messages. Of course, applications can still poll, or even wait, for new subscribed messages in the staging area to become available.

Rules

Rules are the core of Oracle Streams, they let users control where and which information to send and share through specification. Use Streams' rules-based subscriptions and custom data-consumption capabilities to create an event-notification system. Users can choose can indicate if they want to capture, propagate or apply changes at the table, schema, or global (database) level. If necessary, related rules can be grouped into rule sets.

Transformations

A transformation can be defined as a change in the form of an object participating in capture and apply or a change in the data it holds. Transformations can include changing the datatype representation of a particular column in a table at a particular site, adding a column to a table at one site only, or including a subset of the data in a table at a particular site. Optional data transformations and rules-based decisions at each stage provide additional flexibility in information handling, making Streams adaptable to many types of applications.

Heterogeneous Support

Streams allows the remote databases to be fully open for read/write actions and doesn't require them to be identical copies of the source database (you can transform data at any stage of the process). If your environment includes non-Oracle databases, you can use explicit capture to enqueue changes from these databases into the stream, and you can apply updates to these databases through Oracle Transparent Gateways.

As with the Advanced Replication feature from prior database versions (still supported for backward compatibility), Oracle Streams supports updatable materialized views, or snapshots, for maintaining point-in-time copies of data. You can define them to contain a full copy of a master table or a defined subset of the rows in the master table that satisfy a value-based selection criterion.

Data-warehouse loading. Another variation on data replication at which Streams excels is the loading of an operational data store (ODS) or a data warehouse. Again, you use efficient, log-based capture to extract changes from a production system and propagate them to their destinations. Streams' data-transformation capabilities and custom apply functions provide optional assistance in reformatting data and updating warehouse-specific data fields as data is loaded (see Figure 2).

Oracle Streams is an open information sharing solution. Each element supports industry standard languages and standards. Streams supports capture and apply from Oracle to non-Oracle systems. Changes can be applied to a non-Oracle system via a transparent gateway or generic connectivity. Streams also includes an API to allow non-Oracle data sources to easily submit or receive change records, allowing for heterogeneous data movement in both directions. In addition, messages can be sent to and received from other message queuing systems such as MQ Series and Tibco via the Message Gateway.

Manageability

Several tools are available for configuring, administering, and monitoring a Streams environment. The primary interface to Streams is a collection of Oracle-supplied PL/SQL packages. To help users configure, administer, and monitor their Streams environments, Oracle provides a Streams tool in the Oracle Enterprise Manager Console. Users can also use the Streams tool to generate Streams configuration scripts, which they can then modify and run to configure their Streams environment. Additionally, Streams data dictionary views keep users informed about their Streams environment.

Managing various kinds of information sharing in a distributed environment doesn't have to be a multiple-tool ordeal. With Oracle Streams, you can use a single, flexible infrastructure to share and propagate both messages and database information, creating applications that range from message queuing and event notification to data replication and data-warehouse loading. By using just one tool for these applications, you reduce the stress of managing your complex environment-or of expanding your relatively simple environment-because you can do so without needing to learn new tools. In either case, you'll enjoy spending less time wrestling with tools as you provide helpful information streams throughout your environment.

Deployment for Specific Markets

Oracle Streams satisfies customer's information sharing requirements in a variety of markets. For example, customers can use Oracle Streams to create Event Notification, Replication, and Data Warehouse Loading solutions. Oracle also provides features built on Streams that extend its capabilities for tasks. Advanced Queuing is built on Oracle Streams and provides robust message queuing functionality integrated with the Oracle9i Database. Streams Deployment for Specific Application Domains

By combining and emphasizing various Streams capabilities in various ways, you can create applications that meet the needs of a wide variety of situations. For example, you might

• Focus on Streams' log-based capture and flexible propagation features to create an efficient data-replication system.
• Leverage Streams' near-real-time data capture and its data-transformation features to create an application that loads a data warehouse or operational data store.
• Use Streams' rules-based subscriptions and custom data-consumption capabilities to create an event-notification system.
• Focus on Streams' application-to-application communication capabilities (the aspect of Streams known as Oracle Advanced Queuing) to create a versatile message-queuing system.
• Take advantage of the queue-auditing features of Advanced Queuing to build a system for business-event management.

Because Streams is flexible enough to enable all these types of uses, you can easily extend an application by giving it new capabilities or migrate it in a new direction. For example, you could build a data-replication system and then extend it to load a data warehouse-with no need to reconfigure the system or deal with an additional tool.