Oracle Migration Workbench Release Notes
Release 1.2.5.0.0 for Windows

Z26174-01

Library

Product

Oracle Migration Workbench

Release Notes

Release 1.2.5.0.0 for Windows NT and Windows 95/98

January 5th, 2000

Part No. Z26174-01

This document contains the following topics:

Introduction

Oracle Migration Workbench (Migration Workbench) release 1.2.5.0.0 enables you to migrate the following databases to an Oracle8 or Oracle8i database:

If you are migrating an MS SQL Server or Sybase database to an Oracle8 release 8.0.5 database, you must change the Parse Options for stored procedures, triggers, and views. To do this, deselect the Generate Oracle8i temp tables check box in the Parse Options page of the Source Model property sheets for these schema objects before creating the Oracle Model.

If you have a previous version of the Migration Workbench installed, you must deinstall it.

Features

Oracle8i Release 2 (8.1.6) includes the Oracle8i ODBC Driver release 8.1.6.0.0. This ODBC driver provides enhanced implementation of returning result sets from procedure calls. The REFCURSOR parameters can be omitted from a procedure call making the driver consistent and compatible with other ODBC implementations.

For a complete list of the features of the Migration Workbench, see the Oracle Migration Workbench Online Help.

Purpose of this Document

These release notes outline how to install the Migration Workbench and the available plugins. They also provide tips on using the Migration Workbench. For more information, please see the following documentation:

Setting Up and Installing Oracle Migration Workbench

Before you install the Migration Workbench, check your Hardware Requirements and Software Requirements.

Hardware Requirements

The Migration Workbench has the following hardware requirements:

Software Requirements

The Migration Workbench requires the following software:

Microsoft Windows NT 4.0 (Service Pack 5) or Windows 95/98, and an Oracle8 or Oracle8i Server database in which the Migration Workbench can create its repository.


Note:

Refer to the READMEDOC.HTM file at the top level of the Oracle8i for Windows NT CD-ROM for the most up-to-date information on Oracle8i Release 2 (8.1.6) support for Windows 2000.  


The Migration Workbench depends on the Oracle8i Client release 8.1.6 for Windows NT and Windows 95/98 which is included on the Oracle8i for Windows NT CD-ROM.

Installing Oracle Migration Workbench on Windows

Refer to the READMEDOC.HTM file at the top level of the Oracle8i for Windows NT CD-ROM for instructions about how to install the Migration Workbench.

Once you have installed the Migration Workbench, you can launch it as follows:

    1. From the Start menu, go to the Programs menu.

    2. Navigate to your Oracle Home.

    3. Go to the Migration Utilities program group.

    4. Select the Migration Workbench icon.


      Note:

      If you intend to use the MS Access plugin, you must manually install Data Access Objects (DAO) using the DAO Setup wizard at the end of the installation. To run this wizard, go to %ORACLE_HOME%\Omwb\install\DaoSetup. This directory contains three subdirectories called Disk1, Disk2, and Disk3. The Disk1 subdirectory contains the Setup.exe file that you must execute to invoke the DAO Setup wizard.  


Software Location

Oracle Migration Workbench release 1.2.5.0.0 supports the migration of MS SQL Server 6.5, MS SQL Server 7.0, Sybase Adaptive Server 11, and MS Access databases to Oracle8 or Oracle8i. The Migration Workbench is located in %ORACLE_HOME%\Omwb. This directory contains all components of the Migration Workbench in several sub-directories as follows:

Oracle Migration Workbench Repository

You must create a user in the Oracle database in which you wish to store the Oracle Migration Workbench Repository (repository). The first time you log in to this user account you will be asked if you would like to create the repository. The username, password, and service that you enter in the Oracle Migration Workbench Repository Login dialog box define where you want to create the repository.

The Migration Workbench requires approximately 1MB of space to accommodate its meta data in the repository. During the capture phase of the migration, the Migration Workbench takes a snapshot of the data dictionary of the database being migrated to Oracle. This information is stored in the repository and the amount of space required for it will depend on the size of the database being migrated. It is recommended that there be at least 30MB of space available in the default tablespace of the repository owner to accommodate the snapshot and the meta data of the Migration Workbench.

The repository can be created in either an Oracle8 or Oracle8i Server. If the account you specify contains an earlier version of the repository, you will be asked if you would like to re-create the repository. If you click Yes to re-create the repository, the old repository will be removed and the new one will be created. All captured information will be lost.

Tips on Using Oracle Migration Workbench

This section provides several tips for using the Migration Workbench.

Connection Information

In order to capture the data dictionary and extract the data from the source database, the Migration Workbench must know the name of the ODBC data source that points to the machine where the MS SQL Server/Sybase source database is running or where the MS Access data file is located.

You can define an ODBC data source by double-clicking the ODBC icon in the Windows Control Panel and adding a new data source.

The Migration Workbench must also know the Net8 Aliases that point to the repository and to the destination Oracle database to which you intend to migrate. The database that contains the repository and the desintation Oracle database may be the same or may be different. If they are different you will need to create two Net8 Aliases. Net8 Aliases are defined in the following file:

%ORACLE HOME%\network\admin\tnsnames.ora

To add or edit a Net8 alias, run the Oracle Net8 Assistant tool from the Network Administration program group in your Oracle Home.

Customizing Tablespaces

When the Oracle Model is created, a tablespace is created for every database that was captured. For example, if you capture the PUBS database, a tablespace called PUBS is created in the Oracle Model. This tablespace has one data file called PUBS.DBF which is the same size as the PUBS database in the source database.

Two tablespaces are created for each MS Access database that is captured; one for the indexes and one for the tables. For example, if you capture a database called NORTHWIND.MDB, two tablespaces called I_NORTHWIND.MDB and T_NORTHWIND.MDB are created.

Additional tablespaces can be created in the Oracle Model by selecting the Tablespaces container in the Oracle Model tree and clicking the right mouse button. A menu pops up that contains one item called Create. When you select this item, a dialog appears allowing you to enter details about the tablespace you wish to create. Note that the tablespace is not actually created in the destination Oracle database until you run the Migration Wizard.

By default, the data files that are created for the tablespaces are located in the $ORACLE_HOME/dbs (UNIX) or %ORACLE_HOME%\database\ if you do not specify their location. To specify the location of the data files, go to the General page of the Options dialog box in the Tools Menu and specify the full path to the directory in the Data file directory field. For example, /newdisk2/oracle8/ or D:\dev\oracle8\. Ensure that you do this before you create the Oracle Model. The location of the data file can also be modified by editing the tablespace in the Oracle Model.

You can delete or rename a tablespace by selecting that tablespace in the Oracle Model tree and clicking the right mouse button. A menu pops up that contains two items called Delete and Rename. Select whichever item you require and confirm that you wish to delete or rename that tablespace.

Customizing Logging Options and Data Type Mappings

The Migration Wizard automatically generates three timestamped files during the creation phase of the migration. These files are also created if you select the Generate Migration Scripts menu item from the Action Menu in the Migration Workbench.

The timestamp is the same as the session ID that can be seen in the Log Window of the Migration Workbench. To view the Log Window, select the Log Window menu item in the Tools Menu.

The SQL scripts are generated in the Log file directory that you specify in the Logging page of the Options dialog box. If you do not specify a value for the Log file directory, the files will be created in %ORACLE_HOME%\Omwb\log.

If you receive an error stating that a log file could not be generated when running the Migration Wizard, you should change the Log file directory option to point to a valid directory on your machine. All important messages are saved to the Log Window. The contents of the Log Window can be saved by using the File Menu in the Log Window.

The creation of the Oracle Model involves analyzing the data stored in the Source Model and converting it into its Oracle equivalent in the Oracle Model. This involves converting data types from the source database into equivalent Oracle data types. The default mappings can be viewed and changed in the Data Type Mappings page of the Options dialog box. To change the default data type mappings, select an alternative data type from the drop-down list in the Oracle column. To change the scale and/or precision of a data type, overwrite the existing value and click OK.

Customizing Users

Once the Oracle Model has been created, you can view it by clicking the Oracle Model tab in the Navigator Pane. At this stage, you can change the default creation options such as the destination password, the default tablespace, and the temporary tablespace for all users by selecting the Users container in the Oracle Model tree, and selecting the Default Creation Options page of the Users property sheet. You can override the default creation options for a particular user by expanding the Users container, selecting the required user and altering the creation options in the General page of the User's property sheet. Click Apply to save any changes that you make to the default creation options. Please note that you must change the creation options before you run the Migration Wizard.

When the Migration Workbench creates users in the destination database, it defaults their password to ORACLE. You can change the default password for all users or individual users. To change the password for all users, select the Users container in the Oracle Model and go to the Users Creation Options property sheet in the Properties Pane. Enter and confirm a new password. To change the password for an individual user, expand the Users container in the Oracle Model, select the required user, go to the Users General Page, and enter and confirm a new password. In both cases, click the Apply button to save the changes.

You can also rename users in the Oracle Model once you have created the Oracle Model. You can do this by selecting the user in the Oracle Model and using either the right mouse option or selecting the Rename item in the Object Menu.

Customizing Indexes and Tables

Once the Oracle Model has been created, you can view it by clicking the Oracle Model tab in the Navigator Pane. At this stage, you can change the default storage options such as storage parameters and tablespaces for indexes and tables. You can do this by expanding the container for a particular user in the Oracle Model tree to view its schema objects. Then select the Indexes or Tables container and go to the Default Storage Options page of the property sheet for that group.

You can also change the default storage options for a particular index or table. This can be done in the Override Default Storage Options page of the property sheet for the required index or table. Click Apply to save any changes that you make to the default storage options. Note that you must change the storage options before you run the Migration Wizard. You can also edit the column types for tables in the Oracle Model. To do this, go to the General Page of the property sheet for the required table in the Oracle Model.

Stored Procedures

In the event of a parse failure, you can edit the text of a stored procedure in the Source Model and reparse it using the Parse item in the Object Menu. See Reporting Problems for more information about stored procedures.

When examining the text of a stored procedure in the Oracle Model, you should pay particular attention to execute clauses, functions, and areas marked by the following text as these may not have been converted properly:

/*[SPCONV-ERR(XXX)]:Manual conversion required */

For a full list of parsing errors, see the Appendix in the Oracle Migration Workbench Online Help for the MS SQL Server and Sybase plugins.

Error.log File

If the Migration Workbench fails to respond, check the contents of the Error.log file to see if there are any errors recorded. This file is located in %ORACLE_HOME%\Omwb\log\Error.log.

See the Reporting Problems section for instructions on how to notify the Migration Workbench Development Team about these errors.

Known Problems and Workarounds

Renaming Users

Users can only be renamed once in the Oracle Model. If you need to change the name of a user more than once, you should re-create the Oracle Model.

nchar, nvarchar, ntext and Memo Data Types

The Oracle Migration Workbench does not support the mapping of nchar, nvarchar, ntext, or Memo data types from MS SQL Server, Sybase Adaptive Server, or MS Access to the NCHAR, NVARCHAR2, or NCLOB data types in Oracle. Instead, the default mapping for these data types is to CHAR, VARCHAR2, and CLOB in Oracle because these Oracle data types support Unicode and multi-byte character sets. The capability to map nchar, nvarchar, ntext, and Memo data types to NCHAR, NVARCHAR2, and NCLOB will be added in a future release of the Migration Workbench.

Refer to the Oracle Migration Workbench FAQ for a complete list of known problems.

Bugs Fixed

Bug No  Abstract 

1094053 

SQUARE BRACKETS NOT HANDLED. 

1089556 

OMWB CONSTRAINT BUG. 

1087417 

COLLISION MANAGER NOT WORKING FOR NAMES > 30 CHARACTER. 

1073376 

ERROR IN CREATE PACKAGE STATEMENTS IN MWBCREATE SCRIPTS. 

1068680 

WARNING IN THE LOG WINDOW TO TELL THE USER THAT THEIR TABLE HAS NO PRIMARY KEY. 

1068628 

ERROR WHEN A DATABASE WITH A NON SUPPORTED ATTACHEMENT IS SELECTED. 

1035804 

FAILED TO CREATE FOREIGN KEY: ORA-00957 - DUPLICATE COLUMN NAME. 

1034185 

IF INVALID DATA INSERTED FOR FLOAT THE DATA FOR THE WHOLE TABLE IS DROPPED. 

1033913 

BETTER ERROR REPORTING ON CAPTURE OF ACCESS DATABASE. 

1033713 

ACCESS PLUGIN BRINGING OVER HIDDEN SYSTEM INDEXES WHICH IT SHOULDN'T. 

1024962 

INDEXES NOT BEING MIGRATED - ERROR: COLUMN ALREADY INDEXED. 

1023123 

NO OPTION TO START A POST MIGRATION IF IT IS INITIALLY CANCELED ON MIGRATION. 

1022710 

ERROR ON MAPPING INDEX NAMES - NAMES TOO LONG FOR ORACLE I.E > 30 CHARS. 

1003253 

USER NAMES ARE NOT PASSED THROUGH THE COLLISION MANAGER. 

1003246 

SPACES IN OBJECT NAMES NOT FULLY HANDLED FOR SQL SERVER 7.0 PLUGIN. 

998128 

MIGRATION WIZARD PASSWORD INPUT. 

985043 

SELECT COL1, COL2 FROM DELETED CONVERSION DOES NOT COMPILE. 

985014 

TRIGGERS ASSOCIATED WITH TIMESTAMP COLUMNS FAIL TO CREATE. 

983705 

RAISERROR 41200 , #T_ACC_BALANCE_LIST BREAKS THE CONVERTER. 

979891 

GET ORACLE.MTG.SYBASE.PARSER.PARSEEXCEPTION IN 4 PROCEDURES AT A WHERE CLAUSE. 

974591 

PARSING STORED PROCEDURE - INSERT STATEMENT. 

941922 

A NULL DEFAULT SET ON NOT NULL COULUMNS WHERE NO DEFAULT SPECIFIED. 

932922 

PROCEDURE DISAPPEARS FROM ORACLE MODEL ON REPARSE. 

932723 

TABLE MAPPING FAILS WHEN THE MASTER DATABASE HAS NOT BEEN RESTORED. 

927166 

IMPROVE USABILITY OF LOGGING WINDOW. 

925068 

EXEC STATEMENT BREAKS THE CONVERTER. 

Reporting Problems

You should report any problems by sending an e-mail to the Migration Workbench Development Team at infomwb@ie.oracle.com. Include the contents of your %ORACLE_HOME%\Omwb\log\Error.log file with your e-mail.

For problems related to stored procedures, a copy of the stored procedure text is required to reproduce the problem.

The Migration Workbench is in the Oracle Bug Database under Oracle Migration Workbench, product ID 143.

A discussion forum for the Migration Workbench has been established on the Oracle Technology Network (OTN) at http://technet.oracle.com.


Oracle
Copyright © 2000 Oracle Corporation.

All Rights Reserved.

Library

Product