This chapter contains conceptual information about instantiation and Streams replication.
This chapter contains these topics:
See Also:
Chapter 10, "Performing Instantiations"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:
Prepare the object for instantiation at the source database.
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.
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.
See Also:
"Instantiating Objects in a Streams Replication Environment"
"Managing Supplemental Logging in a Streams Replication Environment" for information about adding and dropping supplemental log groups
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. In addition, these procedures optionally can enable supplemental logging for key columns or all columns in the tables that are being prepared for instantiation.
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
|
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.
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:
"Capture Process Overview" for more information about local and downstream capture
"Preparing Database Objects for Instantiation at a Source Database"
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 |
---|---|---|
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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 |
|
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.
See Also:
"Preparing Database Objects for Instantiation at a Source Database"
"DBMS_STREAMS_ADM Package Procedures Automatically Prepare Objects"
"Aborting Preparation for Instantiation at a Source Database" for information about removing supplemental logging enabled by the prepare procedures
The following sections contain information about Streams instantiations that use Oracle Data Pump.
See Also:
Oracle Streams Concepts and Administration for information about performing a full database export/import on a database using Streams
Oracle Database Utilities for more information about Data Pump
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.
The following sections provide more information about Oracle Data Pump import and Streams instantiation.
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.
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.
See Also:
Chapter 4, "Streams Tags"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 theSTREAMS_CONFIGURATION
setting.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
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.
See Also:
Oracle Database Backup and Recovery Advanced User's Guide for instructions on using the RMAN DUPLICATE
command and the RMAN CONVERT
DATABASE
command
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 RMANTRANSPORT
TABLESPACE
command does not support user-managed auxiliary instances.This section describes parameters for the original Export and Import utilities that are relevant to Streams.
See Also:
Oracle Streams Concepts and Administration for information about performing a full database export/import on a database using Streams
Oracle Database Utilities for information about performing exports and imports using the original Export and Import utilities
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.
The following parameters for the original Import utility are relevant to 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 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