Skip Headers
Oracle® Migration Workbench User's Guide
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86
Part No. B15857-01
  Go To Table Of Contents
Contents		 Go To Index
Index
Previous
Previous 		Next
Next 		 

3 Before You Migrate

This chapter describes the tasks that you must perform before using Migration Workbench to migrate a third-party database to an Oracle database. These tasks are listed by plug-in for each third-party database that you can migrate.

This chapter contains the following sections:

Configuring Migration Workbench

Regardless of the third-party database that you are migrating, verify that you have the latest version of Migration Workbench. You can download Migration Workbench and the appropriate plug-in for the third-party database you want to migrate from the Oracle Technology Network Web site at: 

http://www.oracle.com/technology/tech/migration/workbench/index.html

Note:

Oracle recommends that before using Migration Workbench, make a complete backup of the source database. For more information about backing up the source database, see the documentation supplied with the specific source database.

Creating a Database User for the Workbench Repository

Migration Workbench requires a repository to migrate a third-party database to an Oracle database. To use an Oracle database for the Workbench repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to use REPUSER, which is the Workbench repository user. You can later delete the REPUSER user and remove all traces of the migration from the database. Oracle recommends that you do not use a standard account, (for example, system) for migration.

To create the REPUSER user:

  1. Log on to the system with the Oracle database you want to use for the Workbench repository.

    You must connect to the Oracle database to create the REPUSER database user that contains the Workbench repository. The user connecting to the Oracle database must have DBA privileges to the Oracle database in order to create the REPUSER account.

  2. Start SQL*Plus using the following command, where password is the password for the database user: 

    prompt> sqlplus SYSTEM/PASSWORD

    A message similar to the following appears: 

    Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

  3. Enter the following commands to create the REPUSER user, and set the user password to REPUSER: 

    SQL> CREATE USER REPUSER IDENTIFIED BY REPUSER;

    The following message appears: 

    User Created.

  4. Assign permissions to the REPUSER user as follows: 

    SQL> GRANT CONNECT,RESOURCE TO REPUSER;

    The following message appears: 

    Grant succeeded.

Configuring the Plug-in

Depending on the third-party database that you are migrating to an Oracle database, you may have to configure connection information and install drivers. For more information about specific third-party database requirements, see the following sections:

Before Migrating From Microsoft SQL Server

To configure a Microsoft SQL Server database for migration:

  1. Ensure that the source database is accessible by the Microsoft SQL Server user that is used by Migration Workbench. This user must have DBA access to the Microsoft SQL Server database.

  2. Ensure that you can connect to the Microsoft SQL Server database from the system where you have installed Migration Workbench.

  3. Install the Microsoft SQL Server ODBC driver release 3.70.06.23 or higher installed on the system as Migration Workbench, as follows:

    The Microsoft SQL Server ODBC driver is shipped with the Microsoft SQL Server client software. Installation procedures depend on the version of Microsoft SQL Server. For more information about the Microsoft SQL Server ODBC driver, see the Microsoft SQL Server specific documentation.

  4. Set the Oracle ODBC Data Source as follows:

    The Oracle ODBC Data Source is used to set up Data Source Names (DSN). You can create a DSN at the user or system level.

    1. From the Start option, select Settings, then select Control Panel.

      The Control Panel window appears.

    2. If you are using Microsoft Windows 2000, select the Administrative Tools icon.

    3. Select the Data Source (ODBC) icon.

      The ODBC Data Source Administrator dialog appears.

    4. From the User DSN tab, click Add.

      The Create New Data Source dialog appears.

    5. Select SQL Server, then click Finish.

      The Create a New Data Source to SQL Server dialog box appears.

    6. Type a name for the data source in the Name field.

    7. From the drop-down list, select the SQL Server database you want to connect to or type it in, then click Next.

    8. Verify the authenticity of the login ID, then click Next.

    9. Review the options, then click Next.

    10. Click Next.

    11. Click Finish.

      The ODBC Microsoft SQL Server Setup page appears.

    12. Click the Test Data Source option to ensure that you have set up the ODBC connection correctly, then click OK.

      If the connection fails, return to Step d and modify the connection details.

    13. Click OK.

      The DSN connection is now created.

Before Migrating From Sybase Adaptive Server

You can migrate Sybase Adaptive Server to Oracle using Migration Workbench for Windows or Linux x86. Depending on the platform, use the appropriate procedure to migrate:

Configuring Sybase Adaptive Server on Windows

To configure a Sybase Adaptive Server database for migration:

  1. Install the Sybase Client software on the system where you have installed Migration Workbench.

  2. Ensure that you can connect to the Sybase Adaptive Server database from the system where you have installed Migration Workbench.

  3. Install the Sybase Adaptive Server ODBC driver Release 3.11.00.01 or higher on the same system as Migration Workbench.

    The Sybase Adaptive Server ODBC driver is shipped with the Sybase Adaptive Server 11 and Sybase Adaptive Server 12 client software. Installation procedures depend on the version of Sybase Adaptive Server.

  4. Configure the Sybase Adaptive Server ODBC Data Source, as follows:

    You can create a DSN at the user or system level.

    1. From the Start option, select Settings, then select Control Panel.

      The Control Panel window appears.

    2. If you are using Microsoft Windows 2000, select the Administrative Tools icon.

    3. Select the Data Source (ODBC) icon.

      The ODBC Data Source Administrator dialog appears

    4. From the User DSN tab, click Add.

      The Create New Data Source dialog appears.

    5. Select Sybase System 11 or Sybase System 12, then click Finish.

      The ODBC Sybase Driver Setup dialog box appears.

    6. Type a name for the ODBC data source in the Data Source Name field.

    7. Type a server name where the Sybase Adaptive Server is installed in the Server Name field, then click OK.

    8. Click OK.

      The Sybase ODBC Data Source connection is now created.

  5. Set up the server address for the Sybase Adaptive Server data source.

    1. From the Sybase Adaptive Server dsedit program, select a directory service, then click OK.

    2. Select the server address.

    3. From the Server Object menu, select Modify Attribute.

      The Network Address Attribute dialog box appears.

    4. Ensure the protocol network address is correct.

    5. Click OK.

Configuring Sybase Adaptive Server on Linux x86

To configure a Sybase Adaptive Server database for migration:

  1. Verify that the Sybase Adaptive Server jConnect 5.5 for JDBC driver is located in the OMWB_install_dir/Omwb/drivers directory.

  2. If necessary, install the jConnect 5.5 for JDBC driver as follows:

    1. Download jConnect 5.5 for JDBC from the Sybase Adaptive Server Web site at: 

      http://www.sybase.com

    2. Extract the jconnect55.zip file to a temporary directory.

  3. Copy the Sybase JAR files, jTDS2.jar and jconn2.jar, to the following directory: 

    OMWB_install_dir/Omwb/drivers

  4. Run the jConnect for JDBC script by typing a command similar to the following: 

    java isqlApp -U sa -P password -S jdbc:sybase:Tds:hostname:port -I$JDBC_HOME/sp/sql_server.sql -c go

    In the previous command, replace the password, hostname, port, classpath, and $JDBC_HOME variables with the appropriate values for the source database. For more information about jConnect for JDBC 5.5, see the Sybase Adaptive Sever documentation at the following Web site: 

    http://sybooks.sybase.com/jc.html

Before Migrating From Microsoft Access

To configure a Microsoft Access database for migration:

  1. Make a back up of the Microsoft Access database files.

  2. If you are migrating Microsoft Access 2.0, 95, or 97, ensure the following is installed on the same system as Migration Workbench:

    • Microsoft Access 97

    • Microsoft ODBC driver release 4.00.4202.00 or greater

      You can obtain the Microsoft ODBC driver by downloading the latest version of the Microsoft Data Access Components (MDAC) from the following Web site at: 

      http://www.microsoft.com/data/download.htm

  3. If you are migrating Microsoft Access 2000, ensure the following is installed on the same system as Migration Workbench:

    • Microsoft Access 2000

    • Microsoft ODBC driver release 4.00.4403.00 or greater

      You can obtain the Microsoft ODBC driver by downloading the latest version of the MDAC from the following Web site at: 

      http://www.microsoft.com/data/download.htm

  4. If security is enabled, you should turn it off by copying the contents of the secured database into a new database, as follows:

    Migration Workbench does not support the migration of Microsoft Access databases that have security enabled. By default, Migration Workbench uses the name of the Microsoft Access MDB file as the user name for the destination Oracle user. If you create an Oracle user in this way, the password is ORACLE.

    1. From the File menu in Microsoft Access, select New Database.

    2. Select the Blank Database icon, then click OK.

    3. In the File New Database option, type a name for the database, then click Create.

    4. From the File menu within the new database, select Get External Data, then select Import.

    5. Select the secured Microsoft Access database that you want to import, then click Import.

    6. From the Import Objects dialog, click Options.

    7. Select the Relationships and Definition and Data options.

    8. From the Tables tab, choose Select All.

    9. Click OK.

      All Microsoft Access objects are copied over to the new Microsoft Access database, except for the security settings.

  5. If the application contains linked tables to other Microsoft Access databases, refresh these links by opening the application in Microsoft Access and performing the following:

    From the Tools menu in Microsoft Access 97, select Add Ins, then select Linked Table Manager.

    From the Tools menu in Microsoft Access 2000, select Database Utilities, then select Linked Table Manager.


    Note:

    Migration Workbench supports linked tables to other Microsoft Access databases. Migration Workbench automatically creates a user schema within the Oracle database for each Microsoft Access database migrated. However, Oracle recommends that you move all tables into a single Microsoft Access database to ensure that a single user schema is created in the destination database.

  6. If you are migrating to an Oracle Lite database, you must rename the Oracle Lite database DSN to POLITE and restart the system.

  7. Ensure that the Microsoft Access database is not a replica database, but a master database.

    When you use the Oracle Migration Workbench Exporter for Microsoft Access to export, an error message is displayed if the database is a replica. Migration Workbench does not support the migration of a replica Microsoft Access database.

  8. From the Tools menu within Microsoft Access, select Database, then select Compact Database to compact the Microsoft Access database files.

  9. Ensure that the Microsoft Access MDB file is accessible from the system where you have installed Migration Workbench.

  10. Use the Oracle Universal Installer to verify that you have the Oracle ODBC driver installed. If you need to install the driver, it is available on the Oracle Database Server or Database Client CD. You can also download the Oracle ODBC driver from the following sections on the Oracle Technology Network (OTN) Web site: 

    http://www.oracle.com/technology/software/tech/windows/odbc/

    You install the Oracle ODBC driver to allow Microsoft Access forms and reports to work with the migrated data. Install the Oracle ODBC driver into an Oracle home directory that contains the Oracle Net Services. You can obtain the Oracle Net Services from the Oracle9i Client or Oracle9i Database CD. You install Oracle Net Services to obtain the Net Configuration Assistant and Net Manager. These allow you to create a net configuration in the tnsnames.ora file.


    Note:

    For more information about installing the networking products needed to connect to an Oracle database, see the Oracle9i Installation Guide.

  11. Set the Oracle ODBC Data Source in the ODBC Data Source Administrator, as follows:

    1. From the Start option, select Settings, then select Control Panel.

      The Control Panel window appears.

    2. If you are using Microsoft Windows 2000, select the Administrative Tools icon.

    3. Select the Data Source (ODBC) icon.

      The ODBC Data Source Administrator dialog appears

    4. From the User DSN tab, click Add.

      The Create New Data Source dialog box appears.


      Note:

      If you want to use the data source name (DSN) for multiple users on the same system, you should create a System DSN instead of a User DSN.

    5. Select the Oracle ODBC Driver from the list of ODBC drivers installed on the system, then click Finish.

    6. In the Data Source Name field, type a name to the ODBC data source.

      This data source name is the ODBC Data Source that you must specify in the Modify Microsoft Access Database dialog box of Migration Workbench.

Creating Microsoft Access XML Files

To prepare for capturing a Microsoft Access database, you must use Oracle Migration Workbench Exporter for Microsoft Access. This application is packaged as a Microsoft Access MDE file and it allows you to export the Microsoft Access MDB file to an XML file. If the Microsoft Access database you select uses linked tables, the schema information for the linked tables is also exported to the XML file.


Note:

You can export and capture only one Microsoft Access database at a time.

To use Oracle Migration Workbench Exporter for Microsoft Access to export the Microsoft Access database to an XML file:

  1. Open the appropriate MDE file in Microsoft Access:

    • For Microsoft Access 97, open the following file: 

      OMWB_install_dir\Omwb\msaccess_exporter\omwb97.mde

    • For Microsoft Access 2000, open the following file: 

      OMWB_install_dir\Omwb\msaccess_exporter\omwb2000.mde

    A screen appears prompting you to specify the Microsoft Access database file that you want to migrate to Oracle.

  2. In the MDB File Name field, enter the path of the Microsoft Access database that you would like to migrate.

    If necessary, choose Browse to locate the Microsoft Access database.

  3. In the XML File Name field, change the path and file name of the XML file, if necessary.


    Note:

    The generated XML file is automatically set to read-only. You must not modify this file.

  4. Click Export.

    The Microsoft Access database you selected is exported to an XML file. Oracle Migration Workbench Exporter for Microsoft Access currently does not support creating XML files from secured or replica databases. Oracle Migration Workbench Exporter for Microsoft Access displays an error message if you try to complete this procedure using a secured or replica database.

Before Migrating From Informix Dynamic Server

To configure an Informix Dynamic Server database for migration:

  1. Verify that the Informix Dynamic Server JDBC Driver JAR file, ifxjdbc.jar, is located in the following directory:

    • On Windows: 

      OMWB_install_dir\Omwb\drivers

    • On UNIX: 

      OMWB_install_dir/Omwb/drivers

  2. If necessary, install the Informix Dynamic Server JDBC Driver, version 1.4:

    1. Insert the Informix Dynamic Server CD into your CD drive.

    2. Follow the instructions in the install.txt file located in the JDCC directory to install the Informix Dynamic Server JDBC driver.

    3. Copy the ifxjdbc.jar file of the driver to the following directory: 

      OMWB_install_dir\Omwb\drivers

      • On Windows: 

        OMWB_install_dir\Omwb\drivers

      • On UNIX: 

        OMWB_install_dir/Omwb/drivers

  3. Ensure that you can connect to the Informix Dynamic Server database from the system where you have installed Migration Workbench.

Before Migrating From MySQL

To configure a MySQL database for migration:

  1. Install MySQLConnector/J release 3.0 on the system where you have installed Migration Workbench, as follows:

    1. Download the MySQLConnector/J API from the MySQL Web site at: 

      http://www.mysql.com/

    2. Extract the mysql-connector-java-3.x.xx-stable.zip file.

    3. From the com subdirectory zip the extracted files.


      Note:

      Ensure that the zip file contains the path information beginning with the com path name.

    4. Rename the zip file to the following: 

      mysql-connector-java.zip

    5. Copy the mysql-connector-java.zip file to the following directory:

      On Windows:, copy to: 

      OMWB_install_dir\Omwb\drivers

      On UNIX, copy to: 

      OMWB_install_dir/Omwb/drivers

  2. Ensure that the MySQL database is accessible by the root user. This user must have database administrative privileges.

Before Migrating From IBM DB2/400

To configure an IBM DB2/400 database for migration:

  1. Install the IBM DB2/400 JDBC driver on the system where you have installed Migration Workbench.

    You can install the driver from the IBM DB2/400 V4R3 or V4R5 CD or you can download it from the IBM Toolkits for Java Web site as follows:

    1. Go to the JTOpen: The Open Source version of the IBM Toolbox for Java Web site at: 

      http://www-124.ibm.com/developerworks/oss/jt400/index.html

    2. Click the Toolbox downloads link.

      The IBM Toolbox for Java page appears.

    3. Click the JTOpen link.

      The registration page appears.

    4. Enter the user ID and password used to register with the IBM Web site.

      If you do not have a user ID and password, you can choose to register. After entering the user ID and password, the JTOpen Version 3.1 page appears.

    5. Review the registration information, then click Accept License.

      You are automatically redirected to the Enter Network Password dialog box.

    6. Re-enter the user ID and password used to register with the IBM Web site.

      The JTOpen Version 3.1 download page appears.

    7. From the Single File Download, click the jtopen_3_1.zip link to download the file.

      The File Download dialog box appears.

    8. Open the j2open_3_1.zip file, then locate and extract the jt400.jar file to the following directory: 

      OMWB_install_dir/Omwb/drivers

      Note:

      You cannot use the native JDBC driver shipped with the AS/400 Developer Kit for Java with Migration Workbench because it only runs on the AS/400 Java Virtual Machine (JVM).

  2. Make sure that you can login to the IBM DB2/400 database with a user name that has database administrator privileges.

Before Migrating From IBM DB2 UDB

To configure an IBM DB2 UDB database for migration:

  1. Install the IBM DB2 UDB Client software on the same system as Migration Workbench.

    This automatically installs the IBM DB2 UDB JDBC driver contained in the db2java.zip file.


    Note:

    Migration Workbench supports the use of either the JDBC 1.2 or JDBC 2.0 compliant IBM DB2 UDB JDBC driver.

  2. Copy the db2java.zip file to the following directory: 

    OMWB_install_dir/Omwb/drivers

  3. Make sure that you can login to the IBM DB2 UDB database with a user name that has database administrator privileges.

  4. Ensure that the IBM DB2 UDB databases or its aliases that you are migrating are displayed in the Databases folder in the IBM DB2 UDB Control Center.

    To add a database or an alias to the Databases folder in the IBM DB2 UDB Control Center:

    1. Select the Databases folder from the correct instance.

    2. From the Selected menu, select Add.

      The Add Database dialog box appears.

    3. Choose the database that you want to add from the Database name drop down list.

    4. If necessary, type an alias for the database in the Alias option.

    5. Click OK.

Determining Connection Information for an Oracle Database

To connect to an Oracle database, you must know the connection information for that database. Otherwise, use the following procedure to list the connection information for an Oracle database. You use the connection information when you log into an Oracle8i, Oracle9i or Oracle Database 10g repository, use the Tablespace Discoverer, or specify a destination database. The connection information includes the host, the port number, and the system identifier (SID).


Note:

If there are multiple instances of an Oracle database, Oracle recommends you contact your organization's DBA to decide the best instance for you.

To list the host, port number, and SID for an Oracle database:

  1. Log into the system with the Oracle database you want to use.

  2. Enter the following command to display the status of the database connection software: 

    prompt> lsnrctl status

    A listing similar to the following appears: 

    LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 13-AUG-2002 12:09:40
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias         LISTENER
Version       TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
Start Date    12-AUG-2002 09:22:49
Uptime        1 days 2 hr. 46 min. 51 sec
Trace Level   OFF
Security      OFF
SNMP          OFF
Listener Parameter File   d:\oracle\network\admin\listener.ora
Listener Log File         d:\oracle\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain1.com)(PORT=1521)))
(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora92" has 1 instance(s).
 Instance "ora", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

  3. Find the TCP protocol entry in the listing and use the host and port number when requested by Migration Workbench.

    For example, in the sample listing the TCP entry is: 

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain1.com)(PORT=1521)

    In the sample listing the host is server1.domain1.com and the port number is 1521.


    Note:

    The default port number for an Oracle database is 1521.

  4. Find the database instance entry in the listing and use the instance name, which corresponds to the SID, when requested by Migration Workbench.

    For example, in the sample listing the database instance entry is: 

    Instance "ora", status UNKNOWN, has 1 handler(s) for this service...

    In the sample listing the SID is ora.

  Previous
Previous 		Next
Next
  Go To Table Of Contents
Contents		 Go To Index
Index