Hi guys

This time I am going to share my experience with migrating a small MS SQL database to Oracle database using Oracle’s SQL Developer

Scenario

Microsoft SQL Server 2008 or later

Oracle database 11g (or later, 12c not tested)

Requirement

Business requires a 3rd party software that depends upon MS SQL Server to be migrated to Oracle platform

Pre-requisites

Oracle database 11g installed and instance is up and online

Oracle SQL Developer 4.2 (Used for this demonstration). Cannot confirm whether 4.1 uses the same approaches. Try it and let me also know

JDBC driver for MS SQL connectivity. For SQL Developer 4.x you need to download the driver from following link

http://sourceforge.net/projects/jtds/files/

Please follow https://kentgraziano.com/2013/01/14/tech-tip-connect-to-sql-server-using-oracle-sql-developer/ to learn how to install and configure the driver in order to establish a connection from SQL developer to MS SQL server.

Scope

I am going to migrate a database called “OPMS” from SQL Server 2008 R2 express edition to Oracle database 11g R2 64Bit

Please note, the JDBC driver fails to connect to the SQL Server using Windows Authentication, hence you must define a login with for your database, change the instance authentication methods to mixed in order to establish a successful connection.

As you could see with the above image, I don’t have any connections to Oracle database defined.

For the database migration, We must define two connection. One connection using SYSTEM user & the 2nd connection using MWREP user that we will create in like following

[code language=”sql” gutter=”false”]
DROP USER MWREP CASCADE
/
CREATE USER MWREP IDENTIFIED BY mwrep
DEFAULT TABLESPACE BAYAN
TEMPORARY TABLESPACE TEMP1
QUOTA UNLIMITED ON BAYAN
/
GRANT DBA,CONNECT,RESOURCE TO MWREP
[/code]

Once the migration is over, you may drop this repository safely. Hence don’t worry about the grants given to the new user. I failed to successfully get the things done properly without DBA role assigned to this migration schema

I’ve struggled hours to understand why the “tables” were not created as a part of the migration process which said “Successfully completed” after the execution and my probing through the log files pointed towards a schema OPMS not existing in indirect wordings.

(I’m pretty sure that I missed something & the intended schema was NOT created during the migration process run. Regardless, creating the Schema manually gives many tuning choices like tablespace selection, quota setting etc)

So, to get the migration work successfully, you need to create a schema with the the same name of your SQL Database. In my example the SQL database name is “OPMS”, the same I pre-defined with the Oracle database, although the script generated by the migration has DDL for creating the user against default tablespace “USERS”. Well, I didn’t want my OPMS schema using “USERS” tablespace…

I created the OPMS user as below

[code language=”sql” gutter=”false”]
DROP USER OPMS CASCADE
/
CREATE USER OPMS IDENTIFIED BY opms
DEFAULT TABLESPACE BAYAN
TEMPORARY TABLESPACE TEMP1
QUOTA UNLIMITED ON BAYAN
/
GRANT DBA,CONNECT,RESOURCE TO OPMS
[/code]

I am all set to start the migration now, so should be you!

Created a new connection for user “System”

Created another connection for “MWREP” user, which will hold the migration repository

Now we have to create the migration repository. Right click on the MWREP connection and expand Migration Repository, then Associate Migration repository

Progress

Finished

If the repository association fails for any reasons, you have restart by dropping the migration schema that you have created and go through the steps once again.

As I have completed creating the migration repository, next step is to connect the SQL developer to MS SQL Server (in my case SQL Server 2008 R2 Express edition)

So you have all the 3 connections required for the migration now.

As soon as you connect the MWREP, you will notice that Migration Windows showing an entry like seen with the below image

Now we will start the migration.

The welcome screen provides you an overview of the activities those will be completed for the SQL database migration. Move ahead

By default your migration repository will be selected, however cross check it and click “Next”

Provide a meaningful name for your Project and Select output directory

Make sure you have selected the correct Source database

The default database for the currently connected SQL database user will be selected by default for capturing. Confirm and click next

Under the convert step (6), make necessary changes. Refer the image for more details

A number of objects will be selected, unless you are pretty confident about objects you don’t want to migrate, leave the default selection intact.

Select the “System” connection for Target Database

Make sure you select SQL connection for source and MWREP connection as target in the move data step (9)

Click finish & the migration immediately starts. Depending upon the size of your source database it may take while for the process to create and move data between the technologies.

 

Progress

 

 

Create a connection to Oracle database for the newly created (We created the OPMS schema prior the migration) schema & verify whether the objects were created by the migration process.

That’s all folks!

rajesh