2 Oracle Streams Information Capture

Implicit Capture

With implicit capture, data definition language (DDL) and data manipulation language (DML) changes are captured automatically either by a capture process or by synchronous capture. A specific type of message called logical change record (LCR) describes these database changes. Both a capture process and synchronous capture can filter database changes with user-defined rules. Therefore, only changes to specified objects are captured.

The following topics describe capture processes and synchronous captures:

• Capture Processes

• Synchronous Captures

Explicit Capture

With explicit capture, applications generate messages and enqueue them. These messages can be formatted as LCRs, or they can be formatted into different types of messages for consumption by other applications. Messages can also be enqueued explicitly by anapply process or by an apply handler for an apply process.

Explicit capture is useful in the following situations:

• When applications generate messages that must be processed by other applications.

• When you have a heterogeneous replication environment in which an apply process in an Oracle database applies changes that originated at a non-Oracle database. In this case, an application captures LCRs based on the changes at the non-Oracle database, and these LCRs are processed by an apply process at an Oracle database.

Logical Change Records (LCRs)

An LCR is a message with a specific format that describes a database change. There are two types of LCRs: row LCRs and DDL LCRs. A capture process, synchronous capture, or an application can capture LCRs.

You can capture the following types of LCRs with Oracle Streams:

• A captured LCR is an LCR that is captured implicitly by a capture process and enqueued into the buffered queue portion of an ANYDATA queue.

• A persistent LCR is an LCR that is enqueued into the persistent queue portion of an ANYDATA queue. A persistent LCR can be enqueued in one of the following ways:

  • Captured implicitly by a synchronous capture and enqueued

  • Constructed explicitly by an application and enqueued

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

  There are no differences between persistent LCRs that were enqueued in any of these ways. That is, a persistent LCR that was captured by a synchronous capture is the same as a persistent LCR that was constructed and enqueued by an application. Further, both of these persistent LCRs are the same as a persistent LCR enqueued by an apply process.

• A buffered LCR is and LCR that is constructed explicitly by an application and enqueued into the buffered queue portion of an ANYDATA queue.

The following sections contain information about LCRs:

• Row LCRs

• DDL LCRs

• Extra Information in LCRs

User Messages

Messages that do not contain LCRs are called user messages. User messages can be of any type (except an LCR type). User messages can be created by an application and consumed by an application. For example, a business application might create a user message for each order, and these messages might be processed by another application.

You can capture the following types of user messages with Oracle Streams:

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

  • Created explicitly by an application and enqueued

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

  A persistent user message can be enqueued into the persistent queue portion of an ANYDATA queue or a typed queue.

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

Introduction to Capture Processes

Every Oracle database has a set of two or more redo log files. The redo log files for a database are collectively known as the database redo log. The primary function of the redo log is to record all changes made to the database.

Redo logs are used to guarantee recoverability in the event of human error or media failure. A capture process is an optional Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. When a capture process is configured to capture changes from a redo log, the database where the changes were generated is called the source database.

When a capture process captures a database change, it converts it into a specific message format called a logical change record (LCR). After capturing an LCR, a capture process enqueues a message containing the LCR into a queue. A capture process is always associated with a single ANYDATA queue, and it enqueues messages into this queue only. For improved performance, captured LCRs always are stored in a buffered queue, which is System Global Area (SGA) memory associated with an ANYDATA queue. You can create multiple queues and associate a different capture process with each queue.

Captured LCRs can be sent to queues in the same database or other databases by propagations. Captured LCRs can also be dequeued by apply processes. In some situations, an optimization enables capture processes to send LCRs directly to apply processes. This optimization is called combined capture and apply.

A capture process can run on the source database or on a remote database. When a capture process runs on the source database, the capture process is a local capture process. When a capture process runs on a remote database, the capture process is called a downstream capture process, and the remote database is called the downstream database.

Figure 2-1 shows a capture process capturing LCRs.

Figure 2-1 Capture Process

Description of "Figure 2-1 Capture Process"

Capture Process Rules

A capture process either captures or discards 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 or negative rule set for the capture process.

If a rule evaluates to TRUE for a change, and the rule is in the positive rule set for a capture process, then the capture process captures the change. If a rule evaluates to TRUE for a change, and the rule is in the negative rule set for a capture process, then the capture process discards the change. If a capture process has both a positive and a negative rule set, then the negative rule set is always evaluated first.

You can specify capture process rules at the following levels:

• A table rule captures 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 captures or discards either row changes resulting from DML changes or DDL changes to the database objects in a particular schema.

• A global rule captures or discards either all row changes resulting from DML changes or all DDL changes in the database.

Data Types Captured by Capture Processes

When capturing the row changes resulting from DML changes made to tables, a capture process can capture changes made to columns of the following data types:

• VARCHAR2

• NVARCHAR2

• FLOAT

• 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

• UROWID

• CLOB with BASICFILE storage

• NCLOB with BASICFILE storage

• BLOB with BASICFILE storage

• XMLType stored as CLOB

A capture process does not capture the results of DML changes to columns of the following data types:

• SecureFile CLOB, NCLOB, and BLOB

• BFILE

• ROWID

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

• XMLType stored object relationally or as binary XML

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

In addition, a capture process does not capture the results of DML changes to virtual columns.

A capture process raises an error if it tries to create a row LCR for a DML change to a column of an unsupported data type. When a capture process raises an error, it writes the LCR that caused the error into its trace file, raises an ORA-26744 error, and becomes disabled. In this case, modify the rules used by the capture process to avoid the error, and restart the capture process.

Types of Changes Captured by Capture Processes

A capture process can capture only certain types of changes made to a database and its objects. The following sections describe the types of DML and DDL changes that can be captured.

This section includes the following topics:

• Types of DML Changes Captured by Capture Processes

• DDL Changes and Capture Processes

• Changes Ignored by a Capture Process

• NOLOGGING and UNRECOVERABLE Keywords for SQL Operations

• UNRECOVERABLE Clause for Direct Path Loads

Supplemental Logging in an Oracle Streams Environment

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

Typically, supplemental logging is required in Oracle Streams replication environments. In these environments, an apply process needs the additional information in the LCRs to properly apply DML changes and DDL changes that are replicated from a source database to a destination database. However, supplemental logging can also be required in environments where changes are not applied to database objects directly by an apply process. In such environments, an apply handler can process the changes without applying them to the database objects, and the supplemental information might be needed by the apply handlers.

Local Capture and Downstream Capture

