8 Instantiation and Oracle Streams Replication

Preparing Tables for Instantiation

This section contains these topics:

• Preparing a Table for Instantiation Using DBMS_STREAMS_ADM When a Capture Process Is Used

• Preparing a Table for Instantiation Using DBMS_CAPTURE_ADM When a Capture Process Is Used

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

• Preparing Tables for Instantiation Using DBMS_CAPTURE_ADM When a Synchronous Capture Is Used

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

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

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

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

Preparing the Database Objects in a Schema for Instantiation

This section contains these topics:

• Preparing the Database Objects in a Schema for Instantiation Using DBMS_STREAMS_ADM

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

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

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

Preparing All of the Database Objects in a Database for Instantiation

This section contains these topics:

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

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

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

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

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.

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.

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

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.

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;
    

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.

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.

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.

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 Step 14. 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:

    Caution:

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

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.

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:

    Caution:

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

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

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

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.