1 Introduction to Oracle Streams

Capture Messages at a Database

Oracle Streams provides two ways to capture database changes implicitly: capture processes and synchronous captures. A capture process can capture DML changes made to tables, schemas, or an entire database, and DDL changes. A synchronous capture can capture DML changes made to tables. Rules determine which changes are captured by a capture process or synchronous capture.

Database changes are recorded in the redo log for the database. A capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR). The messages captured by a capture process are called captured LCRs.

A synchronous capture uses an internal mechanism to capture changes and format each captured change into an LCR. The messages captured by a synchronous capture are called persistent LCRs.

The rules used by a capture process or a synchronous capture determine which changes it captures. When changes are captured by a capture process, the database where changes are generated in the redo log is the source database. When changes are captured by a synchronous capture, the database where the synchronous capture is configured is the source database.

A capture process can capture changes locally at the source database, or it can capture changes remotely at a downstream database. A synchronous capture can only capture changes locally at the source database. Both a capture process and a synchronous capture enqueue logical change records (LCRs) into a queue. When a capture process or a synchronous capture captures changes, it is referred to as implicit capture.

Users and applications can also enqueue messages manually. These messages can be LCRs, or they can be messages of a user-defined type called user messages. When users and applications enqueue messages manually, it is referred to as explicit capture.

Stage Messages in a Queue

Messages are stored (or staged) in a queue. These messages can be logical change records (LCRs) or user messages. Capture processes and synchronous captures enqueue messages into an ANYDATA queue, which can stage messages of different types. Users and applications can enqueue messages into an ANYDATA queue or into a typed queue. A typed queue can stage messages of one specific type only.

Propagate Messages from One Queue to Another

Oracle Streams propagations can propagate messages from one queue to another. These queues can be in the same database or in different databases. Rules determine which messages are propagated by a propagation.

Oracle Streams enables you to configure an environment in which changes are shared through directed networks. In a directed network, propagated messages pass through one or more intermediate databases before arriving at a destination database where they are consumed. The messages might or might not be consumed at an intermediate database in addition to the destination database. Using Oracle Streams, you can choose which messages are propagated to each destination database, and you can specify the route messages will traverse on their way to a destination database.

Consume Messages

A message is consumed when it is dequeued from a queue. An apply process can dequeue messages implicitly. A user, application, or messaging client can dequeue messages explicitly. The database where messages are consumed is called the destination database. In some configurations, the source database and the destination database can be the same.

Rules determine which messages are dequeued and processed by an apply process. An apply process can apply messages directly to database objects or pass messages to custom PL/SQL subprograms for processing.

Rules determine which messages are dequeued by a messaging client. A messaging client dequeues messages when it is invoked by an application or a user.

Detect and Resolve Conflicts

An apply process detects conflicts automatically when directly applying LCRs in a replication environment. A conflict is a mismatch between the old values in an LCR and the expected data in a table. Typically, a conflict results when the same row in the source database and destination database is changed at approximately the same time.

When a conflict occurs, you need a mechanism to ensure that the conflict is resolved in accordance with your business rules. Oracle Streams offers a variety of prebuilt conflict handlers. Using these prebuilt handlers, you can define a conflict resolution system for each of your databases that resolves conflicts in accordance with your business rules. If you have a unique situation that prebuilt conflict resolution handlers cannot resolve, then you can build your own conflict resolution handlers.

If a conflict is not resolved, or if a handler procedure raises an error, then all messages in the transaction that raised the error are saved in the error queue for later analysis and possible reexecution.

Transform Messages

A rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. There are two types of rule-based transformations: declarative and custom.

Declarative rule-based transformations cover a set of common transformation scenarios for row LCRs, including renaming a schema, renaming a table, adding a column, renaming a column, keeping columns, and deleting a column. You specify (or declare) such a transformation using Oracle Enterprise Manager or a procedure in the DBMS_STREAMS_ADM package. Oracle Streams performs declarative transformations internally, without invoking PL/SQL.

