15 Managing Oracle Streams Implicit Capture

Both capture processes and synchronous captures perform implicit capture. This chapter contains instructions for managing implicit capture.

The following topics describe managing Oracle Streams implicit capture:

Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.

See Also:

Managing a Capture Process

A capture process captures changes in a redo log, reformats each captured change into a logical change record (LCR), and enqueues the LCR into an ANYDATA queue.

The following topics describe managing a capture process:

See Also:

Starting a Capture Process

You run the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package to start an existing capture process. For example, the following procedure starts a capture process named strm01_capture:

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => 'strm01_capture');
END;
/

Note:

If a new capture process will use a new LogMiner data dictionary, then, when you first start the new capture process, some time might be required to populate the new LogMiner data dictionary. A new LogMiner data dictionary is created if a non-NULL first SCN value was specified when the capture process was created.

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about starting a capture process with Oracle Enterprise Manager Cloud Control

Stopping a Capture Process

You run the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop an existing capture process. For example, the following procedure stops a capture process named strm01_capture:

BEGIN
  DBMS_CAPTURE_ADM.STOP_CAPTURE(
    capture_name => 'strm01_capture');
END;
/

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about stopping a capture process with Oracle Enterprise Manager Cloud Control

Managing the Rule Set for a Capture Process

This section contains instructions for completing the following tasks:

See Also:

Specifying a Rule Set for a Capture Process

You can specify one positive rule set and one negative rule set for a capture process. The capture process captures a change if it evaluates to TRUE for at least one rule in the positive rule set and evaluates to FALSE for all the rules in the negative rule set. The negative rule set is evaluated before the positive rule set.

Specifying a Positive Rule Set for a Capture Process

You specify an existing rule set as the positive rule set for an existing capture process using the rule_set_name parameter in the ALTER_CAPTURE procedure. This procedure is in the DBMS_CAPTURE_ADM package.

For example, the following procedure sets the positive rule set for a capture process named strm01_capture to strm02_rule_set.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name  => 'strm01_capture',
    rule_set_name => 'strmadmin.strm02_rule_set');
END;
/
Specifying a Negative Rule Set for a Capture Process

You specify an existing rule set as the negative rule set for an existing capture process using the negative_rule_set_name parameter in the ALTER_CAPTURE procedure. This procedure is in the DBMS_CAPTURE_ADM package.

For example, the following procedure sets the negative rule set for a capture process named strm01_capture to strm03_rule_set.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name           => 'strm01_capture',
    negative_rule_set_name => 'strmadmin.strm03_rule_set');
END;
/

Adding Rules to a Rule Set for a Capture Process

To add rules to a rule set for an existing capture process, you can run one of the following procedures in the DBMS_STREAMS_ADM package and specify the existing capture process:

Excluding the ADD_SUBSET_RULES procedure, these procedures can add rules to the positive rule set or negative rule set for a capture process. The ADD_SUBSET_RULES procedure can add rules only to the positive rule set for a capture process.

See Also:

"System-Created Rules"
Adding Rules to the Positive Rule Set for a Capture Process

The following example runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the positive rule set of a capture process named strm01_capture:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      =>  'hr.departments',
    streams_type    =>  'capture',
    streams_name    =>  'strm01_capture',
    queue_name      =>  'strmadmin.streams_queue',
    include_dml     =>  TRUE,
    include_ddl     =>  TRUE,
    inclusion_rule  =>  TRUE);
END;
/

Running this procedure performs the following actions:

  • Creates two rules. One rule evaluates to TRUE for DML changes to the hr.departments table, and the other rule evaluates to TRUE for DDL changes to the hr.departments table. The rule names are system generated.

  • Adds the two rules to the positive rule set associated with the capture process because the inclusion_rule parameter is set to TRUE.

  • Prepares the hr.departments table for instantiation by running the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package.

  • Enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the hr.departments table. When the PREPARE_TABLE_INSTANTIATION procedure is run, the default value (keys) is specified for the supplemental_logging parameter.

If the capture process is performing downstream capture, then the table is prepared for instantiation and supplemental logging is enabled for key columns only if the downstream capture process uses a database link to the source database. If a downstream capture process does not use a database link to the source database, then the table must be prepared for instantiation manually and supplemental logging must be enabled manually.

Adding Rules to the Negative Rule Set for a Capture Process

The following example runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the negative rule set of a capture process named strm01_capture:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      =>  'hr.job_history',
    streams_type    =>  'capture',
    streams_name    =>  'strm01_capture',
    queue_name      =>  'strmadmin.streams_queue',
    include_dml     =>  TRUE,
    include_ddl     =>  TRUE,
    inclusion_rule  =>  FALSE);
END;
/

Running this procedure performs the following actions:

  • Creates two rules. One rule evaluates to TRUE for DML changes to the hr.job_history table, and the other rule evaluates to TRUE for DDL changes to the hr.job_history table. The rule names are system generated.

  • Adds the two rules to the negative rule set associated with the capture process, because the inclusion_rule parameter is set to FALSE.

