This content has been marked as final. Show 19 replies
The article you refer to is for HSODBC which was desupported a long time agao and which was replaced by the Database Gateway for ODBC (DG4ODBC) in 11g.
To configure DG4ODBC on Windows 64-bit have a look at this note in My Oracle Support -
How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems (Doc ID 1266572.1)
The latest version is 22.214.171.124 which is available from My Oracle Support as -
Patch 10404530: 126.96.36.199.0 PATCH SET FOR ORACLE DATABASE SERVER
- logon to My Oracle Support
- go to Patches & Updates section
- search for patch number 10404530 and your platform
- you may need to press the '+' button to see the platform list
- click on the patch number
- from the download screen you only need to download the following for the gateways -
- this is the Gateway media pack and has everything needed for a standalone gateway install.
- unzip the file and run the installer and choose the gateway you want.
It would be better to install it in a separate ORACLE_HOME from any existing installs.
Yes, that is all you need for the gateways on Windows 64-bit -
Oracle Gateways p10404530_112030_platform_5of7.zip
as it details in the readme, so for Windows 64-bit you need the zip file you have named -
10404530 is called a paptchset but it is actually a full release. The gateway zip file has everything needed for a standalone install, so install in a separate OH from any other installs. This makes maintenance and configuration easier.
Hi Mike, I opened up Windows Console >> ODBC Data Source Administrator, but where would I enter the SID_DESC, PROGRAM etc. as below? There is a long list of Drivers.
When I pick Oracle for XE I get the option as image below:
0) ODBC System DSN Please open the ODBC Administrator and configure a SYSTEM DSN choosing your ODBC driver and following its onscreen dialog. 1) The listener needs a new SID entry like the following: (SID_DESC = (PROGRAM = dg4odbc) (SID_NAME = dg4odbc) (ORACLE_HOME = D:\oracle\product\11.2.0) ) Document Display https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx...
When setting up the System DSN for DG4ODBC to use then you need to select the ODBC driver needed to connect to the non-Oracle database, not an Oracle ODBC driver. In your case it would use a SQL*Server ODBC driver.
The screen shot shows you already have a DSN using the 'SQL Server' driver so you could use that for the HS_FDS_CONNECT_INFO parameter in the gateway init<sid>.ora if it connects to the SQL*Server database you want. If not, then create a new one connecting to the SQL*Server database to which you want to connect.
I've followed that article from Oracle Support and still bogged down. I have installed the gateway via the OUI, and did the following:
Step 0: Already done and tested as with screenshot
Step 1: I created my entry as below in C:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora and bounced the listener with lsnrctl reload and pinged dg4odbc. The result was ok.
Step 2: tnsnames.ora in Oracle Home where DB in installed: C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
# listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora # Generated by Oracle configuration tools. #LISTENER = # (DESCRIPTION_LIST = # (DESCRIPTION = # (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) # (ADDRESS = (PROTOCOL = TCP)(HOST =AA-PC)(PORT = 1522)) # ) # ) LISTENER = (DESCRIPTION_LIST = ( DESCRIPTION = (ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC) (PORT = 1521)) ) ) ) ADR_BASE_LISTENER = C:\product\11.2.0\tg_1 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (PROGRAM = dg4odbc) (SID_NAME = dg4odbc) (ORACLE_HOME = C:\product\11.2.0\tg_1) ) )
But here it wasn't clear if I should edit it here or in C:\oraclexe\app\oracle\product\11.2.0\server\hs\admin\tnsnames.ora, I did in both places.
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) dg4odbc = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK) )
Also updated sqlnet.ora in C:\product\11.2.0\tg_1\NETWORK\ADMIN\sqlnet.ora
3. Created the file initdg4odbc.ora in C:\product\11.2.0\tg_1\hs\admin which has the following:
SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DEFAULT_DOMAIN = AA-PC NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Created the database link in SYS schema but still got that net8 error as above.
# # HS init parameters # HS_FDS_CONNECT_INFO = MYSQLSERVERDSN HS_FDS_TRACE_LEVEL = off
Orion Starc wrote:Tnsping does not test services, only listener port response.
bounced the listener with lsnrctl reload and pinged dg4odbc. The result was ok.
ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC) (PORT = 1521))Tg_1 home's listener should be set to a port not used by XE listener, e.g 1522.
dg4odbc =And this entry in xe home should match with tg listener's port number etc.
Test by select * from dual@nameofdblink;
Created the database link in SYS schema but still got that net8 error as above.In general, never use SYS for such purposes. Create objects as your own dba-type user, or in your application schema.
Thanks orafad I've made those changes now, restarted listener. Still no luck. Is there anything else that I can check?
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQLSERVER
28545. 0000 - "error diagnosed by Net8 when connecting to an agent"
*Cause: An attempt to call an external procedure or to issue SQL
to a non-Oracle system on a Heterogeneous Services database link
failed at connection initialization. The error diagnosed
by Net8 NCR software is reported separately.
*Action: Refer to the Net8 NCRO error message. If this isn't clear,
check connection administrative setup in tnsnames.ora
and listener.ora for the service associated with the
Heterogeneous Services database link being used, or with
'extproc_connection_data' for an external procedure call.
Error at Line: 6 Column: 19
C:\Windows\system32>lsnrctl status LSNRCTL for 32-bit Windows: Version 188.8.131.52.0 - Production on 25-SEP-2012 10:00 :05 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 184.108.40.206.0 - Produ ction Start Date 21-SEP-2012 16:29:39 Uptime 3 days 17 hr. 30 min. 26 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a dmin\listener.ora Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\AA-PC\liste ner\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=8081))(Presentati on=HTTP)(Session=RAW)) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "xe", status READY, has 1 handler(s) for this service... Service "xe" has 1 instance(s). Instance "xe", status READY, has 1 handler(s) for this service... The command completed successfully