Forum Stats

  • 3,734,738 Users
  • 2,247,033 Discussions
  • 7,857,475 Comments

Discussions

(ODBC) Oracle not reading initSID.ora

gatesps
gatesps Member Posts: 7
edited Oct 21, 2019 9:47AM in Heterogeneous Connectivity

First time setting up an ODBC link with Oracle. I've set up the listener.ora, tnsnames.ora, and the init file in \hs\admin, but the database link always fails with:

An error was encountered performing the requested operation:ORA-28545: error diagnosed by Net8 when connecting to an agentUnable to retrieve text of NETWORK/NCR message 65535ORA-02063: preceding 2 lines from EXTLINK28545. 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.Vendor code 28545

No trace file appears, which makes me think that the init file isn't even being read. I can connect to the ODBC database using Access, so I know it works. The ODBC drivers and DG4ODBC are both 64-bit.

Any ideas what I'm doing wrong? This is on a 64-bit fresh install of Oracle 12.2.0.1.0 on Windows 10. I've changed some names for confidentiality reasons, but the code is otherwise untouched. The DSN is EXTLINK.

tnsnames.ora:

LISTENER_ORCL =  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))ORACLR_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )    (CONNECT_DATA =      (SID = CLRExtProc)      (PRESENTATION = RO)    )  )ORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl.[DOMAIN_NAME].local)    )  )EXTLINK=  (DESCRIPTION=    (ADDRESS=(PROTOCOL=TCP)(HOST = [HOSTNAME])(PORT=1521))    (CONNECT_DATA=(SID=EXTLINK))    (HS=OK)  )

listener.ora:

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = CLRExtProc)      (ORACLE_HOME = C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1)      (PROGRAM = extproc)      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")    )     (SID_DESC=       (SID_NAME=EXTLINK)       (ORACLE_HOME=C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1)       (PROGRAM=dg4odbc)     )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))      (ADDRESS = (PROTOCOL = TCP)(HOST = [HOSTNAME])(PORT = 1521))    )  )CONNECT_TIMEOUT_LISTENER = 0 

%ORACLE_HOME\hs\admin\initEXTLINK.ora:

# WindowsHS_FDS_CONNECT_INFO = EXTLINKHS_FDS_TRACE_LEVEL = 255HS_FDS_FETCH_ROWS=1HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL#HS_NLS_NCHAR=UCS2HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

Connect string:

CREATE PUBLIC DATABASE LINK EXTLINK CONNECT TO "Username" IDENTIFIED BY "Pass" USING 'EXTLINK';

Best Answer

Answers

  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited Oct 15, 2019 2:34PM

    A couple of things to check:

    1.  Look at the C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1\bin

    dirctory and make certain there is a dg4odbc.exe file present.

    If so, what is its files size? (Should not be 0)

    2.  Does the does the dg4odbc.exe file have privilege restrictions that would keep it from being read or executed?

    3.  Does LSNRCTL STATUS show a service handler for EXTLINK?

    If not, you need to stop and start the listener.

    Regards,

    Matt

  • gatesps
    gatesps Member Posts: 7
    edited Oct 15, 2019 3:34PM
    1. dg4odbc.exe is present. It's 1.03 MB.
    2. I couldn't see anything weird with the permissions. ORA_OraDB12Home1_SVCACCTS is set to be able to access it, and an un-elevated command prompt can run it.
    3. LSNRCTL STATUS does show a handler for EXTLINK, but it says that the status is UNKNOWN. Could that be my problem?
    LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 15-OCT-2019 13:26:49Copyright (c) 1991, 2016, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER------------------------Alias                    LISTENERVersion                  TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - ProductionStart Date                15-OCT-2019 10:15:40Uptime                    0 days 3 hr. 11 min. 12 secTrace Level              offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File  C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1\network\admin\listener.oraListener Log File        C:\app\[MYUSERNAME]\virtual\diag\tnslsnr\[HOSTNAME]\listener\alert\log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=[HOSTNAME].[DOMAINNAME].local)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=[HOSTNAME].[DOMAINNAME].local)(PORT=5500))(Security=(my_wallet_directory=C:\APP\[MYUSERNAME]\VIRTUAL\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))Services Summary...Service "EXTLINK" has 1 instance(s).  Instance "EXTLINK", status UNKNOWN, has 1 handler(s) for this service...Service "CLRExtProc" has 1 instance(s).  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "d5872d4283fa40c38b34f3f9db73b696.[DOMAINNAME].local" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...Service "orcl.[DOMAINNAME].local" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...Service "orclXDB.[DOMAINNAME].local" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...Service "orclpdb.[DOMAINNAME].local" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully
  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited Oct 16, 2019 7:13AM

    Status UNKNOWN is expected and normal.

    Does the address of the entry in tnsnames.ora actually point to the listener address?

    If you change the HOST value from the loopback IP to the actual hostname or IP of the

    server, does that make a difference?

    Matt

  • gatesps
    gatesps Member Posts: 7
    edited Oct 16, 2019 10:42AM

    It doesn't seem to make a difference. Here's what I've tried so far:

    • I set the HOST in both files to the hostname, to the IP address, and to [HOSTNAME].[DOMAIN].local, but with no change.
    • I made the connection address (PROTOCOL/HOST/PORT) in tnsnames.ora and listener.ora identical, but with no change.
    • I replaced the HOST of the listener with random garbage and then TNSPING failed, which it doesn't do otherwise.
    • I copied the tnsnames.ora.sample, initgd4odbc.ora, and listener.ora.sample files in hs\admin and made a test connection with no ODBC source at the end; but it fails in the same way, with no trace file generated.
    • I put random characters for the SID_NAME in listener.ora and the SID in CONNECT_DATA in tnsnames.ora and still get the same error, with seemingly no other impact.
  • gatesps
    gatesps Member Posts: 7
    edited Oct 18, 2019 5:06PM Accepted Answer

    Switched to 18c Express edition and everything seems to be working. I was probably looking at the wrong documentation to get it working in 12c.

  • mxallen-Oracle
    mxallen-Oracle Member Posts: 342 Employee
    edited Oct 21, 2019 8:08AM

    Good to hear.  The setup is the same across the board.  Id did not look like anything was incorrect.  It might have been an issue with the install itself, maybe related to the user starting different services (i.e. the listener for the gateway), or some other problem.

    Anyway, since it is working, if you can mark this thread as solved that would be great.

    Thanks!
    Matt

  • gatesps
    gatesps Member Posts: 7
    edited Oct 21, 2019 9:47AM

    Done! Thanks for the help!

Sign In or Register to comment.