5 Replies Latest reply: Aug 26, 2013 3:57 AM by 1189fb51-5238-4983-8a9f-268fdade5f4c RSS

    Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench

    1189fb51-5238-4983-8a9f-268fdade5f4c

      Hi,

       

      We face an issue while migrating an SQL Server 2005 DB to Oracle 11.2.  It fails during the process.  I hope someone on the forum has seen this before and can give us some advice.

       

      I use the latest version of SQL Developer with JRE included (3.2.20.09).  The JDBC driver to connect to SQL Server 2005 is 1.2.0

       

      Here are the steps we take in the Migration Workbench wizard:

       

      • I made an online extract of the SQL Server database.
      • Using the workbench in SQL Developer I created a migration repository in schema PDM_MIGRATION.
      • Next I start the migration, it captures the tables fine, but immediately after the start of the conversion I get a failed message without further explanations.

       

      This is the content of the error xml:

      <?xml version="1.0" encoding="windows-1252" standalone="no"?>

      <log>

      <record>

        <date>2013-08-14T16:23:32</date>

        <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>

        <level>SEVERE</level>

        <class>oracle.dbtools.migration.workbench.core.logging.MigrationLogUtil</class>

        <message>Ongeldig naampatroon.: PDM_MIGRATION .MIGR_FILTER</message>

        <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@4c12ab</param>

        <exception>

          <message>oracle.dbtools.migration.convert.ConvertException: Ongeldig naampatroon.: PDM_MIGRATION .MIGR_FILTER</message>

          <frame>

            <class>oracle.dbtools.migration.convert.ConverterWorker</class>

            <line>1078</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.convert.ConverterWorker</class>

            <line>316</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>1002</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>303</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>205</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>159</line>

          </frame>

          <frame>

            <class>oracle.dbtools.raptor.backgroundTask.RaptorTask</class>

            <line>193</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask$Sync</class>

            <line>303</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask</class>

            <line>138</line>

          </frame>

          <frame>

            <class>oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask</class>

            <line>515</line>

          </frame>

          <frame>

            <class>java.util.concurrent.Executors$RunnableAdapter</class>

            <line>441</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask$Sync</class>

            <line>303</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask</class>

            <line>138</line>

          </frame>

          <frame>

            <class>java.util.concurrent.ThreadPoolExecutor$Worker</class>

            <line>886</line>

          </frame>

          <frame>

            <class>java.util.concurrent.ThreadPoolExecutor$Worker</class>

            <line>908</line>

          </frame>

          <frame>

            <class>java.lang.Thread</class>

            <line>662</line>

          </frame>

        </exception>

       

      </record>

      <record>

        <date>2013-08-14T16:23:32</date>

        <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>

        <level>SEVERE</level>

        <class>oracle.dbtools.migration.workbench.core.logging.MigrationLogUtil</class>

        <message>Ongeldig naampatroon.: PDM_MIGRATION .MIGR_FILTER</message>

        <param>oracle.dbtools.migration.convert.ConverterWorker.copyModel(ConverterWorker.java:1078)</param>

        <param>oracle.dbtools.migration.convert.ConverterWorker.runConvert(ConverterWorker.java:316)</param>

        <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doConvert(FullMigrateTask.java:1002)</param>

        <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doMaskBasedActions(FullMigrateTask.java:303)</param>

        <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:205)</param>

        <param>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:159)</param>

        <param>oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)</param>

        <param>java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)</param>

        <param>java.util.concurrent.FutureTask.run(FutureTask.java:138)</param>

        <param>oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:515)</param>

        <param>java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)</param>

        <param>java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)</param>

        <param>java.util.concurrent.FutureTask.run(FutureTask.java:138)</param>

        <param>java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)</param>

        <param>java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)</param>

        <param>java.lang.Thread.run(Thread.java:662)</param>

        <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@5dc1bc</param>

        <exception>

          <message>oracle.dbtools.migration.convert.ConvertException: Ongeldig naampatroon.: PDM_MIGRATION .MIGR_FILTER</message>

          <frame>

            <class>oracle.dbtools.migration.convert.ConverterWorker</class>

            <line>1078</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.convert.ConverterWorker</class>

            <line>316</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>1002</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>303</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>205</line>

          </frame>

          <frame>

            <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

            <line>159</line>

          </frame>

          <frame>

            <class>oracle.dbtools.raptor.backgroundTask.RaptorTask</class>

            <line>193</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask$Sync</class>

            <line>303</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask</class>

            <line>138</line>

          </frame>

          <frame>

            <class>oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask</class>

            <line>515</line>

          </frame>

          <frame>

            <class>java.util.concurrent.Executors$RunnableAdapter</class>

            <line>441</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask$Sync</class>

            <line>303</line>

          </frame>

          <frame>

            <class>java.util.concurrent.FutureTask</class>

            <line>138</line>

          </frame>

          <frame>

            <class>java.util.concurrent.ThreadPoolExecutor$Worker</class>

            <line>886</line>

          </frame>

          <frame>

            <class>java.util.concurrent.ThreadPoolExecutor$Worker</class>

            <line>908</line>

          </frame>

          <frame>

            <class>java.lang.Thread</class>

            <line>662</line>

          </frame>

        </exception>

       

      </record>

      <record>

        <date>2013-08-14T16:23:32</date>

        <logger>oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle</logger>

        <level>WARNING</level>

        <class>oracle.dbtools.migration.workbench.core.ui.FullMigrateTask</class>

        <message>Building converted model: FAILED : Database Migration : FAILED</message>

        <param>oracle.dbtools.migration.workbench.core.logging.LogInfo@15a3779</param>

       

      </record>

       

      Does anybody know what this error means and what steps we should take to continue the migration?

      I see the PDM_MIGRATION.MIGR_FILTER is a type.

       

      Many thanks in advance,

       

      Kris

        • 1. Re: Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench
          wkobargs-Oracle

          Hi Kris,

           

          > Ongeldig naampatroon.: PDM_MIGRATION .MIGR_FILTER

           

          The question is why you are getting "invalid name pattern" for MIGR_FILTER in your repository. MIGR_FILTER is a Type in the repository, and it should look like:

           

          create or replace

          TYPE MIGR_FILTER IS OBJECT (

            FILTER_TYPE INTEGER, -- Filter Types are 0-> ALL, 1->NAMELIST, 2->WHERE CLAUSE, 3->OBJECTID LIST

            OBJTYPE VARCHAR2(40),

            OBJECTIDS OBJECTIDLIST,

            NAMES NAMELIST,

            WHERECLAUSE VARCHAR2(1000));

           

          Do you have this Type at all in your repository? Perhaps it is a privilege issue, have you given the user that creates the repository all the necessary privileges, as they are listed in the SQL Developer documentation in the chapter: "Creating a Database User for the Migration Repository" ?

           

          Regards

          Wolfgang

          • 2. Re: Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench
            1189fb51-5238-4983-8a9f-268fdade5f4c

            Hi Wolfgang,

             

            Thanks for your reply.

             

            This is how the type MIGR_FILTER looks like:

             

            create or replace

            TYPE MIGR_FILTER IS OBJECT (

              FILTER_TYPE INTEGER, -- Filter Types are 0-> ALL, 1->NAMELIST, 2->WHERE CLAUSE, 3->OBJECTID LIST

              OBJTYPE VARCHAR2(40),

              OBJECTIDS OBJECTIDLIST,

              NAMES NAMELIST,

              WHERECLAUSE VARCHAR2(1000));

             

            I think the repository user has the correct privileges.  This is the overview of privileges it has:


            SQL> select * from dba_sys_privs where GRANTEE in ('PDM_MIGRATION') order by GRANTEE;

             

            GRANTEE PRIVILEGE ADM

            ------------------------------ ---------------------------------------- ---

            PDM_MIGRATION ALTER SESSION NO

            PDM_MIGRATION CREATE CLUSTER NO

            PDM_MIGRATION CREATE DATABASE LINK NO

            PDM_MIGRATION CREATE PROCEDURE NO

            PDM_MIGRATION CREATE SEQUENCE NO

            PDM_MIGRATION CREATE SESSION NO

            PDM_MIGRATION CREATE SYNONYM NO

            PDM_MIGRATION CREATE TABLE NO

            PDM_MIGRATION CREATE TRIGGER NO

            PDM_MIGRATION CREATE VIEW NO

            PDM_MIGRATION UNLIMITED TABLESPACE NO

             

            SQL> select * from dba_role_privs where GRANTEE in ('PDM_MIGRATION') order by GRANTEE;

             

            GRANTEE GRANTED_ROLE ADM DEF

            ------------------------------ ------------------------------ --- ---

            PDM_MIGRATION CONNECT NO  YES

            PDM_MIGRATION RESOURCE NO  YES


            Best regards,


            Kris

            • 3. Re: Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench
              wkobargs-Oracle

              Hi Kris,

               

              I did some further research but I did not find a solution nor was I able to reproduce this. I am using the same SQL Developer version, but my SQL Server version is 2008 and the jtds version is 1.2.5. I don't know whether this has something to do with your issue.

               

              Your privileges are not identical to those that are listed in the SQL Developer documentation. But if I create a new repository user with the same privileges that your user has, I still do not get a problem.

               

              When searching in the internet for "invalid name pattern", I have found some discussions that mention that the CREATE PUBLIC SYNONYM privilege needs to be granted. Though, if I grant CREATE SYNONYM then it works also fine for me. If I have none of the both privileges then I cannot even create a repository.

               

              Sorry that I don't have more for you in the moment. Perhaps I'll find some time next week to investigate this further.

               

              Regards

              Wolfgang

              • 4. Re: Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench
                1189fb51-5238-4983-8a9f-268fdade5f4c

                Hi Wolfgang,

                 

                Thanks a lot for investigating the issue, this is really appreciated.  I am using jtds version 1.2.0 as it was recommended in the Oracle Documentation.  I will try with version 1.2.5.

                If necessary we call Oracle Support to have a look at the issue.

                 

                I keep you posted on the progress.

                 

                Thanks once more,

                Best regards,

                 

                Kris

                • 5. Re: Issue with migration DB from SQL Server 2005 to Oracle 11.2 using SQL Developer Migration workbench
                  1189fb51-5238-4983-8a9f-268fdade5f4c

                  Hi Wolfgang,

                   

                  We managed to solve the issue last week with the help of Oracle Support.

                  The issue was caused by SQL Developer 3.2.  Using the Early Adopter release 4 solved the issue.

                   

                  Now the migrations is going well.

                   

                  Thanks once more for looking into this issue.

                   

                  Best regards,

                   

                  Kris