2 Replies Latest reply on Apr 13, 2012 1:51 PM by orcldeveloper-Oracle

    Need help in migrating MSSQL 2008 database to Oracle 11g

      Hi everyone,

      So, as stated above I want to migrate our apps database from mssql to oracle as it is what the client wants..
      I have absolutely no experience working with Oracle.
      My computer is running windows 7 32 bit, our DB people installed a 9i client for me to connect to the server and the my SQL Developer just came straight from the site. (So it's the latest release)

      I have one "DBA" access to the database I'm trying to migrate to.
      I've downloaded the Oracle SQL Developer and have been attempting to "migrate" our database using the migration tool for 3 days now, but to no avail.

      So the error I'm encountering is that the migration just stops, and a popup with the message "Migration actions have failed. Check the migration reports for details. : The connection is closed." comes up.
      I have no idea where the migration report can be found. As I cannot find it in the reports tab available.

      I'm hoping someone here can have the patience and knowledge to get me through this?
        • 1. Re: Need help in migrating MSSQL 2008 database to Oracle 11g
          You haven't said how you are trying to do the migration but I would guess you are trying to do it online.
          If you have anything but a very small SQL*Server database then the best option is to do the migration offline, at least of the actual tables and data.
          I suggest the following -

          1. Start here - Oracle Database Migration Technology

          2. Then click on 'Microsoft SQL Server to Oracle' and go here -

          3. Have a look at the demonstrations -
          Offline Migration (No SQL Server connection required)
          Offline Data Move (No SQL Server connection required)

          4. Review the tutorial -
          Migrating a Microsoft SQL Server Database to Oracle Database 11g

          By making an offline migration you have more control over what is happening and it is easier to see and resolve any errors you may get.

          • 2. Re: Need help in migrating MSSQL 2008 database to Oracle 11g
            The migration report is generally created under the "Documents and Settings" folder under application data/sqldeveloper. Key thing is at what stage the failure occurs?. Does it happen immediately after you start the data migration or after a while?.

            If the data migration process fails immediately after the start then there might be an issue with permissions on the source/target databases. Probably it is having trouble with disabling constraints on the target database. Because rememeber SQL Developer will disable constraints before it moves data across. My preference is to give DBA privilege to the Oracle user to avoid just cases or you can disable the constraints yourself by using scripts. Check the source/target database date/time format masks. You can set them up in SQL Developer.

            If the data migration process fails after a while and you notice that data for some tables is migrated then you may be running into network timeouts or space issues on Oracle etc.