4
Streams Apply Process

This chapter explains the concepts and architecture of the Streams apply process.

This chapter contains these topics:

• Introduction to the Apply Process
• Apply Process Rules
• Event Processing with an Apply Process
• Datatypes Applied
• Streams Apply Processes and RESTRICTED SESSION
• Streams Apply Processes and Oracle Real Application Clusters
• Apply Process Architecture

  See Also: Chapter 11, "Managing an Apply Process"

Introduction to the Apply Process

An apply process is an optional Oracle background process that dequeues logical change records (LCRs) and user messages from a specific queue and either applies each one directly or passes it as a parameter to a user-defined procedure. The LCRs dequeued by an apply process contain the results of data manipulation language (DML) changes or data definition language (DDL) changes that an apply process can apply to database objects in a destination database. A user-enqueued message dequeued by an apply process is of type SYS.AnyData and can contain any user message, including a user-created LCR.

Note: An apply process can be associated only with a SYS.AnyData queue, not with a typed queue.

Apply Process Rules

An apply process applies changes based on rules that you define. Each rule specifies the database objects and types of changes for which the rule evaluates to TRUE. You can place these rules in a positive rule set for the apply process or a negative rule set for the apply process.

If a rule evaluates to TRUE for a change, and the rule is in the positive rule set for an apply process, then the apply process applies the change. If a rule evaluates to TRUE for a change, and the rule is in the negative rule set for an apply process, then the apply process discards the change. 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 LCR events 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.

For non-LCR events, you can create your own rules to control apply process behavior.

See Also: Chapter 5, "Rules" Chapter 6, "How Rules Are Used In Streams"

Event Processing with an Apply Process

An apply process is a flexible mechanism for processing the events in a queue. You have options to consider when you configure one or more apply processes for your environment. This section discusses the types of events that an apply process can apply and the ways that it can apply them.

Processing Captured and User-Enqueued Events with an Apply Process

A single apply process can apply either captured events or user-enqueued events, but not both. If a queue at a destination database contains both captured and user-enqueued events, then the destination database must have at least two apply processes to process the events.

When you create an apply process using one of the following procedures in the DBMS_STREAMS_ADM package, the apply process applies only captured events:

• ADD_SUBSET_RULES
• ADD_TABLE_RULES
• ADD_SCHEMA_RULES
• ADD_GLOBAL_RULES

When you create an apply process using the ADD_MESSAGE_RULE procedure in the DBMS_STREAMS_ADM package, the apply process applies only user-enqueued events.

When you create an apply process using the CREATE_APPLY procedure in the DBMS_APPLY_ADM package, you use the apply_captured parameter to specify whether the apply process applies captured or user-enqueued events. By default, the apply_captured parameter is set to false for an apply process created with this procedure. Therefore, by default, an apply process created with the CREATE_APPLY procedure in the DBMS_APPLY_ADM package applies user-enqueued events.

See Also: "Introduction to Event Staging and Propagation" for more information about captured and user-enqueued events PL/SQL Packages and Types Reference for more information about the CREATE_APPLY procedure

Event Processing Options with an Apply Process

Your options for event processing depend on whether or not the event received by an apply process is an LCR event. Figure 4-1 shows the event processing options for an apply process.

Figure 4-1 The Apply Process

Text description of the illustration strms013.gif

LCR Event Processing

An apply process either can apply captured LCRs or user-enqueued LCRs, but not both. Regarding captured LCRs, an apply process can apply captured LCRs from only one source database, because processing these LCRs requires knowledge of the dependencies, meaningful transaction ordering, and transactional boundaries at the source database. For a captured LCR, the source database is the database where the change encapsulated in the LCR was generated in the redo log.

Captured LCRs from multiple databases may be sent to a single destination queue. However, if a single queue contains captured 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 captured LCRs from exactly one source database using rules. Regarding user-enqueued events containing LCRs (not captured events), a single apply process can apply these user-enqueued events, even if they are from multiple source databases.

Also, each apply process can apply captured LCRs from only one capture process. If there are multiple capture processes 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 Corporation recommends that each SYS.AnyData queue used by a capture process 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.

You can configure an apply process to process a captured or user-enqueued event that contains an LCR in the following ways: directly apply the LCR event or pass the LCR event as a parameter to a user procedure for processing. The following sections explain these options.

Apply the LCR Event Directly

If you use this option, then an apply process applies the LCR event without running a user procedure. The apply process either successfully applies the change in the LCR to a database object 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 may 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.

Call a User Procedure to Process the LCR Event

If you use this option, then an apply process passes the LCR event as a parameter to a user procedure for processing. The user procedure can process the LCR event in a customized way.

