7 Replies Latest reply on Oct 21, 2019 1:47 PM by gatesps

    (ODBC) Oracle not reading initSID.ora

    gatesps

      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 agent

      Unable to retrieve text of NETWORK/NCR message 65535

      ORA-02063: preceding 2 lines from EXTLINK

      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.

      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:

      # Windows

      HS_FDS_CONNECT_INFO = EXTLINK

      HS_FDS_TRACE_LEVEL = 255

      HS_FDS_FETCH_ROWS=1

      HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL

      #HS_NLS_NCHAR=UCS2

      HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

       

      Connect string:

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

        • 1. Re: (ODBC) Oracle not reading initSID.ora
          mxallen-Oracle

          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

          • 2. Re: (ODBC) Oracle not reading initSID.ora
            gatesps
            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:49

             

             

            Copyright (c) 1991, 2016, Oracle.  All rights reserved.

             

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

            STATUS of the LISTENER

            ------------------------

            Alias                    LISTENER

            Version                  TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production

            Start Date                15-OCT-2019 10:15:40

            Uptime                    0 days 3 hr. 11 min. 12 sec

            Trace Level              off

            Security                  ON: Local OS Authentication

            SNMP                      OFF

            Listener Parameter File  C:\app\[MYUSERNAME]\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

            Listener Log File        C:\app\[MYUSERNAME]\virtual\diag\tnslsnr\[HOSTNAME]\listener\alert\log.xml

            Listening 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

            • 3. Re: (ODBC) Oracle not reading initSID.ora
              mxallen-Oracle

              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

              • 4. Re: (ODBC) Oracle not reading initSID.ora
                gatesps

                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.
                • 5. Re: (ODBC) Oracle not reading initSID.ora
                  gatesps

                  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.

                  • 6. Re: (ODBC) Oracle not reading initSID.ora
                    mxallen-Oracle

                    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

                    • 7. Re: (ODBC) Oracle not reading initSID.ora
                      gatesps

                      Done! Thanks for the help!