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

One option is to ensure that no data manipulation language (DML) or data definition language (DDL) changes are made to the tables that contain user-defined types 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 use the following general steps to retain changes to these tables during the upgrade:

For instructions, go to the My Oracle Support (formerly OracleMetaLink) Web site using a Web browser:

http://support.oracle.com/

Database bulletin 556742.1 describes additional data type support for Oracle Streams.

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 11g Release 2 (11.2) or later 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, the following conditions must be met:

  • If the database is an Oracle9i 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 E-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.

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

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

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

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

2. Start the apply process. For example:

   BEGIN
     DBMS_APPLY_ADM.START_APPLY(
       apply_name  => 'apply_upgrade');
   END;
   /
   

3. Connect to the capture database as the Oracle Streams administrator.

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

5. While still connected as the Oracle Streams administrator in SQL*Plus to the capture database, monitor the Oracle Streams environment until the apply process at the destination database has applied most of the changes from the source database.

   To determine whether the apply process at the destination database has applied most of the changes from the source database, complete the following steps:

   1. Query the enqueue message number of the capture process and the message number with the oldest system change number (SCN) for the apply process to see if they are nearly equal.

      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 capture database:

      COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Captured SCN' FORMAT 99999999999
      COLUMN OLDEST_SCN_NUM HEADING 'Oldest Applied SCN' FORMAT 99999999999
      
      SELECT c.ENQUEUE_MESSAGE_NUMBER, a.OLDEST_SCN_NUM
        FROM V$STREAMS_CAPTURE c, V$STREAMS_APPLY_READER@updb.example.com a
        WHERE c.CAPTURE_NAME = 'CAPTURE_UPGRADE'
          AND a.APPLY_NAME   = 'APPLY_UPGRADE';
      

      When the two 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 ensure that the Oracle 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 Information About the Schedules for Propagation Jobs".

      If an Oracle Streams client is disabled, then try restarting it. If an Oracle Streams client will not restart, then troubleshoot the environment using the information in Identifying Problems in an Oracle Streams Environment.

   2. Query the state of the apply process apply servers at the destination database to determine whether they have finished applying changes.

      For example, if the name of the apply process is apply_upgrade, then run the following query at the capture database:

      COLUMN STATE HEADING 'Apply Server State' FORMAT A20
       
      SELECT STATE
        FROM V$STREAMS_APPLY_SERVER@updb.example.com
        WHERE APPLY_NAME = 'APPLY_UPGRADE';
      

      When the state for all apply servers is IDLE, you can proceed to the next step.

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

7. Ensure that there are no apply errors by running the following query:

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

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

9. Connect as an administrative user to the source database. In this example, the source database is orcl.example.com.

10. Restrict access to the database. For example:

    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    

11. Connect as an administrative user in SQL*Plus to the capture database, and repeat the query you ran in Step 55.a. When the two 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.

12. Connect as the Oracle Streams administrator in SQL*Plus to the destination database, and repeat the query you ran in Step 7. 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 more information.

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

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

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

16. Connect to the destination database as an administrative user.

17. Change the global name of the database to match the source database. For example:

    ALTER DATABASE RENAME GLOBAL_NAME TO orcl.example.com;
    

18. At the destination database, enable any jobs that you disabled earlier.

19. 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 more information.

20. At the destination database, remove the Oracle Streams components that are no longer needed. Connect as an administrative user to the destination database, and run the following procedure:

    Note:

    Running this procedure is dangerous. It removes the local Oracle Streams configuration. Ensure that you are ready to remove the Oracle 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 Oracle Streams administrator at the destination database, then run the following statement:

    DROP USER strmadmin CASCADE;
    

21. If the capture database was a third database, then, at the third database, remove the Oracle Streams components that are no longer needed. Connect as an administrative user to the third database, and run the following procedure:

    Note:

    Running this procedure is dangerous. It removes the local Oracle Streams configuration. Ensure that you are ready to remove the Oracle 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 Oracle Streams administrator at the destination database, then run the following statement:

    DROP USER strmadmin CASCADE;
    

The database upgrade is complete.