Investigating an "ORA 02063" DG4ODBC Error
Recently, a customer who was using our SQL Server ODBC driver to connect Oracle to SQL Server, reported the following error to us:
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 SQLSERVERLINK
This "catchall" error can happen if:
- The environment is not set correctly (e.g. LD_LIBRARY_PATH does not point to the unixODBC library directories, or ODBCSYSINI does not point to the to the directory containing the copy of odbc.ini where the target ODBC DSN is defined.)
- A 64-bit DG4ODBC library is being used with 32-bit ODBC libraries and vice versa.
- The SID specified in your DG4ODBC config is not running on the host specified in tnsnames.ora.
However upon investigation none of these issues applied. We suspected the cause was an Oracle misconfiguration issue, because although DG4ODBC debugging was enabled, no DG4ODBC debug files were being generated i.e. Oracle was not getting as far as loading the DG4ODBC library.
In such cases, we request the customer's Oracle config files, so we can reproduce their setup, as it can be hard to spot a missing or misplaced bracket in a .ora file.
We were unable to reproduce the customer's error, the supplied config files worked perfectly for us.
The next step was to use strace to look "under the hood" at what config files were being loaded when the Oracle listener was being started. To do this, we asked the customer to:
- Start two shell sessions as the Oracle user.
- In shell 1, stop the Oracle listener.
- Start the listener with this command:
strace -f -o /tmp/easysoft.log -s 512 lsnrctl start
- In shell 2, start SQL*PLus and run a SQL statement against the DG4ODBC / SQL Server database link.
- In shell 2, stop the Oracle listener.
The strace log, /tmp/easysoft.log, exposed the underlying problem. Initially, the Oracle listener was able to load and read listener.ora:
53049 open("/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora", O_RDONLY) = 3
53049 read(3, "#/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora Network Configuration File:
\n# Generated by Oracle configuration tools.\n\nLISTENER =\n (DESCRIPTION_LIST =\n (DESCRIPTION =\n (ADDRESS
= (PROTOCOL = TCP)..., 4096) = 577
However, the customer's Oracle setup was: user A started the listener, which became user B. What strace revealed was that user B did not have sufficient access permissions to load that .ora file:
53051 open("/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora", O_RDONLY) = -1
EACCES (Permission denied)
Ultimately, this was the cause of the customer's "ORA 02063".
