E Online Upgrade of a 10.1 or Earlier Database with Oracle Streams

The Source Database Is the Capture Database

Complete the following steps to set up Oracle Streams before instantiation when the source database is the capture database:

1. Configure your network and Oracle Net so that the source database can communicate with the destination database. See Oracle Database Net Services Administrator's Guide for instructions.

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

   Seethe Oracle Database Administrator's Guide for the source database release for information about connecting to a database in SQL*Plus.

3. Create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.capture_queue_table',
       queue_name  => 'strmadmin.capture_queue');
   END;
   /
   

4. Configure a capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 3. Do not start the capture process. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'capture',
       streams_name       => 'capture_upgrade',
       queue_name         => 'strmadmin.capture_queue',
       include_dml        => TRUE,
       include_ddl        => TRUE,
       include_tagged_lcr => FALSE,
       source_database    => 'orcl.example.com',
       inclusion_rule     => TRUE);
   END;
   /
   

   "Preparing to Upgrade a Database with User-Defined Types" discusses a method for retaining changes to tables that contain user-defined types during the maintenance operation. If you are using this method, then ensure that the capture process does not attempt to capture changes to tables with user-defined types. See the Oracle Streams documentation for the source database release for information about excluding database objects from an Oracle Streams configuration with rules.

5. Proceed to "Task 3: Instantiating the Database".

The Destination Database Is the Capture Database

The database being upgraded must be an Oracle Database 10g Release 1 (10.1) database to use this option. Complete the following steps to set up Oracle Streams before instantiation when the destination database is the capture database:

1. Configure your network and Oracle Net so that the source database and destination database can communicate with each other. See Oracle Database Net Services Administrator's Guide for instructions.

2. Follow the instructions in the appropriate section based on the method you are using for instantiation:

   • Export/Import

   • RMAN

   Export/Import

   Complete the following steps if you are using export/import for instantiation:

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

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

   2. Create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

      BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE(
          queue_table => 'strmadmin.destination_queue_table',
          queue_name  => 'strmadmin.destination_queue');
      END;
      /
      

   3. Configure a downstream capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step b. Ensure that the capture process uses a database link to the source database. The capture process can be a real-time downstream capture process or an archived-log downstream capture process. See Oracle Streams Replication Administrator's Guide for instructions. Do not start the capture process.

      "Preparing to Upgrade a Database with User-Defined Types" discusses a method for retaining changes to tables that contain user-defined types during the maintenance operation. If you are using this method, then ensure that the capture process does not attempt to capture changes to tables with user-defined types. See the Oracle Streams documentation for the source database for information about excluding database objects from an Oracle Streams configuration with rules.

   RMAN

   Complete the following steps if you are using RMAN for instantiation:

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

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

   2. Perform a build of the data dictionary in the redo log:

      SET SERVEROUTPUT ON
      DECLARE
        scn  NUMBER;
      BEGIN
        DBMS_CAPTURE_ADM.BUILD(
          first_scn => scn);
        DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
      END;
      /
      First SCN Value = 1122610
      

      This procedure displays the valid first SCN value for the capture process that will be created at the destination database. Make a note of the SCN value returned because you will use it when you create the capture process at the destination database.

   3. Prepare the source database for instantiation:

      exec DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION();
      

3. Proceed to "Task 3: Instantiating the Database".

A Third Database Is the Capture Database

To use this option, meet the following requirements:

• The database being upgraded must be an Oracle Database 10g Release 1 (10.1) database.

• The third database must be an Oracle Database 10g Release 1 or later database.

This example assumes that the global name of the third database is thrd.example.com. Complete the following steps to set up Oracle Streams before instantiation when a third database is the capture database:

1. Configure your network and Oracle Net so that the source database, destination database, and third database can communicate with each other. See Oracle Database Net Services Administrator's Guide for instructions.

2. In SQL*Plus, connect to the third database thrd.example.com as an administrative user.

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

3. Create an Oracle Streams administrator:

   • If the third database is an Oracle Database 10g database or an Oracle Database 11g Release 1 database, then see the Oracle Streams Concepts and Administration book for that release for instructions.

   • If the third database is an Oracle Database 11g Release 2 database, then see Oracle Streams Replication Administrator's Guide for instructions.

   These instructions assume that the name of the Oracle Streams administrator at the third database is strmadmin.

4. While still connected to the third database as the Oracle Streams administrator, create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.capture_queue_table',
       queue_name  => 'strmadmin.capture_queue');
   END;
   /
   

5. Configure a downstream capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 4. Ensure that the capture process uses a database link to the source database. Do not start the capture process.

   See the following documentation for instructions:

   • If the capture database is an Oracle Database 10g database or an Oracle Database 11g Release 1 database, then see the Oracle Streams Concepts and Administration book for that release for instructions.

   • If the capture database is an Oracle Database 11g Release 2 database, then see Oracle Streams Replication Administrator's Guide.

   The capture process can be a real-time downstream capture process or an archived-log downstream capture process.

   "Preparing to Upgrade a Database with User-Defined Types" discusses a method for retaining changes to tables that contain user-defined types during the upgrade operation. If you are using this method, then ensure that the capture process does not attempt to capture changes to tables with user-defined types. See the Oracle Streams documentation for the source database for information about excluding database objects from an Oracle Streams configuration with rules.

