B Online Database Upgrade with Streams

The Source Database Is the Capture Database

Complete the following steps to set up Streams prior to 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. Connect as the Streams administrator in SQL*Plus to the source database, and create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

   CONNECT strmadmin/strmadminpw@orcl.net
   
   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.capture_queue_table',
       queue_name  => 'strmadmin.capture_queue');
   END;
   /
   
   

3. While still as the Streams administrator to the source database, 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 2. 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.net',
       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 make sure the capture process does not attempt to capture changes to tables with user-defined types. See the Streams documentation for the source database for information about excluding database objects from a Streams configuration with rules.

4. 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 database to use this option. Complete the following steps to set up Streams prior to 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. Connect to the destination database as an administrative user, and create a Streams administrator. See "Configuring a Streams Administrator" for instructions.

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

3. 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. Connect as the Streams administrator in SQL*Plus to the destination database, and create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

      CONNECT strmadmin/strmadminpw@updb.net
      
      BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE(
          queue_table => 'strmadmin.destination_queue_table',
          queue_name  => 'strmadmin.destination_queue');
      END;
      /
      
      

   2. While still as the Streams administrator to the destination database, 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 a. Make sure 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 "Creating a Capture Process". 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 make sure the capture process does not attempt to capture changes to tables with user-defined types. See the Streams documentation for the source database for information about excluding database objects from a Streams configuration with rules.

   RMAN

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

   1. Connect as the Streams administrator in SQL*Plus to the source database, and perform a build of the data dictionary in the redo log:

      CONNECT strmadmin/strmadminpw@orcl.net
      
      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.

   2. While still as the Streams administrator to the source database, prepare the source database for instantiation:

      exec DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION();
      
      

4. 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 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.net. Complete the following steps to set up Streams prior to 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. Connect to the third database as an administrative user, and create a Streams administrator:

   • If the third database is an Oracle Database 10g 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 10g Release 2 database, then see "Configuring a Streams Administrator" for instructions.

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

3. Connect as the Streams administrator in SQL*Plus to the third database, and create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

   CONNECT strmadmin/strmadminpw@thrd.net
   
   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.capture_queue_table',
       queue_name  => 'strmadmin.capture_queue');
   END;
   /
   
   

4. While still connected as the Streams administrator to the third database, 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 3. Make sure 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 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 10g Release 2 database, then see "Creating a Capture Process" for instructions. 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 maintenance operation. If you are using this method, then make sure the capture process does not attempt to capture changes to tables with user-defined types. See the Streams documentation for the source database for information about excluding database objects from a Streams configuration with rules.

5. 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 make sure 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 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.net if one does not exist.

2. While connected as an administrative user in SQL*Plus to the source database, determine the until SCN for the RMAN DUPLICATE command. For example:

   CONNECT SYSTEM/MANAGER@orcl.net 
   
   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 5.

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

   CONNECT SYSTEM/MANAGER@orcl.net 
   
   ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

4. 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 (10.1) for instructions.

5. 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 2 for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 3 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 updb.net. Therefore, the DUPLICATE command for this example includes TO updb.net.

   The following example is an RMAN DUPLICATE command:

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

6. While connected as an administrative user in SQL*Plus to the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

   CONNECT SYSTEM/MANAGER 
   
   ALTER SYSTEM DISABLE RESTRICTED SESSION;
   
   

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

   CONNECT SYSTEM/MANAGER 
   
   ALTER DATABASE RENAME GLOBAL_NAME TO updb.net;
   
   

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

9. Upgrade the destination database to Oracle Database 10g Release 2. See the Oracle Database Upgrade Guide for instructions.

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

11. Connect as the Streams administrator in SQL*Plus to the destination database, and create a database link to the source database. For example:

    CONNECT strmadmin/strmadminpw@updb.net
    
    CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'orcl.net';
    
    

12. While connected as the Streams administrator in SQL*Plus to the destination database, 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 5. In this example, the until SCN was set to 439882. Therefore, the instantiation SCN should be set to 439882 - 1, or 439881.

    CONNECT strmadmin/strmadminpw@updb.net
    
    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name => 'orcl.net',
        instantiation_scn    => 439881,
        recursive            => true);
    END;
    /
    
    

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

The Source Database Is the Capture Database

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

1. Connect as the Streams administrator in SQL*Plus to the destination database, and remove the 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:

   CONNECT strmadmin/strmadminpw@updb.net
   
   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:

   CONNECT strmadmin/strmadminpw@updb.net
   
   BEGIN
     DBMS_CAPTURE_ADM.DROP_CAPTURE(
       capture_name          => 'capture_upgrade',
       drop_unused_rule_sets => true);
   END;
   /
   
   

2. While connected as the Streams administrator in SQL*Plus to the destination database, create an ANYDATA queue. This queue will stage changes propagated from the source database. For example:

   CONNECT strmadmin/strmadminpw@updb.net
   
   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.destination_queue_table',
       queue_name  => 'strmadmin.destination_queue');
   END;
   /
   
   

3. Connect as the Streams administrator in SQL*Plus to the source database, and create a database link to the destination database. For example:

   CONNECT strmadmin/strmadminpw@orcl.net
   
   CREATE DATABASE LINK updb.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'updb.net';
   
   

4. While connected as the Streams administrator in SQL*Plus to the source database, create a propagation that propagates all changes from the source queue 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.net', 
       include_dml             => true,
       include_ddl             => true,
       include_tagged_lcr      => true,
       source_database         => 'orcl.net');
   END;
   /
   
   

5. Connect as the Streams administrator in SQL*Plus to destination database, and create an apply process that applies all changes in the queue created in Step 2. For example:

   CONNECT strmadmin/strmadminpw@updb.net
   
   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.net');
   END;
   /
   
   

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

The Destination Database Is the Capture Database

Complete the following steps to set up 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. Connect as the Streams administrator in SQL*Plus to the destination database, and create an ANYDATA queue that will stage changes made to the source database during the upgrade process. For example:

      CONNECT strmadmin/strmadminpw@updb.net
      
      BEGIN
        DBMS_STREAMS_ADM.SET_UP_QUEUE(
          queue_table => 'strmadmin.destination_queue_table',
          queue_name  => 'strmadmin.destination_queue');
      END;
      /
      
      

   2. While still as the Streams administrator to the destination database, 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 a.

      Make sure you set the first_scn parameter in the CREATE_CAPTURE procedure to the value obtained for the data dictionary build in Step 3a 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 "Creating a Capture Process". 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 make sure the capture process does not attempt to capture changes to tables with user-defined types. See the Streams documentation for the source database for information about excluding database objects from a Streams configuration with rules.

2. Connect as the Streams administrator in SQL*Plus to destination database, and create an apply process that applies all changes in the queue used by the downstream capture process. For example:

   CONNECT strmadmin/strmadminpw@updb.net
   
   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.net');
   END;
   /
   
   

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

A Third Database Is the Capture Database

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

1. Connect as the Streams administrator in SQL*Plus to the destination database, and create an ANYDATA queue. This queue will stage changes propagated from the capture database. For example:

   CONNECT strmadmin/strmadminpw@updb.net
   
   BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_table => 'strmadmin.destination_queue_table',
       queue_name  => 'strmadmin.destination_queue');
   END;
   /
   
   

2. Connect as the Streams administrator in SQL*Plus to the capture database, and create a database link to the destination database. For example:

   CONNECT strmadmin/strmadminpw@thrd.net
   
   CREATE DATABASE LINK updb.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
      USING 'updb.net';
   
   

3. While connected as the Streams administrator in SQL*Plus to the capture database, create a propagation that propagates all changes from the source queue at the capture database to the destination queue created in Step 1. 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.net', 
       include_dml             => true,
       include_ddl             => true,
       include_tagged_lcr      => true,
       source_database         => 'orcl.net');
   END;
   /
   
   

4. Connect as the Streams administrator in SQL*Plus to destination database, and create an apply process that applies all changes in the queue created in Step 1. For example:

   CONNECT strmadmin/strmadminpw@updb.net
   
   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.net');
   END;
   /
   
   

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