As Published In September/October 2007

TECHNOLOGY: SQL Developer

Moving to an Oracle Database

Use Oracle Migration Workbench to migrate third-party schema objects to an Oracle database.

Does your business have a collection of different databases in house? Do you want an easy way to review all the data in these various databases by using one tool and then, in some cases, to consolidate the databases by migrating objects and data to an Oracle database? This column introduces the new Oracle Migration Workbench, a powerful extension to Oracle SQL Developer that enables you to access data in Oracle and third-party databases and migrate third-party databases—including the schema objects, triggers, and stored procedures—to an Oracle9i Database or Oracle Database 10g. Third-party databases currently supported include Microsoft SQL Server, Microsoft Access, and MySQL.

This column shows you how to set up your environment and then, using a Microsoft SQL Server database as an example, how to browse and migrate database objects and data from SQL Server to an Oracle database.

Configuring the Environment

Before you migrate your third-party database objects, you need to configure your Oracle SQL Developer and Oracle Migration Workbench environment to work with your third-party database(s). Oracle Migration Workbench is available in Oracle SQL Developer as of Release 1.2.0.29.98. You can download Oracle SQL Developer at oracle.com/technetwork/products/database/sql_developer.

Oracle SQL Developer uses Java DataBase Connectivity (JDBC) to connect to databases, so in addition to installing Oracle SQL Developer, you need to install and set up the JDBC drivers for the third-party database to which you want to connect. The following includes download requirements and location information for supported third-party databases: 

• The MySQL JDBC driver, version 5.04, is available at http://dev.mysql.com/downloads/connector/j/5.0.html.  

• Microsoft SQL Server requires the jTDS driver, available at http://jtds.sourceforge.net. The download for version 1.2 is available at http://sourceforge.net/project/showfiles.php?group_id=33291.  

• Microsoft Access does not require an additional driver, because it uses a JDBC/ODBC bridge.

Setting Up JDBC Drivers

Once you have downloaded the JDBC drivers you need, expand the driver binary JAR file, typically a separate JAR file inside the downloaded archive file: 

• For MySQL, the mysql-connector-java-5.0.4.tar.gz (or .zip) download includes a binary driver JAR file called mysql-connector-java-5.0.4-bin.jar. 

• For SQL Server, the jtds-1.2-dist.zip file includes a file called jtds-1.2.jar.

Ensure that your third-party database is up and running, and start up Oracle SQL Developer. From the main menu, select Tools -> Preferences... , expand the Database node, select Third Party JDBC Drivers , and click Add Entry.... Then browse to and select the JAR file mentioned above, and click OK .

Creating Database Connections

Before you can work with a database in Oracle SQL Developer, you need to create a database connection. Create a connection for any third-party database you plan to browse or migrate from and a connection for any Oracle database you plan to browse or migrate database objects to.

To create a database connection in Oracle SQL Developer, click the Connections tab, right-click the Connections node, and select New Connection . For a SQL Server connection, click the SQLServer tab and complete the following: 

• Connection name, such as SQLServer2005_mwb

• Username and password

• Hostname of the machine where the SQL Server database resides

• Port

Now click Retrieve database to populate the list of available SQL Server databases. For this column, I am using Northwind, a sample database provided with SQL Server.

Finally, click Test , to ensure that you can connect successfully, and then click Connect to save the connection, close the dialog box, and connect to the selected SQL Server database.

Browsing Your Data

When you open a database connection in Oracle SQL Developer, a SQL worksheet opens to enable you to write basic queries and commands. Expand the SQL Server connection you have created, and navigate to the Tables node under dbo . You can select and drag any table onto the SQL worksheet, and the action will create a SELECT statement. Press F9 to execute the query.

If you have multiple queries you want to execute, press F5. As with any other Oracle database connection, you can sort the returned data by clicking the column headings.

To view the definition of a table such as Customers in the Connections Navigator, click the table. Note that the tabs displayed are slightly different from those available when the connection is to an Oracle database. Also note that when Oracle SQL Developer is connected to an Oracle database, the context menus offer many Data Definition Language (DDL) commands or utilities; the context menus for third-party databases are focused on browsing and migration.

Setting Up a Repository

Oracle Migration Workbench uses an Oracle database schema as a migration repository to store the metadata it collects and transforms for the migration process. You need to set up a migration repository only once, regardless of how many migrations you perform. The repository consists of 37 tables, with associated primary keys and indexes, eight views, triggers, and PL/SQL code. Although this is not required, Oracle recommends that you configure a dedicated database schema for the repository.

The user who creates and owns the repository must be granted various system privileges and roles. Listing 1 creates a user named migration and grants the necessary privileges.

Code Listing 1: Creating the migration user 

CREATE USER migration IDENTIFIED BY migration
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM 
TO migration WITH ADMIN OPTION;

GRANT  ALTER ANY ROLE, ALTER ANY SEQUENCE, 
ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER, 
COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, 
CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE, 
DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, 
DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, 
SELECT ANY TABLE, UPDATE ANY TABLE 
TO migration;

Once you have created this user, you'll need to create a new database connection, such as Migration_Repos, for this user in the same way you did before. However, for this user you are creating the connection for an Oracle database. After you have entered the necessary information, click Connect to save the connection and close the dialog box. From the main menu, select Migration -> Repository Management -> Create Repository (as shown in Figure 1). Select the connection for your new repository, and click Create . An Installing Repository dialog box will appear, showing the progress. When the process completes, close the dialog box. 

Two additional windows open below the Connections Navigator, displaying captured and converted models.

Migration

The migration process comprises a few separate activities. Once you have connected to the database you want to migrate and have built the migration repository, the process is as follows:

1. Capture the source database
2. Convert the captured database
3. Generate DDL for the new Oracle schema objects
4. Run the generated DDL script to create the new user and objects
5. Copy any data from the source database to the new database, if required Quick Migration. Oracle SQL Developer also provides a Quick Migration feature. This wizard-driven approach migrates schemas and data to an Oracle database and supports least privilege migration, which means that users can migrate source database objects for which they have access to the target database without the DBA privilege. The Quick Migration wizard completes the migration process without user intervention.

To invoke the Quick Migration option, select Migration -> Quick Migrate from the main menu. The wizard provides a single dialog box that requires you to select the source connection, which is the connection to the third-party database, and the target connection, which is the connection to the Oracle database for the new data. The wizard determines whether you have an available repository, and if there is none, it creates one during the migration process. (You can also elect to save or delete the repository once the migration is complete.) Click Verify to connect to the third-party and Oracle databases and verify that the schema in the Oracle database has been granted the necessary privileges. With verification complete, click Migrate to complete the quick migration.

Manual Migration. Using manual migration, you have much more control over the process and can select individual tables or schema objects to migrate. You can also make updates at various stages of the migration process, such as renaming the schema and dropping or renaming tables after they have been captured and before they are converted to the Oracle model. Let's take them one at a time:

Step 1. Capture the source database. Right-click the source database connection node ( SQL Server2005_mwb in this example) in the Connections Navigator, and select Capture Microsoft SQL Server . A progress dialog box showing you the capture process appears. Once it is complete, click Close . After the source has been captured, the metadata definitions are stored in the Migration Repository and are ready to be converted. The captured definitions are available in the Captured Objects window. Expand the new node, \\sourcehostname:port\Northwind (Microsoft SQL Server) , and browse the output. You can make any modifications, such as renaming tables, at this point.

Step 2. Convert the captured database. Once you are satisfied with it, right-click the newly captured model, \\sourcehostname:port\Northwind (Microsoft SQL Server) , and select Convert to Oracle Model . A data map appears for you to accept or modify. For this exercise, click Apply to accept the mappings offered. A progress dialog box appears; click Close when the process completes. Figure 2 illustrates the status thus far. Both Captured Models and Converted Models reflect the metadata stored in the Migration Repository. 

Step 3. Generate DDL for the new Oracle schema objects. At this stage, you can elect to generate the DDL for individual objects or categories of objects. To generate all the converted objects, right-click the high-level converted model, Converted: \\hostname:port\Northwind (Microsoft SQL Server) , and click Generate .

Step 4. Run the script to create the new user and schema objects. When the DDL generation is complete, the DDL script opens in the SQL worksheet. Note that the opening commands create a new database user and grant the new user privileges. This means that you need to run the script as a user who has these privileges, such as SYSTEM. At the top right side of the SQL worksheet, select the user you want to run the script, as shown in Figure 3, and press F5 to run the script. 

Once the script is complete, create a new database connection, such as ConvertedSQLServer_mwb , in the Connections Navigator to browse and review the newly migrated objects.

Figure 1: Creating a repository

Figure 2: Captured and converted models

Figure 3: Run script as system

Step 5. Copy the data from the source database to the new database, if required. The last phase of the migration process is to copy any data you have in the source database to the target database. From the main menu, select Migration -> Migrate Data . In the dialog box, select the Source Connection , the Target Connection , and the Converted Model (in this example, Converted: \\hostname:port\Northwind (Microsoft SQL Server) ), and click OK . You can now browse, edit, and work with your new objects and data as you would with any other schema in your Oracle database.

Conclusion

Oracle Migration Workbench supports the migration of Microsoft Access, Microsoft SQL Server, and MySQL database schemas to Oracle Database. It also reduces the risks involved in migration and reduces migration time by automating a large portion of the process. 

Send us your comments