Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

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

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

4 Oracle Streams Information Consumption

The following topics contain information about consuming information with Oracle Streams.

See Also:

Overview of Information Consumption with Oracle Streams

Consuming information with Oracle Streams means dequeuing a message that contains the information from a queue and either processing or discarding the message. The consumed information can describe a database change, or it can be any other type of information. A dequeued message might have originated at the same database where it is dequeued, or it might have originated at a different database.

This section contains these topics:

Ways to Consume Information with Oracle Streams

The following are ways to consume information with Oracle Streams:

Implicit Consumption

With implicit consumption, an apply process automatically dequeues either captured LCRs, persistent LCRs, or persistent user messages. The queue must be an ANYDATA queue. If a message contains a logical change record (LCR), then the apply process can either apply it directly or call a user-specified procedure for processing. If the message does not contain an LCR, then the apply process can invoke a user-specified procedure called a message handler to process it.

Note:

Captured LCRs must be dequeued by an apply process. However, if an apply process or a user procedure called by an apply process re-enqueues a captured LCR, then the LCR becomes a persistent LCR and can be explicitly dequeued.

Explicit Consumption

With explicit consumption, messages are dequeued in one of the following ways:

Types of Information Consumed with Oracle Streams

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

Captured LCRs

A captured LCR is a logical change record (LCR) that was captured implicitly by a capture process and enqueued into the buffered queue portion of an ANYDATA queue.

Only an apply process can dequeue captured LCRs. After dequeue, an apply process can apply the captured LCR directly to make a database change, discard the captured LCR, send the captured LCR to an apply handler for processing, or re-enqueue the captured LCR into a persistent queue.

Persistent LCRs

A persistent LCR is a logical change record (LCR) that was enqueued into the persistent queue portion of an ANYDATA queue. A persistent LCR can be enqueued in one of the following ways:

  • Captured implicitly by a synchronous capture and enqueued

  • Constructed explicitly by an application and enqueued

  • Dequeued by an apply process and enqueued by the same apply process using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package

Persistent LCRs can be dequeued by an apply process, a messaging client, or an application.

Buffered LCRs

A buffered LCR is a logical change record (LCR) that was constructed explicitly by an application and enqueued into the buffered queue portion of an ANYDATA queue. Only an application can dequeue buffered LCRs.

Persistent User Messages

A persistent user message is a non-LCR message of a user-defined type that was enqueued into a persistent queue. A persistent user message can be enqueued in one of the following ways:

  • Created explicitly by an application and enqueued

  • Dequeued by an apply process and enqueued by the same apply process using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package

Apply processes and messaging clients can only dequeue persistent user messages that are in an ANYDATA queue. Applications can dequeue persistent user messages that are in an ANYDATA queue or a typed queue.

Buffered User Messages

A buffered user message is a non-LCR message of a user-defined type that was created explicitly by an application and enqueued into a buffered queue. A buffered user message can be enqueued into the buffered queue portion of an ANYDATA queue or a typed queue. Only an application can dequeue buffered user messages.

Summary of Information Consumption Options

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

Table 4-1 Information Consumption Options with Oracle Streams

Consumption Type Dequeues Messages Message Types Use When

Implicit Consumption with an Apply Process

Continually and automatically when enabled

Captured LCRs

Persistent LCRs

Persistent user messages

You want to dequeue and process captured LCRs.

You want to dequeue persistent LCRs or persistent user messages continually and automatically from the persistent queue portion of an ANYDATA queue.

You want to dequeue LCRs that must be applied directly to database objects to make database changes.

You want to dequeue messages and process them with an apply handler.

Explicit Consumption with a Messaging Client

When invoked by an application

Persistent LCRs

Persistent user messages

You want to use a simple method for dequeuing on demand persistent LCRs or persistent user messages from the persistent queue portion of an ANYDATA queue.

You want to send messages to an application for processing after dequeue.

Explicit Consumption with Manual Dequeue

Manually according to application logic

Persistent LCRs

Buffered LCRs

Persistent user messages

