Installing OTG and ODBC on Oracle Linux 6
EdStevens Sep 20, 2019 6:25 PMA 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.