You can configure a capture process to run locally on a source database or remotely on a downstream database. A single database can have one or more capture processes that capture local changes and other capture processes that capture changes from a remote source database. That is, you can configure a single database to perform both local capture and downstream capture.

Downstream Capture

Downstream capture means that a capture process runs on a database other than the source database. The following types of downstream capture configurations are possible: real-time downstream capture and archived-log downstream capture. The downstream_real_time_mine capture process parameter controls whether a downstream capture process performs real-time downstream capture or archived-log downstream capture. A real-time downstream capture process and one or more archived-log downstream capture processes can coexist at a downstream database.

Note:

• References to "downstream capture processes" in this document apply to both real-time downstream capture processes and archived-log downstream capture processes. This document distinguishes between the two types of downstream capture processes when necessary.

• A downstream capture process only can capture changes from a single source database. However, multiple downstream capture processes at a single downstream database can capture changes from a single source database or multiple source databases.

• To configure downstream capture, the source database must be an Oracle Database 10g Release 1 or later database.

Real-Time Downstream Capture

A real-time downstream capture configuration works in the following way:

• Redo transport services use the log writer process (LGWR) at the source database to send redo data to the downstream database either synchronously or asynchronously. At the same time, the LGWR records redo data in the online redo log at the source database.

• A remote file server process (RFS) at the downstream database receives the redo data over the network and stores the redo data in the standby redo log.

• A log switch at the source database causes a log switch at the downstream database, and the ARCHn process at the downstream database archives the current standby redo log file.

• The real-time downstream capture process captures changes from the standby redo log whenever possible and from the archived standby redo log files whenever necessary. A capture process can capture changes in the archived standby redo log files if it falls behind. When it catches up, it resumes capturing changes from the standby redo log.

Figure 2-2 Real-Time Downstream Capture

Description of "Figure 2-2 Real-Time Downstream Capture"

The advantage of real-time downstream capture over archived-log downstream capture is that real-time downstream capture reduces the amount of time required to capture changes made at the source database. The time is reduced because the real-time capture process does not need to wait for the redo log file to be archived before it can capture data from it.

Note:

Only one real-time downstream capture process can exist at a downstream database.

Archived-Log Downstream Capture

A archived-log downstream capture process 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 2-3 Archived-Log Downstream Capture

Description of "Figure 2-3 Archived-Log Downstream Capture"

Note:

As illustrated in Figure 2-3, the source database for a change captured by a downstream capture process is the database where the change was recorded in the redo log, not the database running the downstream capture process.

The advantage of archived-log downstream capture over real-time downstream capture is that archived-log downstream capture allows multiple downstream capture processes at a downstream database. You can copy redo log files from multiple source databases to a single downstream database and configure multiple archived-log downstream capture processes to capture changes in these redo log files.

See Also:

Oracle Data Guard Concepts and Administration for more information about redo transport services

The Downstream Database Performs Most Change Capture Actions

If you configure either real-time or archived-log downstream capture, then the following actions are performed at the downstream database:

• The first time a downstream capture process is started at the downstream database, Oracle uses data dictionary information in the redo data from the source database to create a LogMiner data dictionary at the downstream database. The DBMS_CAPTURE_ADM.BUILD procedure is run at the source database to extract the source data dictionary information to the redo log at the source database. Next, the redo data is copied to the downstream database from the source database. Additional downstream capture processes for the same source database can use this existing LogMiner data dictionary, or they can create new LogMiner data dictionaries. Also, a real-time downstream capture process can share a LogMiner data dictionary with one or more archived-log downstream capture processes.

• A capture process scans the redo data from the source database for changes using LogMiner.

• The rules engine evaluates changes based on the rules in one or more of the capture process rule sets.

• The capture process enqueues changes that satisfy the rules in its rule sets into a local ANYDATA queue. The capture process formats the changes as LCRs.

• If the captured LCRs are shared with one or more other databases, then one or more propagations propagate these LCRs from the downstream database to the other databases.

In a downstream capture configuration, the following actions are performed at the source database:

• The DBMS_CAPTURE_ADM.BUILD procedure is run at the source database to extract the data dictionary to the redo log.

• Supplemental logging at the source database places additional information that might be needed for apply in the redo log.

• If database objects at the source database must be instantiated at other databases in the environment, then the objects must be prepared for instantiation and a mechanism such as an Export utility must be used to make a copy of the database objects.

In addition, the redo data must be copied from the computer system running the source database to the computer system running the downstream database. In a real-time downstream capture configuration, redo transport services use LWGR to send redo data to the downstream database. Typically, in an archived-log downstream capture configuration, redo transport services copy the archived redo log files to the downstream database.

See Also:

Chapter 6, "How Rules Are Used in Oracle Streams" for more information about rule sets for Oracle Streams clients and for information about how messages satisfy rule sets

Advantages of Downstream Capture

The following are the advantages of using downstream capture:

• Capturing changes uses fewer resources at the source database because the downstream database performs most of the required work.

• If you plan to capture changes originating at multiple source databases, then capture process administration can be simplified by running multiple archived-log downstream capture processes with different source databases at one downstream database. That is, one downstream database can act as the central location for change capture from multiple sources. In such a configuration, one real-time downstream capture process can run at the downstream database in addition to the archived-log downstream capture processes.

• Copying redo data to one or more downstream databases provides improved protection against data loss. For example, redo log files at the downstream database can be used for recovery of the source database in some situations.

• The ability to configure at one or more downstream databases multiple capture processes that capture changes from a single source database provides more flexibility and can improve scalability.

Optional Database Link from the Downstream Database to the Source Database

When you create or alter a downstream capture process, you optionally can specify the use of a database link from the downstream database to the source database. This database link must have the same name as the global name of the source database. Such a database link simplifies the creation and administration of a downstream capture process. You specify that a downstream capture process uses a database link by setting the use_database_link parameter to TRUE when you run CREATE_CAPTURE or ALTER_CAPTURE on the downstream capture process.

When a downstream capture process uses a database link to the source database, the capture process connects to the source database to perform the following administrative actions automatically:

• In certain situations, runs the DBMS_CAPTURE_ADM.BUILD procedure at the source database to extract the data dictionary at the source database to the redo log when a capture process is created.

• Prepares source database objects for instantiation.

• Obtains the first SCN for the downstream capture process if the first system change number (SCN) is not specified during capture process creation. The first SCN is needed to create a capture process.

If a downstream capture process does not use a database link, then you must perform these actions manually.

Note:

