Skip Headers
Oracle® Streams Concepts and Administration
10g Release 2 (10.2)

Part Number B14229-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

B Online Database Upgrade with Streams

This appendix describes how to perform a database upgrade of an Oracle Database with Oracle Streams. The database upgrade operation described in this appendix uses the features of Oracle Streams to achieve little or no database down time. To use Streams for a database upgrade, the database must be Oracle9i Database Release 2 (9.2) or later.

This appendix contains these topics:

See Also:

Appendix C, "Online Database Maintenance with Streams" for information about performing other database maintenance operations with Streams

Overview of Using Streams in the Database Upgrade Process

An Oracle database upgrade is the process of transforming an existing, prior release of an Oracle Database system into the current release of the Oracle Database system. A database upgrade typically requires substantial database down time, but you can perform a database upgrade with little or no down time by using the features of Oracle Streams. To do so, you use Oracle Streams to configure a single-source replication environment with the following databases:

Specifically, you can use the following general steps to perform a database upgrade while the database is online:

  1. Create an empty destination database.

  2. Configure an Oracle Streams single-source replication environment where the original database is the source database and a copy of the database is the destination database for the changes made at the source.

  3. Perform the database upgrade on the destination database. During this time the original source database is available online.

  4. Use Oracle Streams to apply the changes made at the source database to the destination database.

  5. When the destination database has caught up with the changes made at the source database, take the source database offline and make the destination database available for applications and users.

Figure B-1 provides an overview of this process.

Figure B-1 Online Database Upgrade with Streams

Description of Figure B-1 follows
Description of "Figure B-1 Online Database Upgrade with Streams"

The Capture Database During the Upgrade Process

During the upgrade process, the capture database is the database where the capture process is created. Downstream capture was introduced in Oracle Database 10g Release 1 (10.1). If you are upgrading a database from Oracle Database 10g Release 1 to Oracle Database 10g Release 2 (10.2), then you have the following options:

  • A local capture process can be created at the source database during the upgrade process.

  • A downstream capture process can be created at the destination database. If the destination database is the capture database, then a propagation from the capture database to the destination database is not needed.

  • A third database can be the capture database. In this case, the third database can be Oracle Database 10g Release 1 or Oracle Database 10g Release 2.

However, if you are upgrading a database from Oracle9i Database Release 2 (9.2) to Oracle Database 10g Release 2, then downstream capture is not supported, and a local capture process must be created at the source database.

A downstream capture process reduces the resources required at the source database during the upgrade process, but a local capture process is easier to configure. Table B-1 describes which database can be the capture database during the upgrade process.

Table B-1 Supported Capture Database During Upgrade

Existing Database Release Capture Database Can Be Source Database? Capture Database Can Be Destination Database? Capture Database Can Be Third Database?

9.2

Yes

No

No

10.1

Yes

Yes

Yes


Note:

If you are upgrading from Oracle Database 10g Release 1, then, before you begin the upgrade, decide which database will be the capture database.

Assumptions for the Database Being Upgraded

The instructions in this appendix assume that all of the following statements are true for the database being upgraded:

  • The database is not part of an existing Oracle Streams environment.

  • The database is not part of an existing logical standby environment.

  • The database is not part of an existing Advanced Replication environment.

  • No tables at the database are master tables for materialized views in other databases.

  • Any user-created queues are read-only during the upgrade process.

Considerations for Job Queue Processes and PL/SQL Package Subprograms

If possible, ensure that no job queue processes are created, modified, or deleted during the upgrade process, and that no Oracle-supplied PL/SQL package subprograms are invoked during the upgrade process that modify both user data and dictionary metadata at the same time. The following packages contain subprograms that modify both user data and dictionary metadata at the same time: DBMS_RLS, DBMS_STATS, and DBMS_JOB.

It might be possible to perform such actions on the database if you ensure that the same actions are performed on the source database and destination database in Steps 9 and 10 in "Task 5: Finishing the Upgrade and Removing Streams". For example, if a PL/SQL procedure gathers statistics on the source database during the upgrade process, then the same PL/SQL procedure should be invoked at the destination database in Step 10.

Preparing for a Database Upgrade Using Streams

The following sections describe tasks to complete before starting the database upgrade with Streams:

Preparing to Upgrade a Database with User-defined Types

User-defined types include object types, REF values, varrays, and nested tables. Currently, Streams capture processes and apply processes do not support user-defined types. This section discusses using Streams to perform a database upgrade on a database that has user-defined types.

One option is to make tables that contain user-defined types read-only during the database upgrade. In this case, these tables are instantiated at the destination database, and no changes are made to these tables during the entire operation. After the upgrade is complete, make the tables that contain user-defined types read/write at the destination database.

If tables that contain user-defined types must remain open during the upgrade, then the following general steps can be used to retain changes to these tables during the upgrade:

  1. Before you begin the upgrade process described in "Performing a Database Upgrade Using Streams", create one or more logging tables to store row changes to tables at the source database that include user-defined types. Each column in the logging table must use a datatype that is supported by Streams in the source database release.

  2. Before you begin the upgrade process described in "Performing a Database Upgrade Using Streams", create a DML trigger at the source database that fires on the tables that contain the user-defined datatypes. The trigger converts each row change into relational equivalents and logs the modified row in a logging table created in Step 1.

  3. When the instructions in "Performing a Database Upgrade Using Streams" say to configure a capture process and propagation, configure the capture process and propagation to capture changes to the logging table and propagate these changes to the destination database. Changes to tables that contain user-defined types should not be captured or propagated.

  4. When the instructions in "Performing a Database Upgrade Using Streams" say to configure a an apply process on the destination database, configure the apply process to use a DML handler that processes the changes to the logging tables. The DML handler reconstructs the user-defined types from the relational equivalents and applies the modified changes to the tables that contain user-defined types.

See Also:

Deciding Which Utility to Use for Instantiation

Before you begin the database upgrade, decide whether you want to use the Export/Import utilities (Data Pump or original) or the Recovery Manager (RMAN) utility to instantiate the destination database during the operation. The destination database will replace the existing database that is being upgraded.

Consider the following factors when you make this decision:

  • If you use original Export/Import or Data Pump Export/Import, then you can make the destination database an Oracle Database 10g Release 2 (10.2) database at the beginning of the operation. Therefore, you do not need to upgrade the destination database after the instantiation.

    If you use Export/Import for instantiation, and Data Pump is supported, then Oracle recommends using Data Pump. Data Pump can perform the instantiation faster than original Export/Import.

  • If you use the RMAN DUPLICATE command, then the instantiation might be faster than with Export/Import, especially if the database is large, but the database release must be the same for RMAN instantiation. Therefore, if the database is an Oracle9i Database Release 2 (9.2) database, then the destination database is an Oracle9i Database Release 2 database when it is instantiated. If the database is an Oracle Database 10g Release 1 (10.1) database, then the destination database is an Oracle Database 10g Release 1 database when it is instantiated. After the instantiation, you must upgrade the destination database.

    Also, Oracle recommends that you do not use RMAN for instantiation in an environment where distributed transactions are possible. Doing so might cause in-doubt transactions that must be corrected manually.

Table B-2 describes whether each instantiation method is supported based on the release being upgraded, whether the platform at the source and destination databases are different, and whether the character set at the source and destination databases are different. Each instantiation method is supported when the platform and character set are the same at the source and destination databases.

Table B-2 Instantiation Methods for Database Upgrade with Streams

Instantiation Method Supported When Upgrading From Different Platforms Supported? Different Character Sets Supported?

Original Export/Import

9.2 or 10.1

Yes

Yes

Data Pump Export/Import

10.1 only

Yes

Yes

RMAN DUPLICATE

9.2 or 10.1

No

No


Performing a Database Upgrade Using Streams

This section contains instructions for performing a database upgrade using Streams. To use Streams for a database upgrade, the database must be Oracle9i Database Release 2 (9.2) or later.

Complete the following tasks to upgrade a database using Streams:

Task 1: Beginning the Upgrade

Complete the following steps to begin the upgrade using Oracle Streams:

  1. Create an empty database. Make sure the destination database has a different global name than the source database. This example assumes that the global name of the source database is orcl.net and the global name of the destination database during the upgrade is updb.net. The global name of the destination database is changed when the destination database replaces the source database at the end of the upgrade process.

    The release of the empty database you create depends on the instantiation method you decided to use in "Deciding Which Utility to Use for Instantiation":

    • If you decided to use export/import for instantiation, then create an empty Oracle Database 10g Release 2 database. This database will be the destination database during the upgrade process.

      See the Oracle Database installation guide for your operating system if you need to install Oracle Database, and see the Oracle Database Administrator's Guide for information about creating a database.

    • If you decided to use RMAN for instantiation, then create an empty Oracle database that is the same release as the database you are upgrading.

      Specifically, if you are upgrading an Oracle9i Database Release 2 (9.2) database, then create an Oracle9i Release 2 database. Alternatively, if you are upgrading an Oracle Database 10g Release 1 (10.1) database, then create an Oracle Database 10g Release 1 database.

      This database will be the destination database during the upgrade process. Both the source database that is being upgraded and the destination database must be the same release of Oracle when you start the upgrade process.

      See the Oracle installation guide for your operating system if you need to install Oracle, and see Database Administrator's Guide for the release for information about creating a database.

  2. Make sure the source database is running in ARCHIVELOG mode. See the Oracle Administrator's Guide for the source database release for information about running a database in ARCHIVELOG mode.

  3. Make sure the initialization parameters are set properly at each database to support a Streams environment. For the source database, see the Oracle Streams documentation for the source database release. For the destination database, see "Setting Initialization Parameters Relevant to Streams". If the capture database is a third database, then see the Oracle Streams documentation for the capture database release.

  4. At the source database, make read-only any database objects that were not supported by Oracle Streams in the release you are upgrading:

    • If you are upgrading an Oracle9i Database Release 2 (9.2) database, then make tables with columns of the following datatypes read-only: NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID, and user-defined types (including object types, REFs, varrays, and nested tables). In addition, make the following types of tables read-only: temporary tables, index-organized tables, and object tables. See Oracle9i Streams for complete information about unsupported database objects.

    • If you are upgrading an Oracle Database 10g Release 1 (10.1) database, then query the DBA_STREAMS_UNSUPPORTED data dictionary view to list the database objects that are not supported by Streams. Make each of the listed database objects read-only.

    "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. If you are using this method, then tables that contain user-defined types can remain open during the upgrade.

  5. At the source database, configure a Streams administrator:

    • If you are upgrading an Oracle9i Database Release 2 (9.2) database, then see Oracle9i Streams for instructions.

    • If you are upgrading an Oracle Database 10g Release 1 (10.1) database, then see the Oracle Streams Concepts and Administration book for that release for instructions.

    These instructions assume that the name of the Streams administrator at the source database is strmadmin. This Streams administrator will be copied automatically to the destination database during instantiation.

  6. Connect as an administrative user in SQL*Plus to the source database, and specify database supplemental logging of primary keys, unique keys, and foreign keys for all updates. For example:

    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 
       (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS; 
    
    

Task 2: Setting Up Streams Prior to Instantiation

The specific instructions for setting up Streams prior to instantiation depend on which database is the capture database. Follow the instructions in the appropriate section:

See Also:

"Overview of Using Streams in the Database Upgrade Process" for information about the capture database

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

    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:

    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:

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

Task 3: Instantiating the Database

"Deciding Which Utility to Use for Instantiation" discusses different options for instantiating an entire database. Complete the steps in the appropriate section based on the instantiation option you are using:

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:

Note:

These steps provide a general outline for using RMAN to duplicate a database. If you are upgrading an Oracle9i Release 2 database, then see the Oracle9i Recovery Manager User's Guide for detailed information about using RMAN in that release. If you upgrading an Oracle Database 10g Release 1 database, then see the Oracle Database Backup and Recovery Advanced User's Guide for that release.
  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".

Task 4: Setting Up Streams After Instantiation

The specific instructions for setting up Streams after instantiation depend on which database is the capture database. Follow the instructions in the appropriate section:

See Also:

"Overview of Using Streams in the Database Upgrade Process" for information about the capture database

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

Task 5: Finishing the Upgrade and Removing Streams

Complete the following steps to finish the upgrade operation using Oracle Streams and remove Streams components:

  1. Connect as the Streams administrator in SQL*Plus to the destination database, and start the apply process. For example:

    CONNECT strmadmin/strmadminpw@updb.net
    
    BEGIN
      DBMS_APPLY_ADM.START_APPLY(
        apply_name  => 'apply_upgrade');
    END;
    /
    
    
  2. Connect as the Streams administrator in SQL*Plus to the capture database, and start the capture process. For example:

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

    This step begins the process of replicating changes that were made to the source database during instantiation of the destination database.

  3. While connected as the Streams administrator in SQL*Plus to the capture database, monitor the Streams environment until the apply process at the destination database has applied most of the changes from the source database. For example, if the name of the capture process is capture_upgrade, and the name of the apply process is apply_upgrade, then run the following query at the source database:

    COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Captured SCN' FORMAT 99999999999
    COLUMN LWM_MESSAGE_NUMBER HEADING 'Applied SCN' FORMAT 99999999999
    
    SELECT c.ENQUEUE_MESSAGE_NUMBER, a.LWM_MESSAGE_NUMBER
      FROM V$STREAMS_CAPTURE c, V$STREAMS_APPLY_COORDINATOR@updb.net a
      WHERE CAPTURE_NAME = 'CAPTURE_UPGRADE'
        AND APPLY_NAME   = 'APPLY_UPGRADE';
    
    

    When the two SCN values returned by this query are nearly equal, most of the changes from the source database have been applied at the destination database, and you can proceed to the next step. At this point in the process, the values returned by this query might never be equal because the source database still allows changes.

    If this query returns no results, then make sure the Streams clients in the environment are enabled by querying the STATUS column in the DBA_CAPTURE view at the capture database and the DBA_APPLY view at the destination database. If a propagation is used, you can check the status of the propagation by running the query in "Displaying the Schedule for a Propagation Job".

    If a Streams client is disabled, then try restarting it. If a Streams client will not restart, then troubleshoot the environment using the information in Chapter 18, "Troubleshooting a Streams Environment".

  4. Connect as the Streams administrator in SQL*Plus to the destination database, and make sure there are no apply errors by running the following query:

    CONNECT strmadmin/strmadminpw@updb.net
    
    SELECT COUNT(*) FROM DBA_APPLY_ERROR;
    
    

    If this query returns zero, then proceed to the next step. If this query shows errors in the error queue, then resolve these errors before continuing. See "Managing Apply Errors" for instructions.

  5. Disconnect all applications and users from the source database.

  6. Connect as an administrative user in SQL*Plus to the source database, and restrict access to the database. For example:

    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    
    
  7. Connect as an administrative user in SQL*Plus to the capture database, and repeat the query you ran in Step 3. When the two SCN values returned by the query are equal, all of the changes from the source database have been applied at the destination database, and you can proceed to the next step.

  8. Connect as the Streams administrator in SQL*Plus to the destination database, and repeat the query you ran in Step 4. If this query returns zero, then move on to the next step. If this query shows errors in the error queue, then resolve these errors before continuing. See "Managing Apply Errors" for instructions.

  9. If you performed any actions that created, modified, or deleted job queue processes at the source database during the upgrade process, then perform the same actions at the destination database. See "Considerations for Job Queue Processes and PL/SQL Package Subprograms" for more information.

  10. If you invoked any Oracle-supplied PL/SQL package subprograms at the source database during the upgrade process that modified both user data and dictionary metadata at the same time, then invoke the same subprograms at the destination database. See "Considerations for Job Queue Processes and PL/SQL Package Subprograms" for more information.

  11. Shut down the source database. This database should not be opened again.

  12. While connected as an administrative user in SQL*Plus to the destination database, change the global name of the database to match the source database. For example:

    ALTER DATABASE RENAME GLOBAL_NAME TO orcl.net;
    
    
  13. At the destination database, enable any jobs that you disabled earlier.

  14. Make the destination database available for applications and users. Redirect any applications and users that were connecting to the source database to the destination database. If necessary, reconfigure your network and Oracle Net so that systems that communicated with the source database now communicate with the destination database. See Oracle Database Net Services Administrator's Guide for instructions.

  15. At the destination database, remove the Streams components that are no longer needed. Connect as an administrator with SYSDBA privilege to the destination database, and run the following procedure:

    Note:

    Running this procedure is dangerous. It removes the local Streams configuration. Make sure you are ready to remove the Streams configuration at the destination database before running this procedure.
    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    
    

    If you no longer need database supplemental logging at the destination database, then run the following statement to drop it:

    ALTER DATABASE DROP SUPPLEMENTAL LOG DATA 
      (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
    
    

    If you no longer need the Streams administrator at the destination database, then run the following statement:

    DROP USER strmadmin CASCADE;
    
    
  16. If the capture database was a third database, then, at the third database, remove the Streams components that are no longer needed. Connect as an administrator with SYSDBA privilege to the third database, and run the following procedure:

    Note:

    Running this procedure is dangerous. It removes the local Streams configuration. Make sure you are ready to remove the Streams configuration at the third database before running this procedure.
    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    
    

    If you no longer need database supplemental logging at the third database, then run the following statement to drop it:

    ALTER DATABASE DROP SUPPLEMENTAL LOG DATA 
      (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
    
    

    If you no longer need the Streams administrator at the destination database, then run the following statement:

    DROP USER strmadmin CASCADE;
    
    

The database upgrade is complete.