11
Managing a Capture Process

A capture process captures changes in a redo log, reformats the captured changes into logical change records (LCRs), and enqueues the LCRs into a Streams queue.

This chapter contains these topics:

• Creating a Capture Process
• Starting a Capture Process
• Specifying the Rule Set for a Capture Process
• Adding Rules to the Rule Set for a Capture Process
• Removing a Rule from the Rule Set for a Capture Process
• Removing the Rule Set for a Capture Process
• Setting a Capture Process Parameter
• Specifying Supplemental Logging at a Source Database
• Setting the Start SCN for a Capture Process
• Preparing Database Objects for Instantiation at a Source Database
• Aborting Preparation for Instantiation at a Source Database
• Stopping a Capture Process
• Dropping a Capture Process

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

See Also: Chapter 2, "Streams Capture Process" "Configuring a Streams Administrator"

Creating a Capture Process

You can use any of the following procedures to create a capture process:

• DBMS_STREAMS_ADM.ADD_TABLE_RULES
• DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
• DBMS_STREAMS_ADM.ADD_GLOBAL_RULES
• DBMS_CAPTURE_ADM.CREATE_CAPTURE

Each of the procedures in the DBMS_STREAMS_ADM package creates a capture process with the specified name if it does not already exist, creates a rule set for the capture process if the capture process does not have a rule set, and may add table, schema, or global rules to the rule set.

The CREATE_CAPTURE procedure creates a capture process, but does not create a rule set or rules for the capture process. However, the CREATE_CAPTURE procedure enables you to specify an existing rule set to associate with the capture process and a start SCN for the capture process.

The following tasks must be completed before you create a capture process:

• Complete the tasks described in "Configuring a Database to Run a Streams Capture Process".
• Create a Streams queue to associate with the capture process, if one does not exist. See "Creating a Streams Queue" for instructions.

  Note: Creation of the first capture process in a database may take some time because the data dictionary is duplicated during this creation.

Example of Creating a Capture Process Using DBMS_STREAMS_ADM

The following is an example that runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to create a capture process:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name         => 'hr.employees',
    streams_type       => 'capture',
    streams_name       => 'strm01_capture',
    queue_name         => 'strm01_queue',
    include_dml        => true,
    include_ddl        => true,
    include_tagged_lcr => false);
END;
/

Running this procedure performs the following actions:

• Creates a capture process named strm01_capture. The capture process is created only if it does not already exist. If a new capture process is created, then this procedure also sets the start SCN to the point in time of creation.
• Associates the capture process with an existing queue named strm01_queue
• Creates a rule set and associates it with the capture process, if the capture process does not have a rule set. The rule set uses the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context. The rule set name is specified by the system.
• Creates two rules. One rule specifies that the capture process captures DML changes to the hr.employees table, and the other rule specifies that the capture process captures DDL changes to the hr.employees table. The rule names are specified by the system.
• Adds the two rules to the rule set associated with the capture process
• Specifies that the capture process captures a change in the redo log only if the change has a NULL tag, because the include_tagged_lcr parameter is set to false. This behavior is accomplished through the system-created rules for the capture process.

  See Also: "Capture Process Creation" "System-Created Rules" "Tags and Rules Created by the DBMS_STREAMS_ADM Package"

Example of Creating a Capture Process Using DBMS_CAPTURE_ADM

The following is an example that runs the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a capture process:

BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name    => 'strm01_queue',
    capture_name  => 'strm02_capture',
    rule_set_name => 'strmadmin.strm01_rule_set',
    start_scn     => 829381993);
END;
/

Running this procedure performs the following actions:

• Creates a capture process named strm02_capture. A capture process with the same name must not exist.
• Associates the capture process with an existing queue named strm01_queue
• Associates the capture process with an existing rule set named strm01_rule_set
• Specifies 829381993 as the start SCN for the capture process.

  See Also: "Capture Process Creation" "The Start SCN for a Capture Process"

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;
/

Specifying the Rule Set for a Capture Process

You specify an existing rule set that you want to associate with an existing capture process using the rule_set_name parameter in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. For example, the following procedure sets the 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;
/

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

Adding Rules to the Rule Set for a Capture Process

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

• DBMS_STREAMS_ADM.ADD_TABLE_RULES
• DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
• DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

The following is an example that runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the 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         => 'strm01_queue',
    include_dml        => true,
    include_ddl        => true);
END;
/

Running this procedure performs the following actions:

• Creates two rules. One rule specifies that the capture process captures DML changes to the hr.departments table, and the other rule specifies that the capture process captures DDL changes to the hr.departments table. The rule names are specified by the system.
• Adds the two rules to the rule set associated with the capture process

  See Also: "System-Created Rules"

Removing a Rule from the Rule Set for a Capture Process

You specify that you want to remove a rule from the 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 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);
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.

In addition, if you want to remove all of the rules in the rule set for the capture process, then specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

Note: If you drop all of the rules in the rule set for a capture process, then the capture process captures no events.

Removing the Rule Set for a Capture Process

You specify that you want to remove the rule set from an existing capture process by setting the remove_rule_set parameter to true in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. For example, the following procedure removes the rule set from a capture process named strm01_capture.

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

Note: If you remove a rule set for a capture process, then the capture process captures all supported changes to all objects in the database, excluding database objects in the SYS and SYSTEM schemas.

Setting a Capture Process Parameter

You 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 3.

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

Note: Setting the parallelism parameter automatically stops and restarts a capture process. The value parameter is always entered as a VARCHAR2, even if the parameter value is a number.

See Also: "Capture Process Parameters" The DBMS_CAPTURE_ADM.SET_PARAMETER procedure in the Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information about the capture process parameters

Specifying Supplemental Logging at a Source Database

Supplemental logging must be specified for certain columns at a source database for changes to the columns to be applied successfully at a destination database. This section illustrates how to specify supplemental logging at a source database.

See Also: "Supplemental Logging" for information about when supplemental logging is required

Specifying Table Supplemental Logging Using Unconditional Log Groups

To specify an unconditional supplemental log group, you must create redo log groups that include the necessary columns using the ADD SUPPLEMENTAL LOG GROUP clause and the ALWAYS specification in an ALTER TABLE statement. These redo log groups can include key columns, if necessary.

For example, the following statement adds the primary key column of the hr.departments table to an unconditional log group named log_group_dep_pk:

ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_dep_pk
  (department_id) ALWAYS;

The ALWAYS specification makes this log group an unconditional log group.

Specifying Table Supplemental Logging Using Conditional Log Groups

To specify a conditional supplemental log group, you must create redo log groups that include the necessary columns using the ADD SUPPLEMENTAL LOG GROUP clause in the ALTER TABLE statement. To make the log group condition, do not include the ALWAYS specification.

For example, suppose the min_salary and max_salary columns in the hr.jobs table are included in a column list for conflict resolution at a destination database. The following statement adds the min_salary and max_salary columns to a log conditional group named log_group_jobs_cr:

ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs_cr 
  (min_salary, max_salary);

Specifying Database Supplemental Logging of Key Columns

You also have the option of specifying supplemental logging for all primary key and unique key columns in a source database. You may choose this option if you configure a capture process to capture changes to an entire database. To specify supplemental logging for all primary key and unique key columns in a source database, issue the following SQL statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

If your primary and unique key columns are the same at all source and destination databases, then running this command at the source database provides the supplemental logging needed for primary and unique key columns at all destination databases.

Switching the Log File

If you specify supplemental logging before any capture process is created on a database, then you must switch the log file after you submit the supplemental logging statement. Switching the log file causes the supplemental logging statement to take effect. The following command switches the log file:

ALTER SYSTEM ARCHIVE LOG CURRENT;

If a capture process was created on a database before you specify supplemental logging, then you do not need to switch the log file.

Setting the Start SCN for a Capture Process

You specify the start SCN for an existing capture process using the start_scn parameter in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. The SCN value specified must be from a point in time after the first capture process was created for the database. The first capture process for the database may or may not be the capture process being altered. An error is returned if an invalid SCN is specified. Typically, you reset a start SCN for a capture process if point-in-time recovery must be performed on one of the destination databases for changes from the capture process.

For example, the following procedure sets the start SCN for a capture process named strm01_capture to 750338948.

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

See Also: "The Start SCN for a Capture Process" "Performing Database Point-in-Time Recovery in a Streams Environment"

Preparing Database Objects for Instantiation at a Source Database

In a Streams environment that shares a database object within a single database or between multiple databases, a source database is the database where changes to the object are generated in the redo log. If a capture process captures or will capture such changes and the changes will be applied locally or propagated to other databases and applied at destination databases, then you may need to instantiate source database objects at destination databases. In any event, you must always prepare the object for instantiation.

The following procedures in the DBMS_CAPTURE_ADM package prepare database objects for instantiation:

• PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.
• PREPARE_SCHEMA_INSTANTIATION prepares for instantiation all of the database objects in a schema and all database objects added to the schema in the future.
• PREPARE_GLOBAL_INSTANTIATION prepares for instantiation all of the database objects in a database and all database objects added to the database in the future.

These procedures record the lowest SCN of each object for instantiation. SCNs subsequent to the lowest SCN for an object can be used for instantiating the object. If you run one of these procedures while a long running transaction is modifying one or more database objects being prepared for instantiation, then the procedure will wait until the long running transaction is complete before it records the lowest SCN.

These procedures also populate the Streams data dictionary for the relevant capture processes, propagation jobs, and apply processes that capture, propagate, or apply changes to the table, schema, or database being prepared for instantiation. Therefore, whenever you add or modify the condition of a capture or propagation rule for an object, you must run the appropriate procedure to prepare the object for instantiation.

Specifically, you must run the appropriate procedure to prepare an object for instantiation at the source database of the object if any of the following conditions are met:

• One or more rules are added to the rule set for a capture process that instruct the capture process to capture changes made to the object. When you use the DBMS_STREAMS_ADM package to add rules to a rule set for a capture process, the appropriate procedure to prepare for instantiation is run automatically at the source database. When you use the DBMS_RULE_ADM package to add these rules, you must prepare for instantiation manually.
• One or more conditions of rules in the rule set for a capture process are modified to instruct the capture process to capture changes made to the object.
• One or more rules are added to the rule set for a propagation job that instruct the propagation job to propagate changes made to the object.
• One or more conditions of rules in the rule set for a propagation job are modified to instruct the propagation job to propagate changes made to the object.

When any of these conditions are met, you must prepare database objects for instantiation at a source database to populate any relevant Streams data dictionary that requires information about the source object, even if the object already exists at a remote database where the rules were added.

The relevant Streams data dictionaries are populated asynchronously for both the local dictionary and all remote dictionaries. The procedure that prepares for instantiation adds information to the redo log at the source database. The local Streams data dictionary is populated with the information about the object when a capture process captures these redo entries, and any remote Streams data dictionaries are populated when the information is propagated to them.

For example, to prepare the hr.regions table for instantiation, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name  => 'hr.regions');
END;
/

See Also: "Streams Data Dictionary for Propagation Jobs" "Streams Data Dictionary for an Apply Process" "Configuring a Capture-Based Streams Environment"

Aborting Preparation for Instantiation at a Source Database

The following procedures in the DBMS_CAPTURE_ADM package abort preparation for instantiation:

• ABORT_TABLE_INSTANTIATION reverses the effects of PREPARE_TABLE_INSTANTIATION.
• ABORT_SCHEMA_INSTANTIATION reverses the effects of PREPARE_SCHEMA_INSTANTIATION.
• ABORT_GLOBAL_INSTANTIATION reverses the effects of PREPARE_GLOBAL_INSTANTIATION.

These procedures remove data dictionary information related to the potential instantiation of the relevant database objects.

For example, to abort the preparation for instantiation of the hr.regions table, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(
    table_name  => 'hr.regions');
END;
/

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;
/

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 strm01_capture:

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