1
Oracle Migration Workbench Frequently Asked Questions

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:

• Overview
  
  
• Installation and Configuration
  
  
• Data Migration
  
  
• Support

  See Also: For database platform specific questions, see the following chapters: Chapter 2, "Informix Dynamic Server Questions" Chapter 3, "Microsoft Access Questions" Chapter 4, "Microsoft SQL Server and Sybase Adaptive Server Questions" Chapter 5, "MySQL Server Questions"

Overview

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:

• Informix Dynamic Server 7.3
  
  
• Microsoft Access 2.0, 95, 97, 2000
  
  
• Microsoft SQL Server 6.5, 7.0, 2000
  
  
• Sybase Adaptive Server 11, 12
  
  
• MySQL 3.22, 3.23

The questions in this chapter apply to all of these database platforms.

Pre-Installation

This section contains Migration Workbench pre-installation questions.

What release of the Oracle Server does the Migration Workbench support?

The Migration Workbench supports the following Oracle Server releases:

• Oracle8i release 3 (8.1.7)
  
  
• Oracle9i release 1 (9.0.1)

What platforms does Oracle Migration Workbench run on?

The Migration Workbench runs on the following platforms:

• Windows NT 4.0 (Service Pack 5 minimum)
  
  
• Windows 98
  
  
• Windows 2000

Does the Migration Workbench support UNIX?

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.

Can I download the Migration Workbench from the Web?

Yes. You can download the latest release of the Migration Workbench, patches, upgrades, support documentation, and this FAQ from the OTN Web site at:

http://otn.oracle.com/tech/migration/workbench

Why am I not able to access Oracle Technology Network (OTN)?

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.

What should I do if I cannot download the Migration Workbench from Oracle Technology Network (OTN)?

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 infomwb_ww@oracle.com.

Why do I receive a blank DOS window or one of the following error messages while launching the Migration Workbench from Windows 98?

• Out of environment space
  
  
• Bad command or file name

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:

• Change the Command Prompt Properties memory setting:
  
  
  1. Choose Start > Programs > Command Prompt.
     
     
  2. Right-click on the title bar of the DOS window and choose Properties...
     
     
  3. Select the Memory tab.
     
     
  4. Change the Initial environment property from Auto to 1024.
     
     
  5. Click Apply.
     
     
  6. Click OK to accept the changes to the memory settings.
     
     
  7. Click OK.
     
     
  8. Start the Migration Workbench.
• If you changed the DOS window Command Prompt memory setting and you still experience problems launching the Migration Workbench, do the following:
  
  
  1. Copy the %ORACLE_HOME%/bin/omwb.bat file to %ORACLE_HOME%/bin/omwb_copy.bat. Use this copy if you need to restore the original file.
     
     
  2. In the %ORACLE_HOME%/bin/omwb.bat file, locate the path for your source database.

     Towards the bottom of the file there is a line beginning with %JRE% -classpath.

     Below this line, there is a corresponding path for each plug-in. The following is the path for SQL Server 6:

     D:\ora816\Omwb\plugins\SQLServer6.jar;
     
     

  3. Delete the paths for all plug-ins except the one that corresponds to the database you are migrating.
     
     
  4. Save the omwb.bat file.

Where can I find out more about Oracle Migration Workbench error messages?

For information on error messages, refer to the Troubleshooting section of the Oracle Migration Workbench Online Help.

What is the difference between Oracle Transparent Gateway (OTG) and the Migration Workbench?

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.

Does the Migration Workbench provide NLS support?

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.

Does the Migration Workbench support object names with spaces in their names?

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.

Does the Migration Workbench use strong REF CURSORS?

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.

Installation and Configuration

This section contains Migration Workbench installation and configuration questions.

Can I install the Migration Workbench into an Oracle home directory that contains a release of the Oracle Server earlier than Oracle9i?

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.

How do I create a new Oracle Migration Workbench repository?

You can create the Oracle Migration Workbench repository in an Oracle9i, Oracle8i, or Oracle8 server. To create a new Oracle Migration Workbench repository:

1. Use Oracle DBA Studio, Oracle Enterprise Manager, or Oracle SQL*Plus to create a user in the destination Oracle database where you want to create the Oracle Migration Workbench Repository.

   The first time you log in to this user account, you are prompted to create the Oracle Migration Workbench repository.

2. In the Oracle Migration Workbench Repository Login dialog box, enter the user name, password, and service where you want to create the repository.

What should I do if the Migration Workbench Repository creation fails?

If the Migration Workbench Repository fails, you might receive one of the following error messages:

• The Migration Workbench was unable to successfully create the repository. Check the Oracle alertORACLE_SID.log for errors. Failed to create a Migration Workbench repository.
  
  
• The Migration 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.

How do I re-create an Oracle 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.

How do I create a tnsnames.ora file?

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.

What should I do if I cannot launch the Oracle Migration Workbench? I attempt to do so but nothing happens.

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.

I attempt to launch the Oracle Migration Workbench and the splash screen appears then quickly disappears. Why does this happen?

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 %ORACLE_HOME%/omwb/network/admin/tnsnames.ora file, and remove the second instance of the Oracle home

Data Migration

This section contains Migration Workbench data migration questions.

Can I migrate single table data?

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.

Can I change the data type of a single column?

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.

Are constraints disabled when loading the data?

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.

How are duplicate object names handled?

Duplicate object names are appended with an underscore (_) followed by a number.

What are the object naming guidelines for the Migration Workbench?

Use the following object naming guidelines when you are naming databases or tablespaces:

• Use upper-case characters.
  
  
• Begin the name with an alphabetic character.
  
  
• Use only alphanumeric characters from the database character set and an underscore (_).
  
  
• Do not include quotation marks in the name.

How does the Migration Workbench handle reserved words?

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.

How does the Migration Workbench migrate datatypes that the Oracle Server does not support?

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:

• Choose Tools Menu > Options, then click the Data Type Mappings tab.

  See Also: For more information, see the Migration Workbench Online Help.

• Access the step within the Capture Wizard that permits you to view and edit the data type mappings.

  See Also: For more information, see Step 3: Data Type Mappings in the Oracle Migration Workbench Online Help.

• Access the General tab of the property sheet after you have created the Oracle Model to modify column types from the individual table you created or from all tables.

  See Also: For more information on data types, see the Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations.

What do the following error messages mean?

• 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.

Incomplete Oracle Migration Workbench Repository

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:

sqlplus migration users/password@database service name select table_name from 
user_tables;

To resolve an incomplete repository within the Migration Workbench:

1. Create a new tablespace using the default options provided by Oracle Enterprise Manager. Make sure that you have sufficient disk space.
   
   
2. In the tablespaces, create a new user and schema.
   
   
3. Log into the Migration Workbench as the new user. The Migration Workbench automatically creates the new repository.

You should monitor the %ORACLE_HOME%\admin\oracle_SID\bdump\alertoracle_SID.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.

Incomplete Source Database

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.

How are NCHAR, NVARCHAR, NTEXT, or MEMO data types mapped within the Oracle 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.

Does the Migration Workbench use SQL*Loader to control files for loading data?

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 %ORACLE_HOME%\Omwb\sqlloader_scripts\timestamp directory.

Can I modify the SQL*Loader control files for loading the data?

Yes. To save a SQL*Loader control file:

1. Use the offline data loading method.
   
   
2. Choose the Object > Generate SQL*Loader Scripts option within the Migration Workbench.
   
   
3. Save the file

You can then update the file at any stage.

Does the data migration process slow down dramatically when migrating tables that contain large volumes of binary data, such as images, binary data, or OLE objects?

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.

What should I do if I receive the error "ORA-22866: default character set is of varying width" when migrating to a destination Oracle database created with the UTF8 character set?

To overcome this problem, do one of the following:

• Migrate to an Oracle9i database.
  
  
• If you are migrating to an Oracle8i database, set the COMPATIBLE initialization parameter as follows:
  
  

  compatible=8.1.0.
  

What is the meaning of the error message "ORA-01426: numeric overflow" in the Log window?

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.

What does the error "Hostdef does not exist" mean?

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 infomwb_ww@oracle.com.

Why do I receive the "ORA-02298: parent key not found" error message?

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.

How do client applications access the data that has been migrated from my source database to an Oracle database?

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:

http://otn.oracle.com/software/index.htm

Does the Oracle Server have implicit date conversion from a character string?

No. To use explicit date conversion (TO_DATE), do one of the following:

• Enter the following command within an Oracle session:
  
  

  $ alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'
  

• Set the NLS_DATE_FORMAT environment variable:
  
  

  $ set NLS_DATE_FORMAT=dd.mm.yyyy hh24:mi:ss
  
  

Can I migrate a single schema object?

No. The Migration Workbench does not support the migration of individual schema objects.

Can I rename a schema object?

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.

Can I maintain schema object name case-sensitivity when migrating?

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.

How do I manage case-sensitivity queries?

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.

Can the Migration Workbench migrate multiple databases?

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.

Does the Migration Workbench allow multi-character field delimiters?

Yes. Oracle9i and Oracle8i allow multi-character field delimiters.

How do I modify the storage options for a table?

To modify the storage options for tables and indexes before running the Migration Wizard:

1. In the Oracle Model, select the Tables container.
   
   
2. Go to the Default Storage Options tab of the Tables property sheet.
   
   
3. Make the changes that you require and click Apply.

   Doing this sets the options for all tables in the Oracle Model.

To modify the default creation options for a particular table:

1. Expand the Tables container in the Navigator Pane
   
   
2. Select the table that you want to modify.
   
   
3. Make the changes that you require in the Override Default Creation Options tab of the property sheet for that table.

Do I need to edit table defaults?

Yes. After migrating to the Oracle Server, you must manually edit the table defaults containing dates and bit-wise operators.

How does the Oracle Server handle temporary tables?

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.

Support

This section contains support questions.

How do I get help on Migration Workbench issues?

You can get help on Migration Workbench from the following sources:

• The Migration Workbench documentation. This consists of the Oracle Migration Online Help, an on-line platform-specific reference guide, the Oracle Migration Workbench FAQ, and release notes.
  
  
• Technical bulletins. These bulletins are available at the support page of the Migration Workbench. Choose Help > Online Support from within the Migration Workbench to obtain an updated list of support facilities.
  
  
• The latest discussion forum for the Migration Workbench on the OTN Web site at:
  
  

  http://otn.oracle.com/support/bboard/discussions.htm?Forum&10
  
  

• Support information on the Migration Workbench from the OTN Website:
  
  

  http://otn.oracle.com/tech/migration/workbench
  
  

• Support by email at infomwb_ww@oracle.com.

How do I know what error messages occurred when running the Migration Wizard?

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 infomwb_ww@oracle.com. You can also generate reports from the Migration Workbench. This provides you with a formatted list of the error messages.