9 Replies Latest reply on Oct 2, 2019 2:40 PM by EdStevens

    Installing OTG and ODBC on Oracle Linux 6

    EdStevens
    EdStevens Sep 20, 2019 6:25 PM

      A few years back I installed OTG on a Windows 2012 server.  We have multiple 'source' (oracle) databases on multiple servers and multiple 'target' MSSQL databases on multiple servers.  I didn't want to have to install and maintain multiple gateways, and given the 'many to many' configuration of source and target dbs, there was no particular server that seemed to suggest itself as the natural home for the gateway.  Since the Windows servers hosting MSSQL already had the necessary ODBC infrastructure, I just picked the one that seemed to host most (but not all) of the production MSSQL databases that would be the target of the HS connections from Oracle.

       

      So far so good, and it has been working just fine ever since.  But the time has come to look at upgrading and I'm thinking that I'd also like to relocate it to an Oracle Linux server.  I have one that is currently dedicated to hosting the RMAN catalog, and it seems that would be the perfect 'database neutral' location to put the new OTG. 

       

      So that brings me to two issues.

       

      First, the simplest.  I’ve often argued for running only one listener per server.  However, that was always in the context of serving oracle databases on the particular server., Now, installing an OTG on a server that is also running an Oracle database, it seems that perhaps it might be best to run a second listener, from the OTG home, serving just HS connections.  However, I can’t really articulate why.  Comments and thoughts on that subject?

       

      Second is the installation of the ODBC infrastructure.  The Gateway Installation Guide (https://docs.oracle.com/en/database/oracle/oracle-database/18/otgis/config-odbc-gateway.html#GUID-32187400-1FC2-4014-9193-9FE3ADC5A6A1) doesn’t really discuss the actual installation of that. 

       

      One Google search led me to “Installing ODBC Drivers for Linux x86-64” in Grid Infrastructure Installation and Upgrade Guide for Linux (rel 12.2) at https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cwlin/installing-odbc-drivers-for-linux-x86-64.html#GUID-494A764B-22A6-4DB4-AB45-AE4B467650D2.  There, it said to download the ODBC driver manager from www.unixodbc.org.  That seems rather odd, given that Oracle supplies those components themselves in their public yum repository. Why would they refer to a third-party source?

       

      Looking further, I found Tim Hall’s article at https://oracle-base.com/articles/linux/create-an-odbc-dsn-on-linux, which is where I discovered that oracle supplies the manager in their yum repository.  But then Tim has you get the actual MSSQL driver from the ‘freetds’ package in the EPL yum repository.  That also strikes me as odd.  Why would one not get the MSSQL driver from MS?

       

      I would expect to get the ODBC manager for Oracle Linux from an oracle source, and I would expect to get the MSQL driver from either Oracle or MS.  I find these references to other sources to be somewhat disconcerting.

       

      So … before going down any more rabbit holes, I thought I just throw this all up for comment  and discussion. 

       

      • 215 Views
      • Tags:
        • 1. Re: Installing OTG and ODBC on Oracle Linux 6
          EdStevens
          EdStevens Sep 20, 2019 6:38 PM (in response to EdStevens)

          Can't edit the original, so I'll add this here ...

           

          Found MS instructions for installing their ODBC driver, here.  In usual Microsoft fashion the procedure simply assumes that everything will work as advertised, with no notes about assumptions, prereqs, etc.  It's another matter of "here, this will work for you …. until it doesn't".

          • 2. Re: Installing OTG and ODBC on Oracle Linux 6
            L. Fernigrini
            L. Fernigrini Sep 20, 2019 6:53 PM (in response to EdStevens)

            Regarding your first question, I understand the general idea of having a single Listener, but since the extra load that having a separate one is completely negligible I usually configure a dedicated listener for the Gateway software when installing it on a computer that already has one, just not to mess with existing listener. I do not have a rational justification, just prefer to not mix up things. If I decide to move the gateway to another server I can just copy the listener config file with minor changes.

             

            Regarding the install of SQL Server drivers on Linux, I did that once many times ago and I do not have the documentation available, but it was not as simple as supposed, sorry I cannot help you on that!

            • 3. Re: Installing OTG and ODBC on Oracle Linux 6
              EdStevens
              EdStevens Oct 1, 2019 1:39 PM (in response to EdStevens)

              I'm not a big fan of 'bumping' a thread, but it's been 10 days since initial post, and I'm somewhat surprised this hasn't drawn more discussion.

               

              I've come to terms with running two listeners - one for the database(s) and one for the gateway.  But I'm still troubled by the lack of documentation on installing and configuring the ODBC part of the setup, and Oracle's own docs directing away from their supplied products . . . .

               

              Even trying to find the documentation seems to be a bit of an Easter egg hunt.

              • 4. Re: Installing OTG and ODBC on Oracle Linux 6
                mxallen-Oracle
                mxallen-Oracle Oct 1, 2019 1:47 PM (in response to EdStevens)

                Ed,

                 

                Oracle does not make nor support 3rd party ODBC drivers. 

                 

                Additionally, one can use virtually any 3rd party ODBC driver that meets the requirements documented

                under ODBC Connectivity Requirements in Chapter 1 of Oracle Database Gateway for ODBC

                https://docs.oracle.com/en/database/oracle/oracle-database/19/odbcu/index.html

                 

                These are the reasons that there is not more instruction and information included in the documentation.

                 

                I think the lack of discussion has to do with only three of us actively participating in this community.  I do appreciate

                your and L. Fernigrini's contributions.

                 

                Hopefully, this explanation helps.

                 

                 

                Take Care!

                Matt

                • 5. Re: Installing OTG and ODBC on Oracle Linux 6
                  EdStevens
                  EdStevens Oct 1, 2019 2:01 PM (in response to EdStevens)

                  Just an example of the kind of rabbit holes Oracle's documentation becomes ...

                   

                  Looking at the Gateway Installation and Configuration Guide for Microsoft Windows

                  Chapter 10, "Installing Oracle Database Gateway for ODBC".  under "Software Requirements", says this:

                   

                  Software Requirements

                  Oracle Database Gateway for ODBC is supported on the following Microsoft Windows (64-bit) operating systems:

                  • Microsoft Windows Server 2003 - all x64 editions
                  • Microsoft Windows Server 2003 R2 - all x64 editions
                  • Microsoft Windows XP Professional x64 Edition
                  • Microsoft Windows Vista x64 - Business, Enterprise, and Ultimate editions
                  • Microsoft Windows 2008 x64

                  So let's get this straight.  The documentation for a 19c version (released sepcificaly for 2019) says that the newest version of Windows that this 2019 software is certified for is eleven years old!

                   

                  The document goes on to say

                   

                  Certified Configurations

                  For the latest certified configuration refer to the OTN Web site:

                  http://www.oracle.com/technetwork/database/gateways/index.html

                  That page is headed "Certification Matrix for Open System GatewaysUpdated: May11th2017"

                  And as would be expected from the title, it only lists 12.2 gateways, not the 19.x of the documentation that pointed to this as the "latest".

                   

                  And an interesting side observation, the browser tab for that page has a title of "Since you are an Oracle employee you can view the Certify via the internal URL (see certmatrix -168347.pdf)".   Hunh?!?!? What's that for a tab title?  And I am not an Oracle employee.

                  • 6. Re: Installing OTG and ODBC on Oracle Linux 6
                    L. Fernigrini
                    L. Fernigrini Oct 1, 2019 2:11 PM (in response to EdStevens)

                    Unfortunately, each driver provider (either the "owner" of the data source, like Microsoft for SQL Server, SAP for Sybase or IBM for Informix or third party companies like DataDirecto from Progress, Easysoft or Intersoft) have their own steps and nuisances....

                     

                    The first thing I recommend when someone post on how to connect Oracle to an ODBC source, is to install the driver and test it with a simple app that support ODBC (Excel on Windows,  RazorSQL on other environments) since that is usually more complex and error prone that the Gateway installation and configuration process (at least for me).

                     

                    Seems strange for me that there are few people following this space, given the fact that I find the ODBC gateway to be a great feature to interact with directly diverse data sources rather than running complex import / export to get or produce data, but this is what we have to live with!

                    • 7. Re: Installing OTG and ODBC on Oracle Linux 6
                      EdStevens
                      EdStevens Oct 2, 2019 12:30 PM (in response to L. Fernigrini)

                      L. Fernigrini wrote:

                       

                      Unfortunately, each driver provider (either the "owner" of the data source, like Microsoft for SQL Server, SAP for Sybase or IBM for Informix or third party companies like DataDirecto from Progress, Easysoft or Intersoft) have their own steps and nuisances....

                       

                      Of course every product's driver will have it's own installation steps and "gotcha's".    What's getting my goat is the rabbit trails that Oracle's own documentation sends us on.  And why (in the case of the odbc manager) their documentation sends you to an obscure 3d party provider, when you can get a (supposedly approved?) product from Oracle's own yum server.

                      • 8. Re: Installing OTG and ODBC on Oracle Linux 6
                        L. Fernigrini
                        L. Fernigrini Oct 2, 2019 12:38 PM (in response to EdStevens)

                        That is probably for Linux installations, and is really annoying.

                         

                        Last couple of Gateway installation I've done were for migration projects, where I just needed to use it only for a couple of weeks at most, so I directly used Windows PC to host the Gateway, and it was really simple to install and ODBC manager comes installed, so no struggle on my side. So it's been quite long since I have done a full Gateway install on Linux / Unix.

                        • 9. Re: Installing OTG and ODBC on Oracle Linux 6
                          EdStevens
                          EdStevens Oct 2, 2019 2:40 PM (in response to L. Fernigrini)

                          Yeah, at this point I've pretty much abandoned the idea of hosting it on one of my linux servers and just continuing to use the current Windows server for the upgrade ....   which goes against my "I avoid Windows every chance I get" mantra ....