4 Adding to an Oracle Streams Replication Environment

About Using the Setup Streams Replication Wizard or a Single Configuration Procedure

There are two easy ways to extend an Oracle Streams replication environment:

• Run the Setup Streams Replication Wizard in Oracle Enterprise Manager Cloud Control

• Run one of the following procedures in the DBMS_STREAMS_ADM package:

  • The MAINTAIN_GLOBAL procedure can add a new database to an environment that replicates changes to all of the database objects in the databases.

  • The MAINTAIN_SCHEMAS procedure can add one or more new schemas to the existing databases in the replication environment, or it can add a new database that replicates schemas that are currently being replicated.

  • The MAINTAIN_SIMPLE_TTS procedure can add a new simple tablespace to an existing replication environment, or it can add a new database that replicates a simple tablespace that is currently being replicated.

  • The MAINTAIN_TABLES procedure can add one or more new tables to the existing databases in the replication environment, or it can add a new database that replicates tables that are currently being replicated.

  • The MAINTAIN_TTS procedure can add a new set of tablespaces to an existing replication environment, or it can add a new database that replicates a set of tablespaces that are currently being replicated.

To use either of these methods to extend an Oracle Streams replication environment, the environment must meet the following conditions:

• It must be a two-database or hub-and-spoke replication environment that was configured by the Setup Streams Replication Wizard or by one of the configuration procedures in the DBMS_STREAMS_ADM package. See "Decide Which Type of Replication Environment to Configure" for information about these types of replication environments.

• It cannot use a synchronous capture at any database in the Oracle Streams replication environment. See Oracle Streams Concepts and Administration for more information about synchronous capture.

• If you are adding a database to the environment, then each database that captures changes must use a local capture process. No database can use a downstream capture process. If you are adding one or more database objects to the environment, then the databases can use either local or downstream capture processes. See "Decide Whether to Configure Local or Downstream Capture for the Source Database" for more information about downstream capture.

• If you are adding database objects to the replication environment, then the database objects must exist at the database specified in the source_database parameter of the configuration procedure.

If your environment meets these conditions, then you can use the Setup Streams Replication Wizard or a single procedure to extend the environment.

The following are additional requirements for cases in which the replicated database objects already exist at an intended destination database before you run the wizard or procedure:

• If you are adding database objects to the replication environment, and one or more of these database objects exist at a database other than the source database, then meet the following requirements:

  • Before running the wizard or procedure, ensure that the replicated database objects at each destination database are consistent with replicated database objects at the source database.

  • After running the wizard or procedure, ensure that the instantiation SCN is set for each replicated database object at each destination database. See "Setting Instantiation SCNs at a Destination Database" and "Monitoring Instantiation""Monitoring Instantiation".

• If you are adding a database to the replication environment, then any of the database objects that are replicated in the current environment exist at the added database, then meet the following requirements:

  • Before running the wizard or procedure, ensure that the replicated database objects at each database being added are consistent with replicated database objects at the source database.

  • After running the wizard or procedure, ensure that the instantiation SCN is set for each replicated database object at the added database. See "Setting Instantiation SCNs at a Destination Database" and "Monitoring Instantiation""Monitoring Instantiation".

For instructions about adding to a replication environment using the wizard or a single procedure, see the following documentation:

• The Oracle Enterprise Manager Cloud Control online help for instructions about using the Setup Streams Replication Wizard

• "Adding Multiple Components Using a Single Procedure" for instructions about using a single procedure in the DBMS_STREAMS_ADM package

About Adding the Oracle Streams Components Individually in Multiple Steps

If you cannot extend the Oracle Streams replication environment by using the Setup Streams Replication Wizard or a configuration procedure in the DBMS_STREAMS_ADM package, then you must complete the configuration steps manually. These steps include adding the necessary rules and Oracle Streams components to the environment, and other configuration steps.

If you must extend the Oracle Streams replication environment manually, then see the instructions in "Adding Components Individually in Multiple Steps".

Adding Database Objects to a Replication Environment Using a Single Procedure

This topic includes an example that uses the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package to add tables to an existing hub-and-spoke replication environment. When the example is complete, the Oracle Streams replication environment replicates the changes made to the added tables at the databases in the environment.

Specifically, the example in this topic extends the replication environment configured in "Example That Configures Hub-and-Spoke Replication". That configuration has the following characteristics:

• The hr schema is replicated at the hub.example.com, spoke1.example.com, and spoke2.example.com databases.

• The hub.example.com database is the hub database in the hub-and-spoke environment, while the other databases are the spoke databases.

• The spoke databases allow changes to the replicated schema, and each database has a local capture process to capture these changes.

• Update conflict handlers are configured for each replicated table at each database to resolve conflicts

This example adds the following tables to the environment:

• oe.orders

• oe.order_items

This example uses the tables in the oe sample schema. The oe sample schema is installed by default with Oracle Database.

Complete the following steps:

1. Ensure that the following directory objects exist, and remove any files related to the previous configuration from them, including Data Pump export dump files and export log files:

   • The hub_dir directory object at the hub.example.com database.

   • The spoke1_dir directory object at the spoke1.example.com database.

   • The spoke2_dir directory object at the spoke2.example.com database.

2. Stop the capture process at the hub database in the hub-and-spoke environment.

   Use the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop a capture process.

   In this example, stop the capture process at the hub.example.com database. The replicated database objects can remain open to changes while the capture process is stopped. These changes will be captured when the capture process is restarted.

3. In SQL*Plus, run the appropriate configuration procedure in the DBMS_STREAMS_ADM package at the hub database to add each new database object for each spoke database.

   You might need to run the procedure several times if the environment has multiple spoke databases. In this example, complete the following steps:

   1. Open SQL*Plus and connect to the hub.example.com database as the Oracle Streams administrator.

      See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

   2. Run the MAINTAIN_TABLES procedure to add the oe.orders and oe.order_items tables for replication between hub.example.com and spoke1.example.com:

      DECLARE
        tables DBMS_UTILITY.UNCL_ARRAY;
        BEGIN
          tables(1) := 'oe.orders';
          tables(2) := 'oe.order_items';
          DBMS_STREAMS_ADM.MAINTAIN_TABLES(
            table_names                  => tables,
            source_directory_object      => 'hub_dir',
            destination_directory_object => 'spoke1_dir',
            source_database              => 'hub.example.com',
            destination_database         => 'spoke1.example.com',
            capture_name                 => 'capture_hns',
            capture_queue_table          => 'source_hns_qt',
            capture_queue_name           => 'source_hns',
            propagation_name             => 'propagation_spoke1',
            apply_name                   => 'apply_spoke1',
            apply_queue_table            => 'destination_spoke1_qt',
            apply_queue_name             => 'destination_spoke1',
            bi_directional               => TRUE);
      END;
      /
      

      The MAINTAIN_TABLES procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the specified tables at the destination database while the procedure is running. When the procedure completes, the new database objects are added to the environment, and the capture process that was stopped in Step 2 is restarted.

      When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.

      The parameter values that specify Oracle Streams component names must be the same as the values specified in the configuration procedure in the DBMS_STREAMS_ADM package that configured the replication environment. The Oracle Streams component names specified include the capture process name, queue names, queue table names, the propagation name, and the apply process name. In this example, the Oracle Streams component names match the ones specified in "Example That Configures Hub-and-Spoke Replication".

   3. Run the MAINTAIN_TABLES procedure to add the oe.orders and oe.order_items tables for replication between hub.example.com and spoke2.example.com:

      DECLARE
        tables DBMS_UTILITY.UNCL_ARRAY;
        BEGIN
          tables(1) := 'oe.orders';
          tables(2) := 'oe.order_items';
          DBMS_STREAMS_ADM.MAINTAIN_TABLES(
            table_names                  => tables,
            source_directory_object      => 'hub_dir',
            destination_directory_object => 'spoke2_dir',
            source_database              => 'hub.example.com',
            destination_database         => 'spoke2.example.com',
            capture_name                 => 'capture_hns',
            capture_queue_table          => 'source_hns_qt',
            capture_queue_name           => 'source_hns',
            propagation_name             => 'propagation_spoke2',
            apply_name                   => 'apply_spoke2',
            apply_queue_table            => 'destination_spoke2_qt',
            apply_queue_name             => 'destination_spoke2',
            bi_directional               => TRUE);
      END;
      /
      

4. Set the instantiation SCN for the replicated tables at the spoke databases:

   Note:

   This step is required in this example because the replicated tables existed at the spoke databases before the MAINTAIN_TABLES procedure was run. If the replicated tables did not exist at the spoke databases before the MAINTAIN_TABLES procedure was run, then the procedure sets the instantiation SCN for the replicated tables and this step is not required. Ensure that the data in the shared table is consistent at the source and destination databases when the instantiation SCN is set and that no changes are made to the table at the source database until after the SCN that is used for the instantiation SCN.

   1. In SQL*Plus, connect to the hub.example.com database as the Oracle Streams administrator.

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

   2. Set the instantiation SCN for the oe.orders table at the spoke1.example.com database:

      DECLARE
        iscn  NUMBER;    -- Variable to hold instantiation SCN value
      BEGIN
        iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
        DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke1.example.com(
          source_object_name    => 'oe.orders',
          source_database_name  => 'hub.example.com',
          instantiation_scn     => iscn);
      END;
      /
      

   3. Set the instantiation SCN for the oe.order_items table at the spoke1.example.com database:

      DECLARE
        iscn  NUMBER;    -- Variable to hold instantiation SCN value
      BEGIN
        iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
        DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke1.example.com(
          source_object_name    => 'oe.order_items',
          source_database_name  => 'hub.example.com',
          instantiation_scn     => iscn);
      END;
      /
      

   4. Set the instantiation SCN for the oe.orders table at the spoke2.example.com database:

      DECLARE
        iscn  NUMBER;    -- Variable to hold instantiation SCN value
      BEGIN
        iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
        DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke2.example.com(
          source_object_name    => 'oe.orders',
          source_database_name  => 'hub.example.com',
          instantiation_scn     => iscn);
      END;
      /
      

   5. Set the instantiation SCN for the oe.order_items table at the spoke2.example.com database:

      DECLARE
        iscn  NUMBER;    -- Variable to hold instantiation SCN value
      BEGIN
        iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
        DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke2.example.com(
          source_object_name    => 'oe.order_items',
          source_database_name  => 'hub.example.com',
          instantiation_scn     => iscn);
      END;
      /
      

5. Configure latest time conflict resolution for the orders and order_items tables in the oe schema at the hub.example.com, spoke1.example.com, and spoke2.example.com databases. See "Prebuilt Update Conflict Handlers" for instructions.

Adding a Database to a Replication Environment Using a Single Procedure

This topic includes an example that uses the MAINTAIN_SCHEMAS procedure in the DBMS_STREAMS_ADM package to add a new spoke database to an existing hub-and-spoke replication environment. When the example is complete, the Oracle Streams replication environment replicates the changes made to the schema with the new database.

Specifically, the example in this topic extends the replication environment configured in "Example That Configures Hub-and-Spoke Replication". That configuration has the following characteristics:

• The hr schema is replicated at the hub.example.com, spoke1.example.com, and spoke2.example.com databases.

• The hub.example.com database is the hub database in the hub-and-spoke environment, while the other databases are the spoke databases.

• The spoke databases allow changes to the replicated schema, and each database has a local capture process to capture these changes.

This example adds the spoke3.example.com database to the environment.

Complete the following steps:

1. Complete the following tasks to prepare the environment for the new database:

   1. Configure network connectivity so that the hub database can communicate with the new spoke database. In this example, configure network connectivity so that the hub.example.com database and the spoke3.example.com databases can communicate with each other.

      See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

   2. Configure an Oracle Streams administrator at the new spoke database. In this example, configure an Oracle Streams administrator at the spoke3.example.com database. See "Configuring an Oracle Streams Administrator on All Databases" for instructions. This example assumes that the Oracle Streams administrator is strmadmin.

   3. Create a database link from the hub database to new spoke database and from new spoke database to the hub database. In this example, create the following database links:

      • From the hub.example.com database to the spoke3.example.com database. Both the name and the service name of the database link must be spoke3.example.com.

      • From the spoke3.example.com database to the hub.example.com database. Both the name and the service name of the database link must be hub.example.com.

      Each database link should be created in the Oracle Streams administrator's schema. Also, each database link should connect to the Oracle Streams administrator at the destination database. See "Configuring Network Connectivity and Database Links" for instructions.

   4. Set initialization parameters properly at the new spoke database. In this example, set initialization parameters properly at the spoke3.example.com database. See "Setting Initialization Parameters Relevant to Oracle Streams" for instructions.

   5. Configure the new spoke database to run in ARCHIVELOG mode. For a capture process to capture changes generated at a source database, the source database must be running in ARCHIVELOG mode. In this example, configure the spoke3.example.com database to run in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

   6. Ensure that the hub_dir directory objects exist at the hub.example.com database, and remove any files related to the previous configuration from it, including Data Pump export dump files and export log files.

2. Open SQL*Plus and connect to the spoke3.example.com database as the Oracle Streams administrator.

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

3. Create a directory object to hold files that will be generated by the MAINTAIN_SCHEMAS procedure, including the Data Pump export dump file used for instantiation. The directory object can point to any accessible directory on the computer system. For example, the following statement creates a directory object named spoke3_dir that points to the /usr/spoke3_log_files directory:

   CREATE DIRECTORY spoke3_dir AS '/usr/spoke3_log_files';
   

