8 Instantiation and Oracle Streams Replication

This chapter contains conceptual information about instantiation and Oracle Streams replication. It also contains instructions for preparing database objects for instantiation, performing instantiations, setting instantiation system change numbers (SCNs), and monitoring instantiations.

This chapter contains these topics:

8.1 Overview of Instantiation and Oracle Streams Replication

In an Oracle Streams environment that replicates a database object within a single database or between multiple databases, a source database is the database where changes to the object are generated, and a destination database is the database where these changes are dequeued by an apply process. If a capture process or synchronous capture 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 Oracle Streams, the following general steps instantiate a database object:

  1. Prepare the database object for instantiation at the source database.

  2. If a copy of the database object does not exist at the destination database, then create a database object physically at the destination database based on a database 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 system change number (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.

All of these instantiation steps can be performed automatically when you use one of the following Oracle-supplied procedures in the DBMS_STREAMS_ADM package that configure replication environments:

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

The RMAN DUPLICATE command can 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 database 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 can be applied at destination databases. This SCN is called the ignore SCN. You should prepare a database object for instantiation after a capture process or synchronous capture 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.

Note:

  • During an export for an Oracle Streams instantiation, ensure that no data definition language (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:

8.2 Capture Rules and Preparation for Instantiation

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

  • The PREPARE_TABLE_INSTANTIATION procedure prepares a single table for instantiation when changes to the table will be captured by a capture process.

  • The PREPARE_SYNC_INSTANTIATION function prepares a single table or multiple tables for instantiation when changes to the table or tables will be captured by a synchronous capture.

  • The PREPARE_SCHEMA_INSTANTIATION procedure prepares for instantiation all of the database objects in a schema and all database objects added to the schema in the future. This procedure should only be used when changes will be captured by a capture process.

  • The PREPARE_GLOBAL_INSTANTIATION procedure prepares for instantiation all of the database objects in a database and all database objects added to the database in the future. This procedure should only be used when changes will be captured by a capture process.

These procedures record the lowest system change number (SCN) of each object for instantiation. SCNs after the lowest SCN for an object can be used for instantiating the object.

If you use a capture process to capture changes, then these procedures also populate the Oracle 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, if you use a capture process to capture changes, then these procedures optionally can enable supplemental logging for key columns or all columns in the tables that are being prepared for instantiation.

Note:

Replication with synchronous capture does not use the Oracle Streams data dictionary and does not require supplemental logging.

8.2.1 DBMS_STREAMS_ADM Package Procedures Automatically Prepare Objects

When you add rules to the positive rule set for a capture process or synchronous capture by running a procedure in the DBMS_STREAMS_ADM package, a procedure or function in the DBMS_CAPTURE_ADM package is run automatically on the database objects where changes will be captured. Table 8-1 lists which procedure or function is run in the DBMS_CAPTURE_ADM package when you run a procedure in the DBMS_STREAMS_ADM package.

Table 8-1 DBMS_CAPTURE_ADM Package Procedures That Are Run Automatically

When you run this procedure in the DBMS_STREAMS_ADM package This procedure or function in the DBMS_CAPTURE_ADM package is run automatically

ADD_TABLE_RULES

ADD_SUBSET_RULES

PREPARE_TABLE_INSTANTIATION when rules are added to a capture process rule set

PREPARE_SYNC_INSTANTIATION when rules are added to a synchronous capture rule set

ADD_SCHEMA_RULES

PREPARE_SCHEMA_INSTANTIATION

ADD_GLOBAL_RULES

PREPARE_GLOBAL_INSTANTIATION

Multiple calls to prepare for instantiation are 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 database 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 database 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.

In addition, some procedures automatically run these procedures. For example, the DBMS_STREAMS_ADM.MAINTAIN_TABLES procedure automatically runs the ADD_TABLE_RULES procedure.

Note:

A synchronous capture only captures changes based on rules created by the ADD_TABLE_RULES or ADD_SUBSET_RULES procedures.

8.2.2 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 that were 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 that were 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 that were 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 that were 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 Oracle 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 Oracle 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 Oracle Streams data dictionary is populated with the information about the object when a capture process captures these redo entries, and any remote Oracle Streams data dictionaries are populated when the information is propagated to them.

Synchronous captures do not use Oracle Streams data dictionaries. However, when you are capturing changes to a database object with synchronous capture, you must prepare the database object for instantiation when you add rules for the database object to the synchronous capture rule set. Preparing the database object for instantiation is required when rules are added because it records the lowest SCN for instantiation for the database object. Preparing the database object for instantiation is not required when synchronous capture rules are modified, but modifications cannot change the database object name or schema in the rule condition.

See Also:

8.2.3 Supplemental Logging Options During Preparation for Instantiation

If a replication environment uses a capture process to capture changes, then supplemental logging is required. 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 include PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_GLOBAL_INSTANTIATION. These procedures have a supplemental_logging parameter which controls the supplemental logging specifications for the database objects being prepared for instantiation.

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

Table 8-2 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 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, user-defined type, and Oracle-supplied type.

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

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

8.2.4 Preparing Database Objects for Instantiation at a Source Database

If you use the DBMS_STREAMS_ADM package to create rules for a capture process or a synchronous capture, then any objects referenced in the system-created rules are prepared for instantiation automatically. If you use the DBMS_RULE_ADM package to create rules for a capture process, then you must prepare the database objects referenced in these rules for instantiation manually. In this case, you should prepare a database object for instantiation after a capture process has been configured to capture changes to the database object. Synchronous captures ignore rules created by the DBMS_RULE_ADM package.

See "Capture Rules and Preparation for Instantiation" for information about the PL/SQL subprograms that prepare database objects for instantiation. If you run one of these procedures while a long running transaction is modifying one or more database objects being prepared for instantiation, then the procedure waits until the long running transaction is complete before it records the ignore SCN for the objects. The ignore SCN is the SCN below which changes to an object cannot be applied at destination databases. Query the V$STREAMS_TRANSACTION dynamic performance view to monitor long running transactions being processed by a capture process or apply process.

The following sections contain examples that prepare database objects for instantiation:

See Also:

Oracle Streams Concepts and Administration for more information about the instantiation SCN and ignore SCN for an apply process

8.2.4.1 Preparing Tables for Instantiation
8.2.4.1.1 Preparing a Table for Instantiation Using DBMS_STREAMS_ADM When a Capture Process Is Used

The example in this section prepares a table for instantiation using the DBMS_STREAMS_ADM package when a capture process captures changes to the table. To prepare the hr.regions table for instantiation and enable supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the table, add rules for the hr.regions table to the positive rule set for a capture process using a procedure in the DBMS_STREAMS_ADM package. If the capture process is a local capture process or a downstream capture process with a database link to the source database, then the procedure that you run prepares this table for instantiation automatically.

The following procedure adds rules to the positive rule set of a capture process named strm01_capture and prepares the hr.regions table for instantiation:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'hr.regions',   
    streams_type   => 'capture',
    streams_name   => 'strm01_capture',
    queue_name     => 'strmadmin.strm01_queue',
    include_dml    => TRUE,
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/
8.2.4.1.2 Preparing a Table for Instantiation Using DBMS_CAPTURE_ADM When a Capture Process Is Used

The example in this section prepares a table for instantiation using the DBMS_CAPTURE_ADM package when a capture process captures changes to the table. To prepare the hr.regions table for instantiation and enable supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the table, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name           => 'hr.regions',
    supplemental_logging => 'keys');
END;
/

The default value for the supplemental_logging parameter is keys. Therefore, if this parameter is not specified, then supplemental logging is enabled for any primary key, unique key, bitmap index, and foreign key columns in the table that is being prepared for instantiation.

8.2.4.1.3 Preparing Tables for Instantiation Using DBMS_STREAMS_ADM When a Synchronous Capture Is Used

The example in this section prepares all of the tables in the hr schema for instantiation using the DBMS_STREAMS_ADM package when a synchronous capture captures changes to the tables.Add rules for the hr.jobs_transport and hr.regions_transport tables to the positive rule set for a synchronous capture using a procedure in the DBMS_STREAMS_ADM package. The procedure that you run prepares the tables for instantiation automatically.

The following procedure adds a rule to the positive rule set of a synchronous capture named sync_capture and prepares the hr.regions table for instantiation:

BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name   => 'hr.regions',
    streams_type => 'sync_capture',
    streams_name => 'sync_capture',
    queue_name   => 'strmadmin.streams_queue');
