Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 



ISBN: 
0-9797951-6-8 
ISBN 13:
978-0-9797951-6-9
Library of Congress Number:
2009930093
400 pages
Perfect bind - 9x7
PD 0411
Shelving:  Databases/Oracle Oracle In-Focus Series
#33

  Migrating to Oracle
Expert Secrets for Migrating from SQL Server and MySQL

Ben Prusinski              

 Retail Price $89.95 /  £74.95

Order now at 30% off and get access to the code depot! Only $62.99
(30% off)
     
Key Features About the Author Table of Contents
Index Reader Comments Errata
     

 


 

One of the biggest challenges that IT departments currently face is how to migrate their non-Oracle databases to Oracle platform with limited budget, staff, and time constraints.

In order to meet these needs, Oracle originally developed an excellent tool called the Oracle Migration Workbench (OMWB). As the latest and greatest enhancement to migration software, Oracle has provided the new version of migration tools along with a full development environment with the SQL Developer software with Oracle 10g and 11g database releases as the method of choice to accomplish these daunting tasks.

This comprehensive guide to these new Oracle migration tools will be valuable to all database professionals who are challenged with the task of migrating their non-Oracle databases to the Oracle 10g and 11g platforms. This text will provide the blueprint and implementation details as well as expert hands-on tips and techniques on how to use this new migration tool to quickly migrate non-Oracle databases to the Oracle 10g database environment.

Topics will include a detailed discussion of these tools including coverage of both SQL Developer and the Oracle Migration Workbench installation and configuration process, techniques on how to best use the editors for parsing non-Oracle SQL and stored procedures to map to the Oracle SQL and PL/SQL code, problem resolution and troubleshooting during the migration process, and testing procedures after the initial migration has been completed.

This book distills the very complex and technical challenges of how to best migrate your non-Oracle database into an easily digested format with clear step by step techniques that all database professionals can implement right away.
 
 

          
            
Migrate to
             Oracle with
             Confidence!

 

Key Features

• Understand SQL Developer Migration tools and the Oracle Migration Workbench

• See real examples for SQL Server migration to Oracle

• See examples for migrating from MySQL to Oracle

• Understand how to convert T-SQL to PL/SQL

• Save hours of time by recognizing potential migration pitfalls

• Avoid tedious and risky manual migrations

SQL Developer and the Oracle 10g Migration Workbench are the premier  migration tools offered by Oracle with release 10g and 11g to perform rapid migrations of large non-Oracle databases to the Oracle 10g/11g platform without the need to purchase expensive conversion tools. It provides a free option to convert all non-Oracle database servers to Oracle 10g instead of manual conversion techniques which would require hundreds of painful hours of trial and error. Because of the powerful capabilities within both SQL Developer and the Migration Workbench tool, it is quick method to perform mass migrations of SQL Server and MySQL databases for example, to the Oracle 10g/11g platform. This tool provides many automation features as well.

This book has a very broad reader base encompassing almost all technical professionals who need to perform database migrations to Oracle 10g. This text will become indispensable to both technical and project management staff who wish to migrate from non-Oracle platforms on a narrow timetable and limited budget.

This book incorporates proven techniques for implementing and completing database migrations from non-Oracle databases to the Oracle 10g/11g platform.

All of the techniques in this book use both SQL Developer as well as the Oracle 10g Workbench Migration tool, so no additional third party tools are required to be used to perform the database migration to Oracle 10g/11g. This text will be comprehensive, covering every aspect of how to migrate your non-Oracle database to Oracle 10g and 11g using either the new SQL Developer tool or the Oracle Workbench Migration tool. This text also includes expert tips and techniques that have been developed by the author that will provide a timeless and invaluable reference for performing database migrations using SQL Developer and the Oracle 10g Workbench Migration tool.

 

About the Author:

 
Ben Prusinski