A user procedure that processes row LCRs resulting from DML statements is called a DML handler, while a user procedure that processes DDL LCRs resulting from DDL statements is called a DDL handler. An apply process can have many DML handlers but only one DDL handler, which processes all DDL LCRs dequeued by the apply process.

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 may have one DML handler to process INSERT operations and a different DML handler to process UPDATE operations.

A user procedure can be used for any customized processing of 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. Or, if you want to log DDL changes before applying them, then you can create a user procedure that processes DDL operations to accomplish this.

A DML handler should never commit and never roll back, except to a named save point 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 execute a DDL LCR inside a DDL handler, invoke the EXECUTE member procedure for the DDL LCR.

To set a DML handler, use the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. You either may set a DML handler for a specific apply process, or you may 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.

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.

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 with the specified operation on the specified table.

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

Attention: Do not modify LONG, LONG RAW or LOB column data in an LCR. This includes DML handlers, error handlers, and rule-based transformation functions.

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.

See Also: "Logical Change Records (LCRs)" for more information about row LCRs and DDL LCRs PL/SQL Packages and Types Reference for more information about the EXECUTE member procedure for LCR types "Rule-Based Transformations" Oracle Streams Replication Administrator's Guide for more information about DML handlers and DDL handlers

Non-LCR User Message Processing

A user-enqueued event that does not contain an LCR is processed by the message handler specified for an apply process. A message handler is a user-defined procedure that can process non-LCR user messages in a customized way for your environment.

The message handler offers advantages in any environment that has applications that need to 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 Streams can propagate each user message to the appropriate queues at destination databases. If there are multiple destinations, then Streams provides the infrastructure for automatic propagation and processing of these messages at these destinations. If there is only one destination, then 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 may format a user message into an electronic mail message. In this case, the user message may contain the attributes you would expect in an electronic mail message, such as from, to, subject, text_of_message, and so on. A message handler could convert these user messages into electronic mail messages and send them 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. A Streams apply process always assumes that a non-LCR message has no dependencies on any other events in the queue. If parallelism is greater than 1 for an apply process that applies user-enqueued messages, then these messages may be dequeued by a message handler in any order. Therefore, if dependencies exist between these messages in your environment, then Oracle Corporation recommends that you set apply process parallelism to 1.

See Also: "Managing the Message Handler for an Apply Process"

Audit Commit Information for Events Using Precommit Handlers

You can use a precommit handler to audit commit directives for captured events and transaction boundaries for user-enqueued events. 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 may work with a DML handler or a message handler.

For example, a handler may improve performance by caching data for the length of a transaction. This data may include cursors, temporary LOBs, data from an event, 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.

Commit Directives for Captured Events

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. Once enqueued into a queue, these commit directives may be propagated to destination queues, along with the LCRs in a transaction. A precommit handler receives the 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 User-Enqueued Events

A user or application may enqueue messages into a queue and then issue a COMMIT statement to end the transaction. The enqueued messages are organized into a message group. Once enqueued into a queue, the messages in a message group may be propagated to other queues. When an apply process is configured to process user-enqueued 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, nor 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.

See Also: "Managing the Precommit Handler for an Apply Process"

Summary of Event Processing Options

Table 4-1 summarizes the event 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 events directly. However, a message handler is required for processing non-LCR user messages. In addition, an apply process dequeues an event only if the event satisfies the rule sets for the apply process. In general, an event satisfies the rule sets for an apply process if no rules in the negative rule set evaluate to TRUE for the event, and at least one rule in the positive rule set evaluates to TRUE for the event.

Table 4-1 Summary of Event Processing Options

Apply Handler	Type of Event	Default Apply Process Behavior	Scope of User Procedure
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	Non-LCR User Message	Create error transaction (if no message handler exists)	Entire apply process
Precommit Handler	Commit directive for transactions that include row LCRs or non-LCR user messages	Commit transaction	Entire apply process

In addition to the event 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 events into a specified destination queue. Also, you can control event execution using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package.

See Also: Chapter 6, "How Rules Are Used In Streams" for more information about rule sets for Streams clients and for information about how events satisfy rule sets "Specifying Event Enqueues by Apply Processes" "Specifying Execute Directives for Apply Processes"

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 a Streams session tag.
• An apply handler may 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: PL/SQL Packages and Types Reference for more information about the EXECUTE member procedure for LCR types Oracle Streams Replication Administrator's Guide for more information about Streams tags

Datatypes Applied

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

• VARCHAR2
• NVARCHAR2
• NUMBER
• 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
• NCLOB
• BLOB
• UROWID

