D Online Database Upgrade and Maintenance with Oracle Streams

The Capture Database During the Upgrade or Maintenance Operation

During the upgrade or maintenance operation, the capture database is the database where the capture process is created. A local capture process can be created at the source database during the maintenance operation, or a downstream capture process can be created at the destination database or at a third database. If the destination database is the capture database, then a propagation from the capture database to the destination database is not needed. A downstream capture process reduces the resources required at the source database during the maintenance operation.

Assumptions for the Database Being Upgraded or Maintained

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

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

• No messages are enqueued into user-created queues during the upgrade or maintenance operation.

Considerations for Job Slaves and PL/SQL Package Subprograms

If possible, ensure that no job slaves are created, modified, or deleted during the upgrade or maintenance operation, and that no Oracle-supplied PL/SQL package subprograms are invoked during the operation that modify both user data and data dictionary metadata at the same time. The following packages contain subprograms that modify both user data and data 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 19 and 20 in "Performing a Database Upgrade or Maintenance Operation Using Oracle Streams". For example, if a PL/SQL procedure gathers statistics on the source database during the maintenance operation, then the same PL/SQL procedure should be invoked at the destination database in Step 20.

Unsupported Database Objects Are Excluded

The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in the DBMS_STREAMS_ADM package include the following parameters:

• exclude_schemas

• exclude_flags

These parameters specify which database objects to exclude from the Oracle Streams configuration. The examples in this appendix set these parameters to the following values:

exclude_schemas => '*',
exclude_flags   => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                   DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                   DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

These values exclude any database objects that are not supported by Oracle Streams. The asterisk (*) specified for exclude_schemas indicates that some database objects in every schema in the database might be excluded from the replication environment. The value specified for the exclude_flags parameter indicates that DML and DDL changes for all unsupported database objects are excluded from the replication environment. Rules are placed in the negative rule sets for the capture processes to exclude these database objects.

To list unsupported database objects, query the DBA_STREAMS_UNSUPPORTED data dictionary view at the source database. If you use these parameter settings, then changes to the database objects listed in this view are not maintained by Oracle Streams during the maintenance operation. Therefore, Step 7 in "Task 1: Beginning the Operation" instructs you to ensure that no changes are made to these database objects during the database upgrade or maintenance operation.

Preparing for Downstream Capture

If you decided that the destination database or a third database will be the capture database, then you must prepare for downstream capture by configuring log file copying from the source database to the capture database. If you decided that the source database will be the capture database, then log file copying is not required. See "The Capture Database During the Upgrade or Maintenance Operation" for information about the decision.

Complete the following steps to prepare the source database to copy its redo log files to the capture database, and to prepare the capture database to accept these redo log files:

1. Configure Oracle Net so that the source database can communicate with the capture database.

   See Also:

   Oracle Database Net Services Administrator's Guide

2. Configure authentication at both databases to support the transfer of redo data.

   Redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file. If the source database has a remote login password file, then copy it to the appropriate directory on the downstream capture database system. The password file must be the same at the source database and the downstream capture database.

   See Also:

   Oracle Data Guard Concepts and Administration for detailed information about authentication requirements for redo transport

3. At the source database, set the following initialization parameters to configure redo transport services to transmit redo data from the source database to the downstream database:

   • LOG_ARCHIVE_DEST_n - Configure at least one LOG_ARCHIVE_DEST_n initialization parameter to transmit redo data to the downstream database. To do this, set the following attributes of this parameter:

     • SERVICE - Specify the network service name of the downstream database.

     • ASYNC or SYNC - Specify a redo transport mode.

       The advantage of specifying ASYNC is that it results in little or no effect on the performance of the source database. ASYNC is recommended to avoid affecting source database performance if the downstream database or network is performing poorly.

       The advantage of specifying SYNC is that redo data is sent to the downstream database faster then when ASYNC is specified. Also, specifying SYNC AFFIRM results in behavior that is similar to MAXIMUM AVAILABILITY standby protection mode. Note that specifying an ALTER DATABASE STANDBY DATABASE TO MAXIMIZE AVAILABILITY SQL statement has no effect on an Oracle Streams capture process.

     • NOREGISTER - Specify this attribute so that the location of the archived redo log files is not recorded in the downstream database control file.

     • VALID_FOR - Specify either (ONLINE_LOGFILE,PRIMARY_ROLE) or (ONLINE_LOGFILE,ALL_ROLES).

     • TEMPLATE - Specify a directory and format template for archived redo logs at the downstream database. The TEMPLATE attribute overrides the LOG_ARCHIVE_FORMAT initialization parameter settings at the downstream database. The TEMPLATE attribute is valid only with remote destinations. Ensure that the format uses all of the following variables at each source database: %t, %s, and %r.

     • DB_UNIQUE_NAME - The unique name of the downstream database. Use the name specified for the DB_UNIQUE_NAME initialization parameter at the downstream database.

     The following example is a LOG_ARCHIVE_DEST_n setting that specifies a capture database (DBS2.EXAMPLE.COM):

     LOG_ARCHIVE_DEST_2='SERVICE=DBS2.EXAMPLE.COM ASYNC NOREGISTER
        VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
        TEMPLATE=/usr/oracle/log_for_dbs1/dbs1_arch_%t_%s_%r.log
        DB_UNIQUE_NAME=dbs2'
     

     Tip:

     Specify a value for the TEMPLATE attribute that keeps log files from a remote source database separate from local database log files. In addition, if the downstream database contains log files from multiple source databases, then the log files from each source database should be kept separate from each other.

   • LOG_ARCHIVE_DEST_STATE_n - Set this initialization parameter that corresponds with the LOG_ARCHIVE_DEST_n parameter for the downstream database to ENABLE.

     For example, if the LOG_ARCHIVE_DEST_2 initialization parameter is set for the downstream database, then set the LOG_ARCHIVE_DEST_STATE_2 parameter in the following way:

     LOG_ARCHIVE_DEST_STATE_2=ENABLE 
     

   • LOG_ARCHIVE_CONFIG - Set the DB_CONFIG attribute in this initialization parameter to include the DB_UNIQUE_NAME of the source database and the downstream database.

     For example, if the DB_UNIQUE_NAME of the source database is dbs1, and the DB_UNIQUE_NAME of the downstream database is dbs2, then specify the following parameter:

     LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbs1,dbs2)'
     

     By default, the LOG_ARCHIVE_CONFIG parameter enables a database to both send and receive redo.

   See Also:

   Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these initialization parameters

4. At the downstream database, set the DB_CONFIG attribute in the LOG_ARCHIVE_CONFIG initialization parameter to include the DB_UNIQUE_NAME of the source database and the downstream database.

   For example, if the DB_UNIQUE_NAME of the source database is dbs1, and the DB_UNIQUE_NAME of the downstream database is dbs2, then specify the following parameter:

   LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbs1,dbs2)'
   

   By default, the LOG_ARCHIVE_CONFIG parameter enables a database to both send and receive redo.

5. If you reset any initialization parameters while the instance is running at a database in Step 3 or Step 4, then you might want to reset them in the initialization parameter file as well, so that the new values are retained when the database is restarted.

   If you did not reset the initialization parameters while the instance was running, but instead reset them in the initialization parameter file in Step 3 or Step 4, then restart the database. The source database must be open when it sends redo log files to the capture database because the global name of the source database is sent to the capture database only if the source database is open.

Preparing for Upgrade or Maintenance of a Database with User-Defined Types

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 an upgrade or maintenance operation 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 operation. 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 operation is complete, make the tables that contain user-defined types read/write at the destination database.

However, if tables that contain user-defined types must remain open during the operation, then use the following general steps to retain changes to these types during the operation:

1. At the source database, create one or more logging tables to store row changes to tables that include user-defined types. Each column in the logging table must use a data type that is supported by Oracle Streams.

2. At the source database, create a DML trigger that fires on the tables that contain the user-defined data types. The trigger converts each row change into relational equivalents and logs the modified row in a logging table created in Step 1.

3. Ensure that the capture process and propagation are configured to capture and, if necessary, propagate changes made to the logging table to the destination database. Changes to tables that contain user-defined types should not be captured or propagated. Therefore, ensure that the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures include the logging tables and exclude the tables that contain user-defined types.

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

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 extended data type support for Oracle Streams.

Preparing for Upgrades to User-Created Applications

This section is relevant only if the operation entails upgrading user-created applications. During an upgrade of user-created applications, schema objects can be modified, and there might be logical dependencies that cannot be detected by the database alone. The following sections describe handling these issues during an application upgrade:

• Handling Modifications to Schema Objects

• Handling Logical Dependencies

Deciding Whether to Configure Oracle Streams Directly or Generate a Script

The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in the DBMS_STREAMS_ADM package configure the Oracle Streams replication environment during the upgrade or maintenance operation. These procedures can configure the Oracle Streams replication environment directly, or they can generate a script that configures the environment.

Using a procedure to configure replication directly is simpler than running a script, and the environment is configured immediately. However, you might choose to generate a script for the following reasons:

• You want to review the actions performed by the procedure before configuring the environment.

• You want to modify the script to customize the configuration.

To configure Oracle Streams directly when you run one of these procedures, set the perform_actions parameter to TRUE. The examples in this appendix assume that the procedures will configure Oracle Streams directly.

To generate a configuration script when you run one of these procedures, complete the following steps when you are instructed to run a procedure in this appendix:

1. In SQL*Plus, connect as the Oracle Streams administrator to database where you will run the procedure.

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

2. Create a directory object to store the script that will be generated by the procedure. For example:

   CREATE DIRECTORY scripts_dir AS '/usr/scripts';
   

3. Run the procedure. Ensure that the following parameters are set to generate a script:

   • Set the perform_actions parameter to FALSE.

   • Set the script_name parameter to the name of the script you want to generate.

   • Set the script_directory_object parameter to the directory object into which you want to place the script. This directory object was created in Step 2.

4. Review or modify the script, if necessary.

5. In SQL*Plus, connect as the Oracle Streams administrator to database where you will run the procedure.

6. Run the generated script. For example:

   @/usr/scripts/pre_instantiation.sql;
   

Deciding Which Utility to Use for Instantiation

Before you begin the database upgrade or 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:

• If you are migrating the database to a different platform, then you can use either Export/Import or the RMAN CONVERT DATABASE command. The RMAN DUPLICATE command does not support migrating a database to a different platform.

• If you are migrating the database to a different character set, then you must use Export/Import, and the new character set must be a superset of the old character set. The RMAN DUPLICATE and CONVERT DATABASE commands do not support migrating a database to a different character set.

• If you are upgrading from a prior release of Oracle Database to Oracle Database 11g Release 2 (11.2), then consider these additional factors:

• If RMAN is supported for the operation, then using RMAN for the instantiation might be faster than using Export/Import, especially if the database is large.

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

• If the RMAN DUPLICATE or CONVERT DATABASE command is used for database instantiation, then the destination database cannot be the capture database.

• If you are upgrading from a prior release of Oracle Database to Oracle Database 11g Release 2 (11.2), then consider these additional factors:

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

  • If you use the RMAN DUPLICATE, then the database release of the destination database must be the same as the source database.

  • If you use the RMAN CONVERT DATABASE, then the database release of the destination database must be the equal to or later than the source database.

Table D-1 describes when each instantiation method is supported based on whether the platform at the source and destination databases are the same or different, and whether the character set at the source and destination databases are the same or different.

Only some platform combinations are supported by the RMAN CONVERT DATABASE command. You can use the DBMS_TDB package to determine whether a platform combination is supported.

Task 1: Beginning the Operation

Complete the following steps to begin the upgrade or maintenance operation using Oracle Streams:

1. Create an empty destination database. If you are migrating the database to a different platform, then create the database on a computer system that uses the new platform. If you are migrating the database to a different character set, then create a database that uses the new character set.

   Ensure that 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.example.com and the global name of the destination database during the database maintenance operation is stms.example.com. The global name of the destination database is changed when the destination database replaces the source database at the end of the maintenance operation.

   If you are not upgrading from a prior release of Oracle Database, then create an Oracle Database 11g Release 2 (11.2) database. See the Oracle installation guide for your operating system if you must install Oracle, and see the Oracle Database Administrator's Guide for information about creating a database.

   If you are upgrading from a prior release of Oracle Database, then 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 11g 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 must install Oracle Database, and see the Oracle Database Administrator's Guide for information about creating a database.

   • If you decided to use RMAN DUPLICATE 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 Oracle Database 10g Release 2 (10.2) database, then create an Oracle Database 10g Release 2 database. Alternatively, if you are upgrading an Oracle Database 11g Release 1 (11.1) database, then create an Oracle Database 11g 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 must install Oracle, and see the Oracle Database Administrator's Guide for the release for information about creating a database.

   • If you decided to use RMAN CONVERT DATABASE for instantiation, then create an empty Oracle database that is a release equal to or later than the database you are upgrading.

     Specifically, if you are upgrading an Oracle Database 10g Release 2 (10.2) database, then create an Oracle Database 10g Release 2 database, an Oracle Database 11g Release 1 database, or an Oracle Database 11g Release 2 database. Alternatively, if you are upgrading an Oracle Database 11g Release 1 (11.1) database, then create an Oracle Database 11g Release 1 database or an Oracle Database 11g Release 2 database.

     This database will be the destination database during the upgrade process.

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

2. Ensure that the source database is running in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode.

3. Create an undo tablespace at the capture database if one does not exist. For example, run the following statement while logged into the capture database as an administrative user:

   CREATE UNDO TABLESPACE undotbs_02
     DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
   

   The capture process at the capture database uses the undo tablespace.

   See "The Capture Database During the Upgrade or Maintenance Operation" for more information about the capture database.

   See Oracle Database Administrator's Guide for more information about creating an undo tablespace.

4. Ensure that the initialization parameters are set properly at both databases to support an Oracle Streams environment.

   For Oracle Database 11g Release 2 (11.2) databases, see Oracle Streams Replication Administrator's Guide for information about setting initialization parameters that are relevant to Oracle Streams.

   If you are upgrading from a prior release of Oracle Database, then for the source database, see the Oracle Streams documentation for the source database release.

5. Configure an Oracle Streams administrator at each database, including the source database, destination database, and capture database (if the capture database is a third database). This example assumes that the name of the Oracle Streams administrator is strmadmin at each database.

   For Oracle Database 11g Release 2 (11.2) databases, see Oracle Streams Replication Administrator's Guide for instructions.

   If you are upgrading from a prior release of Oracle Database, then for the source database, see the Oracle Streams documentation for the source database release.

6. If you are upgrading user-created applications, then supplementally log any columns at the source database that will be involved in a rule-based transformation, procedure DML handler, or value dependency. 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.

7. At the source database, ensure that no changes are made to the database objects that are not supported by Oracle Streams during the upgrade or maintenance operation. To list unsupported database objects, query the DBA_STREAMS_UNSUPPORTED data dictionary view.

   "Preparing for Upgrade or Maintenance of 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 tables that contain user-defined types can remain open during the operation.

Task 2: Setting Up Oracle Streams Before Instantiation

The specific instructions for setting up Oracle Streams before instantiation depend on which database is the capture database. The PRE_INSTANTIATION_SETUP procedure always configures the capture process on the database where it is run. Therefore, this procedure must be run at the capture database.

When you run this procedure, you can specify that the procedure performs the configuration directly, or that the procedure generates a script that contains the configuration actions. See "Deciding Whether to Configure Oracle Streams Directly or Generate a Script". The examples in this section specify that the procedure performs the configuration directly.

Follow the instructions in the appropriate section:

• The Source Database Is the Capture Database

• The Destination Database Is the Capture Database

• A Third Database Is the Capture 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

• Instantiating the Database Using the RMAN DUPLICATE Command

• Instantiating the Database Using the RMAN CONVERT DATABASE Command

Task 4: Setting Up Oracle Streams After Instantiation

To set up Oracle Streams after instantiation, run the POST_INSTANTIATION_SETUP procedure. The POST_INSTANTIATION_SETUP procedure must be run at the database where the PRE_INSTANTIATION_SETUP procedure was run in "Task 2: Setting Up Oracle Streams Before Instantiation".

When you run the POST_INSTANTIATION_SETUP procedure, you can specify that the procedure performs the configuration directly, or that the procedure generates a script that contains the configuration actions. See "Deciding Whether to Configure Oracle Streams Directly or Generate a Script". The examples in this section specify that the procedure performs the configuration directly.

The parameter values specified in the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures must match, except for the values of the following parameters: perform_actions, script_name, script_directory_object, and start_processes. In this example, all of the parameter values match in the two procedures.

It is important to set the instantiation_scn parameter in the POST_INSTANTIATION_SETUP procedure correctly. Follow these instructions when you set this parameter:

• If RMAN was used for instantiation, then set the instantiation_scn parameter to the value determined during instantiation. This value was determined when you completed the instantiation in "Instantiating the Database Using the RMAN DUPLICATE Command" or "Instantiating the Database Using the RMAN CONVERT DATABASE Command".

  The source database and third database examples in this section set the instantiation_scn parameter to 748044 for the following reasons:

  • If the RMAN DUPLICATE command was used for instantiation, then the command duplicates the database up to one less than the SCN value specified in the UNTIL SCN clause. Therefore, you should subtract one from the until SCN value that you specified when you ran the DUPLICATE command in Step 7 in "Instantiating the Database Using the RMAN DUPLICATE Command". In this example, the until SCN was set to 748045. Therefore, the instantiation_scn parameter should be set to 748045 - 1, or 748044.

  • If the RMAN CONVERT DATABASE command was used for instantiation, then the instantiation_scn parameter should be set to the SCN value determined immediately before running the CONVERT DATABASE command. This value was determined in Step 5 in "Instantiating the Database Using the RMAN CONVERT DATABASE Command".

• If Export/Import was used for instantiation, then the instantiation SCN was set during import, and the instantiation_scn parameter must be set to NULL. The destination database example in this section sets the instantiation_scn to NULL because RMAN cannot be used for database instantiation when the destination database is the capture database.

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

• The Source Database Is the Capture Database

• The Destination Database Is the Capture Database

• A Third Database Is the Capture Database

Task 5: Finishing the Upgrade or Maintenance Operation and Removing Oracle Streams

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

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

2. If you are applying a patch or patch set, then apply the patch or patch set to the destination database. Follow the instructions included with the patch or patch set. If you are not applying a patch or patch set, then skip this step and proceed to the next step.

3. If you are upgrading user-created applications, then, at the destination database, you might need to complete the following steps:

   1. Modify the schema objects in the database to support the upgraded user-created applications.

   2. Configure one or more declarative rule-based transformations and procedure 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 declarative rule-based transformation should rename the column in a row LCR that involves the column.

      See "Handling Modifications to Schema Objects".

   3. Configure one or more virtual dependency definitions if row LCRs might contain logical dependencies that cannot be detected by the apply process alone.

      See "Handling Logical Dependencies".

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

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

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

   ALTER SYSTEM DISABLE RESTRICTED SESSION;
   

6. In SQL*Plus, connect to the destination database as the Oracle Streams administrator.

7. Start the apply process. For example:

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

8. In SQL*Plus, connect to the capture database as the Oracle Streams administrator.

9. Start the capture process. For example:

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

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

10. 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 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_maint, and the name of the apply process is apply_maint, 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@stms.example.com a
         WHERE c.CAPTURE_NAME = 'CAPTURE_MAINT'
           AND a.APPLY_NAME   = 'APPLY_MAINT';
       

       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 Chapter 30, "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_maint, then run the following query at the source database:

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

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

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

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

    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.

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

14. Connect to the source database as an administrative user.

15. Restrict access to the database. For example:

    ALTER SYSTEM ENABLE RESTRICTED SESSION;
    

16. While connected as an administrative user in SQL*Plus to the source database, repeat the query you ran in Step 10a. 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 move on to the next step.

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

18. Repeat the query you ran in Step 12. 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.

19. If you performed any actions that created, modified, or deleted job slaves at the source database during the upgrade or maintenance operation, then perform the same actions at the destination database. See "Considerations for Job Slaves and PL/SQL Package Subprograms" for more information.

20. If you invoked any Oracle-supplied PL/SQL package subprograms at the source database during the upgrade or maintenance operation 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 Slaves and PL/SQL Package Subprograms" for more information.

21. Remove the Oracle Streams components that are no longer needed from both databases, including the ANYDATA queues, supplemental logging specifications, the capture process, the propagation if one exists, and the apply process. Connect as the Oracle Streams administrator in SQL*Plus to the capture database, and run the CLEANUP_INSTANTIATION_SETUP procedure to remove the Oracle Streams components at both databases.

    If the capture database is the source database or a third database, then run the following procedure:

    DECLARE
      empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
    BEGIN
      DBMS_STREAMS_ADM.CLEANUP_INSTANTIATION_SETUP(
        maintain_mode           => 'GLOBAL',
        tablespace_names        => empty_tbs,
        source_database         => 'orcl.example.com',
        destination_database    => 'stms.example.com',
        perform_actions         => TRUE,
        script_name             => NULL,
        script_directory_object => NULL,
        capture_name            => 'capture_maint',
        capture_queue_table     => 'strmadmin.capture_q_table',
        capture_queue_name      => 'strmadmin.capture_q',
        propagation_name        => 'prop_maint',
        apply_name              => 'apply_maint',
        apply_queue_table       => 'strmadmin.apply_q',
        apply_queue_name        => 'strmadmin.apply_q_table',
        bi_directional          => FALSE,
        change_global_name      => TRUE);
    END;
    /
    

    If the capture database is the destination database, then run the following procedure:

    DECLARE
      empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
    BEGIN
      DBMS_STREAMS_ADM.CLEANUP_INSTANTIATION_SETUP(
        maintain_mode           => 'GLOBAL',
        tablespace_names        => empty_tbs,
        source_database         => 'orcl.example.com',
        destination_database    => 'stms.example.com',
        perform_actions         => TRUE,
        script_name             => NULL,
        script_directory_object => NULL,
        capture_name            => 'capture_maint',
        capture_queue_table     => 'strmadmin.streams_q_table',
        capture_queue_name      => 'strmadmin.streams_q',
        apply_name              => 'apply_maint',
        apply_queue_table       => 'strmadmin.streams_q',
        apply_queue_name        => 'strmadmin.streams_q_table',
        bi_directional          => FALSE,
        change_global_name      => TRUE);
    END;
    /
    

    Notice that the propagation_name parameter is omitted because a propagation is not necessary when the destination database is the capture database.

    Both sample procedures in this step rename the global name of the destination database to orc1.example.com because the change_global_name parameter is set to TRUE.

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

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

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

25. If you no longer need the Oracle Streams administrator at the destination database, then connect as an administrative user in SQL*Plus to the destination database, and run the following statement:

    DROP USER strmadmin CASCADE;
    

The upgrade or maintenance operation is complete.