Migration: Basic Options and Steps

To migrate all or part of a third-party database to Oracle, you have the following basic options:

However, before you perform any migration actions, you may want to prepare by setting any appropriate Migration user preferences (such as date and timestamp masks and Is Quoted Identifier On?) and by reading relevant topics in Migration: Background Information and Guidelines.

After you migrate by using the wizard or by copying tables to Oracle, verify that the results are what you expected.

For a description of the user interface for database migrations, see SQL Developer User Interface for Migration.


Tip:

For a walk-through of a typical migration, go to the sqldeveloper\sqldeveloper\bin folder and enter the following command:
sdcli migration -help=guide

For information about the SQL Developer command-line interface, see Command-Line Interface for SQL Developer.


Migrating Using the Migration Wizard

The Migration wizard provides convenient, comprehensive guidance through the actions that can be involved in database migration (capturing the source database, converting it to Oracle format, generating DDL to perform the conversion, and so on). This is the recommended approach when performing a migration: you can resolve issues during these phases, and you can then inspect or modify objects to suit your needs.

The migration wizard is invoked in a variety of contexts, such as when you right-click a third-party database connection and select Migrate to Oracle or when you click Tools, then Migration, then Migrate. Sometimes the wizard is invoked at a page other than the first step.

On all pages except the last, enabling Proceed to Summary Page causes Next to go to the Summary page.

Repository

The Repository page of the wizard requires that you specify the database connection for the migration repository to be used.

The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. If you do not already have a migration repository and a database connection to the repository, create them as follows:

  1. Create an Oracle user named MIGRATIONS with default tablespace USERS and temporary tablespace TEMP; and grant it at least the RESOURCE role and the CREATE SESSION and CREATE VIEW privileges. (For multischema migrations, you must grant the RESOURCE role with the ADMIN option; and you must also grant this user the CREATE ROLE, CREATE USER, and ALTER ANY TRIGGER privileges, all with the ADMIN option.)

  2. Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

  3. Right-click the Migration_Repository connection, and select Migration Repository, then Associate Migration Repository to create the repository.

If you do not already have a database connection to the third-party database to be migrated, create one. (For migrations other than from Microsoft Access, you should set the third party JDBC driver preference before creating the connection.) For example, create a database connection named Sales_Sybase to a Sybase database named sales.

Connection: The database connection to the migration repository to be used.

Truncate: If this option is enabled, the repository is cleared (all data from previous migrations is removed) before any data for the current migration is created.

Project

The Project page of the wizard specifies the migration project for this migration. A migration project is a container for migration objects.

New lets you create a new project, or Existing lets you select from a list of existing projects.

Name: Name to be associated with this migration project.

Description: Optional descriptive comments about the project.

Output Directory: The directory or folder in which all scripts generated by the migration wizard will be placed. Enter a path or click Choose to select the location.

Source Database

The Source Database page of the wizard specifies the third-party database to be migrated.

Mode: Online causes the migration to be performed by SQL Developer when you have completed the necessary information in the wizard; Offline causes SQL Developer to perform the migration using a file (the Offline Capture Source File) that you specify.

Connection (Online mode): The database connection to the third-party database to be migrated. To add a connection to the list, click the Add (+) icon; to edit the selected connection, click the Edit (pencil) icon.

Available Source Platforms (Online mode) List of third-party databases that you can migrate. If the desired platform is not listed, you probably need the appropriate JDBC driver, which you can get by clicking Help, then Check for Updates, or by clicking the Add Platform link and adding the necessary entry on the Database: Third Party JDBC Drivers preferences page.

Offline Capture Source File (Offline mode): The .ocp file (or .xml file for a Microsoft Access migration). This is a file that you previously created by clicking Tools, then Migration, then Create Database Capture Scripts.


Cannot Connect error:

If you receive the Cannot Connect error, this means that the .ocp file that is normally in the generated Offline Capture data is not present to identify the type of database, and therefore SQL Developer cannot select the appropriate plugin to perform the conversion. Ensure that the correct, valid .ocp file is present.

Capture

The Capture page of the wizard lets you specify the database or databases (of the platform that you specified) to be migrated. Select the desired items under Available Databases, and use the arrow icons to move them individually or collectively to Selected Databases.

Convert

The Convert page of the wizard lets you examine and modify, for each data type in the source database, the Oracle Database data type to which columns of that source type will be converted in the migrated database. For each source data type entry, the possible Oracle Data Type values reflect the valid possible mappings (which might be only one).

Add New Rule: Lets you specify mappings for other source data types.

Edit Rule: Lets you modify the mapping for the selected source data type.

Advanced Options: Displays the Migration: Identifier Options preferences page.

Translate

The Translate page of the wizard lets you specify the SQL objects to be translated. Select the desired items under Available SQL Objects, and use the arrow icons to move them individually or collectively to Selected SQL Objects.

Target Database

The Target Database page of the wizard specifies the Oracle database to which the third-party database or databases will be migrated.

Mode: Online causes the migration to be performed by SQL Developer when you have completed the necessary information in the wizard; Offline causes SQL Developer to generate scripts after you have completed the necessary information in the wizard, and you must later run those scripts to perform the migration.

Connection: The database connection to the Oracle Database user into whose schema the third-party database or databases are to be migrated. To add a connection to the list, click the Add (+) icon; to edit the selected connection, click the Edit (pencil) icon.

Generated Script Directory: The directory or folder in which migration script files will be generated (derived based on your previous entry for the project Output Directory).

Drop Target Objects: If this option is enabled, any existing database objects in the target schema are deleted before the migration is performed (thus ensuring that the migration will be into an empty schema).

Advanced Options: Displays the Migration: Generation Options preferences page.

Move Data

The Move Data page of the wizard lets you specify options for moving table data as part of the migration. Moving the table data is independent of migrating the table definitions (metadata) Note that if you do not want to move the table data, you can specify the mode as Offline and then simply not run the scripts for moving the data.

Mode: Online causes the table data to be moved by SQL Developer when you have completed the necessary information in the wizard; Offline causes SQL Developer to generate scripts after you have completed the necessary information in the wizard, and you must later run those scripts if you want to move the data. (Online moves are convenient for moving small data sets; offline moves are useful for moving large volumes of data.)

Connections for online data move: The Source and Target connections for the third-party and Oracle connections, respectively. To add a connection to either list, click the Add (+) icon; to edit the selected connection, click the Edit (pencil) icon.

Truncate Data: If this option is enabled, any existing data in a target (Oracle) table that has the same name as the source table is deleted before the data is moved. If this option is not enabled, any data from a source table with the same name as the corresponding target (Oracle) table is appended to any existing data in the target table.

Summary

The Summary page of the wizard provides a summary of your specifications for the project, repository, and actions, in an expandable tree format. If you want to make any changes, go back to the relevant wizard page.

To perform the migrat6ion actions that you have specified, click Finish.

Copying Selected Tables to Oracle

To copy one or more tables from a third-party database to an Oracle database, you can select the third-party tables and use the Copy to Oracle feature. With this approach, you do not need to create or use a migration repository, or to capture and convert objects.

Note that this approach does not perform a complete migration. It only lets you copy the table, and optionally the table data, from the third-party database to an Oracle database. It does not migrate or re-create primary and foreign key definitions and most constraints. (Any UNIQUE constraints or default values are not preserved in the copy. NOT NULL constraints are preserved in most cases, but not for Microsoft Access tables.) The approach also does not consider any non-table objects, such as procedures.

In addition, this approach supports autoincrement columns only if the INCREMENT BY value is 1, and if the sequence starts at 1 or is adjusted to MAX VAL + 1 at the first call to the trigger.

If these restrictions are acceptable, this approach is fast and convenient. For example, many Microsoft Access database owners only need the basic table definitions and the data copied to an Oracle database, after which they can add keys and constraints in the Oracle database using SQL Developer.

To copy selected tables, follow these steps:

  1. Create and open a database connection for the third-party database. (For migrations other than from Microsoft Access, you should set the third party JDBC driver preference before creating the connection.)

    For example, create a database connection named Sales_Access to a Microsoft Access database named sales.mdb, and connect to it.

  2. In the Connections navigator, expand the display of Tables for the third-party database connection, and select the table or tables to be migrated.

    To select multiple tables, use the standard method for individual and range selections (using the Ctrl and Shift keys) as appropriate.

  3. Right-click and select Copy to Oracle.

  4. In the Choose Database for Copy to Oracle dialog box, select the appropriate entries:

    Destination Database Name: Database connection to use for copying the selected tables into the Oracle database. (Only Oracle Database connections are shown for selection.)

    Include Data: If this option is enabled, any data in the table in the third-party database is copied to the new table after it is created in the Oracle database. If this option is not enabled, the table is created in the Oracle database but no data is copied.

    If Table Exists: Specifies what happens if a table with the same name as the one to be copied already exists in the destination Oracle database: Indicate Error generates an error and does not perform the copy; Append adds the rows from the copied table to the destination Oracle table; Replace replaces the data in the destination Oracle table with the rows from the copied table. Note that if the two tables with the same name do not have the same column definitions and if Include Data is specified, the data may or may not be copied, depending on whether the source and destination column data types are compatible.

  5. To perform the copy operation, click Apply.

If a table with the same name as the one to be copied already exists in the destination Oracle database, then:

Related Topics

SQL Developer: Migrating Third-Party Databases

Migration: Background Information and Guidelines

SQL Developer User Interface for Migration