The apply process does not apply row LCRs containing the results of DML changes in columns of the following datatypes: BFILE, ROWID, and user-defined type (including object types, REFs, varrays, nested tables, and Oracle-supplied types). The apply process raises an error if it attempts to apply a row LCR that contains information about a column of an unsupported datatype. Next, the apply process moves the transaction that includes the LCR into the error queue.

See Also: "Datatypes Captured" Oracle Database SQL Reference for more information about these datatypes

Streams Apply Processes and RESTRICTED SESSION

When the 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 the restricted session is enabled in a running database by the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause, it does not affect any running apply processes. These apply processes continue to run and apply events. If a stopped apply process is started in a restricted session, then the apply process does not start until the restricted session is disabled.

Streams Apply Processes and Oracle Real Application Clusters

You can configure a Streams apply process to apply changes in a Real Application Clusters (RAC) environment. Each apply process is started on the owner instance for its SYS.AnyData queue, even if the start 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 a RAC environment, an apply coordinator process, its corresponding apply reader server, and all of its apply server processes run on a single instance.

See Also: "SYS.AnyData Queues and Oracle Real Application Clusters" for information about primary and secondary instance ownership for queues "Streams Capture Processes and Oracle Real Application Clusters" Oracle Database Reference for more information about the DBA_QUEUE_TABLES data dictionary view "Persistent Apply Process Status Upon Database Restart"

Apply Process Architecture

You can create, alter, start, stop, and drop an apply process, and you can define apply process rules that control which events an apply process dequeues from its queue. Events applied by an apply process are applied by an apply user. The apply user applies all changes that satisfy the apply process rule sets. In addition, the apply user runs all 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 rule-based transformation functions specified for rules in the positive rule set, execute privilege on any apply handlers, and privileges to dequeue events from the apply process queue. An apply process can be associated with only one user, but one user may be associated with many apply processes.

See Also: "Configuring a Streams Administrator" for information about the required privileges

This section discusses the following topics:

• Apply Process Components
• Apply Process Creation
• Streams Data Dictionary for an Apply Process
• Apply Process Parameters
• Persistent Apply Process Status Upon Database Restart
• The Error Queue

Apply Process Components

An apply process consists of the following components:

• A reader server that dequeues events. The reader server is a parallel execution server that computes dependencies between LCRs and assembles events 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 annn, where nnn is a coordinator process number. Valid coordinator process names include a001 through a999. 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 handlers. For non-LCR messages, the apply servers pass the events to the message handler. Each apply server is a parallel execution server. 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 events, 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.

If a transaction being handled by an apply server has a dependency with 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.

For example, consider these two transactions:

1. A row is inserted into a table.
2. The same row is updated to change certain column values.

In this case, transaction 2 is dependent on transaction 1, because the row cannot be updated until after it is inserted into the table. Suppose these transactions are captured from the redo log at a source database, propagated to a destination database, and applied at the destination database. Apply server A handles the insert transaction, and apply server B handles the update transaction.

If apply server B is ready to apply the update transaction before apply server A has applied the insert transaction, then apply server B waits for instructions from the coordinator process. After apply server A has applied the insert transaction, the coordinator process instructs apply server B to apply the update transaction.

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:

• IDLE - Performing no work
• DEQUEUE MESSAGES - Dequeuing events from the apply process queue
• SCHEDULE MESSAGES - Computing dependencies between events and assembling events into transactions

  See Also: "Displaying Information About the Reader Server for Each Apply Process" for a query that displays the state of an apply process reader server

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: "Displaying General Information About Each Coordinator Process" for a query that displays the state of a coordinator process

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:

• 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: "Displaying Information About the Apply Servers for Each Apply Process" for a query that displays the state of each apply process apply server

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.

In addition, when you use the DBMS_STREAMS_ADM package, a rule set is created for the apply process and rules may 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), or it is a negative rule set if the inclusion_rule parameter is set to false. Alternatively, using the DBMS_APPLY_ADM package to create an apply process is more flexible, and you create one or more rule sets and rules for the apply process either before or after it is created.

An apply process created by the procedures in the DBMS_STREAMS_ADM package can apply events only at the local database. To create an apply process that applies events at a remote database, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

Changes applied by an apply process created by the DBMS_STREAMS_ADM package generate tags in the redo log at the destination database with a value of 00 (double zero), but you can set the tag value if you use the CREATE_APPLY procedure. Alternatively, you can set the tag using the ALTER_APPLY procedure in the DBMS_APPLY_ADM package.

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.

