| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
This chapter explains the concepts and architecture of the Streams apply process.
This chapter contains these topics:
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 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-defined message dequeued by an apply process is of type SYS.AnyData and can contain any user message, including a user-created LCR.
Events applied by an apply process are applied by an apply user. The apply user is the user who applies all DML statements and DDL statements and who runs user-defined apply handlers.
An apply process applies changes based on rules that you define. Each rule specifies the database objects to which an apply process applies changes and the types of changes to apply. You can specify apply rules at the following levels:
For non-LCR events, you can create your own rules to control apply process behavior.
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.
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 a procedure in the DBMS_STREAMS_ADM package, the apply process applies only captured 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.
The database where an event originated is important to an apply process for captured events but not for user-enqueued events. For a captured event, the source database is the database where the change was generated in the redo log. An apply process must determine the source database for each captured LCR to ensure that it applies changes from only one source database, and a database administrator must ensure that an apply process applies changes from only one capture process at that source database. For a user-enqueued event, an apply process ignores information about the database where the event originated, even if the event is a user-enqueued LCR. A single apply process can apply user-enqueued events that originated at different databases.
See Also:
|
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.
Each apply process can apply captured events from only one source database, because processing the LCRs in these events requires knowledge of the dependencies, meaningful transaction ordering, and transactional boundaries at the source database. Captured LCRs from multiple databases may be sent to a single destination queue. However, if a single queue contains captured LCRs from multiple 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 events 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 Streams queue used by a capture process or apply process have captured events 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 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.
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 error handler cannot resolve the error, then the apply process places the transaction, and all LCRs associated with the transaction, into an exception queue.
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 then 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:
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 savepoint that the user procedure has established. To execute DDL inside a DDL handler, invoke the EXECUTE member procedure for the LCR.
To set a DML handler, use the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package, and this setting is used by all apply processes in the database. 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. Then, the 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.
See Also:
|
A user-enqueued event that does not contain an LCR is processed by the message handler specified for an apply process, if the user-enqueued event satisfies at least one rule in the rule set for the 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. Therefore, if dependencies exist between these messages in your environment, then Oracle Corporation recommends that you set apply process parallelism to 1.
Table 4-1 summarizes the event processing options available when you are using one or more of the event handlers described in the previous sections. Event 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 at least one of the rules in the rule set for the apply process.
See Also:
|
When applying row LCRs for data manipulation language (DML) changes to tables, an apply process applies changes made to columns of the following datatypes:
CHARVARCHAR2NCHARNVARCHAR2NUMBERDATECLOBBLOBRAWTIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTHINTERVAL DAY TO SECONDThe apply process does not apply DML changes in columns of the following datatypes: NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID, and user-defined type (including object types, REFs, varrays, and nested tables). The apply process raises an error if it attempts to apply a row LCR that contains information about a column of an unsupported datatype. Then, the apply process moves the transaction that includes the LCR into an exception queue.
See Also:
|
The following sections discuss considerations for applying DML changes to tables:
You must ensure that the primary key columns at the destination database are logged in the redo log at the source database for every update. A unique or foreign key constraint at a destination database that contains data from more that one column at the source database requires additional logging at the source database.
There are various ways to ensure that a column is logged at the source database. For example, whenever the value of a column is updated, the column is logged. Also, Oracle has a feature called supplemental logging that automates the logging of specified columns.
For a unique key and foreign key constraint at a destination database that contains data from only one column at a source database, no supplemental logging is required. However, for a constraint that contains data from multiple columns at the source database, you must create a conditional supplemental log group containing all the columns at the source database that are used by the constraint at the destination database.
Typically, unique key and foreign key constraints include the same columns at the source database and destination database. However, in some cases, an apply handler or rule-based transformation may combine a multi-column constraint from the source database into a single key column at the destination database. Also, an apply handler or rule-based transformation may separate a single key column from the source database into a multi-column constraint at the destination database. In such cases, the number of columns in the constraint at the source database determines whether a conditional supplemental log group is required. If there is more than one column in the constraint at the source database, then a conditional supplemental log group containing all the constraint columns is required at the source database. If there is only one column in the constraint at the source database, then no supplemental logging is required for the key column.
If possible, each table for which changes are applied by an apply process should have a primary key. When a primary key is not possible, Oracle Corporation recommends that each table have a set of columns that can be used as a unique identifier for each row of the table. If the tables that you plan to use in your Streams environment do not have a primary key or a set of unique columns, then consider altering these tables accordingly.
To detect conflicts and handle errors accurately, Oracle must be able to identify uniquely and match corresponding rows at different databases. By default, Streams uses the primary key of a table to identify rows in the table. When a table at a destination database does not have a primary key, or when you want to use columns other than the primary key for the key, you can designate a substitute key at the destination database. A substitute key is a column or set of columns that Oracle can use to identify rows in the table during apply.
You can specify the substitute primary key for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. Unlike true primary keys, the substitute key columns may contain NULLs. Also, the substitute key columns take precedence over any existing primary key for the specified table for all apply processes at the destination database.
If you specify a substitute key for a table in a destination database, and these columns are not a primary key for the same table at the source database, then you must create an unconditional supplemental log group containing the substitute key columns at the source database.
In the absence of both substitute key columns and a primary key constraint, an apply process uses all of the columns in the table as the key columns, excluding LOB and LONG columns. In this case, you must create an unconditional supplemental log group containing these columns at the source database. Using substitute key columns is preferable when there is no primary key constraint for a table because fewer columns are needed in the row LCR.
See Also:
|
You can use the ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package to maintain a subset of the rows in a particular table. To do this, you specify a condition similar to the condition in the WHERE clause of a SELECT statement in the dml_condition parameter for this procedure. For a particular table, only one subset rule is allowed for a particular apply process.
When you use subset rules, a captured update operation may be converted to an insert or delete operation when it is applied by an apply process. This automatic conversion is called row migration and is performed by an internal LCR transformation specified in a rule's action context.
For example, suppose you use a subset rule to specify that an apply process applies changes only to the hr.employees table when the employee's department_id is 50 using the following subset condition: department_id = 50. Assume that the table at the destination database is a subset table that only contains records for employees whose department_id is 50. If a source database captures a change to an employee that changes the employee's department_id from 80 to 50, then the apply process with the subset rule at a destination database applies this change by converting the update operation into an insert operation. This conversion is needed because the employee's row does not exist in the destination table.
Similarly, if a captured update changes an employee's department_id from 50 to 20, then an apply process with this subset rule converts the update operation into a delete operation.
If an apply process may perform row migration when applying changes to a table and you allow local changes to the table, then the apply process cannot ensure that all rows in the table meet the subset condition. For example, suppose the condition is department_id = 50 for the hr.employees table. If a user or an application inserts a row for an employee whose department_id is 30, then this row remains in the table and is not removed by the apply process. Similarly, if a user or an application updates a row locally and changes the department_id to 30, then this row also remains in the table. To avoid such errors, Oracle Corporation recommends that you ensure that all DML performed on a subset table satisfy the subset condition.
If you specify a subset rule for a table at a destination database, then an unconditional supplemental log group must be specified at the source database for all of the columns in the table at the destination database and all the columns in the subset condition. In certain cases, when a subset rule is specified, an update may be converted to an insert by an apply process, and in these cases supplemental information may be needed for some or all of the columns.
For example, if you specify a subset rule at database dbs2.net on the postal_code column in the hr.locations table, and the source database for changes to this table is dbs1.net, then specify supplemental logging at dbs1.net for all of the columns that exist in the hr.locations table at dbs2.net, as well as the postal_code column, even if this column does not exist in the table at the destination database.
A column discrepancy is any difference in the columns in a table at a source database and the columns in the same table at a destination database. If there are column discrepancies in your Streams environment, then use rule-based transformations or DML handlers to make the columns in row LCRs being applied by an apply process match the columns in the relevant tables at a destination database. The following sections describe apply process behavior for common column discrepancies.
See Also:
|
If the table at the destination database is missing one or more columns that are in the table at the source database, then an apply process raises an error and moves the transaction that caused the error into an exception queue. You can avoid such an error by creating a rule-based transformation or DML handler that eliminates the missing columns from the LCRs before they are applied. Specifically, the transformation or handler can remove the extra columns using the DELETE_COLUMN member procedure on the row LCR.
If the table at the destination database has more columns than the table at the source database, then apply process behavior depends on whether the extra columns are required for dependency computations. If the extra columns are not used for dependency computations, then an apply process applies changes to the destination table. In this case, if column defaults exist for the extra columns at the destination database, then these defaults are used for these columns for all inserts. Otherwise, these inserted columns are NULL.
If, however, the extra columns are used for dependency computations, then an apply process places the transactions that include these changes in an exception queue. The following types of columns are required for dependency computations:
INSERT and DELETE statements, all columns involved with constraintsUPDATE statements, if a constraint column is changed, such as a unique key constraint column or a foreign key constraint column, then all columns involved in the constraintIf the datatype for a column in a table at the destination database does not match the datatype for the same column at the source database, then an apply process places transactions containing the changes to the mismatched column into an exception queue. To avoid such an error, you can create a rule-based transformation or DML handler that converts the datatype.
Conflicts are possible in a Streams configuration where data is shared between multiple databases. A conflict can occur if DML changes are allowed to a table for which changes are captured and to a table where these changes are applied.
For example, a transaction at the source database may update a row at nearly the same time as a different transaction that updates the same row at a destination database. In this case, if data consistency between the two databases is important, then when the change is propagated to the destination database, an apply process must be instructed either to keep the change at the destination database or replace it with the change from the source database. When data conflicts occur, you need a mechanism to ensure that the conflict is resolved in accordance with your business rules.
Streams automatically detects conflicts and, for update conflicts, tries to use an update conflict handler to resolve them if one is configured. Streams offers a variety of prebuilt handlers that enable you to define a conflict resolution system for your database that resolves conflicts in accordance with your business rules. If you have a unique situation that a prebuilt conflict resolution handlers cannot resolve, then you can build and use your own custom conflict resolution handlers in an error handler or DML handler.
Any of the following handlers may process a row LCR:
The following sections describe the possible scenarios involving these handlers:
You cannot have a DML handler and an error handler simultaneously for the same operation on the same table. Therefore, there is no scenario in which they could both be invoked.
If there are no relevant handlers for a row LCR, then an apply process tries to apply the change specified in the row LCR directly. If the apply process can apply the row LCR, then the change is made to the row in the table. If there is a conflict or an error during apply, then the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
Consider a case where there is a relevant update conflict handler configured, but no other relevant handlers are configured. An apply process tries to apply the change specified in a row LCR directly. If the apply process can apply the row LCR, then the change is made to the row in the table.
If there is an error during apply that is caused by a condition other than an update conflict, including a uniqueness conflict or a delete conflict, then the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
If there is an update conflict during apply, then the relevant update conflict handler is invoked. If the update conflict handler resolves the conflict successfully, then the apply process either applies the LCR or discards the LCR, depending on the resolution of the update conflict, and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set. If the update conflict handler cannot resolve the conflict, then the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
Consider a case where an apply process passes a row LCR to a DML handler and there is no relevant update conflict handler configured.
The DML handler processes the row LCR. The designer of the DML handler has complete control over this processing. Some DML handlers may perform SQL operations or run the EXECUTE member procedure of the row LCR. If the DML handler runs the EXECUTE member procedure of the row LCR, then the apply process tries to apply the row LCR. This row LCR may have been modified by the DML handler.
If any SQL operation performed by the DML handler fails, or if an attempt to run the EXECUTE member procedure fails, then the DML handler can try to handle the exception. If the DML handler does not raise an exception, then the apply process assumes the DML handler has performed the appropriate action with the row LCR, and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set.
If the DML handler cannot handle the exception, then the DML handler should raise an exception. In this case, the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
Consider a case where an apply process passes a row LCR to a DML handler and there is a relevant update conflict handler configured.
The DML handler processes the row LCR. The designer of the DML handler has complete control over this processing. Some DML handlers may perform SQL operations or run the EXECUTE member procedure of the row LCR. If the DML handler runs the EXECUTE member procedure of the row LCR, then the apply process tries to apply the row LCR. This row LCR may have been modified by the DML handler.
If any SQL operation performed by the DML handler fails, or if an attempt to run the EXECUTE member procedure fails for any reason other than an update conflict, then the behavior is the same as that described in "DML Handler But No Relevant Update Conflict Handler". Note that uniqueness conflicts and delete conflicts are not update conflicts.
If an attempt to run the EXECUTE member procedure fails because of an update conflict, then the behavior depends on the setting of the conflict_resolution parameter in the EXECUTE member procedure:
If the conflict_resolution parameter is set to true, then the relevant update conflict handler is invoked.
If the update conflict handler resolves the conflict successfully, and all other operations performed by the DML handler succeed, then the DML handler finishes without raising an exception and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set.
If the update conflict handler cannot resolve the conflict, then the DML handler can try to handle the exception. If the DML handler does not raise an exception, then the apply process assumes the DML handler has performed the appropriate action with the row LCR, and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set. If the DML handler cannot handle the exception, then the DML handler should raise an exception. In this case, the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
If the conflict_resolution parameter is set to false, then the relevant update conflict handler is not invoked. In this case, the behavior is the same as that described in "DML Handler But No Relevant Update Conflict Handler".
Consider a case where an apply process encounters an error when it tries to apply a row LCR. This error may be caused by a conflict or by some other condition. There is an error handler for the table operation but no relevant update conflict handler configured.
The row LCR is passed to the error handler. The error handler processes the row LCR. The designer of the error handler has complete control over this processing. Some error handlers may perform SQL operations or run the EXECUTE member procedure of the row LCR. If the error handler runs the EXECUTE member procedure of the row LCR, then the apply process tries to apply the row LCR. This row LCR may have been modified by the error handler.
If any SQL operation performed by the error handler fails, or if an attempt to run the EXECUTE member procedure fails, then the error handler can try to handle the exception. If the error handler does not raise an exception, then the apply process assumes the error handler has performed the appropriate action with the row LCR, and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set.
If the error handler cannot handle the exception, then the error handler should raise an exception. In this case, the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
Consider a case where an apply process encounters an error when it tries to apply a row LCR. There is an error handler for the table operation, and there is a relevant update conflict handler configured.
The handler that is invoked to handle the error depends on the type of error it is:
If the update conflict handler cannot resolve the conflict, then the error handler is invoked. If the error handler does not raise an exception, then the apply process assumes the error handler has performed the appropriate action with the row LCR, and the apply process continues applying the other LCRs in the transaction that satisfy the apply process rule set. If the error handler cannot process the LCR, then the error handler should raise an exception. In this case, the transaction containing the row LCR is rolled back, and all of the LCRs in the transaction that satisfy the apply process rule set are moved to an exception queue.
See Also:
|
The following sections discuss considerations for applying DDL changes to tables:
The following types of DDL changes are not supported by an apply process. These types of DDL changes are not applied:
ALTER MATERIALIZED VIEWALTER MATERIALIZED VIEW LOGCREATE DATABASE LINKCREATE SCHEMA AUTHORIZATIONCREATE MATERIALIZED VIEWCREATE MATERIALIZED VIEW LOGDROP DATABASE LINKDROP MATERIALIZED VIEWDROP MATERIALIZED VIEW LOGRENAMEAlso, the following types of CREATE TABLE and ALTER TABLE statements are ignored by an apply process:
CREATE TABLE AS SELECT statement on a clustered table is not supported in a Streams environment.CREATE TABLE statement that creates an index-organized table.ALTER TABLE statement that alters an index-organized table.If an apply process receives a DDL LCR that specifies an operation that cannot be applied, then the apply process ignores the DDL LCR and records the following message in the apply process trace file, followed by the DDL text ignored:
Apply process ignored the following DDL:
An apply process applies all other types of DDL changes if the DDL LCRs containing the changes satisfy the rules in the apply process rule set. Also, an apply process can apply valid, user-enqueued DDL LCRs.
For captured DDL changes to be applied properly at a destination database, either the destination database must have the same database structures as the source database, or the non-identical database structural information must not be specified in the DDL statement. Database structures include data files, tablespaces, rollback segments, and other physical and logical structures that support database objects.
For example, for captured DDL changes to tables to be applied properly at a destination database, the following conditions must be met:
CREATE TABLE statement at the source database and destination database.However, if the tablespaces and rollback segments are not specified in the DDL statement, then the default tablespaces and rollback segments are used. In this case, the tablespaces and rollback segments can differ at the source database and destination database.
For a DDL LCR to be applied at a destination database successfully, the user specified as the current_schema in the DDL LCR must exist at the destination database. The current schema is the schema that is used if no schema is specified for an object in the DDL text.
See Also:
|
If you plan to capture DDL changes at a source database and apply these DDL changes at a destination database, then avoid using system-generated names. If a DDL statement results in a system-generated name for an object, then the name of the object typically will be different at the source database and each destination database applying the DDL change from this source database. Different names for objects can result in apply errors for future DDL changes.
For example, suppose the following DDL statement is run at a source database:
CREATE TABLE sys_gen_name (n1 NUMBER NOT NULL);
This statement results in a NOT NULL constraint with a system-generated name. For example, the NOT NULL constraint may be named sys_001500. When this change is applied at a destination database, the system-generated name for this constraint may be sys_c1000.
Suppose the following DDL statement is run at the source database:
ALTER TABLE sys_gen_name DROP CONSTRAINT sys_001500;
This DDL statement succeeds at the source database, but it fails at the destination database and results in an apply error.
To avoid such an error, explicitly name all objects resulting from DDL statements. For example, to name a NOT NULL constraint explicitly, run the following DDL statement:
CREATE TABLE sys_gen_name (n1 NUMBER CONSTRAINT sys_gen_name_nn NOT NULL);
When applying a change resulting from a CREATE TABLE AS SELECT statement, an apply process performs two steps:
CREATE TABLE AS SELECT statement is executed at the destination database, but it creates only the structure of the table. It does not insert any rows into the table. If the CREATE TABLE AS SELECT statement fails, then an apply process error results. Otherwise, the statement autocommits, and the apply process performs Step 2.CREATE TABLE AS SELECT statement into the corresponding table at the destination database. It is possible that a capture process, a propagation, or an apply process will discard all of the row LCRs with these inserts based on their rule sets. In this case, the table remains empty at the destination database.
You can control a DML or DDL trigger's firing property using the SET_TRIGGER_FIRING_PROPERTY procedure in the DBMS_DDL package. This procedure lets you specify whether a trigger's firing property is set to fire once. If a trigger's firing property is set to fire once, then it does not fire in the following cases:
EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM packageIf a trigger is not set to fire once, then it fires in both of these cases.
By default, DML and DDL triggers are set to fire once. You can check a trigger's firing property by using the IS_TRIGGER_FIRE_ONCE function in the DBMS_DDL package.
For example, in the hr schema, the update_job_history trigger adds a row to the job_history table when data is updated in the job_id or department_id column in the employees table. Suppose, in a Streams environment, the following configuration exists:
dbs1.net database.dbs2.net database.dbs2.net database.update_job_history trigger exists in the hr schema in both databases.If the update_job_history trigger is not set to fire once at dbs2.net in this scenario, then these actions result:
job_id column is updated for an employee in the employees table at dbs1.net.update_job_history trigger fires at dbs1.net and adds a row to the job_history table that records the change.dbs1.net captures the changes to both the employees table and the job_history table.dbs2.net database.dbs2.net database applies both changes.update_job_history trigger fires at dbs2.net when the apply process updates the employees table.In this case, the change to the employees table is recorded twice at the dbs2.net database: when the apply process applies the change to the job_history table and when the update_job_history trigger fires to record the change made to the employees table by the apply process.
As you can see, the database administrator may not want the update_job_history trigger to fire at the dbs2.net database when a change is made by the apply process. Similarly, a database administrator may not want a trigger to fire because of the execution of an apply error transaction. If the update_job_history trigger's firing property is set to fire once, then it does not fire at dbs2.net when the apply process applies a change to the employees table, and it does not fire when an executed error transaction updates the employees table.
Also, if you use the ON SCHEMA clause to create a schema trigger, then the schema trigger fires only if the schema performs a relevant change. Therefore, when an apply process is applying changes, a schema trigger that is set to fire always fires only if the apply user is the same as the schema specified in the schema trigger. If the schema trigger is set to fire once, then it never fires when an apply process applies changes, regardless of whether the apply user is the same as the schema specified in the schema trigger.
For example, if you specify a schema trigger that always fires on the hr schema at a source database and destination database, but the apply user at a destination database is strmadmin, then the trigger fires when the hr user performs a relevant change on the source database, but the trigger does not fire when this change is applied at the destination database. However, if you specify a schema trigger that always fires on the strmadmin schema at the destination database, then this trigger fires whenever a relevant change is made by the apply process, regardless of any trigger specifications at the source database.
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about setting a trigger's firing property with the |
In a Streams environment that shares information between multiple databases, a source database is the database where changes are generated in the redo log. Suppose an environment has the following characteristics:
In such an environment, the tables that already exist at the destination database are not instantiated. That is, because these tables already exist at the destination database, they are not created at the destination by exporting them at the source database and then importing them at the destination database. Instead, the apply process at the destination database must be instructed explicitly to apply changes that committed after a specific system change number (SCN) for each source database table. The instantiation SCN for the tables specifies this SCN.
The instantiation SCN for a database object controls which LCRs that contain changes to the database object are ignored by an apply process and which LCRs are applied by an apply process. If the commit SCN of an LCR for a database object from a source database is less than or equal to the instantiation SCN for that database object at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.
Also, if there are multiple source databases for a shared database object at a destination database, then an instantiation SCN must be set for each source database, and the instantiation SCN may be different for each source database. You can set an instantiation SCN using export/import or using a procedure in the DBMS_APPLY_ADM package.
Streams also records the ignore SCN for each database object. The ignore SCN is the SCN below which the instantiation SCN cannot be set. This value corresponds to the SCN value at the source database at the time when the object was prepared for instantiation.
You can view the instantiation SCN and ignore SCN for database objects by querying the DBA_APPLY_INSTANTIATED_OBJECTS data dictionary view.
If an apply process is running, then the oldest SCN is the earliest SCN of the transactions currently being dequeued and applied. For a stopped apply process, the oldest SCN is the earliest SCN of the transactions that were being applied when the apply process was stopped.
The following are two common scenarios in which the oldest SCN is important:
In both cases, you should determine the oldest SCN for the apply process by querying the DBA_APPLY_PROGRESS data dictionary view; the OLDEST_MESSAGE_NUMBER column in this view contains the oldest SCN. Then, set the start SCN for the capture process that is capturing changes for the apply process to the same value as the oldest SCN value. If the capture process is capturing changes for other apply processes, then these other apply processes may receive duplicate LCR events when you reset the start SCN for the capture process. In this case, the other apply processes automatically discard the duplicate LCR events.
The low-watermark for an apply process is the system change number (SCN) up to which all events have been applied. That is, events that were committed at an SCN less than or equal to the low-watermark number have definitely been applied, but some events that were committed with a higher SCN also may have been applied. The low-watermark is also sometimes called the applied SCN.
The high-watermark for an apply process is the SCN beyond which no events have been applied. That is, no events that were committed with an SCN greater than the high-watermark have been applied.
You can view the low-watermark and high-watermark for one or more apply processes by querying the V$STREAMS_APPLY_COORDINATOR and ALL_APPLY_PROGRESS data dictionary views.
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.
You can configure a Streams apply process to apply changes in a Real Application Clusters environment. If you use an apply process to apply captured LCRs in a Real Application Clusters environment, then any call to the START_APPLY procedure in the DBMS_APPLY_ADM package must be run on the owner instance of the queue that is used by the apply process.
Calls to other procedures and functions that operate on an apply process can be performed from any instance. Also, an apply process that applies user-enqueued events can start in any 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. If this happens, then, to restart the apply process, connect to the owner instance for the queue and run the START_APPLY procedure. The DBA_QUEUE_TABLES data dictionary view contains information about the owner instance for a queue table. The apply process maintains a persistent start/stop state in a Real Application Clusters environment only if the owner instance for its queue does not change before the database instance owning the queue is restarted.
Also, in a Real Application Clusters environment, an apply coordinator process, its corresponding apply reader server, and all of its apply server processes run on a single instance.
See Also:
|
You can create, alter, start, stop, and drop an apply process, and you can define apply rules that control which events an apply process dequeues from the queue. The user who creates an apply process is, by default, the user who applies changes. This user must have the necessary privileges to apply changes.
| See Also:
"Configuring a Streams Administrator" for information about the required privileges |
This section discusses the following topics:
An apply process consists of the following components:
apnn, where nn is a coordinator process number. Valid coordinator process names include ap01 through ap99.When an apply server commits a completed transaction, this transaction has been applied. When an apply server places a transaction in an exception 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 and waits for instructions. The coordinator monitors all of the apply servers to ensure that transactions are applied and committed in the correct order.
For example, consider these two transactions:
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. After apply server A has applied the insert transaction, the coordinator process instructs apply server B to apply the update transaction.
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 are added to the rule set automatically. The DBMS_STREAMS_ADM package was designed for use in replication environments. Alternatively, using the DBMS_APPLY_ADM package to create an apply process is more flexible, and you create a rule set 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 and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, 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 the 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:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the following procedures, which can be used to create an apply process. |
When a capture process is created, a duplicate data dictionary called the Streams data dictionary is populated automatically. 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 when a capture process evaluates rules and creates LCRs. 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 user 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.
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:
|
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 one or more parallel execution servers.
See Also:
|
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 from 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 than the source 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 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 contain commit changes in a different order from the source database. For example, suppose two nondependent transactions are committed at the source database in the following order:
At the destination database, these transactions may be committed in the opposite order:
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 (apnn) remains the same.
Using the disable_on_error apply process parameter, you can either 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.
An apply process maintains a persistent state. That is, an apply process maintains its current state when the database is shut down and restarted. For example, if an apply process is running when the database is shut down, then the apply process automatically starts when the database is restarted, but, if an apply process is stopped when a database is shut down, then the apply process remains stopped when the database is restarted.
An exception queue is associated with each queue table. When you create a Streams queue using the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, an exception queue is created automatically, if one does not already exist for the queue table used by the Streams queue. To view information about Streams apply errors in all of the exception queues in a database, query the DBA_APPLY_ERROR data dictionary view.
An exception queue stores information about transactions that could not be applied successfully by an apply process running in the database. A transaction may include many events, and when an unhandled error occurs during apply, the apply process automatically copies all of the events in the transaction that satisfy the apply process rule set to an exception queue. The last error moved to an exception queue is on top of the error stack.
An exception queue contains information only about errors encountered at the local destination database. It does not contain information about errors for apply processes running in other databases in a Streams environment.
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 exception queue using the EXECUTE_ERROR and EXECUTE_ALL_ERRORS procedures or delete the transaction from the exception queue using the DELETE_ERROR and DELETE_ALL_ERRORS procedures. Both of these procedures are in the DBMS_APPLY_ADM package.
When you reexecute a transaction in an exception queue, you can specify that the transaction be executed either by the user who originally placed the error in the exception 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 an exception queue.
A reexecuted transaction uses any relevant apply handlers and conflict resolution handlers. If, to resolve the error, the LCR inside an exception queue needs to be modified before it is executed, then you can configure a DML handler to process the LCR that caused the error in the exception queue. In this case, the DML handler may modify the LCR in some way to avoid a repeat of the same error. The LCR is passed to the DML handler when you reexecute the error containing the LCR using the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package.
See Also:
|
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|