4 Replies Latest reply: Jul 4, 2014 8:27 AM by ljames RSS

    Migrate SQLServer2008 DATA to Oracle11g

    achana

      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
          ljames

          Achana,

           

          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.

           

          Regards

          Leo

          • 2. Re: Migrate SQLServer2008 DATA to Oracle11g
            achana

            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 11.2.0.1.0 Productio on Thu Jul 3 14:20:29 2014

             

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

             

            ERROR:

            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
              achana

              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
                ljames

                Achana

                 

                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

                 

                Pre_load

                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.

                 

                Leo