B Migrating to Another Workspace Manager Release

B.1 Upgrading Workspace Manager

To upgrade Workspace Manager from an earlier release to the highest current release number for a given major release, ensure that you have the appropriate patch set for the release to which you want to upgrade. You can find the latest patch set on My Oracle Support (formerly called MetaLink). To perform the upgrade, follow these steps.

1. At a system prompt, change to the installation directory of the release to which you are upgrading. If you are upgrading to the Workspace Manager release that is included in the current Oracle Database 11g installation, change to $ORACLE_HOME/rdbms/admin.

2. Start SQL*Plus.

3. Connect to the database instance as a user with SYSDBA privileges.

4. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   

5. Start the instance in RESTRICT mode:

   SQL> STARTUP RESTRICT
   

6. Determine the current release of the Workspace Manager software by finding the value of OWM_VERSION in the WM_INSTALLATION view:

   SQL> SELECT * FROM wm_installation;
   

   If the OWM_VERSION value is NOT_INSTALLED, Workspace Manager is not currently installed.

   If the OWM_VERSION value is BETA_RELEASE, the upgrade is not supported. Use DisableVersioning on all version-enabled tables, uninstall the old release of Workspace Manager using the old uninstall script, and install the new release of the Workspace Manager software.

   If the WM_INSTALLATION view does not exist, run the following script to create the view.

   SQL> @owmcmdv.plb
   

7. Set the system to spool results to a log file for later verification of success. For example:

   SQL> SPOOL catoutowmu.log
   

8. Run the owmupgrd.plb upgrade script:

   SQL> @owmupgrd.plb
   

9. Verify whether or not the upgrade was successful:

   SELECT * FROM all_wm_vt_errors;
   

   This view should be empty. If it has any rows, the upgrade did not complete successfully. To recover one or more tables that were left in an inconsistent state because of the upgrade failure, use the RecoverAllMigratingTables or RecoverAllMigratingTables procedure, both of which are described in Chapter 4.

10. Verify the current version of Workspace Manager:

    SELECT * FROM wm_installation;
    

    The value of OWM_VERSION is the new version of Workspace Manager.

11. Turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

12. Disable the RESTRICTED SESSION feature for the instance:

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;  
    

B.2 Downgrading to a Previous Release

Downgrading is strongly discouraged, except for rare cases where it is necessary. If you downgrade to a previous release, you will not benefit from bug fixes and enhancements that have been made in intervening releases.

To downgrade from the current Workspace Manager release to a previous major release, ensure that you have the appropriate patch set for the release to which you want to downgrade. You can find Workspace Manager patch sets on My Oracle Support (formerly called MetaLink). The following downgrade options are supported:

• Downgrading from a release to the base release of the same major version (for example, from Workspace Manager 9.2.0.x to Workspace Manager 9.2.0.1). If you need to downgrade to a release other than the base release, you must downgrade to the base release and then upgrade to the desired release number.

• Downgrading from any release to the base release of the previous major version (for example, from Workspace Manager 10.1.0 to Workspace Manager 9.2.0.1)

To perform a Workspace Manager downgrade, follow these steps. (The main steps are running the appropriate .sql and .plb files.)

1. Copy the appropriate .plb file from the patch set for the latest installed Workspace Manager release to the $ORACLE_HOME/rdbms/admin directory for the patch set of the Workspace Manager release to which you want to downgrade.

   This file has a name in the form owmdnnn.plb, where nnn reflects the number of the latest installed Workspace Manager release. For example, for downgrading from release 9.2.0.5.1, the name of the file is owmd920.plb.

2. Note the location (under where you unzipped the patch set for the latest installed Workspace Manager release) of the appropriate owmennn.sql file, where nnn reflects the number of the latest installed Workspace Manager release. For example, for downgrading from release 9.2.0.5.1, the name of the file is owme920.sql.

3. Start SQL*Plus, and connect as SYS AS SYSDBA. (You can connect as another user, but it must be AS SYSDBA.)

4. Shut down the instance, and restart it in RESTRICT mode:

   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP RESTRICT
   

5. Optionally, set the system to spool results to a log file for later verification of success. For example:

   SQL> SPOOL catoutowmd.log
   

6. Run the .sql file that you noted in step 2. For example:

   SQL> @C:\my_patches\92051_OWM\owme920.sql
   

7. Run the .plb file that you copied in step 1. For example:

   SQL> @$ORACLE_HOME\rdbms\admin\owmd920.plb
   

8. Verify whether or not the downgrade was successful:

   SELECT * FROM wm_downgrade_tables;
   

   This table should not exist. If it exists and has any rows, the downgrade did not complete successfully; contact Oracle Support Services.

9. Verify the current version of Workspace Manager:

   SELECT * FROM wm_installation;
   

   The value of OWM_VERSION is the new version of Workspace Manager.

10. If you enabled spooling in step 5, turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

11. Disable the RESTRICTED SESSION feature for the instance:

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;  
    

B.3 History Management Changes Effective With Release 10.1

For Oracle Database release 10.1, Workspace Manager implemented history management changes that are especially of interest if you want to perform an upgrade or downgrade operation. Workspace Manager uses the TIMESTAMP WITH TIME ZONE type with history data, whereas before release 10.1 it used the DATE type.

Using a timestamp with a time zone has several benefits:

• Finer granularity. Workspace Manager uses a time granularity of microseconds, whereas for the DATE type the granularity is seconds.

• Correct interpretation of times when information is imported, exported, or replicated across time zones.

The following considerations apply to the history management changes for release 10.1:

• The TIMESTAMP WITH TIME ZONE type is used only in Oracle9i and higher releases. In release 8.1.7, Workspace Manager uses the DATE type for history management.

• The GotoDate procedure has a format (with the fmt parameter) that enables you to specify a timestamp or a date, with the same options as for the TO_DATE function, described in Oracle Database SQL Language Reference. The in_date parameter is of type VARCHAR2 to support Workspace Manager on all relevant Oracle releases (because the TIMESTAMP WITH TIME ZONE type is not available on Oracle releases before Oracle9i).

• Workspace Manager allows tables with history columns of DATE and TIMESTAMP WITH TIME ZONE types to coexist, so that data exported from release 8.1.7 can be imported into Oracle9i. Tables that are version-enabled using the release 10.1 or higher will use the TIMESTAMP WITH TIME ZONE type for history management.

• You can upgrade the history columns of either all version-enabled tables or an individual version-enabled table:

  WMSYS.OWM_MIG_PKG.UpgradeHistoryColumns with no parameters upgrades the history columns of all version-enabled tables

  WMSYS.OWM_MIG_PKG.UpgradeHistoryColumns(owner_var VARCHAR2, table_name_var VARCHAR2)upgrades the history column of a specified version-enabled table.

• Downgrading to an earlier version of Workspace Manager changes the history columns back to the DATE type.