Oracle->Mysql dblink 建立詳細過程
Oracle->Mysql dblink 建立詳細過程
環境：
192.168.9.146
mysql5.1.6
centOS6.4
192.168.9.235
oracle11.2.0
rhel6.4
*******************************************************************************
（一定要注意字符集）
*******************************************************************************
一. 在mysql端建立初始表
順帶好好弄一下字符集的問題。
[root@nagios ~]# export LANG=zh_CN.GB2312
[root@nagios ~]# echo $LANG
zh_CN.GB2312
[root@nagios ~]#
[root@nagios ~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 228939
Server version: 5.1.66-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
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> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
mysql> create database test character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.02 sec)
mysql> show create database test;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test;
Database changed
mysql> create table test (id varchar(255), name varchar(255)) ENGINE=innodb DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test value ("1", "測試");
Query OK, 1 row affected (0.03 sec)
mysql> insert into test select * from test;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
+------+------+
16 rows in set (0.00 sec)
mysql>
至此，目標庫和目標表都已建立完成！！
*******************************************************************************
二. 在oracle端對應配置。
1.檢查字元，然後裝 mysql-connector-odbc和unixODBC
[root@testdb1 ~]# export LANG=zh_CN.GB2312
[root@testdb1 ~]# echo $LANG
zh_CN.GB2312
安裝所需要的包。可以採用yum安裝
[root@testdb1 ~]#
[root@testdb1 ~]# rpm -qa | grep mysql-connector
mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
[root@testdb1 ~]# rpm -qa | grep ODBC
unixODBC-2.2.14-12.el6_3.x86_64
unixODBC-devel-2.2.14-12.el6_3.x86_64
[root@testdb1 ~]#
2.配置 /etc/odbc.ini
[root@testdb1 ~]# vi /etc/odbc.ini
[mysqlodbc]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.168.9.146
PORT = 3306
USER = root
Password = 123456
Database = test
OPTION = 3
SOCKET =
charset = gbk
STMT = SET NAMES 'gbk'
3.配置/etc/odbcinst.ini
[root@testdb1 ~]# vi /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1
~
4.測試連線
[root@testdb1 ~]# isql mysqlodbc -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from test.test limit 1;
+-------------+------------------------+
| id | name |
+-------------+------------------------+
| 1 | 測試 |
+-------------+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
5.配置oracle環境變數
[oracle@testdb1 ~]$ vi prof_testdb1
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=testdb1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib/$ORACLE_HOME/hs/lib:/usr/lib64:$LD_LIBRARY_PATH:
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI
~
最重要是後面幾個以及 LD_LIBRARY_PATH變數
6.配置監聽
[oracle@testdb1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdb1)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = testdb1)
)
(SID_DESC =
(GLOBAL_DBNAME = testdb3)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = testdb3)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = mysqlodbc)
(ENVS=LD_LIBRARY_PATH=/app/oracle/ora11g/lib:/lib:/usr/lib:/usr/local/lib//app/oracle/ora11g/hs/lib:/usr/lib64)
)
)
ADR_BASE_LISTENER = /app/oracle/ora11g
[oracle@testdb1 admin]$
[oracle@testdb1 admin]$ vi tnsnames.ora
mysqlodbc=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522))
(CONNECT_DATA =
(SID = mysqlodbc))
(HS = OK)
)
7.配置odbc監聽
路徑：$ORACLE_HOME/hs/admin
注意：名字要跟odbc配置的名字一樣 mysqlodbc
配置透明閘道器。
[oracle@testdb1 admin]:testdb1> vi initmysqlodbc.ora
HS_FDS_CONNECT_INFO = mysqlodbc
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.gbk
HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291
set DBCINI=/etc/odbc.ini
~
8.測試下監聽
[oracle@testdb1 admin]:testdb1> tnsping mysqlodbc
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-AUG-2015 11:32:43
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/app/oracle/ora11g/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522)) (CONNECT_DATA = (SID = mysqlodbc)) (HS = OK))
OK (110 msec)
[oracle@testdb1 admin]:testdb1>
9.建立dblink
SQL> create public database link mysqlodbc connect to "root" identified by "123456" using'mysqlodbc';
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
刪除dblink
SQL> drop public database link myodbc;
SQL> select count(*) from "test"@mysqlodbc;
COUNT(*)
----------
16
SQL>
常見報錯：
1.
SQL> select count(*) from "nagios_hosts"@myodbc;
select count(*) from "nagios_hosts"@myodbc
*
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=%s
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from MYODBC
Process ID: 23084
Session ID: 44 Serial number: 21157
解決辦法：
chown -R oracle:dba $ORACLE_HOME/bin
2.
SQL> select count(*) from "nagios_hosts"@myodbc;
select count(*) from "nagios_hosts"@myodbc
*
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=192.168.9.235)(PORT=1521))(CONNECT
_DATA=(SID=myodbc3)))
ORA-02063: preceding line from MYODBC
Process ID: 23084
Session ID: 44 Serial number: 21157
SQL>
這個報錯基本上都是透明閘道器的報錯，仔細檢查一下$ORACLE_HOME/hs/admin/initmysqlodbc.ora這個檔案裡面的引數。
3.
SQL> select * from "test"@mysqlodbc;
select * from "test"@mysqlodbc
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 30336
Session ID: 45 Serial number: 81
SQL>
檢視日誌：
[oracle@testdb1 trace]:testdb1> cd $ORACLE_BASE/diag/rdbms/testdb1/testdb1/trace
[oracle@testdb1 trace]:testdb1> tail -f alert_testdb1.log
Fri Aug 14 16:20:18 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x2ACDA6D, kpuexMaxColRowsize()+111] [flags: 0x0, count: 1]
Errors in file /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30593.trc (incident=36292):
ORA-07445: exception encountered: core dump [kpuexMaxColRowsize()+111] [SIGSEGV] [ADDR:0x6] [PC:0x2ACDA6D] [Address not mapped to object] []
Incident details in: /app/oracle/diag/rdbms/testdb1/testdb1/incident/incdir_36292/testdb1_ora_30593_i36292.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Aug 14 16:20:19 2015
Dumping diagnostic data in directory=[cdmp_20150814162019], requested by (instance=1, osid=30593), summary=[incident=36292].
Fri Aug 14 16:20:20 2015
Sweep [inc][36292]: completed
Sweep [inc2][36292]: completed
[oracle@testdb1 cdump]$ cat /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30297.trc
Trace file /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30297.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/ora11g
System name: Linux
Node name: testdb1
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: testdb1
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 30297, image: oracle@testdb1 (TNS V1-V3)
*** 2015-08-14 15:54:38.014
*** SESSION ID:(47.83) 2015-08-14 15:54:38.014
*** CLIENT ID:() 2015-08-14 15:54:38.014
*** SERVICE NAME:(SYS$USERS) 2015-08-14 15:54:38.014
*** MODULE NAME:(sqlplus@testdb1 (TNS V1-V3)) 2015-08-14 15:54:38.014
*** ACTION NAME:() 2015-08-14 15:54:38.014
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x2ACDA6D, kpuexMaxColRowsize()+111] [flags: 0x0, count: 1]
DDE: Problem Key 'ORA 7445 [kpuexMaxColRowsize()+111]' was flood controlled (0x2) (incident: 36290)
ORA-07445: exception encountered: core dump [kpuexMaxColRowsize()+111] [SIGSEGV] [ADDR:0x6] [PC:0x2ACDA6D] [Address not mapped to object] []
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
ksdbgcra: writing core file to directory '/app/oracle/diag/rdbms/testdb1/testdb1/cdump'
不懂解決，求大神留言
環境：
192.168.9.146
mysql5.1.6
centOS6.4
192.168.9.235
oracle11.2.0
rhel6.4
*******************************************************************************
（一定要注意字符集）
*******************************************************************************
一. 在mysql端建立初始表
順帶好好弄一下字符集的問題。
[root@nagios ~]# export LANG=zh_CN.GB2312
[root@nagios ~]# echo $LANG
zh_CN.GB2312
[root@nagios ~]#
[root@nagios ~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 228939
Server version: 5.1.66-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
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> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
mysql> create database test character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.02 sec)
mysql> show create database test;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test;
Database changed
mysql> create table test (id varchar(255), name varchar(255)) ENGINE=innodb DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test value ("1", "測試");
Query OK, 1 row affected (0.03 sec)
mysql> insert into test select * from test;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test select * from test;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
| 1 | 測試 |
+------+------+
16 rows in set (0.00 sec)
mysql>
至此，目標庫和目標表都已建立完成！！
*******************************************************************************
二. 在oracle端對應配置。
1.檢查字元，然後裝 mysql-connector-odbc和unixODBC
[root@testdb1 ~]# export LANG=zh_CN.GB2312
[root@testdb1 ~]# echo $LANG
zh_CN.GB2312
安裝所需要的包。可以採用yum安裝
[root@testdb1 ~]#
[root@testdb1 ~]# rpm -qa | grep mysql-connector
mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
[root@testdb1 ~]# rpm -qa | grep ODBC
unixODBC-2.2.14-12.el6_3.x86_64
unixODBC-devel-2.2.14-12.el6_3.x86_64
[root@testdb1 ~]#
2.配置 /etc/odbc.ini
[root@testdb1 ~]# vi /etc/odbc.ini
[mysqlodbc]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.168.9.146
PORT = 3306
USER = root
Password = 123456
Database = test
OPTION = 3
SOCKET =
charset = gbk
STMT = SET NAMES 'gbk'
3.配置/etc/odbcinst.ini
[root@testdb1 ~]# vi /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1
~
4.測試連線
[root@testdb1 ~]# isql mysqlodbc -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from test.test limit 1;
+-------------+------------------------+
| id | name |
+-------------+------------------------+
| 1 | 測試 |
+-------------+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
5.配置oracle環境變數
[oracle@testdb1 ~]$ vi prof_testdb1
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=testdb1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib/$ORACLE_HOME/hs/lib:/usr/lib64:$LD_LIBRARY_PATH:
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI
~
最重要是後面幾個以及 LD_LIBRARY_PATH變數
6.配置監聽
[oracle@testdb1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdb1)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = testdb1)
)
(SID_DESC =
(GLOBAL_DBNAME = testdb3)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = testdb3)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /app/oracle/ora11g)
(SID_NAME = mysqlodbc)
(ENVS=LD_LIBRARY_PATH=/app/oracle/ora11g/lib:/lib:/usr/lib:/usr/local/lib//app/oracle/ora11g/hs/lib:/usr/lib64)
)
)
ADR_BASE_LISTENER = /app/oracle/ora11g
[oracle@testdb1 admin]$
[oracle@testdb1 admin]$ vi tnsnames.ora
mysqlodbc=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522))
(CONNECT_DATA =
(SID = mysqlodbc))
(HS = OK)
)
7.配置odbc監聽
路徑：$ORACLE_HOME/hs/admin
注意：名字要跟odbc配置的名字一樣 mysqlodbc
配置透明閘道器。
[oracle@testdb1 admin]:testdb1> vi initmysqlodbc.ora
HS_FDS_CONNECT_INFO = mysqlodbc
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.gbk
HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291
set DBCINI=/etc/odbc.ini
~
8.測試下監聽
[oracle@testdb1 admin]:testdb1> tnsping mysqlodbc
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-AUG-2015 11:32:43
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/app/oracle/ora11g/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.235)(PORT = 1522)) (CONNECT_DATA = (SID = mysqlodbc)) (HS = OK))
OK (110 msec)
[oracle@testdb1 admin]:testdb1>
9.建立dblink
SQL> create public database link mysqlodbc connect to "root" identified by "123456" using'mysqlodbc';
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
刪除dblink
SQL> drop public database link myodbc;
SQL> select count(*) from "test"@mysqlodbc;
COUNT(*)
----------
16
SQL>
常見報錯：
1.
SQL> select count(*) from "nagios_hosts"@myodbc;
select count(*) from "nagios_hosts"@myodbc
*
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=%s
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from MYODBC
Process ID: 23084
Session ID: 44 Serial number: 21157
解決辦法：
chown -R oracle:dba $ORACLE_HOME/bin
2.
SQL> select count(*) from "nagios_hosts"@myodbc;
select count(*) from "nagios_hosts"@myodbc
*
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=192.168.9.235)(PORT=1521))(CONNECT
_DATA=(SID=myodbc3)))
ORA-02063: preceding line from MYODBC
Process ID: 23084
Session ID: 44 Serial number: 21157
SQL>
這個報錯基本上都是透明閘道器的報錯，仔細檢查一下$ORACLE_HOME/hs/admin/initmysqlodbc.ora這個檔案裡面的引數。
3.
SQL> select * from "test"@mysqlodbc;
select * from "test"@mysqlodbc
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 30336
Session ID: 45 Serial number: 81
SQL>
檢視日誌：
[oracle@testdb1 trace]:testdb1> cd $ORACLE_BASE/diag/rdbms/testdb1/testdb1/trace
[oracle@testdb1 trace]:testdb1> tail -f alert_testdb1.log
Fri Aug 14 16:20:18 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x2ACDA6D, kpuexMaxColRowsize()+111] [flags: 0x0, count: 1]
Errors in file /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30593.trc (incident=36292):
ORA-07445: exception encountered: core dump [kpuexMaxColRowsize()+111] [SIGSEGV] [ADDR:0x6] [PC:0x2ACDA6D] [Address not mapped to object] []
Incident details in: /app/oracle/diag/rdbms/testdb1/testdb1/incident/incdir_36292/testdb1_ora_30593_i36292.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Aug 14 16:20:19 2015
Dumping diagnostic data in directory=[cdmp_20150814162019], requested by (instance=1, osid=30593), summary=[incident=36292].
Fri Aug 14 16:20:20 2015
Sweep [inc][36292]: completed
Sweep [inc2][36292]: completed
[oracle@testdb1 cdump]$ cat /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30297.trc
Trace file /app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30297.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/ora11g
System name: Linux
Node name: testdb1
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: testdb1
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 30297, image: oracle@testdb1 (TNS V1-V3)
*** 2015-08-14 15:54:38.014
*** SESSION ID:(47.83) 2015-08-14 15:54:38.014
*** CLIENT ID:() 2015-08-14 15:54:38.014
*** SERVICE NAME:(SYS$USERS) 2015-08-14 15:54:38.014
*** MODULE NAME:(sqlplus@testdb1 (TNS V1-V3)) 2015-08-14 15:54:38.014
*** ACTION NAME:() 2015-08-14 15:54:38.014
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x2ACDA6D, kpuexMaxColRowsize()+111] [flags: 0x0, count: 1]
DDE: Problem Key 'ORA 7445 [kpuexMaxColRowsize()+111]' was flood controlled (0x2) (incident: 36290)
ORA-07445: exception encountered: core dump [kpuexMaxColRowsize()+111] [SIGSEGV] [ADDR:0x6] [PC:0x2ACDA6D] [Address not mapped to object] []
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
ksdbgcra: writing core file to directory '/app/oracle/diag/rdbms/testdb1/testdb1/cdump'
不懂解決，求大神留言
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1771714/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL MHA詳細搭建過程MySql
- oracle 11G RAC 建立詳細過程Oracle
- MySQL MGR單主模式詳細搭建過程MySql模式
- mysql5.7.23安裝詳細過程MySql
- Oracle建立dblink MySQLOracleMySql
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- MySQL主從複製的詳細過程介紹MySql
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- SQL Server建立dblink至MySQLServerMySql
- mysql建立master/slave詳細步驟MySqlAST
- 原始碼編譯安裝MySQL5.6.12詳細過程原始碼編譯MySql
- MySQL 使用mysqld_multi部署單機多例項詳細過程MySql
- MySQL 利用xtrabackup進行增量備份詳細過程彙總MySql
- 泊松過程的詳細理解
- nginx配置https詳細過程NginxHTTP
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- MySQL 建立儲存過程報錯MySql儲存過程
- Oracle-解析啟動的全過程Oracle
- mysql儲存過程詳解MySql儲存過程
- 深入mysql建立自定義函式與儲存過程的詳解MySql函式儲存過程
- Mybatis詳解(二) sqlsession的建立過程MyBatisSQLSession
- MySQL儲存過程的建立和使用MySql儲存過程
- 新手搭建雲伺服器詳細過程伺服器
- ESXI 6.7 系統安裝詳細過程
- Webpack安裝配置及打包詳細過程Web
- centos7安裝的詳細過程CentOS
- 詳細瞭解 synchronized 鎖升級過程synchronized
- 原創：oracle 授權的詳細過程Oracle
- Hadoop2.3.0詳細安裝過程Hadoop
- ubuntu系統安裝mysql並支援遠端連線的詳細過程UbuntuMySql
- MySQL 5.5 建立儲存過程和函式MySql儲存過程函式
- 刪除私有dblink的儲存過程儲存過程
- vue原始碼解析-圖解diff詳細過程Vue原始碼圖解
- 超詳細講解頁面載入過程
- Https原理解析及詳細推演過程HTTP
- linux下cacti的搭建之詳細過程！Linux
- Oracle 11G DataGuard重啟詳細過程Oracle