7 Replies Latest reply: Jul 8, 2014 8:21 AM by Mkirtley-Oracle RSS

    Migrate tables and data from ODBC datasource to Oracle 11.2

    Kim Berg Hansen

      Hiya

       

      I have a database file in Dynamics NAV 5.0 native format - about 200-300 tables totalling maybe 10 GB worth of data.

      My task is to migrate those tables with their data into a schema in our Oracle 11.2 database.

       

      No tools support the NAV format directly :-) But I have a windows ODBC driver for that format that works.

      The Oracle database runs on Linux and I have no ODBC transparent gateway and no linux ODBC driver that would let me access the NAV datafile directly from within Oracle.

       

      I tried migration wizard in SQL Developer, but it seems it cannot use a "generic ODBC" datasource (even with JDBC-ODBC bridge), it must be a supported database format?

      Then I have tried trial of product Altova MapForce, but it can't seem to read the schema (table/columns) from the ODBC datasource for some reason.

       

      I know reading the schema from the ODBC datasource is possible - I have used third party product "Database Browser" that reads the ODBC with no problems.

      And a colleague has read the schema of the ODBC in C# also.

       

      I am primarily a SQL developer. My colleague said he could write in C# a program that looped over the tables in the ODBC, read the columns, executed "create table" in Oracle, read data from the ODBC, inserted data in Oracle.

      But he is going on vacation right now :-( And I keep thinking that someone else must have written such a migration program ;-)

      I just can't seem to find it by googling? Altova Mapforce was my best bet, but it just seems to have problems with this specific ODBC datasource.

       

      Any ideas for a migration tool that can accept a generic ODBC datasource and not specifically SQL Server, MySQL, DB2, etc.?

       

      Thanks for any hints that might help ;-)

       

      Regards

      Kim Berg Hansen

        • 1. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
          Mkirtley-Oracle

          Hi Kim,

                I am not aware of any thrid party tools to make the migration, so unless anyone else comes up with anything you will have to make a Web search.
          You say you have an ODBC driver on Windows that can access the NAV database.  In that case you could install the Database Gateway for ODBC (DG4ODBC) on the Windows machine and then connect to it from Oracle on Linux to access the NAV tables.
          If you have access to My Oracle Support then the following note has further information and other suggestions -

           

          How To Migrate Non-Oracle Databases For Which a SQL*Developer or Migration Workbench Option Is Not Available (Doc ID 393760.1)

           

          Also, is there anyway to get the NAV data into flat files ?  If so, you could use SQL*Loader to put that into Oracle tables.

           

          Regards,

          Mike

          • 2. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
            Kim Berg Hansen

            Hi, Mike

             

            So far what I've found is this migration tool:

            DTM Migration Kit, powerful data import, export or transformation tool

            Just about what I need, except it is not quite up to the task - fails now and then and does not quite like the ODBC driver ;-)

             

            I have been able to use the DTM tool to create table scripts to get the datatypes of columns right.

            I am at the moment patiently exporting the tables one at a time to flat files. And then load those into the tables I've created.

            It'll work - just a slow process ;-)

             

            But you say I can install DG4ODBC on my windows machine and connect to that from my Linux Oracle?

            I was not aware of that possibility, I thought the gateway had to be on the database server?

            To do that, what will I do? I guess:

            1. Install gateway and listener on windows and setup listener for DG
            2. Add to tnsnames.ora on database server so database link can be created connecting to the windows DG listener

            Would that be correct?

             

            I guess I just assumed the gateway had to be at the database server, but I see now the docs to not state that explicitly.

            (On the other hand the docs also to not state the gateway and listener can be "remote" ;-)

             

            I think that would be a very nice solution...

             

            Regards

            Kim

            • 3. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
              Mkirtley-Oracle

              Hi Kim,

                 The earlier 9i and 10g generic connectivity option - HSODBC - could only run as part of the RDBMS but the 11g DG4ODBC can be installed and run standalone without an RDBMS.  You then connect using database links from Linux using an entry in the RDBMS tnsnames.ora as you have said.

              See these notes depending if Windows is 32-bit or 64-bit -

               

              How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install (Doc ID 466225.1)

              How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install (Doc ID 1266572.1)

               

              The latest 11.2 version is available from My Oracle Support as -

               

              Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

               

              and for the gateways you only need to download -

               

              p13390677_112040_WINNT_4of6.zip

               

              for 32-bit but

               

              p13390677_112040_MSWIN-x86-64_5of7.zip

               

              for 64-bit.
              The downloads have everything necessary for a gateway standalone install.

               

              Regards,

              Mike

              • 4. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
                Kim Berg Hansen

                Thanks, Mike

                 

                I'll definitely try that :-)

                 

                Regards

                Kim

                • 5. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
                  Mkirtley-Oracle

                  Kim,

                    Let me know what happens.

                   

                  Regards,

                  Mike

                  • 6. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
                    Kim Berg Hansen

                    D*ng... :-(

                     

                    Works nicely with gateway and listener installed on my Windows PC and an entry in Linux DB server TNSNAMES.ORA and a database link.

                     

                    But when I select, I get the error:

                     

                    ORA-28500: forbindelse fra ORACLE til et ikke-Oracle-system returnerede denne meddelelse:

                    [Simba][SimbaEngine ODBC Driver]Driver not capable. {HYC00}

                    ORA-02063: går forud for 2 lines fra ITAGO_DB

                     

                    I am almost certain the problem is this requirement from the doc you linked to:

                     

                    - DG4ODBC requires now a ODBC LEVEL 3 COMPLIANT ODBC driver.

                     

                    I am almost sure the ODBC driver in question is probably not level 3 compliant - several other tools I have tried had problems with this driver, they could select the data but not the schema. I assume that might be ODBC level compliancy at fault :-(

                     

                    Oh well, it was a nice try and definitely something I can use for other cases :-)

                    Thanks for your help.

                     

                    Regards

                    Kim

                    • 7. Re: Migrate tables and data from ODBC datasource to Oracle 11.2
                      Mkirtley-Oracle

                      Hi Kim,

                           Yes, it could be that it is not ODBC v3 compliant or it does not support all the ODBC functions needed by Dg4ODBC. these are listed in the Dg4ODBC documentation in the section "ODBC Connectivity Requirements".
                      if you get a gateway 255 level trace by setting -

                      HS_FDS_TRACE_LEVEL=255

                      in the gateway init<sid>.ora file then it may show on what it is failing.

                       

                      Regards,

                      Mike