During the creation of a downstream capture process, if the first_scn parameter is set to NULL in the CREATE_CAPTURE procedure, then the use_database_link parameter must be set to TRUE. Otherwise, an error is raised.

See Also:

"Preparing for and Configuring a Real-Time Downstream Capture Process" for information about when the DBMS_CAPTURE_ADM.BUILD procedure is run automatically during capture process creation if the downstream capture process uses a database link

Operational Requirements for Downstream Capture

The following are operational requirements for using downstream capture:

• The source database must be running at least Oracle Database 10g and the downstream capture database must be running the same release of Oracle as the source database or later.

• The downstream database must be running Oracle Database 10g Release 2 or later to configure real-time downstream capture. In this case, the source database must be running Oracle Database 10g Release 1 or later.

• The operating system on the source and downstream capture sites must be the same, but the operating system release does not need to be the same. In addition, the downstream sites can use a different directory structure from the source site.

• The hardware architecture on the source and downstream capture sites must be the same. For example, a downstream capture configuration with a source database on a 32-bit Sun system must have a downstream database that is configured on a 32-bit Sun system. Other hardware elements, such as the number of CPUs, memory size, and storage configuration, can be different between the source and downstream sites.

In a downstream capture environment, the source database can be a single instance database or a multi-instance Oracle Real Application Clusters (Oracle RAC) database. The downstream database can be a single instance database or a multi-instance Oracle RAC database, regardless of whether the source database is single instance or multi-instance.

SCN Values Relating to a Capture Process

This section describes system change number (SCN) values that are important for a capture process. You can query the DBA_CAPTURE data dictionary view to display these values for one or more capture processes.

• Captured SCN and Applied SCN

• First SCN and Start SCN

COLUMN FIRST_CHANGE# HEADING 'First SCN' FORMAT 999999999
COLUMN NAME HEADING 'Log File Name' FORMAT A50

SELECT DISTINCT FIRST_CHANGE#, NAME FROM V$ARCHIVED_LOG
  WHERE DICTIONARY_BEGIN = 'YES';

Oracle Streams Capture Processes and RESTRICTED SESSION

When you enable restricted session during system startup by issuing a STARTUP RESTRICT statement, capture processes do not start, even if they were running when the database shut down. When restricted session is disabled with an ALTER SYSTEM statement, each capture process that was running when the database shut down is started.

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

Apply Process Interoperability with Oracle Streams Capture Processes

An apply process must be Oracle9i Database release 9.2.0.6 or later to process changes captured by an Oracle Database 11g Release 1 (11.1) capture process.

Oracle Streams Capture Processes and Oracle Real Application Clusters

You can configure an Oracle Streams capture process to capture changes in an Oracle Real Application Clusters (Oracle RAC) environment. If you use one or more capture processes and Oracle RAC in the same environment, then all archived logs that contain changes to be captured by a capture process must be available for all instances in the Oracle RAC environment. In an Oracle RAC environment, a capture process reads changes made by all instances.

Each capture process is started and stopped on the owner instance for its ANYDATA queue, even if the start or stop procedure is run on a different instance. Also, a capture 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. If the owner instance for a queue table containing a queue used by a capture process becomes unavailable, then queue ownership is transferred automatically to another instance in the cluster. In addition, if the capture process was enabled when the owner instance became unavailable, then the capture process is restarted automatically on the new owner instance. If the capture process was disabled when the owner instance became unavailable, then the capture 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, any processes used by a single capture process run on a single instance in an Oracle RAC environment.

LogMiner supports the LOG_ARCHIVE_DEST_n initialization parameter, and Oracle Streams capture processes use LogMiner to capture changes from the redo log. If an archived log file is inaccessible from one destination, a local capture process can read it from another accessible destination. On an Oracle RAC database, this ability also enables you to use cross instance archival (CIA) such that each instance archives its files to all other instances. This solution cannot detect or resolve gaps caused by missing archived log files. Hence, it can be used only to complement an existing solution to have the archived files shared between all instances.

Oracle Streams Capture Processes and Transparent Data Encryption

Local capture processes can capture changes to columns that have been encrypted using transparent data encryption. Downstream capture processes can capture changes to columns that have been encrypted only if the downstream database shares a wallet with the source database. A wallet can be shared through a network file system (NFS), or it can be copied from one computer system to another manually. When a wallet is shared with a downstream database, ensure that the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file at the downstream database specifies the wallet location.

If you copy a wallet to a downstream database, then ensure that you copy the wallet from the source database to the downstream database whenever the wallet at the source database changes. Do not perform any operations on the wallet at the downstream database, such as changing the encryption key for a replicated table.

Encrypted columns in row logical change records (row LCRs) captured by a local or downstream capture process are decrypted when the row LCRs are staged in a buffered queue. If row LCRs spill to disk in a database with transparent data encryption enabled, then Oracle Streams transparently encrypts any encrypted columns while the row LCRs are stored on disk.

Capture Process Components

A capture process is an optional Oracle background process whose process name is CPnn, where nn can include letters and numbers. A capture process captures changes from the redo log by using the infrastructure of LogMiner. Oracle Streams configures LogMiner automatically. The underlying LogMiner process name is MSnn, where nn can include letters and numbers. You can create, alter, start, stop, and drop a capture process, and you can define capture process rules that control which changes a capture process captures.

A capture process consists of the following components:

• One reader server that reads the redo log and divides the redo log into regions.

• One or more preparer servers that scan the regions defined by the reader server in parallel and perform prefiltering of changes found in the redo log. Prefiltering involves sending partial information about changes, such as schema and object name for a change, to the rules engine for evaluation, and receiving the results of the evaluation.

• One builder server that merges redo records from the preparer servers. These redo records either evaluated to TRUE during partial evaluation or partial evaluation was inconclusive for them. The builder server preserves the system change number (SCN) order of these redo records and passes the merged redo records to the capture process.

• The capture process (CPnn) performs the following actions for each change when it receives merged redo records from the builder server:

  • Formats the change into an LCR

  • If the partial evaluation performed by a preparer server was inconclusive for the change in the LCR, then sends the LCR to the rules engine for full evaluation

  • Receives the results of the full evaluation of the LCR if it was performed

  • Enqueues the LCR into the queue associated with the capture process if the LCR satisfies the rules in the positive rule set for the capture process, or discards the LCR if it satisfies the rules in the negative rule set for the capture process or if it does not satisfy the rules in the positive rule set

Each reader server, preparer server, and builder server is a process.

