1 Understanding Oracle Streams Replication

Capture Process Overview

Changes made to database objects in an Oracle database are logged in the redo log to guarantee recoverability in the event of user error or media failure. A capture process is an Oracle background process that reads the database redo log to capture DML and DDL changes made to database objects. The source database for a change that was captured by a capture process is always the database where the change was generated in the redo log. A capture process formats these changes into messages called LCRs and enqueues them into the buffered queue portion of a queue. LCRs enqueued by a capture process are called captured LCRs. Because a running capture process automatically captures changes based on its rules, change capture using a capture process is a form of implicit capture.

There are two types of LCRs: a row LCR contains information about a change to a row in a table resulting from a DML operation, and a DDL LCR contains information about a DDL change to a database object. You use rules to specify which changes are captured. A single DML operation can change more than one row in a table. Therefore, a single DML operation can result in more than one row LCR, and a single transaction can consist of multiple DML operations.

Changes are captured by a capture user. The capture user captures all DML changes and DDL changes that satisfy the capture process rule sets.

A capture process can capture changes locally at the source database, or it can capture changes remotely at a downstream database. Figure 1-2 illustrates a local capture process.

Figure 1-2 Local Capture Process

Description of "Figure 1-2 Local Capture Process"

Downstream capture means that a capture process runs on a database other than the source database. The following types of configurations are possible for a downstream capture process:

• A real-time downstream capture configuration means that redo transport services use the log writer process (LGWR) at the source database to send redo data from the online redo log to the downstream database. At the downstream database, a remote file server process (RFS) receives the redo data and stores it in the standby redo log, and the archiver at the downstream database archives the redo data in the standby redo log. The real-time downstream capture process captures changes from the standby redo log whenever possible and from the archived redo log whenever necessary.

• An archived-log downstream capture configuration means that archived redo log files from the source database are copied to the downstream database, and the capture process captures changes in these archived redo log files. You can copy the archived redo log files to the downstream database using redo transport services, the DBMS_FILE_TRANSFER package, file transfer protocol (FTP), or some other mechanism.

Figure 1-3 illustrates a real-time downstream capture process.

Figure 1-3 Real-Time Downstream Capture

Description of "Figure 1-3 Real-Time Downstream Capture"

Figure 1-4 illustrates an archived-log downstream capture process.

Figure 1-4 Archived-Log Downstream Capture Process

Description of "Figure 1-4 Archived-Log Downstream Capture Process"

A local capture process reads the online redo log whenever possible and archived redo log files otherwise. A real-time downstream capture process reads the standby redo log whenever possible and archived standby redo log files otherwise. An archived-log downstream capture process always reads archived redo log files from the source database.

Supplemental Logging for Oracle Streams Replication

Supplemental logging places additional column data into a redo log whenever an operation is performed. The capture process captures this additional information and places it in LCRs. Supplemental logging is always configured at a source database, regardless of the location of the capture process that captures changes to the source database.

There are two types of supplemental logging: database supplemental logging and table supplemental logging. Database supplemental logging specifies supplemental logging for an entire database, while table supplemental logging enables you to specify log groups for supplemental logging of a particular table. If you use table supplemental logging, then you can choose between two types of log groups: unconditional log groups and conditional log groups.

Unconditional log groups log the before images of specified columns when the table is changed, regardless of whether the change affected any of the specified columns. Unconditional log groups are sometimes referred to as "always log groups." Conditional log groups log the before images of all specified columns only if at least one of the columns in the log group is changed.

Supplementing logging at the database level, unconditional log groups at the table level, and conditional log groups at the table level together determine which old values are logged for a change.

If you plan to use one or more apply processes to apply LCRs captured by a capture process, then you must enable supplemental logging at the source database for the following types of columns in tables at the destination database:

• Any columns at the source database that are used in a primary key in tables for which changes are applied at a destination database must be unconditionally logged in a log group or by database supplemental logging of primary key columns.

• If the parallelism of any apply process that will apply the changes is greater than 1, then any unique constraint column at a destination database that comes from multiple columns at the source database must be conditionally logged. Supplemental logging does not need to be specified if a unique constraint column comes from a single column at the source database.

• If the parallelism of any apply process that will apply the changes is greater than 1, then any foreign key column at a destination database that comes from multiple columns at the source database must be conditionally logged. Supplemental logging does not need to be specified if the foreign key column comes from a single column at the source database.

