1 Reply Latest reply on Feb 5, 2013 7:34 AM by Kgronau-Oracle

    MS sqlserver to Oracle migration

      we are working on sql server to oracle 11g migration.(i will explain what we did and questions follows)

      step1: captured .ocp file from sql server database and with help of sql developer 3.2 we generated captured scripts.

      some scripts came up with modified Object names(example table)(if the sql server length is more than 30 chars)
      we modified tables name according to what we want rather than keeping the sql developer given name

      step 2 : we are capturing the datamove with the same .ocp file


      1)what is the best method to compare sql server objects with the moved oracle objects to make sure the data model is right in place.

      2)as we modified some of the object names in oracle ,what is method to generate datamove scripts with this renamed objects
      is there any option in sqldeveloper to do so
        • 1. Re: MS sqlserver to Oracle migration
          Let me start with your question #2.

          In my SQl Server database I created a table 8x8 and I've started the migration. The table is renamed in Oracle to A8x8.
          When I now create the offline data migration scripts in SQL Developer it is aware of the object name change and creates the offline files accordingly.
          Here the script which is used to export the SQl Server data:
          bcp "gateway.dbo.8x8" out "data\dbo_gateway.A8x8.dat" -q -c -CRAW -t "<EOFD>" -r "<EORD>" -U%2 -P%3 -S%1

          You see it creates an export of the 8x8 table and puts the content into the A8x8.dat file.
          The Oracle SQL*Loader control file then uses this dat file and puts the content into the A8x8 table:
          infile 'data/dbo_gateway.A8x8.dat'
          "str '<EORD>'"
          into table dbo_gateway.A8x8

          So in general when you rename the objects using SQL Developer at the Convert stage where you can change the data type mapping or rename objects, then SQl Dev takes care of it and creates the offline files accordingly.

          Regarding #1 there's no generic tool and as usual it depends on a lot of factors. Some customers just check out the log and then just switch the application to now use the Oracle database, others perform more tests like checking the amount of data in the source and target, check of mandatory constraints etc. So here it really depends on your effort you're going to spend.