8
Performing Instantiations

This chapter contains instructions for performing instantiations in a Streams replication environment. Database objects must be instantiated at a destination database before changes to these objects can be replicated.

This chapter contains these topics:

• Preparing Database Objects for Instantiation at a Source Database
• Aborting Preparation for Instantiation at a Source Database
• Instantiating Objects in a Streams Replication Environment
• Setting Instantiation SCNs at a Destination Database

  See Also: Chapter 2, "Instantiation and Streams Replication"

Preparing Database Objects for Instantiation at a Source Database

If you use the DBMS_STREAMS_ADM package to create rules for a capture process, 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.

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

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

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 will wait until the long running transaction is complete before it records the ignore SCN for the objects, which is the SCN below which changes to an object cannot be applied at destination databases.

For example, to prepare the hr.regions table for instantiation, run the following procedure:

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

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.
• ABORT_SCHEMA_INSTANTIATION reverses the effects of PREPARE_SCHEMA_INSTANTIATION.
• ABORT_GLOBAL_INSTANTIATION reverses the effects of PREPARE_GLOBAL_INSTANTIATION.

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;
/

Instantiating Objects in a Streams Replication Environment

You can instantiate database objects in a Streams environment in the following ways:

• Instantiating Objects in a Streams Environment Using Data Pump Export/Import
• Instantiating Objects in a Streams Environment Using Transportable Tablespaces
• Instantiating Objects in a Streams Environment Using Original Export/Import
• Instantiating an Entire Database in a Streams Environment Using RMAN

You can use Oracle Data Pump, transportable tablespaces, and the original Export/Import utilities to instantiate individual database objects, schemas, or an entire database. You can use RMAN only to instantiate an entire database.

Instantiating Objects in a Streams Environment Using Data Pump Export/Import

The example in this section describes the steps required to instantiate objects in a 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 a source database but does not exist at a 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 objects at the destination database.

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

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

  See Also: Oracle Streams Concepts and Administration for information about configuring a Streams administrator Oracle Database Utilities for more information about Data Pump Part III, "Example Replication Environments" for examples that use the DBMS_DATAPUMP package for Streams instantiations

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

1. While connected in SQL*Plus to the source database as the Streams administrator, create a directory object to hold the export dump file and export log file:

   CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';
   
   

2. While connected as the Streams administrator strmadmin at the source database, prepare the database objects in the hr schema for instantiation. You can complete this step in one of the following ways:
   • 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.

     For example, 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 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.

   • Add rules for the hr schema to the positive rule set for a capture process using a procedure in the DBMS_RULE_ADM package, and then prepare the objects for instantiation manually by specifying the hr schema when you run the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package:

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

     Make sure you add the rules to the positive rule set for the capture process before you prepare the database objects for instantiation.

3. While still connected to the source database as the 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 4. 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, make sure no DDL changes are made to the objects being exported until after the export is complete.

4. 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 1. This example connects as the the Streams administrator strmadmin.

   The following is an example Data Pump export command:

   expdp strmadmin/strmadminpw 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

5. While connected in SQL*Plus to the destination database the Streams administrator, create a directory object to hold the import dump file and import log file:

   CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';
   
   

6. 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 5.
7. On a command line at the destination database, use Data Pump to import the export dump file hr_schema_dp.dmp. Make sure 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 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 5. This example connects as the the Streams administrator strmadmin.

   The following is an example import command:

   impdp strmadmin/strmadminpw 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 may drop these supplemental log groups if necessary.

   See Also: Oracle Database Utilities for information about performing a Data Pump import

Instantiating Objects in a Streams Environment Using Transportable Tablespaces

The example in this section describes the steps required to instantiate objects in a Streams environment using transportable tablespaces. Transportable tablespaces is usually faster than export/import.

To run this example, connect to the source database as an administrative user and create a new tablespace called jobs_tbs:

CREATE TABLESPACE jobs_tbs DATAFILE '/usr/oracle/dbs/jobs_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;

This example makes the following assumptions:

• You want to capture all of the changes to the hr.jobs_transport table 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 datafile named jobs_tbs.dbf.
• The jobs_tbs tablespace does not contain data from any other schemas.
• Neither the hr.jobs_transport table nor the jobs_tbs tablespace exist at the destination database.
• You have configured a Streams administrator at the source database named strmadmin.
• You have granted the Streams administrator the DBA role at the source database. The DBA role includes the EXP_FULL_DATABASE role, and a user must be granted this role to perform a transportable tablespaces export. In this example, the Streams administrator performs the transportable tablespaces export.
• You have configured a Streams administrator at the destination database named strmadmin.
• You have granted the Streams administrator the DBA role at the destination database. The DBA role includes the IMP_FULL_DATABASE role, and a user must be granted this role to perform a transportable tablespaces import. In this example, the Streams administrator performs the transportable tablespaces import.

  Note: You can use the MAINTAIN_SIMPLE_TABLESPACE procedure to configure Streams replication for a simple tablespace, and you can use the MAINTAIN_TABLESPACES procedure to configure Streams replication for a set of self-contained tablespaces. If you use one of these procedures, then instantiation is performed automatically for the objects in the tablespace. Oracle Corporation recommends using MAINTAIN_SIMPLE_TABLESPACE or MAINTAIN_TABLESPACES to configure replication for the objects in a tablespace.

  See Also: Oracle Database Administrator's Guide for more information about using transportable tablespaces and for information about limitations that may apply Oracle Streams Concepts and Administration for information about configuring a Streams administrator "Replicating and Maintaining Tablespaces Using Streams" for more information about using the MAINTAIN_SIMPLE_TABLESPACE and MAINTAIN_TABLESPACES procedures

Given these assumptions, complete the following steps to instantiate the hr schema using transportable tablespaces:

1. While connected in SQL*Plus to the source database as the Streams administrator strmadmin, create a directory object to hold the export dump file and export log file:

   CREATE DIRECTORY TRANS_DIR AS '/usr/trans_dir';
   
   

2. While connected as the Streams administrator strmadmin at the source database, prepare the hr.jobs_transport table for instantiation. You can complete this step in one of the following ways:
   • Add rules for the hr.jobs_transport 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.

     For example, the following procedure adds rules to the positive rule set of a capture process named strm01_capture and prepares the hr.jobs_transport table:

     BEGIN
       DBMS_STREAMS_ADM.ADD_TABLE_RULES(
         table_name     => 'hr.jobs_transport',   
         streams_type   => 'capture',
         streams_name   => 'strm01_capture',
         queue_name     => 'strmadmin.strm01_queue',
         include_dml    => true,
         include_ddl    => true,
         inclusion_rule => true);
     END;
     /
     
     

   • Add rules for the hr.jobs_transport table to the positive rule set for a capture process using a procedure in the DBMS_RULE_ADM package, and then prepare the hr.jobs_transport table for instantiation manually by specifying the table when you run the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package:

     BEGIN
       DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
         table_name  => 'hr.jobs_transport');
     END;
     /
     
     

     Make sure you add the rules to the positive rule set for the capture process before you prepare the database objects for instantiation.

3. While connected the Streams administrator at the source database, make the tablespace that contains the objects you are instantiating read-only. In this example, the demo_hr tablespace contains the database objects.

   ALTER TABLESPACE jobs_tbs READ ONLY;
   
   

4. On a command line, use the Data Pump Export utility to export the demo_hr tablespace at the source database using transportable tablespaces export parameters. The following is an example export command that uses transportable tablespaces export parameters:

   expdp strmadmin/strmadminpw TRANSPORT_TABLESPACES=jobs_tbs 
   DIRECTORY=TRANS_DIR DUMPFILE=jobs_tbs_ts.dmp
   
   

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

   You also may perform an instantiation using transportable tablespaces and the original Export/Import utilities.

   See Also: Oracle Database Utilities for information about performing an export

5. While connected to the destination database as the Streams administrator strmadmin, create a directory object to hold the import dump file and import log file:

   CREATE DIRECTORY TRANS_DIR AS '/usr/trans_dir';
   
   

6. Transfer both the data files for the tablespace and the export dump file jobs_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 5.
7. On a command line at the destination database, use the Data Pump Import utility to import the export dump file jobs_tbs_ts.dmp using transportable tablespaces import parameters. Performing the import automatically sets the instantiation SCN for the hr.jobs_transport table at the destination database.

   The following is an example import command:

   impdp strmadmin/strmadminpw DIRECTORY=TRANS_DIR DUMPFILE=jobs_tbs_ts.dmp 
   TRANSPORT_DATAFILES='/usr/orc/dbs/jobs_tbs.dbf'
   
   

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

   If you are importing a tablespace that had more than one datafile, then specify each datafile in the import command. For example, if the import in this example had a second datafile named jobs_tbs2.dbf, then you can use the following import command:

   impdp strmadmin/strmadminpw DIRECTORY=TRANS_DIR DUMPFILE=jobs_tbs_ts.dmp 
   TRANSPORT_DATAFILES=('/usr/orc/dbs/jobs_tbs.dbf', 
   '/usr/orc/dbs/jobs_tbs2.dbf')
   
   

   See Also: Oracle Database Utilities for information about performing an import

8. If necessary, at both the source database and the destination database, connect as the Streams administrator and put the tablespace into read/write mode:

    ALTER TABLESPACE jobs_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 may drop these supplemental log groups if necessary.

Instantiating Objects in a Streams Environment Using Original Export/Import

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

• You want to capture changes to all of the tables in the hr schema at a source database and apply these changes at a separate destination database.
• The hr schema exists at both the source database and the destination database. The hr schema at the source database contains seven tables. The hr schema at the destination database does not contain any tables. For the purposes of this example, you can drop the tables in the hr schema at the destination database using the following SQL statements:

  DROP TABLE hr.countries CASCADE CONSTRAINTS;
  DROP TABLE hr.departments CASCADE CONSTRAINTS;
  DROP TABLE hr.employees CASCADE CONSTRAINTS;
  DROP TABLE hr.job_history CASCADE CONSTRAINTS;
  DROP TABLE hr.jobs CASCADE CONSTRAINTS;
  DROP TABLE hr.locations CASCADE CONSTRAINTS;
  DROP TABLE hr.regions CASCADE CONSTRAINTS;
  
  

  The import re-creates these tables at the destination database.

• You have configured a Streams administrator at the source database named strmadmin.

  See Also: Oracle Streams Concepts and Administration for information about configuring a Streams administrator

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

1. While connected in SQL*Plus as the Streams administrator strmadmin at the source database, prepare the database objects in the hr schema for instantiation. You can complete this step in one of the following ways:
   • 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.

     For example, 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 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;
     /
     
     

   • Add rules for the hr schema to the positive rule set for a capture process using a procedure in the DBMS_RULE_ADM package, and then prepare the objects for instantiation manually by specifying the hr schema when you run the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package:

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

     Make sure you add the rules to the positive rule set for the capture process before you prepare the database objects for instantiation.

2. On the command line, use the original Export utility to export the tables in the hr schema at the source database. Make sure no DDL changes are made to the tables during the export.

   The following is an example export command:

   exp hr/hr FILE=hr_schema.dmp CONSISTENT=y 
   TABLES=countries,departments,employees,jobs,job_history,locations,regions 
   
   

   Because the hr schema includes foreign key constraints between tables, the CONSISTENT export parameter is set to y to ensure consistency between all of the objects in the schema. The OBJECT_CONISTENT export parameter is not used because the CONSISTENT export parameter provides a more stringent level of consistency.

   See Also: Oracle Database Utilities for information about performing an export using the original Export utility

3. Transfer the export dump file hr_schema.dmp to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer the to the destination database.
4. At the destination database, use the original Import utility to import the export dump file hr_schema.dmp. When you run the import command, make sure you set the STREAMS_INSTANTIATION import parameter to y. This parameter ensures that the import records instantiation SCN information for each object imported. Also, make sure 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 each table in the hr schema at the destination database.

   The following is an example import command:

   imp hr/hr FILE=hr_schema.dmp FULL=y COMMIT=y STREAMS_INSTANTIATION=y 
   LOG=import.log 
   
   

   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 may drop these supplemental log groups if necessary.

   See Also: Oracle Database Utilities for information about performing an import using the original Import utility

Instantiating an Entire Database in a Streams Environment Using RMAN

The example in this section describes the steps required to instantiate an entire database using the Recovery Manager (RMAN) DUPLICATE command. Using the RMAN DUPLICATE command is usually faster than export/import of an entire database. When you use the RMAN DUPLICATE command for full database instantiation, you perform the following general steps:

1. Copy the entire source database to the destination site using the RMAN DUPLICATE command.
2. Remove the Streams configuration at the destination site using the REMOVE_STREAMS_CONFIGURATION procedure in the DBMS_STREAMS_ADM package.
3. Configure 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. The example in this section makes the following assumptions:

• You want to capture all of the changes made to a source database named dpx1.net, propagate these changes to a separate destination database named dpx2.net, and apply these changes at the destination database.
• You have configured a Streams administrator at the source database named strmadmin.

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

  See Also: Oracle Streams Concepts and Administration for information about configuring a Streams administrator Oracle Database Backup and Recovery Advanced User's Guide for instructions on using the RMAN DUPLICATE command

Complete the following steps to instantiate an entire database using RMAN:

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.net if one does not exist.
2. While connected in SQL*Plus as the Streams administrator strmadmin at the source database, create a SYS.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 Streams administrator in SQL*Plus at the source database through Step 8.

3. Create a database link from dpx1.net to dpx2.net:

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

4. 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 events 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 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.net',
       include_dml             => true,
       include_ddl             => true,
       source_database         => 'dpx1.net',
       inclusion_rule          => true);
   END;
   /
   
   

5. Disable the propagation you created in Step 4.

   BEGIN
     DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(
       queue_name  => 'strmadmin.streams_queue',
       destination => 'dpx2.net');
   END;
   /
   
   