• If the parallelism of any apply process that will apply the changes is greater than 1, then any bitmap index column at a destination database that comes from multiple columns at the source database must be conditionally logged. Supplemental logging does not need to be specified if the bitmap index column comes from a single column at the source database.

• Any columns at the source database that are used as substitute key columns for an apply process at a destination database must be unconditionally logged. You specify substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package.

• The columns specified in a column list for conflict resolution during apply must be conditionally logged if more than one column at the source database is used in the column list at the destination database.

• Any columns at the source database that are used by a DML handler or error handler at a destination database must be unconditionally logged.

• Any columns at the source database that are used by a rule or a rule-based transformation must be unconditionally logged.

• Any columns at the source database that are specified in a value dependency virtual dependency definition at a destination database must be unconditionally logged.

• If you specify row subsetting for a table at a destination database, then any columns at the source database that are in the destination table or columns at the source database that are in the subset condition must be unconditionally logged. You specify a row subsetting condition for an apply process using the dml_condition parameter in the ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package.

If you do not use supplemental logging for these types of columns at a source database, then changes involving these columns might not apply properly at a destination database.

Captured LCRs and Persistent LCRs

A single apply process can apply messages from either a buffered queue or a persistent queue, but not both. If a queue at a destination database contains LCRs in both its buffered queue and persistent queue, then the destination database must have at least two apply processes to dequeue these LCRs.

When an apply process is dequeuing messages from a buffered queue, it can only dequeue messages that were captured by a capture process (captured LCRs). An apply process cannot dequeue messages that were enqueued by an application into a buffered queue. When an apply process is dequeuing messages from a persistent queue, it can dequeue persistent LCRs that were enqueued by a synchronous capture or by an application.

You can use the DBMS_STREAMS_ADM package or the DBMS_APPLY_ADM package to create an apply process that dequeues captured LCRs, but only the CREATE_APPLY procedure in the DBMS_APPLY_ADM package can create an apply process that dequeues persistent LCRs.

Direct and Custom Apply of LCRs

Direct apply means that an apply process applies an LCR 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 an error handler can resolve the error, then it should apply the LCR, if appropriate. An error handler can resolve an error by modifying the LCR before applying it. If the error handler cannot resolve the error, then the apply process places the transaction, and all LCRs associated with the transaction, into the error queue.

Custom apply means that an apply process passes the LCR as a parameter to a user procedure for processing. The user procedure can process the LCR 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 can 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 to skip DELETE operations for the hr.employees table at a certain destination database, then you can specify a DML handler for DELETE operations on this table to accomplish this goal. Such a handler is not invoked for INSERT, UPDATE, or LOB_UPDATE operations on the table. 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.

In addition to DML handlers and DDL handlers, you can specify a precommit handler for an apply process. A precommit handler is a PL/SQL procedure that takes the commit SCN from an internal commit directive in the queue used by the apply process. The precommit handler can process the commit information in any customized way. For example, it can record the commit information for an apply process in an audit table.

How Dependent Transactions Are Applied

The parallelism apply process parameter controls the parallelism of an apply process. When apply process parallelism is set to 1, a single apply server applies transactions in the same order as the order in which they were committed on the source database. In this case, dependencies are not an issue. For example, if transaction A committed before transaction B on the source database, then, on the destination database, all of the LCRs in transaction A are applied before any LCRs in transaction B.

However, when apply process parallelism is set to a value greater than 1, multiple apply servers apply transactions simultaneously. When an apply process is applying transactions in parallel, it applies the row LCRs in these transactions until it detects a row LCR that depends on a row LCR in another transaction. When a dependent row LCR is detected, an apply process finishes applying the LCRs in the transaction with the lower commit system change number (CSCN) and commits this transaction before it finishes applying the remaining row LCRs in the transaction with the higher CSCN.

For example, consider two transactions: transaction A and transaction B. The transactions are dependent transactions, and each transaction contains 100 row LCRs. Transaction A committed on the source database before transaction B. Therefore, transaction A has the lower CSCN of the two transactions. An apply process can apply these transactions in parallel in the following way:

1. The apply process begins to apply row LCRs from both transactions in parallel.

2. Using a constraint in the destination database's data dictionary or a virtual dependency definition at the destination database, the apply process detects a dependency between a row LCR in transaction A and a row LCR in transaction B.

