4 Oracle Streams Information Consumption

Ways to Consume Information with Oracle Streams

The following are ways to consume information with Oracle Streams:

• Implicit Consumption

• Explicit Consumption

Types of Information Consumed with Oracle Streams

The following types of information can be consumed with Oracle Streams:

• Captured LCRs

• Persistent LCRs

• Buffered LCRs

• Persistent User Messages

• Buffered User Messages

Summary of Information Consumption Options

Figure 4-1 summarizes the information consumption options available with Oracle Streams.

Introduction to the Apply Process

An apply process is an optional Oracle background process that dequeues messages from a specific queue and either applies each message directly, discards it, passes it as a parameter to an apply handler, or re-enqueues it. These messages can be logical change records (LCRs) or user messages.

Apply Process Rules

An apply process applies messages based on rules that you define. For LCRs, each rule specifies the database objects and types of changes for which the rule evaluates to TRUE. For user messages, you can create rules to control apply process behavior for specific types of messages. You can place these rules in the positive rule set or negative rule set for the apply process.

If a rule evaluates to TRUE for a message, and the rule is in the positive rule set for an apply process, then the apply process dequeues and processes the message. If a rule evaluates to TRUE for a message, and the rule is in the negative rule set for an apply process, then the apply process discards the message. If an apply process has both a positive and a negative rule set, then the negative rule set is always evaluated first.

You can specify apply process rules for LCRs at the following levels:

• A table rule applies or discards either row changes resulting from DML changes or DDL changes to a particular table. Subset rules are table rules that include a subset of the row changes to a particular table.

• A schema rule applies or discards either row changes resulting from DML changes or DDL changes to the database objects in a particular schema.

• A global rule applies or discards either all row changes resulting from DML changes or all DDL changes in the queue associated with an apply process.

Types of Messages That Can Be Processed with an Apply Process

Apply processes can dequeue the following types of messages:

• Captured LCRs: A logical change record (LCR) that was captured implicitly by a capture process and enqueued into the buffered queue portion of an ANYDATA queue. In some situations, an optimization enables capture processes to send LCRs directly to apply processes. This optimization is called combined capture and apply.

• Persistent LCRs: An LCR that was captured implicitly by a synchronous capture, constructed and enqueued persistently by an application, or enqueued by an apply process. A persistent LCR is enqueued into the persistent queue portion of an ANYDATA queue.

• Persistent user messages: A non-LCR message of a user-defined type that was enqueued explicitly by an application or an apply process. A persistent user message is enqueued into the persistent queue portion of an ANYDATA queue. In addition, a user message can be enqueued into an ANYDATA queue or a typed queue, but an apply process can dequeue only user messages in an ANYDATA queue.

A single apply process cannot dequeue both from the buffered queue and persistent queue portions of a queue. If messages in both the buffered queue and persistent queue must be processed by an apply process, then the destination database must have at least two apply processes to process the messages.

Message Processing Options for an Apply Process

An apply process can either apply messages directly or send messages to an apply handler for processing. Your options for message processing depend on whether or not the message received by an apply process is a row logical change record (row LCR), a DDL logical change record (DDL LCR), or a user message.

Figure 4-1 shows the message processing options for an apply process and which options can be used for different types of messages.

Figure 4-1 Apply Process Message Processing Options

Description of "Figure 4-1 Apply Process Message Processing Options"

The following sections describe these message processing options:

• Apply LCRs Directly

• Process Messages with Apply Handlers

• Summary of Message Processing Options

The Source of Messages Applied by an Apply Process

The following list describes source database for different types of messages that are processed by an apply process:

• For a captured LCR, the source database is the database where the change encapsulated in the LCR was generated in the redo log.

• For a persistent LCR captured by a synchronous capture, the source database is the database where the synchronous capture that captured the row LCR is configured.

• For a persistent LCR constructed and enqueued by an application, the source database is the database where the message was first enqueued.

• For a user message, the source database is the database where the message was first enqueued.

A single apply process can apply user messages that originated at multiple databases. However, a single apply process can apply captured LCRs from only one source database. Similarly, a single apply process can apply persistent LCRs captured by a synchronous capture from only one source database. Applying these LCRs requires knowledge of the dependencies, meaningful transaction ordering, and transactional boundaries at the source database.

