Migrating a SQL Server Database to Oracle 11g

Freshness Warning

This post is more than 4 years old. Please bear in mind its age when reading.

about 4 years ago Published in Database by Nick Kewney - Comments (3)

After attending a session with an Oracle consultant this week, I thought I'd share with you some tips for migrating an SQL server database to the Oracle platform.

Firstly, let's get one thing clear. I love SQL Server. I use it for 99% of projects I do. However, for a particular project I'm working on just now, it makes sense to migrate to Oracle for two reasons; rapid scalability and redunancy. Migration is simply put the conversion of an existing database into a new architecture by preserving data and functionality of the old architecture. A typical migration consists of the following steps:

Analysis
Migration
Testing
Deployment

SQLDeveloper is a wizard-driven java tool which will attempt to convert the following database elements for you:

Tables and Data
Primary Keys
Check Constraints
Foreign Keys
Indexes
Views
Groups / Users
Databases
Stored Procedures
Triggers
Grants
Rules
Defaults
User Defined Types

SQLDeveloper performs the following tasks to help you migrate your database to Oracle and is designed to execute tasks in the following order (I have outlined the exact steps at the bottom of this post).

Capture the source database structure into Source Model (online/offline)
Convert to Oracle Model
Create a representation of the structure of the destination database
Migrate the source database
Create the schema online or offline
Transfer the data online or offline

The steps below assume you have followed the steps to complete an OFA-complinant installation of Oracle 11g database on your server. I'll cover this process in another post soon.

Connect to SQLPlus using the following command: > sqlplus / as sysdba
Create a new user called "sqlmig" with a password of "sqlmig" as follows > create user sqlmig identified by sqlmig default tablespace users temprary tablespace temp
Give permissions required to the "sqlmig" user: > grant resource, create view, create session, create users, dba to sqlmig;
Launch SQLDeveloper 1.5
Create a new connection called "sqlmig" to the Oracle 11g database as sqlmig user
Configure the JDBC Driver for SQL server > Tools -> Preferences -> Database -> Third Party JDBC Drivers > Map to jtds-1.2.2.jar > Copy ntlmauth.dll from the jtds directory to e:\jdeveloper\sqldeveloper\jdk\jre\bin
Add a new connection to the SQL Server Database to your database > User: Windows Authentication and select your database
Right click the SQL connection and select 'Capture Microsoft SQL Server'
Right click the captured model and select 'Convert to Oracle Model'
Right click on the converted model and select 'Generate'
Run the script that is produced
Create a new connection to your new 'dbo_xxx' database
Choose Migration -> Migrate data Watch it happen :-)

Here are the slides from the day:

I'm running the 39th BMW Berlin Marathon on 30th September 2012 for the British Lung Foundation, who are currently funding research on the prevention of lung damage in COPD and many other areas related to lung disease.

I'm participating with my sister Claire Kewney and, on behalf of the charity, would appreciate even the smallest donation. My own JustGiving page is here, our team page is here.

if you're in the UK, you can also donate using your mobile phone by texting NKEW82 £5 (or any amount) to 70070. Your donation will be appreciated!

Comments

- Raju
- 10/19/2011 1:44:06 PM
Hi i tried to convert data from sql server to oracle, i managed to convert database scripts but i am unable to move data from sql server to oracle10g.can you please guide me with screen shots, i have gone through oracle website but i am unable to understand it can you please its urgent.