Migrating a Microsoft SQL Server Database to Oracle Database 11g

1. Online Capture: This requires creating a connection in SQL Developer to a live Microsoft SQL Server database. Using JDBC, the Microsoft SQL Server database metadata can be accessed and the Capture Model created.
2. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Microsoft SQL Server 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 Microsoft SQL Server Northwind sample database has been extracted into offline data files. The SQLServer2005.ocp file generated by the Capture tool contains the database schema information for the Microsoft SQL Server Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft SQL Server database. This structure is called the Captured Model.

1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data.
   
2. Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer</font> 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 mssqlservermigration.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.

Enter system_orcl in the Connection Name field (or any other name that identifies your connection), system for the Username field, and <your password> for the Password field. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

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. To connect, click Connect. Close the window.

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

Expand the system_orcl connection.

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;

Run the script , using the "Run Script (F5)" icon.

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.

Enter mwrep_orcl in the Connection Name field (or any other name that identifies your connection), mwrep for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

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.

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 the sqlserver2005.ocp file and click Open.

Click Next.

In step 5, you need to select the database for definition capture. Select the Northwind database and click Next.

In Step 6, you can specify the conversion options. You can add a new rule. Click Next.

Step 7 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, and click Next.

Step 8 provides details about the Data Move. Note down the location of the Data Move Script Directory, and click Next.

Step 9 provides you the summary of the entire Project. Note down the details and you can always click Back to make modifications. Finally, click Finish.

The Data Migration happens successfully.

Once you receive the message box, click Ok.

The SQL from the script is shown. Select system_orcl from the drop-down on the right. icon.

Click Run Script (F5).

The results are display from the script execution.

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

Enter dbo_northwind_migrated_orcl in the Connection Name field (or any other name that identifies your connection), dbo_Northwind for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

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 the dbo_Northwind_migrated_orcl connection.

Expand Tables.

The database tables that were converted to Oracle are listed. Select the EMPLOYEES table.

Select the 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.

Open a terminal and execute the following commands:

<prompt> cd <location where files are>

<prompt> dos2unix oracle_ctl.sh

<prompt> sh oracle_ctl.sh <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 Refresh the connection.

The data for the table has been loaded successfully.