3. Because transaction B has the higher CSCN of the two transactions, the apply process waits to apply transaction B and does not apply the dependent row LCR in transaction B. The row LCRs before the dependent row LCR in transaction B have been applied. For example, if the dependent row LCR in transaction B is the 81st row LCR, then the apply process could have applied 80 of the 100 row LCRs in transaction B.

4. Because transaction A has the lower CSCN of the two transactions, the apply process applies all the row LCRs in transaction A and commits.

5. The apply process applies the dependent row LCR in transaction B and the remaining row LCRs in transaction B. When all of the row LCRs in transaction B are applied, the apply process commits transaction B.

Row LCR Ordering During Apply

An apply process orders and applies row LCRs in the following way:

• Row LCRs within a single transaction are always applied in the same order as the corresponding changes on the source database.

• Row LCRs that depend on each other in different transactions are always applied in the same order as the corresponding changes on the source database. When apply process parallelism is greater than 1, and the apply process detects a dependency between row LCRs in different transactions, the apply process always executes the transaction with the lower CSCN before executing the dependent row LCR. This behavior is described in more detail in "How Dependent Transactions Are Applied".

• If commit_serialization apply process parameter is set to full, then the apply process commits all transactions, regardless of whether they contain dependent row LCRs, in the same order as the corresponding transactions on the source database.

• If commit_serialization apply process parameter is set to none, then the apply process might apply transactions that do not depend on each other in a different order than the commit order of the corresponding transactions on the source database.

Dependencies and Constraints

If the names of shared database objects are the same at the source and destination databases, and if the objects are in the same schemas at these databases, then an apply process automatically detects dependencies between row LCRs, assuming constraints are defined for the database objects at the destination database. Information about these constraints is stored in the data dictionary at the destination database.

Regardless of the setting for the commit_serialization parameter and apply process parallelism, an apply process always respects dependencies between transactions that are enforced by database constraints. When an apply process is applying a transaction that contains row LCRs that depend on row LCRs in another transaction, the apply process ensures that the row LCRs are applied in the correct order and that the transactions are committed in the correct order to maintain the dependencies. Apply processes detect dependencies for captured row LCRs and persistent row LCRs.

However, some environments have dependencies that are not enforced by database constraints, such as environments that enforce dependencies using applications. If your environment has dependencies for shared database objects that are not enforced by database constraints, then set the commit_serialization parameter to full for apply processes that apply changes to these database objects.

Dependency Detection, Rule-Based Transformations, and Apply Handlers

When rule-based transformations are specified for rules used by an apply process, and apply handlers are configured for the apply process, LCRs are processed in the following order:

1. The apply process dequeues LCRs from its queue.

2. The apply process runs rule-based transformations on LCRs, when appropriate.

3. The apply process detects dependencies between LCRs.

4. The apply process passes LCRs to apply handlers, when appropriate.

Virtual Dependency Definitions

In some cases, an apply process requires additional information to detect dependencies in row LCRs that are being applied in parallel. The following are examples of cases in which an apply process requires additional information to detect dependencies:

• The data dictionary at the destination database does not contain the required information. The following are examples of this case:

  • The apply process cannot find information about a database object in the data dictionary of the destination database. This can happen when there are data dictionary differences for shared database objects between the source and destination databases. For example, a shared database object can have a different name or can be in a different schema at the source database and destination database.

  • A relationship exists between two or more tables, and the relationship is not recorded in the data dictionary of the destination database. This can happen when database constraints are not defined to improve performance or when an application enforces dependencies during database operations instead of database constraints.

• Data is denormalized by an apply handler after dependency computation. For example, the information in a single row LCR can be used to create multiple row LCRs that are applied to multiple tables.

Apply errors or incorrect processing can result when an apply process cannot determine dependencies properly. In some of the cases described in the previous list, rule-based transformations can be used to avoid apply problems. For example, if a shared database object is in different schemas at the source and destination databases, then a rule-based transformation can change the schema in the appropriate LCRs. However, the disadvantage with using rule-based transformations is that they cannot be executed in parallel.

A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions at a destination database. A virtual dependency definition is not described as a constraint in the data dictionary of the destination database. Instead, it is specified using procedures in the DBMS_APPLY_ADM package. Virtual dependency definitions enable an apply process to detect dependencies that it would not be able to detect by using only the constraint information in the data dictionary. After dependencies are detected, an apply process schedules LCRs and transactions in the correct order for apply.

