11
Configuring a Streams Environment

This chapter provides instructions for preparing a database or a distributed database environment to use Streams and for configuring a Streams environment.

This chapter contains these topics:

• Configuring a Streams Administrator
• Setting Initialization Parameters Relevant to Streams
• Setting Export and Import Parameters Relevant to Streams
• Configuring a Database to Run a Streams Capture Process
• Configuring Network Connectivity and Database Links
• Configuring a Capture-Based Streams Environment

Configuring a Streams Administrator

To manage a Streams environment, either create a new user with the appropriate privileges or grant these privileges to an existing user. You should not use the SYS or SYSTEM user as a Streams administrator, and the Streams administrator should not use the SYSTEM tablespace as its default tablespace.

Complete the following steps to configure a Streams administrator at each database in the environment that will use Streams:

1. Connect as an administrative user who can create users, grant privileges, create tablespaces, and alter users.
2. Create a new user to act as the Streams administrator or use an existing user. For example, to create a new user named strmadmin, run the following statement:

   CREATE USER strmadmin IDENTIFIED BY strmadminpw;
   

   Note: To ensure security, use a password other than strmadminpw for the Streams administrator.

3. Grant the Streams administrator at least the following privileges:

   GRANT CONNECT, RESOURCE TO strmadmin;
   GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
   GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
   
   BEGIN 
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
       privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
       grantee      => 'strmadmin', 
       grant_option => FALSE);
   END;
   /
   
   BEGIN 
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
       privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
       grantee      => 'strmadmin', 
       grant_option => FALSE);
   END;
   /
   
   

4. If necessary, grant the Streams administrator the following privileges:
   • EXECUTE privilege on the DBMS_APPLY_ADM package if the Streams administrator will manage one or more apply processes on the database. The Streams administrator also must have EXECUTE privilege on any apply handlers and error handlers configured using the subprograms in the DBMS_APPLY_ADM package.
   • EXECUTE privilege on the DBMS_CAPTURE_ADM package if the Streams administrator will manage one or more capture processes on the database
   • EXECUTE privilege on the DBMS_PROPAGATION_ADM package if the Streams administrator will manage one or more propagations on the database
   • EXECUTE privilege on the DBMS_FLASHBACK package if the Streams administrator will need to obtain the current SCN for a database. Typically, the Streams administrator must determine the current SCN to set an instantiation SCN using the SET_TABLE_INSTANTIATION_SCN, SET_SCHEMA_INSTANTIATION_SCN, or SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package.
   • SELECT_CATALOG_ROLE if you want to enable the Streams administrator to monitor the environment easily
   • SELECT ANY DICTIONARY privilege if you plan to use the Streams tool in Oracle Enterprise Manager
   • SELECT privilege on the DBA_APPLY_ERROR data dictionary view if you want the Streams administrator to be able to select from this view within a PL/SQL subprogram. See "Displaying Detailed Information About Apply Errors" for an example of such a PL/SQL subprogram.
   • If no apply user is specified for an apply process, then the necessary privileges to perform DML and DDL changes on the apply objects owned by another user. If an apply user is specified, then the apply user must have these privileges.
   • If no apply user is specified for an apply process, then EXECUTE privilege on any PL/SQL procedure owned by another user that is executed by a Streams apply process. These procedures may be used in apply handlers or error handlers. If an apply user is specified, then the apply user must have these privileges.
   • EXECUTE privilege on any PL/SQL function owned by another user that is specified in a rule-based transformation for a rule used by a Streams capture process, propagation, or apply process. For an apply process, if an apply user is specified, then the apply user must have these privileges.
   • If the Streams administrator does not own the queue used by a Streams capture process, propagation, or apply process, and is not specified as the queue user for the queue when the queue is created, then the Streams administrator must be configured as a secure queue user of the queue if you want the Streams administrator to be able to enqueue events into or dequeue events from the queue. The Streams administrator may also need ENQUEUE or DEQUEUE privileges on the queue, or both. See "Enabling a User to Perform Operations on a Secure Queue" for instructions.