6. Proceed to "Task 3: Instantiating the Database".

Instantiating the Database Using Export/Import

Complete the following steps to instantiate the destination database using export/import:

1. Instantiate the destination database using Export/Import. See Oracle Streams Replication Administrator's Guide for more information about performing instantiations, and see Oracle Database Utilities for information about performing an export/import using the Export and Import utilities.

   If you use Oracle Data Pump or original Export/Import to instantiate the destination database, then ensure that the following parameters are set to the appropriate values:

   • Set the STREAMS_CONFIGURATION import parameter to n.

   • If you use original Export/Import, then set the CONSISTENT export parameter to y. This parameter does not apply to Data Pump exports.

   • If you use original Export/Import, then set the STREAMS_INSTANTIATION import parameter to y. This parameter does not apply to Data Pump imports.

   If you are upgrading an Oracle9i Database Release 2 (9.2) database, then you must use original Export/Import.

2. At the destination database, disable any imported jobs that modify data that will be replicated from the source database. Query the DBA_JOBS data dictionary view to list the jobs.

3. Proceed to "Task 4: Setting Up Oracle Streams After Instantiation".

Instantiating the Database Using RMAN

Complete the following steps to instantiate the destination database using the RMAN DUPLICATE command:

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 orcl.example.com if one does not exist.

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

   Seethe Oracle Database Administrator's Guide for the source database release for information about connecting to a database in SQL*Plus.

3. Determine the until SCN for the RMAN DUPLICATE command. For example:

   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 value. This example assumes that the until SCN value is 439882. You will set the UNTIL SCN option to this value when you use RMAN to duplicate the database in Step 7.

4. While still connected as an administrative user in SQL*Plus to the source database, archive the current online redo log. For example:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   

5. Prepare your environment for database duplication, which includes preparing the destination database as an auxiliary instance for duplication. See the documentation for the release from which you are upgrading for instructions. Specifically, see the "Duplicating a Database with Recovery Manager" chapter in the Oracle9i Recovery Manager User's Guide or Oracle Database Backup and Recovery Advanced User's Guide (10g) for instructions.

6. Start the RMAN client, and connect to the database orcl.example.com as TARGET and to the updb.example.com database as AUXILIARY. Connect to each database as an administrative user.

   See the RMAN documentation for your Oracle Database release for more information about the RMAN CONNECT command.

7. 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 3 for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 4 archived the redo log containing the until SCN.

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

   The following is an example of an RMAN DUPLICATE command:

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

8. In SQL*Plus, connect to the destination database as an administrative user.

9. Use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

   ALTER SYSTEM DISABLE RESTRICTED SESSION;
   

10. While still connected as an administrative user in SQL*Plus to the destination database, rename the database global name. After the RMAN DUPLICATE command, the destination database has the same global name as the source database, but the destination database must have its original name until the end of the upgrade. For example:

    ALTER DATABASE RENAME GLOBAL_NAME TO updb.example.com;
    

11. At the destination database, disable any jobs that modify data that will be replicated from the source database. Query the DBA_JOBS data dictionary view to list the jobs.

12. Upgrade the destination database to Oracle Database 11g Release 2. See the Oracle Database Upgrade Guide for instructions.

13. If you have not done so already, configure your network and Oracle Net so that the source database and destination database can communicate with each other. See Oracle Database Net Services Administrator's Guide for instructions.

14. Connect to the destination database as the Oracle Streams administrator in SQL*Plus. In this example, the destination database is updb.example.com.

15. Create a database link to the source database. For example:

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

16. Set the instantiation SCN for the entire database and all of the database objects. 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 7. In this example, the until SCN was set to 439882. Therefore, the instantiation SCN should be set to 439882 - 1, or 439881.

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

17. Proceed to "Task 4: Setting Up Oracle Streams After Instantiation".

The Source Database Is the Capture Database

Complete the following steps to set up Oracle Streams after instantiation when the source database is the capture database:

1. In SQL*Plus, connect to the destination database as the Oracle Streams administrator. In this example, the destination database is updb.example.com.

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

2. Remove the Oracle Streams components that were cloned from the source database during instantiation:

   • If export/import was used for instantiation, then remove the ANYDATA queue that was cloned from the source database.

   • If RMAN was used for instantiation, then remove the ANYDATA queue and the capture process that were cloned from the source database.

   To remove the queue that was cloned from the source database, run the REMOVE_QUEUE procedure in the DBMS_STREAMS_ADM package. For example:

   BEGIN
     DBMS_STREAMS_ADM.REMOVE_QUEUE(
       queue_name              => 'strmadmin.capture_queue',
       cascade                 => FALSE,
       drop_unused_queue_table => TRUE);
   END;
   /
   

   To remove the capture process that was cloned from the source database, run the DROP_CAPTURE procedure in the DBMS_CAPTURE_ADM package. For example:

   BEGIN
     DBMS_CAPTURE_ADM.DROP_CAPTURE(
       capture_name          => 'capture_upgrade',
       drop_unused_rule_sets => TRUE);
   END;
   /
   

3. Create an ANYDATA queue. This queue will stage changes propagated from the source database. For example:

   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.destination_queue_table',
       queue_name  => 'strmadmin.destination_queue');
   END;
   /
   

4. Connect to the source database as the Oracle Streams administrator. In this example, the source database is orcl.example.com.

5. Create a database link to the destination database. For example:

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

6. Create a propagation that propagates all changes from the source queue to the destination database created in Step 3. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
       streams_name            => 'to_updb',
       source_queue_name       => 'strmadmin.capture_queue',
       destination_queue_name  => 'strmadmin.destination_queue@updb.example.com', 
       include_dml             => TRUE,
       include_ddl             => TRUE,
       include_tagged_lcr      => TRUE,
       source_database         => 'orcl.example.com');
   END;
   /
   

7. Connect to the destination database as the Oracle Streams administrator.

8. Create an apply process that applies all changes in the queue created in Step 3. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'apply',
       streams_name       => 'apply_upgrade',
       queue_name         => 'strmadmin.destination_queue',
       include_dml        => TRUE,
       include_ddl        => TRUE,
       include_tagged_lcr => TRUE,
       source_database    => 'orcl.example.com');
   END;
   /
   

9. Proceed to "Task 5: Finishing the Upgrade and Removing Oracle Streams".

The Destination Database Is the Capture Database

Complete the following steps to set up Oracle Streams after instantiation when the destination database is the capture database:

1. Complete the following steps if you used RMAN for instantiation. If you used export/import for instantiation, then proceed to Step 2.

   1. In SQL*Plus, connect to the destination database as the Oracle Streams administrator. In this example, the destination database is updb.example.com.

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

   2. Create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

      BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE(
          queue_table => 'strmadmin.destination_queue_table',
          queue_name  => 'strmadmin.destination_queue');
      END;
      /
      

   3. Configure a downstream capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step b.

      Ensure that you set the first_scn parameter in the CREATE_CAPTURE procedure to the value obtained for the data dictionary build in Step 2b in "The Destination Database Is the Capture Database". In this example, the first_scn parameter should be set to 1122610.

      The capture process can be a real-time downstream capture process or an archived-log downstream capture process. See Oracle Streams Replication Administrator's Guide for instructions. Do not start the capture process.

      "Preparing to Upgrade a Database with User-Defined Types" discusses a method for retaining changes to tables that contain user-defined types during the maintenance operation. If you are using this method, then ensure that the capture process does not attempt to capture changes to tables with user-defined types. See the Oracle Streams documentation for the source database for information about excluding database objects from an Oracle Streams configuration with rules.

2. Create an apply process that applies all changes in the queue used by the downstream capture process. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'apply',
       streams_name       => 'apply_upgrade',
       queue_name         => 'strmadmin.destination_queue',
       include_dml        => TRUE,
       include_ddl        => TRUE,
       include_tagged_lcr => TRUE,
       source_database    => 'orcl.example.com');
   END;
   /
   

3. Proceed to "Task 5: Finishing the Upgrade and Removing Oracle Streams".

A Third Database Is the Capture Database

This example assumes that the global name of the third database is thrd.example.com. Complete the following steps to set up Oracle Streams after instantiation when a third database is the capture database:

1. In SQL*Plus, connect to the destination database as the Oracle Streams administrator. In this example, the destination database is updb.example.com.

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

2. Create an ANYDATA queue. This queue will stage changes propagated from the capture database. For example:

   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.destination_queue_table',
       queue_name  => 'strmadmin.destination_queue');
   END;
   /
   

3. Connect to the capture database as the Oracle Streams administrator. In this example, the capture database is thrd.example.com.

4. Create a database link to the destination database. For example:

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

5. Create a propagation that propagates all changes from the source queue at the capture database to the destination queue created in Step 2. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
       streams_name            => 'to_updb',
       source_queue_name       => 'strmadmin.capture_queue',
       destination_queue_name  => 'strmadmin.destination_queue@updb.example.com', 
       include_dml             => TRUE,
       include_ddl             => TRUE,
       include_tagged_lcr      => TRUE,
       source_database         => 'orcl.example.com');
   END;
   /
   

6. Connect to the destination database as the Oracle Streams administrator. In this example, the destination database is updb.example.com.

7. Create an apply process that applies all changes in the queue created in Step 2. For example:

   BEGIN
     DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'apply',
       streams_name       => 'apply_upgrade',
       queue_name         => 'strmadmin.destination_queue',
       include_dml        => TRUE,
       include_ddl        => TRUE,
       include_tagged_lcr => TRUE,
       source_database    => 'orcl.example.com');
   END;
   /
   

8. Complete the steps in "Task 5: Finishing the Upgrade and Removing Oracle Streams".