Removing a Rule from a Rule Set for a Capture Process

You remove a rule from the rule set for a capture process if you no longer want the capture process to capture the changes specified in the rule. For example, assume that the departments3 rule specifies that DML changes to the hr.departments table be captured. If you no longer want a capture process to capture changes to the hr.departments table, then remove the departments3 rule from its rule set.

You remove a rule from a rule set for an existing capture process by running the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package. For example, the following procedure removes a rule named departments3 from the positive rule set of a capture process named strm01_capture.

BEGIN
  DBMS_STREAMS_ADM.REMOVE_RULE(
    rule_name        => 'departments3',
    streams_type     => 'capture',
    streams_name     => 'strm01_capture',
    drop_unused_rule => TRUE,
    inclusion_rule   => TRUE);
END;
/

In this example, the drop_unused_rule parameter in the REMOVE_RULE procedure is set to TRUE, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule parameter is set to FALSE, then the rule is removed from the rule set, but it is not dropped from the database.

If the inclusion_rule parameter is set to FALSE, then the REMOVE_RULE procedure removes the rule from the negative rule set for the capture process, not the positive rule set.

To remove all of the rules in a rule set for the capture process, specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

See Also:

"Oracle Streams Client with One or More Empty Rule Sets"

Removing a Rule Set for a Capture Process

You remove a rule set from an existing capture process using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. This procedure can remove the positive rule set, negative rule set, or both. Specify TRUE for the remove_rule_set parameter to remove the positive rule set for the capture process. Specify TRUE for the remove_negative_rule_set parameter to remove the negative rule set for the capture process.

For example, the following procedure removes both the positive and negative rule set from a capture process named strm01_capture.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name             => 'strm01_capture',
    remove_rule_set          => TRUE,
    remove_negative_rule_set => TRUE);
END;
/

Note:

If a capture process does not have a positive or negative rule set, then the capture process captures all supported changes to all objects in the database, excluding database objects in the SYS, SYSTEM, and CTXSYS schemas.

Setting a Capture Process Parameter

Set a capture process parameter using the SET_PARAMETER procedure in the DBMS_CAPTURE_ADM package. Capture process parameters control the way a capture process operates.

For example, the following procedure sets the parallelism parameter for a capture process named strm01_capture to 4.

BEGIN
  DBMS_CAPTURE_ADM.SET_PARAMETER(
    capture_name => 'strm01_capture',
    parameter    => 'parallelism',
    value        => '4');
END;
/

Note:

  • Setting the parallelism parameter automatically stops and restarts a capture process.

  • The value parameter is always entered as a VARCHAR2 value, even if the parameter value is a number.

  • If the value parameter is set to NULL or is not specified, then the parameter is set to its default value.

See Also:

Setting the Capture User for a Capture Process

The capture user is the user who captures all DML changes and DDL changes that satisfy the capture process rule sets. Set the capture user for a capture process using the capture_user parameter in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

To change the capture user, the user who invokes the ALTER_CAPTURE procedure must be granted DBA role. Only the SYS user can set the capture_user to SYS.

For example, the following procedure sets the capture user for a capture process named strm01_capture to hr.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    capture_user => 'hr');
END;
/

Running this procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue. In addition, ensure that the capture user has the following privileges:

These privileges can be granted to the capture user directly or through roles.

In addition, the capture user must be granted EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the capture process. These privileges must be granted directly to the capture user. They cannot be granted through roles.

Note:

If Oracle Database Vault is installed, then the user who changes the capture user must be granted the BECOME USER system privilege. Granting this privilege to the user is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after capture user is changed, if necessary.

Managing the Checkpoint Retention Time for a Capture Process

The checkpoint retention time is the amount of time that a capture process retains checkpoints before purging them automatically.

Set the checkpoint retention time for a capture process using checkpoint_retention_time parameter in the ALTER_CAPTURE procedure of the DBMS_CAPTURE_ADM package.

This section contains these topics:

See Also:

Setting the Checkpoint Retention Time for a Capture Process to a New Value

When you set the checkpoint retention time, you can specify partial days with decimal values. For example, run the following procedure to specify that a capture process named strm01_capture should purge checkpoints automatically every ten days and twelve hours:

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name              => 'strm01_capture',
    checkpoint_retention_time => 10.5);
END;
/

Setting the Checkpoint Retention Time for a Capture Process to Infinite

To specify that a capture process should not purge checkpoints automatically, set the checkpoint retention time to DBMS_CAPTURE_ADM.INFINITE. For example, the following procedure sets the checkpoint retention time for a name strm01_capture to infinite:

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name              => 'strm01_capture',
    checkpoint_retention_time => DBMS_CAPTURE_ADM.INFINITE);
END;
/

Adding an Archived Redo Log File to a Capture Process Explicitly

You can add an archived redo log file to a capture process manually using the following statement:

ALTER DATABASE REGISTER LOGICAL LOGFILE 
   file_name FOR capture_process;