Capture User

Changes are captured in the security domain of the capture user for a capture process. The capture user captures all changes that satisfy the capture process rule sets. In addition, the capture user runs all custom rule-based transformations specified by the rules in these rule sets. The capture user must have the necessary privileges to perform these actions, including EXECUTE privilege on the rule sets used by the capture process, EXECUTE privilege on all custom rule-based transformation functions specified for rules in the positive rule set, and privileges to enqueue messages into the capture process queue. A capture process can be associated with only one user, but one user can be associated with many capture processes.

Capture Process States

The state of a capture process describes what the capture process is doing currently. You can view the state of a capture process by querying the STATE column in the V$STREAMS_CAPTURE dynamic performance view. The following capture process states are possible:

• INITIALIZING - Starting up.

• WAITING FOR DICTIONARY REDO - Waiting for redo log files containing the dictionary build related to the first SCN to be added to the capture process session. A capture process cannot begin to scan the redo log files until all of the log files containing the dictionary build have been added.

• DICTIONARY INITIALIZATION - Processing a dictionary build.

• MINING (PROCESSED SCN = scn_value) - Mining a dictionary build at the SCN scn_value.

• LOADING (step X of Y) - Processing information from a dictionary build and currently at step X in a process that involves Y steps, where X and Y are numbers.

• CAPTURING CHANGES - Scanning the redo log for changes that evaluate to TRUE against the capture process rule sets.

• WAITING FOR REDO - Waiting for new redo log files to be added to the capture process session. The capture process has finished processing all of the redo log files added to its session. This state is possible if there is no activity at a source database. For a downstream capture process, this state is possible if the capture process is waiting for new log files to be added to its session.

• EVALUATING RULE - Evaluating a change against a capture process rule set.

• CREATING LCR - Converting a change into an LCR.

• ENQUEUING MESSAGE - Enqueuing an LCR that satisfies the capture process rule sets into the capture process queue.

• PAUSED FOR FLOW CONTROL - Unable to enqueue LCRs either because of low memory or because propagations and apply processes are consuming messages slower than the capture process is creating them. This state indicates flow control that is used to reduce spilling of captured LCRs when propagation or apply has fallen behind or is unavailable.

• SHUTTING DOWN - Stopping.

Multiple Capture Processes in a Single Database

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

Also, Oracle recommends that each ANYDATA queue used by a capture process, propagation, or apply process have captured LCRs from at most one capture process from a particular source database. Therefore, a separate queue should be used for each capture process that captures changes originating at a particular source database, and each queue should have its own queue table. Also, messages from two or more capture processes with the same source database should not be propagated to the same queue.

Capture Process Checkpoints

A checkpoint is information about the current state of a capture process that is stored persistently in the data dictionary of the database running the capture process. A capture process tries to record a checkpoint at regular intervals called checkpoint intervals.

Checkpoint Retention Time

The checkpoint retention time is the amount of time, in number of days, that a capture process retains checkpoints before purging them automatically. A capture process periodically computes the age of a checkpoint by subtracting the NEXT_TIME of the archived redo log that corresponds to the checkpoint from FIRST_TIME of the archived redo log file containing the required checkpoint SCN for the capture process. If the resulting value is greater than the checkpoint retention time, then the capture process automatically purges the checkpoint by advancing its first SCN value. Otherwise, the checkpoint is retained. The DBA_REGISTERED_ARCHIVED_LOG view displays the FIRST_TIME and NEXT_TIME for archived redo log files, and the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE view displays the required checkpoint SCN for a capture process. Figure 2-4 shows an example of a checkpoint being purged when the checkpoint retention time is set to 20 days.

Figure 2-4 Checkpoint Retention Time Set to 20 Days

Description of "Figure 2-4 Checkpoint Retention Time Set to 20 Days"

In Figure 2-4, with the checkpoint retention time set to 20 days, the checkpoint at SCN 435250 is purged because it is 21 days old, while the checkpoint at SCN 479315 is retained because it is 8 days old.

Whenever the first SCN is reset for a capture process, the capture process purges information about archived redo log files prior to the new first SCN from its LogMiner data dictionary. After this information is purged, the archived redo log files remain on the hard disk, but the files are not needed by the capture process. The PURGEABLE column in the DBA_REGISTERED_ARCHIVED_LOG view displays YES for the archived redo log files that are no longer needed. These files can be removed from disk or moved to another location without affecting the capture process.

If you create a capture process using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package, then you can specify the checkpoint retention time, in days, using the checkpoint_retention_time parameter. The default checkpoint retention time is 60 days if the checkpoint_retention_time parameter is not specified in the CREATE_CAPTURE procedure, or if you use the DBMS_STREAMS_ADM package to create the capture process. The CHECKPOINT_RETENTION_TIME column in the DBA_CAPTURE view displays the current checkpoint retention time for a capture process.

You can change the checkpoint retention time for a capture process by specifying a new time in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. If you do not want checkpoints for a capture process to be purged automatically, then specify DBMS_CAPTURE_ADM.INFINITE for the checkpoint_retention_time parameter in CREATE_CAPTURE or ALTER_CAPTURE.

Note:

To specify a checkpoint retention time for a capture process, the compatibility level of the database running the capture process must be 10.2.0 or higher. If the compatibility level is lower than 10.2.0 for a database, then the checkpoint retention time for all capture processes running on the database is infinite.

See Also:

• "The LogMiner Data Dictionary for a Capture Process"

• "First SCN and Start SCN Specifications During Capture Process Creation"

• "A New First SCN Value and Purged LogMiner Data Dictionary Information"

• "Managing the Checkpoint Retention Time for a Capture Process"

• Oracle Database PL/SQL Packages and Types Reference for more information about the CREATE_CAPTURE and ALTER_CAPTURE procedures

Capture Process Creation

You can create a capture process using the DBMS_STREAMS_ADM package or the DBMS_CAPTURE_ADM package. Using the DBMS_STREAMS_ADM package to create a capture 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 capture process and rules can be added to the rule set automatically. The rule set is a positive rule set if the inclusion_rule parameter is set to TRUE (the default), or it is a negative rule set if the inclusion_rule parameter is set to FALSE.

Alternatively, using the DBMS_CAPTURE_ADM package to create a capture process is more flexible, and you create one or more rule sets and rules for the capture process either before or after it is created. 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 capture process. To create a capture process at a downstream database, you must use the DBMS_CAPTURE_ADM package.

