|
Have you just downloaded
SQL Developer? Do you want to migrate from a third party database
to Oracle and have no idea how to start or what to do next?
From install to migration, get started here, step by step. The following
document outlines what you need to do to migrate a third party database
to Oracle.
I. Preparing your Environment for Migration
- Set up a Migration Repository
- Configuring the Third Party Connection
- Configuring JDBC Drivers
- Adding a Third Party Connection
II. Migration
- Capturing your Database
- Convert your Captured Model
- Generating SQL Scripts for your Converted Model
- Running the Generation Script
- Moving your Data to Oracle
- Creating a Connection to your New Database
- Issuing Data Move
1. Set up a Migration
Repository
The Migration Workbench requires a database schema to store the
meta-data it collects and transforms for the migration process.
This repository consists of 37 tables, 8 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:
- RESOURCE
- CREATE SESSION
- CREATE VIEW
See the SQL
Developer main documentation on creating users.
We must now install a repository on this user.
To do this we must set up the connection in SQL Developer, and then
assign it to act as the migration repository.
See the SQL
Developer documentation on setting up a new connection.
Once you have the new connection, right click
on it, and select "Create Repository". You can also use
the main Migration Menu option, Repository Management.
This may take some time, during which a progress
bar will be shown.
2. Configuring the
Third Party Connection
You now need to configure a database connection for the database
which you would like to migrate. There are two steps to this process:
- Configuring JDBC drivers, and
- Configuring the third party connection
Configuration of JDBC drivers needs to be carried out only once
per third party connection (MySQL and SQL Server), whereas the second
step is carried out for each database you wish to migrate.
2.1 Configuring
JDBC Drivers
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:
- For MySQL:
- For Microsoft SQL Server:
- Microsoft Access
- No additional driver is required used the
JDBC/ODBC bridge
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:
- For MySQL you download an
archive file called mysql-connector-java-5.0.4.tar.gz (or .zip)
Inside this file, the binary driver jar file is called mysql-connector-java-5.0.4-bin.jar
- For SQL Server, when you
download the jtds distribution, you download an archive file called
jtds-1.2-dist.zip
The binary driver jar file is located within this archive in a
file called jtds-1.2.jar.
Once you have extracted the relevant jar file to your disk, you
need to configure SQL Developer to tell it where this driver resides:
- In SQL Developer choose Tools -> Preferences...
- Expand the "Database" option in
the left hand tree
- Click on "Third Party JDBC Drivers"
- Click on "Add Entry..."
- Navigate to your third party driver jar file and choose OK
2.2 Adding a Third
Party Connection
You should now add the third party connection for the database you
wish to migrate from.
To do this:
- In the main SQL Developer connection navigator click on the
green "plus" icon in the top left corner, to invoke
the new connection dialog.
- Fill in the details of your third party database. Select the
tab for the third party data you are working with. Tabs are provided
for Microsoft SQL Sever, Microsoft Access and MySQL.
Once configured, you can explore your third party database just
like other Oracle SQL Developer connections.
Oracle supports two approaches when migrating
third-party databases.
- Quick Migrate
Quick Migrate offers a wizard driven approach to simplify the
migration of third-party schemas and data to Oracle. It supports
least privilege migration. i.e. users do not require DBA privileges
to complete this migration.
- Manual Migration
Using the manual migration approach provides customization capabilities,
including the ability to choose all data type mappings, selective
object migration and the ability to drop or rename objects during
the process, giving users full control. The high level steps are
described below.
1. Capturing your
Database
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 Workbench works on the meta-data
stored in its repository, instead of issuing queries against your
live database.
To capture a database:
- Click on your third party connection
to connect to your database
- Right click on the connection and choose the
"Capture" menu item
This will read "Capture SQL Server", or "Capture
MySQL" ..etc.
NOTE: For Microsoft Access migrations,
run the exporter tool: click Migration, then Microsoft Access Exporter,
then the item for your version of Microsoft Access. Follow the steps
for the exporter tool, which has its own online help. Then, in SQL
Developer click Migration, then Capture Exporter XML, and specify
the XML file that you created using the exporter tool. You can also
launch the Microsoft Access Exporter using the context menu.
Once chosen, the Migration Workbench will begin
to capture information about your database. A progress dialog will
be displayed showing current activity during the process.
Once complete, your captured database will show up in the "Captured
Databases" window.
2. Convert your Captured
Model
The next step in the migration process is to convert the captured
model of your 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.
To convert your captured model:
- Navigate to the Captured Objects
tab (It's below the Connections tab by default)
- Select and right click on the node created
under Captured Models
- Choose "Convert to Oracle"
This invokes a data type conversion dialog.
This dialog allows you to specify data type transformations that
may be required to convert platform-specific data types to Oracle-specific
data types. For the majority of cases, it is safe to accept the
defaults here. If you wish, you can change the mappings used,
and once you have made your choices, press Apply
to start the convert process.
This process carries out a number of transformations
on your data model. A progress dialog is displayed showing current
activity during the process.
3. Generating SQL Scripts
for your Converted Model
Once the conversion process has completed,
the Migration Workbench has a model of what the converted database
will look like. We use this to generate SQL scripts for the creation
of your database schema(s).
To generate these scripts:
- Navigate to the Converted Objects
tab (It's below the Captured Objects tab by default)
- Select and right click on the converted model
node created under Converted Models
- Select "Generate"
The Migration Workbench generates the necessary SQL for creating
your database schema(s). During this process, a progress dialog
is displayed showing current activity.
Once complete, close the dialog. The generated
SQL will be opened in a new SQL worksheet window.
4. Running the Generation
Script
To create your database, you need to run
the generated script. The script is opened in a SQL Worksheet that
has the capability of running SQL scripts. The output script begins
by creating a user for the generated database schema(s). This means
that the user you run the script as needs to have CREATE USER rights.
The generated script typically begins:
SET SCAN OFF;
CREATE USER <newuser> identified by <newuser> default
tablespace USERS temporary tablespace TEMP;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW to <newuser>
connect <newuser>/<newuser>;
It is not necessary to run the script as it is. You can remove
the lines that are responsible for creating and connecting as this
new user if you wish to create the database within an existing user's
database schema.
To run the script:
- In the SQL Worksheet, select the drop down list in the top
right hand corner to choose the connection on which to run the
script.
- Press F5 to run the script to run, or choose the "Run
script" icon from the SQL Worksheet toolbar
Examine the script output to ensure that the script ran correctly.
Note: Once the script has run, it is important
to examine the results of the output to ensure all statements ran
as expected. In some cases, you may need to make manual adjustments.
Note: This script builds the database objects.
The last step completes the process by copying the data.
5. Migrating
your Data to Oracle
The last step in the process is to migrate your data to your new
database. Migrating the data is a process that copies the data from
the third party database to your new tables in the Oracle database.
The process involves two steps:
- Creating a connection for your new database
schema
- Migrating the data to this new database schema
5.1 Creating a Connection
to your New Database Schema
The username and password of your new database is based on the
source database and appears at the top of the generation script
(e.g. "newuser" as outlined in step 4,
above). Use SQL Developer's create a new connection dialog to create
a new Oracle connection to your newly created database. (This process
is similar to the one you used in step 2.2,
above).
5.2 Migrating the Data
by Issuing the Data Move
Once you have your new connection created,
you are ready to migrate your data:
- From the main "Migration"
menu choose "Migrate Data".
This invokes a dialog prompting for the information required to
copy the data.
- The first field is labeled "Source
connection" - For this you select the third party
connection you configured in step
2.2 above.
- The second field is labeled "Target
Connection" - Select the newly created connection
configured in step 5.1
- The third field is labeled "Converted
Model" - Select the converted model created as a
result of step 2.
- Click OK.
The data move process will now begin. This will launch a number
of parallel connections to process the data in a timely fashion.
During this process a progress dialog will be displayed showing
current activity.
Once complete, your data should now be in
your newly created database schema. |
Bookmark