Here, file_name is the name of the archived redo log file being added, and capture_process is the name of the capture process that will use the redo log file at the downstream database. The capture_process is equivalent to the logminer_session_name and must be specified. The redo log file must be present at the site running capture process.

For example, to add the /usr/log_files/1_3_486574859.dbf archived redo log file to a capture process named strm03_capture, issue the following statement:

ALTER DATABASE REGISTER LOGICAL LOGFILE '/usr/log_files/1_3_486574859.dbf' 
  FOR 'strm03_capture';

See Also:

Oracle Database SQL Language Reference for more information about the ALTER DATABASE statement and Oracle Data Guard Concepts and Administration for more information registering redo log files

Setting the First SCN for an Existing Capture Process

You can set the first SCN for an existing capture process.

The specified first SCN must meet the following requirements:

  • It must be greater than the current first SCN for the capture process.

  • It must be less than or equal to the current applied SCN for the capture process. However, this requirement does not apply if the current applied SCN for the capture process is zero.

  • It must be less than or equal to the required checkpoint SCN for the capture process.

You can determine the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN
   FROM DBA_CAPTURE;

When you reset a first SCN for a capture process, information below the new first SCN setting is purged from the LogMiner data dictionary for the capture process automatically. Therefore, after the first SCN is reset for a capture process, the start SCN for the capture process cannot be set lower than the new first SCN. Also, redo log files that contain information before the new first SCN setting will never be needed by the capture process.

For example, the following procedure sets the first SCN for a capture process named strm01_capture to 351232 using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package:

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    first_scn    => 351232);
END;
/

Note:

  • If the specified first SCN is higher than the current start SCN for the capture process, then the start SCN is set automatically to the new value of the first SCN.

  • If you must capture changes in the redo log from a point in time in the past, then you can create a capture process and specify a first SCN that corresponds to a previous data dictionary build in the redo log. The BUILD procedure in the DBMS_CAPTURE_ADM package performs a data dictionary build in the redo log.

  • You can query the DBA_LOGMNR_PURGED_LOG data dictionary view to determine which redo log files will never be needed by any capture process.

See Also:

Setting the Start SCN for an Existing Capture Process

You can set the start SCN for an existing capture process. Typically, you reset the start SCN for a capture process if point-in-time recovery must be performed on one of the destination databases that receive changes from the capture process.

The specified start SCN must be greater than or equal to the first SCN for the capture process. When you reset a start SCN for a capture process, ensure that the required redo log files are available to the capture process.

You can determine the first SCN for each capture process in a database using the following query:

SELECT CAPTURE_NAME, FIRST_SCN FROM DBA_CAPTURE;

For example, to set the start SCN for a capture process named strm01_capture to 750338, complete the following steps:

  1. Stop the capture process. See "Stopping a Capture Process" for more information.

  2. Run the ALTER_CAPTURE procedure to set the start SCN:

    BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    start_scn    => 750338);
END;
/

  3. Start the capture process. See "Starting a Capture Process" for more information.

See Also:

Specifying Whether Downstream Capture Uses a Database Link

You specify whether an existing downstream capture process uses a database link to the source database for administrative purposes using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. Set the use_database_link parameter to TRUE to specify that the downstream capture process uses a database link, or you set the use_database_link parameter to FALSE to specify that the downstream capture process does not use a database link.

If you want a capture process that is not using a database link currently to begin using a database link, then specify TRUE for the use_database_link parameter. In this case, a database link with the same name as the global name as the source database must exist at the downstream database.

If you want a capture process that is using a database link currently to stop using a database link, then specify FALSE for the use_database_link parameter. In this case, some administration must be performed manually after you alter the capture process. For example, if you add new capture process rules using the DBMS_STREAMS_ADM package, then you must prepare the objects relating to the rules for instantiation manually at the source database.

If you specify NULL for the use_database_link parameter, then the current value of this parameter for the capture process is not changed.

To create a database link to the source database dbs1.example.com and specify that this capture process uses the database link, complete the following steps:

  1. In SQL*Plus, connect to the downstream database as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

  2. Create the database link to the source database. Ensure that the database link connects to the Oracle Streams administrator at the source database. For example:

    CREATE DATABASE LINK dbs1.example.com CONNECT TO strmadmin 
   IDENTIFIED BY password 
   USING 'dbs1.example.com';

  3. Alter the capture process to use the database link. For example:

    BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name       => 'strm05_capture',
    use_database_link  => TRUE);
END;
/

See Also:

"Local Capture and Downstream Capture"

Dropping a Capture Process

You run the DROP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to drop an existing capture process. For example, the following procedure drops a capture process named strm02_capture:

BEGIN
  DBMS_CAPTURE_ADM.DROP_CAPTURE(
    capture_name          => 'strm02_capture',
    drop_unused_rule_sets => TRUE);
END;
/

Because the drop_unused_rule_sets parameter is set to TRUE, this procedure also drops any rule sets used by the strm02_capture capture process, unless a rule set is used by another Oracle Streams client. If the drop_unused_rule_sets parameter is set to TRUE, then both the positive rule set and negative rule set for the capture process might be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.