When you create a capture process using a procedure in the DBMS_STREAMS_ADM package and generate one or more rules in the positive rule set for the capture process, the objects for which changes are captured are prepared for instantiation automatically, unless it is a downstream capture process and there is no database link from the downstream database to the source database.

When you create a capture process using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package, you should prepare for instantiation any objects for which you plan to capture changes as soon as possible after capture process creation. You can prepare objects for instantiation using one of the following procedures in the DBMS_CAPTURE_ADM package:

• PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.

• PREPARE_SCHEMA_INSTANTIATION prepares for instantiation all of the objects in a schema and all objects added to the schema in the future.

• PREPARE_GLOBAL_INSTANTIATION prepares for instantiation all of the objects in a database and all objects added to the database in the future.

These procedures can also enable supplemental logging for the key columns or for all columns in the table or tables prepared for instantiation.

The LogMiner Data Dictionary for a Capture Process

A capture process requires a data dictionary that is separate from the primary data dictionary for the source database. This separate data dictionary is called a LogMiner data dictionary. There can be more than one LogMiner data dictionary for a particular source database. If there are multiple capture processes capturing changes from the source database, then two or more capture processes can share a LogMiner data dictionary, or each capture process can have its own LogMiner data dictionary. If the LogMiner data dictionary that is needed by a capture process does not exist, then the capture process populates it using information in the redo log when the capture process is started for the first time.

The DBMS_CAPTURE_ADM.BUILD procedure extracts data dictionary information to the redo log, and this procedure must be run at least once on the source database before any capture process capturing changes originating at the source database is started. The extracted data dictionary information in the redo log is consistent with the primary data dictionary at the time when the DBMS_CAPTURE_ADM.BUILD procedure is run. This procedure also identifies a valid first SCN value that can be used to create a capture process.

You can perform a build of data dictionary information in the redo log multiple times, and a particular build might or might not be used by a capture process to create a LogMiner data dictionary. The amount of information extracted to a redo log when you run the BUILD procedure depends on the number of database objects in the database. Typically, the BUILD procedure generates a large amount of redo data that a capture process must scan subsequently. Therefore, you should run the BUILD procedure only when necessary.

In most cases, if a build is required when a capture process is created using a procedure in the DBMS_STREAMS_ADM or DBMS_CAPTURE_ADM package, then the procedure runs the BUILD procedure automatically. However, the BUILD procedure is not run automatically during capture process creation in the following cases:

• You use CREATE_CAPTURE and specify a non-NULL value for the first_scn parameter. In this case, the specified first SCN must correspond to a previous build.

• You create a downstream capture process that does not use a database link. In this case, the command at the downstream database cannot communicate with the source database to run the BUILD procedure automatically. Therefore, you must run it manually on the source database and specify the first SCN that corresponds to the build during capture process creation.

A capture process requires a LogMiner data dictionary because the information in the primary data dictionary might not apply to the changes being captured from the redo log. These changes might have occurred minutes, hours, or even days before they are captured by a capture process. For example, consider the following scenario:

1. A capture process is configured to capture changes to tables.

2. A database administrator stops the capture process. When the capture process is stopped, it records the SCN of the change it was currently capturing.

3. User applications continue to make changes to the tables while the capture process is stopped.

4. The capture process is restarted three hours after it was stopped.

In this case, to ensure data consistency, the capture process must begin capturing changes in the redo log at the time when it was stopped. The capture process starts capturing changes at the SCN that it recorded when it was stopped.

The redo log contains raw data. It does not contain database object names and column names in tables. Instead, it uses object numbers and internal column numbers for database objects and columns, respectively. Therefore, when a change is captured, a capture process must reference a data dictionary to determine the details of the change.

Because a LogMiner data dictionary might be populated when a capture process is started for the first time, it might take some time to start capturing changes. The amount of time required depends on the number of database objects in the database. You can query the STATE column in the V$STREAMS_CAPTURE dynamic performance view to monitor the progress while a capture process is processing a data dictionary build.

See Also:

• "Capture Process Rule Evaluation"

• "First SCN and Start SCN"

• "Capture Process States"

• Oracle Streams Replication Administrator's Guide for more information about preparing database objects for instantiation

Scenario Illustrating Why a Capture Process Needs a LogMiner Data Dictionary

Consider a scenario in which a capture process has been configured to capture changes to table t1, which has columns a and b, and the following changes are made to this table at three different points in time:

Time 1: Insert values a=7 and b=15.

Time 2: Add column c.

Time 3: Drop column b.

If for some reason the capture process is capturing changes from an earlier time, then the primary data dictionary and the relevant version in the LogMiner data dictionary contain different information. Table 2-2 illustrates how the information in the LogMiner data dictionary is used when the current time is different than the change capturing time.

Table 2-2 Information About Table t1 in the Primary and LogMiner Data Dictionaries

Current Time	Change Capturing Time	Primary Data Dictionary	LogMiner Data Dictionary
1	1	Table t1 has columns a and b.	Table t1 has columns a and b at time 1.
2	1	Table t1 has columns a, b, and c.	Table t1 has columns a and b at time 1.
3	1	Table t1 has columns a and c.	Table t1 has columns a and b at time 1.

Assume that the capture process captures the change resulting from the insert at time 1 when the actual time is time 3. If the capture process used the primary data dictionary, then it might assume that a value of 7 was inserted into column a and a value of 15 was inserted into column c, because those are the two columns for table t1 at time 3 in the primary data dictionary. However, a value of 15 actually was inserted into column b, not column c.

Because the capture process uses the LogMiner data dictionary, the error is avoided. The LogMiner data dictionary is synchronized with the capture process and continues to record that table t1 has columns a and b at time 1. So, the captured change specifies that a value of 15 was inserted into column b.

Multiple Capture Processes for the Same Source Database

If one or more capture processes are capturing changes made to a source database, and you want to create a new capture process that captures changes to the same source database, then the new capture process can either create a new LogMiner data dictionary or share one of the existing LogMiner data dictionaries with one or more other capture processes. Whether a new LogMiner data dictionary is created for a new capture process depends on the setting for the first_scn parameter when you run CREATE_CAPTURE to create a capture process:

• If you specify NULL for the first_scn parameter, then the new capture process attempts to share a LogMiner data dictionary with one or more existing capture processes that capture changes from the same source database. NULL is the default for the first_scn parameter.