A custom rule-based transformation requires a user-defined PL/SQL function to perform the transformation. Oracle Streams invokes the PL/SQL function to perform the transformation. A custom rule-based transformation can modify either LCRs or user messages. For example, a custom rule-based transformation can change the data type of a particular column in an LCR.

Either type of rule-based transformation can occur at the following times:

• During enqueue of a message by a capture process, which can be useful for formatting a message in a manner appropriate for all destination databases

• During propagation of a message, which can be useful for transforming a message before it is sent to a specific remote site

• During dequeue of a message by an apply process or messaging client, which can be useful for formatting a message in a manner appropriate for a specific destination database

When a transformation is performed during apply, an apply process can apply the transformed message directly or send the transformed message to an apply handler for processing.

Track Messages with Oracle Streams Tags

Every redo entry in the redo log has a tag associated with it. The data type of the tag is RAW. By default, when a user or application generates redo entries, the value of the tag is NULL for each redo entry, and a NULL tag consumes no space in the redo entry. The size limit for a tag value is 2000 bytes.

In Oracle Streams, rules can have conditions relating to tag values to control the behavior of Oracle Streams clients. For example, you can use a tag to determine whether an LCR contains a change that originated in the local database or at a different database, so that you can avoid change cycling (sending an LCR back to the database where it originated). Also, you can use a tag to specify the set of destination databases for each LCR. Tags can be used for other LCR tracking purposes as well.

You can specify Oracle Streams tags for redo entries generated by a certain session or by an apply process. These tags then become part of the LCRs captured by a capture process or synchronous capture. Typically, tags are used in Oracle Streams replication environments, but you can use them whenever it is necessary to track database changes and LCRs.

Share Information with Non-Oracle Databases

In addition to information sharing between Oracle databases, Oracle Streams supports heterogeneous information sharing between Oracle databases and non-Oracle databases.

Data Replication

Oracle Streams can capture data manipulation language (DML) and data definition language (DDL) changes made to database objects and replicate those changes to one or more other databases. An Oracle Streams capture process or synchronous capture captures changes made to source database objects and formats them into LCRs, which can be propagated to destination databases and then applied by Oracle Streams apply processes.

The destination databases can allow DML and DDL changes to the same database objects, and these changes might or might not be propagated to the other databases in the environment. In other words, you can configure an Oracle Streams environment with one database that propagates changes, or you can configure an environment where changes are propagated between databases bidirectionally. Also, the tables for which data is shared do not need to be identical copies at all databases. Both the structure and the contents of these tables can differ at different databases, and the information in these tables can be shared between these databases.

Data Warehouse Loading

Data warehouse loading is a special case of data replication. Some of the most critical tasks in creating and maintaining a data warehouse include refreshing existing data, and adding new data from the operational databases. Oracle Streams components can capture changes made to a production system and send those changes to a staging database or directly to a data warehouse or operational data store. Oracle Streams capture of redo data with a capture process avoids unnecessary overhead on the production systems. Oracle Streams provides a "one-step" procedure (DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE) that configures Oracle Streams to record the changes made to a table. Support for data transformations and user-defined apply procedures enables the necessary flexibility to reformat data or update warehouse-specific data fields as data is loaded. In addition, Change Data Capture uses some of the components of Oracle Streams to identify data that has changed so that this data can be loaded into a data warehouse.

Database Availability During Upgrade and Maintenance Operations

You can use the features of Oracle Streams to achieve little or no database down time during database upgrade and maintenance operations. Maintenance operations include migrating a database to a different platform, migrating a database to a different character set, modifying database schema objects to support upgrades to user-created applications, and applying an Oracle software patch.

Message Queuing

Oracle Streams Advanced Queuing (AQ) enables user applications to enqueue messages into a queue, propagate messages to subscribing queues, notify user applications that messages are ready for consumption, and dequeue messages at the destination. A queue can be configured to stage messages of a particular type only, or a queue can be configured as an ANYDATA queue. Messages of almost any type can be wrapped in an ANYDATA wrapper and staged in ANYDATA queues. Oracle Streams AQ supports all the standard features of message queuing systems, including multiconsumer queues, publish and subscribe, content-based routing, Internet propagation, transformations, and gateways to other messaging subsystems.

