This discussion is archived
3 Replies Latest reply: Dec 11, 2012 11:57 PM by wkobargs RSS

Data Migration from sql server Db to Oracle db

979147 Newbie
Currently Being Moderated
Hi
How to Migrate Sql server 2008 to Oracle 11g DB.

Example:

Sql server 2008 (window server)
Oracle 11g (Linux Server)
(Both server in india only) IP address : Windows server : 192.166.222.1, oracle server 192.166.222.2

I have a one table for sql server
(Table Name : useraddress, Field Name : userid, username, password, email, address1, address2, city, state, zip, country, phone1, phone2, createdon, updatedon, status)

New Oracle Migration table we are designed (country, state, city, user, userdetails) Master tables
Table Name:
country : countryid, countryname
State : Stateid, countryid, statename
city : cityid, countryid, stateid, cityname
User : userid, username, password, status, email, createdon, updatedon
UserDetails : userid, address1, address2, cityid, stateid, countryid, phone1, phone2, createdon, updatedon


I want to check each and every value in oracle Master tables if value is correct means the information insert into particular table else go to error table.
(Note: Already i stored value for country, state, city table. Previously sql server table no constraint check its a raw data.)

How to connect sql server and get the data and how to connect oracle server and check each and every value in equivalent table.

Please give me a solution and sample migration scripts.

thanks & regards,

M Manikandan
  • 1. Re: Data Migration from sql server Db to Oracle db
    wkobargs Journeyer
    Currently Being Moderated
    Hello,
    How to Migrate Sql server 2008 to Oracle 11g DB.
    This question is easy to answer. Use the SQL Developer Migration Workbench. Please start here: http://www.oracle.com/technetwork/database/migration/index-084442.html

    But your description of what you want to do (splitting one SQL Server table into 5 tables on Oracle side) cannot be achieved with SQL Developer. At least not without much manual work.

    Wouldn't it be easier to do the separation of the 1 into 5 tables completely on SQL Server side at first? Then the migration to Oracle using SQL Developer would be straight forward.
    How to connect sql server and get the data and how to connect oracle server and check each and every value in equivalent table.
    If you are ready with the migration of metadata and data then you can use a database link from Oracle to your SQL Server database and can execute all queries that you want to compare data on both sides, using SQL statements. For that you need a gateway. There are two possibilities: The Database Gateway for ODBC (DG4ODBC), that requires a suitable ODBC driver for SQL Server, but the license for DG4ODBC is included in your license for Oracle RDBMS. Or the Database Gateway for MS SQL Server (DG4MSQL), which does not require a separate ODBC driver, but which must be licensed separately. Functionality of DG4ODBC is a little bit restricted compared to DG4MSQL, e.g. with DG4ODBC you can't use two-phase commit. About gateways I suggest to start reading here: http://www.oracle.com/technetwork/database/gateways/index.html

    Regards
    Wolfgang
  • 2. Re: Data Migration from sql server Db to Oracle db
    979147 Newbie
    Currently Being Moderated
    Hi,

    thankyou for you replay.

    my doubt directly not migrate sql server to oracle (with my scenario).

    how to data move one oracle server to another oracle server. both server with same structure(1 server in india and another server in usa).
    Daily one time move the data from 1 oracle server to another oracle server.
    i am try to "Data Loader 4.1" tool but connect not open error. i entered username and pwd, IP address, sid, portnumber But not connect.

    Please help.

    Saran
  • 3. Re: Data Migration from sql server Db to Oracle db
    wkobargs Journeyer
    Currently Being Moderated
    Hi Saran,

    I suggest that you post your question about moving data from one Oracle server to another Oracle server in a new thread and choose one of the forums that you find here:

    https://forums.oracle.com/forums/category.jspa?categoryID=18

    We are experts for migrations of foreign databases to Oracle so you won't get much help in this forum about your new question.

    Regards
    Wolfgang

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points