This appendix describes how to migrate version-enabled tables from one release of Workspace Manager to another release. You can either upgrade to the current release or downgrade to a previous major release (no earlier than release 9.0.1). For example:
If you have been working with version-enabled tables with a previous Oracle Workspace Manager release (9.0.1.0.0 or higher), you can upgrade to release 10.1 to preserve your existing work and then continue working with release 10.1. (Note that an Oracle Workspace Manager release lower than 10.1 could have been installed on a release 8.1.6.0.0 or higher Oracle database.)
If you are using Workspace Manager with Oracle Database 10g but need to go back to release 9.2 or 9.0.1, you can downgrade to release 9.2 or 9.0.1 to preserve your existing work and then continue working with release 9.2 or 9.0.1.
For an upgrade or downgrade operation, the tables can remain version-enabled. You do not need to disable versioning before performing an upgrade or downgrade.
To upgrade to the current Workspace Manager release from a previous release, perform the following steps.
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 10g installation, change to $ORACLE_HOME/rdbms/admin
.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA
privileges.
Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
Start the instance in RESTRICT mode:
SQL> STARTUP RESTRICT
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
Set the system to spool results to a log file for later verification of success. For example:
SQL> SPOOL catoutowmu.log
Run the owmupgrd.plb
upgrade script:
SQL> @owmupgrd.plb
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.
Verify the current version of Workspace Manager:
SELECT * FROM wm_installation;
The value of OWM_VERSION
is the new version of Workspace Manager.
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Disable the RESTRICTED SESSION
feature for the instance:
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
To downgrade from the current Workspace Manager release to a previous major release, perform the following steps.
Copy the downgrade scripts from the current release to the installation directory of the release to which you are downgrading.
If you are downgrading to the Workspace Manager release shipped with the RDBMS installation, copy the downgrade scripts to $ORACLE_HOME/rdbms/admin
.
If you are downgrading to release 9.0.1.0.0 or 9.0.1.2.0, copy the following file: owmd901.plb
If you are downgrading to release 9.2.0.1.0, copy the following file: owmd920.plb
At a system prompt, change to the installation directory of the release to which you are downgrading. If you are downgrading to the Workspace Manager release shipped with the RDBMS installation, change to $ORACLE_HOME/rdbms/admin
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA
privileges.
Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
Start the instance in RESTRICT
mode:
SQL> STARTUP RESTRICT
Set the system to spool results to a log file for later verification of success. For example:
SQL> SPOOL catoutowmd.log
Run the appropriate downgrade script depending on the release to which you are downgrading.
To downgrade Workspace Manager, you must first run one procedure in the context of the new Oracle database (higher release number), and then run another procedure in the context of the old Oracle database (lower release number). Here, context refers to the installation directory under $ORACLE_HOME where Workspace Manager files reside (usually $ORACLE_HOME/rdbms/admin).
To downgrade Workspace Manager in the context of the new Oracle database (higher release number), run a procedure whose fourth character is e
(owme
xxx
.sql
). To downgrade Workspace Manager in the context of the old Oracle database (lower release number), run a procedure whose fourth character is d
(owmd
xxx
.plb
). (If the Oracle RDBMS is being downgraded, Workspace Manager is automatically downgraded in the context of the new Oracle database as part of the procedure; however, you must still run the appropriate downgrade procedure in the context of the old Oracle database.)
If you are downgrading to Workspace Manager release 9.0.1.0.0 or 9.0.1.2.0 in the context of release 10.1 or higher of Oracle Database (that is, not downgraded), run owme901.sql
:
SQL> @owme901.sql
If you are downgrading to Workspace Manager release 9.0.1.0.0 or 9.0.1.2.0 in the context of a downgraded Oracle database, run owmd901.plb
:
SQL> @owmd901.plb
If you are downgrading to Workspace Manager release 9.2.0.1.0 in the context of release 10i or higher of the Oracle RDBMS (that is, not downgraded), run owme920.sql
:
SQL> @owme920.sql
If you are downgrading to Workspace Manager release 9.2.0.1.0 in the context of a downgraded Oracle RDBMS, run owmd920.plb
:
SQL> @owmd920.plb
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.
Verify the current version of Workspace Manager:
SELECT * FROM wm_installation;
The value of OWM_VERSION
is the new version of Workspace Manager.
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Disable the RESTRICTED SESSION
feature for the instance:
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
For Oracle Database release 10.1, Workspace Manager implements history management changes that are especially of interest if you want to perform an upgrade or downgrade operation. For release 10.1, Workspace Manager uses the TIMESTAMP WITH TIME ZONE
type with history data, whereas in previous releases 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 on Oracle9i and higher releases. On 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 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 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:
SYS.OWM_MIG_PKG.UpgradeHistoryColumns
with no parameters upgrades the history columns of all version-enabled tables
SYS.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.