Oracle 11g 設定 Gateways－assistant engineer

今天測試Oracle 11g Gateways for MSSQL2008 設定

先到官網去下載oracle 11g Gateways

然後下載是圖形介面!!

注意這邊路徑不要與Oracle Soft 路徑一樣

否則會覆蓋Oracle Soft bin底下的程式

解釋:

第一行為MSSQL 資料庫所在Server位置IP

第二行為端口.預設端口好像是1433

再來是SQL server name
MSSQL查 servername語法 select @@servername

第三行為database name

安裝完後我的位置在於

$ORACLE_HOME/dg4msql

裡面會有一份文件為dg4msql 為預設的也就是gateways 的 SID名字如果要修改也是可以但是有格式限制

內容
HS_FDS_CONNECT_INFO=[172.18.2.234]:1433//test # alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

修改gateways 格式 init{自己取名字}.ora 例如inittest.ora 切記test就是sid名字

調整

listener.ora

位置在$ORACLE_HOME/network/admin

新增紅字部分!!

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql) #gateways的SID名字
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) #ORACLE_HOME位置
      (PROGRAM = dg4msql) #預設一定要為dg4msql
   )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ogg.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

ADR_BASE_LISTENER = /u01/app/oracle

重新開啟 lsnrctl

修改tnsnames.ora 位置在$ORACLE_HOME/network/admin

增加紅字部分

tnsnames.ora
----------------------

dg4msql =
(DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ogg.localdomain)(PORT = 1521))
                (CONNECT_DATA =(SID = dg4msql) #Gateways SID
        (HS = OK)
)
)

OGG =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ogg.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ogg)
    )
)

存檔後使用tnsping dg4msql 看能使用這個tnsnames檔名嗎?

ogg-> tnsping dg4msql

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-MAR-2016 01:21:35

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST= ogg.localdomain)(PORT=1521))) (CONNECT_DATA = (SID = dg4msql)) (HS=OK))
OK (0 msec)
ogg->

在登入 Oracle 創建 DBlink

create public database link mylink connect to "sa" identified by "oracle" using 'dg4msql';

解說創建一個db link為 mylink 連到 MSSQL帳號為sa 密碼是 oracle 使用 tnsnames 為 dg4msql

SQL> select * from test1@mylink;

id name
---------- ----------------------------------------
2 joe

SQL>

如果出現以下錯誤

ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from MYLINK1

將tnsname.ora

內容改為

DG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ogg.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dg4msql)
)
(HS=OK)
)

即可修正此錯誤