You can create a queue at a database, and applications can enqueue messages into the queue explicitly. Subscribing applications or messaging clients can dequeue messages directly from this queue. If an application is remote, then a queue can be created in a remote database that subscribes to messages published in the source queue. The destination application can dequeue messages from the remote queue. Alternatively, the destination application can dequeue messages directly from the source queue using a variety of standard protocols.

Event Management and Notification

Business events are valuable communications between applications or organizations. An application can enqueue messages that represent events into a queue explicitly, or an Oracle Streams capture process or synchronous capture can capture database events and encapsulate them into messages called LCRs. These messages can be the results of DML or DDL changes. Propagations can propagate messages in a stream through multiple queues. Finally, a user application can dequeue messages explicitly, or an Oracle Streams apply process can dequeue messages implicitly. An apply process can reenqueue these messages explicitly into the same queue or a different queue if necessary.

You can configure queues to retain explicitly-enqueued messages after consumption for a specified period of time. This capability enables you to use Oracle Streams Advanced Queuing (AQ) as a business event management system. Oracle Streams AQ stores all messages in the database in a transactional manner, where they can be automatically audited and tracked. You can use this audit trail to extract intelligence about the business operations.

Oracle Streams capture processes, synchronous captures, propagations, apply processes, and messaging clients perform actions based on rules. You specify which events are captured, propagated, applied, and dequeued using rules, and a built-in rules engine evaluates events based on these rules. The ability to capture events and propagate them to relevant consumers based on rules means that you can use Oracle Streams for event notification. Messages representing events can be staged in a queue and dequeued explicitly by a messaging client or an application, and then actions can be taken based on these events, which can include an e-mail notification, or passing the message to a wireless gateway for transmission to a cell phone or pager.

Data Protection

One solution for data protection is to create a local or remote copy of a production database. In the event of human error or a catastrophe, you can use the copy to resume processing.

You can use Oracle Data Guard SQL Apply, a data protection feature that uses some of the same infrastructure as Oracle Streams, to create and maintain a logical standby database, which is a logically equivalent standby copy of a production database. As in Oracle Streams replication, a capture process captures changes in the redo log and formats these changes into LCRs. These LCRs are applied at the standby databases. The standby databases are open for read/write and can include specialized indexes or other database objects. Therefore, these standby databases can be queried as updates are applied.

It is important to move the updates to the remote site as soon as possible with a logical standby database. Doing so ensures that, in the event of a failure, lost transactions are minimal. By directly and synchronously writing the redo logs at the remote database, you can achieve no data loss in the event of a disaster. At the standby system, the changes are captured and directly applied to the standby database with an apply process.

Sample Hub-and-Spoke Replication Configuration

Figure 1-2 shows a sample hub-and-spoke replication configuration. A hub-and-spoke replication configuration typically is used to distribute information to multiple target databases and to consolidate information from multiple databases to a single database.

A hub-and-spoke replication configuration is one in which a central database, or hub, communicates with one or more secondary databases, or spokes. The spokes do not communicate directly with each other. In a hub-and-spoke replication configuration, the spokes might or might not allow changes to the replicated database objects.

In the sample hub-and-spoke replication configuration shown in Figure 1-2, there is one hub database and two spoke databases. The spoke databases allow changes to the replicated database objects.

Figure 1-2 Sample Hub-and-Spoke Replication Configuration

Description of "Figure 1-2 Sample Hub-and-Spoke Replication Configuration"

For more information about this configuration, see Oracle Database 2 Day + Data Replication and Integration Guide.

Sample Replication Configuration with Downstream Capture

Figure 1-3 shows a sample replication configuration that uses a downstream capture process. Downstream capture means that the capture process runs on a remote database instead of the source database. Using downstream capture removes the capture workload from the production database.

In the sample replication configuration shown in Figure 1-3, the downstream capture process runs at the remote database dest.example.com, and the redo data is sent from the source database src.example.com to the remote database. At the remote database, a downstream capture process captures the changes in the redo data sent from the source database and an apply process applies these changes to the local database objects.