4. Stop the capture process at the hub database in the hub-and-spoke environment.

   Use the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop a capture process.

   In this example, stop the capture process at the hub.example.com database. The replicated database objects can remain open to changes while the capture process is stopped. These changes will be captured when the capture process is restarted.

5. In SQL*Plus, run the appropriate configuration procedure in the DBMS_STREAMS_ADM package at the hub database to add the new spoke database.

   In this example, complete the following steps:

   1. Open SQL*Plus and connect to the hub.example.com database as the Oracle Streams administrator.

   2. Run the MAINTAIN_SCHEMAS procedure to add the spoke3.example.com database to the Oracle Streams replication environment:

      BEGIN
        DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
          schema_names                 => 'hr',
          source_directory_object      => 'hub_dir',
          destination_directory_object => 'spoke3_dir',
          source_database              => 'hub.example.com',
          destination_database         => 'spoke3.example.com',
          capture_name                 => 'capture_hns',
          capture_queue_table          => 'source_hns_qt',
          capture_queue_name           => 'source_hns',
          propagation_name             => 'propagation_spoke3',
          apply_name                   => 'apply_spoke3',
          apply_queue_table            => 'destination_spoke3_qt',
          apply_queue_name             => 'destination_spoke3',
          bi_directional               => TRUE);
      END;
      /
      

      The MAINTAIN_SCHEMAS procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the database objects in the specified schema at the destination database while the procedure is running. When the procedure completes, the new database objects are added to the environment, and the capture process that was stopped in Step 4 is restarted.

      The parameter values specified in capture_name, capture_queue_table, and capture_queue_name must be the same as the values specified in the configuration procedure in the DBMS_STREAMS_ADM package that configured the replication environment. In this example, these parameter values match the ones specified in "Example That Configures Hub-and-Spoke Replication".

      When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.

6. Configure latest time conflict resolution for all of the tables in the hr schema at the spoke3.example.com database. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables. See "Prebuilt Update Conflict Handlers" for instructions.

Adding Replicated Objects to an Existing Single-Source Environment

You can add existing database objects to an existing single-source environment by adding the necessary rules to the appropriate capture processes, synchronous captures, propagations, and apply processes. Before creating or altering capture or propagation rules in a running Oracle Streams environment, ensure that any propagations or apply processes that will receive logical change records (LCRs) because of the new or altered rules are configured to handle these LCRs. That is, the propagations or apply processes should exist, and each one should be associated with rule sets that handle the LCRs appropriately. If these propagations and apply processes are not configured properly to handle these LCRs, then LCRs might be lost.

For example, suppose you want to add a table to an Oracle Streams replication environment that already captures, propagates, and applies changes to other tables. Assume that only one capture process or synchronous captures will capture changes to this table, and only one apply process will apply changes to this table. In this case, you must add one or more table rules to the following rule sets:

• The positive rule set for the apply process that will apply changes to the table

• The positive rule set for each propagation that will propagate changes to the table

• The positive rule set for the capture process or synchronous capture that will capture changes to the table

If you perform administrative steps in the wrong order, you can lose LCRs. For example, if you add the rule to a capture process rule set first, without stopping the capture process, then the propagation will not propagate the changes if it does not have a rule that instructs it to do so, and the changes can be lost.

This example assumes that the replicated database objects are read-only at the destination databases. If the replicated database objects are read/write at the destination databases, then the replication environment will not stay synchronized because Oracle Streams is not configured to replicate the changes made to the replicated database objects at the destination databases.

Figure 4-1 shows the additional configuration steps that must be completed to add replicated database objects to a single-source Oracle Streams environment.

Figure 4-1 Example of Adding Replicated Objects to a Single-Source Environment

Description of "Figure 4-1 Example of Adding Replicated Objects to a Single-Source Environment"

To avoid losing LCRs, complete the configuration in the following order:

1. At each source database where replicated database objects are being added, specify supplemental logging for the added replicated database objects. See "Specifying Supplemental Logging" for instructions.

2. Either stop the capture process, one of the propagations, or the apply processes:

   • Use the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop a capture process.

   • Use the STOP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to stop a propagation.

   • Use the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an apply process.

   In general, it is best to stop the capture process so that messages do not accumulate in queues during the operation.

   Note:

   Synchronous captures cannot be stopped.

   See Also:

   Oracle Streams Concepts and Administration for more information about completing these tasks with PL/SQL procedures

3. Add the relevant rules to the rule sets for the apply processes. To add rules to the rule set for an apply process, you can run one of the following procedures:

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

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

4. Add the relevant rules to the rule sets for the propagations. To add rules to the rule set for a propagation, you can run one of the following procedures:

   • DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES

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

