techblog.jj-it.de - Configure Oracle Gateway for ODBC with FreeTDS

Configure Oracle Gateway for ODBC with FreeTDS – Joachim Jäckel

Techblog.jj-it.de Spined HTML

Configure Oracle Gateway for ODBC with FreeTDS – Joachim Jäckel Skip to contentJoachim Jäckel MenuHomeContact Configure Oracle Gateway for ODBC with FreeTDSContents1 Configure Oracle Gateway1.1 Gateway initialization file1.2 Listener configuration1.3 tnsnames.ora configuration1.4 Database Link configuration1.5 Overview on names used in the configuration2 Using the database link2.1Specimensensitive Object Names2.2 Data Type Conversion3 Synopsis of names used in this example4 Troubleshooting4.1 Some typical errormessagesIn the last steps we have configured unixODBC and FreeTDS to wangle an MS SQL Server target database. Now we will configure the Oracle Gateway.Usually the gateway will run on the same system where the Oracle database in running.Configure Oracle GatewayFor each connection we will configurethe gateway initialization file $ORACLE_HOME/hs/admin/initGWSID.orathe oracle net listenertnsnames.ora (optional)a database linkGateway initialization fileOracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the $ORACLE_HOME/hs/admin directory.For this example we will create a file $ORACLE_HOME/hs/admin/initGWMSSQLSID.ora$ORACLE_HOME/hs/admin/initGWMSQLSID.ora # HS_FDS_CONNECT_INFO: data source name from odbc.ini HS_FDS_CONNECT_INFO = testdsn # HS_FDS_TRACE_LEVEL: Values: OFF, ON, DEBUG, default: OFF. Activate to get a trace file in $ORACLE_HOME/hs/log/ #HS_FDS_TRACE_LEVEL = DEBUG # HS_FDS_SHAREABLE_NAME : full path of the ODBC suburbanite HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so # HS_FDS_SUPPORT_STATISTICS: see Oracle Support Doc ID 744636.1 HS_FDS_SUPPORT_STATISTICS = FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 HS_FDS_TIMESTAMP_MAPPING=DATE HS_NLS_NCHAR=UCS2 # # ODBC specific environment variables # # ODBCINI: full path of odbc.ini set ODBCINI = /usr/local/etc/odbc.ini1234567891011121314151617181920# HS_FDS_CONNECT_INFO: data source name from odbc.iniHS_FDS_CONNECT_INFO = testdsn # HS_FDS_TRACE_LEVEL: Values: OFF, ON, DEBUG, default: OFF. Activate to get a trace file in $ORACLE_HOME/hs/log/#HS_FDS_TRACE_LEVEL = DEBUG # HS_FDS_SHAREABLE_NAME : full path of the ODBC driverHS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so # HS_FDS_SUPPORT_STATISTICS: see Oracle Support Doc ID 744636.1HS_FDS_SUPPORT_STATISTICS = FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15HS_FDS_TIMESTAMP_MAPPING=DATEHS_NLS_NCHAR=UCS2## ODBC specific environment variables## ODBCINI: full path of odbc.iniset ODBCINI = /usr/local/etc/odbc.iniListener configurationOn the system where the gateway is configured, you need to add some lines to the file $ORACLE_HOME/network/admin/listener.ora$ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = # full path of ORACLE_HOME here (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) # SID_NAME from initGWMSQLSID.ora (SID_NAME = GWMSQLSID) # PROGRAM = dg4odbc tells the listener the use the database gateway for odbc (PROGRAM = dg4odbc) ) )1234567891011SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      # full path of ORACLE_HOME here      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)      # SID_NAME from initGWMSQLSID.ora      (SID_NAME = GWMSQLSID)      # PROGRAM = dg4odbc tells the listener the use the database gateway for odbc      (PROGRAM = dg4odbc)    )  )If you have once the section SID_LIST= in your file listener.ora the you just add the marked lines. If dont have the section, you add all lines.Important: After waffly the file listener.ora you have to reload the listener:command executed as user oracle lsnrctl reload1lsnrctl reloadChecking the listener:command executed as user oracle lsnrctl status # you should see the GWMSQLSID: # Service "GWMSQLSID" has 1 instance(s). # Instance "GWMSQLSID", status UNKNOWN, has 1 handler(s) for this service...1234lsnrctl status# you should see the GWMSQLSID:#  Service "GWMSQLSID" has 1 instance(s).#    Instance "GWMSQLSID", status UNKNOWN, has 1 handler(s) for this service...tnsnames.ora configurationThis step is optional, TNS information moreover may be specified in the database link.On the system where the database is running, we add some lines to the file $ORACLE_HOME/network/admin/tnsnames.ora.$ORACLE_HOME/network/admin/tnsnames.ora # TESTCONN is my name for the connection. This name will be used in the Database Link TESTCONN= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) # HOST: system where the gateway is running (or localhost if gateway and database host are the same) (HOST = localhost) # PORT: portnumber of listener (usually 1521) (PORT = 1521) ) ) (CONNECT_DATA = # Gateway SID from listener.ora (SID = GWMSQLSID) ) # HS = OK : this is a gateway connection (HS = OK) )123456789101112131415161718# TESTCONN is my name for the connection. This name will be used in the Database LinkTESTCONN=  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)         # HOST: system where the gateway is running (or localhost if gateway and database host are the same)         (HOST = localhost)         # PORT: portnumber of listener (usually 1521)         (PORT = 1521)      )    )    (CONNECT_DATA =      # Gateway SID from listener.ora      (SID = GWMSQLSID)    )    # HS = OK : this is a gateway connection    (HS = OK)  )Database Link configurationThe database link will be created by the oracle schema user who will use it. As an volitional you may create a public database link.For megacosm we need:Username and Password for the target database (e.g. the MS SQL Server database)The TNS plume configured in tnsnames.oraYour Oracle DB Admin should grant CREATE DATABASE LINK and ALTER DATABASE LINK to your schema user.create database link, to be executed by the database user who wants to use the link -- replace user and password with your values create database link TESTLINK connect to "username" identified by "password" using 'TESTCONN'; -- volitional if you have not configured an TNS plume create database link TESTLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWMSQLSID))(HS=OK))';1234-- replace user and password with your valuescreate database link TESTLINK connect to "username" identified by "password" using 'TESTCONN';-- volitional if you have not configured an TNS aliascreate database link TESTLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWMSQLSID))(HS=OK))';Configuration finished!Using the database linkNow we are ready to test the database link -- the simplest test. if you get a result, the connection to the target database works. select 'it works' as status from dual@TESTLINK; -- query for all tables you can see on the target database select * from all_tables@TESTLINK;12345-- the simplest test. if you get a result, the connection to the target database works.select 'it works' as status from dual@TESTLINK; -- query for all tables you can see on the target databaseselect * from all_tables@TESTLINK;Case sensitive Object NamesSQL Server Object names are case-sensitive, Oracle Object Names normally only have uppercase letters. In this example, the target SQL Server database has installed a sample schema Adventure Works 2014. The query for all_tables shows us the names:select owner, table_name from all_tables@TESTLINK OWNER TABLE_NAME ------------------------------ ------------------------------ dbo AWBuildVersion dbo DatabaseLog dbo ErrorLog HumanResources Department HumanResources Employee HumanResources EmployeeDepartmentHistory HumanResources EmployeePayHistory HumanResources JobCandidate HumanResources Shift PersonWritePerson AddressType Person BusinessEntity Person BusinessEntityAddress Person BusinessEntityContact Person ContactType Person CountryRegion Person EmailAddress Person Password Person Person Person PersonPhone Person PhoneNumberType Person StateProvince ...12345678910111213141516171819202122232425OWNER                          TABLE_NAME                  ------------------------------ ------------------------------dbo                            AWBuildVersion                dbo                            DatabaseLog                  dbo                            ErrorLog                      HumanResources                 Department                    HumanResources                 Employee                      HumanResources                 EmployeeDepartmentHistory    HumanResources                 EmployeePayHistory            HumanResources                 JobCandidate                  HumanResources                 Shift                        Person                         Address                      Person                         AddressType                  Person                         BusinessEntity                Person                         BusinessEntityAddress        Person                         BusinessEntityContact        Person                         ContactType                  Person                         CountryRegion                Person                         EmailAddress                  Person                         Password                      Person                         Person                        Person                         PersonPhone                  Person                         PhoneNumberType              Person                         StateProvince          ...To wangle Objects whith lowercase reports from Oracle, we habe to enclose the name with ” characters: Example for select from TableWriteowned by Person:select select * from "Person"."Address"@TESTLINK;1select * from "Person"."Address"@TESTLINK;Data Type ConversionData Types are converted from MS SQL Types to ODBC Types and the to Oracle Types. More Info you find in the documentation for FreeTDS, unixODBC and Oracle Gateways.To see, what data type we get, we can query all_tab_columns:select column_name, data_type, data_length, data_precision, nullable from all_tab_columns@testlink where owner='Person' and table_name='Address'; SQL> select column_name, data_type, data_length, data_precision, nullable from all_tab_columns@testlink where owner='Person' and table_name='Address'; COLUMN_NAME DATA_TYPE DATA_LENGT DATA_PRECI NULLABLE ------------------------------ --------------- ---------- ---------- ---------- AddressID NUMBER 4 22 N AddressLine1 NVARCHAR2 120 N AddressLine2 NVARCHAR2 120 Y City NVARCHAR2 60 N StateProvinceID NUMBER 4 22 N PostalCode NVARCHAR2 30 N SpatialLocation LONG RAW 2147483647 Y rowguid 16 N ModifiedDate DATE 16 N1234567891011121314SQL> select column_name, data_type, data_length, data_precision, nullable from all_tab_columns@testlink where owner='Person' and table_name='Address';COLUMN_NAME                    DATA_TYPE       DATA_LENGT DATA_PRECI NULLABLE------------------------------ --------------- ---------- ---------- ----------AddressID                      NUMBER                   4         22 N        AddressLine1                   NVARCHAR2              120            N        AddressLine2                   NVARCHAR2              120            Y        City                           NVARCHAR2               60            N        StateProvinceID                NUMBER                   4         22 N        PostalCode                     NVARCHAR2               30            N        SpatialLocation                LONG RAW        2147483647            Y        rowguid                                                16            N        ModifiedDate                   DATE                    16            NSynopsis of names used in this exampleIn the configuration many names are used. This will be sometime confusing. Here you see all names:NamePurposedefined inused incase sensitive?TESTLINKdatabase link namecreate database link ...sql queries, eg. select * from table@TESTLINKnoTESTCONNTNS aliastnsnames.oraUSING clause of database linknoGWMSQLSIDGateway SIDlistener.oratnsnames.ora and filename of initGWSID.oraYESTESTDSNODBC Datasource Nameodbc.iniHS_FDS_CONNECT_INFO in file initGWSID.oranoHOSTNAMEHostname or IP write of target database serverodbc.ininoDBNAMEMS SQL Server Database nameSQL Serverodbc.inino There are some limitations on name length and specimen sensitiveness. You are self-ruling to use own names, plane you can use for all objects the same name but you should know what your are doing.Troubleshooting For a gateway connection, there are many components involved:the oracle databasethe database linkthe TNSPlumein tnsnames.orathe network connection between database and gatewaythe listener configuration in listener.orathe gateway configuration file initGWSID.orathe unixODBC installationthe unixODBC configuration file odbc.inithe FreeTDS installationthe network connection to target database serverthe target databaseIf the connection does not work, unchangingly first test with isql and tsql and try to isolate the problem.Some typical errormessagesErrormessageReasonORA-12154: TNS:could not resolve the connect identifier specifiedTNSPlumeused in the database link not found in tnsnames.oraORA-28545: error diagnosed by Net8 when connecting to an wage-earner Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from TESTLINK Mismatch between tnsnames.ora and listener.ora (e.g. wrong SID, wrong port number)ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ORA-28541: Error in HS init file on line 39. ORA-02063: preceding 2 lines from TESTLINK error in file $ORACLE_HOME/hs/admin/initGWSID.oraORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Data source name not found, and no default suburbanite specified {IM002} ORA-02063: preceding 2 lines from TESTLINK Mismatch between data source name in odbc.ini and HS_FDS_CONNECT_INFO in initGWSID.oraMoreovercheck the ODBCINI setting ininitGWSID.oraORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Can't unshut lib '/usr/local/lib/libtdsodbc.so' : file not found {01000} ORA-02063: preceding 2 lines from TESTLINK This error should have found once when testing with isql Search for: SearchPagesOracle Gateway to ODBCUnix ODBC InstallationAccess to MS SQL Server Database: FreeTDS InstallationConfigure Oracle Gateway for ODBC with FreeTDSAccess to MySQL Database: Connector/ODBC InstallationConfigure Oracle Gateway for ODBC with MySQL ConnectorAccess to Informix Database: Informix ODBCSuburbaniteInstallationConfigure Oracle Gateway for ODBC with Informix ODBC DriverRecent Posts Gateway from Oracle to Informix Gateway from Oracle to MySQL Gateway from Oracle to MS SQL ServerAbout me Consultant for Databases, Linux/Unix, High Availabiltiy. German Homepage www.jj-it.de ContactHomeContact Joachim Jäckel Proudly powered by WordPress