12
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
• Changing the DBID of a Database Where Changes Are Captured
• Resetting the Log Sequence Number Where Changes Are Captured
• 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, Captured SCN, and Applied 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.

Note: LOBs, LONGs, and user-defined types cannot be part of a supplemental log group

See Also: "Supplemental Logging in a Streams Environment" 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);

Dropping a Supplemental Log Group

To drop a conditional or unconditional supplemental log group, use the DROP SUPPLEMENTAL LOG GROUP clause in the ALTER TABLE statement. For example, to drop a supplemental log group named log_group_jobs_cr, run the following statement:

ALTER TABLE hr.jobs DROP SUPPLEMENTAL LOG GROUP log_group_jobs_cr;

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.

Dropping Database Supplemental Logging of Key Columns

To drop supplemental logging for all primary key and unique key columns in a source database, issue the following SQL statement:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Note: Dropping database supplemental logging of key columns does not affect any existing table-level supplemental log groups.

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, Captured SCN, and Applied SCN for a Capture Process" "Performing Database Point-in-Time Recovery on a Destination Database"

Preparing Database Objects for Instantiation at a Source Database

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 objects in a database and all objects added to the database in the future.

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.

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: "Instantiation" "Streams Data Dictionary for Propagations" "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;
/

Changing the DBID of a Database Where Changes Are Captured

Typically, database administrators change the DBID of a database when it is a clone of another database. You can view the DBID for a database by querying the DBID column in the V$DATABASE dynamic performance view.

If a capture process is capturing changes generated by a database for which you have changed the DBID, then complete the following steps:

1. Shut down the database.
2. Restart the database with RESTRICTED SESSION enabled using STARTUP RESTRICT.
3. Drop the capture process.
4. Run the ALTER SYSTEM SWITCH LOGFILE statement on the database.
5. If the database has captured any changes, then manually resynchronize the data at all destination databases that apply changes originating at this source database. If the database never captured any changes, then this step is not necessary.
6. Recreate the capture process, if necessary.
7. Disable the restricted session using the ALTER SYSTEM DISABLE RESTRICTED SESSION statement.

   See Also: Oracle9i Database Utilities for more information about changing the DBID of a database using the DBNEWID utility

Resetting the Log Sequence Number Where Changes Are Captured

Typically, database administrators reset the log sequence number of a database during point-in-time recovery. The ALTER DATABASE OPEN RESETLOGS statement is an example of a statement that resets the log sequence number. When you reset the log sequence number of a database, any existing local capture processes become unusable.

If a capture process is capturing changes generated by a database for which you have reset the log sequence number, then complete the following steps:

1. Drop the capture process.
2. Manually resynchronize the data at all destination databases that apply changes originating at this source database.
3. Recreate the capture process, if necessary.

   See Also: Oracle9i Backup and Recovery Concepts for more information about point-in-time recovery

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

A capture process must be stopped before it can be dropped.