Buffered user messages

You want an application to dequeue manually persistent LCRs or buffered LCRs from an ANYDATA queue and process them.

You want an application to dequeue manually persistent user messages or buffered user messages from an ANYDATA queue or a typed queue and process them.


Note:

A single database can use any combination of the information consumption options summarized in the table.

See Also:

Implicit Consumption with an Apply Process

This section explains the concepts related to Oracle Streams apply processes.

This section contains these topics:

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.

Note:

An apply process can only dequeue messages from an ANYDATA queue, not a typed queue.

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. A subset rule is a table rule 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 to apply processes more efficiently. 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 follows
Description of "Figure 4-1 Apply Process Message Processing Options"

The following sections describe these message processing options:

Apply LCRs Directly

If you use this option, then an apply process applies LCRs without running a user procedure. The apply process either successfully applies the change in the LCR or, if a conflict or an apply error is encountered, tries to resolve the error with a conflict handler or a user-specified procedure called an error handler.

If a conflict handler can resolve the conflict, then it either applies the LCR or it discards the change in the LCR. If the error handler can resolve the error, then it should apply the LCR, if appropriate. An error handler can resolve an error by modifying the LCR before applying it. If the conflict handler or error handler cannot resolve the error, then the apply process places the transaction, and all LCRs associated with the transaction, into the error queue.

Note:

An apply process cannot apply non-LCR messages directly. Each user message dequeued by an apply process must be processed with a message handler.

Process Messages with Apply Handlers

When you use an apply handler, an apply process passes a message as a parameter to a user procedure for processing. The user procedure can process the message in a customized way.

The following sections provide information about specific apply handlers and considerations for using apply handlers:

DML Handlers

A user procedure that processes row LCRs is called a DML handler. An apply process can have many DML handlers. A DML handler can process row LCRs that were created by a capture process, a synchronous capture, or an application.

For each table associated with an apply process, you can set a separate DML handler to process each of the following types of operations in row LCRs:

  • INSERT

  • UPDATE

  • DELETE

  • LOB_UPDATE

For example, the hr.employees table can have one DML handler procedure to process INSERT operations and a different DML handler procedure to process UPDATE operations. Alternatively, the hr.employees table can use the same DML handler procedure for each type of operation.

The user procedure can perform any customized processing of row LCRs. For example, if you want each insert into a particular table at the source database to result in inserts into multiple tables at the destination database, then you can create a user procedure that processes INSERT operations on the table to accomplish this.

A DML handler should never commit and never roll back, except to a named savepoint that the user procedure has established. To execute a row LCR inside a DML handler, invoke the EXECUTE member procedure for the row LCR.

To set a DML handler, use the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. You can either set a DML handler for a specific apply process, or you can set a DML handler to be a general DML handler that is used by all apply processes in the database. If a DML handler for an operation on a table is set for a specific apply process, and another DML handler is a general handler for the same operation on the same table, then the specific DML handler takes precedence over the general DML handler.

You create an error handler in the same way that you create a DML handler, except that you set the error_handler parameter to TRUE when you run the SET_DML_HANDLER procedure. An error handler is invoked only if an apply error results when an apply process tries to apply a row LCR for the specified operation on the specified table.

Typically, DML handlers are used in Oracle Streams replication environments to perform custom processing of row LCRs, but DML handlers can be used in nonreplication environments as well. For example, such handlers can be used to record changes made to database objects without replicating these changes.

Note:

When you run the SET_DML_HANDLER procedure, you specify the object for which the handler is used. This object does not need to exist at the destination database when you run the procedure.

See Also:

DDL Handlers

A user procedure that processes DDL LCRs is called a DDL handler. An apply process can have only one DDL handler, and this single DDL handler processes all DDL LCRs dequeued by the apply process. A DDL handler can process DDL LCRs that were created by a capture process or an application.

The user procedure can perform any customized processing of DDL LCRs. For example, if you want to log DDL changes before applying them, then you can create a user procedure that processes DDL operations to accomplish this.

