- Download SQL Developer from: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. Unzip the file and open sql developer.
- First, Create user ”Migration” for migration repositery where sqldeveloper manages metadata for migration. Grant privileges to Migration user.
Create user Migration identified by migration
Default tablespace users
temporary tablespace temp;
grant all privileges to migration;
- Right click on connection tab to Create new connection:
- Now create a database connection repository for migration user. Enter migration user name and password. Set SID of your oracle database and test your connection. On successful status click on connect.
- To create the repository right click on Repository connection, and select Migration Repository menu then click Associate Migration Repository.
- To connect to third-party database(SQL Server) using SQL Developer we need jTDS driver. You can download it from here: http://sourceforge.net/projects/jtds/files/jtds/1.3.0/jtds-1.3.0-dist.zip/download
- Open SQL Developer select Tools and then preferences.
- Expand the database and select Third Party JDBC Drivers. And click on Add Entry.
- Select .jar file located in jTDS folder. click OK.
- Now create a connection for SQL Server database using SQL Developer.
- Give connection name as SQL_Server.
- Checked Use Windows Authentication check box.
- Through Retrieve database you can select sql server database which want to migrate.
- Test and Connect.
n case of Error: SSO Failed: Native SSPI library not loaded. Try this:
- Open the JTDS 1.3.0 folder.
- The ntlmauth.dll you will find inside the x86/SSO folder
- The JtdsXA.dll you will find inside the x86/XA folder
- Put the ntlmauth.ddl and JtdsXA.dll inside de Sql Developer/sqldeveloper/bin
- Copy the jtds-1.2.4.jar to Sql Developer/sqldeveloper/lib
- Copy ntlmauth.dll in java bin folder
Now create user in oracle db in which you want to migarate sql server database
- Create user test identified by test;
- Create database connection for test user.
Finally you can start migration.
- Open sql developer.
- Click Tools>Migration>Migrate
- Migration Wizard will appear on your screen. Click Next
- Select Migration_repository database connection.
- Give a name for migration project and select a directory to write output.
- Now select third party database connection SQL Server which you want to migrate. In case of online migration Migration wizard will perform all operations. If you select offline migration then Migration Wizard will generate all DDL scripts.
- Select SQL Server database which we want to migrate.
- Conversion window will appear. Which will convert source data types according to oracle model.
- Select SQL objects to translate.
- Choose targeted Database.
- Select the source and target database.
- Summery window will show you the migration detail. In case of any change go back and correct it.
- Click Finish.
- Migration process will start.
- On successful migration message your sql server database is migrated to oracle.
Copying tables to Oracle:
- Right click the table located in third party database and click Copy To Oracle
- Select target database connection and click Apply button. If you checked Include Data check box then it will create table structure and move data.
- Finaly, Copy to Oracle database is finished.