Skip Headers

Oracle® Streams Concepts and Administration
10g Release 1 (10.1)

Part Number B10727-01
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
Feedback

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

B
Online Database Upgrade and Maintenance With Streams

This appendix describes performing certain maintenance operations on an Oracle database with little or no down time. These maintenance operations include upgrading to a new version of the Oracle Database, migrating an Oracle Database to a different operating system or character set, upgrading user-created applications, and applying Oracle Database patches. The maintenance operations described in this appendix use the features of Oracle Streams to achieve little or no database down time.

This appendix contains these topics:

Overview of Using Streams in the Database Maintenance Process

The following operations typically require substantial database down time:

You can achieve these maintenance operations 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 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.

Specifically, you can use the following general steps to perform the maintenance operation 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 maintenance operation 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.

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

The following sections provide detailed instructions for completing one of the maintenance operations:

Performing a Database Version Upgrade Using Streams

To use Streams for a database version upgrade, the database must be Oracle9i release 2 (9.2). Before you begin the database version upgrade, decide whether you want to use the original Export/Import utilities 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:

After you decide which utility you want to use for instantiation, complete the steps in the appropriate section:

Performing a Database Version Upgrade Using Streams and Original Export/Import

Complete the following steps to perform a database version upgrade using Export/Import and Oracle Streams:

  1. Create an empty Oracle Database 10g database. This database will be the destination database during the upgrade process. It may use a different operating system and character set than the source database that is being upgraded.

    See the Oracle installation guide for your operating system if you need to install Oracle, and see Oracle Database Administrator's Guide for information about creating a 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 stms.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.

  2. At the source database, make any database objects that were not supported by Streams in Oracle9i release 2 (9.2) read-only. In Oracle9i, Streams did not support tables with columns of the following datatypes: NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID, and user-defined types (including object types, REFs, varrays, and nested tables). In addition, Streams did not support temporary tables, index-organized tables, or object tables. See Oracle9i Streams for complete information about unsupported database objects.
  3. At the source database, configure a Streams administrator. See Oracle9i Streams
    for instructions. This example assumes 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.
  4. While connected as an administrative user in SQL*Plus at the source database, specify database supplemental logging of primary keys or unique indexes (in the absence of primary keys) for all updates. For example:
    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) 
    COLUMNS; 
    
    
  5. While connected as the Streams administrator in SQL*Plus at the source database, create a SYS.AnyData queue that will stage changes made to the source database during the upgrade process. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  6. While connected as the Streams administrator in SQL*Plus at the source database, configure a local capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 5. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN 
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'capture',
       streams_name       => 'capture',
       queue_name         => 'streams_queue',
       include_dml        => true,
       include_ddl        => true,
       include_tagged_lcr => true,
       source_database    => NULL);
    END;
    /
    
    

    Do not start the capture process.

  7. Instantiate the destination database using original Export/Import by completing the following steps:
    1. At the source database command line, perform a full database export with the CONSISTENT export parameter set to y:
      exp SYSTEM/password FULL=y FILE=instant.dmp GRANTS=y ROWS=y 
      CONSISTENT=y
      
      
    2. If the source and destination databases are on different computer systems, then transfer the export dump file to the computer system running the destination database.
    3. At the destination database command line in the directory that contains the dump file, perform a full database import with the STREAMS_INSTANTIATION import parameter set to y and the STREAMS_CONFIGURATION import parameters set to n:
      imp SYSTEM/password FULL=y FILE=instant.dmp COMMIT=y 
      LOG=import.log STREAMS_INSTANTIATION=y STREAMS_CONFIGURATION=n
      
      

    See Oracle Database Utilities for information about performing an export/import using the original Export and Import utilities.

  8. 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.
  9. While connected as the Streams administrator in SQL*Plus at the destination database, remove the imported SYS.AnyData queue. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.REMOVE_QUEUE(
        queue_name              => 'strmadmin.streams_queue',
        cascade                 => false,
        drop_unused_queue_table => true);
    END;
    /
    
    
  10. While connected as the Streams administrator in SQL*Plus at the destination database, re-create the SYS.AnyData queue. This queue will stage changes propagated from the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  11. Configure your network and Oracle Net so that the source database can communicate with the destination database. See Oracle Net Services Administrator's Guide for instructions.
  12. While connected as the Streams administrator in SQL*Plus at the source database, create a database link to the destination database. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'stms.net';
    
    
  13. While connected as the Streams administrator in SQL*Plus at the source database, create a propagation that propagates all changes from the source queue created in Step 5 to the destination queue created in Step 10. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'orcl_to_stms',
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@stms.net', 
        include_dml             => true,
        include_ddl             => true,
        include_tagged_lcr      => true,
        source_database         => 'orcl.net');
    END;
    /
    
    
  14. While connected as the Streams administrator in SQL*Plus at the destination database, create an apply process that applies all changes in the queue created in Step 10. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        streams_type       => 'apply',
        streams_name       => 'apply',
        queue_name         => 'strmadmin.streams_queue',
        include_dml        => true,
        include_ddl        => true,
        include_tagged_lcr => true,
        source_database    => 'orcl.net');
    END;
    /
    
    
  15. Complete the steps in "Finishing the Database Maintenance Operation".

Performing a Database Version Upgrade Using Streams and RMAN

Complete the following steps to perform a database version upgrade using Recovery Manager (RMAN) and Oracle Streams:

  1. Create an empty Oracle9i release 2 (9.2) database. This database will be the destination database during the upgrade process. It may use a different operating system and character set than the source database that is being upgraded. Both the source database that is being upgraded and the destination database must be Oracle9i release 2 (9.2) databases when you start the upgrade process.

    See the Oracle installation guide for your operating system if you need to install Oracle, and see Oracle9i Database Administrator's Guide for information about creating a 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.

  2. At the source database, make any database objects that were not supported by Streams in Oracle9i release 2 (9.2) read-only. In Oracle9i release 2 (9.2), Streams did not support tables with columns of the following datatypes: NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID, and user-defined types (including object types, REFs, varrays, and nested tables). In addition, Streams did not support temporary tables, index-organized tables, or object tables. See Oracle9i Streams for complete information about unsupported database objects.
  3. At the source database, configure a Streams administrator. See Oracle9i Streams
    for instructions. This example assumes that the name of the Streams administrator at the source database is strmadmin and that the global name of the source database is orcl.net.
  4. While connected as an administrative user in SQL*Plus at the source database, specify database supplemental logging of primary keys and unique indexes (in the absence of primary keys) for all updates. For example:
    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) 
    COLUMNS; 
    
    
  5. While connected as the Streams administrator in SQL*Plus at the source database, create a SYS.AnyData queue that will stage changes made to the source database during the upgrade process. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  6. While connected as the Streams administrator in SQL*Plus at the source database, configure a local capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 5. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN 
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'capture',
       streams_name       => 'capture',
       queue_name         => 'streams_queue',
       include_dml        => true,
       include_ddl        => true,
       include_tagged_lcr => true,
       source_database    => NULL);
    END;
    /
    
    

    Do not start the capture process.

  7. Instantiate the destination database using RMAN DUPLICATE command by completing the following steps. These steps provide a general outline for using RMAN to duplicate a database. See the Oracle9i Recovery Manager User's Guide for detailed information about using RMAN.
    1. Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of orcl.net if one does not exist.
    2. While connected as an administrative user in SQL*Plus at 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 e.

    3. While connected as an administrative user in SQL*Plus at 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 "Duplicating a Database with Recovery Manager" chapter in the Oracle9i Recovery Manager User's Guide for instructions.
    5. Use the RMAN DUPLICATE command to instantiate the source database at the destination database. You can use the UNTIL SCN clause to specify an SCN for the duplication. Use the until SCN determined in Step b for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step c 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 stms.net. Therefore, the DUPLICATE command for this example includes TO stms.net.

      The following is an example of an RMAN DUPLICATE command:

      rman
      RMAN> CONNECT TARGET SYS/change_on_install@orcl.net
      RMAN> CONNECT AUXILIARY SYS/change_on_install@stms.net
      RMAN> RUN
            { 
              SET UNTIL SCN 439882;
              ALLOCATE AUXILIARY CHANNEL updb DEVICE TYPE sbt; 
              DUPLICATE TARGET DATABASE TO updb 
              NOFILENAMECHECK;
            }
      
      
    6. While connected as an administrative user in SQL*Plus at the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:
      CONNECT SYSTEM/MANAGER 
      
      ALTER SYSTEM DISABLE RESTRICTED SESSION;
      
      
  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. While connected as an administrative user in SQL*Plus at 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. For example:
    CONNECT SYSTEM/MANAGER 
    
    ALTER DATABASE RENAME GLOBAL_NAME TO stms.net;
    
    
  10. Configure your network and Oracle Net so that the source database and the destination database can communicate with each other. See Oracle Net Services Administrator's Guide for instructions.
  11. Upgrade the destination database to Oracle Database 10g. See the Oracle Database Upgrade Guide for instructions.
  12. At the destination database, connect as an administrator with SYSDBA privilege and run the following procedure:


    Attention:

    Make sure you are connected to the destination database, not the source database, when you run this procedure because it removes the local Streams configuration.


    CONNECT SYS/CHANGE_ON_INSTALL@stms.net AS SYSDBA
    
    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    
    

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

  13. While connected as the Streams administrator in SQL*Plus at the destination database, create a database link to the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'orcl.net';
    
    
  14. While connected as the Streams administrator in SQL*Plus at 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 7e. 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@stms.net
    
    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name => 'orcl.net',
        instantiation_scn    => 439881,
        recursive            => true);
    END;
    /
    
    
  15. While connected as the Streams administrator in SQL*Plus at the destination database, remove the imported SYS.AnyData queue. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.REMOVE_QUEUE(
        queue_name              => 'strmadmin.streams_queue',
        cascade                 => false,
        drop_unused_queue_table => true);
    END;
    /
    
    
  16. While connected as the Streams administrator in SQL*Plus at the destination database, re-create the SYS.AnyData queue. This queue will stage changes propagated from the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  17. While connected as the Streams administrator in SQL*Plus at the source database, create a database link to the destination database. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'stms.net';
    
    
  18. While connected as the Streams administrator in SQL*Plus at the source database, create a propagation that propagates all changes from the source queue created in Step 5 to the destination queue created in Step 10. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'orcl_to_stms',
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@stms.net', 
        include_dml             => true,
        include_ddl             => true,
        include_tagged_lcr      => true,
        source_database         => 'orcl.net');
    END;
    /
    
    
  19. While connected as the Streams administrator in SQL*Plus at the destination database, create an apply process that applies all changes in the queue created in Step 10. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        streams_type       => 'apply',
        streams_name       => 'apply',
        queue_name         => 'strmadmin.streams_queue',
        include_dml        => true,
        include_ddl        => true,
        include_tagged_lcr => true,
        source_database    => 'orcl.net');
    END;
    /
    
    
  20. Complete the steps in "Finishing the Database Maintenance Operation".

Performing a Database Maintenance Operation Using Streams

This section describes performing one of the following database maintenance operations on an Oracle Database 10g database:

You can use Streams to achieve little or no downtime during one of these operations. During the operation, the source database is the existing database on which you are performing database maintenance. The destination database is the database that will replace the source database at the end of the operation.

Preparing for Upgrades to User-Created Applications

If you are upgrading user-created applications, then, typically, schema objects in the database change to support the upgraded applications. In Streams, row LCRs contain information about row changes that result from DML statements. A DML handler is a user procedure that processes row LCRs resulting from DML statements at a source database. A Streams apply process can pass row LCRs to a DML handler, and the DML handler can modify the row LCR to account for differences between a source database and a destination database.

The process for upgrading your user-created applications using Streams involves modifying and creating the schema objects at the destination database after instantiation. You can use one or more DML handlers at the destination database to process changes from the source database so that they apply to the modified schema objects correctly.

Before you begin the database maintenance operation, you should complete the following tasks to prepare your DML handlers:

Deciding Which Utility to Use for Instantiation

Before you begin the database maintenance operation, decide whether you want to use Export/Import utilities (Data Pump or original) or the Recovery Manager (RMAN) utility to instantiate the destination database during the operation. Consider the following factors when you make this decision:

After you decide which utility you want to use for instantiation, complete the steps in the appropriate section:

Performing the Maintenance Operation Using Export/Import and Streams

You may use Data Pump Export/Import or original Export/Import to instantiate the database during the database maintenance operation. Oracle Corporation recommends using Data Pump, and Data Pump may perform the instantiation faster than original Export/Import.

Complete the following steps to perform a maintenance operation using Export/Import and Oracle Streams:

  1. Create an empty Oracle Database 10g database. This database will be the destination database during the maintenance operation. If you are migrating the database to a different operating system, then create the database on a computer system running this operating system. If you are migrating the database to a different character set, then create a database that uses the character set.

    See the Oracle installation guide for your operating system if you need to install Oracle, and see Oracle Database Administrator's Guide for information about creating a 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 database maintenance operation is stms.net. The global name of the destination database is changed when the destination database replaces the source database at the end of the maintenance operation.

  2. At the source database, make any database objects that were not supported by Streams in Oracle Database 10g read-only. See "Datatypes Captured" and "Types of Changes Captured" for information about unsupported objects.
  3. At the source database, configure a Streams administrator. See "Configuring a Streams Administrator" for instructions. This example assumes 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.
  4. While connected as an administrative user in SQL*Plus at the source database, specify supplemental logging at the source database of primary keys or unique indexes (in the absence of primary keys) for all updates:
    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 
       (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
    
    
  5. If you are upgrading user-created applications, then supplementally log any columns at the source database that will be involved in a DML handler at the destination database. These columns must be unconditionally logged at the source database. See Oracle Streams Replication Administrator's Guide for information about specifying unconditional supplemental log groups for these columns.
  6. While connected as the Streams administrator in SQL*Plus at the source database, create a SYS.AnyData queue that will stage changes made to the source database during the maintenance operation. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  7. While connected as the Streams administrator in SQL*Plus at the source database, configure a local capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 6. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN 
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'capture',
       streams_name       => 'capture',
       queue_name         => 'streams_queue',
       include_dml        => true,
       include_ddl        => true,
       include_tagged_lcr => true,
       source_database    => NULL);
    END;
    /
    
    

    Do not start the capture process.

  8. Instantiate the destination database using Oracle Data Pump or original Export/Import. See Oracle Streams Replication Administrator's Guide for instructions. In either case, make sure the following parameters are set to the appropriate values:
    • Set the CONSISTENT export parameter to y.
    • Set the STREAMS_CONFIGURATION import parameter to n.
    • If you use original Export/Import, then set the STREAMS_INSTANTIATION import parameter to y. This parameter does not apply to Data Pump imports.
  9. 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.
  10. If you are applying a patch, then apply the patch now. Follow the instructions included with the patch.
  11. While connected as the Streams administrator in SQL*Plus at the destination database, remove the imported SYS.AnyData queue. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.REMOVE_QUEUE(
        queue_name              => 'strmadmin.streams_queue',
        cascade                 => false,
        drop_unused_queue_table => true);
    END;
    /
    
    
  12. While connected as the Streams administrator in SQL*Plus at the destination database, re-create the SYS.AnyData queue. This queue will stage changes propagated from the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  13. Configure your network and Oracle Net so that the source database can communicate with the destination database. See Oracle Net Services Administrator's Guide for instructions.
  14. While connected as the Streams administrator in SQL*Plus at the source database, create a database link to the destination database. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'stms.net';
    
    
  15. While connected as the Streams administrator in SQL*Plus at the source database, create a propagation that propagates all changes from the source queue created in Step 5 to the destination queue created in Step 10. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'orcl_to_stms',
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@stms.net', 
        include_dml             => true,
        include_ddl             => true,
        include_tagged_lcr      => true,
        source_database         => 'orcl.net');
    END;
    /
    
    
  16. While connected as the Streams administrator in SQL*Plus at the destination database, create an apply process that applies all changes in the queue created in Step 12. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        streams_type       => 'apply',
        streams_name       => 'apply',
        queue_name         => 'strmadmin.streams_queue',
        include_dml        => true,
        include_ddl        => true,
        include_tagged_lcr => true,
        source_database    => 'orcl.net');
    END;
    /
    
    
  17. If you are upgrading user-created applications, then, at the destination database, complete the following steps:
    1. Modify the schema objects in the database to support the upgraded user-applications.
    2. Configure one or more DML handlers that modify row LCRs from the source database so that the apply process applies these row LCRs to the modified schema objects correctly. For example, if a column name was changed to support the upgraded user-created applications, then a DML handler should rename the column in a row LCR that involves the column.

      See Oracle Streams Replication Administrator's Guide for information about configuring DML handlers.

  18. Complete the steps in "Finishing the Database Maintenance Operation".

Performing the Maintenance Operation Using RMAN and Streams

You may use RMAN to instantiate the database during either of the following database maintenance operations:

However, if you are migrating the database to a different operating system or character set, then you must use Export/Import for instantiation.

See Also:

"Performing the Maintenance Operation Using Export/Import and Streams" if you are migrating the database to a different operating system or character set

Complete the following steps to perform a database migration or apply a patch using RMAN and Oracle Streams:

  1. Create an empty Oracle Database 10g database. This database will be the destination database during the database maintenance operation. Both the source database and the destination database must be Oracle Database 10g databases when you start the database maintenance operation.

    See the Oracle installation guide for your operating system if you need to install Oracle, and see Oracle Database Administrator's Guide for information about creating a 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 database maintenance operation is stms.net. The global name of the destination database is changed when the destination database replaces the source database at the end of the maintenance operation.

  2. At the source database, make any database objects that were not supported by Streams in Oracle Database 10g read-only. See "Datatypes Captured" and "Types of Changes Captured" for information about unsupported objects.
  3. At the source database, configure a Streams administrator. See "Configuring a Streams Administrator" for instructions. This example assumes 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.
  4. While connected as an administrative user in SQL*Plus at the source database, specify supplemental logging at the source database of primary keys or unique indexes (in the absence of primary keys) for all updates:
    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 
       (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
    
    
  5. If you are upgrading user-created applications, then supplementally log any columns at the source database that will be involved in a DML handler at the destination database. These columns must be unconditionally logged at the source database. See Oracle Streams Replication Administrator's Guide for information about specifying unconditional supplemental log groups for these columns.
  6. While connected as the Streams administrator in SQL*Plus at the source database, create a SYS.AnyData queue that will stage changes made to the source database during the database maintenance operation. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  7. While connected as the Streams administrator in SQL*Plus at the source database, configure a local capture process that will capture all supported changes made to the source database and stage these changes in the queue created in Step 6. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN 
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type       => 'capture',
       streams_name       => 'capture',
       queue_name         => 'streams_queue',
       include_dml        => true,
       include_ddl        => true,
       include_tagged_lcr => true,
       source_database    => NULL);
    END;
    /
    
    

    Do not start the capture process.

  8. Instantiate the destination database using RMAN DUPLICATE command by completing the following steps. These steps provide a general outline for using RMAN to duplicate a database. See the Oracle Database Backup and Recovery Advanced User's Guide for detailed information about using RMAN.
    1. Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of orcl.net if one does not exist.
    2. While connected as an administrative user in SQL*Plus at 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 748045. You will set the UNTIL SCN option to this value when you use RMAN to duplicate the database in Step e.

    3. While connected as an administrative user in SQL*Plus at 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 Oracle Database Backup and Recovery Advanced User's Guide 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 b for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step c 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 stms.net. Therefore, the DUPLICATE command for this example includes TO stms.net.

      The following is an example of an RMAN DUPLICATE command:

      rman
      RMAN> CONNECT TARGET SYS/change_on_install@orcl.net
      RMAN> CONNECT AUXILIARY SYS/change_on_install@stms.net
      RMAN> RUN
            { 
              SET UNTIL SCN 748045;
              ALLOCATE AUXILIARY CHANNEL mgdb DEVICE TYPE sbt; 
              DUPLICATE TARGET DATABASE TO mgdb 
              NOFILENAMECHECK
              OPEN RESTRICTED;
            }
      
      
    6. While connected as an administrative user in SQL*Plus at the destination database, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:
      CONNECT SYSTEM/MANAGER
      
      ALTER SYSTEM DISABLE RESTRICTED SESSION;
      
      
  9. At the destination database, connect as an administrator with SYSDBA privilege and run the following procedure:


    Attention:

    Make sure you are connected to the destination database, not the source database, when you run this procedure because it removes the local Streams configuration.


    CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA
    
    EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    
    

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

  10. 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.
  11. While connected as an administrative user in SQL*Plus at 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. For example:
    CONNECT SYSTEM/MANAGER
    
    ALTER DATABASE RENAME GLOBAL_NAME TO stms.net;
    
    
  12. Configure your network and Oracle Net so that the source database and destination databases can communicate with each other. See Oracle Net Services Administrator's Guide for instructions.
  13. If you are applying a patch, then apply the patch now to the destination database. Follow the instructions included with the patch.
  14. While connected as the Streams administrator in SQL*Plus at the destination database, create a database link to the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    CREATE DATABASE LINK orcl.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'orcl.net';
    
    
  15. While connected as the Streams administrator in SQL*Plus at the destination database, set the instantiation SCN for the entire database and all of the database objects to the until SCN value determined in Step 8b. For example, if the until SCN value is 748045, then run the following procedure:
  16. While connected as the Streams administrator in SQL*Plus at 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 7e. In this example, the until SCN was set to 748045. Therefore, the instantiation SCN should be set to 748045 - 1, or 748044.
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
        source_database_name => 'orcl.net',
        instantiation_scn    => 748044,
        recursive            => true);
    END;
    /
    
    
  17. While connected as the Streams administrator in SQL*Plus at the destination database, remove the imported SYS.AnyData queue. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.REMOVE_QUEUE(
        queue_name              => 'strmadmin.streams_queue',
        cascade                 => false,
        drop_unused_queue_table => true);
    END;
    /
    
    
  18. While connected as the Streams administrator in SQL*Plus at the destination database, re-create the SYS.AnyData queue. This queue will stage changes propagated from the source database. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
    
    
  19. While connected as the Streams administrator in SQL*Plus at the source database, create a database link to the destination database. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    CREATE DATABASE LINK stms.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
       USING 'stms.net';
    
    
  20. While connected as the Streams administrator in SQL*Plus at the source database, create a propagation that propagates all changes from the source queue created in Step 6 to the destination queue created in Step 18. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
        streams_name            => 'orcl_to_stms',
        source_queue_name       => 'strmadmin.streams_queue',
        destination_queue_name  => 'strmadmin.streams_queue@stms.net', 
        include_dml             => true,
        include_ddl             => true,
        include_tagged_lcr      => true,
        source_database         => 'orcl.net');
    END;
    /
    
    
  21. While connected as the Streams administrator in SQL*Plus at the destination database, create an apply process that applies all changes in the queue created in Step 18. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        streams_type       => 'apply',
        streams_name       => 'apply',
        queue_name         => 'strmadmin.streams_queue',
        include_dml        => true,
        include_ddl        => true,
        include_tagged_lcr => true,
        source_database    => 'orcl.net');
    END;
    /
    
    
  22. If you are upgrading user-created applications, then, at the destination database, complete the following steps:
    1. Modify the schema objects in the database to support the upgraded user-applications.
    2. Configure one or more DML handlers that modify row LCRs from the source database so that the apply process applies these row LCRs to the modified schema objects correctly. Row LCRs contain information about row changes that result from DML statements. For example, if a column name was changed to support the upgraded user-created applications, then a DML handler should rename the column in a row LCR that involves the column.

      See Oracle Streams Replication Administrator's Guide for information about configuring DML handlers.

  23. Complete the steps in "Finishing the Database Maintenance Operation".

Finishing the Database Maintenance Operation

Complete the following steps to finish the database maintenance operation:

  1. While connected as the Streams administrator in SQL*Plus at the destination database, start the apply process. For example:
    CONNECT strmadmin/strmadminpw@stms.net
    
    BEGIN
      DBMS_APPLY_ADM.START_APPLY(
        apply_name  => 'apply');
    END;
    /
    
    
  2. While connected as the Streams administrator in SQL*Plus at the source database, start the capture process. For example:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
        capture_name  => 'capture');
    END;
    /
    
    

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

  3. 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, and the name of the apply process is apply, then run the following query at the source database:
    CONNECT strmadmin/strmadminpw@orcl.net
    
    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@stms.net a
      WHERE CAPTURE_NAME = 'CAPTURE'
        AND APPLY_NAME   = 'APPLY';
    
    

    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 move on to the next step. At this point in the process, the values returned by this query may 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 source database and the DBA_APPLY view at the destination database. 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 15, "Troubleshooting a Streams Environment".

  4. While connected as the Streams administrator in SQL*Plus at the destination database, make sure there are no apply errors by running the following query:
    CONNECT strmadmin/strmadminpw@stms.net
    
    SELECT COUNT(*) FROM DBA_APPLY_ERROR;
    
    

    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.

  5. Disconnect all applications and users from the source database.
  6. While connected as an administrative user in SQL*Plus at the source database, restrict access to the database. For example:
    CONNECT SYSTEM/MANAGER@orcl.net
    
    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    
    
  7. While connected as an administrative user in SQL*Plus at the source database, 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 move on to the next step.
  8. While connected as the Streams administrator in SQL*Plus at the destination database, 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. Shut down the source database.
  10. At the destination database, remove the Streams components that are no longer needed, including the SYS.AnyData queue, the apply process, supplemental logging specifications, and the Streams administrator. See the following sections for instructions:

    If you no longer need database supplemental logging, then connect as an administrative user in SQL*Plus at the destination database, and run the following statement to drop it:

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

    Also, the following statement drops a user named strmadmin:

    DROP USER strmadmin CASCADE;
    
    
  11. While connected as an administrative user in SQL*Plus at the destination database, change the global name of the database to match the source database. For example:
    CONNECT SYSTEM/MANAGER@stms.net
    
    ALTER DATABASE RENAME GLOBAL_NAME TO orcl.net;
    
    
  12. At the destination database, enable any jobs that you disabled earlier.
  13. 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 Net Services Administrator's Guide for instructions.