Oracle Migration Workbench - DBGuide.net · 2004-10-04 · 18 Oracle Migration WorkBench yFull...
Transcript of Oracle Migration Workbench - DBGuide.net · 2004-10-04 · 18 Oracle Migration WorkBench yFull...
Oracle Migration Workbench (Release 10.1.0.2.0) ; 오라클데이타베이스로이전하기
고 봉 수DB 기술팀한국오라클
3
Agenda
Migration is….Why Migrate to Oracle?How to Migrate to OracleOracle Migration WorkbenchQ&A
4
Migration is…
5
Migration is
Migration is the conversion of existing
applications into a new environment and a
new architecture, preserving data and
functionality of the old application.
6
Why Migrate to Oracle?
7
We are seeing an increase in the demand to consolidate on Oracle
Sybase
Informix
SQL Server Competing databases are becoming obsoleteDesire to move to Linux for higher availability and better performanceDesire to reduce total cost of ownership
8
Why Migrate to Oracle
Top 5 Business Drivers To Migrate To Oracle DB
1. Improved PerformanceScalability
2. Company Direction3. Consolidation4. Latest Technology5. Improved Security
Migration Technology Group SurveyJuly 2003
9
10 Reasons to Choose Oracle
1. Database Market Leader2. Proven Performance3. Innovative Database
Technology4. Leading Database for
Enterprise Applications5. Linux Platform (Oracle co-
develops Red Hat Advanced Server )
1. Database Market LeaderProven PerformanceInnovative Database TechnologyLeading Database for Enterprise ApplicationsLinux Platform (Oracle co-develops Red Hat Advanced Server )
6. Open Standards for Application Development, SQL, JDBC, ODBC, XML
7. Oracle 10g Grid Real Application Clusters for Scalability
8. High Availability Solutions9. Database Security10. Data Warehousing and
Business Intelligence Features
Open Standards for Application Development, SQL, JDBC, ODBC, XMLOracle 10g Grid Real Application Clusters for ScalabilityHigh Availability SolutionsDatabase SecurityData Warehousing and Business Intelligence Features
10
How to Migrate to Oracle
11
Migration Factory for DB and associated custom applications
SybaseSQL Server
Informix
Applications
Migration Experts
Migration Tools
Technology Migration Factory
Methodology
Applications
Only applications written in one of the following are covered:C, C++, Java, XMLVisual Basic, PERL and other scripting languages
12
Migration ProcessMigration ProcessMigration Process
• Database Migrations consists of:• Planning• Performing the Migration
• Schema Conversion• Data Conversion• Procedure Conversion• Handling special cases which
require manual intervention• Testing & validating• Taking advantage of Oracle features
13
Phases of the Migration Process
DeploymentTestMigrationDetailAnalysis
High LevelAnalysis
• Cost• Risk• Time• Expertise
Challenges:
Oracle deliversToolkits that overcome these
migration challenges
Oracle deliversToolkits that overcome these
migration challenges
14
Oracle Migration Workbench
15
Oracle Migration WorkBench(OMWB)
Intuitive user interface, supported by migration wizardsSupports complete database migration– Schema & Data– Triggers & Stored Procedures
Customization supported prior to generationRelease 10.1.0.2
16
Migration Workbench Architecture
Migration Workbench andWorkbench Plug-in
WorkbenchRepository
SourceModel
OracleModel
Source Database(Sybase, SQL Server,Informix, DB2..)
Destination Database(Oracle 7,8,8i,9i,10g)
17
Migration Workbench & Plugin
Plugin Workbench
Source ModelUser Interface
Services
LoadingLoading
MappingMapping
Source Database Access
SourceModel
SourceModel
ProjectProject Oracle ModelOracle Model
GenerationGeneration
ProjectProject
DependencyManagementDependencyManagement
Data MovingData Moving
WorkbenchModel
WorkbenchModel
OracleModelOracleModel
Oracle Database Access
Services
User Interface
Workbench Repository
Source Database Oracle
18
Oracle Migration WorkBenchFull support for the Oracle10g Database More efficient locking algorithmsSupport for Oracle10g IEEE binary precision data types enabling faster look-ups Enhanced regular expression support providing performance gains in PL/SQL and PRO*C code generation Significant enhancements to the Informix plug-in Full support for the migration MySQL 4.X schema including foreign key constraints Productivity enhancements to reduce overall migration times
19
Oracle Migration WorkBenchMigrates a range of third-party databases to the Oracle platform.Stores information about the structure of the production databases in a repository where you can make changes. Retrieves source database information via an online capture or an off-line capture.Parses and transforms stored procedures, triggers, and views to Oracle PL/SQL and Pro*C. Provides advanced customization capabilities such as the ability to change data type mappings, and delete and rename objects.
20
Oracle Migration WorkBench
Generates reports about the status of the migration. Generates the DDL scripts for the creation of the destination Oracle database. Generates SQL*Loader scripts for data movement. Allows you to select existing tablespaces in the destination Oracle database to which objects can be migrated. Displays informational, error, and warning messages about the migration in the Progress window. Resolves object name conflicts, such as conflicts with Oracle reserved words, automatically.
21
Migration Steps in OMWBSource
Database
Online CaptureCapturing the Source Database
Offline Capture
Source Model
Mapping( Creating andCustomizing the Oracle Model )
Oracle Model
OnlineSchemaCreation
OfflineSchemaCreation
OnlineData
Transfer
OfflineData
TransferMigrating data from the SourceDatabase to the Oracle Database
DestinationDatabase
22
Supported Database
Third-Party Database Version(s)
Microsoft SQL Server 6.5, 7.0, 2000
Microsoft Access 2.0,95,97,2000
Informix Dynamic Server 7.3, 9.1,9.2,9.3, 9.4
Sybase Adaptive Server 11, 12
MySQL 3.22, 3.23,4.X
IBM DB2/400 V4R3, V4R5
IBM DB2 UDB V6, V7.1 and V7.2 (All Beta)
ThirdThird--Party Database Version(s) Party Database Version(s)
Microsoft SQL ServerMicrosoft SQL Server 6.5, 7.0, 20006.5, 7.0, 2000
Microsoft AccessMicrosoft Access 2.0,95,97,20002.0,95,97,2000
Informix Dynamic ServerInformix Dynamic Server 7.3, 9.1,9.2,9.3, 9.47.3, 9.1,9.2,9.3, 9.4
Sybase Adaptive ServerSybase Adaptive Server 11, 1211, 12
MySQL MySQL 3.22, 3.23,4.X3.22, 3.23,4.X
IBM DB2/400IBM DB2/400 V4R3, V4R5V4R3, V4R5
IBM DB2 UDBIBM DB2 UDB V6, V7.1 and V7.2 (All Beta)V6, V7.1 and V7.2 (All Beta)
Oracle Migration Workbench Data Sheet , page 2
23
Supported Object
Schema objects migrated to User objects.
N/AENUM Support
N/ARelations, link tables,app code reuse.
N/AOther Features
N/ANN/AESQL/C to Pro*CN/AN/AEmbedded SQL
NNN/AYN/AYTriggers
NNN/AYN/AYStored Procedure
YYYN/AN/AYUser Defined Types
YYYYNYPrivileges
YYYYY
Validation rulesYConstraints
YYYYNYUsers
YNN/AYN/AYGroup/Roles
YYYYYYIndexes
NYN/AYY Queries(Limited)YVIEWS
YYYYYYTABLES
IBM DB2 UDB
IBM DB2400
MySQLInformixMS Access
MS SQL Server & Sybase
FeatureSupported
24
Capture
Provides a visual representation of the source database.– Oracle equivalent form– Wizard based intuitive user interface
The source model is generated from the source meta data.Information about the structure of the source databases is stored into a repository.Customizing the source model is allowed.
25
Capture Modes
Online capture– Direct source capture via a JDBC connection
Offline capture– Offline source capture via scripts or tools which
generates metadata dump files.– Migration Workbench can read the source data from
the files in an offline mode.– Offers increased flexibility.
26
Capture SampleSource Model(MS SQL Server 2000)
27
Mapping
Maps the source model to the oracle model.During the map phase, the map wizard maps objects in the source model to their Oracle equivalents.The wizard also converts logic and security structures and populates it into a repository, creating an Oracle Model.
28
Correcting Errors
Find the Source Model schema object the cause the error.Modify a property of schema object in the source model.Remap the source model
29
Mapping Sample
30
Customizing the Oracle Model
Modify the Properties of a Schema Object.Working with Tablespaces.– Create new tablespace– Change the default tablespace
Rename a tablespaceUsing existing tablespaces
Changing the default user password.– Change the default password, ‘oracle’ to other.
31
Customizing Sample
Source Model(MS SQL Server 2000)
Oracle Model(Oracle 10g)
32
Migrating the Database
Adjust the Oracle Model to the destination DatabaseMigration Orders
– Migrate tablespaces, users and tables– Migrate data– Migrate the remaining schema objects
Migration Methods– Creating the schema and transferring the data online– Creating the schema online and transferring the data offline– Creating the schema and transferring the data offline
33
Migrating Sample
34
Oracle Migration ArchivesOracle Migration Technology Center
– http://otn.oracle.com/tech/migration/index.htmlOracle Workbench Home
– http://otn.oracle.com/tech/migration/workbench/index.htmlWorkbench White paper
– http://otn.oracle.com/tech/migration/workbench/pdf/omwb_wp.pdfMigrate a Microsoft SQL Server Application to Oracle
– http://otn.oracle.com/tech/migration/ama/docs/ama_wp.pdfWorkbench Manuals
– http://otn.oracle.com/documentation/migration.htmlMigration Solution Directory
– http://otn.oracle.com/tech/migration/mti/index.html
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S