Migrate Database from Oracle to Microsoft SQL Server with SSMA Utility
Following are the steps performed from migrate database from oracle to sql server:
1. Download the SSMA software for migration from Oracle to SQL Server
2. Install the SSMA software and found SSMA icon on Desktop by simple next next button.
3. Open the SSMA software and start a new project.
Go to File–>click New then Pop Window ask for project name, location and into which sql server want to migrate included 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 database if not using PDB of 12c
3. ODBC Driver – Create ODBC connection first
Note: you are on another server or pdb database of 12c version.Its better to install Oracle client for connectivity and create TNS entries in Client.
In my example, My Server is in 12c and Schema is in PDB database. So i installed Oracle Client and configured PDB1 for direct connectivity. Make Sure your pdb is in 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 SSMA Module in Oracle Connectivity:
Note: it will take time after message of connected, it will start load the object from Oracle to make its own repository.
Connection to Oracle failed.
ORA-01033: ORACLE initialization or shutdown in progress
I am using PDB1 database , it is not in open state. So, I connected with sysdba user and open the pdb1 database
sqlplus sus@pdb1 as sysdba
SQL> Alter database open;
6. After loading the Oracle Object, it will enable the connectivity button for SQL Server.
7. Connect with the SQL Server database.
Note: Choosed TEST database as name which is not present in SQL Server. So it will pop window for creating TEST database.
Error: Sql Server Agent is now working. You need to start the service of SQL Server Agent in services.msc, Then press continue button.
Note: You can also modify reporserver schema in TEST Database to default dbo
8. Right click on the schema in Oracle tab which want to migrate and choose create report.
Note: Process of creating report is started in output window.
9. Warning occured during the create report and continue 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 detail of objects and estimate of manual convertion time.
12. Start the convert process by right click on schema of Oracle and choose the convert button using the SSMA tool.
13. On Right tab SQL SERVER Metadata Explorer, Right click for sync the sql server database with repository
It will create the tables, procedure, function into the sql server database when you choose to sync the database.
14. Syncronization is completed for sturcture
15. Start the migration data process
Note: Both oracle and SQL Server database is selected in migration process
Note: Will ask for password again for data migration.
16. Datamigration report is finished.
Error 58 table failed to migrated
Save the report for future correction.
17. Verify the sql server by connecting the TEST database.