Ben Prusinski is an Oracle Certified Professional and Oracle ACE with more than 10 years of full-time experience as a database administrator and has written numerous articles and white papers on database management. Ben is also an active member of the IOUG, OAUG, and SROAUG user group communities. As a top Oracle expert, Ben is a frequent speaker and presenter at major technical conferences including Oracle OpenWorld, IOUG, and CLOUG. 

Ben has worked with databases including Oracle, Microsoft SQL Server, IBM DB2 UDB, and MySQL since 1996.  As a certified Oracle RAC expert, Ben has designed robust architectures for high availability with Oracle RAC, Streams, and Data Guard for Oracle E-Business Applications and data warehouse environments. Furthermore, he has accumulated over a decade of practical knowledge and experience with complex database migrations and support, focusing mainly on how to best achieve results with large database migrations to the Oracle platform. 

Technical Editor:
 


Paulo Ferreira Portugal
 

Paulo Ferreira Portugal is a DBA with a decade of experience in IT and has worked as a DBA for 8 years. He is an Oracle Certified Professional (9i and 10g); IBM DB2 Certified (8 and 9 “Viper”); and an Oracle 11i Applications Database Administrator Certified Professional.

Currently, Paulo works as Senior Applications DBA for a company named F2C Consultoria in Rio de Janeiro/Brazil. F2C serves one of Oracle E-Business’ largest clients in Brazil.

He has participated in the Oracle Beta Test 11i project using Data Guard, and is a specialist in High Availability tools like Oracle Data Guard, Oracle Streams and Oracle RAC. His clients in Brazil demand the most sophisticated services using tools like Oracle E-Business Suite 11i, Oracle OTM, Oracle Retail and Oracle BPEL with Oracle RAC 10g.

Paulo greatly enjoys what he does and is always improving his technical knowledge by attending events like Oracle Open World - San Francisco (2005 and 2006) and IBM Information on Demand – Los Angeles (2006).

Table of Contents:

Chapter 1:  Introduction to Oracle Migrations

 

Migrating to Oracle

Overview of Database Migration

Manual Procedures for Database Migration

Export and Import Method for Database Migration (Data Pump)

Server Level Copy and Cloning Method

Replication for Database Migrations to Oracle  

Schema Copy and DDL Generation          

Third Party Tools for Database Migration to Oracle 10g 

GoldenGate® Software 

Quest® Shareplex           

Swis® SQL           

The New Oracle 10g Database Workbench Migration (OMWB)  

Preparation for Database Migration to Oracle 10g           

Project Planning for Database Migration to Oracle 10g 

Staffing Requirements for Database Migration 

Database Architecture Differences and Oracle 10g         

Oracle 10g Database Architecture           

Microsoft SQL Server Architecture          

Introduction to the MySQL Database Architecture           

SQL Differences and Oracle SQL               

MySQL SQL Versus Oracle SQL Constructs            

Microsoft SQL Server 2000 SQL versus Oracle SQL            

Data Type Issues:  MySQL and Oracle 10g            

Data Type Issues with Microsoft SQL Server 2000 and Oracle 10g              

Stored Procedures and Oracle PL/SQL    

Microsoft Transact SQL and Oracle PL/SQL          

Migration Workbench: The Preferred Method for Database Migration  

Conclusion

               

Chapter 2:  Overview of the Oracle Migration Workbench

          

Introduction to Oracle Migration Workbench    

Oracle Migration Workbench and the APEX Migration Workbench          

Oracle 10g Migration Workbench Components 

The Oracle 10g Application Express (APEX) Migration Workbench           

Oracle 10g APEX and the Oracle 10g APEX Migration Workshop Suite     

Oracle 10g APEX Components   

A Closer Look at Oracle 10g APEX Migration Workshop 

Benefits of the Oracle 10g APEX Migration Workshop    

Oracle 10g APEX Migration Workshop Components        

Migrating MS Access Applications           

Oracle Application Express 3.0   