See Also: PL/SQL Packages and Types Reference for more information about the following procedures, which can be used to create an apply process. DBMS_STREAMS_ADM.ADD_TABLE_RULES DBMS_STREAMS_ADM.ADD_SUBSET_RULES DBMS_STREAMS_ADM.ADD_SCHEMA_RULES DBMS_STREAMS_ADM.ADD_GLOBAL_RULES DBMS_STREAMS_ADM.ADD_MESSAGE_RULE DBMS_CAPTURE_ADM.CREATE_APPLY Also, see Oracle Streams Replication Administrator's Guide for more information about Streams tags.

Streams Data Dictionary for an Apply Process

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

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

See Also: "The Streams Data Dictionary" "Streams Data Dictionary for Propagations"

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 can be used to specify 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: "Setting an Apply Process Parameter" This section does not discuss all of the available apply process parameters. See the DBMS_APPLY_ADM.SET_PARAMETER procedure in the PL/SQL Packages and Types Reference for detailed information about all of the apply process parameters.

Apply Process Parallelism

The parallelism apply process parameter specifies the number of apply servers that may concurrently apply transactions. For example, if parallelism is set to 5, then an apply process uses a total of five apply servers. In addition, the reader server is a parallel execution server. So, if parallelism is set to 5, then an apply process uses a total of six parallel execution servers, assuming six parallel execution servers are available in the database. An apply process always uses two or more parallel execution servers.

Note: Resetting the parallelism parameter automatically stops and restarts the apply process when the currently executing transactions are applied, which may take some time depending on the size of the transactions. Setting the parallelism parameter to a number higher than the number of available parallel execution servers may disable the apply process. Make sure the PROCESSES and PARALLEL_MAX_SERVERS initialization parameters are set appropriately when you set the parallelism apply process parameter.

See Also: "Apply Process Components" for more information about apply servers and the reader server Oracle Database Administrator's Guide for information about managing parallel execution servers

Commit Serialization

Apply servers may apply transactions at the destination database in an order that is different from the commit order at the source database. Only nondependent transactions can be applied in a different order than the commit order at the source database. Dependent transactions are always applied at the destination database in the same order as they were committed at the source database.

You control whether the apply servers can apply nondependent transactions in a different order at the destination database using the commit_serialization apply parameter. This parameter has the following settings:

• full: An apply process commits applied transactions in the order in which they were committed at the source database. This setting is the default.
• none: An apply process may commit non-dependent transactions in any order. Performance is best if you specify this value.

If you specify none, then it is possible that a destination database may commit changes in a different order than the source database. For example, suppose two nondependent transactions are committed at the source database in the following order:

1. Transaction A
2. Transaction B

At the destination database, these transactions may be committed in the opposite order:

1. Transaction B
2. Transaction A

Automatic Restart of an Apply Process

You can configure an apply process to stop automatically when it reaches certain predefined limits. The time_limit apply process parameter specifies the amount of time an apply process runs, and the transaction_limit apply process parameter specifies the number of transactions an apply process can apply. The apply process stops automatically when it reaches these limits.

The disable_on_limit parameter controls whether an apply process becomes disabled or restarts when it reaches a limit. If you set the disable_on_limit parameter to y, then the apply process is disabled when it reaches a limit and does not restart until you restart it explicitly. If, however, you set the disable_on_limit parameter to n, then the apply process stops and restarts automatically when it reaches a limit.

When an apply process is restarted, it gets a new session identifier, and the parallel execution servers associated with the apply process also get new session identifiers. However, the coordinator process number (annn) remains the same.

Stop or Continue on Error

Using the disable_on_error apply process parameter, you either can instruct an apply process to become disabled when it encounters an error, or you can allow the apply process to continue applying transactions after it encounters an error.

See Also: "The Error Queue"

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.

The error queue stores information about transactions that could not be applied successfully by the apply processes running in a database. A transaction may include many events, and when an unhandled error occurs during apply, an apply process automatically moves all of the events 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 error transaction. 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 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 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 may modify the row LCR in some way to avoid a repeat of the same error. The row LCR is passed to the DML handler when you reexecute the error containing the row LCR using the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package.

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 a Streams environment.

The error queue uses the exception queues in the database. When you create a SYS.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 may use a single queue table, and each queue table has one exception queue. Therefore, a single exception queue may store errors for multiple queues and multiple apply processes.

An exception queue only contains the apply errors for its queue table, but the 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 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.

See Also: "Managing Apply Errors" "Checking for Apply Errors" "Displaying Detailed Information About Apply Errors" "Managing an Error Handler" Chapter 6, "How Rules Are Used In Streams" for more information about rule sets for Streams clients and for information about how events satisfy rule sets PL/SQL Packages and Types Reference for more information on the DBMS_APPLY_ADM package Oracle Database Reference for more information about the DBA_APPLY_ERROR data dictionary view