END;
/
8.2.4.1.4 Preparing Tables for Instantiation Using DBMS_CAPTURE_ADM When a Synchronous Capture Is Used

The example in this section prepares all of the tables in the hr schema for instantiation using the DBMS_CAPTURE_ADM package when a synchronous capture captures changes to the tables. To prepare the tables in the hr schema for instantiation, run the following function:

SET SERVEROUTPUT ON
DECLARE
  tables       DBMS_UTILITY.UNCL_ARRAY;
  prepare_scn  NUMBER;
  BEGIN
    tables(1) := 'hr.departments';
    tables(2) := 'hr.employees';
    tables(3) := 'hr.countries';
    tables(4) := 'hr.regions';
    tables(5) := 'hr.locations';
    tables(6) := 'hr.jobs';
    tables(7) := 'hr.job_history';
    prepare_scn := DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION(
                      table_names => tables);
  DBMS_OUTPUT.PUT_LINE('Prepare SCN = ' || prepare_scn);
END;
/
8.2.4.2 Preparing the Database Objects in a Schema for Instantiation
8.2.4.2.1 Preparing the Database Objects in a Schema for Instantiation Using DBMS_STREAMS_ADM

The example in this section prepares the database objects in a schema for instantiation using the DBMS_STREAMS_ADM package when a capture process captures changes to these objects.

To prepare the database objects in the hr schema for instantiation and enable supplemental logging for the all columns in the tables in the hr schema, run the following procedure, add rules for the hr schema to the positive rule set for a capture process using a procedure in the DBMS_STREAMS_ADM package. If the capture process is a local capture process or a downstream capture process with a database link to the source database, then the procedure that you run prepares the objects in the hr schema for instantiation automatically.

The following procedure adds rules to the positive rule set of a capture process named strm01_capture and prepares the hr schema, and all of its database objects, for instantiation:

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     =>  'hr',
    streams_type    =>  'capture',
    streams_name    =>  'strm01_capture',
    queue_name      =>  'strm01_queue',
    include_dml     =>  TRUE,
    include_ddl     =>  TRUE,
    inclusion_rule  =>  TRUE);
END;
/

If the specified capture process does not exist, then this procedure creates it.

In addition, supplemental logging is enabled for any primary key, unique key, bitmap index, and foreign key columns in the tables that are being prepared for instantiation.

8.2.4.2.1.1 Preparing the Database Objects in a Schema for Instantiation Using DBMS_CAPTURE_ADM

The example in this section prepares the database objects in a schema for instantiation using the DBMS_CAPTURE_ADM package when a capture process captures changes to these objects. To prepare the database objects in the hr schema for instantiation and enable supplemental logging for the all columns in the tables in the hr schema, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
    schema_name          => 'hr',
    supplemental_logging => 'all');
END;
/

After running this procedure, supplemental logging is enabled for all of the columns in the tables in the hr schema and for all of the columns in the tables added to the hr schema in the future.

8.2.4.3 Preparing All of the Database Objects in a Database for Instantiation
8.2.4.3.1 Preparing All of the Database Objects in a Database for Instantiation Using DBMS_STREAMS_ADM

The example in this section prepares the database objects in a database for instantiation using the DBMS_STREAMS_ADM package when a capture process captures changes to these objects. To prepare all of the database objects in a database for instantiation, run the ADD_GLOBAL_RULES procedure in the DBMS_STREAMS_ADM package:

BEGIN
  DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
    streams_type   => 'capture',
    streams_name   => 'capture_db',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => TRUE,
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/

If the specified capture process does not exist, then this procedure creates it.

In addition, supplemental logging is enabled for any primary key, unique key, bitmap index, and foreign key columns in the tables that are being prepared for instantiation.

8.2.4.3.1.1 Preparing All of the Database Objects in a Database for Instantiation Using DBMS_CAPTURE_ADM

The example in this section prepares the database objects in a database for instantiation using the DBMS_CAPTURE_ADM package when a capture process captures changes to these objects. To prepare all of the database objects in a database for instantiation, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION(
    supplemental_logging => 'none');
END;
/

Because none is specified for the supplemental_logging parameter, this procedure does not enable supplemental logging for any columns. However, you can specify supplemental logging manually using an ALTER TABLE or ALTER DATABASE statement.

8.2.5 Aborting Preparation for Instantiation at a Source Database

The following procedures in the DBMS_CAPTURE_ADM package abort preparation for instantiation:

  • ABORT_TABLE_INSTANTIATION reverses the effects of PREPARE_TABLE_INSTANTIATION and removes any supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure.

  • ABORT_SYNC_INSTANTIATION reverses the effects of PREPARE_SYNC_INSTANTIATION

  • ABORT_SCHEMA_INSTANTIATION reverses the effects of PREPARE_SCHEMA_INSTANTIATION and removes any supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION and PREPARE_TABLE_INSTANTIATION procedures.

  • ABORT_GLOBAL_INSTANTIATION reverses the effects of PREPARE_GLOBAL_INSTANTIATION and removes any supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_TABLE_INSTANTIATION procedures.

These procedures remove data dictionary information related to the potential instantiation of the relevant database objects.

For example, to abort the preparation for instantiation of the hr.regions table, run the following procedure:

BEGIN
  DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(
    table_name  => 'hr.regions');
END;
/

8.3 Oracle Data Pump and Oracle Streams Instantiation

The following sections contain information about performing Oracle Streams instantiations using Oracle Data Pump:

See Also:

8.3.1 Data Pump Export and Object Consistency

During export, Oracle Data Pump automatically uses Oracle Flashback 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 an Oracle Streams environment, some degree of consistency is required. Using the Data Pump Export utility is sufficient to ensure this consistency for Oracle Streams instantiations.

If you are using an export dump file for other purposes in addition to an Oracle 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 Oracle Streams instantiations. For example, if an export includes objects with foreign key constraints, then more stringent consistency might be required.

8.3.2 Oracle Data Pump Import and Oracle Streams Instantiation

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

8.3.2.1 Instantiation SCNs and Data Pump Imports

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

8.3.2.2 Instantiation SCNs and Oracle Streams Tags Resulting from Data Pump Imports

A Data Pump import session can set its Oracle 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.

8.3.2.3 The STREAMS_CONFIGURATION Data Pump Import Utility Parameter