5. Add the relevant rules to the rule sets used by the capture process or synchronous capture. 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:

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

   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.

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

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   When you use a procedure in the DBMS_STREAMS_ADM package to add the capture process rules, it automatically runs the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table, specified schema, or entire database, respectively, if the capture process is a local capture process or a downstream capture process with a database link to the source database.

   You must run the appropriate procedure to prepare for instantiation manually if any of the following conditions is true:

   • You use DBMS_RULE_ADM to create or modify rules in a capture process rule set.

   • You do not add rules for the added objects to a capture process rule set, because the capture process already captures changes to these objects. In this case, rules for the objects can be added to propagations and apply processes in the environment, but not to the capture process.

   • You use a downstream capture process with no database link to the source database.

   If you must prepare for instantiation manually, then see "Preparing Database Objects for Instantiation at a Source Database" for instructions.

   When you use a procedure in the DBMS_STREAMS_ADM package to add the synchronous capture rules, it automatically runs the PREPARE_SYNC_INSTANTIATION function in the DBMS_CAPTURE_ADM package for the specified table.

6. At each destination database, either instantiate, or set the instantiation SCN for, each database object you are adding to the Oracle Streams environment. If the database objects do not exist at a destination database, then instantiate them using export/import, transportable tablespaces, or RMAN. If the database objects already exist at a destination database, then set the instantiation SCNs for them manually.

   • To instantiate database objects using export/import, first export them at the source database. Next, import them at the destination database. See Chapter 8, "Instantiation and Oracle Streams Replication".

     Do not allow any changes to the database objects being exported while exporting these database objects at the source database. Do not allow changes to the database objects being imported while importing these database objects at the destination database.

     You can specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

   • To set the instantiation SCN for a table, schema, or database manually, run the appropriate procedure or procedures in the DBMS_APPLY_ADM package at a destination database:

     • SET_TABLE_INSTANTIATION_SCN

     • SET_SCHEMA_INSTANTIATION_SCN

     • SET_GLOBAL_INSTANTIATION_SCN

     When you run one of these procedures at a destination database, you must ensure that every added object at the destination database is consistent with the source database as of the instantiation SCN.

     If you run SET_GLOBAL_INSTANTIATION_SCN at a destination database, then set the recursive parameter for this procedure to TRUE so that the instantiation SCN also is set for each schema at the destination database and for the tables owned by these schemas.

     If you run SET_SCHEMA_INSTANTIATION_SCN at a destination database, then set the recursive parameter for this procedure to TRUE so that the instantiation SCN also is set for each table in the schema.

     If you set the recursive parameter to TRUE in the SET_GLOBAL_INSTANTIATION_SCN procedure or the SET_SCHEMA_INSTANTIATION_SCN procedure, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the user who executes the procedure. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

     Alternatively, you can perform a metadata export/import to set the instantiation SCNs for existing database objects. If you choose this option, then ensure that no rows are imported. Also, ensure that every added object at the importing destination database is consistent with the source database that performed the export at the time of the export. If you are sharing DML changes only, then table level export/import is sufficient. If you are sharing DDL changes also, then additional considerations apply. See "Setting Instantiation SCNs Using Export/Import" for more information about performing a metadata export/import.

7. Start any Oracle Streams client you stopped in Step 2:

   • Use the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package to start a capture process.

   • Use the START_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package start a propagation.

   • Use the START_APPLY procedure in the DBMS_APPLY_ADM package to start an apply process.

   See Also:

   Oracle Streams Concepts and Administration for more information about completing these tasks using PL/SQL procedures

You must stop the capture process, disable one of the propagation jobs, or stop the apply process in Step 2 to ensure that the table or schema is instantiated before the first LCR resulting from the added rule(s) reaches the apply process. Otherwise, LCRs could be lost or could result in apply errors, depending on whether the apply process rule(s) have been added.

If you are certain that the added table is not being modified at the source database during this procedure, and that there are no LCRs for the table already in the stream or waiting to be captured, then you can perform Step 7 before Step 6 to reduce the amount of time that an Oracle Streams process or propagation job is stopped.

Adding a New Destination Database to a Single-Source Environment

You can add a destination database to an existing single-source environment by creating one or more new apply processes at the new destination database and, if necessary, configuring one or more propagations to send changes to the new destination database. You might also need to add rules to existing propagations in the stream that send changes to the new destination database.

As in the example that describes "Adding Replicated Objects to an Existing Single-Source Environment", before creating or altering propagation rules in a running Oracle Streams replication environment, ensure that any propagations or apply processes that will receive logical change records (LCRs) because of the new or altered rules are configured to handle these LCRs. Otherwise, LCRs might be lost.