6. Prepare the entire source database for instantiation, if it has not been prepared for instantiation previously. 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 make sure the source database has been prepared for instantiation by querying the DBA_CAPTURE_PREPARED_DATABASE data dictionary view.

   If you need to create a capture process, then this example creates the capture_db capture process if it does not already exist:

   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 capture process already exists and you need to prepare the entire database for instantiation, then run the following procedure:

   EXEC DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION();
   
   

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

   BEGIN
     DBMS_CAPTURE_ADM.START_CAPTURE(
       capture_name  => 'capture_db');
   END;
   /
   
   

8. 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 Step 11. For this example, assume that the returned until SCN is 3050191.

9. Connect to the source database as a system administrator in SQL*Plus and archive the current online redo log:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

10. Prepare your environment for database duplication, which includes preparing the destination database as an auxiliary instance for duplication. See Oracle Database Backup and Recovery Advanced User's Guide for instructions.
11. 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 8 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 6. Also, archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 9 archived the redo log containing the until SCN.

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

    The following is an example of an RMAN DUPLICATE command:

    rman
    RMAN> CONNECT TARGET SYS/change_on_install@dpx1.net
    RMAN> CONNECT AUXILIARY SYS/change_on_install@dpx2.net
    RMAN> RUN
          { 
            SET UNTIL SCN 3050191;
            ALLOCATE AUXILIARY CHANNEL dpx2 DEVICE TYPE sbt; 
            DUPLICATE TARGET DATABASE TO dpx2 
            NOFILENAMECHECK
            OPEN RESTRICTED;
          }
    
    

12. 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.NET;
    
    

13. At the destination database, connect as an administrator with SYSDBA privilege in SQL*Plus and run the following procedure:

    Attention: Make sure you are connected to the destination database, not the source database, when you run this procedure because it removes the local 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 may drop these supplemental log groups if necessary.

    See Also: PL/SQL Packages and Types Reference for more information about the REMOVE_STREAMS_CONFIGURATION procedure

14. At the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

    ALTER SYSTEM DISABLE RESTRICTED SESSION;
    
    

15. At the destination database, create the queue specified in Step 4. For example:

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

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    

16. At the destination database, connect as the Streams administrator and configure the Streams environment.

    Attention: Do not start any apply processes at the destination database until you set the global instantiation SCN in Step 17.

    See Also: Oracle Streams Concepts and Administration for information about configuring a Streams administrator

17. 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 11. 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.net source database, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name   =>  'dpx1.net',
        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.

18. At the destination database, you may start any apply processes that you configured.
19. At the source database, enable the propagation you disabled in Step 5:

    BEGIN
      DBMS_AQADM.SCHEDULE_PROPAGATION(
        queue_name  => 'strmadmin.streams_queue',
        destination => 'dpx2.net');
    END;
    /
    

Setting Instantiation SCNs at a Destination Database

An instantiation SCN instructs an apply process at a destination database to apply changes to a database object 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 into 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 may use Data Pump export/import or original 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 or original export/import. 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. If you use original export/import, then set the ROWS parameter to n during export at the source database or import at the destination database, or both. In either case, 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 Streams Environment Using Transportable Tablespaces".
• 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".

  See Also: "Instantiation SCN and Ignore SCN for an Apply Process" "Overview of Instantiation and Streams Replication" "Instantiating Objects in a Streams Replication Environment"

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. Also, you may use either Data Pump export/import or original export/import.

If you use the original Export utility, then set the OBJECT_CONSISTENT export parameter to y. Regardless of whether you use Data Pump export or original export, you may specify a more stringent degree of consistency by using an export parameter such as FLASHBACK_SCN or FLASHBACK_TIME. Also, if you use the original Import utility, then set the STREAMS_INSTANTIATION import parameter to y.

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, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedures 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 during import.

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 SCNs for each prepared table that is imported

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

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.

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:

• SET_TABLE_INSTANTIATION_SCN
• SET_SCHEMA_INSTANTIATION_SCN
• SET_GLOBAL_INSTANTIATION_SCN

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.

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

Table 8-1 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

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 example, the database link is hrdb2.net. The following example sets the instantiation SCN for the hr.departments table at the hrdb2.net database to the current SCN by running the following procedure at the source database hrdb1.net:

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.NET(
    source_object_name    => 'hr.departments',
    source_database_name  => 'hrdb1.net',
    instantiation_scn     => iscn);
END;
/

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

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.NET(
    source_schema_name    => 'oe',
    source_database_name  => 'hrdb1.net',
    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.

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 example, the database link is hrdb1.net. The following example sets the instantiation SCN for the hr.departments table at the hrdb2.net database to the current source database SCN at hrdb1.net by running the following procedure at the destination database hrdb2.net:

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

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

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@HRDB1.NET;
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
    source_schema_name    => 'oe',
    source_database_name  => 'hrdb1.net',
    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.