TECHNOLOGY: SQL Developer
Moving to an Oracle DatabaseBy Sue Harper
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 220.127.116.11.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:
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:
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:
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.
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
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.
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.
Sue Harper (firstname.lastname@example.org) is an Oracle senior principal product manager based in London.