Note:

The status of a capture process must be DISABLED or ABORTED before it can be dropped. You cannot drop an ENABLED capture process.

Managing a Synchronous Capture

A synchronous capture uses an internal mechanism to capture data manipulation language (DML) changes made to tables. A synchronous capture reformats each captured change into a logical change record (LCR), and enqueues the LCR into an ANYDATA queue.

This section contains these topics:

See Also:

Managing the Rule Set for a Synchronous Capture

This section contains instructions for completing the following tasks:

See Also:

Specifying a Rule Set for a Synchronous Capture

You can specify one positive rule set for a synchronous capture. The synchronous capture captures a change if it evaluates to TRUE for at least one rule in the positive rule set.

You specify an existing rule set as the positive rule set for an existing synchronous capture using the rule_set_name parameter in the ALTER_SYNC_CAPTURE procedure. This procedure is in the DBMS_CAPTURE_ADM package.

For example, the following procedure sets the positive rule set for a synchronous capture named sync_capture to sync_rule_set.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_SYNC_CAPTURE(
    capture_name  => 'sync_capture',
    rule_set_name => 'strmadmin.sync_rule_set');
END;
/

Note:

You cannot remove the rule set for a synchronous capture.

Adding Rules to a Rule Set for a Synchronous Capture

To add rules to a rule set for an existing synchronous capture, you can run one of the following procedures in the DBMS_STREAMS_ADM package and specify the existing synchronous capture:

See Also:

"System-Created Rules"

The following example runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the positive rule set of a synchronous capture named sync_capture:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      =>  'hr.departments',
    streams_type    =>  'sync_capture',
    streams_name    =>  'sync_capture',
    queue_name      =>  'strmadmin.streams_queue',
    include_dml     =>  TRUE);
END;
/

Running this procedure performs the following actions:

  • Creates one rule which evaluates to TRUE for DML changes to the hr.departments table. The rule name is system generated.

  • Adds the rule to the positive rule set associated with the synchronous capture.

  • Prepares the hr.departments table for instantiation by running the PREPARE_SYNC_INSTANTIATION function in the DBMS_CAPTURE_ADM package.

Note:

  • A synchronous capture captures changes to a table only if the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure was used to add the rule or rules for the table to the synchronous capture rule set. Synchronous capture does not capture changes to a table if a table or subset rule is added to its rule set using the ADD_RULE procedure in the DBMS_RULE_ADM package. In addition, a synchronous capture ignores all non-table and non-subset rules in its rule set, including global and schema rules.

  • When the ADD_TABLE_RULES or the ADD_SUBSET_RULES procedure adds rules to a synchronous capture rule set, the procedure must obtain an exclusive lock on the specified table. If there are outstanding transactions on the specified table, then the procedure waits until it can obtain a lock.

Removing a Rule from a Rule Set for a Synchronous Capture

You remove a rule from the rule set for a synchronous capture if you no longer want the synchronous capture to capture the changes specified in the rule. For example, assume that the departments3 rule specifies that DML changes to the hr.departments table be captured. If you no longer want a synchronous capture to capture changes to the hr.departments table, then remove the departments3 rule from its rule set.

You remove a rule from a rule set for an existing synchronous capture by running the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package. For example, the following procedure removes a rule named departments3 from the positive rule set of a synchronous capture named sync_capture.

BEGIN
  DBMS_STREAMS_ADM.REMOVE_RULE(
    rule_name        => 'departments3',
    streams_type     => 'sync_capture',
    streams_name     => 'sync_capture',
    drop_unused_rule => TRUE);
END;
/

In this example, the drop_unused_rule parameter in the REMOVE_RULE procedure is set to TRUE, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule parameter is set to FALSE, then the rule is removed from the rule set, but it is not dropped from the database.

To remove all of the rules in a rule set for the synchronous capture, specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

See Also:

"Oracle Streams Client with One or More Empty Rule Sets"

Setting the Capture User for a Synchronous Capture

The capture user is the user who captures all DML changes that satisfy the synchronous capture rule set. Set the capture user for a synchronous capture using the capture_user parameter in the ALTER_SYNC_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

To change the capture user, the user who invokes the ALTER_SYNC_CAPTURE procedure must be granted DBA role. Only the SYS user can set the capture_user to SYS.

For example, the following procedure sets the capture user for a synchronous capture named sync_capture to hr.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_SYNC_CAPTURE(
    capture_name => 'sync_capture',
    capture_user => 'hr');
END;
/

Running this procedure grants the new capture user enqueue privilege on the queue used by the synchronous capture and configures the user as a secure queue user of the queue. In addition, ensure that the capture user has the following privileges:

These privileges can be granted to the capture user directly or through roles.

In addition, the capture user must be granted EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the synchronous capture. These privileges must be granted directly to the capture user. They cannot be granted through roles.

Note:

If Oracle Database Vault is installed, then the user who changes the capture user must be granted the BECOME USER system privilege. Granting this privilege to the user is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after capture user is changed, if necessary.

Dropping a Synchronous Capture

You run the DROP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to drop an existing synchronous capture. For example, the following procedure drops a synchronous capture named sync_capture:

BEGIN
  DBMS_CAPTURE_ADM.DROP_CAPTURE(
    capture_name          => 'sync_capture',
    drop_unused_rule_sets => TRUE);
END;
/

Because the drop_unused_rule_sets parameter is set to TRUE, this procedure also drops any rule sets used by the sync_capture synchronous capture, unless a rule set is used by another Oracle Streams client. If the drop_unused_rule_sets parameter is set to TRUE, then the rule set for the synchronous capture might be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.

Managing Extra Attributes in Captured LCRs

You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process or a synchronous capture to capture one or more extra attributes. You can also use this procedure to instruct a capture process or synchronous capture to exclude an extra attribute that it is capturing currently.

The extra attributes are the following:

  • row_id (row LCRs only)

  • serial#

  • session#

  • thread#

  • tx_name

  • username

This section contains instructions for completing the following tasks:

See Also:

Including Extra Attributes in Implicitly Captured LCRs

To include an extra attribute in the LCRs captured by a capture process or synchronous capture, run the INCLUDE_EXTRA_ATTRIBUTES procedure, and set the include parameter to TRUE. For example, to instruct a capture process or synchronous capture named strm01_capture to include the transaction name in each LCR that it captures, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'strm01_capture',
    attribute_name => 'tx_name',
    include        => TRUE);
END;
/

Excluding Extra Attributes from Implicitly Captured LCRs

To exclude an extra attribute from the LCRs captured by a capture process or synchronous capture, run the INCLUDE_EXTRA_ATTRIBUTES procedure, and set the include parameter to FALSE. For example, to instruct a capture process or synchronous capture named strm01_capture to exclude the transaction name from each LCR that it captures, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'strm01_capture',
    attribute_name => 'tx_name',
    include        => FALSE);
END;
/

Switching From a Capture Process to a Synchronous Capture

This section describes how to switch from a capture process to a synchronous capture. Typically, a synchronous capture is used to capture data manipulation language (DML) changes to a relatively small number of tables. You might decide to make this switch if you are currently capturing changes to a small number of tables with a capture process instead of a synchronous capture.

You should not switch from a capture process to a synchronous capture if any of the following conditions are true:

  • Instead of capturing the changes made to a small number of tables, the capture process is capturing changes made to an entire database, one or more schemas, or a large number of tables, and you want to continue to capture these changes.

  • The capture process is capturing data definition language (DDL) changes, and you want to continue to capture DDL changes. A synchronous capture cannot capture DDL changes.

  • The capture process uses a negative rule set, and you want to continue to use a negative rule set. A synchronous capture cannot use negative rule set.

  • The capture process is a downstream capture process. Downstream capture is not possible with a synchronous capture.

This section uses an example to describe how to switch from a capture process to a synchronous capture. Table 15-1 shows the Oracle Streams components in the sample environment before the switch and after the switch.

Table 15-1 Sample Switch From a Capture Process to a Synchronous Capture

Oracle Streams Component Before Switch After Switch

Capture Process

cap_proc

None

Capture Process Rule Set

cap_rules

None

Synchronous Capture

None

sync_cap

Synchronous Capture Rule Set

None

cap_rules

Propagation

cap_proc_prop

sync_cap_prop

Propagation Rule Set

prop_rules

prop_rules

Source Queue

cap_proc_source

sync_cap_source

Destination Queue

cap_proc_dest

sync_cap_dest

Apply Process

apply_cap_proc

apply_sync_cap

Apply Process Rule Set

apply_rules

apply_rules

In Table 15-1, notice that the Oracle Streams environment uses the same rule sets before the switch and after the switch. Also, for the example in this section, assume that the source database is db1.example.com and the destination database is db2.example.com.

Note:

The example in this section assumes that the Oracle Streams environment only involves two databases. If you are using a directed network to send changes through multiple databases, then you might need to configure additional propagations and queues for the new synchronous capture stream of changes, and you might need to drop additional propagations and queues that were used by the capture process stream.

To switch from a capture process to a synchronous capture, complete the following steps:

  1. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

    This example assumes that the Oracle Streams administrator is strmadmin at each database. See Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator.

  2. Stop the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.STOP_CAPTURE(
    capture_name => 'cap_proc');
END;
/

  3. In SQL*Plus, log in to the destination database as the Oracle Streams administrator.

  4. Create a commit-time queue for the apply process that will apply the changes that were captured by the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.sync_cap_dest_qt',
    queue_name  => 'strmadmin.sync_cap_dest');
END;
/

  5. Create an apply process that applies the changes in the queue created in Step 4. Ensure that the apply_captured parameter is set to FALSE. Also, ensure that the rule_set_name parameter specifies the rule set used by the existing apply process.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'strmadmin.sync_cap_dest',
    apply_name     => 'apply_sync_cap',
    rule_set_name  => 'strmadmin.apply_rules',
    apply_captured => FALSE);
END;
/

    Ensure that the apply process is configured properly for your environment. Specifically, ensure that the new apply process is configured properly regarding the following items:

    • Apply user

    • Apply handlers

    • Apply tag

    If appropriate, then ensure that the new apply process is configured in the same way as the existing apply process regarding these items.

    See Oracle Streams Replication Administrator's Guide for information about creating an apply process.

  6. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

  7. Create a commit-time queue for the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.sync_cap_source_qt',
    queue_name  => 'strmadmin.sync_cap_source');
END;
/

    See Oracle Streams Replication Administrator's Guide for information about configuring queues.

  8. Create a propagation that sends changes from the queue created in Step 7 to the queue created in Step 4. Ensure that the rule_set_name parameter specifies the rule set used by the existing propagation.

    In this example, run the following procedure:

    BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'sync_cap_prop',
    source_queue       => 'strmadmin.sync_cap_source',
    destination_queue  => 'strmadmin.sync_cap_dest',
    destination_dblink => 'db2.example.com',
    rule_set_name      => 'strmadmin.prop_rules');
END;
/

    See Oracle Streams Replication Administrator's Guide for information about creating propagations.

  9. Create a synchronous capture. Ensure that the queue_name parameter specifies the queue created in Step 7. Also, ensure that the rule_set_name parameter specifies the rule set used by the existing capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.CREATE_SYNC_CAPTURE(
    queue_name    => 'strmadmin.sync_cap_source',
    capture_name  => 'sync_cap',
    rule_set_name => 'strmadmin.capture_rules');
END;
/

    The specified rule set must only contain rules that were created using the ADD_TABLE_RULES and ADD_SUBSET_RULES procedures in the DBMS_STREAMS_ADM package. If the current capture process rule set contains other types of rules, then create a rule set for the synchronous capture and use the ADD_TABLE_RULES and ADD_SUBSET_RULES procedures to add rules to the new rule set.

    In addition, a synchronous capture cannot have a negative rule set. If the current capture process has a negative rule set, and you want the synchronous capture to behave the same as the capture process, then add rules to the positive synchronous capture rule set that result in the same behavior.

    If the existing capture process uses a capture user that is not the Oracle Streams administrator, then ensure that you use the capture_user parameter in the CREATE_SYNC_CAPTURE procedure to specify the correct capture user for the new synchronous capture.

    See Oracle Streams Replication Administrator's Guide for information about configuring synchronous capture.

  10. Verify that the tables that are configured for synchronous capture are the same as the ones configured for the existing capture process by running the following query:

    SELECT * FROM DBA_SYNC_CAPTURE_TABLES ORDER BY TABLE_OWNER, TABLE_NAME;

    If any table is missing or not enabled, then use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure to add the table.

  11. Prepare the replicated tables for instantiation. The replicated tables are the tables for which the synchronous capture captures changes.

    For example, if the synchronous capture captures changes to the hr.employees and hr.departments tables, then run the following function:

    SET SERVEROUTPUT ON
DECLARE
  tables       DBMS_UTILITY.UNCL_ARRAY;
  prepare_scn  NUMBER;
  BEGIN
    tables(1) := 'hr.departments';
    tables(2) := 'hr.employees';
    prepare_scn := DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION(
                      table_names => tables);
  DBMS_OUTPUT.PUT_LINE('Prepare SCN = ' || prepare_scn);
END;
/

    The returned prepare system change number (SCN) is used in steps later in this procedure. This example assumes that the prepare SCN is 2700000.

    All of the replicated tables must be included in one call to the PREPARE_SYNC_INSTANTIATION function.

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

  12. In SQL*Plus, log in to the destination database as the Oracle Streams administrator.

  13. Set the apply process that applies changes from the capture process to stop applying changes when it reaches the SCN returned in Step 11 plus 1.

    For example, if the prepare SCN is 2700000, then run the following procedure to set the maximum_scn parameter to 2700001 (2700000 + 1).:

    BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name   => 'apply_cap_proc',
    parameter    => 'maximum_scn',
    value        => '2700001');
END;
/

  14. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

  15. Start the capture process that you stopped in Step 2.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => 'cap_proc');
END;
/

  16. In SQL*Plus, log in to the destination database as the Oracle Streams administrator.

  17. Wait until the apply process that applies changes that were captured by the capture process has reached the SCN specified in Step 13. When this event occurs, the apply process is automatically disabled with error ORA-26717 to indicate the SCN limit has reached.

    To determine if the apply process has reached this point, query the DBA_APPLY view. In this example, run the following query:

    SELECT 1 FROM DBA_APPLY 
   WHERE STATUS       = 'DISABLED' AND
         ERROR_NUMBER = 26717 AND 
         APPLY_NAME   = 'APPLY_CAP_PROC';

    Do not proceed to the next step until this query returns a row.

  18. Set the instantiation SCN for the replicated tables to the SCN value the SCN returned in Step 11.

    In this example, run the following procedures:

    BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.employees',
    source_database_name  => 'db1.example.com',
    instantiation_scn     => 2700000);
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.departments',
    source_database_name  => 'db1.example.com',
    instantiation_scn     => 2700000);
