1 Introduction

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.

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.

Chapter 4, "Capturing a Source Database" describes these tasks in more detail.

Accessing the Destination Database

To access the destination database for migration purposes, you must have the following roles and privileges:

Roles

CONNECT WITH ADMIN OPTION
RESOURCE WITH ADMIN OPTION

Privileges

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 PUBLIC SYNONYM WITH ADMIN OPTION
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

For example, you can create a user called omwb_user with the minimum required privileges required to migrate a database using Migration Workbench by using the following command:

CREATE USER omwb_user IDENTIFIED BY omwb_user;

GRANT CONNECT, RESOURCE, CREATE PUBLIC SYNONYM TO omwb_user 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 omwb_user;

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.

Chapter 5, "Creating and Customizing the Oracle Model" describes this task in more detail.

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. Chapter 6, "Migrating the Database" describes this task in more detail.

Migration Workbench replicates some source database system functions. These replicated source functions are created in the omwb_emulation schema. Migration Workbench creates public synonyms for these functions so that all users in the destination database can access the functions.