Problem with dblink between Oracle and MySQL.
I recently created a dblink between an Oracle Databse 11.2.0.3 and MySQL database (Part of Bitnami's Redmine don't know the release).
So i created and odbc entry:
[SAR]
Driver = /home/oracle/mysqlodbc/lib/libmyodbc5.so
SERVER = IP
UID = username
PWD = pass
DATABASE = dbname
PORT = 3306
trace=1
HS_LANGUAGE=american_america.we8iso8859P1
And i can connect to it:
[oracle@dsbd01 ~]$ isql SAR
---------------------------------------
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
---------------------------------------
SQL>
So i make an initSAR.ora file in $ORACLE_HOME/hs/admin
HS_FDS_CONNECT_INFO = SAR
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /home/oracle/mysqlodbc/lib/libmyodbc5.so
HS_LANGUAGE=american_america.we8iso8859P1
set ODBCINI=/etc/odbc.ini
a listener.ora entry:
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=SAR)
(ORACLE_HOME=/u02/oracle/TEST/db/tech_st/11.1.0)
(PROGRAM=dg4odbc)
)
)
and a tnsnames.ora
SAR =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA=
(SID=SAR))
(HS=OK)
)
and my sqlnet.ora look like:
NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
#SQLNET.EXPIRE_TIME= 10
SQLNET.INBOUND_CONNECT_TIMEOUT=0
DIAG_ADR_ENABLED=ON
ADR_BASE=/u02/oracle/TEST/db/tech_st/11.1.0/admin/TEST_dsbd01
SEC_USER_AUDIT_ACTION_BANNER = /u02/oracle/TEST/db/tech_st/11.1.0/appsutil/template/txkDBSecUserAuditActionBanner.txt
IFILE=/u02/oracle/TEST/db/tech_st/11.1.0/network/admin/TEST_dsbd01/sqlnet_ifile.ora
So when i try to make a query using this dblink i got this error:
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DAT
A=(SID=SAR)))
ORA-02063: preceding line from LK_REDMINE_SAR
Process ID: 1336
Session ID: 300 Serial number: 12975
I look in the database trace file but i can't find so much information:
*** 2013-01-30 12:01:39.804
HS: RPC error reported on server, DescribeTable: ncrorou_recv_out_args; NCR code 1003
*** 2013-01-30 12:01:39.804
HS: Lost RPC connection to remote Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=SAR))), NCR status = -2147385341
*** 2013-01-30 12:05:39.050
HS: RPC error reported on server, DescribeTable: ncrorou_recv_out_args; NCR code 1003
*** 2013-01-30 12:05:39.051
HS: Lost RPC connection to remote Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=SAR))), NCR status = -2147385341
And this is the trace for the HS:
Oracle Corporation --- WEDNESDAY JAN 30 2013 12:05:29.663
Heterogeneous Agent Release
11.1.0.7.0
Oracle Corporation --- WEDNESDAY JAN 30 2013 12:05:29.662
Version 11.1.0.7.0
Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "4"
Entered hgosdip
setting HS_OPEN_CURSORS to default of 50
setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
setting HS_FDS_RECOVERY_PWD to default value
setting HS_FDS_TRANSACTION_LOG to default of "HS_TRANSACTION_LOG"
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
setting HS_NLS_NCHAR to default of "AL32UTF8"
setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
setting HS_RPC_FETCH_REBLOCKING to default of "ON"
setting HS_FDS_FETCH_ROWS to default of "100"
setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
setting HS_FDS_MAP_NCHAR to default of "TRUE"
setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
setting HS_FDS_QUERY_DRIVER to default of "TRUE"
setting HS_FDS_SUPPORT_STATISTICS to default of "TRUE"
Parameter HS_FDS_QUOTE_IDENTIFIER is not set
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
Exiting hgosdip, rc=0
ORACLE_SID is "SAR"
Product-Info:
Port Rls/Upd:7/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.1.0.7.0_0006, Instance:SAR
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=31
HOCXU_SEM_VER=110000
Entered hgolofn at 2013/01/30-12:05:29
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/home/oracle/mysqlodbc/lib/libmyodbc5.so"
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLAllocHandle
symbol_peflctx=0xaab1b602
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLBindCol
symbol_peflctx=0xaab21670
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLBindParameter
symbol_peflctx=0xaab1f4bd
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLCancel
symbol_peflctx=0xaab1855a
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLDescribeParam
symbol_peflctx=0xaab1f040
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLDisconnect
symbol_peflctx=0xaab12bef
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLEndTran
symbol_peflctx=0xaab22470
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLExecute
symbol_peflctx=0xaab1a9d9
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLFetch
symbol_peflctx=0xaab215ce
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLFreeHandle
symbol_peflctx=0xaab1b535
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLFreeStmt
symbol_peflctx=0xaab1afcd
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetData
symbol_peflctx=0xaab2077f
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetEnvAttr
symbol_peflctx=0xaab1d9e0
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetFunctions
symbol_peflctx=0xaab1b6e0
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLMoreResults
symbol_peflctx=0xaab2098c
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLNumResultCols
symbol_peflctx=0xaab2221f
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLParamData
symbol_peflctx=0xaab1a18c
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLPutData
symbol_peflctx=0xaab1869b
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLRowCount
symbol_peflctx=0xaab20959
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLSetEnvAttr
symbol_peflctx=0xaab1da41
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLSetDescRec
symbol_peflctx=0xaab0abab
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLColAttribute
symbol_peflctx=0xaab0ceb0
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLColumns
symbol_peflctx=0xaab0c9b8
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLConnect
symbol_peflctx=0xaab0c8c3
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLDescribeCol
symbol_peflctx=0xaab0c753
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLDriverConnect
symbol_peflctx=0xaab0c5cb
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLExecDirect
symbol_peflctx=0xaab0c59e
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLForeignKeys
symbol_peflctx=0xaab0c312
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetConnectAttr
symbol_peflctx=0xaab0c306
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetDiagField
symbol_peflctx=0xaab0befc
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetDiagRec
symbol_peflctx=0xaab0be0d
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetInfo
symbol_peflctx=0xaab0baa9
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetStmtAttr
symbol_peflctx=0xaab0ba9d
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLGetTypeInfo
symbol_peflctx=0xaab0ba8e
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLPrepare
symbol_peflctx=0xaab0ba03
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLPrimaryKeys
symbol_peflctx=0xaab0b7b1
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLProcedureColumns
symbol_peflctx=0xaab0b5ed
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLProcedures
symbol_peflctx=0xaab0b477
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLSetConnectAttr
symbol_peflctx=0xaab0b46b
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLSetStmtAttr
symbol_peflctx=0xaab0b291
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLSetDescField
symbol_peflctx=0xaab0abb6
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLStatistics
symbol_peflctx=0xaab0af55
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Entered hgolofns at 2013/01/30-12:05:29
libname=/home/oracle/mysqlodbc/lib/libmyodbc5.so, funcname=SQLTables
symbol_peflctx=0xaab0abda
hoaerr:0
Exiting hgolofns at 2013/01/30-12:05:29
Exiting hgolofn, rc=0 at 2013/01/30-12:05:29
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_CHARACTER" returned ".,"
Exiting hgoinit, rc=0 at 2013/01/30-12:05:29
Entered hgolgon at 2013/01/30-12:05:29
reco:0, name:consulta, tflag:0
Entered hgosuec at 2013/01/30-12:05:29
Exiting hgosuec, rc=0 at 2013/01/30-12:05:29
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using consulta as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2013/01/30-12:05:29
HS_FDS_CONNECT_INFO = "SAR"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2013/01/30-12:05:29
dsn:SAR, name:consulta
optn:
##>Connect Parameters (len=33)<##
## DSN=SAR;
#! UID=username;
#! PWD=*
Exiting hgogenconstr, rc=0 at 2013/01/30-12:05:29
DriverName:libmyodbc5.so, DriverVer:05.01.0011
DBMS Name:MySQL, DBMS Version:5.0.83-community-log
Exiting hgocont, rc=0 at 2013/01/30-12:05:34
SQLGetInfo returns Y for SQL_CATALOG_NAME
SQLGetInfo returns 192 for SQL_MAX_CATALOG_NAME_LEN
Exiting hgolgon, rc=0 at 2013/01/30-12:05:34
Entered hgoulcp at 2013/01/30-12:05:34
Entered hgowlst at 2013/01/30-12:05:34
Exiting hgowlst, rc=0 at 2013/01/30-12:05:34
SQLGetInfo returns ` for SQL_IDENTIFIER_QUOTE_CHAR
SQLGetInfo returns Y for SQL_COLUMN_ALIAS
2 instance capabilities will be uploaded
capno:1991, context:0x0001ffff, add-info: 0
capno:1992, context:0x0001ffff, add-info: 1, translation:"`"
Exiting hgoulcp, rc=0 at 2013/01/30-12:05:34
Entered hgouldt at 2013/01/30-12:05:34
0 instance DD translations were uploaded
Exiting hgouldt, rc=0 at 2013/01/30-12:05:34
Entered hgobegn at 2013/01/30-12:05:34
tflag:0 , initial:1
hoi:0xc1104908, ttid (len 46) is ...
00: 54455354 2E434F52 504C4946 452E434F
10: 52504752 4F55502E 434C2E35 33616530
20: 6263372E 362E3136 2E373030 3130
tbid (len 43) is ...
00: 54455354 2E434F52 504C4946 452E434F
10: 52504752 4F55502E 434C5B36 2E31362E ]
20: 37303031 305D5B31 2E325D
TXN Capable:3, Isolation Option:0xf
Exiting hgobegn, rc=0 at 2013/01/30-12:05:34
Entered hgodtab at 2013/01/30-12:05:34
count:1
table: ecpg_test
Anyone can help me to find the error?
Thanks.