|Oracle Migration Workbench Frequently Asked Questions (FAQ)
Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT
Part Number A97247-01
This chapter contains frequently asked questions about using the Oracle Migration Workbench release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT. It contains the following sections:
For database platform specific questions, see the following chapters:
Use the Oracle Migration Workbench release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT to migrate to Oracle9i or Oracle8i, from the following database platforms:
The questions in this chapter apply to all of these database platforms.
This section contains Migration Workbench pre-installation questions.
The Migration Workbench supports the following Oracle Server releases:
The Migration Workbench runs on the following platforms:
The Migration Workbench runs on Windows NT and Windows 98/2000. However, you can use the Migration Workbench to migrate from a supported source database to an Oracle9i or Oracle8i database, regardless of the operating system platform of either database. To do this successfully, you must have a JDBC/ODBC connection to the source database and a SQL*Net connection to the destination Oracle database. However, if the Migration Workbench is remote to the source and destination databases, you should use the offline data loading capability when you migrate the data.
Yes. You can download the latest release of the Migration Workbench, patches, upgrades, support documentation, and this FAQ from the OTN Web site at:
The firewall that the company you work for might prevent you from accessing Oracle Technology Network (OTN). Contact the system administrator to see if there is a restriction that prevents you from downloading files outside of the company firewall.
If the maximum number of connections available on the Oracle Server is reached, you cannot download the Migration Workbench from the Oracle Technology Network (OTN) Web site. Keep reloading the page until the Save As dialog box appears, then save the Migration Workbench.
If the Save As dialog box does not appear, send an email message to the Migration Workbench support team at firstname.lastname@example.org.
These messages appear if the Migration Workbench installation is incomplete, if the DOS window Command Prompt Properties memory setting is too low, or if there are limitations in Windows 98 that prevent the Migration Workbench
omwb.bat BAT file from successfully executing. To correct these problems:
%ORACLE_HOME%/bin/omwb_copy.bat. Use this copy if you need to restore the original file.
%ORACLE_HOME%/bin/omwb.batfile, locate the path for your source database.
Towards the bottom of the file there is a line beginning with
Below this line, there is a corresponding path for each plug-in. The following is the path for SQL Server 6:
For information on error messages, refer to the Troubleshooting section of the Oracle Migration Workbench Online Help.
Use the Migration Workbench for one-time migrations to an Oracle Server because it migrates all table data. You cannot migrate only the differences between several migrations.
Use the Oracle Transparent Gateway if you want to have links to other heterogeneous databases.
The Migration Workbench has not been tested for NLS support. However, the Migration Workbench has successfully migrated databases with non-English language characters. If you use the Migration Workbench to migrate data or schema object names that contain non-English language characters, you should verify that all data has migrated correctly.
Yes. If you have an object with a space in the name, convert the space to an underscore (_) by putting it in square brackets . The Migration Workbench can then migrate the object.
The Migration Workbench uses weak REF CURSORS. You can alter these to use packages and strong REF CURSORS. To improve the readability of the migrated application, release 9.2.0 of the Migration Workbench supports Weak Ref Cursors.
This section contains Migration Workbench installation and configuration questions.
No. The Migration Workbench contains Oracle9i release 1 dependencies. Therefore, you can only install the Migration Workbench into an Oracle9i release 1 home directory or into a new Oracle home directory. If you install the Migration Workbench into a new Oracle home directory, and there is already an Oracle server installed on the system you must launch the Home Selector from the Oracle Installation Products program group, to switch the default Oracle home directory to specify the previous Oracle installation. You must do this so that your system does not pick up the new client dependencies for other applications that you might use on the system.
If you attempt to install the Migration Workbench into an Oracle home directory that contains an Oracle Server release earlier than Oracle9i, the Oracle Universal Installer displays the following error message:
The Migration Workbench can only be installed into a new Oracle home directory or an existing Oracle9i Oracle home directory.
You can create the Oracle Migration Workbench repository in an Oracle9i, Oracle8i, or Oracle8 server. To create a new Oracle Migration Workbench repository:
The first time you log in to this user account, you are prompted to create the Oracle Migration Workbench repository.
If the Migration Workbench Repository fails, you might receive one of the following error messages:
was unable to successfully create the repository. Check the Oracle alertORACLE_SID
.log for errors. Failed to create aMigration Workbench
was unable to successfully create the repository. Check the Oracle alertORACLE_SID
.log for errors.
The Migration Workbench creates an incomplete repository when it is unable to create all of the required tables. To fix an incomplete repository, recreate a new Migration Workbench repository.
You must drop the user in the destination Oracle database, then re-create the user in the destination Oracle database where you want to store the repository. The first time you log in to the newly created user account, you are prompted to create the repository.
After you install the Migration Workbench, but before you run it, you must set up the
tnsnames.ora configuration file. You can do this manually or through the Net8 Configuration Assistant. Launch the Net8 Configuration Assistant, then follow the instructions in the wizard and the online help.
This may occur because the Oracle Java Runtime Environment has been installed into a non-default location. The default location is specified in the
omwb.bat file in the Oracle Migration Workbench Oracle home. To resolve this issue open the
omwb.bat file in a text editor and change the jre directory path to point to the correct jre file location.
This may occur because there are two instances of the same Oracle home directory in the
tnsnames.ora file. To resolve this issue open the
tnsnames.ora file, and remove the second instance of the Oracle home
This section contains Migration Workbench data migration questions.
Yes. You can select a table in the Migration Workbench and migrate the data for that table only. To migrate single table data, select Object > Migrate Table Data.
The Migrate Table Data option is enabled after you have used the Migration Wizard to create the users and tables in the destination Oracle database.
When you have created the Oracle Model, you can modify column types from the individual table you created or from all tables. Do this from the General tab of the Property sheet.
Yes. By default, the Migration Wizard first creates the users and their tables, then loads the data, and finally creates all constraints. This avoids problems. It is possible to migrate the database to an Oracle database in stages. For example, you can first create the users and their tables, then create the constraints, and finally load the data. However, doing this might cause problems as a result of referential integrity. Therefore, Oracle Corporation recommends the default implementation.
Duplicate object names are appended with an underscore (_) followed by a number.
Use the following object naming guidelines when you are naming databases or tablespaces:
If a column name is an Oracle Server reserved word, the Migration Workbench appends an underscore (_) when you create the object within an Oracle database. You can use ANSI names for objects by choosing Tools>Options, then selecting Create ANSI-compliant names from the General page. This means that all object names are surrounded by double quotes when you use the Migration Wizard to create them in the destination Oracle database.
If you are using words that are not official T-SQL reserved words, but that have meaning in T-SQL syntax, you must enable the parser to recognize these words. Select the Allow 'Reserved Words' in table names option from the Parse Options tab within the Stored Procedures. You can enable this for a specific stored procedure or for all stored procedures.
For more information on schema object names, see Schema Object Similarities in the Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations.
Datatypes that the Oracle Server does not support are mapped to Oracle data types that encapsulate similar type characteristics. To view or edit the data type mappings, do one of the following:
ORA-00903 - Invalid table name
ORA-00904 - Invalid column name
ORA-00942 - Table or view does not exist
These messages can appear in the Migration Workbench UI and error log file while you load the source database into the Migration Workbench or while you create the Oracle Model.
If you receive one of the previous error messages while you load the source database, you may have an incomplete repository. Incomplete repositories occur when the Migration Workbench is unable to create all of the required tables. This is caused when the tablespace where the migration schema resides is created with incorrect or missing storage options. It can also occur if there are any other problems, such as space limitations, with the tablespace.
To verify a complete repository within the Migration Workbench, make sure that there are 166 tables listed for all available plug-ins. Enter the following:
To resolve an incomplete repository within the Migration Workbench:
You should monitor the
.log Oracle Alert log file for the database on an ongoing basis. This log file contains all error messages generated by the destination Oracle database and indicates any tablespace problems.
If you receive on of the preceding error message while you create the Oracle Model, you may have an incomplete source database. An incomplete source databases can occur if you imported the source Microsoft SQL Server database from another Microsoft SQL Server database without importing the master database. The master databases for incomplete source databases are out of sync.
The master database stores all the security information for a Microsoft SQL Server database. This security information, including the ownership of objects, is mapped to the individual databases within the SQL Server. In order to maintain the security information, you must also move the master database. You can resolve an incomplete source database by importing the correct master database into the correct SQL Server.
The Migration Workbench does not support the mapping of NCHAR, NVARCHAR, NTEXT, or MEMO data types from Microsoft SQL Server, Sybase Adaptive Server, or Microsoft Access to the NCHAR, NVARCHAR2, or NCLOB data types in an Oracle database. 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.
No. The Migration Workbench does not use SQL*Loader control files when loading the data from the source database to the destination Oracle database. However, you can choose the Object>Generate SQL*Loader Scripts option to create the SQL*Loader control files in the
Yes. To save a SQL*Loader control file:
You can then update the file at any stage.
Yes. This is a known problem in the data migration component of the Migration Workbench. You can create a data pump using JDBC/ODBC. The
%ORACLE_HOME%/jdbc/demo directory contains a demonstration data pump.
You can not use offline data loading for Sybase Adaptive Server and Microsoft SQL Server migrations because SQL*Loader cannot load binary data generated by BCP.
Choose Object > Generate SQL*Loader Scripts from the Migration Workbench to use the offline data loading option.
To overcome this problem, do one of the following:
This error message means that a double value in the source database is outside the allowable range for the Oracle Server. In this case, the Migration Workbench migrates all other columns in the row as normal and migrates a null value for the column that contains the double value. The error message in the Log window indicates the table and the row number affected.
If you receive this message, you have been disconnected from the server-side processes. This can occur for several reasons. For example, you may have a problem that is associated with a data move. Locate the last table that was moved before the error occurred. Attempt to migrate the table again. If you are disconnected again, report the problem by sending an email message to email@example.com.
You receive this error message if you do not obey the foreign key constraint. When you use the Migration Workbench, you should load the data for the parent table before the data for a referenced table in a foreign key relationship. By loading the reference table data first you create orphaned child records. An Oracle database requires that the column specified in the reference table be either a primary key or unique index. You should make sure that the table has no orphaned child records before issuing an
ALTER TABLE ENABLE CONSTRAINT command.
For more information, see Table Design Considerations in the Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations.
You can use JDBC, Oracle ODBC drivers, Oracle OLEDB, and third-party ODBC and OLEDB drivers to access client applications after you have migrated the source database to an Oracle database. Download the latest version of the Oracle drivers from the OTN Web site at:
No. To use explicit date conversion (TO_DATE), do one of the following:
No. The Migration Workbench does not support the migration of individual schema objects.
For more information on schema objects, see Schema Object Similarities in the Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations and the Oracle Migration Workbench Reference Guide for Informix Dynamic Server 7.3 Migrations.
Yes. You can rename tablespaces and users in the Oracle Model. Choose Object>Rename or click the right mouse button, then select Rename. Enter the new name of the tablespace or user.
An Oracle database stores all schema values in upper-case. Oracle Corporation recommends that you do not maintain case-sensitivity. However, the Migration Workbench enables you to preserve case-sensitivity schema object names. Choose Tools>Options, then click the Create ANSI-compliant names option from the General tab. Selecting this option creates all schema values using quotes. This ensures case-sensitivity of the tables and columns.
The Oracle Server evaluates equality in a case-sensitive manner. This means that you can use uppercase values and uppercase fields for comparisons. You can use UPPER functions to simulate case-insensitivity. However, you must apply this to every SQL statement. Oracle9i and Oracle8i have functional indexes so you can manage indexed fields.
Yes. You can select the databases that you want to migrate through one of the steps in the Capture Wizard. Choose Action>Capture Source Database to access the Capture Wizard.
Yes. Oracle9i and Oracle8i allow multi-character field delimiters.
To modify the storage options for tables and indexes before running the Migration Wizard:
Doing this sets the options for all tables in the Oracle Model.
To modify the default creation options for a particular table:
Yes. After migrating to the Oracle Server, you must manually edit the table defaults containing dates and bit-wise operators.
By default, the Migration Workbench uses the temporary tables feature of Oracle9i. When the parser encounters SQL statements such as
CREATE #TABLE in a stored procedure, the parser explicitly creates a temporary table. When the parser encounters SQL statements such as
INSERT INTO #TABLE in a trigger or stored procedure, it implicitly creates a temporary table. An implicit creation is required when a
CREATE TABLE statement cannot be found.
These creation (DDL) statements are associated with stored procedures and triggers and are executed by the Migration Wizard before the stored procedures or triggers are created. Clashes can arise when the parser creates multiple copies of the same DDL statement. The clashes are written to the Log window and you can ignore them.
This section contains support questions.
You can get help on Migration Workbench from the following sources:
All error messages are logged to the Log window and the columns of the Log window can be sorted by clicking on the column header. During the creation phase of the migration, the Migration Wizard creates three log files that record all successful
CREATE statements and
ALTER statements, the equivalent
DROP statements and
ALTER statements, and all SQL statements that the Migration Workbench was unable to execute. The location of these log files is governed by the log file directory setting in the Logging page of the Options dialog box. The default location is
%ORACLE_HOME%\omwb\log. If the Migration Workbench fails to respond, check the contents of the
%ORACLE_HOME%\Omwb\log\Error.log file to see if there are any error messages recorded. The contents of this file can be sent to the Migration Workbench development team at firstname.lastname@example.org. You can also generate reports from the Migration Workbench. This provides you with a formatted list of the error messages.