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博客 ” ，链接：https://blog.itpub.net/29500582/viewspace-1771714/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
DG 创建
请登录后发表评论
登录
全部评论
