dblink issues : Unable to retrieve text of NETWORK/NCR message 65535

December 10, 2015 · by dbversity · in MySQL, Oracle DB

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] cat listener.ora
# listener.ora Network Configuration File: /optware/oracle/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

CLOUDAPP =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbversitydotcom)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /optware/oracle/12.1.0.2/db_1)
(SID_NAME = CLOUDAPP)
)
(SID_DESC =
(ORACLE_HOME = /optware/oracle/12.1.0.2/db_1)
(SID_NAME = mysql)
(PROGRAM = extproc)
(ENVS = “LD_LIBRARY_PATH=/usr/lib64:/optware/oracle/mysql515/lib:/usr/lib:$ORACLE_HOME/lib”)

))
MYSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = dbversitydotcom)
(PORT = 1522))
)
)

CONNECT_TIMEOUT_MYSQLSERVERLSNR = 0

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] cat tnsnames.ora
# tnsnames.ora Network Configuration File: /optware/oracle/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_CLOUDAPP =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbversitydotcom)(PORT = 1522))
CLOUDAPP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbversitydotcom)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = CLOUDAPP)
)
)
MYSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbversitydotcom)(PORT = 1522))
(CONNECT_DATA=(SID = mysql))
(HS = OK)
)
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] env | grep ora
OLDPWD=/optware/oracle/12.1.0.2/db_1/hs/admin
USER=oracle
LD_LIBRARY_PATH=/optware/oracle/12.1.0.2/db_1/lib
ORACLE_BASE=/optware/oracle
PATH=/optware/oracle/12.1.0.2/db_1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:.
MAIL=/var/spool/mail/oracle
PWD=/optware/oracle/12.1.0.2/db_1/network/admin
HOME=/optware/oracle
LOGNAME=oracle
CLASSPATH=/var/lib/pgsql/9.4/data/mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar:/optware/oracle/12.1.0.2/db_1/JRE:/optware/oracle/12.1.0.2/db_1/jlib:/optware/oracle/12.1.0.2/db_1/rdbms/jlib
ORACLE_HOME=/optware/oracle/12.1.0.2/db_1
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] sqlplus sys@cloudapp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 10 06:30:53 2015

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

SQL> sho parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string LISTENER_CLOUDAPP
parallel_force_local boolean FALSE
SQL>
SQL> sho parameter port

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_adaptive_reporting_only boolean FALSE
redo_transport_user string
SQL>
SQL>
SQL> select * from global_name;

GLOBAL_NAME
——————————————————————————–
MSTDB.NAM.NSROOT.NET

SQL>
SQL>

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.6.25-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

mysql> create database dbversity character set latin1;
Query OK, 1 row affected (0.01 sec)

mysql> grant all privileges on dbversity.* to ‘dbversity’@’localhost’
-> identified by ‘dbversity’ with grant option;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] mysql -udbversity -pdbversity -Ddbversity
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.6.25-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> create table dbversity (
-> col1 integer,
-> col2 date,
-> col3 varchar(10),
-> col4 varchar(10) character set utf8,
-> col5 varbinary(10)) engine innodb;
Query OK, 0 rows affected (0.21 sec)

mysql>
mysql>
mysql> insert into dbversity(col1, col2, col3, col4, col5)
-> values(1, cast(now() as date), ‘0123456789’, ‘0123456789’, ‘0123456789’);
Query OK, 1 row affected (0.07 sec)

mysql>
mysql>
mysql> select * from dbversity \G
*************************** 1. row ***************************
col1: 1
col2: 2015-12-09
col3: 0123456789
col4: 0123456789
col5: 0123456789
1 row in set (0.00 sec)

mysql>
mysql>
mysql> ^DBye
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] cat /etc/odbc.ini
[mysqldsn]
Driver = /usr/lib64/libmyodbc5w.so
SERVER = localhost
PORT = 3306
DATABASE = test
OPTION = 3
USER =
PASSWORD =

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] cat /etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
UsageCount = 2

[MySQL]
Description = MySQL driver for Plesk
Driver = /usr/lib64/libmyodbc5.so
Setup =
FileUsage = 1
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 =
UsageCount = 2

[MySQL ODBC 5.3 Unicode Driver]
Driver = /usr/lib64/libmyodbc5w.so
UsageCount = 2

[MySQL ODBC 5.3 ANSI Driver]
Driver = /usr/lib64/libmyodbc5a.so
UsageCount = 2

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] vi ~oracle/.odbc.ini

[ODBC Data Sources]
dbversity = MySQL ODBC Driver 5.6.25

[dbversity]
Driver = /usr/lib64/libmyodbc5.so
DATABASE = dbversity
DESCRIPTION = MySQL ODBC 5.6.25 Connector Sample
PORT = 3306
SERVER = 127.0.0.1
UID = dbversity
PWD = dbversity
CHARSET = latin1
TRACEFILE = /tmp/myodbc-dbversitydsn.trc
TRACE = OFF
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
“~/.odbc.ini” [New] 15L, 328C written
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] cat /optware/oracle/.odbc.ini

[ODBC Data Sources]
dbversity = MySQL ODBC Driver 5.6.25

[dbversity]
Driver = /usr/lib64/libmyodbc5.so
DATABASE = dbversity
DESCRIPTION = MySQL ODBC 5.6.25 Connector Sample
PORT = 3306
SERVER = 127.0.0.1
UID = dbversity
PWD = dbversity
CHARSET = latin1
TRACEFILE = /tmp/myodbc-dbversitydsn.trc
TRACE = OFF
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] cat /optware/oracle/12.1.0.2/db_1/hs/admin/initmysql.ora
cat: /optware/oracle/12.1.0.2/db_1/hs/admin/initmysql.ora: No such file or directory
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] vi /optware/oracle/12.1.0.2/db_1/hs/admin/initmysql.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=dbversity
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
# HS_NLS_NCHAR=AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/optware/oracle/.odbc.ini
set LD_LIBRARY_PATH=/optware/oracle/12.1.0.2/db_1/lib

#
# Environment variables required for the non-Oracle system
#
set HOME=/optware/oracle
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
“hs/admin/initmysql.ora” [New] 18L, 408C written

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] mkdir /optware/oracle/mysql515
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] mkdir /optware/oracle/mysql515/lib
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] cp /optware/oracle/12.1.0.2/db_1/lib /optware/oracle/mysql515/lib
cp: omitting directory `/optware/oracle/12.1.0.2/db_1/lib’
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] cp -R /optware/oracle/12.1.0.2/db_1/lib /optware/oracle/mysql515/lib
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ] cp /usr/lib64/libmyodbc5.so /optware/oracle/mysql515/lib
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1 ]

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 9 05:51:32 2015

SQL>
SQL>
SQL>
SQL>
SQL> create database link mysql
connect to “dbversity”
identified by “dbversity”
using ‘mysql’; 2 3 4

Database link created.

SQL>
SQL>
SQL> desc dba_db_links
Name Null? Type
—————————————– ——– —————————-
OWNER NOT NULL VARCHAR2(128)
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(128)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE

SQL> select DB_LINK from dba_db_links;

DB_LINK
——————————————————————————–
MYSQL.NAM.NSROOT.NET

SQL> select * from dba_db_links;

OWNER
——————————————————————————–
DB_LINK
——————————————————————————–
USERNAME
——————————————————————————–
HOST
——————————————————————————–
CREATED
———
SYS
MYSQL.NAM.NSROOT.NET
dbversity

OWNER
——————————————————————————–
DB_LINK
——————————————————————————–
USERNAME
——————————————————————————–
HOST
——————————————————————————–
CREATED
———
mysql
09-DEC-15

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] tnsping MYSQL

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 09-DEC-2015 07:00:24

Used parameter files:
/optware/oracle/12.1.0.2/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1522))) (CONNECT_DATA = (SID = mysql)) (HS = OK))
OK (0 msec)
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] lsnrctl reload cloudapp

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 09-DEC-2015 07:01:21

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbversitydotcom.nam.nsroot.net)(PORT=1522)))
The command completed successfully
[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ]

[ oracle @ dbversitydotcom : ~/12.1.0.2/db_1/network/admin ] sqlplus sys@cloudapp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 10 06:30:53 2015

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

SQL>
SQL>
SQL> select “col3” from “dbversity”@mysql;
select “col3” from “dbversity”@mysql
*
ERROR at line 1:
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 MYSQL

Tags: dblink issues : Unable to retrieve text of NETWORK/NCR message 65535

dblink issues : Unable to retrieve text of NETWORK/NCR message 65535

Leave a Reply

Categories

Categories