4 Replies Latest reply on Jul 4, 2014 1:27 PM by ljames

    Migrate SQLServer2008 DATA to Oracle11g


      This is the first time and I followed the Oracle instructions here:

      Migrating a Microsoft SQL Server Database to Oracle Database 11g

      The migration seemed to be successful even though it was a 2005.ocp and far as I can see all database objects created


      When migrating the DATA, SQLDeveloper created scripts for dos2unix oracle_ctl.sh

      I am running Oracle11gR2 in Windows7 Prof sp1


      Can SQLDeveloper create scripts for migrating the DATA to Oracle11g on a Windows7 platform?


      If not, I can try using SSIS to extract from SQLServer and load into Oracle, but if I do that, what happens to the referential integrity and constraints, esp the FK constraints if not inserted in sequence?

        • 1. Re: Migrate SQLServer2008 DATA to Oracle11g



          Sql-Developer does create data load scripts which includes extracting from MS SQL. Also it loads into Oracle.  It uses two methods


          Sql Loader

          External tables


          All the constraints will be disabled before the load and enabled after the load.  If you used offline migration methods please check the data move folder for batch file and shell scripts.

          It goes few level down to get to the actual script.


          Let me know if you can find any issues loading the data.  My email can be found in my profile.




          • 2. Re: Migrate SQLServer2008 DATA to Oracle11g

            Hi Leo,


            Thanks for the reply. I encounter yet again the issue with username/password when the oracle_ctl.bat batch file tried to execute the instructions and pre- and post-SQL scripts. I am thinking I have to go in and modify the .bat scripts myself...


            I have seemingly successfully created the Northwind database in Oracle and the next step is to migrate the data for which SQLDeveloper has set up


            the batch file and a shell script in the directory mssqlservermigration\Data.



            The file names are oracle_ctl.bat, oracle_ctl.sh, pre_load.sql and post_load.sql



            But when I ran oracle_ctl.bat, it throws following error messages ( and because of it, many more error messages followed after that, locking some


            unkown account)



            C:> mssqlservermigration\Data> oracle_ctl.bat



            C:> mssqlservermigration\Data> sqlplus dbo_Northwind/dbo_Northwind@orcl0<pre_load.sql


            SQL*Plus: Release Productio on Thu Jul 3 14:20:29 2014


            Copyright (c) 1982, 2010, Oracle. All rights reserved.



            ORA-01017: invalid username/password; logon denied



            Do we have to modify the .BAT created by SQL Developer ourselves?

            Also this waring: account locked! I logged onto SQLplus and confirm that no accounts have been locked, all free.

            • 3. Re: Migrate SQLServer2008 DATA to Oracle11g

              OK, it's this line in the batch file:

              sqlplus dbo_Northwind/dbo_Northwind@orcl < pre_load.sql


              Do the same thing like last time, log onto SQLPlus and unlock account, set indentity to dbo_Northwind and the batch file executes without complains.


              Data are loaded, now we must do QA tests.


              I DO NOT feel in control of the whole process, I'll do this again with another SQLServer database...

              • 4. Re: Migrate SQLServer2008 DATA to Oracle11g



                Looks like you already figured it out the loading process.  Looks like you ran the pre_load.sql which disables the constraints.


                oracle_ctl [user] [password@dbname]  <= This is the top level of the script calls



                Data load

                post_load  scripts.


                If you go inside you can find individual data load ctl and external tables scripts.  You can try that way to load single load have control rather than kicking the top level of the script and wait till it finishes the whole flow.  Let me know if this helps.