• If you specify a non-NULL value for the first_scn parameter, then the new capture process uses a new LogMiner data dictionary that is created when the new capture process is started for the first time.

  Note:

  • When you create a capture process and specify a non-NULL first_scn parameter value, this value should correspond to a data dictionary build in the redo log obtained by running the DBMS_CAPTURE_ADM.BUILD procedure.

  • During capture process creation, if the first_scn parameter is NULL and the start_scn parameter is non-NULL, then an error is raised if the start_scn parameter setting is lower than all of the first SCN values for all existing capture processes.

If multiple LogMiner data dictionaries exist, and you specify NULL for the first_scn parameter during capture process creation, then the new capture process automatically attempts to share the LogMiner data dictionary of one of the existing capture processes that has taken at least one checkpoint. You can view the maximum checkpoint SCN for all existing capture processes by querying the MAX_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.

If multiple LogMiner data dictionaries exist, and you specify a non-NULL value for the first_scn parameter during capture process creation, then the new capture process creates a new LogMiner data dictionary the first time it is started. In this case, before you create the new capture process, you must run the BUILD procedure in the DBMS_CAPTURE_ADM package on the source database. The BUILD procedure generates a corresponding valid first SCN value that you can specify when you create the new capture process. You can find a first SCN generated by the BUILD procedure by running the following query:

COLUMN FIRST_CHANGE# HEADING 'First SCN' FORMAT 999999999
COLUMN NAME HEADING 'Log File Name' FORMAT A50

SELECT DISTINCT FIRST_CHANGE#, NAME FROM V$ARCHIVED_LOG
  WHERE DICTIONARY_BEGIN = 'YES';

This query can return more than one row if the BUILD procedure was run more than once.

The most important factor to consider when deciding whether a new capture process should share an existing LogMiner data dictionary or create a new one is the difference between the maximum checkpoint SCN values of the existing capture processes and the start SCN of the new capture process. If the new capture process shares a LogMiner data dictionary, then it must scan the redo log from the point of the maximum checkpoint SCN of the shared LogMiner data dictionary onward, even though the new capture process cannot capture changes prior to its first SCN. If the start SCN of the new capture process is much higher than the maximum checkpoint SCN of the existing capture process, then the new capture process must scan a large amount of redo data before it reaches its start SCN.

A capture process creates a new LogMiner data dictionary when the first_scn parameter is non-NULL during capture process creation. Follow these guidelines when you decide whether a new capture process should share an existing LogMiner data dictionary or create a new one:

• If one or more maximum checkpoint SCN values is greater than the start SCN you want to specify, and if this start SCN is greater than the first SCN of one or more existing capture processes, then it might be better to share the LogMiner data dictionary of an existing capture process. In this case, you can assume there is a checkpoint SCN that is less than the start SCN and that the difference between this checkpoint SCN and the start SCN is small. The new capture process will begin scanning the redo log from this checkpoint SCN and will catch up to the start SCN quickly.

• If no maximum checkpoint SCN is greater than the start SCN, and if the difference between the maximum checkpoint SCN and the start SCN is small, then it might be better to share the LogMiner data dictionary of an existing capture process. The new capture process will begin scanning the redo log from the maximum checkpoint SCN, but it will catch up to the start SCN quickly.

• If no maximum checkpoint SCN is greater than the start SCN, and if the difference between the highest maximum checkpoint SCN and the start SCN is large, then it might take a long time for the capture process to catch up to the start SCN. In this case, it might be better for the new capture process to create a new LogMiner data dictionary. It will take some time to create the new LogMiner data dictionary when the new capture process is first started, but the capture process can specify the same value for its first SCN and start SCN, and thereby avoid scanning a large amount of redo data unnecessarily.

Figure 2-5 illustrates these guidelines.

Figure 2-5 Deciding Whether to Share a LogMiner Data Dictionary

Description of "Figure 2-5 Deciding Whether to Share a LogMiner Data Dictionary"

Note:

• If you create a capture process using one of the procedures in the DBMS_STREAMS_ADM package, then it is the same as specifying NULL for the first_scn and start_scn parameters in the CREATE_CAPTURE procedure.

• You must prepare database objects for instantiation if a new capture process will capture changes made to these database objects. This requirement holds even if the new capture process shares a LogMiner data dictionary with one or more other capture processes for which these database objects have been prepared for instantiation.

See Also:

• "First SCN and Start SCN"

• "Capture Process Checkpoints"

A New First SCN Value and Purged LogMiner Data Dictionary Information

When you reset the first SCN value for an existing capture process, Oracle automatically purges LogMiner data dictionary information prior to the new first SCN setting. If the start SCN for a capture process corresponds to information that has been purged, then Oracle automatically resets the start SCN to the same value as the first SCN. However, if the start SCN is higher than the new first SCN setting, then the start SCN remains unchanged.

Figure 2-6 shows how Oracle automatically purges LogMiner data dictionary information prior to a new first SCN setting, and how the start SCN is not changed if it is higher than the new first SCN setting.

Figure 2-6 Start SCN Higher than Reset First SCN

Description of "Figure 2-6 Start SCN Higher than Reset First SCN"

Given this example, if the first SCN is reset again to a value higher than the start SCN value for a capture process, then the start SCN no longer corresponds to existing information in the LogMiner data dictionary. Figure 2-7 shows how Oracle resets the start SCN automatically if it is lower than a new first SCN setting.

Figure 2-7 Start SCN Lower than Reset First SCN

Description of "Figure 2-7 Start SCN Lower than Reset First SCN"

As you can see, the first SCN and start SCN for a capture process can continually increase over time, and, as the first SCN moves forward, it might no longer correspond to an SCN established by the DBMS_CAPTURE_ADM.BUILD procedure.

The Oracle Streams Data Dictionary

Propagations and apply processes use a Oracle Streams data dictionary to keep track of the database objects from a particular source database. An Oracle Streams data dictionary is populated whenever one or more database objects are prepared for instantiation at a source database. Specifically, when a database object is prepared for instantiation, it is recorded in the redo log. When a capture process scans the redo log, it uses this information to populate the local Oracle Streams data dictionary for the source database. In the case of local capture, this Oracle Streams data dictionary is at the source database. In the case of downstream capture, this Oracle Streams data dictionary is at the downstream database.

When you prepare a database object for instantiation, you are informing Oracle Streams that information about the database object is needed by propagations that propagate changes to the database object and apply processes that apply changes to the database object. Any database that propagates or applies these changes requires an Oracle Streams data dictionary for the source database where the changes originated.