Captured LCRs from multiple databases can be sent to a single destination queue. The same is true for persistent LCRs captured by a synchronous capture. However, if a single queue contains these LCRs from multiple source databases, then there must be multiple apply processes retrieving these LCRs. Each of these apply processes should be configured to receive messages from exactly one source database using rules. Oracle recommends that you use a separate ANYDATA queue for messages from each source database.

Also, each apply process can apply captured LCRs from only one capture process. If multiple capture processes are running on a source database, and LCRs from more than one of these capture processes are applied at a destination database, then there must be one apply process to apply changes from each capture process. In such an environment, Oracle recommends that each ANYDATA queue used by a capture process, propagation, or apply process have captured LCRs from at most one capture process from a particular source database. A queue can contain LCRs from more than one capture process if each capture process is capturing changes that originated at a different source database.

The same restriction applies to persistent LCRs captured by multiple synchronous captures at the same source database. These messages should be stored in separate ANYDATA queues, and a separate apply process should apply the messages from each synchronous capture.

Data Types Applied

When applying row LCRs resulting from DML changes to tables, an apply process applies changes made to columns of the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• FLOAT

• LONG

• DATE

• BINARY_FLOAT

• BINARY_DOUBLE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• LONG RAW

• CHAR

• NCHAR

• CLOB with BASICFILE storage

• NCLOB with BASICFILE storage

• BLOB with BASICFILE storage

• UROWID

• XMLType stored as CLOB, object relationally, or as binary XML

An apply process does not apply row LCRs containing the results of DML changes in columns of the following data types:

• SecureFile CLOB, NCLOB, and BLOB

• BFILE

• ROWID

• User-defined types (including object types, REFs, varrays, and nested tables)

• The following Oracle-supplied types: Any types, URI types, spatial types, and media types

An apply process raises an error if it attempts to apply a row LCR that contains information about a column of an unsupported data type. Next, the apply process moves the transaction that includes the LCR into the error queue.

Oracle Streams Apply Processes and RESTRICTED SESSION

When restricted session is enabled during system startup by issuing a STARTUP RESTRICT statement, apply processes do not start, even if they were running when the database shut down. When the restricted session is disabled, each apply process that was not stopped is started.

When restricted session is enabled in a running database by the SQL statement ALTER SYSTEM ENABLE RESTRICTED SESSION, it does not affect any running apply processes. These apply processes continue to run and apply messages. If a stopped apply process is started in a restricted session, then the apply process does not actually start until the restricted session is disabled.

Oracle Streams Apply Processes and Oracle Real Application Clusters

You can configure an Oracle Streams apply process to apply changes in an Oracle Real Application Clusters (Oracle RAC) environment. Each apply process is started and stopped on the owner instance for its ANYDATA queue, even if the start or stop procedure is run on a different instance.

If the owner instance for a queue table containing a queue used by an apply process becomes unavailable, then queue ownership is transferred automatically to another instance in the cluster. Also, an apply process will follow its queue to a different instance if the current owner instance becomes unavailable. The queue itself follows the rules for primary instance and secondary instance ownership. In addition, if the apply process was enabled when the owner instance became unavailable, then the apply process is restarted automatically on the new owner instance. If the apply process was disabled when the owner instance became unavailable, then the apply process remains disabled on the new owner instance.

The DBA_QUEUE_TABLES data dictionary view contains information about the owner instance for a queue table. Also, in an Oracle RAC environment, an apply coordinator process, its corresponding apply reader server, and all of its apply servers run on a single instance.

Apply Processes and Transparent Data Encryption

An apply process can dequeue and process implicitly captured row logical change records (row LCRs) that contain columns encrypted using transparent data encryption. When row LCRs with encrypted columns are dequeued by an apply process, the encrypted columns are decrypted. These row LCRs with decrypted columns can be sent to an apply handler for custom processing, or they can be applied directly. When row LCRs are applied, and the modified table contains encrypted columns, any changes to encrypted columns are encrypted when they are applied.

When row LCRs contain encrypted columns, but the corresponding columns at the destination database are not encrypted, then the preserve_encryption apply process parameter controls apply process behavior:

• If the preserve_encryption parameter is set to y, then the apply process raises an error when row LCRs contain encrypted columns, but the corresponding columns at the destination database are not encrypted. When an error is raised, the row LCR is not applied, and all of the row LCRs in the transaction are moved to the error queue.

• If the preserve_encryption parameter is set to n, then the apply process applies the row change when row LCRs contain encrypted columns, but the corresponding columns at the destination database are not encrypted.

Apply Process Components

An apply process consists of the following components:

• A reader server that dequeues messages. The reader server is a process that computes dependencies between LCRs and assembles messages into transactions. The reader server then returns the assembled transactions to the coordinator process, which assigns them to idle apply servers.

• A coordinator process that gets transactions from the reader server and passes them to apply servers. The coordinator process name is APnn, where nn can include letters and numbers. The coordinator process is an Oracle background process.

• One or more apply servers that apply LCRs to database objects as DML or DDL statements or that pass the LCRs to their appropriate apply handlers. For non-LCR messages, the apply servers pass the messages to the message handler. Apply servers can also enqueue LCR and non-LCR messages into the persistent queue portion of a queue specified by the DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION procedure. Each apply server is a process. If an apply server encounters an error, then it then tries to resolve the error with a user-specified conflict handler or error handler. If an apply server cannot resolve an error, then it rolls back the transaction and places the entire transaction, including all of its messages, in the error queue.

  When an apply server commits a completed transaction, this transaction has been applied. When an apply server places a transaction in the error queue and commits, this transaction also has been applied.

The reader server name and the apply server process names are ASnn, where nn can include letters and numbers. If a transaction being handled by an apply server has a dependency on another transaction that is not known to have been applied, then the apply server contacts the coordinator process and waits for instructions. The coordinator process monitors all of the apply servers to ensure that transactions are applied and committed in the correct order.

The following sections describe the possible states for each apply process component:

• Reader Server States

• Coordinator Process States

• Apply Server States

Apply User

Messages are applied in the security domain of the apply user for an apply process. The apply user dequeues all messages that satisfy the apply process rule sets. The apply user can apply messages directly to database objects. In addition, the apply user runs all custom rule-based transformations specified by the rules in these rule sets. The apply user also runs user-defined apply handlers.

The apply user must have the necessary privileges to apply changes, including EXECUTE privilege on the rule sets used by the apply process, EXECUTE privilege on all custom rule-based transformation functions specified for rules in the positive rule set, EXECUTE privilege on any apply handlers, and privileges to dequeue messages from the apply process queue. An apply process can be associated with only one user, but one user can be associated with many apply processes.

Apply Process Creation

You can create an apply process using the DBMS_STREAMS_ADM package or the DBMS_APPLY_ADM package. Using the DBMS_STREAMS_ADM package to create an apply process is simpler because defaults are used automatically for some configuration options. Alternatively, using the DBMS_APPLY_ADM package to create an apply process is more flexible.

When you create an apply process by running the CREATE_APPLY procedure in the DBMS_APPLY_ADM package, you can specify nondefault values for the apply_captured, apply_database_link, and apply_tag parameters. Then you can use the procedures in the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package to add rules to a rule set for the apply process.

If you create more than one apply process in a database, then the apply processes are completely independent of each other. These apply processes do not synchronize with each other, even if they apply LCRs from the same source database.

Table 4-3 describes the differences between using the DBMS_STREAMS_ADM package and the DBMS_APPLY_ADM package for apply process creation.

Oracle Streams Data Dictionary for an Apply Process

When a database object is prepared for instantiation at a source database, an Oracle Streams data dictionary is populated automatically at the database where changes to the object are captured by a capture process. The Oracle Streams data dictionary is a multiversioned copy of some of the information in the primary data dictionary at a source database. The Oracle Streams data dictionary maps object numbers, object version information, and internal column numbers from the source database into table names, column names, and column data types. This mapping keeps each captured LCR as small as possible because a captured LCR can often use numbers rather than names internally.

Unless a captured LCR is passed as a parameter to a custom rule-based transformation during capture or propagation, the mapping information in the Oracle Streams data dictionary at the source database is needed to interpret the contents of the LCR at any database that applies the captured LCR. To make this mapping information available to an apply process, Oracle automatically populates a multiversioned Oracle Streams data dictionary at each destination database that has an Oracle Streams apply process. Oracle automatically propagates relevant information from the Oracle Streams data dictionary at the source database to all other databases that apply captured LCRs from the source database.

Apply Process Parameters

After creation, an apply process is disabled so that you can set the apply process parameters for your environment before starting the process for the first time. Apply process parameters control the way an apply process operates. For example, the time_limit apply process parameter specifies the amount of time an apply process runs before it is shut down automatically. After you set the apply process parameters, you can start the apply process.

This section contains the following topics:

• Apply Process Parallelism

• Commit Serialization

• Automatic Restart of an Apply Process

• Stop or Continue on Error

Multiple Apply Processes in a Single Database

If you run multiple apply processes in a single database, consider increasing the size of the System Global Area (SGA). In an Oracle Real Application Clusters (Oracle RAC) environment, consider increasing the size of the SGA for each instance. Use the SGA_MAX_SIZE initialization parameter to increase the SGA size. Also, if the size of the Oracle Streams pool is not managed automatically in the database, then you should increase the size of the Oracle Streams pool by 1 MB for each apply process parallelism. For example, if you have two apply processes running in a database, and the parallelism parameter is set to 4 for one of them and 1 for the other, then increase the Oracle Streams pool by 5 MB (4 + 1 = 5 parallelism).

Persistent Apply Process Status upon Database Restart

An apply process maintains a persistent status when the database running the apply process is shut down and restarted. For example, if an apply process is enabled when the database is shut down, then the apply process automatically starts when the database is restarted. Similarly, if an apply process is disabled or aborted when a database is shut down, then the apply process is not started and retains the disabled or aborted status when the database is restarted.

The Error Queue

The error queue contains all of the current apply errors for a database. If there are multiple apply processes in a database, then the error queue contains the apply errors for each apply process. To view information about apply errors, query the DBA_APPLY_ERROR data dictionary view or use Enterprise Manager.

The error queue stores information about transactions that could not be applied successfully by the apply processes running in a database. A transaction can include many messages. When an unhandled error occurs during apply, an apply process automatically moves all of the messages in the transaction that satisfy the apply process rule sets to the error queue.

You can correct the condition that caused an error and then reexecute the transaction that caused the error. For example, you might modify a row in a table to correct the condition that caused an error.

When the condition that caused the error has been corrected, you can either reexecute the transaction in the error queue using the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure, or you can delete the transaction from the error queue using the DELETE_ERROR or DELETE_ALL_ERRORS procedure. These procedures are in the DBMS_APPLY_ADM package.

When you reexecute a transaction in the error queue, you can specify that the transaction be executed either by the user who originally placed the error in the error queue or by the user who is reexecuting the transaction. Also, the current Oracle Streams tag for the apply process is used when you reexecute a transaction in the error queue.

A reexecuted transaction uses any relevant apply handlers and conflict resolution handlers. If, to resolve the error, a row LCR in an error queue must be modified before it is executed, then you can configure a DML handler to process the row LCR that caused the error in the error queue. In this case, the DML handler can modify the row LCR in some way to avoid a repetition of the same error. The row LCR is passed to the DML handler when you reexecute the error containing the row LCR.

The error queue contains information about errors encountered at the local destination database only. It does not contain information about errors for apply processes running in other databases in an Oracle Streams environment.

The error queue uses the exception queues in the database. When you create an ANYDATA queue using the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, the procedure creates a queue table for the queue if one does not already exist. When a queue table is created, an exception queue is created automatically for the queue table. Multiple queues can use a single queue table, and each queue table has one exception queue. Therefore, a single exception queue can store errors for multiple queues and multiple apply processes.

An exception queue only contains the apply errors for its queue table, but the Oracle Streams error queue contains information about all of the apply errors in each exception queue in a database. You should use the procedures in the DBMS_APPLY_ADM package to manage Oracle Streams apply errors. You should not dequeue apply errors from an exception queue directly.

If you are capturing changes to tables with columns that were encrypted using transparent data encryption, then encrypted columns in implicitly captured row LCRs are encrypted while the row LCRs are in the error queue. Row LCRs are implicitly captured using capture processes and synchronous captures.