2
Instantiation and Streams Replication

This chapter contains conceptual information about instantiation and Streams replication.

This chapter contains these topics:

• Overview of Instantiation and Streams Replication
• Capture Process Rules and Preparation for Instantiation
• Oracle Data Pump and Streams Instantiation
• Original Export/Import and Streams Instantiation

  See Also: Chapter 8, "Performing Instantiations"

Overview of Instantiation and Streams Replication

In a Streams environment that shares a database object within a single database or between multiple databases, a source database is the database where changes to the object are generated in the redo log, and a destination database is the database where these changes are dequeued by an apply process. If a capture process captures, or will capture, such changes, and the changes will be applied locally or propagated to other databases and applied at destination databases, then you must instantiate these source database objects before you can replicate changes to the objects. If a database where changes to the source database objects will be applied is a different database than the source database, then the destination database must have a copy of these database objects.

In Streams, the following general steps instantiate a database object:

1. Prepare the object for instantiation at the source database.
2. If a copy of the object does not exist at the destination database, then create an object physically at the destination database based on an object at the source database. You can use export/import, transportable tablespaces, or RMAN to copy database objects for instantiation. If the database object already exists at the destination database, then this step is not necessary.
3. Set the instantiation SCN for the database object at the destination database. An instantiation SCN instructs an apply process at the destination database to apply only changes that committed at the source database after the specified SCN.

In some cases, Step 1 and Step 3 are completed automatically. For example, when you add rules for an object to the positive rule set for a capture process by running a procedure in the DBMS_STREAMS_ADM package, the object is prepared for instantiation automatically. Also, when you use export/import or transportable tablespaces to copy database objects from a source database to a destination database, instantiation SCNs may be set for these objects automatically.

If the database object being instantiated is a table, then the objects at the source and destination database do not need to be an exact match. However, if some or all of the table data is replicated between the two databases, then the data that is replicated should be consistent when the table is instantiated. Whenever you plan to replicate changes to a database object, you must always prepare the object for instantiation at the source database and set the instantiation SCN for the object at the destination database. By preparing an object for instantiation, you are setting the lowest SCN for which changes to the object may need to be applied at destination databases. This SCN is called the ignore SCN. You should prepare a database object for instantiation after a capture process has been configured to capture changes to the object.

When you instantiate tables using export/import, transportable tablespaces, or RMAN, any table supplemental log group specifications are retained for the instantiated tables. That is, after instantiation, log group specifications for imported tables at the import database are the same as the log group specifications for these tables at the export database. If you do not want to retain supplemental log group specifications for tables at the import database, then you can drop specific supplemental log groups after import.

Database supplemental logging specifications are not retained during export/import, even if you perform a full database export/import. However, RMAN retains database supplemental logging specifications at the instantiated database.

The following sections provide more information about instantiation and Streams replication:

• Capture Process Rules and Preparation for Instantiation
• Oracle Data Pump and Streams Instantiation
• Original Export/Import and Streams Instantiation

  Attention: During an export for a Streams instantiation, make sure no DDL changes are made to objects being exported. When you export a database or schema that contains rules with non-NULL action contexts, the database or the default tablespace of the schema that owns the rules must be writeable. If the database or tablespace is read-only, then export errors result.

  See Also: "Instantiating Objects in a Streams Replication Environment" "The Oldest SCN for an Apply Process" "Managing Supplemental Logging in a Streams Replication Environment" for information about adding and dropping supplemental log groups

Capture Process Rules and Preparation for Instantiation

The following procedures in the DBMS_CAPTURE_ADM package prepare database objects for instantiation:

• PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.
• PREPARE_SCHEMA_INSTANTIATION prepares for instantiation all of the database objects in a schema and all database objects added to the schema in the future.
• PREPARE_GLOBAL_INSTANTIATION prepares for instantiation all of the database objects in a database and all database objects added to the database in the future.

These procedures record the lowest SCN of each object for instantiation. SCNs subsequent to the lowest SCN for an object can be used for instantiating the object. These procedures also populate the Streams data dictionary for the relevant capture processes, propagations, and apply processes that capture, propagate, or apply changes made to the table, schema, or database being prepared for instantiation.

DBMS_STREAMS_ADM Package Procedures Automatically Prepare Objects

When you add rules to the positive rule set for a capture process by running a procedure in the DBMS_STREAMS_ADM package, a procedure in the DBMS_CAPTURE_ADM package is run automatically on the database objects whose changes will be captured by the capture process. The following table lists which procedure is run in the DBMS_CAPTURE_ADM package when you run a procedure in the DBMS_STREAMS_ADM package.

When you run this procedure in the DBMS_STREAMS_ADM package	This procedure in the DBMS_CAPTURE_ADM package is run automatically
ADD_TABLE_RULES ADD_SUBSET_RULES	PREPARE_TABLE_INSTANTIATION
ADD_SCHEMA_RULES	PREPARE_SCHEMA_INSTANTIATION
ADD_GLOBAL_RULES	PREPARE_GLOBAL_INSTANTIATION

More than one call to prepare for instantiation is allowed. When capture process rules are created by the DBMS_RULE_ADM package instead of the DBMS_STREAMS_ADM package, you must run the appropriate procedure manually to prepare each table, schema, or database whose changes will be captured for instantiation, if you plan to apply changes that result from these capture process rules with an apply process.

If you are using downstream capture, and the downstream capture process uses a database link from the downstream database to the source database, then the objects are prepared for instantiation automatically. However, if the downstream capture process does not use a database link from the downstream database to the source database, then you must prepare the objects for instantiation manually.

When Preparing for Instantiation Is Required

Whenever you add, or modify the condition of, a capture process, propagation, or apply process rule for an object that is in a positive rule set, you must run the appropriate procedure to prepare the object for instantiation at the source database if any of the following conditions are met:

• One or more rules are added to the positive rule set for a capture process that instruct the capture process to capture changes made to the object.
• One or more conditions of rules in the positive rule set for a capture process are modified to instruct the capture process to capture changes made to the object.
• One or more rules are added to the positive rule set for a propagation that instruct the propagation to propagate changes made to the object.
• One or more conditions of rules in the positive rule set for a propagation are modified to instruct the propagation to propagate changes made to the object.
• One or more rules are added to the positive rule set for an apply process that instruct the apply process to apply changes made to the object at the source database.
• One or more conditions of rules in the positive rule set for an apply process are modified to instruct the apply process to apply changes made to the object at the source database.

Whenever you remove, or modify the condition of, a capture process, propagation, or apply process rule for an object that is in a negative rule set, you must run the appropriate procedure to prepare the object for instantiation at the source database if any of the following conditions are met:

• One or more rules are removed from the negative rule set for a capture process to instruct the capture process to capture changes made to the object.
• One or more conditions of rules in the negative rule set for a capture process are modified to instruct the capture process to capture changes made to the object.
• One or more rules are removed from the negative rule set for a propagation to instruct the propagation to propagate changes made to the object.
• One or more conditions of rules in the negative rule set for a propagation are modified to instruct the propagation to propagate changes made to the object.
• One or more rules are removed from the negative rule set for an apply process to instruct the apply process to apply changes made to the object at the source database.
• One or more conditions of rules in the negative rule set for an apply process are modified to instruct the apply process to apply changes made to the object at the source database.

When any of these conditions are met for changes to a positive or negative rule set, you must prepare the relevant database objects for instantiation at the source database to populate any relevant Streams data dictionary that requires information about the source object, even if the object already exists at a remote database where the rules were added or changed.

The relevant Streams data dictionaries are populated asynchronously for both the local dictionary and all remote dictionaries. The procedure that prepares for instantiation adds information to the redo log at the source database. The local Streams data dictionary is populated with the information about the object when a capture process captures these redo entries, and any remote Streams data dictionaries are populated when the information is propagated to them.

Oracle Data Pump and Streams Instantiation

The following sections contain information about Streams instantiations that use Oracle Data Pump.

Data Pump Export and Object Consistency

During export, Oracle Data Pump automatically uses the Oracle Flashback feature to ensure that the exported data and the exported procedural actions for each object are consistent to a single point in time. When you perform an instantiation in a Streams environment, some degree of consistency is required. Using the Data Pump Export utility 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 those provided by Data Pump's default export, then you can use the Data Pump Export utility parameters FLASHBACK_SCN or FLASHBACK_TIME for Streams instantiations. For example, if an export includes objects with foreign key constraints, then more stringent consistency may be required.

Oracle Data Pump Import and Streams Instantiation

The following sections provide more information about Oracle Data Pump import and Streams instantiation.

Instantiation SCNs and Data Pump Imports

During Data Pump import, an instantiation SCN is set at the import database for each database object that was prepared for instantiation at the export database before the Data Pump export was performed. The instantiation SCN settings are based on metadata obtained during Data Pump export.

Instantiation SCNs and Streams Tags Resulting From Data Pump Imports

A Data Pump import session may set its Streams tag to the hexadecimal equivalent of '00' to avoid cycling the changes made by the import. Redo entries resulting from such an import have this tag value.

Whether the import session tag is set to the hexadecimal equivalent of '00' depends on the export that is being imported. Specifically, the import session tag is set to the hexadecimal equivalent of '00' in either of the following cases:

• The Data Pump export was in FULL or SCHEMA mode.
• The Data Pump export was in TABLE or TABLESPACE mode and at least one table included in the export was prepared for instantiation at the export database before the export was performed.

If neither one of these conditions is true for a Data Pump export that is being imported, then the import session tag is NULL.

The STREAMS_CONFIGURATION Data Pump Import Utility Parameter

The STREAMS_CONFIGURATION Data Pump 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 if the information is present in the export dump file:

• SYS.AnyData queues and their queue tables
• Queue subscribers
• Advanced Queuing agents
• Job queue jobs related to Streams propagations
• Rules, including their positive and negative 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_RULES, ALL_STREAMS_GLOBAL_RULES, ALL_STREAMS_SCHEMA_RULES, ALL_STREAMS_TABLE_RULES, DBA_STREAMS_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, if the information is present in the export dump file; when the STREAMS_CONFIGURATION Import utility parameter is set to n, the import does not include the following information:

• Capture processes that capture local changes, 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 each positive and negative rule set used by the capture process
  • Capture user for the capture process
  • Extra attribute settings if a capture process is configured to include extra attributes in LCRs
  • The time that the status of the capture process last changed. This information is recorded in the DBA_CAPTURE data dictionary view.
  • If the capture process disabled or aborted, then the error number and message of the error that was the cause. This information is recorded in the DBA_CAPTURE data dictionary view.
• 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 SYS.AnyData queue that is used by a Streams client, either started or stopped. Streams clients include capture processes, propagations, apply process, and messaging clients. SYS.AnyData queues themselves are imported regardless of the STREAMS_CONFIGURATION Import utility 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 each positive and negative 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 each positive and negative rule set used by the apply process
  • Whether the apply process applies captured or user-enqueued events
  • Apply user for the apply process
  • Message handler used by the apply process, if one exists
  • DDL handler used by the apply process, if one exists
  • Precommit 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 (oldest SCN), apply time, and applied message create time
  • Apply errors
  • The time that the status of the apply process last changed. This information is recorded in the DBA_APPLY data dictionary view.
  • If the apply process disabled or aborted, then the error number and message of the error that was the cause. This information is recorded in the DBA_APPLY data dictionary view.
• 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
• Messaging Clients, including the following information for each messaging client:
  • Name of the messaging client
  • Queue owner and queue name of the queue used by the messaging client
  • Rule set owner and rule set name of each positive and negative rule set used by the messaging client
  • Message notification settings
• Some data dictionary information about Streams rules. The rules themselves are imported regardless of the setting for the STREAMS_CONFIGURATION parameter.
• Data dictionary information about Streams administrators, messaging clients, message rules, and extra attributes used in message rules.

  Note: Downstream capture processes are not included in an import regardless of the STREAMS_CONFIGURATION setting.

Original Export/Import and Streams Instantiation

This section describes parameters for the original Export and Import utilities that are 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 for the database objects being instantiated. 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 those provided by OBJECT_CONSISTENT, then you can use Export utility parameters CONSISTENT, FLASHBACK_SCN, or FLASHBACK_TIME for Streams instantiations. For example, if an export includes objects with foreign key constraints, then more stringent consistency may be required.

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.

Original Import Utility Parameters Relevant to Streams

The following parameters for the original Import utility 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, and the export dump file contains the metadata for instantiation SCNs, an instantiation SCN is set at the import database for each database object imported.

In addition, 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 behaves the same for the original Import utility and the Data Pump Import utility.