Hi all!
Some time ago a client reported issues when integrating Oracle and PostgreSQL. The error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
No query has been executed with that handle;
Could not send Query(connection dead) {HY000,NativeErr = 1}
Braking down errors:
– Oracle: Connection from ORACLE to a non-Oracle system returned this message
– PostgreSQL: {HY000,NativeErr = 1} Could not send Query(connection dead)
Both are general errors:
– Oracle’s one is for any error returned by remote service, when using Oracle Heterogeneous Services (old Database Gateway).
– Postgre’s one is for connections ended.
I also found some other similar errors:
– Oracle: ORA-28511: lost RPC connection to heterogeneous remote agent using SID
– PostgreSQL: {08S01,NativeErr = 26} Error fetching next row
In summary, the root cause was a firewall configuration ending connection. But what I want to share with you the workaround. 🙂
You know that sometimes, due certain rules, firewall rules may require formal change requests and procedures, so what you can do on database side is basically disable parameter UseDeclareFetch (default is false) in ODBC descriptor, as per below.
#Original:
[oracle@server01 admin]$ cat /etc/odbc.ini [mydbpost] Driver = PostgreSQL Database = mydbpost Servername = 192.168.120.120 Port = 5432 ReadOnly = Yes Username = query_oracle Password = ******** UseDeclareFetch = 1 [oracle@server01 admin]$
# After:
[oracle@server01 admin]$ cat /etc/odbc.ini [mydbpost] Driver = PostgreSQL Database = mydbpost Servername = 192.168.120.120 Port = 5432 ReadOnly = Yes Username = query_oracle Password = ******** [oracle@server01 admin]$
From PostgreSQL.Org:
Use Declare/Fetch: If true, the driver automatically uses declare cursor/fetch to handle SELECT statements and keeps 100 rows in a cache.
This is mostly a great advantage, especially if you are only interested in reading and not updating. It results in the driver not sucking down lots of memory to buffer the entire result set. If set to false, cursors will not be used and the driver will retrieve the entire result set. For very large tables, this is very inefficient and may use up all the Windows memory/resources. However, it may handle updates better since the tables are not kept open, as they are when using cursors.
In summary, the DML transactions were taking to long, and with this performance improvement, the performance on DML has improved and timeouts stopped.
Hope it helps!
