oracle通过DBlink连接oracle

1.1赋权（将crh_snp用户赋权具体创建dblink权限）　

在sys用户下，把CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK权限授予给你的用户

　　grant CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK to crh_snp;

1.2具体创建db_link

然后以crh_snp用户登录本地数据库,执行以下语名

　create database linkdcrac2

　　connect to wlyxpt identified by wlyxpt

　　using '(DESCRIPTION =

(ADDRESS_LIST =

(address = (protocol = tcp)(host = 192.19.0.81)(port = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dcrac)

(INSTANCE_NAME = dcrac2)

(failover_mode =

(backup = dcrac1)

(type = select)

(method = preconnect)

)

)

)';

---说明：本次创建的db_link名称为：dcrac2,对方oracle数据库的用户名为wlyxpt，密码--wlyxpt,服务名为dcrac，由于是rac数据库该兰色tnsname配置可由券商相关dba给出

1.3以crh_snp查询ods_ufs.his_his_delive表数据（obs所属表，wlyxpt用户有相应权限）

select * fromods_ufs.his_his_deliver@dcrac2;

2、oracle通过DBlink连接mysql

oracle：系统redhat 5.5  数据库：11.2.0.1

mysql：系统centos6.3  数据库：5.6.21

2.1先装 mysql-connector-odbc和unixODBC

[root@rac1 ~]# rpm -qa | grep mysql

mysql-5.0.77-4.el5_4.2

mysql-5.0.77-4.el5_4.2

mysql-connector-odbc-5.1.13-1.rhel5

[root@rac1 ~]# rpm -qa | grep ODBC

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

2.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         = bi

Password     = 123456

Database     = chanpin

OPTION       = 3

SOCKET       =

charset      = utf8

2.3.配置/etc/odbcinst.ini

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

FileUsage = 1

2.4.测试连接

[root@rac1 ~]# isql myodbc3 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

2.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

2.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)

)

2.7.配置odbc监听

路径：$ORACLE_HOME/hs/admin

注意：名字要跟odbc配置的名字一样我这里是myodbc3

[oracle@rac1 admin]$cd $ORACLE_HOME/hs/admin

[oracle@rac1 admin]$ 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

2.8.测试监听

[oracle@rac1 admin]$ tnsping myodbc3

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-NOV-2014 03:02:16

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))

OK (0 msec)

2.9.创建dblink

SQL>create public database link myodbc connect to "bi" identified by "123456" using'myodbc3';

SQL>select count(*) from "t_user"@myodbc;

COUNT(*)

----------

53980

3、删除dblink

DROPPUBLICDATABASELINK tomysql;