5. Either create a tablespace for the Streams administrator or use an existing tablespace. For example, the following statement creates a new tablespace for the Streams administrator:

   CREATE TABLESPACE streams_tbs DATAFILE '/usr/oracle/dbs/streams_tbs.dbf' 
     SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   
   

6. Specify the tablespace for the Streams administrator:

   ALTER USER strmadmin DEFAULT TABLESPACE streams_tbs
                        QUOTA UNLIMITED ON streams_tbs;
   
   

7. Repeat all of the previous steps at each database in the environment that will use Streams.

Setting Initialization Parameters Relevant to Streams

Table 11-1 lists initialization parameters that are important for the operation, reliability, and performance of a Streams environment. Set these parameters appropriately for your Streams environment.

Table 11-1 Initialization Parameters Relevant to Streams (Page 1 of 4)

Parameter	Values	Description
AQ_TM_PROCESSES	Default: 0 Range: 0 to 10	Establishes queue monitor processes. Setting the parameter to 1 or more starts the specified number of queue monitor processes. These queue monitor processes are responsible for managing time-based operations of messages such as delay and expiration, cleaning up retained messages after the specified retention time, and cleaning up consumed messages if the retention time is zero. If you want to enqueue user events into a Streams queue, then set this parameter to 1 or higher. User events are those created by users and applications, not by a Streams capture process.
ARCHIVE_LAG_TARGET	Default: 0 Range: 0 or any integer in [60, 7200]	Limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after a user-specified time period elapses. If you are using Streams in a Real Application Clusters environment, then set this parameter to a value greater than zero to switch the log files automatically. See Also: "Streams Capture Processes and Oracle Real Application Clusters"
COMPATIBLE	Default: 8.1.0 Range: 8.1.0 to Current Release Number	This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use Streams, this parameter must be set to 9.2.0 or higher.
GLOBAL_NAMES	Default: false Range: true or false	Specifies whether a database link is required to have the same name as the database to which it connects. If you want to use Streams to share information between databases, then set this parameter to true at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES	Default: 0 Range: 0 to 1000	Specifies the number of Jn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by DBMS_JOB. You can change the setting for JOB_QUEUE_PROCESSES dynamically by using the ALTER SYSTEM statement. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
LOG_PARALLELISM	Default: 1 Range: 1 to 255	Specifies the level of concurrency for redo allocation within Oracle. If you plan to run one or more capture processes on a database, then this parameter must be set to 1. Setting this parameter to 1 does not affect the parallelism of capture. You can set parallelism for a capture process using the SET_PARAMETER procedure in the DBMS_CAPTURE_ADM package.
LOGMNR_MAX_PERSISTENT_SESSIONS	Default: 1 Range: 1 to LICENSE_MAX_SESSIONS	Specifies the maximum number of persistent LogMiner mining sessions that are concurrently active when all sessions are mining redo logs generated by instances. If you plan to run multiple Streams capture processes on a single database, then set this parameter equal to or higher than the number of planned capture processes.
OPEN_LINKS	Default: 4 Range: 0 to 255	Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, make sure this parameter is set to the default value of 4 or higher.
PARALLEL_MAX_SERVERS	Default: Derived from the values of the following parameters: CPU_COUNT PARALLEL_ADAPTIVE_MULTI_USER PARALLEL_AUTOMATIC_TUNING Range: 0 to 3599	Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process may use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
PROCESSES	Default: Derived from PARALLEL_MAX_SERVERS Range: 6 to operating system dependent limit	Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Make sure the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
SESSIONS	Default: Derived from: (1.1 * PROCESSES) + 5 Range: 1 to 231	Specifies the maximum number of sessions that can be created in the system. If you plan to run one or more capture processes or apply processes in a database, then you may need to increase the size of this parameter. Each background process in a database requires a session.
SGA_MAX_SIZE	Default: Initial size of SGA at startup Range: 0 to operating system dependent limit	Specifies the maximum size of SGA for the lifetime of a database instance. If you plan to run multiple capture processes on a single database, then you may need to increase the size of this parameter.
SHARED_POOL_SIZE	Default: 32-bit platforms: 8 MB, rounded up to the nearest granule size 64-bit platforms: 64 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: operating system-dependent	Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. You should increase the size of the shared pool by 10 MB for each capture process on a database.
TIMED_STATISTICS	Default: If STATISTICS_LEVEL is set to TYPICAL or ALL, then true If STATISTICS_LEVEL is set to BASIC, then false The default for STATISTICS_LEVEL is TYPICAL. Range: true or false	Specifies whether or not statistics related to time are collected. If you want to collect elapsed time statistics in the data dictionary views related to Streams, then set this parameter to true. The views that include elapsed time statistics include: V$STREAMS_CAPTURE, V$STREAMS_APPLY_COORDINATOR, V$STREAMS_APPLY_READER, V$STREAMS_APPLY_SERVER.