Figure 1-3 Sample Replication Configuration with Downstream Capture

Description of "Figure 1-3 Sample Replication Configuration with Downstream Capture"

For more information about this configuration, see Oracle Database 2 Day + Data Replication and Integration Guide.

Sample Replication Configuration That Uses Synchronous Captures

Figure 1-4 shows a sample replication configuration that uses synchronous captures to capture changes instead of capture processes. You can use a synchronous capture replication configuration to replicate changes to tables with infrequent data changes in a highly active database or in situations where capturing changes from the redo logs is not possible.

Figure 1-4 Sample Replication Configuration with Synchronous Captures

Description of "Figure 1-4 Sample Replication Configuration with Synchronous Captures"

For more information about this configuration, see Oracle Database 2 Day + Data Replication and Integration Guide.

Sample N-Way Replication Configuration

Figure 1-5 shows a sample n-way replication configuration. An n-way replication configuration typically is used in an environment with several peer databases and each database must replicate data with each of the other databases. An n-way replication configuration can provide load balancing, and it can provide failover protection if a single database becomes unavailable.

An n-way replication configuration is one in which each database communicates directly with each other database in the environment. The changes made to replicated database objects at one database are captured and sent directly to each of the other databases in the environment, where they are applied.

In the sample n-way replication configuration shown in Figure 1-5, each of the three databases captures changes to the replicated database objects and sends these changes to the other two databases in the configuration. Apply processes at each database apply the changes sent from the other two databases.

Figure 1-5 Sample N-Way Replication Configuration

Description of "Figure 1-5 Sample N-Way Replication Configuration"

For more information about this configuration, see Oracle Streams Extended Examples.

Sample Configuration That Performs Capture and Apply in a Single Database

Figure 1-6 shows a sample configuration that captures database changes with a capture process and applies these changes with an apply process in a single database. In this configuration, the apply process reenqueues the changes into the queue for processing by an application. Also, a procedure DML handler inserts rows that were deleted from the hr.employees table into a hr.emp_del table.

Figure 1-6 Sample Single Database Capture and Apply Configuration

Description of "Figure 1-6 Sample Single Database Capture and Apply Configuration"

For more information about this configuration, see Oracle Streams Extended Examples.

Sample Messaging Configuration

Figure 1-7 shows a sample messaging configuration. A messaging configuration sends messages from one queue to another queue. The two queues can be in the same database or in different databases. The messages can be dequeued and processed by applications in a customized way.

In the sample messaging configuration shown in Figure 1-7, a trigger at one database creates and enqueues messages. A propagation sends the messages to another database, where a PL/SQL procedure dequeues the messages and processes them.

Figure 1-7 Sample Messaging Configuration

Description of "Figure 1-7 Sample Messaging Configuration"

For more information about this configuration, see Oracle Database 2 Day + Data Replication and Integration Guide.

Documentation for Learning About Oracle Streams

Before setting up an Oracle Streams environment, it is best to understand the features of Oracle Streams and how you can use them. Table 1-1 helps you find conceptual information about Oracle Streams.

Documentation About Setting Up or Extending an Oracle Streams Environment

You can set up many different types of Oracle Streams environments, and you have several options for setting them up. Table 1-2 helps you find the documentation you need to set up an Oracle Streams environment.

Documentation About Managing an Oracle Streams Environment

You can use Oracle-supplied PL/SQL packages and Oracle Enterprise Manager to manage an Oracle Streams environment. Table 1-3 helps you find the documentation you need to manage an Oracle Streams environment.

Documentation About Monitoring an Oracle Streams Environment

You primarily use Oracle supplied PL/SQL packages, data dictionary views, and Oracle Enterprise Manager to manage an Oracle Streams environment. Table 1-4 helps you find the documentation you need to manage an Oracle Streams environment.

Documentation About Using Oracle Streams for Upgrade and Maintenance

You can use Oracle Streams to achieve little or no down time for one-time operations, such as upgrading a database. Table 1-5 helps you find the documentation you need to perform one-time operations with Oracle Streams.