Hi,
I am trying to configure a connection from Oracle RDBMS 12.1/12.2/19.1 to PostgreSQL 10.3/9.5 through a database link.
But it does not work.
Here are my configuration files:
1) odbc.ini:
Location: /home/oracle
Content:
---
[ODBC Data Sources]
PG = PostgreSQL
[PG]
Debug = 1
CommLog = 1
ReadOnly = no
Driver = /usr/lib64/psqlodbc.so
Servername = 192.168.1.105
FetchBufferSize = 99
Username = postgres
Password = postgres
Port = 5432
Database = pagila
[Default]
Driver = /usr/lib64/liboplodbcS.so.2
---
2) initPG.ora:
Location: /u01/oracle/base/product/rdbms/193/hs/admin
Content:
---
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL=ON
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set ODBCINI=/home/oracle/odbc.ini
---
3) tnsnames.ora:
Location: /u01/oracle/base/product/rdbms/193/network/admin
Content:
---
ANUBIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ANUBIS)
)
)
PG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))
(CONNECT_DATA =
(SID = PG)
)
(HS = OK)
)
LISTENER_ANUBIS =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))
---
4) listener.ora:
Location: /u01/oracle/base/product/rdbms/193/network/admin
Content:
---
# listener.ora Network Configuration File: /u01/oracle/base/product/rdbms/193/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ANUBIS)
(ORACLE_HOME = /u01/oracle/base/product/rdbms/193)
(SID_NAME = ANUBIS)
)
(SID_DESC =
(SID_NAME=PG)
(ORACLE_HOME=/u01/oracle/base/product/rdbms/193)
# PROGRAM = dg4odbc tells the listener the use the database gateway for odbc
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel764-odb19c-nopdb-copy-s1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
---
I have created a database link PG that points to tnsname PG (PostgreSQL pgila database as data source) and that connects trough the postgres user:
---
OWNER DB_LINK USERNAME HOST CREATED
------------ -------------------- --------------- ------------------------------ -------------------
SYS SYS_HUB SEEDDATA 17-APR-19
PUBLIC PG postgres PG 13-MAY-19
---
When I run this query:
select * from "public"."actor"@PG;
I get this error:
---
SQL> select * from "public"."actor"@PG;
select * from "public"."actor"@PG
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
---
When I look in the generated trace file I have:
---
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"
HOCXU_VC2_MAX=4000
HOCXU_RAW_MAX=2000
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_TRUNC_ANSI_DATE" returned "OFF"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using postgres as default schema
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
hgocont, line 2823: calling SqlDriverConnect got sqlstate I
HOA 05/14 08:49:37.834760000: (horcrces_CleanupExtprocSession) Entered!
HOA 05/14 08:49:37.834796000: (horcrpooe_PopOciEnv) Entered!
HOA 05/14 08:49:37.834802000: (horcrfoe_FreeOciEnv) Entered!
HOA 05/14 08:49:37.834809000: (horcrfoe_FreeOciEnv) Exiting...
HOA 05/14 08:49:37.834815000: (horcrfse_FreeStackElt) Entered!
HOA 05/14 08:49:37.834820000: (horcrfse_FreeStackElt) Exiting...
HOA 05/14 08:49:37.834826000: (horcrpooe_PopOciEnv) Exiting...
HOA 05/14 08:49:37.834832000: (horcrces_CleanupExtprocSession) Exiting...
---
Does someone know what the issue can be and where to investigate further?
Thanks by advance for any tip(s).
Kind Regards