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

Configure Oracle Gateway for ODBC with MySQL Connector

Techblog.jj-it.de Spined HTML

Configure Oracle Gateway for ODBC with MySQL Connector Skip to contentJoachim Jäckel MenuHomeContact Configure Oracle Gateway for ODBC with MySQL ConnectorContents1 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 MySQL Connector to wangle a MySQL 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/initGWMYSQLSID.ora$ORACLE_HOME/hs/admin/initGWMYSQLSID.ora # HS_FDS_CONNECT_INFO: data source name from odbc.ini HS_FDS_CONNECT_INFO = mysqltestdsn # 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 # transpiration the characterset if you have problems # HS_LANGUAGE should be set exactly the same as Oracle database ## HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252 # If you get ORA-28528: Heterogeneous Services datatype conversion error # then try HS_FDS_SQLLEN_INTERPRETATION=32 # Problem occurs, if you use a 32Bit unixODBC on a 64Bit System # (see Oracle Support Doc ID 554409.1) # HS_FDS_SQLLEN_INTERPRETATION=32 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.ini123456789101112131415161718192021222324252627# HS_FDS_CONNECT_INFO: data source name from odbc.iniHS_FDS_CONNECT_INFO = mysqltestdsn # 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 # transpiration the characterset if you have problems# HS_LANGUAGE should be set exactly the same as Oracle database## HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252 # If you get ORA-28528: Heterogeneous Services datatype conversion error# then try HS_FDS_SQLLEN_INTERPRETATION=32# Problem occurs, if you use a 32Bit unixODBC on a 64Bit System# (see Oracle Support Doc ID 554409.1)# HS_FDS_SQLLEN_INTERPRETATION=32 HS_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 initGWMYSQLSID.ora (SID_NAME = GWMYSQLSID) # 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 initGWMYSQLSID.ora      (SID_NAME = GWMYSQLSID)      # 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:Withoutwaffly the file listener.ora you have to reload the listener:command executed as user oracle on the gateway host lsnrctl reload1lsnrctl reloadChecking the listener:command executed as user oracle on the gateway host lsnrctl status # you should see the GWMYSQLSID: # Service "GWMYSQLSID" has 1 instance(s). # Instance "GWMYSQLSID", status UNKNOWN, has 1 handler(s) for this service...1234lsnrctl status# you should see the GWMYSQLSID:#  Service "GWMYSQLSID" has 1 instance(s).#    Instance "GWMYSQLSID", 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 # GWMYSQLCONN is my name for the connection. This name will be used in the Database Link GWMYSQLCONN= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) # HOST: system where the gateway is running (or localhost if gateway and database host are the same) (HOST = gatwewayserver) # PORT: portnumber of listener (usually 1521) (PORT = 1521) ) ) (CONNECT_DATA = # Gateway SID from listener.ora (SID = GWMYSQLSID) ) # HS = OK : this is a gateway connection (HS = OK) )123456789101112131415161718# GWMYSQLCONN is my name for the connection. This name will be used in the Database LinkGWMYSQLCONN=  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)         # HOST: system where the gateway is running (or localhost if gateway and database host are the same)         (HOST = gatwewayserver)         # PORT: portnumber of listener (usually 1521)         (PORT = 1521)      )    )    (CONNECT_DATA =      # Gateway SID from listener.ora      (SID = GWMYSQLSID)    )    # HS = OK : this is a gateway connection    (HS = OK)  )Checking with tnsping:command executed as user oracle on database host tnsping gwmysqlconn TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 19-JAN-2016 18:56:54 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES connector to resolve the plume Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = GATEWAYSERVER) (PORT = 1521))) (CONNECT_DATA = (SID = GWMYSQLSID)) (HS = OK)) OK (0 msec)123456789101112tnsping gwmysqlconn TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 19-JAN-2016 18:56:54 Copyright (c) 1997, 2014, Oracle.  All rights reserved. Used parameter files:  Used TNSNAMES connector to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = GATEWAYSERVER) (PORT = 1521))) (CONNECT_DATA = (SID = GWMYSQLSID)) (HS = OK))OK (0 msec)The tnsping writ only verifies the configuration in tnsnames.ora and the listener configuration on the gateway server. It shows, that the listener can be reached and the SID is configured in the listener.It show OK moreover when something without the listener is misconfigured or not working.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 MySQL 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 GWMYSQLLINK connect to "username" identified by "password" using 'GWMYSQLCONN'; -- volitional if you have not configured an TNS plume create database link GWMYSQLLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWMYSQLSID))(HS=OK))';1234-- replace user and password with your valuescreate database link GWMYSQLLINK connect to "username" identified by "password" using 'GWMYSQLCONN';-- volitional if you have not configured an TNS aliascreate database link GWMYSQLLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWMYSQLSID))(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@GWMYSQLLINK; -- query for all tables you can see on the target database select * from all_tables@GWMYSQLLINK;12345-- the simplest test. if you get a result, the connection to the target database works.select 'it works' as status from dual@GWMYSQLLINK; -- query for all tables you can see on the target databaseselect * from all_tables@GWMYSQLLINK;Case sensitive Object NamesSQL Server Object names are case-sensitive, Oracle Object Names normally only have uppercase letters. In this example, the target MySQL database has installed a sample database sakila. The query for all_tables shows us the names:select owner, table_name from all_tables@GWMYSQLLINK OWNER TABLE_NAME ------------------------------ ------------------------------ DG4ODBC two-face DG4ODBC write DG4ODBC category DG4ODBC municipality DG4ODBC country DG4ODBC consumer DG4ODBC mucosa DG4ODBC film_actor DG4ODBC film_category DG4ODBC film_text DG4ODBC inventory DG4ODBC language DG4ODBC payment DG4ODBC rental DG4ODBC staff DG4ODBC store 16 rows selected.1234567891011121314151617181920OWNER                          TABLE_NAME------------------------------ ------------------------------DG4ODBC                        actorDG4ODBC                        addressDG4ODBC                        categoryDG4ODBC                        cityDG4ODBC                        countryDG4ODBC                        customerDG4ODBC                        filmDG4ODBC                        film_actorDG4ODBC                        film_categoryDG4ODBC                        film_textDG4ODBC                        inventoryDG4ODBC                        languageDG4ODBC                        paymentDG4ODBC                        rentalDG4ODBC                        staffDG4ODBC                        store 16 rows selected.OWNER is unchangingly DG4ODBC (Database Gateway for ODBC). When you wangle a MySQL database via ODBC you unchangingly only see the default database of the user. If you want to wangle other databases, you have to create separate DSN (odbc.ini) and Database Links (Oracle Database).To wangle Objects whith lowercase reports from Oracle, we habe to enclose the name with ” characters: Example for select from Table film:SELECT COLUMN "film_id" FORMAT 9999; COLUMN "title" FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SELECT "film_id", "title", "last_update" FROM "film"@GWMYSQLLINK WHERE "film_id" <=10; film_id title last_update ------- -------------------- ------------------- 1 ACADEMY DINOSAUR 2006-02-15 05:03:42 2 ACE GOLDFINGER 2006-02-15 05:03:42 3 ADAPTATION HOLES 2006-02-15 05:03:42 4 AFFAIR PREJUDICE 2006-02-15 05:03:42 5 AFRICAN EGG 2006-02-15 05:03:42 6 AGENT TRUMAN 2006-02-15 05:03:42 7 AIRPLANE SIERRA 2006-02-15 05:03:42 8 AIRPORT POLLOCK 2006-02-15 05:03:42 9 ALABAMA DEVIL 2006-02-15 05:03:42 10 ALADDIN CALENDAR 2006-02-15 05:03:42 10 rows selected.1234567891011121314151617181920COLUMN "film_id" FORMAT 9999;COLUMN "title" FORMAT A20;ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SELECT "film_id", "title", "last_update" FROM "film"@GWMYSQLLINK WHERE "film_id" <=10; film_id title                last_update------- -------------------- -------------------      1 ACADEMY DINOSAUR     2006-02-15 05:03:42      2 ACE GOLDFINGER       2006-02-15 05:03:42      3 ADAPTATION HOLES     2006-02-15 05:03:42      4 AFFAIR PREJUDICE     2006-02-15 05:03:42      5 AFRICAN EGG          2006-02-15 05:03:42      6 AGENT TRUMAN         2006-02-15 05:03:42      7 AIRPLANE SIERRA      2006-02-15 05:03:42      8 AIRPORT POLLOCK      2006-02-15 05:03:42      9 ALABAMA DEVIL        2006-02-15 05:03:42     10 ALADDIN CALENDAR     2006-02-15 05:03:42 10 rows selected.Data Type ConversionData Types are converted from MySQL Types to ODBC Types and the to Oracle Types. More Info you find in the documentation for MySql Connector, 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@GWMYSQLLINK WHERE table_name='film'; COLUMN column_name FORMAT A30 COLUMN data_type FORMAT A8 COLUMN data_length FORMAT 99999 COLUMN data_precision FORMAT 999 COLUMN nullable FORMAT A1 SELECT column_name, data_type, data_length, data_precision, nullable FROM all_tab_columns@GWMYSQLLINK WHERE table_name='film'; COLUMN_NAME DATA_TYP DATA_LENGTH DATA_PRECISION N ------------------------------ -------- ----------- -------------- - film_id NUMBER 2 22 Y title VARCHAR2 765 N unravelment LONG 65535 Y release_year NUMBER 1 22 Y language_id NUMBER 1 22 N original_language_id NUMBER 1 22 Y rental_duration NUMBER 1 22 N rental_rate NUMBER 6 22 N length NUMBER 2 22 Y replacement_cost NUMBER 7 22 N rating CHAR 15 Y special_features CHAR 162 Y last_update DATE 16 Y 13 rows selected.12345678910111213141516171819202122232425COLUMN column_name FORMAT A30COLUMN data_type FORMAT A8COLUMN data_length FORMAT 99999COLUMN data_precision FORMAT 999COLUMN nullable FORMAT A1 SELECT column_name, data_type, data_length, data_precision, nullable FROM all_tab_columns@GWMYSQLLINK WHERE table_name='film'; COLUMN_NAME                    DATA_TYP DATA_LENGTH DATA_PRECISION N------------------------------ -------- ----------- -------------- -film_id                        NUMBER             2             22 Ytitle                          VARCHAR2         765                Ndescription                    LONG           65535                Yrelease_year                   NUMBER             1             22 Ylanguage_id                    NUMBER             1             22 Noriginal_language_id           NUMBER             1             22 Yrental_duration                NUMBER             1             22 Nrental_rate                    NUMBER             6             22 Nlength                         NUMBER             2             22 Yreplacement_cost               NUMBER             7             22 Nrating                         CHAR              15                Yspecial_features               CHAR             162                Ylast_update                    DATE              16                Y 13 rows selected.Synopsis 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?GWMYQLLINKdatabase link namecreate database link ...sql queries, eg. select * from table@GWMSQLLINKnoGWMYSQLCONNTNS aliastnsnames.oraUSING clause of database linknoGATEWAYSERVERHostname where Gateway is installedtnsnames.oranoGWMYQLSIDGateway SIDlistener.oratnsnames.ora and filename of initGWSID.oraYESMYSQLDSNODBC Datasource Nameodbc.iniHS_FDS_CONNECT_INFO in file initGWSID.oranoMYSQLSERVERHostname or IP write of target database serverodbc.ininoSAKILAMySQL Server Database nameMySQL 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 MySQL Connector installationthe network connection to target database serverthe target databaseIf the connection does not work, unchangingly first test with isql 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 GWMYSQLLINK This error should moreover find when testing with isqlORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][MySQL][ODBC 5.3(w) Driver]Can't connect to MySQL server on 'mysqlserver' (111) {HY000,NativeErr = 2003} ORA-02063: preceding 2 lines from GWMYSQLLINK MySQL Database not running. This error should moreover find 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