Oracle Migration Workbench
Release Notes
Release 1.3.0.0.0 for
Windows NT and Windows 95/98/2000
September 2000
Part Number: Z26939-01
This document contains the following topics:
Introduction
Oracle Migration Workbench (Migration Workbench) release 1.3.0.0.0 enables you to migrate the following databases to an Oracle8 or Oracle8i database:
- Microsoft (MS) SQL Server 6.5
- MS SQL Server 7.0
- Sybase Adaptive Server 11
The new release of the Oracle Migration Workbench (Migration Workbench) for migrating an MS Access databases to an Oracle8 or Oracle8i database is planned to be available in September from the following Web site:
http://technet.oracle.com/tech/migration/
If you have a previous version of the Migration Workbench installed, you must deinstall it.
Purpose of this Document
This document outline how to install the Migration Workbench and the available plugins. It also provide tips on using the Migration Workbench. For more information, please see the following documentation:
- Oracle Migration Workbench Online Help
- Oracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide (Part Number: Z26940-01)
- Oracle Migration Workbench FAQ (Part Number: Z26938-01)
Setting Up the Migration Workbench
Before you install the Migration Workbench, review the Hardware Requirements and Software Requirements topics.
Hardware Requirements
The Migration Workbench has the following minimum and recommended hardware requirements:
- Personal computer based on an Intel 80486 processor or higher; a Pentium is recommended.
- 64MB RAM (128MB recommended).
- 115MB hard disk space for the installation of the Migration Workbench and its dependencies.
- VGA video (SVGA is recommended).
- Windows NT or Windows 95/98/2000 compatible network adapter.
Software Requirements
The Migration Workbench has the following minimum and recommended software requirements:
- Microsoft Windows NT 4.0 (Service Pack 5) or Windows 95/98/2000.
In order to migrate from MS SQL Server to Oracle you must have the following installed on the same system as the Migration Workbench:
- MS SQL Server ODBC driver release 3.70.06.23 or higher. You can obtain this by downloading Microsoft Data Access Components (MDAC) release 2.1.2.4202.3 from the Microsoft Web site at:
http://www.microsoft.com
Issues Installing the Migration Workbench
In order to obtain a successful migration, consider the following:
Installing into an Oracle_Home Directory
Since release 1.3.0.0.0 of the Migration Workbench includes 8.1.7 dependencies, you can only install it into an 8.1.7 %ORACLE_HOME% or its own home directory. If you have a previous release of Oracle installed on your system, you must install this release of the Migration Workbench into a separate %ORACLE_HOME%. You then need to switch your default %ORACLE_HOME% to point to your previous Oracle installation so that you do not pick up the new NT client dependencies for other applications you may use on the system. You can do this using Home Selector from the 'Oracle Installation Products' program group. In this case, the Oracle Universal Installer displays the error message: Oracle Migration Workbench can only be installed into a new ORACLE_HOME or an existing 8.1.7 ORACLE_HOME.
Loading the Source Master Database from Sybase
There is a known bug with Java runtime environment (JRE) within the JDK 1.1.7 that Oracle ships. It can cause the Migration Workbench to hang when you are loading the source master database from Sybase. Therefore, you should upgrade to Sun Microsystems Java Development Kit (JDK) 1.1.8.
Installing the Migration Workbench
You can install the Migration Workbench in either of two ways:
Installing the Migration Workbench using the Oracle Universal Installer
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 along with Oracle8i (Release 3) 8.1.7.
Once you have installed the Migration Workbench, you can launch it. For more information, see the Launching the Migration Workbench topic.
Installing the Migration Workbench from the Download
To install the Migration Workbench for Windows NT and Windows 95/98/2000 as a stand-alone product:
- Start Windows NT or Windows 95/98/2000.
- Log on to your PC as a member of the Administrators Group.
- Download the Migration Workbench from the Web site at:
http://technet.oracle.com/tech/migration/
- Run the self-extracting ZIP file, omwb_nt_13000_c.exe. This contains the installation for the Migration Workbench and automatically invokes the Oracle Universal Installer.
- From the Oracle Universal Installer Welcome window click Next to continue.
- The File Location window appears. Keep the information in the Path field of the Source section. This contains the full path of the
products.jar file that contains the installation components for the Migration Workbench.
- In the Name field in the Destination section, type the name of the Oracle Home where you would like to install the Migration Workbench, or select it from the drop-down list. If this is the first time you are installing Oracle products on this system, Oracle Corporation recommends you accept the default home name, DEFAULT_HOME. DEFAULT_HOME is also the Oracle name used for previous installations of Oracle software on your system. If you are installing into an existing Oracle Home, it must be an 8.1.7 Oracle Home.
Note:
The name of the Oracle Home must be 1 to 16 characters long. It can only include alphanumeric characters and underscores.
|
- In the Path field in the Destination section, type the path of the Oracle Home, or select it from the drop-down list. The Migration Workbench automatically creates a path if it does not already exist. After completing all the fields in the File Location window click Next.
- The Available Product Components window lists the components that are installed with the Migration Workbench. Ensure that only the items that you want installed on your system are selected. If you deselect an item that is already installed, it is automatically deinstalled. When you are satisfied with the components to install and deinstall, click Next.
- The Summary window shows what you are going to install. Review it for accuracy, then click Install. The Install window appears showing the status of the installation.
- If Net8 Client 8.1.7.0.0 is part of the installation process because you did not previously install it, the Net8 Configuration Assistant automatically runs. Follow the Net8 Easy Config wizard and the online help, then click Finish. This creates your tnsnames.ora file necessary for connecting to an Oracle database.
- You can exit from the End of Installation window by clicking Exit.
- Follow the instructions in the Launching the Migration Workbench topic.
Launching the Migration Workbench
If you are running Windows 95/98, restart your system before running the Migration Workbench. Rebooting your system resets the path for the JRE.
To launch the Migration Workbench:
- Choose Program -> Start.
- Navigate to your Oracle Home.
- Go to the Migration Utilities program group.
- Select the Migration Workbench icon.
Installation Structure
Oracle Migration Workbench release 1.3.0.0.0 supports the migration of MS SQL Server 6.5, MS SQL Server 7.0, and Sybase Adaptive Server 11 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:
- Bin - Contains the batch file, omwb.bat, required to launch the Migration Workbench.
- Docs - Contains the Oracle Migration Workbench FAQ, the Oracle Migration Workbench Release Notes, and the Oracle Migration Workbench Quick Tour.
- Jlib - Contains the Java libraries, images, and online Help files.
- Log - Contains the SQL scripts, MwbCreated<timestamp>.sql and MwbDrops<timestamp>.sql, that are generated when you run the Migration wizard. It also contains the MwbErrors<timestamp>.sql file that contains all the errors generated by the Migration Workbench.
- Sqlloader_scripts - Contains the files generated for off-line data loading. This occurs when you choose the Object -> Generate SQL*Loader Scripts option within the Migration Workbench.
- Migration_scripts - Contains the Create.sql and Drop.sql files generated when you choose the Action -> Generate Migration Scripts option within the Migration Workbench. You overwrite these files every time you generate the migration scripts.
- Plugins - Contains a jar file for each supported database plugin.
Oracle Migration Workbench Repository
You must create a user in the Oracle database where you want to store the Oracle Migration Workbench Repository (repository). You must have already granted connect and resource pre-defined roles to this user. The first time you log in to this user account you are asked if you would like to create the repository. The user name, 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 depends on the size of the database being migrated. It is recommended that you have 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.
You can create the repository in either an Oracle8, Oracle8i, or Oracle8i Lite server. If the account you specify contains an earlier version of the repository, you are asked if you would like to re-create the repository. If you click Yes to re-create the repository, the old repository is removed and the new one is created. You lose all previously captured information.
Installation Configuration
In order to use the Migration Workbench to successfully migrate to an Oracle database, you need the following:
- An Oracle8, Oracle8i, Oracle8i Lite server database where the Migration Workbench can create and store its repository.
- A configured ODBC driver so that you can connect to the source database.
- Net8 Client 8.1.7.0.0 so that you can connect to Oracle.
- A user that has already been assigned connect and resource grants.
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 system where you are running the MS SQL Server or Sybase source database.
The Migration Workbench must also know the Net8 Aliases that point to the repository and to the destination Oracle database where you intend to migrate. If the database that contains the repository and the destination Oracle database are different, you need to create two Net8 Aliases. You define Net8 Aliases 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.
Online Support
The Migration Workbench Development Team provides support and solutions to your migration problems. Choose Help -> Online Support to launch a page that explains how to report a problem and provides a list of the support
Limitations and Bugs
Open Bugs
Bug 933067
Description: Separate cursor variables are needed for multiple result sets.
Workaround: The Migration Workbench does not support multiple result sets. However, you can manually change the stored procedure and the application code to accommodate the multiple result sets. For more information, see the Result Sets topic, Does the Migration Workbench support the conversion of T/SQL stored procedures that return multiple result sets?, within the Oracle Migration Workbench FAQ (Part Number Z26938-01).
Bug 974643
Description: Oracle explicitly handles result sets, while MS SQL Server and Sybase implicitly handle them. The Migration Workbench cannot handle a stored procedure calling another stored procedure containing a result set.
Workaround: You must manually add an extra parameter into the stored procedure that is calling the stored procedure that contains the result set. You can select the following result set types from the Stored Procedures: Parse Options Page within the Migration Workbench:
- Single Result Set: Converts a T-SQL stored procedure into a PL/SQL package and a PL/SQL procedure/function.
- Power Builder: Creates an Oracle stored procedure that supports result sets for PowerBuilder.
- Multiple Output Arguments: Converts a T-SQL stored procedure into a PL/SQL procedure/function.
Bug 974643
Description: In some situations, the output parameters wihtin a stored procedure are not automatically handled by the Migration Workbench.
Workaround: If you have an output parameter in a stored procedure, you must have a variable to put the parameter into. This is because PL/SQL is more selective about syntax then T/SQL.
Fixed Bugs
|
Bug Number |
Plugin |
Description |
|
1346763 |
COMMON |
CANNOT DELETE A TABLESPACE |
|
1346445 |
WSDK |
BEIN/ENDS PARSER ERROR |
|
1346341 |
WSDK |
MIGRATION NOT POSSIBLE WHEN DEFAULT LOG DIRECTORY HAS NOT BEEN CREATED |
|
1345547 |
COMMON |
ERROR WHEN DOING TWO MIGRATIONS OF THE SAME DATABASE |
|
1345518 |
COMMON |
RELEASE NOTES, FAQ SLOW TO LOAD UP WHEN CALLED FROM MAIN MENU |
|
1345516 |
WSDK |
CANNOT RENAME A USER - MISSING RESOURCE IN ERROR.LOG |
|
1345513 |
SQLSERVER |
AFTER SWITCHING REPOSITORY THE CAPTURE WINDOW DISPLAYS NOTHING |
|
1342935 |
COMMON |
TITLE MISMATCH IN STEP 2 OF THE MIGRATION WIZARD |
|
1339383 |
WSDK |
HELP WRONG IN LOGIN DIALOG WHEN INVOKED FROM THE FILE MENU. |
|
1339358 |
WSDK |
NULLS IN LOGIN DIALOG |
|
1337817 |
WSDK |
INCORRECT DIALOG BOX APPEARS WHEN PERFORMING A MIGRATION USING 8LITE AS DESTINAT |
|
1333418 |
SYBASE |
SQL LOADER SCRIPTS |
|
1332316 |
WSDK |
SWITCHING BETWEEN ORACLE8I AND DEFAULT REPOSITORY |
|
1331190 |
COMMON |
MIGRATION SCRIPTS ERROR CREATING SYNONYMS |
|
1331131 |
COMMON |
COSMETIC CHANGE REQUIRED TO TEXT ON STEP 3 OF 4: MIGRATE TABLE DATA |
|
1312972 |
COMMON |
CHECK SUCCESSFUL CREATION OF WORKBENCH (& PLUGIN SPECIFIC) REPOSITORY REQUIRED |
|
1292384 |
SQLSERVER |
AS USED IN DECLARING A VARIABLE CREATE PARSE EXCEPTION |
|
1289135 |
COMMON |
"MIGRATE TABLE DATA" FOR THE JOBS TABLE IN THE PUBS D/B CAUSES STACK TRACE. |
|
1281945 |
SYBASE |
CONVERT(DATATYPE,VARIABLE) CONVERSION DOES TAKE NUMERIC(18,2) INTO ACCOUNT. |
|
1271482 |
SYBASE |
UNABLE TO USE DIRECT PATH LOAD WITH DATE COLUMNS EXTRACTED UNSING BCP |
|
1269483 |
SYBASE |
$ IN PROCEDURE NAME CAUSES PARSE ERROR |
|
1263807 |
SYBASE |
TO_CHAR(TO_CHAR EXTRA TO_CHAR WHEN CONVERTING CONVERT(VARCHAR(8) |
|
1247759 |
COMMON |
NO LONGER ABLE TO GENERATE THE MIGRATION SCRIPTS AT A COMPONENT LEVEL |
|
1247723 |
SYBASE |
JAVA.LANG.ARRAYOUTOFBOUNDSEXCEPTION ON PARSING STORED PROCEDURE |
|
1246311 |
SYBASE |
TABLE REFERENCE REQUIRED ON ORDER BY CLAUSE |
|
1246310 |
SYBASE |
MISSING FROM CLAUSE IN SELECTS WHERE FROM IS NOT REQUIRED IN SYBASE |
|
1246288 |
SYBASE |
VARIABLE %FOUND REFERENCED INSTEAD OF SQL%FOUND WHEN IMPLICIT CURSOR REFERENCED. |
|
1246263 |
SYBASE |
RETURN 0 WHEN PROCEDURE ONLY IS REQUESTED [0 SHOULD BE REMOVED] |
|
1245844 |
INSTALLER |
GOTO STATEMENTS IN BATCH FILE DO NOT WORK |
|
1239263 |
SYBASE |
|| AND + NOT CONSISTENTLY USED IN CONVERTED STORED PROCEDURES |
|
1226363 |
SQLSERVER |
STUFF FUNCTION INCORRECTLY TRANSLATED |
|
1210060 |
SQLSERVER |
SQL LOADER DATE MASK IN SQL SERVER 6.5 PLUGIN IS INCORRECT |
|
1209955 |
COMMON |
IMPROVE HELP MENU |
|
1205082 |
SYBASE |
HAVE TO ENTER SA IN THE CAPTURE WIZARD |
|
1199985 |
COMMON |
FUNCTIONS ARE NOT TERMINATED BY / IN THE SCRIPT FILE |
|
1190475 |
SQLSERVER |
PARSE EXCEPTION - JAVA.LANG.NUMBERFORMATEXCEPTION. |
|
1188944 |
COMMON |
REMOVING A USER IN THE ORACLE MODEL DOES NOT WORK. |
|
1170360 |
COMMON |
THE "GENERATE MIGRATION SCRIPTS" OPTION WORKS ONLY ONCE PER SESSION. |
|
1170349 |
COMMON |
CANNOT CHANGE THE DEFAULT TABLESPACE FOR TABLES. |
|
1170323 |
COMMON |
WRONG INDEX GETS DELETED FROM THE ORACLE MODEL. |
|
1170315 |
COMMON |
THE DATA TYPE MAPPINGS GRID DECREASES WHEN YOU INCREASE THE COLUMN SIZE. |
|
1156645 |
COMMON |
MODIFICATIONS MADE IN THE ORACLE MODEL PANEL ARE NOT INTUITIVELY IMPLEMENTED. |
|
1104863 |
COMMON |
REPLACING OF NON COMPLIANT CHARACTERS WITH "_" CAN CAUSE DUPLICATE COLUMN NAMES |
|
1087423 |
COMMON |
WORKBENCH MAY NOT WORK AGAINST ORACLE MTS |
|
1022655 |
COMMON |
MIGRATE USERS AND TABLES - IS IT NESSISARY FIRST TIME ROUND |
|
1009275 |
SQLSERVER |
PRESERVE THE USER DEFINED TYPES WHERE POSSIBLE |
|
995869 |
SQLSERVER |
FAILED TO EXECUTE DDL STATEMENT: ORA-00906: MISSING LEFT PARENTHESIS |
|
992429 |
SQLSERVER |
PARAMETERS IN THE SET CLAUSE OF AN UPDATE STATEMENT BREAK THE CONVERTER |
|
983437 |
COMMON |
UNABLE TO VIEW LOG FILES CREATE<TIME-STAMP>.SQL ETC WHILE WORKBENCH IS RUNNING |
|
982410 |
SYBASE |
PARSE ERROR ON ENCOUNTERING THE STRING "CHARACTER" IN A WHERE CLAUSE |
|
981087 |
SQLSERVER |
CAN'T SELECT FROM NCHAR COLUMN AFTER DATA MIGRATION |
|
977411 |
SQLSERVER |
FAILED TO LOAD SOURCE MODEL. ORA-01008: NOT ALL VARIABLES BOUND |
|
974670 |
SYBASE |
IMPLEMENT EQUIVALENT OF SYBASE STUFF() FUNCTION IN PL/SQL |
|
974600 |
SYBASE |
TRANCOUNT |
|
963025 |
SQLSERVER |
SELECT X FROM (SELECT ..) AS FRED DOES NOT PARSE |
|
954865 |
SYBASE |
CURSOR DECLARATION IS NOT CORRECTLY CONVERTED |
|
954848 |
SQLSERVER |
THE CONVERT FUNCTION IS NOT CORRECTLY CONVERTED FOR INT DATATYPE |
|
941989 |
SYBASE |
DEFAULTS ARE NOT APPLIED CORRECTLY TO THE NECESSARY COLUMNS IN MOST CASES |
|
933048 |
SQLSERVER |
VERY COMPLEX JOIN CREATE PARSE ERRORS |
|
933025 |
SQLSERVER |
LINE COMMENT IS SOMETIMES COMBINED WITH NEXT LINE ON CAPTURE OPERATION |
|
932990 |
SQLSERVER |
COMPLEX OUTER JOINS SHOULD ONLY BE SIGNALLED ON COMPLEX CASES |
|
932981 |
COMMON |
EDITOR HAS NO LINE NUMBERS |
|
927157 |
COMMON |
SAME TABLE APPEARS MULTIPLE TIMES IN DEPENDENCY LIST |
|
922465 |
SQLSERVER |
SQLSERVER RESERVERED WORDS CANNOT BE USED AS FREELY AS THEY CAN IN SQLSERVER |
|
911748 |
SQLSERVER |
SEQUENCES AND TABLES GENERATED FOR TIMESTAMP COLUMNS DO NOT HAVE TABLE NAME |
|
902404 |
COMMON |
NEED A PROGRESS DIALOG WHEN REPOSITORY IS BEING CREATED |
|
869502 |
SQLSERVER |
TEMPORARY TABLES WITH PRIMARY KEY BREAK THE CONVERTER |
|
764561 |
SQLSERVER |
THE GLOBAL VARIABLE @@IDENTITY IS NOT SUPPORTED |
|
713863 |
COMMON |
PROVIDE LIST OF DATABASES IN LOGIN DIALOG |