Oracle Essentials Oracle Database 11g (60 page)

Overwrite and discard value

Used when there is a single master (originating) site for new values to update current values at destination sites.

Minimum and maximum value

Minimum compares the new value at the originating site and the current value at the destination and applies the new value only if it is less than the current value.

Maximum compares the new value at the originating site and the current value at the destination and applies the new value only if it is greater than the current value.

Moving Data Between Distributed Systems

|

311

Earliest and latest timestamp value (with designation of a column of type DATE)
Earliest dictates that when there are multiple new values, the value used for updates will be in the row with the earliest timestamp.

Latest dictates that when there are multiple new values, the value used for updates will be in the row with the latest timestamp.

Additive and average value for column groups with single numeric columns
Additive takes the difference of new and old values at the originating site and adds them to the current value at the destination site.

Average takes the current value at the destination and the new value at the originating site, divides by 2, and applies the new value.

Priority groups and site priority

When priority levels are assigned to columns and multiple new values occur, higher priority columns will update columns with lower priority.

Uniqueness conflict-resolution routines are used to resolve conflicts that result from the distributed use of primary key and unique constraints. The built-in routines include the following:

Append site name to duplicate value

Appends the global database name of the originating site to the replicated column.

Append sequence to duplicate value

Appends a generated sequence number to the column value.

Discard duplicate value

Discards the row at the originating site that causes errors.

You can also write your own custom conflict-resolution routines and assign them if your business requirements are not addressed by the standard routines.

Managing Advanced Replication

Youcan manage replication through Oracle Enterprise Manager. Administrators can configure database objects that need to be replicated, schedule replication, troubleshoot error conditions, and view the deferred transaction queue at each location through this central interface. A deferred transaction queue is a queue holding transactions that will be replicated (and applied) to child sites.

For example, to set up a typical multimaster replication, you must first define master groups and tables and objects to be replicated in each of the databases.

Youdefine a connection to the master definition site, and then create one or more master groups for replicating tables and objects to the multiple master sites. Next, you assign conflict-resolution routines for replicated tables in each master group.

Finally, yougrant appropriate access privileges to users of applications that access the data at the multiple sites.

312

|

Chapter 13: Oracle Distributed Databases and Distributed Data
Advanced Queuing

In the 1980s,
message-oriented middleware
(MOM) gained popular usage. MOM uses
messages
to transmit information between systems. It doesn’t require the overhead of a two-phase commit because the MOM itself guarantees the delivery of all messages.

Products such as IBM’s MQSeries store control information (message destination, expiration, priority, and recipients) and the message contents in a file-based queue.

Delivery is guaranteed in that the message will remain in the queue until the destination is available and the message is forwarded.

Oracle’s Advanced Queuing (AQ) facility, first introduced with Oracle8 Enterprise Edition and now part of Oracle Streams, provides a complete queuing environment by storing the queue in the Oracle relational database. Advanced queues are Oracle database tables that support queuing operations—in particular,
enqueue
to create messages and
dequeue
to consume them. These messages, which can either be unstructured (raw) or structured (as Oracle objects, which are described in

Chapter 14),
correspond to rows in a table. Messages are stored in normal queues for normal message handling or in exception queues if they cannot be retrieved for some reason.

Queue creation and management

Queues are created through PL/SQL commands or the Java API. An administrator creates a queue by following these steps:

1. Create a queue table.

2. Create and name the queue.

3. Specify the queue as a normal queue or an exception queue.

4. Specify how long messages remain in the queue: indefinitely, for a fixed length of time, until a particular time elapses between retries, or based on the number of retries.

Queues can be started and stopped by the administrator, who also grants users the privileges necessary for using the queue and revokes those privileges when necessary.

Producers of messages specify a queue name, enqueue options, message properties, and the payload to be put into the queue, which is then handled by a producer agent.

Consumer agents listen for messages in one or more queues that are then dequeued so users can use the contents. Notification of the existence of messages in the queue can occur via the Oracle Call Interface (OCI; described in
Chapter 1)
callback registration or through a listen call that can be used by applications to monitor for messages in multiple queues.

Because messages are stored persistently in queues in the database, a number of message-management features are available. End-to-end tracking is enabled since each message carries its history with it, including location and state of the message, nodes
Moving Data Between Distributed Systems

|

313

visited, and previous recipients. Messages that don’t reach subscribers within a defined lifetime are moved to the exception queue, from which they can be traced.

Messages that successfully reach subscribers may be retained after consumption for additional analysis, including enqueue and dequeue times. As messages may be related (for example, one message might be caused by the successful execution of two other messages), retaining the messages can be useful in tracking sequences.

Oracle Database 10
g
Release 2 introduced the ability to use nonpersistent message queuing for better performance in situations where there is not a need for the capabilities provided by data-based queues.

Queue management through Oracle Enterprise Manager includes the following capabilities:

• Creating, dropping, starting, and stopping queues.

• Adding and removing subscribers.

• Scheduling message propagation from local to remote queues.

• Displaying queue statistics, including the average queue length, the number of messages in the wait state, the number of messages in the ready state, and the number of expired messages.

Oracle9
i
introduced several new AQ capabilities:

• XML-based messaging over HTTP enables support across firewalls; requests may be through the XML-based Internet Document Access Protocol (
i
DAP).

• AQ policies and services can be defined using Dynamic Services.

• AQ agents can be defined in and managed through the Oracle Internet Directory (OID).

Oracle9
i
(and beyond) AQ includes a built-in message transformation for PL/SQL

and XSLT. A messaging gateway is also available for propagation to other systems, such as MQSeries and TIBCO.

Publish-and-subscribe capabilities

Oracle8
i
Enterprise Edition introduced publish-and-subscribe capabilities to Advanced Queuing. As illustrated in
Figure 13-3,
a
publisher
puts a message onto a queue, while a
subscriber
receives messages from a queue. The publisher and subscriber interact separately with the queue, and neither party needs to know of the existence of the other. Publishers decide when, how, and what to publish, while subscribers express an interest. Messages can be published and subscribed to using the subject name or content (through filtering rules). Asynchronous notification is enabled when subscribers register callback functions.

You can use Advanced Queuing and its publish-and-subscribe features for additional notification of database events that, in turn, improve the management of the
314

|

Chapter 13: Oracle Distributed Databases and Distributed Data
Publish Message

Subscribe to Queue

Oracle Database Server

with Advanced Queuing / Rules

Engine

Publisher

Subscriber

Application

Application

Figure 13-3. Advanced Queuing configuration for publish-subscribe applications
database or business applications. Database events such as DML (inserts, updates, deletions) and system events (startup, shutdown, and so on) can be published and subscribed to. As an example, an application may be built to automatically inform a subscriber when a shipment occurs to certain highly valued customers; the subscriber would then know that she should begin to track the shipment’s progress and alert the customer that it is in transit.

Oracle Database 11
g
includes several enhancements to Oracle’s messaging server that increase the performance and reliability of the server.

Oracle Streams

Oracle9
i
Release 2 introduced Oracle Streams, which folded the capabilities of Advanced Replication and Advanced Queuing into a single product family and added a method of sharing data and events within a database or between databases.

Streams enable the propagation of changes via a capture-and-apply process, including Oracle’s change data capture. Changes can be propagated between Oracle instances, from Oracle instances to non-Oracle instances (via Transparent Gateways), and from non-Oracle databases to Oracle (via messaging gateways in combination with custom code on the non-Oracle source to collect changes).

Streams leverages log-based procedures to capture DML or DDL changes or synchronous capture for DML changes and then uses queuing procedures as part of the staging. User-supplied “apply” rules define consumption.

When changes are captured from an Oracle source database redo log or changes in rows are gathered from synchronous capture, a background database process creates a logical change record (LCR). LCR and user message events are enqueued in a
Moving Data Between Distributed Systems

|

315

Streams queue. Events are propagated from source to target queues and then, via a background process, dequeued in the target database and applied. Since Oracle Database 10
g
, downstream capture of changes and enqueue/dequeue of messages in batch are supported.

Also, since Oracle Database 10
g
, Streams can be configured to provide Database Change Notification via email, HTTP, and PL/SQL. This feature can send notifications to a client whenever the data in a query result set has changed. Oracle Database 11
g
enhances this feature enabling notification for individual row changes, rather than just a single notification whenever any row in the result set changes.

As of Oracle Database 10
g
Release 2, Streams can be managed through Oracle Enterprise Manager. A migration tool is available to aid migration from Advanced Replication to Streams.

Streams and Grid Computing

Oracle Streams provides key functionality in grid computing implementations. By its nature, grid computing can consist of widely distributed data, users, and platforms.

Streams enables the movement of data when and where it is needed, as well as message sharing, notification or invocation of user procedures on events, message and database change subscriptions, and interoperation with other platforms. Streaming databases can offload processing to replica databases by creating operational data stores, or can create replicas and apply changes from replicas or data transformations to the production database.

As of Oracle Database 11
g
, Streams can mine active online log files for DML and DDL, enabling low-latency change propagation among RAC instances. Streams runs from a single RAC instance identified as the primary for queues and processes. A secondary instance can be identified to provide a more highly available solution.

Streams can also play a role in database migrations to grid computing and newer Oracle versions. When the new database is installed and while the original database remains in production, Streams can be used to capture changes on the original database that are then applied to the new database as migration nears completion.

Transportable Tablespaces

The previous sections focused on sharing data between distributed databases when the data is “live”—making sure changes are propagated to other databases in real time.
Transportable tablespaces
are a way to speed up the distribution of complete tablespaces between multiple databases while the tablespaces are not active.

316

|

Chapter 13: Oracle Distributed Databases and Distributed Data
Transportable tablespaces were introduced with Oracle8
i
Enterprise Edition to rapidly copy and distribute tablespaces among database instances. Previously, tablespaces needed to be exported from the source database and imported at the target (or unloaded and loaded). Transportable tablespaces enable copies to be moved simply through the use of file transfer commands such as
ftp
.

Before youcopy and move a copy of the tablespace, youshould make the tablespace read-only to avoid inadvertently changing it. Data dictionary information needs to be exported from the source prior to transfer, then imported at the target.