---

Quick Links

News | The Premier Online | Ministries & Organizations | Job Opportunities | Main Index

---

Contents

• B.C. Home
• Ministry of Environment
• Ministry of Agriculture and Lands
• CSD
  • IMB
    • Services
      • Database Services
      • Management Services
      • Application Services
      • Information Management Services
      • Technology Services
      • Web Services
        • File Transfer Service (SOFT)
      • Data Services
    • Organization
      • Business Plan
      • Corporate Information Architecture
      • Web Services
      • GIS and Access Support Services
      • Application Development Services
    • Standards
      • Alphabetical List of All Standards
      • Application & Architecture
      • Project Management
      • Standards for Standards
      • System Development Life Cycle (SDLC)
      • Technology
      • Web
    • Projects
      • Corporate Information Architecture
      • Web Services
      • Data Management Services
      • GIS and Access Support Services
      • Application Development Services
      • Land and Resource Data Warehouse
  • Reports and Publications
  • Branch Contacts

B.C. Home » Environment » CSD

ORACLE MIGRATION WORKBENCH MIGRATION STANDARDS
Version 1.0.0      August 8, 2002

Table of Contents

VERSION CONTROL

1. INTRODUCTION

1.1 Audience
1.2 Purpose
1.3 References to Other Documents

2. DEFINITIONS
2.1 Standard
2.2 Guideline

3. ORACLE MIGRATION WORKBENCH
3.1 Supported Version
3.2 Migration_Path

4. WIZARD MIGRATION INSTRUCTIONS

4.1 Prepare Target Database
4.2 Prepare Source Database
4.3 Capture Source Database
4.4 Modify Oracle Model
4.5 Migrate to Oracle

5. MANUAL MIGRATION INSTRUCTIONS

5.1 Generate Scripts

6. CONCLUSION

VERSION CONTROL

This section of the document is to be used to control the various versions or releases of the document.

Version	Description	Distribution	Date	Author	Organization
1.0.0	Production	Whole Document	08-Aug-2002	Jordan Armstrong	HeavyLifters Inc.

DOCUMENT CONVENTIONS

 

The following document conventions are used in this document:

CAPS	are used when capital characters are to be entered
Bold	will indicate a proper name or system command to be performed
Bold Italics	indicates a role or function to be performed
<version>	represents a versioning format ##.##.## where the first ## represents a major version release, the second ## represents a minor version release, and the third ## represents a patch version release.

1. INTRODUCTION

 

This section outlines the Audience and Purpose of this standards document.

1.1 Audience

This document is directed at those who will be performing database migrations from Microsoft Access to an Oracle Relational Database within the British Columbia Ministry of Sustainable Resource Management and Ministry of Water, Land and Air Protection corporate infrastructures. This includes mainly Ministry employees but may also include external contractors, consultants, and business partners.

1.2 Purpose

This document outlines the standards that must be followed when migrating a Microsoft Access database to a Ministry of Sustainable Resource Management and/or a Ministry of Water, Land and Air Protection Oracle database. From this point forward any mention to either the Ministry of Sustainable Resource Management or the Ministry of Water, Land and Air Protection will be referred to as "the Ministry". The Information Management Branch (IMB) serves both Ministries with the mandate to formulate and maintain application development and delivery standards.

1.3 References to Other Documentation

The Ministry of Sustainable Resource Management has a number of standards pertaining to application development and the system development lifecycle (SDLC).

2. DEFINITIONS

The following definitions apply throughout this document.

2.1 Standard

A standard is a specific statement of the rules and constraints governing the naming, contents, and operations of software. A standard must be followed.

2.2 Guideline

A guideline is a method or custom, which through common usage has become an accepted method of work. A guideline is not enforced, and is not a standard.

3. ORACLE MIGRATION WORKBENCH

Oracle Migration Workbench is a wizard-driven tool used to migrate non-Oracle databases to an Oracle host database. The core install has the following features:

• Can migrate database objects with or without data.
• Visual representation of the source database and its target Oracle equivalent.
• Users can customize schema names, tables, indexes, and tablespaces.
• Users can customize the default data type mappings.
• Automatically resolves object name conflicts, such as conflicts with Oracle reserved words.
• Users can delete and rename objects in the Oracle Model.

3.1 Supported Version

The core install of the Oracle Migration Workbench must be installed to perform any migration. The plug-in associated with the source database, in this case Microsoft Access, must also be installed. These instructions pertain to Oracle Migration Workbench version 9.2.0.1.0 and the associated plug-in for MS Access 97 or 2000. You can download both here:

Oracle Migration Workbench 9.2.0.1.0
MS Access plug-in

Please note that local workstation Oracle installs are not supported by the IMB. The use of the Oracle Migration Workbench is at your own risk, the IMB is not responsible nor can be held accountable for the use of this tool . It is assumed that the individuals peforming the migrations have Oracle database experience. It is highly recommended that this document be read and the instructions followed by anyone performing these types of migrations within the Ministry. Failure to do so could result in a variety of database errors and complications.

Documentation for installation and troubleshooting can be found here:

Oracle Migration Workbench Documentation

3.2 Migration Path

A general overview of the migration path is as follows:

1. Capture MS Access database using Oracle Migration Workbench.
2. Migrate database objects to a schema in the ENVDEV1 database instance.
3. Review schema in ENVDEV1 and confirm object integrity.
4. Export schema or tables from ENVDEV1.
5. Import schema or tables to host database.

4. WIZARD MIGRATION INSTRUCTIONS

 

This section describes the Ministry's Oracle Migration Workbench (OMWB) Wizard migration instructions. These instructions will ensure that the database objects are migrated according to Ministry standards and guidelines.

4.1 Prepare Target Database

The initial step of the migration process is to prepare the target database, ENVDEV1, that will house the database objects from the MS Access database. A schema and and tablespace will need to be set up in ENVDEV1 that will be used for the migration to Oracle. Contact the IMB Application Delivery Specialist at 387-2495 to perform the steps required to prepare the target database.

4.2 Prepare Source Database

The first step in the migration process is to prepare the source MS Access database for the migration. OMWB requires an XML file that represents the MS Access database you wish to capture. To generate this file perform the following steps:

1. Make a backup copy of the .mdb file you want to capture.
2. Open the OMWB Exporter for MS Access located in the $OMWB_HOME\Omwb\msaccess_exporter directory, where $OMWB_HOME is the directory where you installed OMWB. If migrating from Access 97, double-click the omwb97.mde file, for Access 2000 double-click omwb2000.mde.
3. Browse to and select the .mdb file you wish to migrate. The XML File Name field will automatically populate with the same directory path and filename as selected in the MDB File Name field. This can be modified if necessary.





4. Click OK.
5. When the process is complete you will receive a confirmation message.





6. Click OK.
7. Click Exit or repeat steps 3 - 6 to prepare remaining .mdb files.

4.3 Capture Source Database

The next step is to capture the source MS Access database using OMWB.

1. Open OMWB and log on to the repository. The repository is stored in the ENVDEV1 instance under the OMWB schema. Contact the IMB Application Delivery Specialist for the password at 387-2495.





2. Select Capture Source Database... under the Action menu or click on the Capture Source Database button found on the left-hand side vertical tool menu.
3. Click Next on the Capture Wizard Welcome screen.
4. In the Select Database to Capture screen, click on the Add XML File... button and navigate to the xml file you generated previously using the OMWB Exporter for MS Access. Click Next.





5. Modify Data Type Mappings if necessary. This can also be done after the capture by selecting Options... under the Tools menu. Click Next.
6. Determine if you want the wizard to create the Oracle Model at this stage and select the appropriate radio button. This can be done after the capture by selecting Create Oracle Model... under the Action menu. Click Next.
7. Review the Capture Details and click Finish.
8. When the capture is complete click OK to the message dialog box.
9. Click OK to close the Message Logging window.
10. Click No when asked to run the Migration Wizard to create the schemas in the Oracle database.

4.4 Modify Oracle Model

Some changes need to be made to the Oracle Model prior to migrating the database objects to the Oracle database.

1. Click on the Oracle Model tab at the bottom of the screen to display the objects that will be migrated to the Oracle database.
2. Expand the Tablespaces node and select the tablespace. Delete the tablespace either by right-clicking and selecting Delete... or by selecting Delete... under the Object menu.
3. Expand the Users node and delete the omwb_emulation user.
4. Rename the remaining user to the schema name that will house the objects in the target Oracle database. Right-click on the username and select Rename, select Rename from the Object menu, or press F2.
5. Change the user's password to the password given to the Oracle schema created in section 4.1. Click Apply.





6. Modify the table data types if necessary.

4.5 Migrate to Oracle

To perform the migration to Oracle, use the OMWB Migration Wizard.

1. Select Migrate to Oracle... under the Action menu.
2. Click Next on the Welcome screen.
3. Enter the username, password, and instance for the OMWB repository owner. Click Next.





4. Click Next.
5. Select Yes or No if you want to include the data in the migration. Data can be moved separately after the database objects have been migrated. Click Next.
6. Ensure the objects you want to migrate are listed in the Selected Schema Objects window. Click Next.
7. Review the summary information. Click Finish.
8. You will receive an error that the username conflicts an existing schema. Click Ignore Error.





9. If the Modify Microsoft Access Database screen is displayed, click Cancel. If required this step can be done later by selecting Modify Microsoft Access Database... under the Action menu.
10. Click OK to the message dialog.
11. Review log. Click OK. Reports can be generated for viewing the migration status by selecting Generate Database Migration Reports under the Report menu.
12. The objects now reside in the specified schema in ENVDEV1.
13. The schema or individual objects can now be exported from ENVDEV1 and imported to another database as needed.

5. MANUAL MIGRATION INSTRUCTIONS

 

This section describes the Ministry's Oracle Migration Workbench (OMWB) manual migration instructions. These instructions are intended for experienced Oracle users who may want to manually create the objects using scripts in the target database. Follow these instructions to ensure that the database objects are migrated according to Ministry standards and guidelines.

5.1 Generate Scripts

The OMWB tool has the capability to generate scripts that can be modified, if necessary, and run against the target Oracle database.

1. To prepare for the migration you must peform the same steps listed under the Wizard Migration Instructions. Follow instructions from sections 4.1 - 4.4.
2. Select Generate Migration Scripts... under the Action menu.
3. Ensure the objects you want to migrate are listed in the Selected Schema Objects window. Remove objects that you don't want to include. Click OK.
4. A message dialog will be displayed indicating the generation was successful and the path where OMWB placed the scripts. Take note of the directory path and click OK.
5. Browse to the specified directory and locate the Drop.sql and Create.sql scripts.
6. Open Create.sql and review. Make any necessary modifications and save. **Note - The schema password is hard-coded in this file.
7. Log on to the target database, ENVDEV1, as the schema owner and run the script.
   • start Create.sql
8. Review the objects in the target schema.
9. The schema or individual objects can now be exported from ENVDEV1 and imported to another database as needed.

6. CONCLUSION

This document provides a set of standards and guidelines for the migration of Microsoft Access datbase objects to an Oracle Relational Database. The purpose of this document is to outline standard instructions that must be used when performing database migrations using Oracle Migration Workbench. The goal of this document is to prevent database errors and complications that may arise if the OMWB is not used according to Ministry standards.