oracle通过DBlink连接mysql(MariaDB)
1.安装先装 mysql-connector-odbc(或 mariadb-connector-odbc )和unixODBC
https://downloads.mariadb.org/mariadb/repositories/#mirror=opencas&distro=CentOS&distro_release=centos7-amd64--centos7&version=10.0
https://downloads.mariadb.org/connector-odbc/2.0.10/#mirror=biznet
MariaDB [(none)]> show engines; MariaDB [(none)]> install plugin connect soname 'ha_connect.so'; yum install unixODBC unixODBC-devel
2.配置 /etc/odbc.ini
[myodbc3]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.1.1.200
PORT = 3306
USER = ods_user
Password = 123456
Database = mysql
OPTION = 3
SOCKET =
charset = utf8
3.配置/etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1
4.测试连接
[root@rac1 ~]# isql myodbc3 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
5.配置oracle环境变量
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/grid
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=rac1
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH:/usr/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI
6.配置监听
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(PROGRAM = dg4odbc)
(SID_NAME= myodbc3)
(ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib:/usr/lib64)
)
)
tnsname.ora:
myodbc3=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
(CONNECT_DATA =
(SID = myodbc3))
(HS = OK)
)
7.配置odbc监听
路径:$ORACLE_HOME/hs/admin
注意:名字要跟odbc配置的名字一样 我这里是myodbc3
cat initmyodbc3.ora
HS_FDS_CONNECT_INFO = myodbc3
HS_FDS_TRACE_LEVEL = ON
HS_FDS_TRACE_FILE_NAME = odbc_test.log
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISCTICS = FALSE
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SQLLEN_INTERPRETATION=32
set ODBCINI = /etc/odbc.ini
8.测试下监听
tnsping myodbc3
9.创建dblink
SQL>create public database link myodbc connect to "ods_user" identified by "123456" using'myodbc3';
SQL> select count(*) from "t_user"@myodbc;
参考资料:
http://www.tuicool.com/articles/V3qYrme
http://blog.itpub.net/28321441/viewspace-750732/
http://www.server110.com/mariadb/201404/10139.html
相关文章
- MySQL 视图
- MYSQL 命令大全
- MySQL DML 整理
- MySQL JOIN操作报错问题小解
- Oracle数据库刷题笔记,mysql刷题笔记
- Oracle数据库:oracle数据表格dmp,sql,pde格式导入与导出,视图、序列、索引等对象的导出,oracle完结,后续开启mysql的学习
- 在Oracle专家眼中,MySQL sys Schema是怎样一种存在?
- 从Oracle迁移到Mysql之前必须知道的50件事
- 【转】101个MySQL调试和优化技巧
- 面试必问:mysql四种事物隔离级别解读
- 【Docker】安装Presto连接Hive、mysql、oracle、postgresql、SQL server等7种类型数据库
- 【docker】docker_dbclient(支持mysql、oracle、postgres、sqlserver客户端)
- PHP同时操作两个mysql数据库
- Navicat工具mysql转库oracle步骤
- mysql中实现oracle中的rowid功能
- Oracle与MySQL的区别
- 转 mysql spool_用MySQL实现类似Oracle SPOOL的功能
- 转 [MySQL 5.6] Innodb 新的监控表 INNODB_METRICS
- MySQL权限管理分配
- MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
- MySQL约束和事务知识归纳。
- MySQL主从复制状态及数据一致性监测工具
- 迁移mysql数据到oracle上
- mysql lower,upper实现大小写
- 【Unity3D进阶4-14】Unity3D 连接MySQL数据库
- Mysql学习---面试基础知识点总结
- Mysql学习---SQL语言的四大分类
- Sqoop是一款开源的工具,主要用于在HADOOP(Hive)与传统的数据库(mysql、oracle...)间进行数据的传递
- Mysql、Oracle 中的日期格式化比较
- MySQL基础
- 从Oracle转到Mysql前需了解的50件事
- MySql - JdbcType - Oracle类型映射
- SpringMvc+Mybatis+Maven+Mysql做一个CRUD的简单例子
- spring boot多数据源配置(mysql,redis,mongodb)实战
- 不同数据库、不同数据表进行数据同步,带有日志记录、数据校验、全量同步、增量同步、数据量自定义功能,满足mysql、oracle等主流数据库进行跨库数据传输、备份、同步。