Oracle 10g SQL Developer Migration Workbench              

Oracle 10g Exporter Tool              

Oracle 10g SQL Developer Migration Workbench             

Oracle 10g Database Migration Verifier

Conclusion        

 

Chapter 3:  Installation Planning and Configuration for the Oracle 10g Migration Workbench 

   

Introduction to Oracle 10g Workbench Migration Tools

Prerequisites: Oracle 10g Migration Workbench Installation      

Preparation for Oracle Migration Workbench Installation            

Required Software for the Oracle Migration Workbench               

Downloading the Oracle Migration Workbench Software             

Download the Required Plugin Software              

Downloading Third Party Drivers for the OMWB Environment    

Download Relevant Software for MySQL Platform          

Oracle 10g SQL Developer Migration Workbench             

Configuration Summary              

Preparing the Database Environment to Migrate to Oracle 10g 

Required Privileges for the New Oracle 10g Migration Workbench Schema           

Creating a Connection with SQL Developer 1.2 

Migration Repository Creation for SQL Developer           

Setup for MySQL, Access and SQL Server             

Migration Setup for Access         

Database Connections - MySQL and MS SQL Server 2000              

Configure JDBC Drivers 

Install JDBC driver files for MySQL and SQL Server            

Configure JDBC for SQL Developer           

Downloading and Installing the JDBC Drivers for MySQL               

Oracle 10g Application Express (APEX) Migration            

Installation Procedures for APEX 3.0 Migration Software              

Oracle 10g Database Verifier (DMV)       

Oracle 10g Migration Workbench (OMWB) Installation 

Install the Plugins for the Oracle Migration Workbench 

Installation Process of Oracle 10g Migration Workbench              

Verify Setup for Oracle 10g Migration Workbench           

Conclusion        

References

               

Chapter 4:  Configuration Tasks for the Oracle 10g Migration Workbench 

           

Configuration - The Oracle Migration Workbench           

Building the Source and Target Models 

Source Database Capture           

Choosing between Online and Offline Data Capture     

Advantages and Disadvantages of Online Capture Method        

Advantages and Disadvantages of Offline Capture Method       

Conclusion        

References        

 

Chapter 5:  Online Capture Process for the Oracle 10g Migration Workbench 

   

Introduction to the Online Capture Process       

Online Capture with Oracle 10g Migration Workbench 

The Automation Process for Online Capture with the Oracle 10g Migration Workbench  

Beginning to Use the Migration Workbench Environment           

Using SQL*Plus for Examining New Repository Objects 

Using OMWB Capture Wizard    

Errors During Source Model Creation     

Online Capture for MySQL Database to Oracle 10g/11g 

Prepare the MySQL Test Environment    

Automation Process and Configuration Parameters for MySQL 

Oracle SQL Developer Migration Workbench                                         

Online Capture for MS SQL Server 2000 to Oracle 10g/11g with the SQL Developer Workbench    

Conclusion        

References

               

 

Chapter 6:  Performing Offline Capture for the Oracle 10g Migration Workbench 

           

The Offline Capture Process      

Offline Capture with Oracle 10g Migration Workbench 

Scripts for Use with Microsoft SQL Server             

Microsoft SQL Server BCP Scripts - Offline Capture         

Files Created By Offline Capture for MS SQL Server          

Scripts for Use with Sybase Adaptive Server        

Scripts for Use with MySQL- Offline Capture      

Offline Capture with MySQL with SQL Developer               

Scripts for Offline Capture- Informix Database 

Conclusion        

References

               

Chapter 7:  Mapping the Source Model to Target Database       

 

Introduction     

Building the Source Model Oracle 10g Migration Workbench     

Capturing the Source Database for Microsoft SQL Server              

Cleanup After the Migration Process      

Building the Source Model for MySQL and SQL Developer           

Migrating the MySQL Source Model to Oracle Using SQL Developer        

