Before You Start Migrating: General Information

You may need to perform certain tasks before you start migrating a third-party database to an Oracle database. See the following for more information:

■ Section 2.4.1, "Creating a Database User for the Migration Repository"

■ Section 2.4.2, "Requirements for Creating the Destination Oracle Objects"

■ Section 2.4.3, "Using Nonstandard Character Encoding on Windows Systems"

See also any information specific to the source database that you will be migrating, as explained in Section 2.5.

2.4.1 Creating a Database User for the Migration Repository

SQL Developer requires a migration repository to migrate a third-party database to an Oracle database. To use an Oracle database for the migration repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to create a user named MIGRATIONS, create a database connection to that user, and use that connection for the migration repository; and if you wish, you can later delete the MIGRATIONS user to remove all traces of the migration from the database.

When you create a user for migrations, specify the tablespace information as in the following example, instead of using the defaults for tablespaces:

CREATE USER migrations IDENTIFIED BY password

DEFAULT TABLESAPACE users TEMPORARY TABLESPACE temp,

Do not use a standard account (for example, SYSTEM) for migration.

Note: Oracle recommends that you make a complete backup of the source database before starting the migration. For more information about backing up the source database, see the documentation for that type of database.

When SQL Developer creates a migration repository, it creates many schema objects that are intended only for its own use. For example, it creates tables, views, indexes, packages, and triggers, many with names starting with MD_ and MIGR. You should not directly modify these objects or any data stored in them.

2.4.2 Requirements for Creating the Destination Oracle Objects

The user associated with the Oracle database connection used to perform the migration (that is, to run the script containing the generated DDL statements) must have the following roles and privileges:

Roles

CONNECT WITH ADMIN OPTION RESOURCE WITH ADMIN OPTION

Privileges

ALTER ANY ROLE ALTER ANY SEQUENCE ALTER ANY TABLE ALTER TABLESPACE ALTER ANY TRIGGER COMMENT ANY TABLE CREATE ANY SEQUENCE CREATE ANY TABLE CREATE ANY TRIGGER

CREATE VIEW WITH ADMIN OPTION

CREATE PUBLIC SYNONYM WITH ADMIN OPTION CREATE ROLE

CREATE TABLESPACE CREATE USER DROP ANY SEQUENCE DROP ANY TABLE DROP ANY TRIGGER DROP TABLESPACE DROP USER DROP ANY ROLE GRANT ANY ROLE INSERT ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE

For example, you can create a user called migrations with the minimum required privileges required to migrate a database by using the following commands:

CREATE USER migrations IDENTIFIED BY password

DEFAULT TABLESAPACE users TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO migrations WITH ADMIN OPTION;

GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,

Note: You must grant these privileges directly to a user account.

Granting the privileges to a role, which is subsequently granted to a user account, does not suffice. You cannot migrate a database as the user SYS.

CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO migrations;

2.4.3 Using Nonstandard Character Encoding on Windows Systems

If you need to configure SQL Developer to use nonstandard character encoding, such as for multibyte character sets, on Microsoft Windows systems, follow these steps:

1. Set the JRE file encoding property as follows:

a. Using a text editor, open the SQL Developer startup file by editing the omwb.bat file located in the OMWB_install_dir/Omwb/bin directory.

b. Add the following information to the start command, before -jar:

-Dfile.encoding="file_encoding"

In the previous line, file_encoding is the required file encoding. For multibyte Microsoft Access databases, the file encoding should be set to UTF-8, and the omwb.bat file should look similar to the following:

start ..\jre\bin\javaw -ms30m -mx256m -Dfile.encoding="UTF-8" -jar ..\lib\boot.jar oracle.mtg.migrationUI.MigrationApp

If you are migrating a multibyte Microsoft Access database (for example Japanese or Chinese), the character set on the target Oracle database instance should be set to UTF8.

c. Save the file, then exit.

2. Install the appropriate font.properties file for the required file encoding as follows:

a. Make a backup copy of your existing font.properties file in the OMWB_install_

dir/Omwb/jre/lib directory.

b. Download the required Java font.properties file from the following Web site:

http://www.sun.com/

c. Rename the downloaded file to font.properties. For example, rename the Japanese font.properties file from font.properties.ja to font.properties.

d. Copy the new font.properties file into the OMWB_install_dir/Omwb/jre/lib directory.

3. If you are performing an offline capture, you must specify delimiter characters in the offline capture scripts as follows:

a. Using a text editor, open the omwb.properties file located in the OMWB_

install_dir/Omwb/bin directory.

b. Edit or add the following fields:

OFFLINE_CAPTURE_COLUMN_DELIMITER="delimiter_column"

OFFLINE_CAPTURE_ROW_DELIMITER="delimiter_row"

In the previous lines, delimiter_column is your choice of column delimiter and delimiter_row is your choice of row delimiter.

Note: These delimiter values must correspond to the delimiter values used in the offline capture scripts.

c. Save the file, then exit.

SQL Developer is now enabled to handle the new character encoding.