其實這個問題很簡單,主要問題是一定要安裝好Oracle的服務端,其次裝好Sybase,問題就好做多了!
Oracle實現異種數據庫連接服務的技術叫做透明網關(Transparent Gateway)。
目前Oracle利用透明網關可以實現和SQL Server、Sybase、DB2等多種主流數據庫的互聯。
筆者由於工作需要,通過Oracle訪問Sybase數據庫,把配置Oracle9i TRANSPARENT GATEWAY FOR Sybase
的步驟寫成文檔,供需要的網友參考!
配置TRANSPARENT GATEWAY FOR Sybase步驟
1. 注意此步驟是在,oracle for window版本用到的,這一點非常的重要,有很多人就是找不到透明網關而信心全失的!一定是Oracle服務器的安裝!客戶端的情況我實驗過很多次是不行的!
oracle所在服務器上安裝Sybase clIEnt(或者在同一台server上安裝Oracle、Sybase服務器)
確保能夠訪問Sybase數據庫
2.在Oracle for window的版本中是有的這個很重要!安裝TRANSPARENT GATEWAY FOR Sybase選件,要用自定義安裝。
正確選擇Sybase的安裝目錄
選擇一個sid字符串准備賦給Sybase數據庫。如:tg4sybs,最好把sid字符串起成tg4sybs這樣方便後面的設計
設置SYBASE的dll路徑到環境變量PATH(這一步很重要),在安裝Sybase時路徑一般已經設置好了!
修改初始化文件,默認的是:
Oracle_HOME\tg4sybs\admin\inittg4sybs.ora
設置參數
HS_FDS_CONNECT_INFO
格式:HS_FDS_CONNECT_INFO= server_name. database_name[,INTERFACE= interface_file]
server_name. database_name是大小寫敏感的。
INTERFACE可選
例子:如下
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
# HS init parameters
HS_FDS_CONNECT_INFO=pengyk.syb_db
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
# Environment variables required for Sybase
set SYBASE="C:\\Sybase" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
上例中
server_name是pengyk
database_name是syb_db
注意最好寫成 set SYBASE="C:\\sybase"而不是C:\Sybase
Listener若是沒有的話在net configuration assistant進行配置
配置Oracle網絡服務的listener,配置文件是:listener.ora
默認路徑:Oracle_HOME\network\admin
加入如下
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME= gateway_sid)
(ORACLE_HOME= Oracle_home_directory)
(PROGRAM=tg4sybs)
gateway_sid就是3選擇的sid字符串(本例中就是tg4sybs)
oracle_home_directory是Oracle_HOME
tg4sybs若是Sybase是特定的。如果是其他數據庫,會不同。
例子如下:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
# LISTENER.ORA Network Configuration File: E:\Oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
#LISTENER1 =
# (DESCRIPTION_LIST =
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 2d45d6e549664fa)(PORT = 1521))
# )
# )
# )
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 2d45d6e549664fa)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\Oracle\ora92)
(PROGRAM = extproc)
#add by pengyk
(SID_DESC =
(SID_NAME = tg4sybs)
(ORACLE_HOME = E:\Oracle\ora92)
(PROGRAM = tg4sybs)
)
)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
停止監聽
lsnrctl stop
重新啟動監聽程序
lsnrctl start
配置Oracle server的tnsnames.ora使其能夠訪問Sybase
connect_descriptor=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= host_name)
(PORT= port_number)
(CONNECT_DATA=
(SID= gateway_sid))
(HS=OK))
connect_descriptor是連接串,任取,一般為sybs
host_name:Oracle server的name
port_number:Oracle監聽端口
gateway_sid就是3選擇的sid字符串(就是tg4sybs)
例子如下:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
sybs=
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST= 192.168.253.60)(PORT= 1521))
)
(CONNECT_DATA=
(SID = tg4sybs)
)
(HS=OK)
) $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
8.建立database link
如:
CREATE DATABASE LINK sybs CONNECT TO "unionsun"
IDENTIFIED BY "unionsun"
USING 'sybs';
即可訪問Sybase 數據庫。
需要注意的是,Sybase數據庫的表名,字段名,如果是小寫的,那麼在Oracle裡訪問的時候要加上雙引號""
如:
SQL〉select "opc" from ;
經過上面的配置,在window上的Oracle配置就可以通過透明網關對對Sybase數據庫進行訪問了