This example assumes that the replicated database objects are read-only at the destination databases. If the replicated database objects are read/write at the destination databases, then the replication environment will not stay synchronized because Oracle Streams is not configured to replicate the changes made to the replicated database objects at the destination databases.

Figure 4-2 shows the additional configuration steps that must be completed to add a destination database to a single-source Oracle Streams environment.

Figure 4-2 Example of Adding a Destination to a Single-Source Environment

Description of "Figure 4-2 Example of Adding a Destination to a Single-Source Environment"

To avoid losing LCRs, you should complete the configuration in the following order:

1. Complete the necessary tasks to prepare each database in your environment for Oracle Streams. See "Tasks to Complete Before Configuring Oracle Streams Replication".

   Some of these tasks might not be required at certain databases.

2. Create any necessary ANYDATA queues that do not already exist at the destination database. When you create an apply process, you associate the apply process with a specific ANYDATA queue. See "Creating an ANYDATA Queue" for instructions.

3. Create one or more apply processes at the new destination database to apply the changes from its source database. Ensure that each apply process uses rule sets that are appropriate for applying changes. Do not start any of the apply processes at the new database. See Chapter 7, "Configuring Implicit Apply" for instructions.

   Keeping the apply processes stopped prevents changes made at the source databases from being applied before the instantiation of the new database is completed. Starting the apply processes might lead to incorrect data and errors.

4. Configure any necessary propagations to send changes from the source databases to the new destination database. Ensure that each propagation uses rule sets that are appropriate for propagating changes. See "Creating Oracle Streams Propagations Between ANYDATA Queues".

5. At the source database, prepare for instantiation each database object for which changes will be applied by an apply process at the new destination database.

   If you are using one or more capture processes, then run either the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table, specified schema, or entire database, respectively.

   If you are using one or more synchronous captures, then run the PREPARE_SYNC_INSTANTIATION function in the DBMS_CAPTURE_ADM package for the specified table.

   See "Preparing Database Objects for Instantiation at a Source Database".

6. At the new destination database, either instantiate, or set the instantiation SCNs for, each database object for which changes will be applied by an apply process. If the database objects do not already exist at the new destination database, then instantiate them using export/import, transportable tablespaces, or RMAN. If the database objects exist at the new destination database, then set the instantiation SCNs for them.

   • To instantiate database objects using export/import, first export them at the source database. Next, import them at the destination database. See Chapter 8, "Instantiation and Oracle Streams Replication".

     Do not allow any changes to the database objects being exported while exporting these database objects at the source database. Do not allow changes to the database objects being imported while importing these database objects at the destination database.

     You can specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

   • To set the instantiation SCN for a table, schema, or database manually, run the appropriate procedure or procedures in the DBMS_APPLY_ADM package at the new destination database:

     • SET_TABLE_INSTANTIATION_SCN

     • SET_SCHEMA_INSTANTIATION_SCN

     • SET_GLOBAL_INSTANTIATION_SCN

     When you run one of these procedures, you must ensure that the replicated database objects at the new destination database are consistent with the source database as of the instantiation SCN.

     If you run SET_GLOBAL_INSTANTIATION_SCN at a destination database, then set the recursive parameter for this procedure to TRUE so that the instantiation SCN also is set for each schema at the destination database and for the tables owned by these schemas.

     If you run SET_SCHEMA_INSTANTIATION_SCN at a destination database, then set the recursive parameter for this procedure to TRUE so that the instantiation SCN also is set for each table in the schema.

     If you set the recursive parameter to TRUE in the SET_GLOBAL_INSTANTIATION_SCN procedure or the SET_SCHEMA_INSTANTIATION_SCN procedure, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the user who executes the procedure. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

     Alternatively, you can perform a metadata export/import to set the instantiation SCNs for existing database objects. If you choose this option, then ensure that no rows are imported. Also, ensure that the replicated database objects at the importing destination database are consistent with the source database that performed the export at the time of the export. If you are sharing DML changes only, then table level export/import is sufficient. If you are sharing DDL changes also, then additional considerations apply. See "Setting Instantiation SCNs Using Export/Import" for more information about performing a metadata export/import.

7. Start the apply processes you created in Step 3 using the START_APPLY procedure in the DBMS_APPLY_ADM package.

Adding Replicated Objects to an Existing Multiple-Source Environment

You can add existing database objects to an existing multiple-source environment by adding the necessary rules to the appropriate capture processes, synchronous captures, propagations, and apply processes.

This example uses the following terms:

• Populated database: A database that already contains the replicated database objects being added to the multiple-source environment. You must have at least one populated database to add the objects to the environment.

• Export database: A populated database on which you perform an export of the database objects you are adding to the environment. This export is used to instantiate the added database objects at the import databases. You might not have an export database if all of the databases in the environment are populated databases.

• Import database: A database that does not contain the replicated database objects before they are added to the multiple-source environment. You instantiate the replicated database objects at an import database by performing an import of these database objects. You might not have any import databases if all of the databases in the environment are populated databases.

Before creating or altering capture or propagation rules in a running Oracle Streams replication environment, ensure that any propagations or apply processes that will receive logical change records (LCRs) because of the new or altered rules are configured to handle these LCRs. That is, the propagations or apply processes should exist, and each one should be associated with rule sets that handle the LCRs appropriately. If these propagations and apply processes are not configured properly to handle these LCRs, then LCRs can be lost.

For example, suppose you want to add a new table to an Oracle Streams replication environment that already captures, propagates, and applies changes to other tables. Assume multiple capture processes or synchronous captures in the environment will capture changes to this table, and multiple apply processes will apply changes to this table. In this case, you must add one or more table rules to the following rule sets:

• The positive rule set for each apply process that will apply changes to the table

• The positive rule set for each propagation that will propagate changes to the table

• The positive rule set for each capture process or synchronous capture that will capture changes to the table

If you perform administrative steps in the wrong order, then you can lose LCRs. For example, if you add the rule to a capture process rule set first, without stopping the capture process, then the propagation will not propagate the changes if it does not have a rule that instructs it to do so, and the changes can be lost.

Figure 4-3 shows the additional configuration steps that must be completed to add replicated database objects to a multiple-source Oracle Streams environment.

Figure 4-3 Example of Adding Replicated Objects to a Multiple-Source Environment

Description of "Figure 4-3 Example of Adding Replicated Objects to a Multiple-Source Environment"

When there are multiple source databases in an Oracle Streams replication environment, change cycling is possible. Change cycling happens when a change is sent back to the database where it originated. Typically, you should avoid change cycling. Before you configure your replication environment, see Chapter 10, "Oracle Streams Tags", and ensure that you configure the replication environment to avoid change cycling.

To avoid losing LCRs, complete the configuration in the following order:

1. At each populated database, specify any necessary supplemental logging for the objects being added to the environment. See "Specifying Supplemental Logging" for instructions.

2. Either stop all of the capture processes that will capture changes to the added objects, stop all of the propagations that will propagate changes to the added objects, or stop all of the apply process that will apply changes to the added objects:

   • Use the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop a capture process.

   • Use the STOP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to stop a propagation.

   • Use the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an apply process.

   In general, it is best to stop the capture process so that messages do not accumulate in queues during the operation.

   Note:

   Synchronous captures cannot be stopped.

   See Also:

   Oracle Streams Concepts and Administration for more information about completing these tasks using PL/SQL procedures

3. Add the relevant rules to the rule sets for the apply processes that will apply changes to the added objects. To add rules to the rule set for an apply process, you can run one of the following procedures:

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

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

4. Add the relevant rules to the rule sets for the propagations that will propagate changes to the added objects. To add rules to the rule set for a propagation, you can run one of the following procedures:

   • DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES

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

5. Add the relevant rules to the rule sets used by each capture process or synchronous capture that will capture changes to the added objects. 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:

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   • DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

   • DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

   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.

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

   • DBMS_STREAMS_ADM.ADD_TABLE_RULES

   • DBMS_STREAMS_ADM.ADD_SUBSET_RULES

   When you use a procedure in the DBMS_STREAMS_ADM package to add the capture process rules, it automatically runs the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table, specified schema, or entire database, respectively, if the capture process is a local capture process or a downstream capture process with a database link to the source database.

   You must run the appropriate procedure to prepare for instantiation manually if any of the following conditions is true:

   • You use DBMS_RULE_ADM to create or modify rules in a capture process rule set.

   • You do not add rules for the added objects to a capture process rule set, because the capture process already captures changes to these objects. In this case, rules for the objects can be added to propagations and apply processes in the environment, but not to the capture process.

   • You use a downstream capture process with no database link to the source database.

   If you must prepare for instantiation manually, then see "Preparing Database Objects for Instantiation at a Source Database" for instructions.

   When you use a procedure in the DBMS_STREAMS_ADM package to add the synchronous capture rules, it automatically runs the PREPARE_SYNC_INSTANTIATION function in the DBMS_CAPTURE_ADM package for the specified table.

