This chapter contains the following migration examples:
Figure 5-1 illustrates how an application that is coded to query a Sybase database may use SQL Translation Framework to query information stored in Oracle Database instead.
This section contains the following information:
The Sybase database used in this example has three tables and five procedures and includes the following features:
INSERT statements into tables with IDENTITY columns
VARCHAR columns with size greater than 4000 characters
Multiple implicit result sets returned from procedures
A Java application connects to this Sybase database using JDBC.
Perform the following steps to set up migration:
Download the JDBC driver JTDS 1.2.
Add JTDS as a third-party JDBC driver as follows:
Select Preferences from the Tools menu.
Select Third Party JDBC Driver from the Database option on the right panel, as shown in Figure 5-2.
Click Add Entry.
The Select Path Entry box is displayed.
jtds-1.2.jar file and click Select.
Connect to the Oracle Database where you want to migrate the information.
Verify that the connection is using Oracle Database 12c JDBC drivers, with the following command:
Create a new user
migrep in Oracle database, for the migration repository, with the following command:
GRANT CONNECT,RESOURCE,CREATE VIEW to migrep INDENTIFIED BY migrep; ALTER USER migrep QUOTA UNLIMITED to users;
Connect to the database as the
migrep user and associate the migration repository with the user, as shown in Figure 5-3.
Create a connection to the Sybase database, in this example,
simpledemo12c, as shown in Figure 5-4.
Note:The migration process has four phases - Capture, Convert, Generate, and Data Move. It is best practice to complete each phase of the migration process, review any issues on the Summary page, and then continue to the next phase. The Migration Wizard enables you to complete each step in turn and then return back to the wizard to complete further steps. To do this, after completing each phase, select the Proceed to Summary Page check box and click Next.
Perform the following steps to capture migration:
Right-click on the
simpledemo12c Sybase database and select the Migrate to Oracle option, as shown in Figure 5-5.
This opens the Migration Wizard, as shown in Figure 5-6.
Choose the Migration Repository, as shown in Figure 5-7.
Enter a project name and specify an output directory to place files, as shown in Figure 5-8.
Select the database connection and the mode, as shown in Figure 5-9.
Select the database, in this case,
simpledemo12c, by moving it from Available Databases to Selected Databases, as shown in Figure 5-10.
Click Proceed to Summary Page to review the Capture phase before moving to the next phase of the migration process.
The capture phase saves a snapshot of the selected database at this point of time. Only the object definitions are captured, not the actual table data. This captured snapshot can be viewed in the Migration Projects navigator.
Note that the snapshot is not a connection to the database, and it only enables you to browse through the information saved in the Migration Repository.
Before starting the conversion phase, you must set the migration preferences. Perform the following steps to achieve this:
From the Tools menu, select Preferences, then Migration, and then Translators. Select the Generate Compound Triggers option, as shown in Figure 5-11.
From the Tools menu, select Preferences, then Migration, and then Generation Options. Select the Use all Oracle Database 12c features in Migration option, as shown in Figure 5-12.
Perform the following steps to start convert phase of the migration process:
Right-click the Capture Model node and choose Convert, as shown in Figure 5-13.
The Migration Wizard is opened at the Convert phase, as shown in Figure 5-14.
Select Proceed to Summary Page and click Next.
During the convert phase, object names are resolved to valid Oracle names. Data types are converted to Oracle Database types and T-SQL defined objects like stored procedures, views, and so on are converted to Oracle PL/SQL. A converted model is created that can be browsed in the Migration Projects navigator. The converted procedures can be reviewed in the converted model.
Note that the converted model is not an actual Oracle database, but a prototype of an Oracle Database. The information is still stored only in the Migration Repository tables.
The migration generation phase creates the objects in the target Oracle Database. A script is created and it is run against a selected Oracle connection in the following two ways:
offline mode, the script is opened in a SQL Worksheet and you have to select the connection and run it manually.
online mode, you must provide the target connection in the wizard and the wizard runs the script automatically.
The following steps demonstrate how to perform the generate phase of the migration process in
Right-click on Converted Database Objects in the Migration Projects panel and select Generate Target.
offline as the database mode in the Migration Wizard, as shown in Figure 5-15.
Choose a connection in the target Oracle Database, as shown in Figure 5-16.
The database objects are not created under the connection selected in this step. However, this connection must have enough privileges to create other users and objects.
Perform the following steps to move the data to Oracle Database:
Right-click the Converted Database Objects node and select Move Data, as shown in Figure 5-18.
online as the data move mode in the Move Data screen.
You can select
offline as the data move mode if the migration process involves large amount of data.
Click Next. The Summary screen appears.
You can browse the database objects to verify the data is moved to Oracle database.
See Oracle SQL Developer User's Guide for details.
Oracle SQL Developer provides a number of reports on the migration process to help identify tasks and issues to resolve. Click or double-click on the migrated project in the Migration Projects navigator. A report will appear on the right panel with a number of tabs and children reports, as shown in Figure 5-19.
The Analysis report provides information about the size of the migrated database like the number of objects, line sizes, and so on, as shown in Figure 5-20.
The Target Status report provides information about the status of the migrated objects in the Target database. First, select a target connection with enough privileges to view the status of other schema objects and then select refresh. Objects that are present in the converted model, but are missing from the target Oracle Database, are listed as missing. These objects can be either valid or invalid.
The Data Quality tab provides information about the number of rows in the target Oracle Database compared with the source database. Perform the following steps to compare the databases:
Select a converted model, a source connection, and a target connection.
This performs a
count(*) function on each table in the source and the target database. So, it is advisable not to perform this operation on production data.