Virtual dependency definitions provide required information so that apply processes can detect dependencies correctly before applying LCRs directly or passing LCRs to apply handlers. Virtual dependency definitions enable apply handlers to process these LCRs correctly, and the apply handlers can process them in parallel to improve performance.

A virtual dependency definition can define one of the following types of dependencies:

• Value Dependency

• Object Dependency

Barrier Transactions

When an apply process cannot identify the table row or the database object specified in a row LCR by using the destination database's data dictionary and virtual dependency definitions, the transaction that contains the row LCR is applied after all of the other transactions with lower CSCN values. Such a transaction is called a barrier transaction. Transactions with higher CSCN values than the barrier transaction are not applied until after the barrier transaction has committed. In addition, all DDL transactions are barrier transactions.

Constraints and 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 key 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 custom rule-based transformation can combine a multi-column constraint from the source database into a single key column at the destination database. Also, an apply handler or custom rule-based transformation can 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.

Substitute Key Columns

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 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 Oracle 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, Oracle Streams uses the primary key of a table to identify rows in the table, and if a primary key does not exist, Oracle Streams uses the smallest unique key that has at least one NOT NULL column to identify rows in the table. When a table at a destination database does not have a primary key or a unique key with at least one NOT NULL column, or when you want to use columns other than the primary key or unique 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 can contain nulls. Also, the substitute key columns take precedence over any existing primary key or unique keys 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 substitute key columns, primary key constraints, and unique key constraints, an apply process uses all of the columns in the table as the key columns, excluding LOB, LONG, and LONG RAW 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.

Apply Process Behavior for Column Discrepancies

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

Index-Organized Tables and an Apply Process

An apply process can apply changes made to an index-organized table only if the index-organized table does not contain any columns of the following data types:

• ROWID

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

• Oracle-supplied types (including Any types, URI types, spatial types, and media types)

If an index-organized table contains a column of one of these data types, then an apply process raises an error if it tries to apply LCRs that contain changes to it.

Conflict Resolution and an Apply Process

Conflicts are possible in an Oracle Streams configuration where data is shared between multiple databases. A conflict is a mismatch between the old values in an LCR and the expected data in a table. 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 can 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.

Oracle Streams automatically detects conflicts and, for update conflicts, tries to use an update conflict handler to resolve them if one is configured. Oracle 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 handler cannot resolve, then you can build and use your own custom conflict resolution handlers in an error handler or DML handler. Conflict detection can be disabled for nonkey columns.

Handlers and Row LCR Processing

Any of the following handlers can process a row LCR:

• DML handler

• Error handler

• Update conflict handler

The following sections describe the possible scenarios involving these handlers:

• No Relevant Handlers

• Relevant Update Conflict Handler

• DML Handler But No Relevant Update Conflict Handler

• DML Handler And a Relevant Update Conflict Handler

• Error Handler But No Relevant Update Conflict Handler

• Error Handler And a Relevant Update Conflict Handler

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.

Types of DDL Changes Ignored by an Apply Process

The following types of DDL changes are not supported by an apply process. These types of DDL changes are not applied:

• ALTER MATERIALIZED VIEW

• ALTER MATERIALIZED VIEW LOG

• CREATE DATABASE LINK

• CREATE SCHEMA AUTHORIZATION

• CREATE MATERIALIZED VIEW

• CREATE MATERIALIZED VIEW LOG

• DROP DATABASE LINK

• DROP MATERIALIZED VIEW

• DROP MATERIALIZED VIEW LOG

• RENAME

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 that was ignored:

Apply process ignored the following DDL:

An apply process applies all other types of DDL changes if the DDL LCRs containing the changes should be applied according to the apply process rule sets.

Database Structures in an Oracle Streams Environment

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 nonidentical database structural information must not be specified in the DDL statement. Database structures include datafiles, 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:

• The same storage parameters must be specified in the CREATE TABLE statement at the source database and destination database.

• If a DDL statement refers to specific tablespaces or rollback segments, then the tablespaces or rollback segments must have the same names and compatible specifications 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.

• The same partitioning specifications must be used at the source database and destination database.

Current Schema User Must Exist at 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.

System-Generated Names

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 might be named sys_001500. When this change is applied at a destination database, the system-generated name for this constraint might 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);

CREATE TABLE AS SELECT Statements

When applying a change resulting from a CREATE TABLE AS SELECT statement, an apply process performs two steps:

1. The 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 auto commits, and the apply process performs Step 2.

2. The apply process inserts the rows that were inserted at the source database as a result of the 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.