After completing these steps, complete the steps in each of the following sections that apply to your environment. You might need to complete the steps in only one of these sections or in both of these sections:

• For each populated database, complete the steps in "Configuring Populated Databases When Adding Replicated Objects". These steps are required only if your environment has multiple populated databases.

• For each import database, complete the steps in "Adding Replicated Objects to Import Databases in an Existing Environment".

Adding a New Database to an Existing Multiple-Source Environment

Figure 4-4 shows the additional configuration steps that must be completed to add a source/destination database to a multiple-source Oracle Streams environment.

Figure 4-4 Example of Adding a Database to a Multiple-Source Environment

Description of "Figure 4-4 Example of Adding a Database to a Multiple-Source Environment"

When there are multiple source databases in an Oracle Streams replication environment, change cycling is possible. Change cycling happens when a change is sent back to the database where it originated. Typically, you should avoid change cycling. Before you configure your replication environment, see Chapter 10, "Oracle Streams Tags", and ensure that you configure the replication environment to avoid change cycling.

Complete the following steps to add a new source/destination database to an existing multiple-source Oracle Streams replication environment:

1. Complete the necessary tasks to prepare each database in your environment for Oracle Streams. See "Tasks to Complete Before Configuring Oracle Streams Replication".

   Some of these tasks might not be required at certain databases.

2. Create any necessary ANYDATA queues that do not already exist. When you create a capture process, synchronous capture, or apply process, you associate the process with a specific ANYDATA queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating an ANYDATA Queue" for instructions.

3. Create one or more apply processes at the new database to apply the changes from its source databases. Ensure that each apply process uses rule sets that are appropriate for applying changes. Do not start any apply process at the new database. See Chapter 7, "Configuring Implicit Apply" for instructions.

   Keeping the apply processes stopped prevents changes made at the source databases from being applied before the instantiation of the new database is completed. Starting the apply processes might lead to incorrect data and errors.

4. If the new database will be a source database, then, at all databases that will be destination databases for the changes made at the new database, create one or more apply processes to apply changes from the new database. Ensure that each apply process uses rule sets that are appropriate for applying changes. Do not start any of these new apply processes. See Chapter 7, "Configuring Implicit Apply" for instructions.

5. Configure propagations at the databases that will be source databases of the new database to send changes to the new database. Ensure that each propagation uses rule sets that are appropriate for propagating changes. See "Creating Oracle Streams Propagations Between ANYDATA Queues".

6. If the new database will be a source database, then configure propagations at the new database to send changes from the new database to each of its destination databases. Ensure that each propagation uses rule sets that are appropriate for propagating changes. See "Creating Oracle Streams Propagations Between ANYDATA Queues".

7. If the new database will be a source database, and the replicated database objects already exist at the new database, then specify any necessary supplemental logging for the replicated database objects at the new database. See "Specifying Supplemental Logging" for instructions.

8. At each source database for the new database, prepare for instantiation each database object for which changes will be applied by an apply process at the new database.

   If you are using one or more capture processes, then run either the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table, specified schema, or entire database, respectively.

   If you are using one or more synchronous captures, then run the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table.

   See "Preparing Database Objects for Instantiation at a Source Database" for instructions.

9. If the new database will be a source database, then create one or more capture processes or synchronous captures to capture the relevant changes. See Chapter 5, "Configuring Implicit Capture" for instructions. If you plan to use capture processes, then Oracle recommends that you use only one capture process for each source database.

   When you use a procedure in the DBMS_STREAMS_ADM package to add the capture process rules, it automatically runs the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table, specified schema, or entire database, respectively, if the capture process is a local capture process or a downstream capture process with a database link to the source database.

   You must run the appropriate procedure to prepare for instantiation manually if any of the following conditions is true:

   • You use the DBMS_RULE_ADM package to add or modify rules.

   • You use an existing capture process and do not add capture process rules for any replicated database object.

   • You use a downstream capture process with no database link to the source database.

   If you must prepare for instantiation manually, then see "Preparing Database Objects for Instantiation at a Source Database" for instructions.

   When you use a procedure in the DBMS_STREAMS_ADM package to add the synchronous capture rules, it automatically runs the PREPARE_SYNC_INSTANTIATION function in the DBMS_CAPTURE_ADM package for the specified table.

10. If the new database will be a source database, then start any capture process you created in Step 9 using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

After completing these steps, complete the steps in the appropriate section:

• If the objects that are to be replicated with the new database already exist at the new database, then complete the steps in "Configuring Databases If the Replicated Objects Already Exist at the New Database".

• If the objects that are to be replicated with the new database do not already exist at the new database, complete the steps in "Adding Replicated Objects to a New Database".