Migrate Database from Oracle to Microsoft SQL Server with SSMA Utility

Following are the steps performed to migrate the database from Oracle to SQL server:

1. Download the SSMA software for migration from Oracle to SQL Server

https://www.microsoft.com/en-us/download/details.aspx?id=54255

2. Install the SSMA software and find the SSMA icon on the Desktop by the simple next button.

3. Open the SSMA software and start a new project.
Go to File–>click New then Pop Window asks for the project name, location, and into which SQL server want to migrate including the SQL Azure option. (selected SQL Server 2014 for example)

4. Connect with Oracle database have three options:
1. Oracle Client Provider – Need to install Oracle Client
2. OLEDB Provider – Direct connectivity for the database if not using PDB of 12c
3. ODBC Driver – Create ODBC connection first
Note: you are on another server or PDB database of the 12c version. It’s better to install Oracle client for connectivity and create TNS entries in the Client.

In my example, My Server is in 12c and the Schema is in the PDB database. So I installed Oracle Client and configured PDB1 for direct connectivity. Make Sure your PDB is in the open state.

Created TNS entry for PDB database in tnsnames.ora file:
Go to Oracle Client location $ORACLE_CLIENT_HOME\network\admin

5. Connect with PDB1 database with the SSMA Module in Oracle Connectivity:

Note: it will take time after the message of connected, it will start loading the object from Oracle to make its own repository.

Error:
Connection to Oracle failed.
ORA-01033: ORACLE initialization or shutdown in progress

I am using the PDB1 database, but it is not in the open state. So, I connected with sysdba user and opened the pdb1 database
sqlplus sys@pdb1 as sysdba
Password
SQL> Alter database open;

6. After loading the Oracle Object, it will enable the connectivity button for the SQL Server.

7. Connect with the SQL Server database.
Note: Chosen TEST database as a name which is not present in SQL Server. So it will pop a window for creating a TEST database.

Error: Sql Server Agent is now working. You need to start the service of SQL Server Agent in services.msc, Then press the continue button.

Note: You can also modify reporserver schema in TEST Database to default dbo

8. Right-click on the schema in the Oracle tab which wants to migrate and choose to create report.

Note: The process of creating a report is started in the output window.

9. Warning occurred during the creation report and continued on it.

10. After continue, it start converting and creating report in html fromat which is present in location of project –> report –> report –M mainindex.html

11. Report open in Internet Explorer which will give you details of objects and an estimate of manual conversion time.

12. Start the convert process by right click on the schema of Oracle and choosing the convert button using the SSMA tool.

13. On the Right tab SQL SERVER Metadata Explorer, Right click to sync the SQL server database with the repository
It will create the tables, procedures, and functions in the SQL server database when you choose to sync the database.

14. Synchronization is completed for structure

15. Start the migration data process
Note: Both Oracle and SQL Server database is selected in the migration process

Note: Will ask for the password again for data migration.

16. Data migration report is finished.
Error 58 tables failed to migrate

Save the report for future correction.

17. Verify the SQL server by connecting the TEST database.