Setting Export and Import Parameters Relevant to Streams

This section describes Export and Import utility parameters that are relevant to Streams.

Export Utility Parameters Relevant to Streams

The following Export utility parameter is relevant to Streams.

The OBJECT_CONSISTENT Export Utility Parameter and Streams

The OBJECT_CONSISTENT Export utility parameter specifies whether or not the Export utility repeatedly uses the SET TRANSACTION READ ONLY statement to ensure that the exported data and the exported procedural actions for each object are consistent to a single point in time. If OBJECT_CONSISTENT is set to y, then each object is exported in its own read-only transaction, even if it is partitioned. In contrast, if you use the CONSISTENT Export utility parameter, then there is only one read-only transaction.

When you perform an instantiation in a Streams environment, some degree of consistency is required in the export dump file. The OBJECT_CONSISTENT Export utility parameter is sufficient to ensure this consistency for Streams instantiations. If you are using an export dump file for other purposes in addition to a Streams instantiation, and these other purposes have more stringent consistency requirements than that provided by OBJECT_CONSISTENT, then you can use Export utility parameters CONSISTENT, FLASHBACK_SCN, or FLASHBACK_TIME for Streams instantiations.

By default the OBJECT_CONSISTENT Export utility parameter is set to n. Specify y when an export is performed as part of a Streams instantiation and no more stringent Export utility parameter is needed.

Import Utility Parameters Relevant to Streams

The following Import utility parameters are relevant to Streams.

The STREAMS_INSTANTIATION Import Utility Parameter and Streams

The STREAMS_INSTANTIATION Import utility parameter specifies whether to import Streams instantiation metadata that may be present in the export dump file. When this parameter is set to y, the import session sets its Streams tag to the hexadecimal equivalent of '00' to avoid cycling the changes made by the import. Redo entries resulting from the import have this tag value. By default the STREAMS_INSTANTIATION Import utility parameter is set to n. Specify y when an import is performed as part of a Streams instantiation.

The STREAMS_CONFIGURATION Import Utility Parameter and Streams

The STREAMS_CONFIGURATION Import utility parameter specifies whether to import any general Streams metadata that may be present in the export dump file. This import parameter is relevant only if you are performing a full database import. By default the STREAMS_CONFIGURATION Import utility parameter is set to y. Typically, specify y if an import is part of a backup or restore operation.

The following objects are imported regardless of the STREAMS_CONFIGURATION setting:

• Streams queues and their queue tables (if STREAMS_CONFIGURATION is set to n, then these queues are not started when they are imported)
• Queue subscribers
• Advanced queuing agents
• Job queue processes related to Streams propagations
• Rules, including their rule sets and evaluation contexts. All rules are imported, including Streams rules and non-Streams rules. Streams rules are rules generated by the system when certain procedures in the DBMS_STREAMS_ADM package are run, while non-Streams rules are rules created using the DBMS_RULE_ADM package.

  If the STREAMS_CONFIGURATION parameter is set to n, then information about Streams rules is not imported into the following data dictionary views: ALL_STREAMS_GLOBAL_RULES, ALL_STREAMS_SCHEMA_RULES, ALL_STREAMS_TABLE_RULES, DBA_STREAMS_GLOBAL_RULES, DBA_STREAMS_SCHEMA_RULES, and DBA_STREAMS_TABLE_RULES. However, regardless of the STREAMS_CONFIGURATION parameter setting, information about these rules is imported into the ALL_RULES, ALL_RULE_SETS, ALL_RULE_SET_RULES, DBA_RULES, DBA_RULE_SETS, DBA_RULE_SET_RULES, USER_RULES, USER_RULE_SETS, and USER_RULE_SET_RULES data dictionary views.

When the STREAMS_CONFIGURATION Import utility parameter is set to y, the import includes the following information; when the STREAMS_CONFIGURATION Import utility parameter is set to n, the import does not include the following information:

• Capture processes, including the following information for each capture process:
  • Name of the capture process
  • State of the capture process
  • Capture process parameter settings
  • Queue owner and queue name of the queue used by the capture process
  • Rule set owner and rule set name of the rule set used by the capture process
• If any tables have been prepared for instantiation at the export database, then these tables are prepared for instantiation at the import database.
• If any schemas have been prepared for instantiation at the export database, then these schemas are prepared for instantiation at the import database.
• If the export database has been prepared for instantiation, then the import database is prepared for instantiation.
• The state of each Streams queue, either started or stopped (Streams queues themselves are imported regardless of the parameter setting)
• Propagations, including the following information for each propagation:
  • Name of the propagation
  • Queue owner and queue name of the source queue
  • Queue owner and queue name of the destination queue
  • Destination database link
  • Rule set owner and rule set name of the rule set used by the propagation
• Apply processes, including the following information for each apply process:
  • Name of the apply process
  • State of the apply process
  • Apply process parameter settings
  • Queue owner and queue name of the queue used by the apply process
  • Rule set owner and rule set name of the rule set used by the apply process
  • Whether the apply process applies captured or user-enqueued events
  • Apply user for the apply process, if one exists
  • Message handler used by the apply process, if one exists
  • DDL handler used by the apply process, if one exists
  • Tag generated in the redo log for changes made by the apply process
  • Apply database link, if one exists
  • Source database for the apply process
  • The information about apply progress in the DBA_APPLY_PROGRESS data dictionary view, including applied message number, oldest message number, apply time, and applied message create time
  • Apply errors
• DML handlers
• Error handlers
• Update conflict handlers
• Substitute key columns for apply tables
• Instantiation SCN for each apply object
• Ignore SCN for each apply object
• Some data dictionary information about Streams rules. The rules themselves are imported regardless of the setting for the STREAMS_CONFIGURATION parameter.

Configuring a Database to Run a Streams Capture Process

The following sections describe database requirements for running a Streams capture process:

• Configuring the Database to Run in ARCHIVELOG Mode
• Specifying an Alternate Tablespace for LogMiner

In addition to these tasks, make sure the initialization parameters are set properly on any database that will run a capture process.

Configuring the Database to Run in ARCHIVELOG Mode

Any database where changes are captured by a capture process must be running in ARCHIVELOG mode.

Specifying an Alternate Tablespace for LogMiner

By default, the LogMiner tables are in the SYSTEM tablespace, but the SYSTEM tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.

The following example creates a tablespace named logmnrts for use by LogMiner:

1. Connect as an administrative user who has privileges to create tablespaces and execute subprograms in the DBMS_LOGMNR_D package.
2. Either create an alternate tablespace for the LogMiner tables or use an existing tablespace. For example, the following statement creates an alternate tablespace for the LogMiner tables:

   CREATE TABLESPACE logmnrts DATAFILE '/usr/oracle/dbs/logmnrts.dbf' 
     SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   
   

3. Run the SET_TABLESPACE procedure in the DBMS_LOGMNR_D package to set the alternate tablespace for LogMiner. For example, to specify a tablespace named logmnrts, run the following procedure:

   EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');
   

Configuring Network Connectivity and Database Links

If you plan to use Streams to share information between databases, then configure network connectivity and database links between these databases:

• For Oracle databases, configure your network and Oracle Net so that the databases can communicate with each other.

  See Also: Oracle9i Net Services Administrator's Guide

• For non-Oracle databases, configure an Oracle gateway for communication between the Oracle database and the non-Oracle database.

  See Also: Oracle9i Heterogeneous Connectivity Administrator's Guide

• If you plan to propagate events from a source queue at a database to a destination queue at another database, then create a private database link between the database containing the source queue and the database containing the destination queue. Each database link should use a CONNECT TO clause for the user propagating events between databases.

  For example, to create a database link to a database named dbs2.net connecting as a Streams administrator named strmadmin, run the following statement:

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

  See Also: Oracle9i Database Administrator's Guide for more information about creating database links

Configuring a Capture-Based Streams Environment

This section describes the general steps for performing the following tasks:

• Creating a New Streams Single Source Environment
• Adding Shared Objects to an Existing Single Source Environment
• Adding a New Destination Database to an Existing Single Source Environment
• Creating a New Multiple Source Environment
• Adding Shared Objects to an Existing Multiple Source Environment
• Adding a New Database to an Existing Multiple Source Environment

  Note: The instructions in the following sections assume you will use the DBMS_STREAMS_ADM package to configure your Streams environment. If you use other packages, then extra steps may be necessary for each task.

  See Also: The following chapters for detailed examples of configuring Streams capture-based environments: Chapter 20, "Single Database Capture and Apply Example" Chapter 21, "Simple Single Source Replication Example" Chapter 22, "Single Source Heterogeneous Replication Example" Chapter 23, "Multiple Source Replication Example"

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 in this case 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 a rule set that handles the events appropriately. If these propagations and apply processes are not configured properly to handle these events, then events may be lost.

In general, if you are configuring a new Streams 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 described previously in this chapter to prepare each database in your environment for Streams:
   • "Configuring a Streams Administrator"
   • "Setting Initialization Parameters Relevant to Streams"
   • "Configuring a Database to Run a Streams Capture Process"
   • "Configuring Network Connectivity and Database Links"

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

2. Create any necessary Streams queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific Streams queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a Streams Queue" for instructions.
3. Specify supplemental logging at each source database for any shared object. See "Specifying Supplemental Logging at a Source Database" 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 a rule set that is appropriate for capturing changes. Do not start the capture processes you create. See "Creating a Capture Process" for instructions.

     When you use the DBMS_STREAMS_ADM package to add the capture 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.

     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 capture rules.
     • You use an existing capture process and do not add capture rules for any shared object.

     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 a rule set that is appropriate for propagating changes. See "Creating a Propagation" for instructions.
   • Create one or more apply processes at each database that will apply changes. Make sure each apply process uses a rule set that is appropriate for applying changes. Do not start the apply processes you create. See "Creating an Apply Process" for instructions.
5. Either instantiate, or set the instantiation SCN for, each database object for which changes are applied by an apply process. If a database object does not exist at a destination database, then instantiate it using Export/Import. If a database object already exists at a destination database, then set the instantiation SCN for it manually.
   • To instantiate database objects using Export/Import, first export them at the source database with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, import them at the destination database with the STREAMS_INSTANTIATION import parameter set to y. See "Setting Instantiation SCNs Using Export/Import" for information.
   • 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 you must also run SET_SCHEMA_INSTANTIATION_SCN for each existing schema in the source database whose DDL changes you are applying, and you must run SET_TABLE_INSTANTIATION_SCN for each existing table in the source database whose DML or DDL changes you are applying.

     If you run SET_SCHEMA_INSTANTIATION_SCN at a destination database, then you must also run SET_TABLE_INSTANTIATION_SCN for each existing source database table in the schema whose DML or DDL changes you are applying.

     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. See "Starting an Apply Process" for instructions.
7. Start each capture process you created in Step 4. See "Starting a Capture Process" for instructions.

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.

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 a rule set that handles 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-level rules to the following rule sets:

• The rule set for the capture process that will capture changes to the table
• The rule set for each propagation that will propagate changes to the table
• The rule set for the apply process that will apply changes to the table

If you perform administrative steps in the wrong order, you may lose events. For example, if you add the rule to the capture 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.

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

1. Either stop the capture process, disable one of the propagation jobs, or stop the apply processes. See one of the following sections for instructions:
   • "Stopping a Capture Process"
   • "Disabling a Propagation Job"
   • "Stopping an Apply Process"
2. Add the relevant rules to the rule sets for the propagations and the apply processes. See the following sections for instructions:
   • "Adding Rules to the Rule Set for a Propagation"
   • "Adding Rules to the Rule Set for an Apply Process"
3. Add the relevant rules to the rule set used by the capture process. See "Adding Rules to the Rule Set for a Capture Process" for instructions.

   When you use the DBMS_STREAMS_ADM package to add the capture 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.

   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 the capture process rule set.
   • You do not add rules for the added objects to the 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.

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

4. At each destination database, either instantiate, or set the instantiation SCN for, each database object you are adding to the Streams environment. If a database object does not exist at a destination database, then instantiate it using Export/Import. If a database object exists at a destination database, then set the instantiation SCN for it.
   • To instantiate database objects using Export/Import, first export them at the source database with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, import them at the destination database with the STREAMS_INSTANTIATION import parameter set to y. See "Setting Instantiation SCNs Using Export/Import" for information.
   • 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 you must also run SET_SCHEMA_INSTANTIATION_SCN for each existing source database schema whose changes you are applying and SET_TABLE_INSTANTIATION_SCN for each existing source database table whose changes you are applying.

     If you run SET_SCHEMA_INSTANTIATION_SCN at a destination database, then you must also run SET_TABLE_INSTANTIATION_SCN for each existing source database table in the schema whose DML or DDL changes you are applying.

     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.

5. Start any process you stopped in Step 1 or enable any propagation job you disabled in Step 1. See one of the following sections for instructions:
   • "Starting a Capture Process"
   • "Enabling a Propagation Job"
   • "Starting an Apply Process"

You must stop the capture process, disable one of the propagation jobs, or stop the apply process in Step 1 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 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 5 before Step 4 to reduce the amount of time that a process or propagation job is stopped.

Adding a New Destination Database to an Existing 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.

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

1. Complete the necessary tasks described previously in this chapter to prepare the new destination database for Streams:
   • "Configuring a Streams Administrator"
   • "Setting Initialization Parameters Relevant to Streams"
   • "Configuring Network Connectivity and Database Links"

   Some of these tasks may not be required at the new database.

2. Create any necessary Streams queues that do not already exist at the destination database. When you create an apply process, you associate the apply process with a specific Streams queue. See "Creating a Streams Queue" for instructions.
3. Create one or more apply processes at the new destination database to apply the changes from its source databases. Make sure each apply process uses a rule set that is appropriate for applying changes. Do not start any of the apply processes at the new database. See "Creating an Apply Process" 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 a rule set that is appropriate for propagating changes. See "Creating a Propagation".
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. If the database objects exist at the new destination database, then set the instantiation SCN for them.
   • To instantiate database objects using Export/Import, first export them at the source database with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, import them at the new destination database with the STREAMS_INSTANTIATION import parameter set to y. See "Setting Instantiation SCNs Using Export/Import" for information.
   • 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 you must also run SET_SCHEMA_INSTANTIATION_SCN for each existing schema in the source database whose DDL changes you are applying, and you must run SET_TABLE_INSTANTIATION_SCN for each existing table in the source database whose DML or DDL changes you are applying.

     If you run SET_SCHEMA_INSTANTIATION_SCN at a destination database, then you must also run SET_TABLE_INSTANTIATION_SCN for each existing source database table in the schema whose DML or DDL changes you are applying.

     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. See "Starting an Apply Process" for instructions.

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

• Populated database: A database that already contains the shared database objects before you create the new multiple source environment. You must have at least one populated database to create the new Streams environment.
• Export database: A populated database on which you perform an export of the shared database objects. This export is used to instantiate the shared database objects at the import databases. You may 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 shared database objects before you create the new multiple source environment. You instantiate the shared database objects at an import database using the export dump file from the export database. You may not have any import databases if all of the databases in the environment are populated databases.

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

1. Complete the necessary tasks described previously in this chapter to prepare each database in the environment for Streams:
   • "Configuring a Streams Administrator"
   • "Setting Initialization Parameters Relevant to Streams"
   • "Configuring a Database to Run a Streams Capture Process"
   • "Configuring Network Connectivity and Database Links"

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

2. At each populated database, specify any necessary supplemental logging for the shared objects. See "Specifying Supplemental Logging at a Source Database" for instructions.
3. Create any necessary Streams queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific Streams queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a Streams Queue" 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 a rule set that is appropriate for capturing changes. Do not start the capture processes you create. See "Creating a Capture Process" for instructions.

     When you use the DBMS_STREAMS_ADM package to add the capture 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.

     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 capture rules.
     • You use an existing capture process and do not add capture rules for any shared object.

     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 a rule set that is appropriate for propagating changes. See "Creating a Propagation" for instructions.
   • Create one or more apply processes at each database that will apply changes. Make sure each apply process uses a rule set that is appropriate for applying changes. Do not start the apply processes you create. See "Creating an Apply Process" 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:

• For each populated database, complete the steps in "Configuring Populated Databases When Creating a Multiple Source Environment".
• For each import database, complete the steps in "Adding Shared Objects to Import Databases When Creating a New Environment".

Configuring Populated Databases When Creating a Multiple Source Environment

After completing the steps in "Creating a New 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 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. 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 at a Destination Database" 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 for each table created during the instantiation.

3. At the export database you chose in Step 1, perform an export of the shared data with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, perform an import with the STREAMS_INSTANTIATION import parameter set to y at each import database. See "Setting Export and Import Parameters Relevant to Streams" and Oracle9i Database Utilities for information about using Export/Import.
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:

• "Creating a New Multiple Source Environment"
• "Configuring Populated Databases When Creating a Multiple Source Environment", if your environment has more than one populated database
• "Adding Shared Objects to Import Databases When Creating a New Environment", if your environment has one or more import databases

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

1. Start each apply process in the environment. See "Starting an Apply Process" for instructions.
2. Start each capture process the environment. See "Starting a Capture Process" for instructions.

   See Also: Chapter 23, "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:

• Populated database: A database that already contains the shared 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 may 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 shared database objects before they are added to the multiple source environment. You instantiate the added database objects at an import database using the export dump file from the export database. You may 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 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 a rule set that handles 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-level rules to the following rule sets:

• The rule set for each capture process that will capture changes to the table
• The rule set for each propagation that will propagate changes to the table
• The rule set for each apply process that will apply changes to the table.

If you perform administrative steps in the wrong order, you may lose events. For example, if you add the rule to the capture 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.

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 "Specifying Supplemental Logging at a Source Database" 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. See one of the following sections for instructions:
   • "Stopping a Capture Process"
   • "Disabling a Propagation Job"
   • "Stopping an Apply Process"
3. Add the relevant rules to the rule sets for the propagations and the apply processes that will propagate or apply changes to the added objects. See the following sections for instructions:
   • "Adding Rules to the Rule Set for a Propagation"
   • "Adding Rules to the Rule Set for an Apply Process"
4. Add the relevant rules to the rule set used by each capture process that will capture changes to the added objects. See "Adding Rules to the Rule Set for a Capture Process" for instructions.

   When you use the DBMS_STREAMS_ADM package to add the capture 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.

   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.

   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:

• For each populated database, complete the steps in "Configuring Populated Databases When Adding Shared Objects".
• For each import database, complete the steps in "Adding Shared Objects to Import Databases in an Existing Environment".

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:

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. 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 at a Destination Database" 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 for each table created during instantiation.

3. At the export database you chose in Step 1, perform an export of the added objects with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, perform an import of the added objects at each import database with the STREAMS_INSTANTIATION import parameter set to y. See "Setting Export and Import Parameters Relevant to Streams" and Oracle9i Database Utilities for information about using Export/Import.
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:

• "Adding Shared Objects to an Existing Multiple Source Environment"
• "Configuring Populated Databases When Adding Shared Objects", if your environment had populated databases
• "Adding Shared Objects to Import Databases in an Existing Environment", if your environment had import databases

When all of the previous configuration steps are finished, start each process you stopped and enable each propagation job you disabled in Step 2 in "Adding Shared Objects to an Existing Multiple Source Environment". See one of the following sections for instructions:

• "Starting a Capture Process"
• "Enabling a Propagation Job"
• "Starting an Apply Process"

Adding a New Database to an Existing Multiple Source Environment

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

1. Complete the necessary tasks described previously in this chapter to prepare the new database for Streams:
   • "Configuring a Streams Administrator"
   • "Setting Initialization Parameters Relevant to Streams"
   • "Configuring a Database to Run a Streams Capture Process"
   • "Configuring Network Connectivity and Database Links"

   Some of these tasks may not be required at the new database.

2. Create any necessary Streams queues that do not already exist. When you create a capture process or apply process, you associate the process with a specific Streams queue. When you create a propagation, you associate it with a specific source queue and destination queue. See "Creating a Streams Queue" 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 a rule set that is appropriate for applying changes. Do not start any apply process at the new database. See "Creating an Apply Process" 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 a rule set that is appropriate for applying changes. Do not start any of these new apply processes. See "Creating an Apply Process" 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 a rule set that is appropriate for propagating changes. See "Creating a Propagation".
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 a rule set that is appropriate for propagating changes. See "Creating a Propagation".
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 "Specifying Supplemental Logging at a Source Database" 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.

   When you use the DBMS_STREAMS_ADM package to add the capture 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.

   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 capture rules.
   • You use an existing capture process and do not add capture rules for any shared object.

   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 processes you created in Step 9. See "Starting a Capture Process" for instructions.

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

• If the objects that are to be shared with the new database already exist at the new database, then complete the steps in "Configuring Databases If the Shared Objects Already Exist at the New Database".
• If the objects that are to be shared with the new database do not already exist at the new database, complete the steps in "Adding Shared Objects to a New Database".

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. Start the apply processes that you created at the new database in Step 3. See "Starting an Apply Process" for instructions.
4. Start the apply processes that you created at each of the other destination databases in Step 4. See "Starting an Apply Process" for instructions. 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 at a Destination Database" for instructions.

   Because you are running these procedures before any tables are instantiated at the new database, and because the local capture processes are configured already at the new database, you will not need to run the SET_TABLE_INSTANTIATION_SCN for each table created during instantiation.

   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 the export at the source database with the OBJECT_CONSISTENT export parameter set to y, or use a more stringent degree of consistency. Then, perform the import at the new database with the STREAMS_INSTANTIATION import parameter set to y. See "Setting Export and Import Parameters Relevant to Streams" and Oracle9i Database Utilities for information about using Export/Import.
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. Start the apply processes at the new database that you created in Step 3. See "Starting an Apply Process" for instructions.
5. Start the apply processes at each of the other destination databases that you created in Step 4. See "Starting an Apply Process" for instructions. If the new database is not a source database, then do not complete this step.