To execute a DDL LCR inside a DDL handler, invoke the EXECUTE member procedure for the DDL LCR. To associate a DDL handler with a particular apply process, use the ddl_handler parameter in the CREATE_APPLY or the ALTER_APPLY procedure in the DBMS_APPLY_ADM package.

Typically, DDL handlers are used in Oracle Streams replication environments to perform custom processing of DDL LCRs, but these handlers can be used in nonreplication environments as well. For example, such handlers can be used to record changes made to database objects without replicating these changes.

See Also:

Message Handlers

A persistent user message is processed by the message handler specified for an apply process. A message handler is a user-defined procedure that can process user messages in a customized way for your environment.

A message handler offers advantages in any environment that has applications that must update one or more remote databases or perform some other remote action. These applications can enqueue user messages into a queue at the local database, and Oracle Streams can propagate each user message to the appropriate queues at destination databases. If there are multiple destinations, then Oracle Streams provides the infrastructure for automatic propagation and processing of these messages at these destinations. If there is only one destination, then Oracle Streams still provides a layer between the application at the source database and the application at the destination database, so that, if the application at the remote database becomes unavailable, then the application at the source database can continue to function normally.

For example, a message handler can convert a user message into an electronic mail message. In this case, the user message can contain the attributes you would expect in an electronic mail message, such as from, to, subject, text_of_message, and so on. After converting a message into an electronic mail messages, the message handler can send it out through an electronic mail gateway.

You can specify a message handler for an apply process using the message_handler parameter in the CREATE_APPLY or the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. An Oracle Streams apply process always assumes that a non-LCR message has no dependencies on any other messages in the queue. If parallelism is greater than 1 for an apply process that applies persistent user messages, then these messages can be dequeued by a message handler in any order. Therefore, if dependencies exist between these messages in your environment, then Oracle recommends that you set apply process parallelism to 1.

Precommit Handlers

You can use a precommit handler to audit commit directives for captured LCRs and transaction boundaries for persistent LCRs and persistent user messages. A precommit handler is a user-defined PL/SQL procedure that can receive the commit information for a transaction and process the commit information in any customized way. A precommit handler can work with a DML handler or a message handler.

For example, a handler can improve performance by caching data for the length of a transaction. This data can include cursors, temporary LOBs, data from a message, and so on. The precommit handler can release or execute the objects cached by the handler when a transaction completes.

A precommit handler executes when the apply process commits a transaction. You can use the commit_serialization apply process parameter to control the commit order for an apply process.

The following list describes commit directives and transaction boundaries:

  • Commit Directives for Captured LCRs: When you are using a capture process, and a user commits a transaction, the capture process captures an internal commit directive for the transaction if the transaction contains row LCRs that were captured by the capture process. The capture process also records the transaction identifier in each captured LCR in a transaction.

    Once enqueued, these commit directives can be propagated to destination queues, along with the LCRs in a transaction. A precommit handler receives each commit SCN for these internal commit directives in the queue of an apply process before they are processed by the apply process.

  • Transaction Boundaries for Persistent LCRs Enqueued by Synchronous Captures: When you are using a synchronous capture, and a user commits a transaction, the persistent LCRs that were enqueued by the synchronous capture are organized into a message group. The synchronous capture records the transaction identifier in each persistent LCR in a transaction.

    After persistent LCRs are enqueued by a synchronous capture, the persistent LCRs in the message group can be propagated to other queues. When an apply process is configured to process these persistent LCRs, it generates a commit SCN for all of the persistent LCRs in a message group. The commit SCN values generated by an individual apply process have no relation to the source transaction, or to the values generated by any other apply process. A precommit handler configured for such an apply process receives the commit SCN supplied by the apply process.

  • Transaction Boundaries for Messages Enqueued by Applications: An application can enqueue persistent LCRs and persistent user messages, as well as other types of messages. When the user performing these enqueue operations issues a COMMIT statement to end the transaction, the enqueued persistent LCRs and persistent user messages are organized into a message group.

    When messages that were enqueued by an application are organized into a message group, the messages in the message group can be propagated to other queues. When an apply process is configured to process these messages, it generates a single transaction identifier and commit SCN for all the messages in a message group. Transaction identifiers and commit SCN values generated by an individual apply process have no relation to the source transaction, or to the values generated by any other apply process. A precommit handler configured for such an apply process receives the commit SCN supplied by the apply process.

Considerations for Apply Handlers

The following are considerations for using apply handlers:

  • DML handlers, DDL handlers, and message handlers can execute an LCR by calling the LCR's EXECUTE member procedure.

  • All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

  • If necessary, an apply handler can set an Oracle Streams session tag.

  • An apply handler can call a Java stored procedure that is published (or wrapped) in a PL/SQL procedure.

  • If an apply process tries to invoke an apply handler that does not exist or is invalid, then the apply process aborts.

  • If an apply handler invokes a procedure or function in an Oracle-supplied package, then the user who runs the apply handler must have direct EXECUTE privilege on the package. It is not sufficient to grant this privilege through a role.

See Also:

Summary of Message Processing Options

The table in this section summarizes the message processing options available when you are using one or more of the apply handlers described in the previous sections. Apply handlers are optional for row LCRs and DDL LCRs because an apply process can apply these messages directly. However, a message handler is required for processing user messages. In addition, an apply process dequeues a message only if the message satisfies the rule sets for the apply process. In general, a message satisfies the rule sets for an apply process if no rules in the negative rule set evaluate to TRUE for the message, and at least one rule in the positive rule set evaluates to TRUE for the message.

Table 4-2 Summary of Message Processing Options

Message Processing Option Type of Message Default Apply Process Behavior Scope of User Procedure

Apply Message Directly

Row LCR or DDL LCR

Execute DML or DDL

Not applicable

DML Handler or Error Handler

Row LCR

Execute DML

One operation on one table

DDL Handler

DDL LCR

Execute DDL

Entire apply process

Message Handler

User Message

Create error transaction (if no message handler exists)

Entire apply process

Precommit Handler

Commit directive for transactions that include row LCRs or user messages

Commit transaction

Entire apply process


In addition to the message processing options described in this section, you can use the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package to instruct an apply process to enqueue messages into the persistent queue portion of a specified destination queue. Also, you can control message execution using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package.

The Source of Messages Applied by an Apply Process

The following list describes the 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. Store these LCRs in separate ANYDATA queues, and use a separate apply process to apply the LCRs from each synchronous capture.

Note:

Captured LCRs are in the buffered queue portion of a queue while persistent LCRs are in the persistent queue portion of a queue. Therefore, a single apply process cannot apply both captured LCRs and persistent LCRs.

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

Note:

Oracle Streams capture processes can only capture changes to XMLType columns that are stored as CLOBs. However, apply processes can apply these captured LCRs to XMLType columns that are stored as CLOBs, object relationally, or as binary XML.

Automatic Data Type Conversion During Apply

During apply, an apply process automatically converts certain data types when there is a mismatch between the data type of a column in the row logical change record (row LCR) and the data type of the corresponding column in a table.

Table 4-3 shows which data type combinations are converted automatically during apply.

Table 4-3 Data Type Combinations Converted Automatically During Apply

Data Types To CHAR To NCHAR To VARCHAR2 To NVARCHAR2 To CLOB To BLOB To DATE To TIMESTAMP

From CHAR

Not Applicable

Yes

Yes

Yes

Yes

No

No

No

From NCHAR

Yes

Not Applicable

Yes

Yes

Yes

No

No

No

From VARCHAR2

Yes

Yes

Not Applicable

Yes

Yes

No

No

No

From NVARCHAR2

Yes

Yes

Yes

Not Applicable

Yes

No

No

No

From NUMBER

Yes

Yes

Yes

Yes

No

No

No

No

From LONG

No

No

No

No

Yes

No

No

No

From LONG RAW

No

No

No

No

No

Yes

Yes

No

From RAW

No

No

No

No

No

Yes

Yes

No

From DATE

No

No

No

No

No

No

Not Applicable

Yes

From TIMESTAMP

No

No

No

No

No

No

Yes

Not Applicable


An apply process automatically performs data type conversion for a data type combination when Table 4-3 specifies "Yes" for the combination. An apply process does not perform data type conversion for a data type combination when Table 4-3 specifies "No" for the combination. For example, an apply process automatically converts a CHAR to an NCHAR, but it does not convert a CHAR to a BLOB.

Also, if the corresponding table column is not large enough to hold the converted string from a row LCR column, then the apply process raises an error.

The following sections provide more information about automatic data type conversion during apply:

Note:

An apply process must be part of Oracle Database 11g Release 1 (11.1.0.7) or later to perform automatic data type conversion. However, an apply process can convert columns in row LCRs that were captured or constructed on an earlier Oracle Database release.

See Also:

Oracle Database SQL Language Reference for more information about data types

Automatic Trimming of Character Data Types During Apply

The rtrim_on_implicit_conversion apply process parameter determines whether the apply process trims data when it converts a CHAR or NCHAR to a VARCHAR2, NVARCHAR2, or CLOB. When this parameter is set to Y, the apply process automatically removes blank padding from the right end of a column during data type conversion. When this parameter is set to N, the apply process preserves blank padding during data type conversion.

Consider the following example:

  • A row LCR contains 'abc' for a CHAR(10) column.

  • The corresponding table column for the row LCR is NVARCHAR2(10).

If the rtrim_on_implicit_conversion apply process parameter is set to Y, the apply process inserts 'abc' into the table column and trims the padding after these characters. If the rtrim_on_implicit_conversion apply process parameter is set to N, then the apply process inserts 'abc' into the table column, and the remaining space in the column is filled with blanks.

Automatic Conversion and LOB Data Types

DML handlers and error handlers can use LOB assembly for data that has been converted from LONG to CLOB or from LONG RAW to BLOB.

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.

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 logical change records (LCRs) and assembles messages into transactions. The reader server then returns the assembled transactions to the coordinator process.

  • 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 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:

See Also:

Reader Server States

The state of a reader server describes what the reader server is doing currently. You can view the state of the reader server for an apply process by querying the V$STREAMS_APPLY_READER dynamic performance view. The following reader server states are possible:

  • INITIALIZING - Starting up

  • IDLE - Performing no work

  • DEQUEUE MESSAGES - Dequeuing messages from the apply process queue

  • SCHEDULE MESSAGES - Computing dependencies between messages and assembling messages into transactions

  • SPILLING - Spilling unapplied messages from memory to hard disk

  • PAUSED - Waiting for a DDL LCR to be applied

See Also:

Coordinator Process States

The state of a coordinator process describes what the coordinator process is doing currently. You can view the state of a coordinator process by querying the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The following coordinator process states are possible:

  • INITIALIZING - Starting up

  • APPLYING - Passing transactions to apply servers

  • SHUTTING DOWN CLEANLY - Stopping without an error

  • ABORTING - Stopping because of an apply error

See Also:

Apply Server States

The state of an apply server describes what the apply server is doing currently. You can view the state of each apply server for an apply process by querying the V$STREAMS_APPLY_SERVER dynamic performance view. The following apply server states are possible:

  • INITIALIZING - Starting up.

  • IDLE - Performing no work.

  • RECORD LOW-WATERMARK - Performing an administrative action that maintains information about the apply progress, which is used in the ALL_APPLY_PROGRESS and DBA_APPLY_PROGRESS data dictionary views.

  • ADD PARTITION - Performing an administrative action that adds a partition that is used for recording information about in-progress transactions.

  • DROP PARTITION - Performing an administrative action that drops a partition that was used to record information about in-progress transactions.

  • EXECUTE TRANSACTION - Applying a transaction.

  • WAIT COMMIT - Waiting to commit a transaction until all other transactions with a lower commit SCN are applied. This state is possible only if the COMMIT_SERIALIZATION apply process parameter is set to a value other than none and the PARALELLISM apply process parameter is set to a value greater than 1.

  • WAIT DEPENDENCY - Waiting to apply an LCR in a transaction until another transaction, on which it has a dependency, is applied. This state is possible only if the PARALELLISM apply process parameter is set to a value greater than 1.

  • WAIT FOR NEXT CHUNK - Waiting for the next set of LCRs for a large transaction.

  • TRANSACTION CLEANUP - Cleaning up an applied transaction, which includes removing LCRs from the apply process queue.

See Also:

Apply User

An apply process applies messages in the security domain of its apply user. 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 the following privileges:

  • 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

  • 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.

See Also:

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. 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-4 describes the differences between using the DBMS_STREAMS_ADM package and the DBMS_APPLY_ADM package for apply process creation.

Table 4-4 DBMS_STREAMS_ADM and DBMS_APPLY_ADM Apply Process Creation

DBMS_STREAMS_ADM Package DBMS_APPLY_ADM Package

A rule set is created automatically for the apply process and rules can be added to the rule set automatically. The rule set is a positive rule set if the inclusion_rule parameter is set to TRUE (the default). It is a negative rule set if the inclusion_rule parameter is set to FALSE. You can use the procedures in the DBMS_STREAMS_ADM and DBMS_RULE_ADM package to manage rule sets and rules for the apply process after the apply process is created.

You create one or more rule sets and rules for the apply process either before or after it is created. You can use the procedures in the DBMS_RULE_ADM package to create rule sets and add rules to rule sets either before or after the apply process is created. You can use the procedures in the DBMS_STREAMS_ADM package to create rule sets and add rules to rule sets for the apply process after the apply process is created.

The apply process can apply messages only at the local database.

You specify whether the apply process applies messages at the local database or at a remote database during apply process creation.

Changes applied by the apply process generate tags in the redo log at the destination database with a value of 00 (double zero).

You specify the tag value for changes applied by the apply process during apply process creation. The default value for the tag is 00 (double zero).


See Also:

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 parallelism apply process parameter specifies the number of apply servers that can concurrently apply transactions, and 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.

See Also:

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). 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).

Note:

The size of the Oracle Streams pool is managed automatically if the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value.

See Also:

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.

Note:

If a messaging client encounters an error when it is dequeuing messages, then the messaging client moves these messages to the exception queue associated with the its queue table. However, information about messaging client errors is not stored in the error queue. Only information about apply process errors is stored in the error queue.

Explicit Consumption with a Messaging Client

A messaging client dequeues messages from its persistent queue when it is invoked by an application or a user. You use rules to specify which messages in the queue are dequeued by a messaging client. These messages can be persistent LCRs or persistent user messages.

You can create a messaging client by specifying dequeue for the streams_type parameter when you run one of the following procedures in the DBMS_STREAMS_ADM package:

When you create a messaging client, you specify the name of the messaging client and the ANYDATA queue from which the messaging client dequeues messages. These procedures can also add rules to the positive rule set or negative rule set of a messaging client. You specify the message type for each rule, and a single messaging client can dequeue messages of different types.

The user who creates a messaging client is granted the privileges to dequeue from the queue using the messaging client. This user is the messaging client user. The messaging client user can dequeue messages that satisfy the messaging client rule sets. A messaging client can be associated with only one user, but one user can be associated with many messaging clients.

Figure 4-2 shows a messaging client dequeuing messages.

Figure 4-2 Messaging Client

Description of Figure 4-2 follows
Description of "Figure 4-2 Messaging Client"

Explicit Consumption with Manual Dequeue

With explicit consumption with manual dequeue, an application explicitly dequeues buffered LCRs, persistent LCRs, buffered user messages, or persistent user messages manually and processes them. The queue from which the messages are dequeued can be an ANYDATA queue or a typed queue. You can use either the DBMS_STREAMS_MESSAGING package or the DBMS_AQ package to dequeue messages.

The dequeue features available with Oracle Streams Advanced Queuing include the following:

See Also: