Skip Headers

Oracle® Streams Concepts and Administration
10g Release 1 (10.1)
Part Number B10727-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
 View PDF

9
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 SYS.AnyData queue.

This chapter contains these topics:

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

See Also:

Creating a Capture Process

You can create a capture process that captures changes to the local source database, or you can create a capture process that captures changes remotely at a downstream database. If a capture process runs on a downstream database, then redo log files from the source database are copied to the downstream database, and the capture process captures changes in these redo log files at the downstream database.

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

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 either a positive or negative rule set for the capture process if the capture process does not have such 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, either as a positive or a negative rule set, a first SCN, and a start SCN for the capture process. To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE procedure.

Attention:

When a capture process is started or restarted, it may need to scan redo log files with a FIRST_CHANGE# value that is lower than start SCN. Removing required redo log files before they are scanned by a capture process causes the capture process to abort. You can query the DBA_CAPTURE data dictionary view to determine the first SCN, start SCN, and required checkpoint SCN. A capture process needs the redo log file that includes the required checkpoint SCN, and all subsequent redo log files. See "Capture Process Creation" for more information about the first SCN and start SCN for a capture process.

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

The following sections describe:

Creating a Local Capture Process

The following sections describe using the DBMS_STREAMS_ADM package and the DBMS_CAPTURE_ADM package to create a local capture process.

Example of Creating a Local 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 local 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,
    source_database    => NULL,
    inclusion_rule     => true);
END;
/

Running this procedure performs the following actions:

Example of Creating a Local 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 local capture process:

BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'strm02_queue',
    capture_name       => 'strm02_capture',
    rule_set_name      => 'strmadmin.strm01_rule_set',
    start_scn          => NULL,
    source_database    => NULL,
    use_database_link  => false,
    first_scn          => NULL);
END;
/

Running this procedure performs the following actions:

Creating a Downstream Capture Process That Assigns Log Files Implicitly

To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE procedure. The following sections describe creating a downstream capture process that uses a database link to the source database and one that does not. In both examples, assume the following:

Preparing to Copy Redo Log Files for Downstream Capture

Whether a database link from the downstream database to the source database is used or not, complete the following steps to prepare the source database to copy its redo log files to the downstream database, and to prepare the downstream database to accept these redo log files.

  1. Configure Oracle Net so that the source database can communicate with the downstream database.

    See Also:

    Oracle Net Services Administrator's Guide

  2. Set the following initialization parameters to configure log transport services to copy redo log files from the source database to the downstream database:
    • At the source database, set at least one archive log destination in the LOG_ARCHIVE_DEST_n initialization parameter to a directory on the computer system running the downstream database. To do this, set the following attributes of this parameter:
      • SERVICE - Specify the network service name of the downstream database.
      • ARCH or LGWR ASYNC - If you specify ARCH (the default), then the archiver process (ARCn) will archive the redo log files to the downstream database. If you specify LGWR ASYNC, then the log writer process (LGWR) will archive the redo log files to the downstream database. Either ARCH or LGWR ASYNC is acceptable for a downstream database destination.
      • MANDATORY or OPTIONAL - If you specify MANDATORY, then archiving of a redo log file to the downstream database must succeed before the corresponding online redo log at the source database can be overwritten. If you specify OPTIONAL, then successful archiving of a redo log file to the downstream database is not required before the corresponding online redo log at the source database can be overwritten. Either MANDATORY or OPTIONAL is acceptable for a downstream database destination.
      • NOREGISTER - Specify this attribute so that the downstream database location is not recorded in the downstream database control file.
      • REOPEN - Specify the minimum number of seconds the archiver process (ARCn) should wait before trying to access the downstream database location if a previous attempt to access this location failed.
      • TEMPLATE - Specify a directory and format template for archived redo logs at the downstream database. The TEMPLATE attribute overrides the LOG_ARCHIVE_FORMAT initialization parameter settings at the downstream database. The TEMPLATE attribute is valid only with remote destinations. Make sure the format uses all of the following variables at each source database: %t, %s, and %r.

      The following is an example of an LOG_ARCHIVE_DEST_n setting that specifies a downstream database:

      LOG_ARCHIVE_DEST_2='SERVICE=DBS2.NET ARCH OPTIONAL NOREGISTER REOPEN=60
   TEMPLATE=/usr/oracle/log_for_dbs1/dbs1_arch_%t_%s_%r.log'

      If another source database transfers log files to this downstream database, then, in the initialization parameter file at this other source database, you can use the TEMPLATE attribute to specify a different directory and format for the log files at the downstream database. The log files from each source database are kept separate at the downstream database.

    • At the source database, set the LOG_ARCHIVE_DEST_STATE_n initialization parameter that corresponds with the LOG_ARCHIVE_DEST_n parameter for the downstream database to enable.

      For example, if the LOG_ARCHIVE_DEST_2 initialization parameter is set for the downstream database, then set one LOG_ARCHIVE_DEST_STATE_2 parameter in the following way:

      LOG_ARCHIVE_DEST_STATE_2=enable
    • At both the source database and the downstream database, set the REMOTE_ARCHIVE_ENABLE initialization parameter to true.

      See Also:

      Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these initialization parameters

  3. If you reset any initialization parameters while the instance is running at a database in Step 2, then you may want to reset them in the initialization parameter file as well, so that the new values are retained when the database is restarted.

    If you did not reset the initialization parameters while the instance was running, but instead reset them in the initialization parameter file in Step 2, then restart the database. The source database must be open when it sends redo log files to the downstream database because the global name of the source database is sent to the downstream database only if the source database is open.

  4. Specify primary key supplemental logging for the hr.departments table at the source database dbs1.net: 
    ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Primary key supplemental logging is required for the hr.departments table because the examples in this section create capture processes that capture changes to this table.

  5. Perform the steps in one of the following sections depending on whether or not you want to use a database link from the downstream database to the source database:

Creating a Downstream Capture Process That Uses a Database Link

This section contains an example that runs the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a capture process at the dbs2.net downstream database that captures changes made to the dbs1.net source database. The capture process in this example uses a database link to dbs1.net for administrative purposes.

Connect to the downstream database dbs2.net as the Streams administrator and complete the following steps:

  1. Create the database link from dbs2.net to dbs1.net. For example, if the user strmadmin is the Streams administrator on both databases, then create the following database link: 
    CONNECT strmadmin/strmadminpw@dbs2.net

CREATE DATABASE LINK dbs1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
   USING 'dbs1.net';

    This example assumes that a Streams administrator exists at the source database dbs1.net. If no Streams administrator exists at the source database, then the Streams administrator at the downstream database should connect to a user who allows remote access by a Streams administrator. You can enable remote access for a user by specifying the user as the grantee when you run the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package at the source database.

  2. Create the queue for the capture process if it does not exist: 
    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.strm03_queue_table',
    queue_name   => 'strmadmin.strm03_queue');
END;
/
  3. Run the CREATE_CAPTURE procedure to create the capture process: 
    BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'strm03_queue',
    capture_name       => 'strm03_capture',
    rule_set_name      => NULL,
    start_scn          => NULL,
    source_database    => 'dbs1.net',
    use_database_link  => true,
    first_scn          => NULL,
    logfile_assignment => 'implicit');
END;
/

    Running this procedure performs the following actions:

    • Creates a capture process named strm03_capture at the downstream database dbs2.net. A capture process with the same name must not exist.
    • Associates the capture process with an existing queue on dbs2.net named strm03_queue
    • Specifies that the source database of the changes that the capture process will capture is dbs1.net
    • Specifies that the capture process uses a database link with the same name as the source database global name to perform administrative actions at the source database
    • Specifies that the capture process accepts new redo log files implicitly from dbs1.net. Therefore, the capture process scans any new log files copied from dbs1.net to dbs2.net for changes that it must capture. These log files can be added to the capture process automatically using log transport services or manually using the following DDL statement: 
      ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;

      Here, file_name is the name of the redo log file and capture_process is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually only if the logfile_assignment parameter is set to explicit.

    This step does not associate the capture process strm03_capture with any rule set. A rule set will be created and associated with the capture process in the next step.

    If no other capture process at dbs2.net is capturing changes from the dbs1.net source database, then the DBMS_CAPTURE_ADM.BUILD procedure is run automatically at dbs1.net using the database link. Running this procedure extracts the data dictionary at dbs1.net to the redo log, and a LogMiner data dictionary for dbs1.net is created at dbs2.net when the capture process strm03_capture is started for the first time at dbs2.net.

    If multiple capture processes at dbs2.net are capturing changes from the dbs1.net source database, then the new capture process strm03_capture uses the same LogMiner data dictionary for dbs1.net as one of the existing capture process. Streams automatically chooses which LogMiner data dictionary to share with the new capture process.

    See Also:
  4. Create the positive rule set for the capture process and add a rule to it: 
    BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.departments',
    streams_type        =>  'capture',
    streams_name        =>  'strm03_capture',
    queue_name          =>  'strm03_queue',
    include_dml         =>  true,
    include_ddl         =>  false,
    include_tagged_lcr  =>  false,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  true);
END;
/

    Running this procedure performs the following actions:

    • Creates a rule set at dbs2.net for capture process strm03_capture. The rule set has a system-generated name. The rule set is the positive rule set for the capture process because the inclusion_rule parameter is set to true.
    • Creates a rule that captures DML changes to the hr.departments table, and adds the rule to the positive rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule parameter is set to true.
    • Prepares the hr.departments table at dbs1.net for instantiation using the database link created in Step 1

Now you can configure propagation or apply, or both, of the LCRs captured by the strm03_capture capture process.

In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net. If this table does not exist at dbs2.net, then instantiate it at dbs2.net.

For example, if the hr.departments table exists at dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net by running the following procedure at the destination database dbs2.net:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@DBS1.NET;
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.departments',
    source_database_name  => 'dbs1.net',
    instantiation_scn     => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments table from the strm03_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object.

Note:

If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, the hr.departments table should be consistent at the source and destination databases when the instantiation SCN is set.

See Also:

Oracle Streams Replication Administrator's Guide for more information about instantiation

Creating a Downstream Capture Process That Does Not Use a Database Link

This section contains an example that runs the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a capture process at the dbs2.net downstream database that captures changes made to the dbs1.net source database. The capture process in this example does not use a database link to dbs1.net.

Complete the following steps:

  1. Connect to the source database dbs1.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement: 
    CONNECT strmadmin/strmadminpw@dbs1.net

    If you do not use a database link from the downstream database to the source database, then a Streams administrator must exist at the source database.

  2. If there is no capture process at dbs2.net that captures changes from dbs1.net, then perform a build of the dbs1.net data dictionary in the redo log. This step is optional if a capture process at dbs2.net is already configured to capture changes from the dbs1.net source database. 
    SET SERVEROUTPUT ON
DECLARE
  scn  NUMBER;
BEGIN
  DBMS_CAPTURE_ADM.BUILD(
    first_scn => scn);
  DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
END;
/
First SCN Value = 409391

    This procedure displays the valid first SCN value for the capture process that will be created at dbs2.net. Make a note of the SCN value returned because you will use it when you create the capture process at dbs2.net.

    If you run this procedure to build the data dictionary in the redo log, then when the capture process is first started at dbs2.net, it will create a LogMiner data dictionary using the data dictionary information in the redo log.

    If you choose to build the data dictionary without displaying the valid first SCN value when the procedure completes, then you can query the V$ARCHIVED_LOG dynamic performance view to determine a valid first SCN value for the capture process that will be created at dbs2.net.

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

    Your output looks similar to the following:

    FIRST_CHANGE#
-------------
       409391

    If more than one value is returned, then make a note of the highest value.

  3. Prepare the hr.departments table for instantiation: 
    BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
     table_name  =>  'hr.departments');
END;
/
  4. Connect to the downstream database dbs2.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement: 
    CONNECT strmadmin/strmadminpw@dbs2.net
  5. Create the queue for the capture process if it does not exist: 
    BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.strm03_queue_table',
    queue_name   => 'strmadmin.strm03_queue');
END;
/
  6. Run the CREATE_CAPTURE procedure to create the capture process and specify the value obtained in Step 2 for the first_scn parameter: 
    BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'strm03_queue',
    capture_name       => 'strm04_capture',
    rule_set_name      => NULL,
    start_scn          => NULL,
    source_database    => 'dbs1.net',
    use_database_link  => false,
    first_scn          => 409391, -- Use value from Step 2
    logfile_assignment => 'implicit');
END;
/

    Running this procedure performs the following actions:

    • Creates a capture process named strm04_capture at the downstream database dbs2.net. A capture process with the same name must not exist.
    • Associates the capture process with an existing queue on dbs2.net named strm03_queue
    • Specifies that the source database of the changes that the capture process will capture is dbs1.net
    • Specifies that the first SCN for the capture process is 409391. This value was obtained in Step 2. The first SCN is the lowest SCN for which a capture process can capture changes. Because a first SCN is specified, the capture process creates a new LogMiner data dictionary when it is first started, regardless of whether there are existing LogMiner data dictionaries for the same source database.
    • Specifies that the capture process accepts new redo log files implicitly from dbs1.net. Therefore, the capture process scans any new log files copied from dbs1.net to dbs2.net for changes that it must capture. These log files must be added to the capture process automatically using log transport services or manually using the following DDL statement: 
      ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;

      Here, file_name is the name of the redo log file and capture_process is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually only if the logfile_assignment parameter is set to explicit.

    This step does not associate the capture process strm04_capture with any rule set. A rule set will be created and associated with the capture process in the next step.

    See Also:
  7. Create the positive rule set for the capture process and add a rule to it: 
    BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.departments',
    streams_type        =>  'capture',
    streams_name        =>  'strm04_capture',
    queue_name          =>  'strm03_queue',
    include_dml         =>  true,
    include_ddl         =>  false,
    include_tagged_lcr  =>  false,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  true );
END;
/

    Running this procedure performs the following actions:

    • Creates a rule set at dbs2.net for capture process strm04_capture. The rule set has a system-generated name. The rule set is a positive rule set for the capture process because the inclusion_rule parameter is set to true.
    • Creates a rule that captures DML changes to the hr.departments table, and adds the rule to the rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule parameter is set to true.

Now you can configure propagation or apply, or both, of the LCRs captured by the strm04_capture capture process.

In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net. If this table does not exist at dbs2.net, then instantiate it at dbs2.net.

For example, if the hr.departments table exists at dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net by running the following procedure at the source database dbs1.net:

CONNECT strmadmin/strmadminpw@dbs1.net

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET(
    source_object_name    => 'hr.departments',
    source_database_name  => 'dbs1.net',
    instantiation_scn     => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments table from the strm03_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object.

Note:
  • To set the instantiation SCN using the previous example requires a database link from the source database to the destination database.
  • If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, the hr.departments table should be consistent at the source and destination databases when the instantiation SCN is set.
See Also:

Oracle Streams Replication Administrator's Guide for more information about instantiation

Creating a Downstream Capture Process That Assigns Log Files Explicitly

To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE procedure. This section describes creating a downstream capture process that assigns redo log files explicitly. That is, you must use the DBMS_FILE_TRANSFER package, FTP, or some other method to transfer redo log files from the source database to the downstream database, and then you must register these redo log files with the downstream capture process manually.

In this example, assume the following:

Complete the following steps:

  1. Connect to the source database dbs1.net as the hr user: 
    CONNECT hr/hr@dbs1.net
  2. Specify primary key supplemental logging for the hr.departments table: 
    ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Primary key supplemental logging is required for the hr.departments table because this example creates a capture processes that captures changes to this table.

  3. Connect to the source database dbs1.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement: 
    CONNECT strmadmin/strmadminpw@dbs1.net

    If you do not use a database link from the downstream database to the source database, then a Streams administrator must exist at the source database.

  4. If there is no capture process at dbs2.net that captures changes from dbs1.net, then perform a build of the dbs1.net data dictionary in the redo log. This step is optional if a capture process at dbs2.net is already configured to capture changes from the dbs1.net source database. 
    SET SERVEROUTPUT ON
DECLARE
  scn  NUMBER;
BEGIN
  DBMS_CAPTURE_ADM.BUILD(
    first_scn => scn);
  DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
END;
/
First SCN Value = 409391

    This procedure displays the valid first SCN value for the capture process that will be created at dbs2.net. Make a note of the SCN value returned because you will use it when you create the capture process at dbs2.net.

    If you run this procedure to build the data dictionary in the redo log, then when the capture process is first started at dbs2.net, it will create a LogMiner data dictionary using the data dictionary information in the redo log.

  5. Prepare the hr.departments table for instantiation: 
    BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
     table_name  =>  'hr.departments');
END;
/
  6. Connect to the downstream database dbs2.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement: 
    CONNECT strmadmin/strmadminpw@dbs2.net
  7. Run the CREATE_CAPTURE procedure to create the capture process and specify the value obtained in Step 2 for the first_scn parameter: 
    BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'strm03_queue',
    capture_name       => 'strm05_capture',
    rule_set_name      => NULL,
    start_scn          => NULL,
    source_database    => 'dbs1.net',
    use_database_link  => false,
    first_scn          => 409391, -- Use value from Step 2
    logfile_assignment => 'explicit');
END;
/

    Running this procedure performs the following actions:

    • Creates a capture process named strm05_capture at the downstream database dbs2.net. A capture process with the same name must not exist.
    • Associates the capture process with an existing queue on dbs2.net named strm03_queue
    • Specifies that the source database of the changes that the capture process will capture is dbs1.net
    • Specifies that the first SCN for the capture process is 409391. This value was obtained in Step 2. The first SCN is the lowest SCN for which a capture process can capture changes. Because a first SCN is specified, the capture process creates a new LogMiner data dictionary when it is first started, regardless of whether there are existing LogMiner data dictionaries for the same source database.
    • Specifies new redo log files from dbs1.net must be assigned to the capture process explicitly. After a redo log file has been transferred to the computer running the downstream database, you assign the log file to the capture process explicitly using the following DDL statement: 
      ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;

      Here, file_name is the name of the redo log file and capture_process is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually if the logfile_assignment parameter is set to explicit.

    This step does not associate the capture process strm05_capture with any rule set. A rule set will be created and associated with the capture process in the next step.

    See Also:
  8. Create the positive rule set for the capture process and add a rule to it: 
    BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.departments',
    streams_type        =>  'capture',
    streams_name        =>  'strm05_capture',
    queue_name          =>  'strm03_queue',
    include_dml         =>  true,
    include_ddl         =>  false,
    include_tagged_lcr  =>  false,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  true );
END;
/

    Running this procedure performs the following actions:

    • Creates a rule set at dbs2.net for capture process strm04_capture. The rule set has a system-generated name. The rule set is a positive rule set for the capture process because the inclusion_rule parameter is set to true.
    • Creates a rule that captures DML changes to the hr.departments table, and adds the rule to the rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule parameter is set to true.
  9. After the redo log file at the source database dbs1.net that contains the first SCN for the downstream capture process is archived, transfer the archived redo log file to the computer running the downstream database. The BUILD procedure in Step 4 determined the first SCN for the downstream capture process. If the redo log file is not yet archived, you can run the ALTER SYSTEM SWITCH LOGFILE statement on the database to archive it.

    You can run the following query at dbs1.net to identify the archived redo log file that contains the first SCN for the downstream capture process:

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

SELECT NAME, FIRST_CHANGE# FROM V$ARCHIVED_LOG
  WHERE FIRST_CHANGE# IS NOT NULL AND DICTIONARY_BEGIN = 'YES';

    Transfer the archived redo log file with a FIRST_CHANGE# that matches the first SCN returned in Step 4 to the computer running the downstream capture process.

  10. At the downstream database dbs2.net, connect as an administrative user and assign the transferred redo log file to the capture process. For example, if the redo log file is /oracle/logs_from_dbs1/1_10_486574859.dbf, then issue the following statement: 
    ALTER DATABASE REGISTER LOGICAL LOGFILE 
   '/oracle/logs_from_dbs1/1_10_486574859.dbf' FOR 'strm05_capture';

Now you can configure propagation or apply, or both, of the LCRs captured by the strm05_capture capture process.

In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net. If this table does not exist at dbs2.net, then instantiate it at dbs2.net.

For example, if the hr.departments table exists at dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net by running the following procedure at the source database dbs1.net:

CONNECT strmadmin/strmadminpw@dbs1.net

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET(
    source_object_name    => 'hr.departments',
    source_database_name  => 'dbs1.net',
    instantiation_scn     => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments table from the strm03_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object.

Note:
  • To set the instantiation SCN using the previous example requires a database link from the source database to the destination database.
  • If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, the hr.departments table should be consistent at the source and destination databases when the instantiation SCN is set.
See Also:

Oracle Streams Replication Administrator's Guide for more information about instantiation

Creating a Local Capture Process with Non-NULL Start SCN

This example runs the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a local capture process with a start SCN set to 223525. This example assumes that there is at least one local capture processes at the database, and that this capture process has taken at least one checkpoint. You can always specify a start SCN for a new capture process that is equal to or greater than the current SCN of the source database. If you want to specify a start SCN that is lower than the current SCN of the database, then the specified start SCN must be higher than the lowest first SCN for an existing local capture process that has been started successfully at least once and has taken at least one checkpoint.

You can determine the first SCN for existing capture processes, and whether these capture processes have taken a checkpoint, by running the following query:

SELECT CAPTURE_NAME, FIRST_SCN, MAX_CHECKPOINT_SCN FROM DBA_CAPTURE;

Your output looks similar to the following:

CAPTURE_NAME                    FIRST_SCN MAX_CHECKPOINT_SCN
------------------------------ ---------- ------------------
CAPTURE_SIMP                       223522             230825

These results show that the capture_simp capture process has a first SCN of 223522. Also, this capture process has taken a checkpoint because the MAX_CHECKPOINT_SCN value is non-NULL. Therefore, the start SCN for the new capture process can be set to 223522 or higher.

Run the following procedure to create the capture process:

BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'strm01_queue',
    capture_name       => 'strm05_capture',
    rule_set_name      => 'strmadmin.strm01_rule_set',
    start_scn          => 223525,
    source_database    => NULL,
    use_database_link  => false,
    first_scn          => NULL);
END;
/

Running this procedure performs the following actions:

Starting, Stopping, and Dropping a Capture Process

This section contains instructions for starting, stopping and dropping a capture process. It contains the following topics:

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

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 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 Streams client. If the drop_unused_rule_sets parameter is set to true, then both the positive and negative rule set for the capture process may 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:

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

Managing the Rule Set for a Capture Process

This section contains instructions for completing the following tasks:

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.

See Also:

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 and specify the existing capture process:

Excluding the ADD_SUBSET_RULES procedure, these procedures can add rules to the positive 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 is an example that 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      =>  'strm01_queue',
    include_dml     =>  true,
    include_ddl     =>  true,
    inclusion_rule  =>  true);
END;
/

Running this procedure performs the following actions:

Adding Rules to the Negative Rule Set for a Capture Process

The following is an example that 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      =>  'strm01_queue',
    include_dml     =>  true,
    include_ddl     =>  true,
    inclusion_rule  =>  false);
END;
/

Running this procedure performs the following actions:

Removing a Rule from a Rule Set for a Capture Process

You specify that you want to 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.

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

See Also:

"Streams Client With One or More Empty Rule Sets"

Removing a Rule Set for a Capture Process

You specify that you want to 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 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:

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, make sure the capture user has the following privileges:

These privileges must be granted directly to the capture user. They cannot be granted through roles.

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. Typically, supplemental logging is required in Streams replication environments, but it may be required in any environment that processes captured events with an apply process. You use the ALTER DATABASE statement to specify supplemental logging for all tables in a database, and you use the ALTER TABLE statement to specify supplemental logging for a particular table.

See Also:

Oracle Streams Replication Administrator's Guide for more information about specifying supplemental logging

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 Reference for more information about the ALTER DATABASE statement and Oracle Data Guard Concepts and Administration for more information registering redo log files

Setting SCN Values for an Existing Capture Process

This section contains the following topics:

Setting the First SCN for an Existing Capture Process

You can set the first SCN for an existing capture process using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. The specified first SCN must meet the following requirements:

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 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 prior that contain information prior to 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.

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 need to capture changes in the redo log from a point in time in the past, then you can create a new 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 using the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. 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. 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;

Also, when you reset a start SCN for a capture process, make sure the required redo log files are available to the capture process.

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

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name => 'strm01_capture',
    start_scn    => 750338);
END;
/
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.

The example in "Creating a Downstream Capture Process That Does Not Use a Database Link" created the capture process strm04_capture and specified that this capture process does not use a database link. To create a database link to the source database dbs1.net and specify that this capture process uses the database link, complete the following actions:

CREATE DATABASE LINK dbs1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
   USING 'dbs1.net';

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name       => 'strm04_capture',
    use_database_link  => true);
END;
/
See Also:

"Local Capture and Downstream Capture"

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 to capture one or more extra attributes. You also can use this procedure to instruct a capture process to exclude an extra attribute that it is capturing currently.

The extra attributes are the following:

This section contains instructions for completing the following tasks:

Including Extra Attributes in Captured LCRs

To instruct a capture process named strm01_capture to include the transaction name in each captured LCR, 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 Captured LCRs

To instruct a capture process named strm01_capture to exclude the transaction name from each captured LCR, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'strm01_capture',
    attribute_name => 'tx_name',
    include        => false);
END;
/
Go to previous page
Previous		 Go to next page
Next
 Oracle
Copyright © 2002, 2003 Oracle Corporation
All Rights Reserved.
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback