--Oracle SQL Developer configured for Microsoft SQL Server--
Download the SQL Developer from Oracle Web Site.
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Download the plugin for Configured the Microsoft SQL Server into the SQL Developer.
http://www.oracle.com/technetwork/database/migration/jdbc-migration-1923524.html
Direct link: https://sourceforge.net/projects/jtds/files/jtds/1.2
Download : jtds-1.2-dist.zip
Unzip the SQL developer folder and open the sqldeveloper.exe file on windows platform:
Now you will see only option for Oracle and Access database for connectivity.
GO to Tool –> Preferences, A new window will open.
Expand the database —> go to third party JDBC Driver –> Add the downloaded driver
Note: Extract the download JTDS software and add file upto jar file in below entry.
Now you open again the connection add button on right side. It will show you the connection for sql
Server and Sysbase database.
--------------------------------------------------------------------------------------------------
--Migrate database from MS SQL Server to Oracle--
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
1. Start from creating the Repository
. 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_repo identified by migration_repo;
grant dba to migration_repo;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW, CREATE MATERIALIZED VIEW TO
migration_repo;
3. Make 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 repository and finish.
Capture and Convert Process for Migration
1. Connect the MS SQL server 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 Next Screen: migration_repo
5. Make a project as migration and assign it a empty folder name for its files: migration_repo
6. Select the Source database in Migration window: MSSQL Database
7. Choose the migration database from the list:
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.
9. After click on summary, It will skip rest steps and show the summary:
10. click on finish button, It will start process for capturing anc converting into repository.
11. On right window of SQL Developer, you will find the project migration listed in below window.
12. You can check the log on left window by click on the Migration projects after capture and ocnvert
process.
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.
select usernamefrom dba_userswhere created > sysdate-2;
--Translate process--
1. We are starting translate process by clicking right click on project shown in right window of SQL
Developer.
2. Migration window will open from Introduction.
3. Click on next directly move to translate tab:
Note: Choose package, procedure and function which you need to translate.
4. Choose target database and click on proceed to summary for TRANSLATION phase execution only
5. Summary page , you can click finish to start the transalation phase:
Note: After translation, you have schema and user in the oracle database.
select usernamefrom dba_userswhere created > sysdate-2;
--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
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:
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:
4. Summary page will appear, click on finish for proceed.
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:
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.
