Migrate database from MS SQL Server to Oracle with SQL Developer

Migration of a database from MS SQL Server to Oracle is a complicated task, but Oracle provides SQL Developer for migrating from SQL Server to Oracle by some configuration. It makes your migration process easy or time-saving instead of doing it manually.

1. Set up SQL developer for connecting both the database Oracle and MS SQL Server.

Oracle SQL Developer configured for Microsoft SQL Server

2. For Migration, we need to create a repository user in Oracle that will track the migration activities.
We will do migration in different phases:
Repository
Capture
Convert
Translate
Data Move

Start from creating the Repository

In SQL Developer, Make a connection with SQL Server. SQL Server database name is used as username in Oracle as migration. If the SQL Server database has multiple schemas like dba, sales, and hr then it is all used as different usernames in Oracle like _.

1. Open the SQL Developer and connect with the Oracle as super user sys as sysdba.

2. Create a new user migration for the Repository.

create user migration identified by migration;

grant dba to migration;

3. Make a connection with migration in SQL developer.

4. Right-click on Migration connection in SQL Developer

Migration right click –> Migration repository –> Associate Migration Repository

5. It will start creating a repository and finish.

Capture and Convert Process for Migration

1. Connect the third-party database such as SQL server in this example with SQL Developer.

2. Right-click on the database of SQL Developer and select Migrate to Oracle database.

3. Migration window will be open as below:

4. Select the repository database on the Next Screen:

5. Make a project a migration and assign it an empty folder name for its files:

6. Select the Source database in Migration window:

7. Choose the migration database from the list:

8. Go to next for choose convert option and click on the summary to create a project.
Note: you can modify any datatype otherwise system will pick automatically.

9. After clicking on the summary, It will skip the rest steps and show the summary:

10. click on the finish button, It will start the process of capturing and converting into the repository.

11. On the right window of SQL Developer, you will find the project migration listed in the below window.

12. You can check the log on the left window by clicking on the Migration projects after the capture and convert process.

Note: We only worked in the repository up to this point, we did not find any schema in Oracle with the name of migration. After the translation process, we have a schema in Oracle Database.

Translate process

1. We are starting the translation process by clicking right-click on the project shown in the right window of SQL Developer.

2. Migration window will open from Introduction.

3. Click on next directly and move to the translate tab:
Note: Choose the package, procedure, and function that you need to translate.

4. Choose the target database and click on proceed to the summary for TRANSLATION phase execution only

5. Summary page, you can click finish to start the translation phase:

Note: After translation, you have schema and user in the Oracle database.

Move Data

Note: Before starting, we will provide permission to the users to write into the Oracle tablespace otherwise we will not be able to insert data into the Oracle USER.

ORA:01950: no Privileges on tablespace USERS

Error Snapshot:

In my case “REPORTSERVER” is a user, So, I am giving permission to REPORTSERVER from sysdba users. I am giving dba control to report server. We can revoke permission after migration and give limited permission that which user needs.

Grant dba to ReportServer;

1. Grant tablespace permission to the user as we have already done to avoid errors:

Grant dba to user;

2. Right-click on Migration project in SQL Developer, Choose move data:

3. The first introduction page is open, press next it directly go to the move data tab
Note: You have two options online or offline, I prefer online move in example:

4. The Summary page will appear, click on finish to proceed.

5. After finishing you can check the data in Oracle by connecting with the user.

For Checking Error

1. Right-click on the migration project in the SQL Developer in right side windows:

2. On clicking on the project, you will get the details on the left side about the capture issue, converting issue, and data quality. you can save them in Excel and check them.