Overview of OMWB Tools: The Editors 

Tablespace Discovery Editor       

Code Parser Editor          

SQL Developer Editors and Tools             

Exporter for Migrating MS Access to Oracle        

Translation Scratch Editor for SQL Developer      

Troubleshooting Migration Issues: Common Problems and Solutions    

Problems with Source and Target Model Mapping          

Issues with SQL Developer Migration Tools        

Problems with Quick Migrate for SQL Developer 1.5        

Incorrect JDBC Driver Versions for SQL Developer              

Completing the Initial Migration Using OMWB 

Completing the Initial Migration Using SQL Developer 

Generate Data Move Scripts with SQL Developer              

Offline Capture Scripts for MySQL Using SQL Developer               

Generating Schema and DDL Creation Scripts    

Conclusion        

References        

 

Chapter 8:  Understanding the Oracle Database Migration Utilities  

      

Introduction     

OMWB Verification Tool             

Features of the Oracle Database Migration Verifier Tool (DMV)

Installation Process for OMWB Verification Tool             

Requirements for DMV Installation for Oracle    

Summary of Steps to Install and Configure DMV for Oracle         

Using the Oracle Database Migration Verifier                                     

Using OMWB Editors     

Log Window Tool in OMWB        

Migration Script Generation       

SQL Developer Migration Tools and Utilities      

SQL Developer Translation Scratch Editor             

Installation for Database Plugins for SQL Developer        

Oracle APEX Migration Tools     

Conclusion        

References

               

Chapter 9:  Testing and Verification of Database Migration         

Introduction     

Data Verification Testing            

Performance Tuning     

User Acceptance Testing             

Deployment Testing       

Functional Testing          

Quality Assurance (QA) Testing

Conclusion        

References        

 

Chapter 10:  Conclusion of Oracle Database Migration  

Book Conclusion             

Lessons Learned From the Trenches      

Prepare, Prepare, Prepare!         

Call for Backup 

Standby to the Rescue  

Appendix and Bibliography       

Index   

 

 


INDEX

#

#mytemp_table

 

A

Active Session History

Ada object oriented language

administration arena

Agile

ANSI-92 SQL Standard

APEX 3.0 directories

APEX 3.0 Migration Software

APEX Migration Workshop Components

Application Builder

Application Express (APEX) Migration 3.1

Application Express (APEX) Migration Workbench

archive log file

Archiver process (ARCH)

Associate Migration Repository

 

B

BCP script

browser_path

 

C

c:\offline_scripts\mysql

C:\omwb\log\Database

Capture Schema232

Capture wizard

Captured Objects

cast function

character data types

Checkpoint Process (CKPT)

clustering standby technology

Code Parser Editor

Commit Count

Configure JDBC for SQL Developer

convert function

cr_miguser.sql

create.sql

 

D

Dat files

Data Compare Testing

Data Guard standby database

Data Move Scripts

data type conversions

Data Type Mappings

Data verification testing

database API layer

database creation script

Database migration

Database Migration Verifier

database replication methods

database upgrade assistant

database writer process (DBWR)

datatype mapping editor

DB2/UDB

dbinfoss2k.prop

dbms_stats

default offline capture scripts

delete mytemp_table

deployment testing

Disaster recovery testing

Discovered tablespaces

dmv_install_dir

DMVReport.txt

drop table #mytemp_table

drop.sql

DSN setting

DTS package

 

E

Err files

Export and Import Method

 

F

failed repository objects

functional testing

 

G

General tab

Generated tablespaces

get_col_table_5.bat

get_constraint_5.bat

get_max_col_5.bat

get_stored_proc_5.bat

get_table_5.bat

global temporary table

GoldenGate Software

graphical interface

 

H

hot standby site

HP NonStop

 

I

IBM DB2

IBM DB2 UDB

IDE

Informix

Informix database

InnoDB storage engine

Insert Batch Size

interprocess communication

 

J

