Overview

Scenario

Prerequisites

Installing and configuring Oracle Migration Workbench

Creating the Source Model

Creating the Oracle Model

Customizing the Oracle Model

Migrating the tablespaces, users, and user tables to the destination database

Migrating data to the destination database

Migrating remaining schema objects to the destination database

Verifying the stored procedures and triggers migrated successfully

Generating and viewing Oracle Migration Workbench reports

Summary

1.

From Windows Explorer, navigate to Oracle_Migration_Workbench folder on the DVD and unzip omwb_windows.zip into a folder on your local drive (e.g. c:\omwb)

2.

Unset the Read-only attribute for the files in the offline_capture subfolder.

i) Locate the offline_capture_files subfolder, right-click and choose Properties.

ii) Under Attributes, deselect Read-Only and click Apply.

iii) Accept the default option "Apply changes to this folder, subfolders, and files" and click OK.

iv) Click OK.

3.

Have access to an Oracle Database 10g Release 2 database.

4.

Set the shared folder, vmware_shared, on your VMware Workstation to c:\vmware_shared on the host folder.

Install Oracle Migration Workbench

Create the Oracle Migration Workbench Repository

Create the omwb_user in the destination Oracle database

Create the sa tablespace in the destination Oracle database

1.

Navigate to the c:\omwb folder, double-click the omwb_w2k.zip file to unzip the files into the c:\omwb folder. Note an omwb sub-folder (e.g. c:\omwb\Omwb) is created.

2.

Copy the c:\omwb\SQLServer2K.jar file and paste it in c:\omwb\Omwb\plugins.

1.

To start Oracle Migration Workbench, open a new Command Prompt window and enter:

cd c:\omwb\Omwb\bin

omwb.bat

2.

The Oracle Migration Workbench Repository Login window opens. Select Default Repository and click OK.

3.

When you log into Oracle Migration Workbench for the first time, a pop-up window appears stating that the repository does not exist. Click Yes to create it.

4.

The Create Migration Repository window displays the progress of the creation. Click Close when you see the message "Repository successfully created."

5.

In the Select Migration Source window , choose OK to select the Microsoft SQL Server 2000 PlugIn.

6.

Two windows open: The Oracle Migration Workbench and the Capture Wizard: Welcome. Click Cancel in the Capture Wizard: Welcome window.

1.

If you have not already done so, log into the Linux OS running on VMWARE as oracle/oracle. Open a terminal window and enter the following commands:

cd /home/oracle/wkdir

sqlplus sys/oracle as sysdba

2.

Execute the c_omwb_user.sql script to create the Oracle Migration Workbench user in the destination Oracle database.

start c_omwb_user

The script executes the following statement:

drop user omwb_user cascade;

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;

GRANT create view to omwb_user with admin option;

1.

In the Linux OS running on VMWare, click on the Enterprise Manager Database Console icon on the desktop to launch Enterprise Manager Database Console

2.

At the Enterprise Manager Database Control Login page, enter the following:

• Username: sys
• Password: oracle
• Connect As: sysdba

Click Login.

3.

From the Home page, click on the Administration tab.

4.

Under the Storage heading, click on the Tablespaces link.

5.

Click the Create button.

6.

In the General tab, enter the tablespace name: sa and click on the Add button at the bottom of the page.

7.

In the Add Datafile page, set File Size to 30 MB and click on the Continue button.

8.

In the General tab, click OK to create the tablespace.

9.

Note that the SA tablespace is created. This tablespace will be used to store the migrated objects.

10.

Click the Cluster Database: RACDB link to return to the Administration page.

Create the Source Model using offline capture files

Review the log files

Customize the Source Model

1.

In the Oracle Migration Workbench window, select Action > Capture Source Database.

2.

Review the Welcome page and click Next.

3.

On the Source Model Load Details page, select Offline Source Model Load and click Next.

4.

Click Load. The Select Root Directory page appears.

5.

Navigate to c:\omwb\offline_capture_files directory, then click OK.

6.

A list of metadata files is loaded. Click Next.

7.

In the Data Type Mappings page, review the data type mappings. Click Next.

8.

In the Create Oracle Model page, select No so that you can explore and customize the Source Model first. Click Next.

9.

On the Summary page, click Finish.

10.

The Loading Source Model window appears, displaying various messages. In the Loading Source Model pop-up message window, click OK.

11.

Review the log messages and click OK to close the Loading Source Model pop-up message window.

1.

To change the logging display settings, select Tools > Options in the Oracle Migration Workbench window.

2.

Click on the Logging tab.

3.

Deselect the following:

• Informational Messages
• Summary Messages
• Debug Messages.

