Tag Archives: Migration

Migrate Database from Oracle to MS SQL Server with SSMA

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

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

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

SSMAicon1.JPG

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)

SSMAnewproject.JPG

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

tnsping.JPG

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

PDBconnectivity.JPG

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

LoadOracleObjects.JPG

Error:
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
Password
SQL> Alter database open;

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

ConnectwithSQLServer

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.

SqlServerconnection.JPG

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

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

Modifytodba.JPG

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

CreateReport.JPG
Note: Process of creating report is started in output window.

9. Warning occured during the create report and continue on it.

Prerequestnotmet.JPG

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

creatingreport

11. Report open in internet explorer which will give you detail of objects and estimate of manual convertion time.

Reporthtml.JPG

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

Conversionfinish.JPG

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.

synchronisedatabase.JPG

14. Syncronization is completed for sturcture

syncforstructure.JPG

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

Startmigratedata.JPG
Note: Will ask for password again for data migration.

16. Datamigration report is finished.
Error 58 table failed to migrated

Datamigrationreport.JPG
Save the report for future correction.

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

Advertisements

Migrate database from MS SQL Server to Oracle

Migrate database from MS SQL Server to Oracle with SQL Developer

Migration of database from MS SQL Server to Oracle is complecated task, but Oracle provide SQL Developer for migrate from SQL Server to Oracle by some configuration. It make you migration process easy or time saving instead of doing manual.

1. Setup SQL developer for connecting both the database Oracle and MSSQL Server.

https://smarttechways.com/2017/07/03/sql-developer-configured-for-microsoft-sql-server/

2. For Migration, we need to create a repository user in Oracle which 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 Sql Server database has multiple schema like dba, sales, hr then it all used as different username in Oracle like _.

1. Open the SQL Developer, Connect with the Oracle as super user sys as sysdba.

2. Create a new user migration for Repository.

create user migration identified by migration;

grant dba to migration;

1Capture.JPG

3. Make connection with migration in SQL developer.

2.1Capture.JPG

4. Right click on Migration connection in SQL Developer

Migration righ click –> Migration repository –> Associate Migration Repository

2Capture.JPG

5. It will start creating repository and finish.

4Capture.JPG

Capture and Convert Process for Migration

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

5.1Capture.JPG

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

9Migratetooracle.JPG

3. Migration window will be open as below:

10migration.JPG

4. Select the repository database on Next Screen:

11Migration.JPG

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

Migrationproject3

6. Select the Source database in Migration window:

13Source.JPG

7. Choose the migration database from the list:

Migrationchoosedatabase.JPG

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

MigartionConvert6.JPG

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

Migrationconvertfirststep.JPG

10. click on finish button, It will start process for capturing anc converting into repository.

24.JPG

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

MigrationCheckissueaftercatpure.JPG

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

MigrationCheckissueaftercapture2.JPG

Note: We only worked in repository upto this point, we did not find any schema in Oracle with name of migration. After translate process we have schema in Oracle Database.
Checkuserisnotcreated8.JPG

Translate process

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

Migartiontranslate9

2. Migration window will open from Introduction.

Migrationtransalate1.JPG

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

Migrationtranslate2.JPG

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

Migrationtranslate3.JPG

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

TranslateSummary4.JPG

Note: After translation, you have schema and user in the oracle database.
Checkuserinoracleaftertranslationphase.JPG

Move Data

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

ORA:01950: no Privileges on tablespace USERS

Error Snapshot:
Movedataerror

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

Grant dba to ReportServer;

1. Grant tablespace permission to user as we already done to avoid error:

Grant dba to user;

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

MigrationMovedata1.JPG

3. First introduction page is open, press next it direcly go to move data tab
Note: You have two option online or offline, I preferred online move in example:

Migrationmovedata3.JPG

4. Summary page will appear, click on finish for proceed.

Migrationmovedate3.JPG

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

For Checking Error

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

MigrationCheckissueaftercatpure

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

Transalationissue10.JPG