After an object has been prepared for instantiation, the local Oracle Streams data dictionary is updated when a DDL statement on the object is processed by a capture process. In addition, an internal message containing information about this DDL statement is captured and placed in the queue for the capture process. Propagations can then propagate these internal messages to destination queues at databases.

An Oracle Streams data dictionary is multiversioned. If a database has multiple propagations and apply processes, then all of them use the same Oracle Streams data dictionary for a particular source database. A database can contain only one Oracle Streams data dictionary for a particular source database, but it can contain multiple Oracle Streams data dictionaries if it propagates or applies changes from multiple source databases.

ARCHIVELOG Mode and a Capture Process

The following list describes how different types of capture processes read the redo data:

• A local capture process reads from the redo log buffer whenever possible. If it cannot read from the log buffer, then it reads from the online redo logs. If it cannot read from the log buffer or the online redo logs, then it reads from the archived redo log files. Therefore, the source database must be running in ARCHIVELOG mode when a local capture process is configured to capture changes.

• A real-time downstream capture process reads online redo data from its source database whenever possible and archived redo log files that contain redo data from the source database otherwise. In this case, the redo data from the source database is stored in the standby redo log at the downstream database, and the archiver at the downstream database archives the redo data in the standby redo log. Therefore, both the source database and the downstream database must be running in ARCHIVELOG mode when a real-time downstream capture process is configured to capture changes.

• An archived-log downstream capture process always reads archived redo log files from its source database. Therefore, the source database must be running in ARCHIVELOG mode when an archived-log downstream capture process is configured to capture changes.

You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process. When the capture process is restarted, it scans the redo log from the required checkpoint SCN forward. Therefore, the redo log file that includes the required checkpoint SCN, and all subsequent redo log files, must be available to the capture process.

You must keep an archived redo log file available until you are certain that no capture process will need that file. The first SCN for a capture process can be reset to a higher value, but it cannot be reset to a lower value. Therefore, a capture process will never need the redo log files that contain information prior to its first SCN. Query the DBA_LOGMNR_PURGED_LOG data dictionary view to determine which archived redo log files will never be needed by any capture process.

When a local capture process falls behind, there is a seamless transition from reading an online redo log to reading an archived redo log, and, when a local capture process catches up, there is a seamless transition from reading an archived redo log to reading an online redo log. Similarly, when a real-time downstream capture process falls behind, there is a seamless transition from reading the standby redo log to reading an archived redo log, and, when a real-time downstream capture process catches up, there is a seamless transition from reading an archived redo log to reading the standby redo log.

Capture Process Parameters

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

Capture Process Rule Evaluation

A capture process evaluates changes it finds in the redo log against its positive and negative rule sets. The capture process evaluates a change against the negative rule set first. If one or more rules in the negative rule set evaluate to TRUE for the change, then the change is discarded, but if no rule in the negative rule set evaluates to TRUE for the change, then the change satisfies the negative rule set. When a change satisfies the negative rule set for a capture process, the capture process evaluates the change against its positive rule set. If one or more rules in the positive rule set evaluate to TRUE for the change, then the change satisfies the positive rule set, but if no rule in the positive rule set evaluates to TRUE for the change, then the change is discarded. If a capture process only has one rule set, then it evaluates changes against this one rule set only.

A running capture process completes the following series of actions to capture changes:

1. Finds changes in the redo log.

2. Performs prefiltering of the changes in the redo log. During this step, a capture process evaluates rules in its rule sets at a basic level to place changes found in the redo log into two categories: changes that should be converted into LCRs and changes that should not be converted into LCRs. Prefiltering is done in two phases. In the first phase, information that can be evaluated during prefiltering includes schema name, object name, and command type. If more information is needed to determine whether a change should be converted into an LCR, then information that can be evaluated during the second phase of prefiltering includes tag values and column values when appropriate.

   Prefiltering is a safe optimization done with incomplete information. This step identifies relevant changes to be processed subsequently, such that:

   • A capture process converts a change into an LCR if the change satisfies the capture process rule sets. In this case, proceed to Step 3.

   • A capture process does not convert a change into an LCR if the change does not satisfy the capture process rule sets.

   • Regarding MAYBE evaluations, the rule evaluation proceeds as follows:

     • If a change evaluates to MAYBE against both the positive and negative rule set for a capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.

     • If the change evaluates to FALSE against the negative rule set and MAYBE against the positive rule set for the capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.

     • If the change evaluates to MAYBE against the negative rule set and TRUE against the positive rule set for the capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.

     • If the change evaluates to TRUE against the negative rule set and MAYBE against the positive rule set for the capture process, then the capture process discards the change.

     • If the change evaluates to MAYBE against the negative rule set and FALSE against the positive rule set for the capture process, then the capture process discards the change.

3. Converts changes that satisfy, or might satisfy, the capture process rule sets into LCRs based on prefiltering.

4. Performs LCR filtering. During this step, a capture process evaluates rules regarding information in each LCR to separate the LCRs into two categories: LCRs that should be enqueued and LCRs that should be discarded.

5. Discards the LCRs that should not be enqueued because they did not satisfy the capture process rule sets.

6. Enqueues the remaining captured LCRs into the queue associated with the capture process.

For example, suppose the following rule is defined in the positive rule set for a capture process: Capture changes to the hr.employees table where the department_id is 50. No other rules are defined for the capture process, and the parallelism parameter for the capture process is set to 1.

Given this rule, suppose an UPDATE statement on the hr.employees table changes 50 rows in the table. The capture process performs the following series of actions for each row change:

1. Finds the next change resulting from the UPDATE statement in the redo log.

2. Determines that the change resulted from an UPDATE statement to the hr.employees table and must be captured. If the change was made to a different table, then the capture process ignores the change.

3. Captures the change and converts it into an LCR.

4. Filters the LCR to determine whether it involves a row where the department_id is 50.

5. Either enqueues the LCR into the queue associated with the capture process if it involves a row where the department_id is 50, or discards the LCR if it involves a row where the department_id is not 50 or is missing.

   See Also:

   • "Capture Process Components"

   • Chapter 6, "How Rules Are Used in Oracle Streams" for more information about rule sets for Oracle Streams clients and for information about how messages satisfy rule sets

Figure 2-8 illustrates capture process rule evaluation in a flowchart.

Figure 2-8 Flowchart Showing Capture Process Rule Evaluation

Description of "Figure 2-8 Flowchart Showing Capture Process Rule Evaluation"

Persistent Capture Process Status Upon Database Restart

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

Introduction to Synchronous Capture