END;
/

    See Oracle Streams Replication Administrator's Guide for more information about setting the instantiation SCN.

  19. Start the apply process that you created in Step 5.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_sync_cap');
END;
/

  20. Drop the apply process that applied changes that were captured by the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.DROP_APPLY(
    apply_name => 'apply_cap_proc');
END;
/

  21. If it is no longer needed, then drop the queue that was used by the apply process that you dropped in Step 20.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.REMOVE_QUEUE(
    queue_name  => 'strmadmin.cap_proc_dest',
    drop_unused_queue_table => TRUE);
END;
/

  22. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

  23. Stop the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.STOP_CAPTURE(
    capture_name => 'cap_proc');
END;
/

  24. Drop the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.DROP_CAPTURE(
    capture_name => 'cap_proc');
END;
/

  25. Drop the propagation that sent changes that were captured by the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
    propagation_name => 'cap_proc_prop');
END;
/

  26. If it is no longer needed, then drop the queue that was used by the capture process and propagation that you dropped in Steps 24 and 25.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.REMOVE_QUEUE(
    queue_name  => 'strmadmin.cap_proc_source',
    drop_unused_queue_table => TRUE);
END;
/

If you have a bi-directional replication environment, then you can perform these steps independently to switch from a capture process to synchronous capture in both directions.

See Also:

Switching from a Synchronous Capture to a Capture Process

This section describes how to switch from a synchronous capture to a capture process. You might decide to make this switch for one or more of the following reasons:

  • You are currently capturing changes to a small number of tables but want to expand your environment to capture changes to a large number of tables, one or more schemas, or an entire database.

  • You want to use a negative rule set during change capture.

  • You want to capture data definition language (DDL) changes to database objects.

This section uses an example to describe how to switch from a synchronous capture to a capture process. Table 15-2 shows the Oracle Streams components in the sample environment before the switch and after the switch.

Table 15-2 Sample Switch From a Synchronous Capture to a Capture Process

Oracle Streams Component Before Switch After Switch

Synchronous Capture

sync_proc

None

Synchronous Capture Rule Set

cap_rules

None

Capture Process

None

cap_proc

Capture Process Rule Set

None

cap_rules

Propagation

sync_cap_prop

cap_proc_prop

Propagation Rule Set

prop_rules

prop_rules

Source Queue

sync_cap_source

cap_proc_source

Destination Queue

sync_cap_dest

cap_proc_dest

Apply Process

apply_sync_cap

apply_cap_proc

Apply Process Rule Set

apply_rules

apply_rules

In Table 15-2, notice that the Oracle Streams environment uses the same rule sets before the switch and after the switch. Also, for the example in this section, assume that the source database is db1.example.com and the destination database is db2.example.com.

Note:

The example in this section assumes that the Oracle Streams environment only involves two databases. If you are using a directed network to send changes through multiple databases, then you might need to configure additional propagations and queues for the new capture process stream of changes, and you might need to drop additional propagations and queues that were used by the synchronous capture stream.

To switch from a synchronous capture to a capture process, complete the following steps:

  1. Ensure that the source database is running in ARCHIVELOG mode. See "ARCHIVELOG Mode and a Capture Process" and Oracle Database Administrator's Guide for more information.

  2. In SQL*Plus, log in to the destination database as the Oracle Streams administrator.

    This example assumes that the Oracle Streams administrator is strmadmin at each database. See Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator.

  3. Create the queue for the apply process that will apply the changes that were captured by the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.cap_proc_dest_qt',
    queue_name  => 'strmadmin.cap_proc_dest');
END;
/

    See Oracle Streams Replication Administrator's Guide for information about configuring queues.

  4. Create an apply process that applies the changes in the queue created in Step 3. Ensure that the apply_captured parameter is set to TRUE. Also, ensure that the rule_set_name parameter specifies the rule set used by the existing apply process.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'strmadmin.cap_proc_dest',
    apply_name     => 'apply_cap_proc',
    rule_set_name  => 'strmadmin.apply_rules',
    apply_captured => TRUE);
END;
/

    Ensure that the apply process is configured properly for your environment. Specifically, ensure that the new apply process is configured properly regarding the following items:

    • Apply user

    • Apply handlers

    • Apply tag

    If appropriate, then ensure that the new apply process is configured in the same way as the existing apply process regarding these items.

    See Oracle Streams Replication Administrator's Guide for information about creating an apply process.

  5. Stop the apply process that applies changes captured by the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.STOP_APPLY(
    apply_name => 'apply_sync_cap');
END;
/

  6. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

  7. Create the queue for the capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.cap_proc_source_qt',
    queue_name  => 'strmadmin.cap_proc_source');
END;
/

  8. Create a propagation that sends changes from the queue created in Step 7 to the queue created in Step 3. Ensure that the rule_set_name parameter specifies the rule set used by the existing propagation.

    In this example, run the following procedure:

    BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'cap_proc_prop',
    source_queue       => 'strmadmin.cap_proc_source',
    destination_queue  => 'strmadmin.cap_proc_dest',
    destination_dblink => 'db2.example.com',
    rule_set_name      => 'strmadmin.prop_rules');
END;
/

    See Oracle Streams Replication Administrator's Guide for information about creating propagations.

  9. Create a capture process. Ensure that the parameters are set properly in the CREATE_CAPTURE procedure:

    • Set the queue_name parameter to the queue created in Step 7.

    • Set the rule_set_name parameter to the rule set used by the existing synchronous capture.

    • If the existing synchronous capture uses a capture user that is not the Oracle Streams administrator, then set the capture_user parameter to the correct capture user for the new capture process.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name    => 'strmadmin.cap_proc_source',
    capture_name  => 'cap_proc',
    rule_set_name => 'strmadmin.cap_rules');
END;
/

    See Oracle Streams Replication Administrator's Guide for more information about configuring a capture process.

  10. Prepare the replicated tables for instantiation. The replicated tables are the tables for which the capture process captures changes.

    For example, if the capture process captures changes to the hr.employees and hr.departments tables, then run the following procedures:

    BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name           => 'hr.employees',
    supplemental_logging => 'keys');
END;
/

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name           => 'hr.departments',
    supplemental_logging => 'keys');
END;
/

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

  11. Lock all of the replicated tables in SHARE MODE.

    In this example, run the following SQL statement:

    LOCK TABLE hr.employees, hr.departments IN SHARE MODE;

  12. Determine the current system change number (SCN) by running the following query:

    SELECT CURRENT_SCN FROM V$DATABASE;

    The returned switch SCN is used in later steps in this procedure. This example assumes that the switch SCN is 2700000.

  13. Run a COMMIT statement to release the lock on the replicated tables:

    COMMIT;

  14. In SQL*Plus, log in to the destination database as the Oracle Streams administrator.

  15. Set the apply process that applies changes from the synchronous capture to stop applying changes when it reaches the SCN returned in Step 12 plus 1.

    For example, if the switch SCN is 2700000, then run the following procedure to set the maximum_scn parameter to 2700001 (2700000 + 1):

    BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name   => 'apply_sync_cap',
    parameter    => 'maximum_scn',
    value        => '2700001');
END;
/

  16. Start the apply process that applies changes from the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_sync_cap');
END;
/

  17. Wait until the apply process that applies changes that were captured by the synchronous capture has reached the SCN specified in Step 15. When this event occurs, the apply process is automatically disabled with error ORA-26717 to indicate the SCN limit has reached.

    To determine if the apply process has reached this point, query the DBA_APPLY view. In this example, run the following query:

    SELECT 1 FROM DBA_APPLY 
   WHERE STATUS       = 'DISABLED' AND
         ERROR_NUMBER = 26717 AND 
         APPLY_NAME   = 'APPLY_SYNC_CAP';

    Do not proceed to the next step until this query returns a row.

  18. Set the instantiation SCN for the replicated tables to the SCN value returned in Step 12.

    In this example, run the following procedures:

    BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.employees',
    source_database_name  => 'db1.example.com',
    instantiation_scn     => 2700000);
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.departments',
    source_database_name  => 'db1.example.com',
    instantiation_scn     => 2700000);
END;
/

    See Oracle Streams Replication Administrator's Guide for more information about setting the instantiation SCN.

  19. Start the apply process that you created in Step 4.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_cap_proc');
END;
/

  20. Drop the apply process that applied changes that were captured by the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.DROP_APPLY(
    apply_name => 'apply_sync_cap');
END;
/

  21. If it is no longer needed, then drop the queue that was used by the apply process that you dropped in Step 20.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.REMOVE_QUEUE(
    queue_name              => 'strmadmin.sync_cap_dest',
    drop_unused_queue_table => TRUE);
END;
/

  22. In SQL*Plus, log in to the source database as the Oracle Streams administrator.

  23. Start the capture process that you created in Step 9.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => 'cap_proc');
END;
/

  24. Drop the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_CAPTURE_ADM.DROP_CAPTURE(
    capture_name => 'sync_cap');
END;
/

  25. Drop the propagation that sent changes that were captured by the synchronous capture.

    In this example, run the following procedure:

    BEGIN
  DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
    propagation_name => 'sync_cap_prop');
END;
/

  26. If it is no longer needed, then drop the queue that was used by the synchronous capture and propagation that you dropped in Steps 24 and 25.

    In this example, run the following procedure:

    BEGIN
  DBMS_STREAMS_ADM.REMOVE_QUEUE(
    queue_name              => 'strmadmin.sync_cap_source',
    drop_unused_queue_table => TRUE);
END;
/

If you have a bi-directional replication environment, then you can perform these steps independently to switch from a synchronous capture to a capture process in both directions.

See Also: