An Oracle database provides you with better scalability, reliability, increased performance, and better security than other third-party databases. For this reason, organizations migrate from their current database, to an Oracle database. Because a database migration can be a complicated process, Oracle provides you with Migration Workbench. Migration Workbench is a tool that helps you to migrate from third-party databases to an Oracle database.

Migration Workbench captures information from the source database and displays it in the Source Model, which is a representation of the structure of the source database that is stored in the Workbench repository. Migration Workbench enables you to migrate the data, including schema objects, triggers, and stored procedures. Migration Workbench uses its own repository, called the Workbench repository, to store migration information that is persistent for the life of the migration. It allows you to modify the Source Model and generate an equivalent Oracle Model. The Oracle Model is a representation of the structure of the destination database and is stored in the Workbench repository. Migration Workbench populates the Oracle Model using the information from the Source Model. You can then use the information in the Source Model and the Oracle Model to compare database objects, identify conflicts with Oracle reserved words, and manage the migration progress.

Following are the components of the Migration Workbench Architecture:

  • Migration Workbench
  • Plug-In
  • Workbench Repository

Migration Workbench

Migration Workbench provides a user interface, including wizards, that help you migrate third-party databases to an Oracle database. Migration Workbench includes the following wizards and scripts:

  • Capture wizard to capture the source database
  • Migration wizard to migrate the Source Model to the Oracle Model
  • Scripts to create a set of files that describe the database structure and also to copy data from the source database to the destination database

Individual plug-ins are available to migrate different third-party databases to an Oracle database. You must install at least one plug-in with Migration Workbench.

Plug-In

Migration Workbench uses a plug-in to:

  • Extract data from the data dictionary of the source database
  • Create the Source Model
  • Convert the Source Model to the Oracle Model

Individual plug-ins are available to migrate different third-party databases to an Oracle database.

Workbench Repository

The Workbench repository is a set of tables in an Oracle database that stores the Source Model and the Oracle Model and other information about the migration. By storing the Source Model and the Oracle Model in the Workbench repository, you can customize the migration without affecting the production environment. The Workbench repository saves dependency information about the database that you are migrating.
Oracle recommends that you use an Oracle Database 10g, Oracle9i, or Oracle8i database to store the Workbench repository. However, if you prefer, you can store the information in the default Workbench repository that is installed with Migration Workbench.

The stages of Migration Workbench process are as follows:

  • Capturing the Source Database
  • Accessing the Destination Database
  • Creating and Customizing the Oracle Model
  • Migrating from the Source Database to the Oracle Database

Capturing the Source Database
In this stage, you use Migration Workbench to extract the metadata from the data dictionary of the source database and populate the Source Model of the Workbench repository. There are two methods you can use during this stage, as follows:

  • Use the Capture wizard to connect to the source database, extract the database structure, and populate the Source Model.
  • Use Migration Workbench offline capture scripts to run against the source database and create a set of files that describe the database structure. You can then use Migration Workbench to populate the Source Model from these files.

Accessing the Destination Database

To access the destination database for migration purposes, you must have certain roles and privileges. You can create a user and give the following grants:

CONNECT, RESOURCE, CREATE PUBLIC SYNONYM, 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

Creating and Customizing the Oracle Model

In this stage, you use Migration Workbench to map the source database metadata, displayed in the Source Model, to similar Oracle schema objects in the Oracle Model. The Capture wizard automatically creates the Oracle Model by default. You can customize both the Source Model and the Oracle Model at a later stage.

Migrating from the Source Database to the Oracle Database
In this stage, you use Migration Workbench to migrate the source database to an Oracle database. Migration Workbench extracts schema object information from the Oracle Model and creates these objects in the destination database. After it has created the schema objects in the Oracle database, it populates these objects with the data from the source database.

Benefits of Migration Workbench

Benefits of using Migration Workbench to migrate a third-party database to an Oracle database are as follows:

  • Reduces the effort and risks involved in a migration project
  • Allows you to make informed decisions based on the amount of automated migration you can achieve
  • Simplifies the process of migrating data and applications from third-party databases to an Oracle database
  • Allows you to migrate an entire third-party database, including triggers and stored procedures
  • Provides an integrated, visual environment to help you manage the migration process
  • Displays a representation of the Source Model and Oracle Model so that you can compare
  • Provides feedback of the migration through reports
  • Allows you to customize the Source Model and Oracle Model to maximize the automation of the migration process
  • Allows you to collect metadata from a third-party database without having to connect to that database with Migration Workbench
  • Eases the migration process by allowing you to perform the migration in stages using intuitive wizards