Skip Headers

Oracle® Streams Replication Administrator's Guide
10g Release 1 (10.1)

Part Number B10728-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

6
Configuring Streams Replication

This chapter contains instructions for configuring Streams single source and multiple source replication environments. This chapter also includes instructions for adding objects and databases to an existing Streams replication environment.

This chapter contains these topics:

Creating a New Streams Single Source Environment

This section lists the general steps to perform when creating a new single source Streams environment. A single source environment is one in which there is only one source database for shared data. There may be more than one source database in a single source environment, but no two source databases capture any of the same data.

Before starting capture processes and configuring propagations in a new Streams environment, make sure any propagations or apply processes that will receive events are configured to handle these events. That is, the propagations or apply processes should exist, and each one should be associated with rule sets that handle the events appropriately. If these propagations and apply processes are not configured properly to handle these events, then events may be lost.

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

Figure 6-1 shows an example Streams single source replication environment.

Figure 6-1 Example Streams Single Source Environment

Text description of strep038.gif follows

Text description of the illustration strep038.gif

You may create a Streams environment that is more complicated than the one shown in Figure 6-1. For example, a single source Streams environment may use downstream capture and directed networks.

In general, if you are configuring a new Streams single source environment in which changes for shared objects are captured at one database and then propagated and applied at remote databases, then you should configure the environment in the following order:

  1. Complete the necessary tasks to prepare each database in your environment for Streams:
    • Configure a Streams administrator
    • Set initialization parameters relevant to Streams
    • For each database that will run a capture process, prepare the database to run a capture process
    • Configure network connectivity and database links

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

    See Also:

    Oracle Streams Concepts and Administration for more information about preparing a database for Streams

  2. Create any necessary SYS.AnyData queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific SYS.AnyData queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a SYS.AnyData Queue to Stage LCRs" for instructions.
  3. Specify supplemental logging at each source database for any shared object. See "Managing Supplemental Logging in a Streams Replication Environment" for instructions.
  4. At each database, create the required capture processes, propagations, and apply processes for your environment. You can create them in any order.
    • Create one or more capture processes at each database that will capture changes. Make sure each capture process uses rule sets that are appropriate for capturing changes. Do not start the capture processes you create. Oracle Corporation recommends that you use only one capture process for each source database. See "Creating a Capture Process" for instructions.

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

    • Create all propagations that propagate the captured events from a source queue to a destination queue. Make sure each propagation uses rule sets that are appropriate for propagating changes. See "Creating a Propagation That Propagates LCRs" for instructions.
    • Create one or more apply processes at each database that will apply changes. Make sure each apply process uses rule sets that are appropriate for applying changes. Do not start the apply processes you create. See "Creating an Apply Process That Applies LCRs" for instructions.
  5. Either instantiate, or set the instantiation SCN for, each database object for which changes are applied by an apply process. 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 "Setting Instantiation SCNs Using Export/Import" for information. Also, see "Instantiating Objects in a Streams Replication Environment" for information about instantiating objects using export/import, transportable tablespaces, and RMAN.

      If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

      If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

    • 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 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 shared objects at the 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 make sure no rows are imported. Also, make sure the shared objects at all of the destination databases 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.

  6. Start each apply process you created in Step 4 using the START_APPLY procedure in the DBMS_APPLY_ADM package.
  7. Start each capture process you created in Step 4 using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

When you are configuring the environment, remember that capture processes and apply processes are stopped when they are created, but propagations are scheduled to propagate events immediately when they are created. The capture process must be created before the relevant objects are instantiated at a remote destination database. You must create the propagations and apply processes before starting the capture process, and you must instantiate the objects before running the whole stream.

See Also:

Adding Shared Objects to an Existing Single Source Environment

You add existing database objects to an existing single source environment by adding the necessary rules to the appropriate capture processes, propagations, and apply processes. Before creating or altering capture or propagation rules in a running Streams environment, make sure any propagations or apply processes that will receive events as a result of the new or altered rules are configured to handle these events. That is, the propagations or apply processes should exist, and each one should be associated with rule sets that handle the events appropriately. If these propagations and apply processes are not configured properly to handle these events, then events may be lost.

For example, suppose you want to add a table to a Streams environment that already captures, propagates, and applies changes to other tables. Assume only one capture process 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:

If you perform administrative steps in the wrong order, you may lose events. 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 may be lost.

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

Figure 6-2 shows the additional configuration steps that must be completed to add shared database objects to a single source Streams environment.

Figure 6-2 Example of Adding Shared Objects to a Single Source Environment

Text description of strep041.gif follows

Text description of the illustration strep041.gif

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

  1. At each source database where shared objects are being added, specify supplemental logging for the added shared objects. See "Managing Supplemental Logging in a Streams Replication Environment" for instructions.
  2. Either stop the capture process, disable one of the propagation jobs, or stop the apply processes:
    • Use the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop a capture process.
    • Use the DISABLE_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package to disable a propagation job.
    • Use the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an apply process.

      See Also:

      Oracle Streams Concepts and Administration for more information about completing these tasks

  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:

    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:

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

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

  6. At each destination database, either instantiate, or set the instantiation SCN for, each database object you are adding to the 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 "Setting Instantiation SCNs Using Export/Import" for information. Also, see "Instantiating Objects in a Streams Replication Environment" for information about instantiating objects using export/import, transportable tablespaces, and RMAN.

      If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

      If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

    • 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 make sure no rows are imported. Also, make sure 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 Streams process you stopped in Step 2 or enable any propagation job you disabled in Step 2:
    • Use the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package to start a capture process.
    • Use the ENABLE_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package to enable a propagation job.
    • 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

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, events 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 a Streams process or propagation job is stopped.

See Also:

"Add Objects to an Existing Streams Replication Environment" for a detailed example that adds objects to an existing single source environment

Adding a New Destination Database to a Single Source Environment

You 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 propagate changes to the new destination database. You may also need to add rules to existing propagations in the stream that propagates to the new destination database.

As in the example that describes "Adding Shared Objects to an Existing Single Source Environment", before creating or altering propagation rules in a running Streams environment, make sure any propagations or apply processes that will receive events as a result of the new or altered rules are configured to handle these events. Otherwise, events may be lost.

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

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

Figure 6-3 Example of Adding a Destination to a Single Source Environment

Text description of strep040.gif follows

Text description of the illustration strep040.gif

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

  1. Complete the necessary tasks to prepare each database in your environment for Streams:
    • Configure a Streams administrator
    • Set initialization parameters relevant to Streams
    • Configure network connectivity and database links

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

    See Also:

    Oracle Streams Concepts and Administration for more information about preparing a database for Streams

  2. Create any necessary SYS.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 SYS.AnyData queue. See "Creating a SYS.AnyData Queue to Stage LCRs" for instructions.
  3. Create one or more apply processes at the new destination database to apply the changes from its source database. Make sure 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 "Creating an Apply Process That Applies LCRs" 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, which would otherwise lead to incorrect data and errors.

  4. Configure any necessary propagations to propagate changes from the source databases to the new destination database. Make sure each propagation uses rule sets that are appropriate for propagating changes. See "Creating a Propagation That Propagates LCRs".
  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. 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. See "Preparing Database Objects for Instantiation at a Source Database" for instructions.
  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 "Setting Instantiation SCNs Using Export/Import" for information. Also, see "Instantiating Objects in a Streams Replication Environment" for information about instantiating objects using export/import, transportable tablespaces, and RMAN.

      If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

      If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

    • 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 shared 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 make sure no rows are imported. Also, make sure the shared 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.

    See Also:

    "Add a Database to an Existing Streams Replication Environment" for detailed example that adds a database to an existing single source environment

Creating a New Streams Multiple Source Environment

This section lists the general steps to perform when creating a new multiple source Streams environment. A multiple source environment is one in which there is more than one source database for any of the shared data.

This example uses the following terms:

Figure 6-4 shows an example multiple source Streams environment.

Figure 6-4 Example Streams Multiple Source Environment

Text description of strep039.gif follows

Text description of the illustration strep039.gif

You may create a Streams environment that is more complicated than the one shown in Figure 6-4. For example, a multiple source Streams environment may use downstream capture and directed networks.

Complete the following steps to create a new multiple source environment:


Note:

Make sure no changes are made to the objects being shared at a database you are adding to the Streams environment until the instantiation at the database is complete.


  1. Complete the necessary tasks to prepare each database in your environment for Streams:
    • Configure a Streams administrator
    • Set initialization parameters relevant to Streams
    • For each database that will run a capture process, prepare the database to run a capture process
    • Configure network connectivity and database links

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

    See Also:

    Oracle Streams Concepts and Administration for more information about preparing a database for Streams

  2. At each populated database, specify any necessary supplemental logging for the shared objects. See "Managing Supplemental Logging in a Streams Replication Environment" for instructions.
  3. Create any necessary SYS.AnyData queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific SYS.AnyData queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a SYS.AnyData Queue to Stage LCRs" for instructions.
  4. At each database, create the required capture processes, propagations, and apply processes for your environment. You can create them in any order.
    • Create one or more capture processes at each database that will capture changes. Make sure each capture process uses rule sets that are appropriate for capturing changes. Do not start the capture processes you create. Oracle Corporation recommends that you use only one capture process for each source database. See "Creating a Capture Process" for instructions.

      When you 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 shared 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.

    • Create all propagations that propagate the captured events from a source queue to a destination queue. Make sure each propagation uses rule sets that are appropriate for propagating changes. See "Creating a Propagation That Propagates LCRs" for instructions.
    • Create one or more apply processes at each database that will apply changes. Make sure each apply process uses rule sets that are appropriate for applying changes. Do not start the apply processes you create. See "Creating an Apply Process That Applies LCRs" for instructions.

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

Configuring Populated Databases When Creating a Multiple Source Environment

After completing the steps in "Creating a New Streams Multiple Source Environment", complete the following steps for the populated databases if your environment has more than one populated database:

  1. For each populated database, set the instantiation SCN at each of the other populated databases in the environment that will be a destination database of the populated source database. These instantiation SCNs must be set, and only the changes made at a particular populated database that are committed after the corresponding SCN for that database will be applied at another populated database.

    For each populated database, you can set these instantiation SCNs in one of the following ways:

    1. Perform a metadata only export of the shared objects at the populated database and import the metadata at each of the other populated databases. Such an import sets the required instantiation SCNs for the populated database at the other populated databases. Make sure no rows are imported. Also, make sure the shared objects at each populated database performing a metadata import are consistent with the populated database that performed the metadata 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.

    2. Set the instantiation SCNs manually at each of the other populated databases. Do this for each of the shared objects. Make sure the shared objects at each populated database are consistent with the instantiation SCNs you set at that database. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

Adding Shared Objects to Import Databases When Creating a New Environment

After completing the steps in "Creating a New Streams Multiple Source Environment", complete the following steps for the import databases:

  1. Pick the populated database that you will use as the export database. Do not perform the instantiations yet.
  2. For each import database, set the instantiation SCNs at all of the other databases in the environment that will be a destination database of the import database. In this case, the import database will be the source database for these destination databases. The databases where you set the instantiation SCNs may include populated databases and other import databases.
    1. If one or more schemas will be created at an import database during instantiation or by a subsequent shared DDL change, then run the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for this import database at all of the other databases in the environment.
    2. If a schema exists at an import database, and one or more tables will be created in the schema during instantiation or by a subsequent shared DDL change, then run the SET_SCHEMA_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for the schema at all of the other databases in the environment for the import database. Do this for each such schema.

    See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

    Because you are running these procedures before any tables are instantiated at the import databases, and because the local capture processes are configured already for these import databases, you will not need to run the SET_TABLE_INSTANTIATION_SCN procedure for each table created during the instantiation. Instantiation SCNs will be set automatically for these tables at all of the other databases in the environment that will be destination databases of the import database.

  3. At the export database you chose in Step 1, perform an export of the shared objects. Next, perform an import of the shared objects at each import database. See "Instantiating Objects in a Streams Replication Environment" and Oracle Database Utilities for information about using export/import.

    If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

    If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

  4. For each populated database, except for the export database, set the instantiation SCNs at each import database that will be a destination database of the populated source database. These instantiation SCNs must be set, and only the changes made at a populated database that are committed after the corresponding SCN for that database will be applied at an import database.

    You can set these instantiation SCNs in one of the following ways:

    1. Perform a metadata only export at each populated database and import the metadata at each import database. Each import sets the required instantiation SCNs for the populated database at the import database. In this case, ensure that the shared objects at the import database are consistent with the populated database 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.

    2. For each populated database, set the instantiation SCN manually for each shared object at each import database. Make sure the shared objects at each import database are consistent with the populated database as of the corresponding instantiation SCN. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

Complete the Multiple Source Environment Configuration

Before completing the steps in this section, you should have completed the following tasks:

When all of the previous configuration steps are finished, complete the following steps:

  1. At each database, configure conflict resolution if conflicts are possible. See "Managing Streams Conflict Detection and Resolution" for instructions.
  2. Start each apply process in the environment using the START_APPLY procedure in the DBMS_APPLY_ADM package.
  3. Start each capture process the environment using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

    See Also:

    Chapter 14, "Multiple Source Replication Example" for a detailed example that creates a multiple source environment

Adding Shared Objects to an Existing Multiple Source Environment

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

This example uses the following terms:

Before creating or altering capture or propagation rules in a running Streams environment, make sure any propagations or apply processes that will receive events as a result of the new or altered rules are configured to handle these events. That is, the propagations or apply processes should exist, and each one should be associated with rule sets that handle the events appropriately. If these propagations and apply processes are not configured properly to handle these events, then events may be lost.

For example, suppose you want to add a new table to a Streams environment that already captures, propagates, and applies changes to other tables. Assume multiple capture processes 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:

If you perform administrative steps in the wrong order, you may lose events. 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 may be lost.

Figure 6-5 shows the additional configuration steps that must be completed to add shared database objects to a multiple source Streams environment.

Figure 6-5 Example of Adding Shared Objects to a Multiple Source Environment

Text description of strep042.gif follows

Text description of the illustration strep042.gif

To avoid losing events, you should 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 "Managing Supplemental Logging in a Streams Replication Environment" for instructions.
  2. Either stop all of the capture processes that will capture changes to the added objects, disable all of the propagation jobs 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 DISABLE_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package to disable a propagation job.
    • Use the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an apply process.

      See Also:

      Oracle Streams Concepts and Administration for more information about completing these tasks

  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:

    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:

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

    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.

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

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

Configuring Populated Databases When Adding Shared Objects

After completing the steps in "Adding Shared Objects to an Existing Multiple Source Environment", complete the following steps for each populated database if your environment has more than one populated database:

  1. For each populated database, set the instantiation SCN for each added object at the other populated databases in the environment. These instantiation SCNs must be set, and only the changes made at a particular populated database that are committed after the corresponding SCN for that database will be applied at another populated database.

    For each populated database, you can set these instantiation SCNs for each added object in one of the following ways:

    1. Perform a metadata only export of the added objects at the populated database and import the metadata at each of the other populated databases. Such an import sets the required instantiation SCNs for the database at the other databases. Make sure no rows are imported. Also, make sure the shared objects at each of the other populated databases are consistent with the populated 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.

    2. Set the instantiation SCNs manually for the added objects at each of the other populated databases. Make sure every added object at each populated database is consistent with the instantiation SCNs you set at that database. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

Adding Shared Objects to Import Databases in an Existing Environment

After completing the steps in "Adding Shared Objects to an Existing Multiple Source Environment", complete the following steps for the import databases:

  1. Pick the populated database that you will use as the export database. Do not perform the instantiations yet.
  2. For each import database, set the instantiation SCNs for the added objects at all of the other databases in the environment that will be a destination database of the import database. In this case, the import database will be the source database for these destination databases. The databases where you set the instantiation SCNs may be populated databases and other import databases.
    1. If one or more schemas will be created at an import database during instantiation or by a subsequent shared DDL change, then run the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for this import database at all of the other databases in the environment.
    2. If a schema exists at an import database, and one or more tables will be created in the schema during instantiation or by a subsequent shared DDL change, then run the SET_SCHEMA_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for the schema for this import database at each of the other databases in the environment. Do this for each such schema.

    See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

    Because you are running these procedures before any tables are instantiated at the import databases, and because the local capture processes are configured already for these import databases, you will not need to run the SET_TABLE_INSTANTIATION_SCN procedure for each table created during instantiation. Instantiation SCNs will be set automatically for these tables at all of the other databases in the environment that will be destination databases of the import database.

  3. At the export database you chose in Step 1, perform an export of the shared objects. Next, perform an import of the shared objects at each import database. See "Instantiating Objects in a Streams Replication Environment" and Oracle Database Utilities for information about using export/import.

    If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

    If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

  4. For each populated database, except for the export database, set the instantiation SCNs for the added objects at each import database that will be a destination database of the populated source database. These instantiation SCNs must be set, and only the changes made at a populated database that are committed after the corresponding SCN for that database will be applied at an import database.

    For each populated database, you can set these instantiation SCNs for the added objects in one of the following ways:

    1. Perform a metadata only export of the added objects at the populated database and import the metadata at each import database. Each import sets the required instantiation SCNs for the populated database at the import database. In this case, ensure that every added object at the import database is consistent with the populated database 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.

    2. Set the instantiation SCNs manually for the added objects at each import database. Make sure every added object at each import database is consistent with the populated database as of the corresponding instantiation SCN. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

Complete the Adding Objects to a Multiple Source Environment Configuration

Before completing the configuration, you should have completed the following tasks:

When all of the previous configuration steps are finished, complete the following steps:

  1. At each database, configure conflict resolution for the added database objects if conflicts are possible. See "Managing Streams Conflict Detection and Resolution" for instructions.
  2. Start each Streams process you stopped and enable each propagation job you disabled in Step 2 in "Adding Shared Objects to an Existing Multiple Source Environment":
    • Use the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package to start a capture process.
    • Use the ENABLE_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package to enable a propagation job.
    • 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

Adding a New Database to an Existing Multiple Source Environment

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

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

Text description of strep043.gif follows

Text description of the illustration strep043.gif

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


Note:

Make sure no changes are made to the objects being shared at the database you are adding to the Streams environment until the instantiation at the database is complete.


  1. Complete the necessary tasks to prepare each database in your environment for Streams:
    • Configure a Streams administrator
    • Set initialization parameters relevant to Streams
    • For each database that will run a capture process, prepare the database to run a capture process
    • Configure network connectivity and database links

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

    See Also:

    Oracle Streams Concepts and Administration for more information about preparing a database for Streams

  2. Create any necessary SYS.AnyData queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific SYS.AnyData queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a SYS.AnyData Queue to Stage LCRs" for instructions.
  3. Create one or more apply processes at the new database to apply the changes from its source databases. Make sure each apply process uses rule sets that are appropriate for applying changes. Do not start any apply process at the new database. See "Creating an Apply Process That Applies LCRs" 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, which would otherwise 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. Make sure each apply process uses rule sets that are appropriate for applying changes. Do not start any of these new apply processes. See "Creating an Apply Process That Applies LCRs" for instructions.
  5. Configure propagations at the databases that will be source databases of the new database to send changes to the new database. Make sure each propagation uses rule sets that are appropriate for propagating changes. See "Creating a Propagation That Propagates LCRs".
  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. Make sure each propagation uses rule sets that are appropriate for propagating changes. See "Creating a Propagation That Propagates LCRs".
  7. If the new database will be a source database, and the shared objects already exist at the new database, then specify any necessary supplemental logging for the shared objects at the new database. See "Managing Supplemental Logging in a Streams Replication Environment" 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. 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. 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 to capture the relevant changes. See "Creating a Capture Process" for instructions. Oracle Corporation 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 shared 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.

  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:

Configuring Databases If the Shared Objects Already Exist at the New Database

After completing the steps in "Adding a New Database to an Existing Multiple Source Environment", complete the following steps if the objects that are to be shared with the new database already exist at the new database:

  1. For each source database of the new database, set the instantiation SCNs at the new database. These instantiation SCNs must be set, and only the changes made at a source database that are committed after the corresponding SCN for that database will be applied at the new database.

    For each source database of the new database, you can set these instantiation SCNs in one of the following ways:

    1. Perform a metadata only export of the shared objects at the source database and import the metadata at the new database. The import sets the required instantiation SCNs for the source database at the new database. Make sure no rows are imported. In this case, ensure that the shared objects at the new database are consistent with the source database 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.

    2. Set the instantiation SCNs manually at the new database for the shared objects. Make sure the shared objects at the new database are consistent with the source database as of the corresponding instantiation SCN. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.
  2. For the new database, set the instantiation SCNs at each destination database of the new database. These instantiation SCNs must be set, and only the changes made at the new source database that are committed after the corresponding SCN will be applied at a destination database. If the new database is not a source database, then do not complete this step.

    You can set these instantiation SCNs for the new database in one of the following ways:

    1. Perform a metadata only export at the new database and import the metadata at each destination database. Make sure no rows are imported. The import sets the required instantiation SCNs for the new database at each destination database. In this case, ensure that the shared objects at each destination database are consistent with the new database 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.

    2. Set the instantiation SCNs manually at each destination database for the shared objects. Make sure the shared objects at each destination database are consistent with the new database as of the corresponding instantiation SCN. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.
  3. At the new database, configure conflict resolution if conflicts are possible. See "Managing Streams Conflict Detection and Resolution" for instructions.
  4. Start the apply processes that you created at the new database in Step 3 using the START_APPLY procedure in the DBMS_APPLY_ADM package.
  5. Start the apply processes that you created at each of the other destination databases in Step 4. If the new database is not a source database, then do not complete this step.

Adding Shared Objects to a New Database

After completing the steps in "Adding a New Database to an Existing Multiple Source Environment", complete the following steps if the objects that are to be shared with the new database do not already exist at the new database:

  1. If the new database is a source database for other databases, then, at each destination database of the new source database, set the instantiation SCNs for the new database.
    1. If one or more schemas will be created at the new database during instantiation or by a subsequent shared DDL change, then run the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for the new database at each destination database of the new database.
    2. If a schema exists at the new database, and one or more tables will be created in the schema during instantiation or by a subsequent shared DDL change, then run the SET_SCHEMA_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package for the schema at each destination database of the new database. Do this for each such schema.

    See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.

    Because you are running these procedures before any tables are instantiated at the new database, and because the local capture process is configured already at the new database, you will not need to run the SET_TABLE_INSTANTIATION_SCN procedure for each table created during instantiation. Instantiation SCNs will be set automatically for these tables at all of the other databases in the environment that will be destination databases of the new database.

    If the new database will not be a source database, then do not complete this step, and continue with the next step.

  2. Pick one source database from which to instantiate the shared objects at the new database using export/import. First, perform an export of the shared objects. Next, perform an import of the shared objects at the new database. See "Instantiating Objects in a Streams Replication Environment" and Oracle Database Utilities for information about using export/import.

    If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

    If you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

  3. For each source database of the new database, except for the source database that performed the export for instantiation in Step 2, set the instantiation SCNs at the new database. These instantiation SCNs must be set, and only the changes made at a source database that are committed after the corresponding SCN for that database will be applied at the new database.

    For each source database, you can set these instantiation SCNs in one of the following ways:

    1. Perform a metadata only export at the source database and import the metadata at the new database. The import sets the required instantiation SCNs for the source database at the new database. In this case, ensure that the shared objects at the new database are consistent with the source database 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.

    2. Set the instantiation SCNs manually at the new database for the shared objects. Make sure the shared objects at the new database are consistent with the source database as of the corresponding instantiation SCN. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package" for instructions.
  4. At the new database, configure conflict resolution if conflicts are possible. See "Managing Streams Conflict Detection and Resolution" for instructions.
  5. Start the apply processes that you created in Step 3 at the new database using the START_APPLY procedure in the DBMS_APPLY_ADM package.
  6. Start the apply processes that you created in Step 4 at each of the other destination databases. If the new database is not a source database, then do not complete this step.