Skip Headers
Oracle® Streams Replication Administrator's Guide
10g Release 2 (10.2)

Part Number B14228-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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

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 a database object to the positive rule set for a capture process by running a procedure in the DBMS_STREAMS_ADM package, the database object is prepared for instantiation automatically. Also, when you use export/import, transportable tablespaces, or the RMAN TRANSPORT TABLESPACE command to copy database objects from a source database to a destination database, instantiation SCNs can be set for these database objects automatically.


Note:

  • You can use either Data Pump export/import or original export/import for Streams instantiations. General references to export/import in this document refer to both Data Pump and original export/import. This document distinguishes between Data Pump and original export/import when necessary.

  • The RMAN DUPLICATE command can be used to instantiate an entire database, but this command does not set instantiation SCNs for database objects.


If the database object being instantiated is a table, then the tables 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 database object at the destination database. By preparing an object for instantiation, you are setting the lowest SCN for which changes to the object might 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 database object.

When you instantiate tables using export/import, transportable tablespaces, or RMAN, any 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, the RMAN DUPLICATE command retains database supplemental logging specifications at the instantiated database.


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.


Capture Process Rules and Preparation for Instantiation

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

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. In addition, these procedures optionally can enable supplemental logging for key columns or all columns in the tables that are 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. 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 when you run one of these procedures in the DBMS_STREAMS_ADM package. 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 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 the capture process rules with an apply process.

When Preparing for Instantiation Is Required

Whenever you add, or modify the condition of, a capture process, propagation, or apply process rule for a database object that is in a positive rule set, you must run the appropriate procedure to prepare the database 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 a database object that is in a negative rule set, you must run the appropriate procedure to prepare the database 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.


See Also:


Supplemental Logging Options During Preparation for Instantiation

Supplemental logging places additional column data into a redo log whenever an operation is performed. The procedures in the DBMS_CAPTURE_ADM package that prepare database objects for instantiation are PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_GLOBAL_INSTANTIATION. These procedures include a supplemental_logging parameter which controls the supplemental logging specifications for the database objects being prepared for instantiation.

Table 2-1 describes the values for the supplemental_logging parameter for each procedure.

Table 2-1 Supplemental Logging Options During Preparation for Instantiation

Procedure supplemental_logging Parameter Setting Description
PREPARE_TABLE_INSTANTIATION keys The procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the table being prepared for instantiation. The procedure places the logged columns for the table in three separate log groups: the primary key columns in an unconditional log group, the unique key columns and bitmap index columns in a conditional log group, and the foreign key columns in a conditional log group.
PREPARE_TABLE_INSTANTIATION all The procedure enables supplemental logging for all columns in the table being prepared for instantiation. The procedure places all of the columns for the table in an unconditional log group.
PREPARE_SCHEMA_INSTANTIATION keys The procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the schema being prepared for instantiation and for any table added to this schema in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally.
PREPARE_SCHEMA_INSTANTIATION all The procedure enables supplemental logging for all columns in the tables in the schema being prepared for instantiation and for any table added to this schema in the future. The columns are logged unconditionally.
PREPARE_GLOBAL_INSTANTIATION keys The procedure enables database supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the database being prepared for instantiation and for any table added to the database in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally.
PREPARE_GLOBAL_INSTANTIATION all The procedure enables supplemental logging for all columns in all of the tables in the database being prepared for instantiation and for any table added to the database in the future. The columns are logged unconditionally.
Any Prepare Procedure none The procedure does not enable supplemental logging for any columns in the tables being prepared for instantiation.

If the supplemental_logging parameter is not specified when one of prepare procedures is run, then keys is the default. Some of the procedures in the DBMS_STREAMS_ADM package prepare tables for instantiation when they add rules to a positive capture process rule set. In this case, the default supplemental logging option, keys, is specified for the tables being prepared for instantiation.


Note:

  • When all is specified for the supplemental_logging parameter, supplemental logging is not enabled for columns of the following types: LOB, LONG, LONG RAW, and user-defined type.

  • Specifying keys for the supplemental_logging parameter does not enable supplemental logging of bitmap join index columns.

  • Oracle Database 10g Release 2 introduces the supplemental_logging parameter for the prepare procedures. By default, running these procedures enables supplemental logging. Prior to this release, these procedures did not enable supplemental logging. If you remove a Streams environment, or if you remove certain database objects from a Streams environment, then you can also remove the supplemental logging enabled by these procedures to avoid unnecessary logging.


Oracle Data Pump and Streams Instantiation

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


See Also:


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 database 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 might 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 can 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.


Note:

  • If you perform a network import using Data Pump, then an implicit export is performed in the same mode as the import. For example, if the network import is in schema mode, then the implicit export is in schema mode also.

  • The import session tag is not set if the Data Pump import is performed in TRANSPORTABLE TABLESPACE mode. An import performed in this mode does not generate any redo data for the imported data. Therefore, setting the session tag is not required.


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

  • 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 ANYDATA queue that is used by a Streams client, either started or stopped. Streams clients include capture processes, propagations, apply process, and messaging clients. 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 messages.

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

Recovery Manager (RMAN) and Streams Instantiation

The RMAN DUPLICATE and CONVERT DATABASE commands can instantiate an entire database, and the RMAN TRANSPORT TABLESPACE command can instantiate a tablespace or set of tablespaces. Using RMAN for instantiation usually is faster than other instantiation methods. The following sections contain information about using these RMAN commands for instantiation.

The RMAN DUPLICATE and CONVERT DATABASE Commands and Instantiation

The RMAN DUPLICATE command creates a copy of the target database in another location. The command uses an RMAN auxiliary instance to restore backups of the target database files and create a new database. In a Streams instantiation, the target database is the source database and the new database that is created is the destination database. The DUPLICATE command requires that the source and destination database are running on the same platform.

The RMAN CONVERT DATABASE command generates the datafiles and an initialization parameter file for a new destination database on a different platform. It also generates a script that creates the new destination database. These files can be used to instantiate an entire destination database that runs on a different platform than the source database but has the same endian format as the source database.

The RMAN DUPLICATE and CONVERT DATABASE commands do not set the instantiation SCN values for the database objects. The instantiation SCN values must be set manually during instantiation.

The RMAN TRANSPORT TABLESPACE Command and Instantiation

The RMAN TRANSPORT TABLESPACE command uses Data Pump and an RMAN-managed auxiliary instance to export the database objects in a tablespace or tablespace set while the tablespace or tablespace set remains online in the source database. RMAN automatically starts up an auxiliary instance with a system-generated name. The RMAN TRANSPORT TABLESPACE command produces a Data Pump export dump file and datafiles for the tablespace or tablespaces.

Data Pump can be used to import the dump file at the destination database, or the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package can be used to attach the tablespace or tablespaces to the destination database. Also, instantiation SCN values for the database objects in the tablespace or tablespaces are set automatically at the destination database when the tablespaces are imported or attached.


Note:

The RMAN TRANSPORT TABLESPACE command does not support user-managed auxiliary instances.

Original Export/Import and Streams Instantiation

This section describes parameters for the original Export and Import utilities that are relevant to Streams.


See Also:


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


See Also:

"The STREAMS_CONFIGURATION Data Pump Import Utility Parameter" for more information about this parameter