Date: March 2011
Oracle SQL Developer incorporates migration support, providing users with the ability to migrate database objects and data from IBM DB2, MySQL, Microsoft SQL Server, Microsoft Access, Sybase and Teradata to Oracle. This document details the setup steps to help prepare your environment for a database migration and then provides a brief description how the new Migration Wizard works and each of the main steps of a migration. For a table of specific supported versions see this Supported Platforms document.
You only need to create a repository once, you can use it again and again for each migration you do.
In order use SQL Developer Migrations you need to create a Migrations Repository. This is a database schema that stores the meta-data collected and transformed in the migration process. This repository consists of 42 tables, 24 views and the primary keys and indexes required, along with triggers and PL/SQL code. We recommend that you configure a dedicated database schema for this purpose, although this is not necessary.
In order to build repository in a new schema for the meta-data, the user requires the following role and system privileges:
Once you have created the schema and granted the above privileges, create a connection for the user and you are ready to install the repository. See the SQL Developer documentation on creating users and setting up connections.
In SQL Developer, select Tools > Migration > Repository management > Create Repository... and select the connection you created for your repository owner.
This will take a short time, during which a progress bar will be shown.
You now need to configure a database connection for the database which you would like to migrate. There are two steps to this process:
Configuration of JDBC drivers needs to be carried out only once per third-party connection (MySQL, Microsoft SQL Server and Sybase), whereas the second step is carried out for each database you wish to migrate.
JDBC is the Java Database Connectivity standard and it provides a mechanism for Java programs to connect to databases. For more information on JDBC see http://java.sun.com/javase/technologies/database/
To access databases using JDBC, we must use a JDBC driver. You can get this from the Database vendor, which typically offers JDBC drivers as free downloads.
This version of the migration workbench has been tested using the following JDBC Drivers.Once downloaded you need to expand the driver binary jar file to a location on your machine. The driver binary jar file is typically a separate jar file located inside the downloaded archive file:
Once downloaded you need to expand the driver binary jar file to a location on your machine. The driver binary jar file is typically a separate jar file located inside the downloaded archive file. Once you have extracted the relevant jar file to your disk, you need to configure SQL Developer to tell it where this driver resides:
Oracle SQL Developer also supports the use of Check for Updates to install the drivers required for MySQL, Microsoft SQL Server and Sybase. To use Check for Updates, start SQL Developer and select Help -> Check for Updates Follow the wizard as follows:
Note: You need to use the method described in 2.1 Configuring JDBC Drivers to install the drivers for IBM DB2 and Teradata.
You should now add the third-party connection for the database you wish to migrate from.
To do this:
Once configured, you can explore your third-party database just like other Oracle SQL Developer connections.
Oracle SQL Developer 3.0 supports both Online and Offline migrations.
Oracle SQL Developer 3.0 introduces the concept of a Migration Project and uses a Migration Wizard to walk you through the migration process. To start select your third-party database connection, right-click the connection and select Migrate to Oracle...
You'll need to know a few details for the Migration Wizard:
Follow the steps of the Migration Wizard.Once the Migration Wizard has completed, if not already available, open the Migration Projects navigator by selecting View > Migration Projects. Your new migration project appears in the list. Expand the new project to see the Captured Database Objects and Converted Database Objects. Expand the Converted database Objects and see the list of users created. These are the names of the new users in your Oracle Database. To review the migrated data, you need to create connections for the new users.
The Migration Wizard works though the following process. Once complete you can re-enter the process at any point. This re-opens the wizard and completes the phases as required.
This step is the first step in the migration process. It is used to capture a snapshot of the current state of your third-party database and is necessary to provide the Migration Workbench with a "point in time" view of your database. Once this step is complete, the Migration wizard works on the meta-data stored in its repository, instead of issuing queries against your live database.
This phase in the online migration uses the connection you provided to the third-party database and the output is displayed in the Captured Database Objects node under the new Migration Project.
The next step in the migration process is to convert the captured model of the database to an Oracle-specific model. The captured model contains data types, naming schemes etc. defined by your database vendor; this now must be converted to Oracle formats. Once the migration has completed, you can return the Captured Database Objects node and rerun the wizard from this point to convert some or all of the objects again.
To convert the captured model:
Once the conversion process has completed, the SQL Developer Migrations has a model of what the converted database will look like. This is used to generate SQL scripts for the creation of the new Oracle Database schema(s) and to run these scripts. NB: This stage of the process requires access to a privileged Oracle user, like SYSTEM or another user who has the ability to create users, tables and so on. It is this user (connection) that is used to execute the scripts.
These generated scripts will be in the directory you specified in the Wizard. To generate these scripts:
The Migration Wizard generates the necessary SQL for creating your database schema(s).
The last step in the Migration Wizard is to migrate the data to the new database. Migrating the data is a process that copies the data from the third-party database to the new tables in the Oracle database. The Migration Wizard uses the same Oracle database connection required to run the scripts as it does to move the data.
To re-enter the Migration Wizard and move the data:
This once again invokes the Migration Wizard and
Once complete, your data should now be in your newly created database schema.
Oracle SQL Developer 3.0 introduces a Copy to Oracle option that allows users to copy tables and data from a third-party database to Oracle without the need of a migration repository. This quick migrate option is very useful for quickly copying a few tables to Oracle and requires no additional setup. This feature is only available for copying tables and the data; use the Migration Wizard if you are migrating more than that.
To use Copy to Oracle, select the third-party connection created in 2. Adding a Third Party Connection above. This invokes a connection dialog that enables you to select the Oracle connection where you want to copy the table(s) and click Apply.
Note: MS Access: We recommend the use of Copy to Oracle for MS Access users, as this does not require any system privileges to access the database metadata. For MS Access connections, the Copy to Oracle option is also available on right-click of the connection to easily enable all tables to be copied.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-703810/，如需转载，请注明出处，否则将追究法律责任。