Click OK.

4.

Review the log messages again by selecting Tools > Log Window.

5.

The content of the Log Window changes, displaying only errors and warnings. Close the window.

6.

In the left pane of the Window, expand the following nodes: Databases > hr.

7.

Note the following number of database objects in the Source Model:

• 8 tables
• 4 stored procedures
• 2 triggers

1.

Expand the Groups/Users node.

2.

Select all groups above the db_owner node (with click and shift-click) and select Object > Delete.

3.

Click Yes to confirm the deletion of the seven selected items.

4.

Expand the nodes again: Databases > hr > Groups/Users.

5.

Select the db_securityadmin role, right-click and choose Delete.

6.

Click Yes to confirm your deletion.

7.

Navigate to Databases > hr > Groups/Users > db_owner and click dbo. On the right of the window, you see that this role is assigned to the sa login. Oracle Migration Workbench will create a corresponding sa user account when you migrate the users.

Create the Oracle Model

Resolve parser error for a trigger

Resolve parser error for a stored procedure

Re-create the Oracle Model

1.

Select Action > Create the Oracle Model.

2.

Click Yes to confirm that you want to create the Oracle Model. The Creating Oracle Model window appears with many messages.

3.

When the Oracle Model is complete, note that there are 4 errors. You will resolve these parser errors in subsequent steps. Click OK.

4.

Note the following objects are not created in the Oracle Model:

• SECURE_EMPLOYEES trigger
• EXAMPLE3 stored procedure.

Click OK again to close the Creating Oracle Model window.

1.

Select Tools > Log Window from the menu.

2.

Double-click the first error for the SECURE_EMPLOYEES trigger. The SECURE_EMPLOYEES window opens with the cursor positioned on the error.

3.

Do the following:

• Enter -- (2 hyphens) in front of INSTEAD OF
• Remove the comment sign -- (2 hyphens) in front of the word FOR.
• Click OK.

4.

To re-parse the SECURE_EMPLOYEES trigger, in the SQL Server 2000 Source Model tab navigate to Databases > hr > Triggers.

5

Right-click on SECURE_EMPLOYEES and select Parse.

6.

A success message is displayed. Click OK.

7.

Click on the Oracle Model tab

8.

Expand Users > sa > Triggers. Verify that the SECURE_EMPLOYEES trigger is now part of the Oracle Model.

1.

Click the SQL Server 2000 Source Model tab

2.

Expand Databases > hr > Stored Procedures. Click example3.

3.

In the General tab on the right of the window, notice the word ERROR is used as a column name in the SELECT statement. This is a reserved word that has special meaning for the parser. Click the Parse Options tab.

4.

Select the Allow 'Reserved Words' in table names check box and click Apply. With this option selected, the parser will treat the word ERROR as an object name, not as one of its own special keywords.

5.

To re-parse the object, on the right pane of the window right-click on example3 and select Parse.

6.

Click OK to acknowledge the success message.

7.

Click on the Oracle Model tab

8.

Expand Users > sa > Stored Procedures. Verify that EXAMPLE3 is indeed created in the Oracle Model.

9.

Close the Oracle Migration Workbench Log Window.

1.

Select Action > Create Oracle Model

2.

Click Yes to confirm that you want to re-create the Oracle Model.

3.

The Creating Oracle Model window appears with many messages. When the Oracle Model creation is complete, note there are no more errors. Click OK.

4.

Click OK to close the Creating Oracle Model window.

Change the data type mappings

Delete the HR tablespace

Use Tablespace Discoverer to import existing tablespace informaton

1.

In the Oracle Model, expand Users > sa > Tables and select the EMPLOYEES table.

2.

In the General tab on the right of the window, scroll down to the TS_CREATED column and change the Type from DATE to TIMESTAMP and click Apply.

1.

In the Oracle Model, navigate to Tablespaces > HR.

2.

Click Yes to save the changes.

3.

Right-click the HR tablespace, choose Delete.

4.

Choose Yes to confirm the deletion.

1.

From the menu, select Tools > Tablespace Discoverer.

2.

In the Tablespace Discoverer window, enter the following information and click Connect.

• Username: omwb_user
• Password: omwb_user
• Hostname: 192.168.203.11
• Port: 1521
• SID: RACDB1

3.

Once the tablespace information is retrieved from the destination database, in the Tablespace Discoverer window, assign the following tablespaces to your Oracle Model and click Apply.

• Default tablespace: SA
• Index tablespace: SA
• Temporary Tablespace: TEMP

4.

In the Oracle Model tab, expand the Tablespaces node to verify that metadata for both SA and TEMP tablespaces have been imported into the Oracle Model.