The STREAMS_CONFIGURATION Data Pump Import utility parameter specifies whether to import any general Oracle 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

  • Rules, including their positive and negative rule sets and evaluation contexts. All rules are imported, including Oracle Streams rules and non-Oracle Streams rules. Oracle Streams rules are rules generated by the system when certain procedures in the DBMS_STREAMS_ADM package are run, while non-Oracle Streams rules are rules created using the DBMS_RULE_ADM package.

    If the STREAMS_CONFIGURATION parameter is set to n, then information about Oracle 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

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

  • Synchronous captures, including the following information for each synchronous capture:

    • Name of the synchronous capture

    • Queue owner and queue name of the queue used by the synchronous capture

    • Rule set owner and rule set name of each rule set used by the synchronous capture

    • Capture user for the synchronous capture

  • 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 an Oracle Streams client, either started or stopped. Oracle Streams clients include capture processes, synchronous captures, 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

    • Oracle Scheduler jobs related to Oracle Streams propagations

  • 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 LCRs in a buffered queue or messages in a persistent queue

    • 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 value 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 (including both statement DML handlers and procedure 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 Oracle Streams rules. The rules themselves are imported regardless of the setting for the STREAMS_CONFIGURATION parameter.

  • Data dictionary information about Oracle Streams administrators, messaging clients, message rules, extra attributes included in logical change records (LCRs) captured by a capture process or synchronous capture, and extra attributes used in message rules

Note:

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

8.3.3 Instantiating Objects Using Data Pump Export/Import

The example in this section describes the steps required to instantiate objects in an Oracle Streams environment using Oracle Data Pump export/import. This example makes the following assumptions:

  • You want to capture changes to all of the database objects in the hr schema at a source database and apply these changes at a separate destination database.

  • The hr schema exists at the source database but does not exist at the destination database. For the purposes of this example, you can drop the hr user at the destination database using the following SQL statement:

    DROP USER hr CASCADE;
    

    The Data Pump import re-creates the user and the user's database objects at the destination database.

  • You have configured an Oracle Streams administrator at the source database and the destination database named strmadmin. At each database, the Oracle Streams administrator is granted DBA role.

Note:

The example in this section uses the command line Data Pump utility. You can also use the DBMS_DATAPUMP package for Oracle Streams instantiations.

See Also:

Given these assumptions, complete the following steps to instantiate the hr schema using Data Pump export/import:

  1. In SQL*Plus, connect to the source database as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  2. Create a directory object to hold the export dump file and export log file:
    CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';
    
  3. Prepare the database objects in the hr schema for instantiation. See "Preparing the Database Objects in a Schema for Instantiation" for instructions.
  4. While still connected to the source database as the Oracle Streams administrator, determine the current system change number (SCN) of the source database:
    SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
    

    The SCN value returned by this query is specified for the FLASHBACK_SCN Data Pump export parameter in Step 5. Because the hr schema includes foreign key constraints between tables, the FLASHBACK_SCN export parameter, or a similar export parameter, must be specified during export. In this example, assume that the query returned 876606.

    After you perform this query, ensure that no DDL changes are made to the objects being exported until after the export is complete.

  5. On a command line, use Data Pump to export the hr schema at the source database.

    Perform the export by connecting as an administrative user who is granted EXP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 2. This example connects as the Oracle Streams administrator strmadmin.

    The following is a sample Data Pump export command:

    expdp strmadmin SCHEMAS=hr DIRECTORY=DPUMP_DIR DUMPFILE=hr_schema_dp.dmp 
    FLASHBACK_SCN=876606
    

    See Also:

    Oracle Database Utilities for information about performing a Data Pump export

  6. In SQL*Plus, connect to the destination database as the Oracle Streams administrator.
  7. Create a directory object to hold the import dump file and import log file:
    CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';
    
  8. Transfer the Data Pump export dump file hr_schema_dp.dmp to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer the file to the destination database. After the file transfer, the export dump file should reside in the directory that corresponds to the directory object created in Step 7.
  9. On a command line at the destination database, use Data Pump to import the export dump file hr_schema_dp.dmp. Ensure that no changes are made to the tables in the schema being imported at the destination database until the import is complete. Performing the import automatically sets the instantiation SCN for the hr schema and all of its database objects at the destination database.

    Perform the import by connecting as an administrative user who is granted IMP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 7. This example connects as the Oracle Streams administrator strmadmin.

    The following is a sample import command:

    impdp strmadmin SCHEMAS=hr DIRECTORY=DPUMP_DIR DUMPFILE=hr_schema_dp.dmp
    

Note:

Any table supplemental log groups for the tables exported from the export database are retained when the tables are imported at the import database. You can drop these supplemental log groups if necessary.

See Also:

Oracle Database Utilities for information about performing a Data Pump import

8.4 Recovery Manager (RMAN) and Oracle Streams Instantiation

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

The following sections contain information about using these RMAN commands for instantiation:

8.4.1 Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN

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 an auxiliary instance with a system-generated name. The RMAN TRANSPORT TABLESPACE command produces a Data Pump export dump file and data files for the tablespace or tablespaces.

You can use Data Pump to import the dump file at the destination database, or you can use the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package 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.

The examples in this section describe the steps required to instantiate the database objects in a tablespace using transportable tablespace or RMAN. These instantiation options usually are faster than export/import. The following examples instantiate the database objects in a tablespace:

  • "Instantiating Objects Using Transportable Tablespace" uses the transportable tablespace feature to complete the instantiation. Data Pump exports the tablespace at the source database and imports the tablespace at the destination database. The tablespace is read-only during the export.

  • "Instantiating Objects Using Transportable Tablespace From Backup With RMAN" uses the RMAN TRANSPORT TABLESPACE command to generate a Data Pump export dump file and data files for a tablespace or set of tablespaces at the source database while the tablespace or tablespaces remain online. Either Data Pump import or the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package can add the tablespace or tablespaces to the destination database.

These examples instantiate a tablespace set that includes a tablespace called jobs_tbs, and a tablespace called regions_tbs. To run the examples, connect to the source database in SQL*Plus as an administrative user and create the new tablespaces:

CREATE TABLESPACE jobs_tbs DATAFILE '/usr/oracle/dbs/jobs_tbs.dbf' SIZE 5 M;

CREATE TABLESPACE regions_tbs DATAFILE '/usr/oracle/dbs/regions_tbs.dbf' SIZE 5 M;

Place the new table hr.jobs_transport in the jobs_tbs tablespace:

CREATE TABLE hr.jobs_transport TABLESPACE jobs_tbs AS 
  SELECT * FROM hr.jobs;

Place the new table hr.regions_transport in the regions_tbs tablespace:

CREATE TABLE hr.regions_transport TABLESPACE regions_tbs AS 
  SELECT * FROM hr.regions;

Both of the examples make the following assumptions:

  • You want to capture all of the changes to the hr.jobs_transport and hr.regions_transport tables at a source database and apply these changes at a separate destination database.

  • The hr.jobs_transport table exists at a source database, and a single self-contained tablespace named jobs_tbs contains the table. The jobs_tbs tablespace is stored in a single data file named jobs_tbs.dbf.

  • The hr.regions_transport table exists at a source database, and a single self-contained tablespace named regions_tbs contains the table. The regions_tbs tablespace is stored in a single data file named regions_tbs.dbf.

  • The jobs_tbs and regions_tbs tablespaces do not contain data from any other schemas.

  • The hr.jobs_transport table, the hr.regions_transport table, the jobs_tbs tablespace, and the regions_tbs tablespace do not exist at the destination database.

  • You have configured an Oracle Streams administrator at both the source database and the destination database named strmadmin, and you have granted this Oracle Streams administrator DBA role at both databases.

8.4.1.1 Instantiating Objects Using Transportable Tablespace

This example uses transportable tablespace to instantiate the database objects in a tablespace set. In addition to the assumptions listed in "Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN", this example makes the following assumptions:

  • The Oracle Streams administrator at the source database is granted the EXP_FULL_DATABASE role to perform the transportable tablespaces export. The DBA role is sufficient because it includes the EXP_FULL_DATABASE role. In this example, the Oracle Streams administrator performs the transportable tablespaces export.

  • The Oracle Streams administrator at the destination database is granted the IMP_FULL_DATABASE role to perform the transportable tablespaces import. The DBA role is sufficient because it includes the IMP_FULL_DATABASE role. In this example, the Oracle Streams administrator performs the transportable tablespaces export.

See Also:

Oracle Database Administrator's Guide for more information about using transportable tablespaces and for information about limitations that might apply

Complete the following steps to instantiate the database objects in the jobs_tbs and regions_tbs tablespaces using transportable tablespace:

  1. In SQL*Plus, connect to the source database as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  2. Create a directory object to hold the export dump file and export log file:
    CREATE DIRECTORY TRANS_DIR AS '/usr/trans_dir';
    
  3. Prepare the hr.jobs_transport and hr.regions_transport tables for instantiation. See "Preparing Tables for Instantiation" for instructions.
  4. Make the tablespaces that contain the objects you are instantiating read-only. In this example, the jobs_tbs and regions_tbs tablespaces contain the database objects.
    ALTER TABLESPACE jobs_tbs READ ONLY;
    
    ALTER TABLESPACE regions_tbs READ ONLY;
    
  5. On a command line, use the Data Pump Export utility to export the jobs_tbs and regions_tbs tablespaces at the source database using transportable tablespaces export parameters. The following is a sample export command that uses transportable tablespaces export parameters:
    expdp strmadmin TRANSPORT_TABLESPACES=jobs_tbs, regions_tbs 
    DIRECTORY=TRANS_DIR DUMPFILE=tbs_ts.dmp
    

    When you run the export command, ensure that you connect as an administrative user who was granted EXP_FULL_DATABASE role and has READ and WRITE privileges on the directory object.

    See Also:

    Oracle Database Utilities for information about performing an export

  6. In SQL*Plus, connect to the destination database as the Oracle Streams administrator.
  7. Create a directory object to hold the import dump file and import log file:
    CREATE DIRECTORY TRANS_DIR AS '/usr/trans_dir';
    
  8. Transfer the data files for the tablespaces and the export dump file tbs_ts.dmp to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer these files to the destination database. After the file transfer, the export dump file should reside in the directory that corresponds to the directory object created in Step 7.
  9. On a command line at the destination database, use the Data Pump Import utility to import the export dump file tbs_ts.dmp using transportable tablespaces import parameters. Performing the import automatically sets the instantiation SCN for the hr.jobs_transport and hr.regions_transport tables at the destination database.

    The following is an example import command:

    impdp strmadmin DIRECTORY=TRANS_DIR DUMPFILE=tbs_ts.dmp 
    TRANSPORT_DATAFILES=/usr/orc/dbs/jobs_tbs.dbf,/usr/orc/dbs/regions_tbs.dbf
    

    When you run the import command, ensure that you connect as an administrative user who was granted IMP_FULL_DATABASE role and has READ and WRITE privileges on the directory object.

    See Also:

    Oracle Database Utilities for information about performing an import

  10. If necessary, at both the source database and the destination database, connect as the Oracle Streams administrator and put the tablespaces into read/write mode:
    ALTER TABLESPACE jobs_tbs READ WRITE;
    
    ALTER TABLESPACE regions_tbs READ WRITE;
    

Note:

Any table supplemental log groups for the tables exported from the export database are retained when tables are imported at the import database. You can drop these supplemental log groups if necessary.

8.4.1.2 Instantiating Objects Using Transportable Tablespace From Backup With RMAN

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. The RMAN TRANSPORT TABLESPACE command produces a Data Pump export dump file and data files, and you can use these files to perform a Data Pump import of the tablespace or tablespaces at the destination database. You can also use the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package to attach the tablespace or tablespaces at the destination database.

In addition to the assumptions listed in "Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN", this example makes the following assumptions:

  • The source database is tts1.example.com.

  • The destination database is tts2.example.com.

See Also:

Oracle Database Backup and Recovery User's Guide for instructions on using the RMAN TRANSPORT TABLESPACE command

Complete the following steps to instantiate the database objects in the jobs_tbs and regions_tbs tablespaces using transportable tablespaces and RMAN:

  1. Create a backup of the source database that includes the tablespaces being instantiated, if a backup does not exist. RMAN requires a valid backup for tablespace cloning. In this example, create a backup of the source database that includes the jobs_tbs and regions_tbs tablespaces if one does not exist.
  2. In SQL*Plus, connect to the source database tts1.example.com as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  3. Optionally, create a directory object to hold the export dump file and export log file:
    CREATE DIRECTORY SOURCE_DIR AS '/usr/db_files';
    

    This step is optional because the RMAN TRANSPORT TABLESPACE command creates a directory object named STREAMS_DIROBJ_DPDIR on the auxiliary instance if the DATAPUMP DIRECTORY parameter is omitted when you run this command in Step 9.

  4. Prepare the hr.jobs_transport and hr.regions_transport tables for instantiation. See "Preparing Tables for Instantiation" for instructions.
  5. Determine the until SCN for the RMAN TRANSPORT TABLESPACE command:
    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
      until_scn NUMBER;
    BEGIN
      until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
          DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
    END;
    /
    

    Make a note of the until SCN returned. You will use this number in Step 9. For this example, assume that the returned until SCN is 7661956.

    Optionally, you can skip this step. In this case, do not specify the until clause in the RMAN TRANSPORT TABLESPACE command in Step 9. When no until clause is specified, RMAN uses the last archived redo log file to determine the until SCN automatically.

  6. In SQL*Plus, connect to the source database tts1.net as an administrative user.
  7. Archive the current online redo log:
    ALTER SYSTEM ARCHIVE LOG CURRENT;
    
  8. Start the RMAN client, and connect to the source database tts1.example.com as TARGET.

    See Oracle Database Backup and Recovery Reference for more information about the RMAN CONNECT command

  9. At the source database tts1.example.com, use the RMAN TRANSPORT TABLESPACE command to generate the dump file for the tablespace set:
    RMAN> RUN
          { 
            TRANSPORT TABLESPACE 'jobs_tbs', 'regions_tbs'
            UNTIL SCN 7661956
            AUXILIARY DESTINATION '/usr/aux_files'
            DATAPUMP DIRECTORY SOURCE_DIR 
            DUMP FILE 'jobs_regions_tbs.dmp' 
            EXPORT LOG 'jobs_regions_tbs.log'
            IMPORT SCRIPT 'jobs_regions_tbs_imp.sql'
            TABLESPACE DESTINATION '/orc/dbs';
          }
    

    The TRANSPORT TABLESPACE command places the files in the following directories on the computer system that runs the source database:

    • The directory that corresponds to the SOURCE_DIR directory object (/usr/db_files) contains the export dump file and export log file.

    • The /orc/dbs directory contains the generated data files for the tablespaces and the import script. You use this script to complete the instantiation by attaching the tablespace at the destination database.

  10. Modify the import script, if necessary. You might need to modify one or both of the following items in the script:
    • You might want to change the method used to make the exported tablespaces part of the destination database. The import script includes two ways to make the exported tablespaces part of the destination database: a Data Pump import command (impdp), and a script for attaching the tablespaces using the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package.

      The default script uses the attach tablespaces method. The Data Pump import command is commented out. To use Data Pump import, remove the comment symbols (/* and */) surrounding the impdp command, and either surround the attach tablespaces script with comments or remove the attach tablespaces script. The attach tablespaces script starts with SET SERVEROUTPUT ON and continues to the end of the file.

    • You might need to change the directory paths specified in the script. In Step 11, you will transfer the import script (jobs_regions_tbs_imp.sql), the Data Pump export dump file (jobs_regions_tbs.dmp), and the generated data file for each tablespace (jobs_tbs.dbf and regions_tbs.dbf) to one or more directories on the computer system running the destination database. Ensure that the directory paths specified in the script are the correct directory paths.

  11. Transfer the import script (jobs_regions_tbs_imp.sql), the Data Pump export dump file (jobs_regions_tbs.dmp), and the generated data file for each tablespace (jobs_tbs.dbf and regions_tbs.dbf) to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer the file to the destination database. After the file transfer, these files should reside in the directories specified in the import script.
  12. In SQL*Plus, connect to the destination database tts2.example.com as the Oracle Streams administrator.
  13. Run the import script:
    SET ECHO ON
    SPOOL jobs_tbs_imp.out
    @jobs_tbs_imp.sql
    

    When the script completes, check the jobs_tbs_imp.out spool file to ensure that all actions finished successfully.

8.4.2 Instantiating an Entire Database Using RMAN

The Recovery Manager (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 an Oracle Streams instantiation, the target database is the source database and the new database that is created is the destination database. The RMAN DUPLICATE command requires that the source and destination database run on the same platform.

The RMAN CONVERT DATABASE command generates the data files 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 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 examples in this section describe the steps required to instantiate an entire database using the RMAN DUPLICATE command or CONVERT DATABASE command. To use one of these RMAN commands for full database instantiation, complete the following general steps:

  1. Copy the entire source database to the destination site using the RMAN command.
  2. Remove the Oracle Streams configuration at the destination site using the REMOVE_STREAMS_CONFIGURATION procedure in the DBMS_STREAMS_ADM package.
  3. Configure Oracle Streams destination site, including configuration of one or more apply processes to apply changes from the source database.

You can complete this process without stopping any running capture processes or propagations at the source database.

Follow the instructions in one of these sections:

Note:

  • To configure an Oracle Streams replication environment that replicates all of the supported changes for an entire database, you can use the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in the DBMS_STREAMS_ADM package. See "Configuring Two-Database Global Replication with Local Capture" for instructions.

  • Oracle recommends that you do not use RMAN for instantiation in an environment where distributed transactions are possible. Doing so can cause in-doubt transactions that must be corrected manually. Use export/import or transportable tablespaces for instantiation instead.

See Also:

"Configuring an Oracle Streams Administrator on All Databases" for information about configuring an Oracle Streams administrator

8.4.2.1 Instantiating an Entire Database on the Same Platform Using RMAN

The example in this section instantiates an entire database using the RMAN DUPLICATE command. The example makes the following assumptions:

  • You want to capture all of the changes made to a source database named dpx1.example.com, propagate these changes to a separate destination database named dpx2.example.com, and apply these changes at the destination database.

  • You have configured an Oracle Streams administrator at the source database named strmadmin. See "Configuring an Oracle Streams Administrator on All Databases".

  • The dpx1.example.com and dpx2.example.com databases run on the same platform.

See Also:

Oracle Database Backup and Recovery User's Guide for instructions about using the RMAN DUPLICATE command

Complete the following steps to instantiate an entire database using RMAN when the source and destination databases run on the same platform:

  1. Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of dpx1.example.com if one does not exist.

    Note:

    A backup of the source database is not necessary if you use the FROM ACTIVE DATABASE option when you run the RMAN DUPLICATE command. For large databases, the FROM ACTIVE DATABASE option requires significant network resources. This example does not use this option.

  2. In SQL*Plus, connect to the source database dpx1.example.com as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  3. Create an ANYDATA queue to stage the changes from the source database if such a queue does not already exist. This queue will stage changes that will be propagated to the destination database after it has been configured.

    For example, the following procedure creates a queue named streams_queue:

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    

    Remain connected as the Oracle Streams administrator in SQL*Plus at the source database through Step 9.

  4. Create a database link from dpx1.example.com to dpx2.example.com:
    CREATE DATABASE LINK dpx2.example.com CONNECT TO strmadmin 
       IDENTIFIED BY password USING 'dpx2.example.com';
    
  5. Create a propagation from the source queue at the source database to the destination queue at the destination database. The destination queue at the destination database does not exist yet, but creating this propagation ensures that logical change records (LCRs) enqueued into the source queue will remain staged there until propagation is possible. In addition to captured LCRs, the source queue will stage internal messages that will populate the Oracle Streams data dictionary at the destination database.

    The following procedure creates the dpx1_to_dpx2 propagation:

    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'dpx1_to_dpx2', 
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@dpx2.example.com',
        include_dml             => TRUE,
        include_ddl             => TRUE,
        source_database         => 'dpx1.example.com',
        inclusion_rule          => TRUE,
        queue_to_queue          => TRUE);
    END;
    /
    
  6. Stop the propagation you created in Step 5.
    BEGIN
      DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
        propagation_name  => 'dpx1_to_dpx2');
    END;
    /
    
  7. Prepare the entire source database for instantiation, if it has not been prepared for instantiation previously. See "Preparing All of the Database Objects in a Database for Instantiation" for instructions.

    If there is no capture process that captures all of the changes to the source database, then create this capture process using the ADD_GLOBAL_RULES procedure in the DBMS_STREAMS_ADM package. If the capture process is a local capture process or a downstream capture process with a database link to the source database, then running this procedure automatically prepares the entire source database for instantiation. If such a capture process already exists, then ensure that the source database has been prepared for instantiation by querying the DBA_CAPTURE_PREPARED_DATABASE data dictionary view.

  8. If you created a capture process in Step 7, then start the capture process:
    BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
        capture_name  => 'capture_db');
    END;
    /
    
  9. Determine the until SCN for the RMAN DUPLICATE command:
    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
      until_scn NUMBER;
    BEGIN
      until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
          DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
    END;
    /
    

    Make a note of the until SCN returned. You will use this number in a later step. For this example, assume that the returned until SCN is 3050191.

  10. In SQL*Plus, connect to the source database dpx1.example.com as an administrative user.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  11. Archive the current online redo log:
    ALTER SYSTEM ARCHIVE LOG CURRENT;
    
  12. Prepare your environment for database duplication, which includes preparing the destination database as an auxiliary instance for duplication. See Oracle Database Backup and Recovery User's Guide for instructions.
  13. Start the RMAN client, and connect to the source database dpx1.example.com as TARGET and to the destination database dpx2.example.com as AUXILIARY.

    See Oracle Database Backup and Recovery Reference for more information about the RMAN CONNECT command.

  14. Use the RMAN DUPLICATE command with the OPEN RESTRICTED option to instantiate the source database at the destination database. The OPEN RESTRICTED option is required. This option enables a restricted session in the duplicate database by issuing the following SQL statement: ALTER SYSTEM ENABLE RESTRICTED SESSION. RMAN issues this statement immediately before the duplicate database is opened.

    You can use the UNTIL SCN clause to specify an SCN for the duplication. Use the until SCN determined in Step 9 for this clause. The until SCN specified for the RMAN DUPLICATE command must be higher than the SCN when the database was prepared for instantiation in Step 7. Also, archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 11 archived the redo log containing the until SCN.

    Ensure that you use TO database_name in the DUPLICATE command to specify the name of the duplicate database. In this example, the duplicate database name is dpx2. Therefore, the DUPLICATE command for this example includes TO dpx2.

    The following is an example of an RMAN DUPLICATE command:

    RMAN> RUN
          { 
            SET UNTIL SCN 3050191;
            ALLOCATE AUXILIARY CHANNEL dpx2 DEVICE TYPE sbt; 
            DUPLICATE TARGET DATABASE TO dpx2 
            NOFILENAMECHECK
            OPEN RESTRICTED;
          }
    

    See Also:

    Oracle Database Backup and Recovery Reference for more information about the RMAN DUPLICATE command

  15. At the destination database, connect as an administrative user in SQL*Plus and rename the database global name. After the RMAN DUPLICATE command, the destination database has the same global name as the source database.
    ALTER DATABASE RENAME GLOBAL_NAME TO DPX2.EXAMPLE.COM;
    
  16. At the destination database, connect as an administrative user in SQL*Plus and run the following procedure:

    Note:

    Ensure that you are connected to the destination database, not the source database, when you run this procedure because it removes the local Oracle Streams configuration.

    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    

    Note:

    Any supplemental log groups for the tables at the source database are retained at the destination database, and the REMOVE_STREAMS_CONFIGURATION procedure does not drop them. You can drop these supplemental log groups if necessary.

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information about the REMOVE_STREAMS_CONFIGURATION procedure

  17. At the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:
    ALTER SYSTEM DISABLE RESTRICTED SESSION;
    
  18. At the destination database, connect as the Oracle Streams administrator. See "Configuring an Oracle Streams Administrator on All Databases".
  19. At the destination database, create the queue specified in Step 5.

    For example, the following procedure creates a queue named streams_queue:

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
  20. At the destination database, configure the Oracle Streams environment.

    Note:

    Do not start any apply processes at the destination database until after you set the global instantiation SCN in Step 22.

  21. At the destination database, create a database link from the destination database to the source database:
    CREATE DATABASE LINK dpx1.example.com CONNECT TO strmadmin 
       IDENTIFIED BY password USING 'dpx1.example.com';
    

    This database link is required because the next step runs the SET_GLOBAL_INSTANTIATION_SCN procedure with the recursive parameter set to TRUE.

  22. At the destination database, set the global instantiation SCN for the source database. The RMAN DUPLICATE command duplicates the database up to one less than the SCN value specified in the UNTIL SCN clause. Therefore, you should subtract one from the until SCN value that you specified when you ran the DUPLICATE command in Step 14. In this example, the until SCN was set to 3050191. Therefore, the instantiation SCN should be set to 3050191 - 1, or 3050190.

    For example, to set the global instantiation SCN to 3050190 for the dpx1.example.com source database, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name   =>  'dpx1.example.com',
        instantiation_scn      =>  3050190,
        recursive              =>  TRUE);
    END;
    /
    

    Notice that the recursive parameter is set to TRUE to set the instantiation SCN for all schemas and tables in the destination database.

  23. At the destination database, you can start any apply processes that you configured.
  24. At the source database, start the propagation you stopped in Step 6:
    BEGIN
      DBMS_PROPAGATION_ADM.START_PROPAGATION(
        queue_name  => 'dpx1_to_dpx2');
    END;
    /
8.4.2.2 Instantiating an Entire Database on Different Platforms Using RMAN

The example in this section instantiates an entire database using the RMAN CONVERT DATABASE command. The example makes the following assumptions:

  • You want to capture all of the changes made to a source database named cvx1.example.com, propagate these changes to a separate destination database named cvx2.example.com, and apply these changes at the destination database.

  • You have configured an Oracle Streams administrator at the source database named strmadmin. See "Configuring an Oracle Streams Administrator on All Databases".

  • The cvx1.example.com and cvx2.example.com databases run on different platforms, and the platform combination is supported by the RMAN CONVERT DATABASE command. You can use the DBMS_TDB package to determine whether a platform combination is supported.

The RMAN CONVERT DATABASE command produces converted data files, an initialization parameter file (PFILE), and a SQL script. The converted data files and PFILE are for use with the destination database, and the SQL script creates the destination database on the destination platform.

See Also:

Oracle Database Backup and Recovery User's Guide for instructions about using the RMAN CONVERT DATABASE command

Complete the following steps to instantiate an entire database using RMAN when the source and destination databases run on different platforms:

  1. Create a backup of the source database if one does not exist. RMAN requires a valid backup. In this example, create a backup of cvx1.example.com if one does not exist.

  2. In SQL*Plus, connect to the source database cvx1.example.com as the Oracle Streams administrator.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  3. Create an ANYDATA queue to stage the changes from the source database if such a queue does not already exist. This queue will stage changes that will be propagated to the destination database after it has been configured.

    For example, the following procedure creates a queue named streams_queue:

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    

    Remain connected as the Oracle Streams administrator in SQL*Plus at the source database through Step 8.

  4. Create a database link from cvx1.example.com to cvx2.example.com:

    CREATE DATABASE LINK cvx2.example.com CONNECT TO strmadmin 
       IDENTIFIED BY password USING 'cvx2.example.com';
    
  5. Create a propagation from the source queue at the source database to the destination queue at the destination database. The destination queue at the destination database does not exist yet, but creating this propagation ensures that logical change records (LCRs) enqueued into the source queue will remain staged there until propagation is possible. In addition to captured LCRs, the source queue will stage internal messages that will populate the Oracle Streams data dictionary at the destination database.

    The following procedure creates the cvx1_to_cvx2 propagation:

    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'cvx1_to_cvx2', 
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@cvx2.example.com',
        include_dml             => TRUE,
        include_ddl             => TRUE,
        source_database         => 'cvx1.example.com',
        inclusion_rule          => TRUE,
        queue_to_queue          => TRUE);
    END;
    /
    
  6. Stop the propagation you created in Step 5.

    BEGIN
      DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
        propagation_name  => 'cvx1_to_cvx2');
    END;
    /
    
  7. Prepare the entire source database for instantiation, if it has not been prepared for instantiation previously. See "Preparing All of the Database Objects in a Database for Instantiation" for instructions.

    If there is no capture process that captures all of the changes to the source database, then create this capture process using the ADD_GLOBAL_RULES procedure in the DBMS_STREAMS_ADM package. If the capture process is a local capture process or a downstream capture process with a database link to the source database, then running this procedure automatically prepares the entire source database for instantiation. If such a capture process already exists, then ensure that the source database has been prepared for instantiation by querying the DBA_CAPTURE_PREPARED_DATABASE data dictionary view.

  8. If you created a capture process in Step 7, then start the capture process:

    BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
        capture_name  => 'capture_db');
    END;
    /
    
  9. In SQL*Plus, connect to the source database as an administrative user.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  10. Archive the current online redo log:

    ALTER SYSTEM ARCHIVE LOG CURRENT;
    
  11. Prepare your environment for database conversion, which includes opening the source database in read-only mode. Complete the following steps:

    1. If the source database is open, then shut it down and start it in read-only mode.

    2. Run the CHECK_DB and CHECK_EXTERNAL functions in the DBMS_TDB package. Check the results to ensure that the conversion is supported by the RMAN CONVERT DATABASE command.

    See Also:

    Oracle Database Backup and Recovery User's Guide for more information about these steps

  12. Determine the current SCN of the source database:

    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
      current_scn NUMBER;
    BEGIN
      current_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
          DBMS_OUTPUT.PUT_LINE('Current SCN: ' || current_scn);
    END;
    /
    

    Make a note of the SCN value returned. You will use this number in Step 24. For this example, assume that the returned value is 46931285.

  13. Start the RMAN client, and connect to the source database cvx1.example.com as TARGET.

    See Oracle Database Backup and Recovery Reference for more information about the RMAN CONNECT command.

  14. Run the CONVERT DATABASE command.

    Ensure that you use NEW DATABASE database_name in the CONVERT DATABASE command to specify the name of the destination database. In this example, the destination database name is cvx2. Therefore, the CONVERT DATABASE command for this example includes NEW DATABASE cvx2.

    The following is an example of an RMAN CONVERT DATABASE command for a destination database that is running on the Linux IA (64-bit) platform:

    CONVERT DATABASE NEW DATABASE 'cvx2'
              TRANSPORT SCRIPT '/tmp/convertdb/transportscript.sql'     
              TO PLATFORM 'Linux IA (64-bit)'
              DB_FILE_NAME_CONVERT '/home/oracle/dbs','/tmp/convertdb';
    
  15. Transfer the data files, PFILE, and SQL script produced by the RMAN CONVERT DATABASE command to the computer system that will run the destination database.

  16. On the computer system that will run the destination database, modify the SQL script so that the destination database always opens with restricted session enabled.

    The following is a sample script with the necessary modifications in bold font:

    -- The following commands will create a new control file and use it
    -- to open the database.
    -- Data used by Recovery Manager will be lost.
    -- The contents of online logs will be lost and all backups will
    -- be invalidated. Use this only if online logs are damaged.
     
    -- After mounting the created controlfile, the following SQL
    -- statement will place the database in the appropriate
    -- protection mode:
    --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
     
    STARTUP NOMOUNT PFILE='init_00gd2lak_1_0.ora'
    CREATE CONTROLFILE REUSE SET DATABASE "CVX2" RESETLOGS  NOARCHIVELOG
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 32
        MAXINSTANCES 1
        MAXLOGHISTORY 226
    LOGFILE
      GROUP 1 '/tmp/convertdb/archlog1'  SIZE 25M,
      GROUP 2 '/tmp/convertdb/archlog2'  SIZE 25M
    DATAFILE
      '/tmp/convertdb/systemdf',
      '/tmp/convertdb/sysauxdf',
      '/tmp/convertdb/datafile1',
      '/tmp/convertdb/datafile2',
      '/tmp/convertdb/datafile3'
    CHARACTER SET WE8DEC
    ;
     
    -- NOTE: This ALTER SYSTEM statement is added to enable restricted session.
    
    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    
    -- Database can now be opened zeroing the online logs.
    ALTER DATABASE OPEN RESETLOGS;
     
    -- No tempfile entries found to add.
    --
     
    set echo off
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    prompt * Your database has been created successfully!
    prompt * There are many things to think about for the new database. Here
    prompt * is a checklist to help you stay on track:
    prompt * 1. You may want to redefine the location of the directory objects.
    prompt * 2. You may want to change the internal database identifier (DBID) 
    prompt *    or the global database name for this database. Use the 
    prompt *    NEWDBID Utility (nid).
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    SHUTDOWN IMMEDIATE 
    -- NOTE: This startup has the UPGRADE parameter.
    -- It already has restricted session enabled, so no change is needed.
    STARTUP UPGRADE PFILE='init_00gd2lak_1_0.ora'
    @@ ?/rdbms/admin/utlirp.sql 
    SHUTDOWN IMMEDIATE 
    -- NOTE: The startup below is generated without the RESTRICT clause.
    -- Add the RESTRICT clause.
    STARTUP RESTRICT PFILE='init_00gd2lak_1_0.ora'
    -- The following step will recompile all PL/SQL modules.
    -- It may take serveral hours to complete.
    @@ ?/rdbms/admin/utlrp.sql 
    set feedback 6;
    

    Other changes to the script might be necessary. For example, the data file locations and PFILE location might need to be changed to point to the correct locations on the destination database computer system.

  17. At the destination database, connect as an administrative user in SQL*Plus and run the following procedure:

    Note:

    Ensure that you are connected to the destination database, not the source database, when you run this procedure because it removes the local Oracle Streams configuration.

    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    

    Note:

    Any supplemental log groups for the tables at the source database are retained at the destination database, and the REMOVE_STREAMS_CONFIGURATION procedure does not drop them. You can drop these supplemental log groups if necessary.

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information about the REMOVE_STREAMS_CONFIGURATION procedure

  18. In SQL*Plus, connect to the destination database cvx2.example.com as the Oracle Streams administrator.

  19. Drop the database link from the source database to the destination database that was cloned from the source database:

    DROP DATABASE LINK cvx2.example.com;
    
  20. At the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

    ALTER SYSTEM DISABLE RESTRICTED SESSION;
    
  21. At the destination database, create the queue specified in Step 5.

    For example, the following procedure creates a queue named streams_queue:

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
  22. At the destination database, connect as the Oracle Streams administrator and configure the Oracle Streams environment. See "Configuring an Oracle Streams Administrator on All Databases".

    Note:

    Do not start any apply processes at the destination database until after you set the global instantiation SCN in Step 24.

  23. At the destination database, create a database link to the source database:

    CREATE DATABASE LINK cvx1.example.com CONNECT TO strmadmin 
       IDENTIFIED BY password USING 'cvx1.example.com';
    

    This database link is required because the next step runs the SET_GLOBAL_INSTANTIATION_SCN procedure with the recursive parameter set to TRUE.

  24. At the destination database, set the global instantiation SCN for the source database to the SCN value returned in Step 12.

    For example, to set the global instantiation SCN to 46931285 for the cvx1.example.com source database, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name   =>  'cvx1.example.com',
        instantiation_scn      =>  46931285,
        recursive              =>  TRUE);
    END;
    /
    

    Notice that the recursive parameter is set to TRUE to set the instantiation SCN for all schemas and tables in the destination database.

  25. At the destination database, you can start any apply processes that you configured.

  26. At the source database, start the propagation you stopped in Step 6:

    BEGIN
      DBMS_PROPAGATION_ADM.START_PROPAGATION(
        propagation_name  => 'cvx1_to_cvx2');
    END;
    /

8.5 Setting Instantiation SCNs at a Destination Database

An instantiation system change number (SCN) instructs an apply process at a destination database to apply changes that committed after a specific SCN at a source database. You can set instantiation SCNs in one of the following ways:

  • Export the relevant database objects at the source database and import them at the destination database. In this case, the export/import creates the database objects at the destination database, populates them with the data from the source database, and sets the relevant instantiation SCNs. You can use Data Pump export/import for instantiations. See "Setting Instantiation SCNs Using Export/Import" for information about the instantiation SCNs that are set for different types of export/import operations.

  • Perform a metadata only export/import using Data Pump. If you use Data Pump export/import, then set the CONTENT parameter to METADATA_ONLY during export at the source database or import at the destination database, or both. Instantiation SCNs are set for the database objects, but no data is imported. See "Setting Instantiation SCNs Using Export/Import" for information about the instantiation SCNs that are set for different types of export/import operations.

  • Use transportable tablespaces to copy the objects in one or more tablespaces from a source database to a destination database. An instantiation SCN is set for each schema in these tablespaces and for each database object in these tablespaces that was prepared for instantiation before the export. See "Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN".

  • Set the instantiation SCN using the SET_TABLE_INSTANTIATION_SCN, SET_SCHEMA_INSTANATIATION_SCN, and SET_GLOBAL_INSTANTIATION_SCN procedures in the DBMS_APPLY_ADM package. See "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package".

8.5.1 Setting Instantiation SCNs Using Export/Import

This section discusses setting instantiation SCNs by performing an export/import. The information in this section applies to both metadata export/import operations and to export/import operations that import rows. You can specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME.

The following sections describe how the instantiation SCNs are set for different types of export/import operations. These sections refer to prepared tables. Prepared tables are tables that have been prepared for instantiation using the PREPARE_TABLE_INSTANTIATION procedure, PREPARE_SYNC_INSTANTIATION function, PREPARE_SCHEMA_INSTANTIATION procedure, or PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package. A table must be a prepared table before export in order for an instantiation SCN to be set for it during import. However, the database and schemas do not need to be prepared before the export in order for their instantiation SCNs to be set for them during import.

8.5.1.1 Full Database Export and Full Database Import

A full database export and full database import sets the following instantiation SCNs at the import database:

  • The database, or global, instantiation SCN

  • The schema instantiation SCN for each imported user

  • The table instantiation SCN for each prepared table that is imported

8.5.1.2 Full Database or User Export and User Import

A full database or user export and user import sets the following instantiation SCNs at the import database:

  • The schema instantiation SCN for each imported user

  • The table instantiation SCN for each prepared table that is imported

8.5.1.3 Full Database, User, or Table Export and Table Import

Any export that includes one or more tables and a table import sets the table instantiation SCN for each prepared table that is imported at the import database.

Note:

  • If a non-NULL instantiation SCN already exists for a database object at a destination database that performs an import, then the import updates the instantiation SCN for that database object.

  • During an export for an Oracle Streams instantiation, ensure that no data definition language (DDL) changes are made to objects being exported.

  • Any table supplemental logging specifications for the tables exported from the export database are retained when the tables are imported at the import database.

8.5.2 Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package

You can set an instantiation SCN at a destination database for a specified table, a specified schema, or an entire database using one of the following procedures in the DBMS_APPLY_ADM package:

If you set the instantiation SCN for a schema using SET_SCHEMA_INSTANTIATION_SCN, then you can set the recursive parameter to TRUE when you run this procedure to set the instantiation SCN for each table in the schema. Similarly, if you set the instantiation SCN for a database using SET_GLOBAL_INSTANTIATION_SCN, then you can set the recursive parameter to TRUE when you run this procedure to set the instantiation SCN for the schemas in the database and for each table owned by these schemas.

Note:

  • If you set the recursive parameter to TRUE in the SET_SCHEMA_INSTANTIATION_SCN procedure or the SET_GLOBAL_INSTANTIATION_SCN procedure, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the user who executes the procedure.

  • When setting an instantiation SCN for a database object, always specify the name of the schema and database object at the source database, even if a rule-based transformation or apply handler is configured to change the schema name or database object name.

  • If a relevant instantiation SCN is not present, then an error is raised during apply.

  • These procedures can set an instantiation SCN for changes captured by capture processes and synchronous captures.

Table 8-3 lists each procedure and the types of statements for which they set an instantiation SCN.

Table 8-3 Set Instantiation SCN Procedures and the Statements They Cover

Procedure Sets Instantiation SCN for Examples

SET_TABLE_INSTANTIATION_SCN

DML and DDL statements on tables, except CREATE TABLE

DDL statements on table indexes and table triggers

UPDATE

ALTER TABLE

DROP TABLE

CREATE, ALTER, or DROP INDEX on a table

CREATE, ALTER, or DROP TRIGGER on a table

SET_SCHEMA_INSTANTIATION_SCN

DDL statements on users, except CREATE USER

DDL statements on all database objects that have a non-PUBLIC owner, except for those DDL statements handled by a table-level instantiation SCN

CREATE TABLE

ALTER USER

DROP USER

CREATE PROCEDURE

SET_GLOBAL_INSTANTIATION_SCN

DDL statements on database objects other than users with no owner

DDL statements on database objects owned by public

CREATE USER statements

CREATE USER

CREATE TABLESPACE

8.5.2.1 Setting the Instantiation SCN While Connected to the Source Database

The user who runs the examples in this section must have access to a database link from the source database to the destination database. In these examples, the database link is hrdb2.example.com. The following example sets the instantiation SCN for the hr.departments table at the hrdb2.example.com database to the current SCN by running the following procedure at the source database hrdb1.example.com:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@HRDB2.EXAMPLE.COM(
    source_object_name    => 'hr.departments',
    source_database_name  => 'hrdb1.example.com',
    instantiation_scn     => iscn);
END;
/

The following example sets the instantiation SCN for the oe schema and all of its objects at the hrdb2.example.com database to the current source database SCN by running the following procedure at the source database hrdb1.example.com:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@HRDB2.EXAMPLE.COM(
    source_schema_name    => 'oe',
    source_database_name  => 'hrdb1.example.com',
    instantiation_scn     => iscn,
    recursive             => TRUE);
END;
/

Because the recursive parameter is set to TRUE, running this procedure sets the instantiation SCN for each database object in the oe schema.

Note:

When you set the recursive parameter to TRUE, a database link from the destination database to the source database is required, even if you run the procedure while you are connected to the source database. This database link must have the same name as the global name of the source database and must be accessible to the current user.

8.5.2.2 Setting the Instantiation SCN While Connected to the Destination Database

The user who runs the examples in this section must have access to a database link from the destination database to the source database. In these examples, the database link is hrdb1.example.com. The following example sets the instantiation SCN for the hr.departments table at the hrdb2.example.com database to the current source database SCN at hrdb1.example.com by running the following procedure at the destination database hrdb2.example.com:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@HRDB1.EXAMPLE.COM;
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.departments',
    source_database_name  => 'hrdb1.example.com',
    instantiation_scn     => iscn);
END;
/

The following example sets the instantiation SCN for the oe schema and all of its objects at the hrdb2.example.com database to the current source database SCN at hrdb1.example.com by running the following procedure at the destination database hrdb2.example.com:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@HRDB1.EXAMPLE.COM;
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
    source_schema_name    => 'oe',
    source_database_name  => 'hrdb1.example.com',
    instantiation_scn     => iscn,
    recursive             => TRUE);
END;
/

Because the recursive parameter is set to TRUE, running this procedure sets the instantiation SCN for each database object in the oe schema.

Note:

If an apply process applies changes to a remote non-Oracle database, then set the apply_database_link parameter to the database link used for remote apply when you set the instantiation SCN.

See Also:

8.6 Monitoring Instantiation

The following sections contain queries that you can run to determine which database objects are prepared for instantiation at a source database and the instantiation SCN for database objects at a destination database:

8.6.1 Determining Which Database Objects Are Prepared for Instantiation

See "Capture Rules and Preparation for Instantiation" for information about preparing database objects for instantiation.

To determine which database objects have been prepared for instantiation, query the following data dictionary views:

  • DBA_CAPTURE_PREPARED_TABLES

  • DBA_SYNC_CAPTURE_PREPARED_TABS

  • DBA_CAPTURE_PREPARED_SCHEMAS

  • DBA_CAPTURE_PREPARED_DATABASE

For example, to list all of the tables that have been prepared for instantiation by the PREPARE_TABLE_INSTANTIATION procedure, the SCN for the time when each table was prepared, and the time when each table was prepared, run the following query:

COLUMN TABLE_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN SCN HEADING 'Prepare SCN' FORMAT 99999999999
COLUMN TIMESTAMP HEADING 'Time Ready for|Instantiation'

SELECT TABLE_OWNER, 
       TABLE_NAME, 
       SCN, 
       TO_CHAR(TIMESTAMP, 'HH24:MI:SS MM/DD/YY') TIMESTAMP
  FROM DBA_CAPTURE_PREPARED_TABLES;

Your output looks similar to the following:

                                                  Time Ready for
Table Owner     Table Name            Prepare SCN Instantiation
--------------- --------------- ----------------- -----------------
HR              COUNTRIES                  196655 12:59:30 02/28/02
HR              DEPARTMENTS                196658 12:59:30 02/28/02
HR              EMPLOYEES                  196659 12:59:30 02/28/02
HR              JOBS                       196660 12:59:30 02/28/02
HR              JOB_HISTORY                196661 12:59:30 02/28/02
HR              LOCATIONS                  196662 12:59:30 02/28/02
HR              REGIONS                    196664 12:59:30 02/28/02

8.6.2 Determining the Tables for Which an Instantiation SCN Has Been Set

An instantiation SCN is set at a destination database. It controls which captured logical change records (LCRs) for a database object are ignored by an apply process and which captured LCRs for a database object are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR. The LCRs can be captured by a capture process or a synchronous capture. See "Setting Instantiation SCNs at a Destination Database".

To determine which database objects have a set instantiation SCN, query the following corresponding data dictionary views:

  • DBA_APPLY_INSTANTIATED_OBJECTS

  • DBA_APPLY_INSTANTIATED_SCHEMAS

  • DBA_APPLY_INSTANTIATED_GLOBAL

The following query lists each table for which an instantiation SCN has been set at a destination database and the instantiation SCN for each table:

COLUMN SOURCE_DATABASE HEADING 'Source Database' FORMAT A20
COLUMN SOURCE_OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN SOURCE_OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN INSTANTIATION_SCN HEADING 'Instantiation SCN' FORMAT 99999999999

SELECT SOURCE_DATABASE, 
       SOURCE_OBJECT_OWNER, 
       SOURCE_OBJECT_NAME, 
       INSTANTIATION_SCN 
  FROM DBA_APPLY_INSTANTIATED_OBJECTS
  WHERE APPLY_DATABASE_LINK IS NULL;

Your output looks similar to the following:

Source Database     Object Owner    Object Name     Instantiation SCN
-------------------- --------------- --------------- -----------------
DBS1.EXAMPLE.COM     HR              REGIONS                    196660
DBS1.EXAMPLE.COM     HR              COUNTRIES                  196660
DBS1.EXAMPLE.COM     HR              LOCATIONS                  196660

Note:

You can also display instantiation SCNs for changes that are applied to remote non-Oracle databases. This query does not display these instantiation SCNs because it lists an instantiation SCN only if the APPLY_DATABASE_LINK column is NULL.