Date:      March 2011

Oracle SQL Developer incorporates migration support, providing users with the ability to migrate database objects and data from IBM DB2, MySQL, Microsoft SQL Server, Microsoft Access, Sybase and Teradata to Oracle. This document details the setup steps to help prepare your environment for a database migration and then provides a brief description how the new Migration Wizard works and each of the main steps of a migration. For a table of specific supported versions see this Supported Platforms document.

Contents

1. Prerequisites
2. Preparing your Environment for Migration
   • Setting up a Migration Repository
   • Configuring the Third Party Connections
     • Configuring JDBC Drivers
     • Using Check for Update
   • Adding a Third Party Connection
3. Using the Migration Wizard
   • Capture your Database
   • Convert your Captured Model
   • Generate Target
   • Move the Data
4. Copy to Oracle

1. Prerequisites

• You need access to an Oracle Database 10g or Oracle Database 11g for your migration repository and an oracle database for the target migration. These can be the same database.
• Download Oracle SQL Developer 3.0. Earlier versions of SQL Developer also support database migrations, however the flow of work has been changed in SQL Developer 3.0 and we recommend that you use the latest for your migrations.

2. Preparing your Environment for Migration

You only need to create a repository once, you can use it again and again for each migration you do.

Setting up a Migration Repository

In order use SQL Developer Migrations you need to create a Migrations Repository. This is a database schema that stores the meta-data collected and transformed in the migration process. This repository consists of 42 tables, 24 views and the primary keys and indexes required, along with triggers and PL/SQL code. We recommend that you configure a dedicated database schema for this purpose, although this is not necessary.

In order to build repository in a new schema for the meta-data, the user requires the following role and system privileges:

• RESOURCE
• CREATE SESSION
• CREATE VIEW

Once you have created the schema and granted the above privileges, create a connection for the user and you are ready to install the repository. See the SQL Developer documentation on creating users and setting up connections.

In SQL Developer, select Tools > Migration > Repository management > Create Repository... and select the connection you created for your repository owner.

This will take a short time, during which a progress bar will be shown.

Configuring the Third Party Connection

You now need to configure a database connection for the database which you would like to migrate. There are two steps to this process:

• Configuring JDBC drivers, and
• Configuring the third-party connection

Configuration of JDBC drivers needs to be carried out only once per third-party connection (MySQL, Microsoft SQL Server and Sybase), whereas the second step is carried out for each database you wish to migrate.

Configuring JDBC Drivers

JDBC is the Java Database Connectivity standard and it provides a mechanism for Java programs to connect to databases. For more information on JDBC see http://java.sun.com/javase/technologies/database/

To access databases using JDBC, we must use a JDBC driver. You can get this from the Database vendor, which typically offers JDBC drivers as free downloads.

This version of the migration workbench has been tested using the following JDBC Drivers.Once downloaded you need to expand the driver binary jar file to a location on your machine. The driver binary jar file is typically a separate jar file located inside the downloaded archive file:

• IBM DB2: You need the binary driver jar files db2jcc.jar and db2jcc_license_cu.jar. Search for DB2 Universal JDBC Drivers or follow the links from http://www-306.ibm.com/software/data/db2/express/additional-downloads.html
• Microsoft Access: No additional driver is required. Access uses the JDBC/ODBC bridge
• Microsoft SQL Server and Sybase: jTDS driver available from http://jtds.sourceforge.net/. Use version 1.2 (Download available from http://sourceforge.net/project/showfiles.php?group_id=33291)
  When you download the jtds distribution, you download an archive file called jtds-1.2-dist.zip. The binary driver jar file is located within this file is called jtds-1.2.jar
• MySQL: MySQL JDBC Driver, version 5.04 available from http://dev.mysql.com/downloads/connector/j/5.0.html
  You download an archive file called mysql-connector-java-5.0.4.tar.gz (or .zip). Inside this file, the binary driver jar file is called mysql-connector-java-5.0.4-bin.jar
  
• Teradata:The Teradata driver is available from http://www.teradata.com/downloadcenter/ You need to download Teradata JDBC Driver 12.0 or above. Both the Teradata JDBC Driver 12.0 and 13.0 use the jar files terajdbc4.jar and tdgssconfig.jar

Once downloaded you need to expand the driver binary jar file to a location on your machine. The driver binary jar file is typically a separate jar file located inside the downloaded archive file. Once you have extracted the relevant jar file to your disk, you need to configure SQL Developer to tell it where this driver resides:

1. In SQL Developer choose Tools -> Preferences...
2. Expand the "Database" option in the left hand tree
3. Click on "Third Party JDBC Drivers"
4. Click on "Add Entry..."
5. Navigate to your third-party driver jar file and choose OK

Using Check For Updates to configure JDBC Drivers

Oracle SQL Developer also supports the use of Check for Updates to install the drivers required for MySQL, Microsoft SQL Server and Sybase. To use Check for Updates, start SQL Developer and select Help -> Check for Updates Follow the wizard as follows:

• Start Oracle SQL Developer
• Select Help -> Check for Updates
• Follow the Check for Updates Wizard. Select required drivers or third-party extensions.
  • You will be asked to read and acknowledge the license agreement and sign into OTN, using your OTN account.
• When prompted, restart SQL Developer
• There is no need to select "Migrate User Settings" dialog as the connections and settings are preserved.

Note: You need to use the method described in 2.1 Configuring JDBC Drivers to install the drivers for IBM DB2 and Teradata.

Adding a Third Party Connection

You should now add the third-party connection for the database you wish to migrate from.

To do this:

1. In the main SQL Developer connection navigator click on the green "plus" icon in the top left corner, to invoke the new connection dialog.
2. Fill in the details of your third-party database. Select the tab for the third-party data you are working with. Tabs are provided for Microsoft SQL Sever, Microsoft Access, DB2 MySQL, and Teradata

Once configured, you can explore your third-party database just like other Oracle SQL Developer connections.

3. Using the Migration Wizard

Oracle SQL Developer 3.0 supports both Online and Offline migrations.

• Online migrations involve connecting directly to the third-party database and migrating the database objects and data to Oracle
• Offline migrations use files that you have prepared from the third-party database. You do not need to be able to connect to the third-party database once you have the files in order to follow this approach.

Oracle SQL Developer 3.0 introduces the concept of a Migration Project and uses a Migration Wizard to walk you through the migration process. To start select your third-party database connection, right-click the connection and select Migrate to Oracle...

You'll need to know a few details for the Migration Wizard:

• The connection name of your repository owner (Step2)
• You need access to a file directory where the generated scripts will be saved (Step3)
• Whether this is an online or offline migration (Step 4)
• For an online migration, the connection for the third-party database (Steps 4 and 8)
• The names of database(s) to be migrated (Step 5)
• The connection name for a users in the Oracle database who has the privileges to be able to create new users, and to create tables. This user is used to run the create ... scripts and to move the data. an example might be SYSTEM or another user with DBA privileges. (Steps 7 and 8)

Follow the steps of the Migration Wizard.

Once the Migration Wizard has completed, if not already available, open the Migration Projects navigator by selecting View > Migration Projects. Your new migration project appears in the list. Expand the new project to see the Captured Database Objects and Converted Database Objects. Expand the Converted database Objects and see the list of users created. These are the names of the new users in your Oracle Database. To review the migrated data, you need to create connections for the new users.

The Migration Wizard works though the following process. Once complete you can re-enter the process at any point. This re-opens the wizard and completes the phases as required.

Capture your Database

This step is the first step in the migration process. It is used to capture a snapshot of the current state of your third-party database and is necessary to provide the Migration Workbench with a "point in time" view of your database. Once this step is complete, the Migration wizard works on the meta-data stored in its repository, instead of issuing queries against your live database.

This phase in the online migration uses the connection you provided to the third-party database and the output is displayed in the Captured Database Objects node under the new Migration Project.

Convert the Captured Model

The next step in the migration process is to convert the captured model of the database to an Oracle-specific model. The captured model contains data types, naming schemes etc. defined by your database vendor; this now must be converted to Oracle formats. Once the migration has completed, you can return the Captured Database Objects node and rerun the wizard from this point to convert some or all of the objects again.

To convert the captured model:

1. Navigate to the Captured Database Objects node
2. Select and right click on the node
3. Choose "Convert..."
   
   This opens the Migration Wizard again, at the stage of the conversion, skipping all previous steps. You'll see the data type conversion dialog. This dialog allows you to specify data type transformations that may be required to convert platform-specific data types to Oracle-specific data types. For the majority of cases, it is safe to accept the defaults here. If you wish, you can change the mappings used, and once you have made your choices, press Next to continue to walk through the wizard.

Generate the Target for your Converted Model

Once the conversion process has completed, the SQL Developer Migrations has a model of what the converted database will look like. This is used to generate SQL scripts for the creation of the new Oracle Database schema(s) and to run these scripts. NB: This stage of the process requires access to a privileged Oracle user, like SYSTEM or another user who has the ability to create users, tables and so on. It is this user (connection) that is used to execute the scripts.

These generated scripts will be in the directory you specified in the Wizard. To generate these scripts:

1. Navigate to the Converted Database Objects node
2. Select and right click on the node
3. Select Generate Target...

The Migration Wizard generates the necessary SQL for creating your database schema(s).

Migrate the Data to Oracle

The last step in the Migration Wizard is to migrate the data to the new database. Migrating the data is a process that copies the data from the third-party database to the new tables in the Oracle database. The Migration Wizard uses the same Oracle database connection required to run the scripts as it does to move the data.

To re-enter the Migration Wizard and move the data:

1. Navigate to the Converted Database Objects node
2. Select and right click on the node
3. Select Move Data...

This once again invokes the Migration Wizard and

1. The field labeled " Source Connection" is for the connection in the third-party database
2. The field labeled " Target" is the privileged users in oracle required to move the data to the newly created user.
3. Click OK.

Once complete, your data should now be in your newly created database schema.

4. Copy to Oracle

Oracle SQL Developer 3.0 introduces a Copy to Oracle option that allows users to copy tables and data from a third-party database to Oracle without the need of a migration repository. This quick migrate option is very useful for quickly copying a few tables to Oracle and requires no additional setup. This feature is only available for copying tables and the data; use the Migration Wizard if you are migrating more than that.

To use Copy to Oracle, select the third-party connection created in 2. Adding a Third Party Connection above. This invokes a connection dialog that enables you to select the Oracle connection where you want to copy the table(s) and click Apply.

Note: MS Access: We recommend the use of Copy to Oracle for MS Access users, as this does not require any system privileges to access the database metadata. For MS Access connections, the Copy to Oracle option is also available on right-click of the connection to easily enable all tables to be copied.