Synchronous capture is an optional Oracle Streams client that captures data manipulation language (DML) changes made to tables. Synchronous capture uses an internal mechanism to capture DML changes to specified tables. When synchronous capture is configured to capture changes to tables, the database that contains these tables is called the source database.

When a DML change it made to a table, it can result in changes to one or more rows in the table. Synchronous capture captures each row change and converts it into a specific message format called a row logical change record (row LCR). After capturing a row LCR, synchronous capture enqueues a message containing the row LCR into a queue. Row LCRs created by synchronous capture always contain values for all the columns in a row, even if some of the columns where not modified by the change.

Figure 2-9 shows a synchronous capture capturing LCRs.

Figure 2-9 Synchronous Capture

Description of "Figure 2-9 Synchronous Capture"

Synchronous Capture and Queues

Synchronous capture is always associated with a single ANYDATA queue, and it enqueues messages into this queue only. The queue used by synchronous capture must be a commit-time queue. Commit-time queues ensure that messages are grouped into transactions, and that transactions groups are in commit system change number (CSCN) order. Synchronous capture always enqueues row LCRs into the persistent queue. The persistent queue is the portion of a queue that only stores messages on hard disk in a queue table, not in memory. You can create multiple queues and associate a different synchronous capture with each queue.

Although synchronous capture must enqueue messages into a commit-time queue, messages captured by synchronous capture can be propagated to queues that are not commit-time queues. Therefore, any intermediate queues that store messages captured by synchronous capture do not need to be commit-time queue. Also, apply processes that apply messages captured by synchronous capture can use queues that are not commit-time queues.

Synchronous Capture Rules

Synchronous capture either captures or discards 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. If a rule evaluates to TRUE for a change, and the rule is in the positive rule set for synchronous capture, then synchronous capture captures the change. Synchronous capture does not use negative rule sets.

You can specify synchronous capture rules at the table level. A table rule captures or discards row changes resulting from DML changes to a particular table. Subset rules are table rules that include a subset of the row changes to a particular table. Synchronous capture does not use schema or global rules.

All synchronous capture rules must be created with one of the following procedures in the DBMS_STREAMS_ADM package:

• ADD_TABLE_RULES

• ADD_SUBSET_RULES

Synchronous capture does not capture changes based on the following types of rules:

• Rules added to the synchronous capture rules set by any procedure other than ADD_TABLE_RULES or ADD_SUBSET_RULES in the DBMS_STREAMS_ADM package.

• Rules created by the DBMS_RULE_ADM package.

If these types of rules are in a synchronous capture rule set, then synchronous capture ignores these rules.

A synchronous capture can use a rule set created by the CREATE_RULE_SET procedure in the DBMS_RULE_ADM package, but you must add rules to the rule set with the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure.

If the specified synchronous capture does not exist when you run the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure, then the procedure creates it automatically. You can also use the CREATE_SYNC_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a synchronous capture.

Data Types Captured by Synchronous Capture

When capturing the row changes resulting from DML changes made to tables, synchronous capture can capture changes made to columns of the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• FLOAT

• DATE

• BINARY_FLOAT

• BINARY_DOUBLE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• CHAR

• NCHAR

• UROWID

Synchronous capture does not capture the results of DML changes to columns of the following data types:

• LONG

• LONG RAW

• CLOB

• NCLOB

• BLOB

• BFILE

• ROWID

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

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

In addition, a synchronous capture does not capture the results of DML changes to virtual columns.

Synchronous capture raises an error if it tries to create a row LCR for a DML change to a table containing a column of an unsupported data type. Synchronous capture returns an ORA-25341 error to the user, and the DML change is not made. In this case, modify the rules used by synchronous capture to avoid the error.

Types of Changes Captured by Synchronous Capture

Synchronous capture can capture only certain types of changes made to a database and its objects. The following sections describe the types of changes that can be captured by synchronous capture.

Synchronous Capture and Oracle Real Application Clusters

You can configure synchronous capture to capture changes in an Oracle Real Application Clusters (Oracle RAC) environment. In an Oracle RAC environment, synchronous capture reads changes made by all instances.

For the best performance with synchronous capture in an Oracle RAC environment, changes to independent sets of tables should be captured by separate synchronous captures. For example, if different applications use different sets of database objects in the database, then configure a separate synchronous capture to capture changes to the database objects for each application. In this case, each synchronous capture should use a different queue and queue table.

Synchronous Capture and Transparent Data Encryption

Oracle Streams synchronous captures can capture changes to columns that have been encrypted using transparent data encryption. Encrypted columns in row logical change records (row LCRs) captured by a synchronous capture remain encrypted when the row LCRs are staged in a persistent queue.

Capture User for Synchronous Capture

Changes are captured in the security domain of the capture user for a synchronous capture. The capture user captures all changes that satisfy the synchronous capture rule set. In addition, the capture user runs all custom rule-based transformations specified by the rules in these rule sets. The capture user must have the necessary privileges to perform these actions, including EXECUTE privilege on the rule set used by synchronous capture, EXECUTE privilege on all custom rule-based transformation functions specified for rules in the rule set, and privileges to enqueue messages into the synchronous capture queue. A synchronous capture can be associated with only one user, but one user can be associated with many synchronous captures.

Multiple Synchronous Captures in a Single Database

Oracle recommends that each ANYDATA queue used by a synchronous capture, propagation, or apply process have messages from at most one synchronous capture from a particular source database. Therefore, a separate queue should be used for each synchronous capture that captures changes originating at a particular source database, and each queue should have its own queue table. Also, messages from two or more synchronous captures in the same source database should not be propagated to the same destination queue.

Types of Messages That Can Be Enqueued Explicitly

Applications can create and enqueue different types of messages for various purposes in an Oracle Streams environment. These messages can be messages of a user-defined type called user messages, or they can be LCRs.

This section contains these topics:

• User Messages

• Logical Change Records (LCRs)

Enqueue Features

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

• Enqueue into a buffered queue or a persistent queue

• Ordering of messages by priority enqueue time, or commit time

• Array enqueue of messages

• Correlation identifiers

• Message grouping

• Sender identification

• Time specification and scheduling

Explicit Capture and Transparent Data Encryption

You can use explicit capture to construct and enqueue row logical change records (row LCRs) for column that are encrypted in database tables. However, you cannot specify that columns are encrypted when you construct the LCRs. Therefore, when explicitly captured row LCRs are staged in a queue, all of the columns in the row LCRs are decrypted.