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 wlyxptidentified 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-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

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;

新聞名稱：oracle通過DBlink連接oracle
文章網址：http://m.kartarina.com/article0/gesooo.html

成都網站建設公司_創新互聯，為您提供云服務器、網站策劃、網站建設、手機網站建設、關鍵詞優化、用戶體驗

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容