jar files

Java Database Connectivity

Java Development Kit

Java J2EE

JDBC Drivers

jTDS driver

jtds-1.2.2-dist.zip

jtds-1.2.jar

jtds-1.2-dist.zip

 

K

kick-off meeting

 

L

local temporary table

Log Window Tool

log write (LGWR)

Logging

 

M

Mapped tablespaces

master_5.bat

materialized views

MAXDB30

Memory storage engine

Microsoft Access database

Microsoft Access Exporter

Microsoft Cluster Server

Microsoft Cluster Services

Microsoft Project

Migrate Data

migration plugins

Migration Script Generation

migration scripts

Migration Setup for Access

migration wizard

Migration Workbench tool

migrations feature

MS Access 2003

MS Access 97

MS SQL Server

msdb database

myISAM storage engine

MySQL

MySQL database architecture

MySQL JDBC files

MySQL Test Environment

mysql5.ocp

mysql-connector-java.zip

mysql-connector-java-5.0.4.tar.gz

mysql-connector-java-5.0.4-bin.jar

mysqldump

 

N

NDB

network configuration file

Northwind database

northwind_INFO.txt

 

O

ODBC DSN

offline capture

Offline capture

OMWB installation

omwb.bat

OMWB_Install_Dir

OMWB_install_dir/Omwb/drivers

OMWB_install_dir\Omwb\drivers

omwb_offline_capture.bat

OMWB_OFFLINE_CAPTURE.BAT

OMWB_OFFLINE_CAPTURE.SH

omwb_rep repository

Omwb\bin

on delete cascade

online capture

Online Parallel Data Movement

online redo log file

Online Source Model Load

operating system

Oracle 10g Database Architecture

Oracle 10g Exporter Tool

Oracle 10g Migration Workbench (OMWB) tool

Oracle 10g SQL Developer Migration Workbench

Oracle Application Express (APEX)

Oracle Application Express 3.0

Oracle Database Migration Verifier (DMV)

Oracle EnterpriseDB

Oracle Migration Workbench

Oracle Migration Workbench User’s Guide

Oracle Streams

Oracle System Global Area

Oracle Transparent Gateways

Oracle Workbench Repository

 

P

parameter file

parameter initialization (pfile)

password file

pilot migration

PMON (Process Monitor)

Program Global Area

properties.sql

PropertiesFilePath

 

Q

Quality Assurance Testing

Quest Shareplex

Quest® Shareplex

quick migrate

Quick Migrate

Quick Migration Wizard

 

R

Real Application Clusters

Recovery manager

Recovery Manager

ReportStorePath

Repository Management

repository schema

RMAN hot and cold backup

Row Prefetch Size

 

S

sanity test process

SAP R/3 Basis

schema copying and DDL generation

Scrum

Server Level Copy and Cloning

Source and Binaries file

source and target models

Source Database Capture

SQL Developer

SQL Developer 1.5

SQL Developer interface

SQL Performance Analyzer

SQL Server replication

SQL Workshop

SQLServer2K.jar

ss2k_bcp_script.bat

staffing and development

state.properties

state.properties configuration

state.properties file

static parameter file

Statspack

stored procedures

Streams based replication

Stress testing

SwisSQL

Sybase

Sybase Adaptive Server

sysmaster directory

System Monitor (SMON)

 

T

Tablespace Discoverer

TEMP tablespace

tempdb database

third party database drivers

to_char

to_date

Transact SQL

transact SQL (T-SQL) scripts

transaction log file

Translation Diff Viewer

Translation Scratch Editor

Transportable tablespaces

Txt files

 

U

UDFs (Universal Disk Formats)

usability testing

User Acceptance Testing

Utilities interface

 

V

v$active_session_history

v$sql

v$sqlarea

v$sqltext

Validation Testing

 

W

Waterfall

Reviews

From the series Editor:

Errata:

   

Hit Counter

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks