Migrating a Sybase Database to Oracle Database 11g

1. Online Capture: This requires creating a connection in SQL Developer to a live Sybase database. Using JDBC, the Sybase database metadata can be accessed and the Capture Model created.
2. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Sybase database metadata to files. These files can then be "captured" by SQL Developer to create the Captured Model.
   The second method is what you will perform in this tutorial.

Using SQL Developers Offline Capture feature, the pubs2 sample database has been extracted into offline data files. The Sybase15.ocp file generated by the Capture tool contains the database schema information for the Sybase pubs2 database. Oracle SQL Developer uses this file as the basis for building a representation of the structure of the source Sybase database. This structure is called the Captured Model.

1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Sybase source database and migrate the data.
   
2. Offline Data Move: You can export the data from Sybase. SQL Developer will create a series of BCP and sqlldr files that you can run from a batch file.
   The second method is what you will perform in this tutorial.
   

Install the Oracle Database 10g or later, or Oracle Database XE

Download and unzip Oracle SQL Developer here.

Download and unzip the files.zip file into your working directory (i.e.wkdir)

If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer session. If you do not have the icon located on your desktop, find the executable file (either named "sqldeveloper.exe" on Windows
or "sqldeveloper.sh" on Linux), and run it.

Select View > Connections.

In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: system_orcl
User Name: system
Password: <your_password> (Select Save Password)
Hostname: localhost
SID: <your_own_SID>

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Save. Then click Connect. Close the window.

The connection is saved and you can see it listed under Connections in the Connections navigator.

Expand system_orcl.

Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

Enter the following code in the SQL Worksheet to create a user for the migration repository

CREATE USER MWREP
IDENTIFIED BY mwrep
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

Click Run Script .

The mwrep user was created successfully.

Before you create the repository, you need to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Note: If this tab is not visible, select View > Connections.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: mwrep_orcl
User Name: mwrep
Password: <your_password> (Select Save Password)
Hostname: localhost
SID: <your_own_SID>

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Save. Then click Connect. Close the window.

The connection is saved and you can see it listed under Connections in the Connections tab.

Right-click the mwrep_orcl connection and select Migration Repository > Associate Migration Repository.

A progress window appears. Click OK.

Select Tools > Migration > Migrate.

The migration wizard appears and it will walk you through the steps to convert third party databases to Oracle.

In step 2, you need to select a connection for the migration repository. Select the mwrep_orcl connection, and click Next.

In step 3, you need to provide the Project details. A Project is a container for the migration entities. Provide a Name to the project, and specify the Output Directory. Click Next.

In step 4, you need to provide the Source Database details. Select the mode as Offline. Click Choose to select the Offline Capture Source file.

Select sybase15.ocp file from the files downloaded onto your working directory as part of the prerequisites and click Open.

Click Next.

In step 5, you need to select the database for definition capture. Select the pubs2 database and check Proceed to Summary Page. Click Next.

Click Finish.

The Database Migration actions are completed successfully. A message dialog box appears. Click OK .

Select Tools > Preferences.

Expand Migration and select Identifier Options. Make sure "Is Quoted Identifier On" is not selected. This is because the Sybase pubs2 database recognizes double quotes as String literals. If this is set incorrectly it can cause the conversion failure of procedures, triggers and views. Click OK.

In the Migration Projects tab, select and right-click the captured database. Click Convert.

In the Migration Wizard, click Next

You can specify the conversion options. You can add a new rule. Check Proceed to Summary Page. Click Next.

Click Finish.

The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

In Migration Projects tab, expand Converted Database Objects. Expand pubs2 > dbo_pubs2 > Procedures. Here you will find a procedure "expectedToFail" that has a red "x" icon against it. This is a failed procedure.

Select dbo_pubs2 tab. Select Conversion Issues. This will display a report of "Translation limitation" and "Errors".

Select the Converted Database Objects in the Migration Projects and expand dbo_pubs2.

Expand Procedures and select expectedToFail.

Copy the contents of the expectedToFail procedure.

Select Tools > Migration > Translation Scratch Editor.

Paste the copied text from the expectedToFail procedure in the left side text box.

Click the drop-down list and select Sybase T-SQL To PL/SQL.

Also, select the project model from the drop-down list.

Click Translate .

You get an error as expected. Click OK

Commenting out statements until the procedure translates will help you identify the problematic statement. In this case there is a new line between the bang and equals -"where lower(title) != @title". A quick solution would be to remove the space, but a more generic approach for all issues would be to comment out the clause with a TODO comment. Then manually replace it once in Oracle. Now, fix the bug and click Translate again. You can notice that the code has been successfully translated into PL/SQL on the right side textbox.

Select the expectedToFail procedure. Paste the copied content over the existing expectedToFail procedure.

Click Save .

Right-click expectedToFail procedure in the left-side navigation bar under Procedures. Select Reconvert.

Click OK on the dialog box.

Note: The procedure "expectedToFail" in the Converted Database Objects will be shown as erroneous even after conversion. This is a known issue in Oracle SQL Developer 3.0

In the Migration Projects tab, right-click Converted Database Objects. Select Generate Target.

In the Migration Wizard, click Next

This step provides you the Target Database details. Select the mode as Offline. You can select to drop the target objects here. Note down the Generated Script Directory location. Check Proceed to Summary Page. Click Next.

Expand Generate Script. Click Finish.

The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Select the Project tab. The SQL from the script appears. Select system_orcl from the drop-down list on the right.

Click Run Script .

Now that your scripts have run successfully, you can create a connection for the dbo_pubs2 user. Right-click Connection and select New Connection.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: dbo_pubs2_orcl
User Name: dbo_pubs2
Password: <your_password> (Select Save Password)
Hostname: localhost
SID: <your_own_SID>

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

Expand dbo_pubs2_orcl.

Expand Tables.

The database tables that were converted to Oracle are listed. Select AUTHORS.

Select Data tab. Notice that currently there is no data in the table. You will migrate the data later in this OBE.

Select Tool > Preferences.

Expand Migration and select Data Move Options.

Make sure the Date Mask format matches the following

Mon dd yyyy HH:mi:ssAM

Make sure the Timestamp Mask matches the following.

Mon dd yyyy HH:mi:ss:ff3AM

Click OK.

In the Migration Projects tab, right-click Converted Database Objects. Select Move Data.

In the Migration Wizard, click Next

This step provides details about the Data Move . Select Offline. Note down the location of Data Move Script Directory. Select Offline. Click Next.

Expand Move Data. Click Finish.

The Database Migration actions are completed successfully. A message dialog box appears. Click OK.

Open a terminal and execute the following commands:

<prompt> cd <location of the unzipped files\files\DataMove\sybase_obe\2011-02-01_09-25-25>

<prompt> oracle_ctl.bat <username> <password>@<connection>

Note: oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.

The command executes successfully.

Switch to Oracle SQL Developer and click Refresh .

.

The data for the table has been loaded successfully.

In the Migration Projects tab, right-click the project. Select Generate Estimation Plan.

Select the location where you would like to save the report. Click Create.

The Excel sheet is displayed. It gives a summary of the various objects within the database. To have a detailed view of the objects, click individual object type sheets within the Excel sheet.