配置透明網關的步驟

　　基本步驟
　　說明
　　HostNamedmserver
　　Win Server Oracle Database Microsoft SQL Server
　　UserName:User Password:User
　　Database:CFDai TableName:Test
　　都使用 tcp/ip 默認端口
　　基本步驟
　　: 安裝 TRANSPARENT GATEWAY FOR MSSQL選件輸入主機名和數據庫名
　　安裝完後在d:\oracle\ora的目錄下產生一個目錄D:\oracle\ora\tgmsql
　　同時自動生成文件inittgmsqlora
　　其中內容為
　　HS_FDS_CONNECT_INFO=dmserverCFDai #服務器名數據庫名
　　HS_FDS_TRACE_LEVEL=OFF
　　HS_FDS_RECOVERY_ACCOUNT=RECOVER
　　HS_FDS_RECOVERY_PWD=RECOVER
　　注第一行可寫為HS_FDS_CONNECT_INFO=dmserver;DATABASE=CFDai
　　
　　: 配置 LISTENERORA
　　SID_LIST_LISTENER =
　　(SID_LIST =
　　……
　　(SID_DESC =
　　(GLOBAL_DBNAME = tgmsql) #可重命名
　　(PROGRAM = tgmsql)
　　(SID_NAME = tgmsql) #可重命名
　　(ORACLE_HOME = D:\oracle\ora) #數據庫主目錄名
　　)
　　)
　　
　　: 停止並重啟 DATABASE 和 LISTENER服務
　　
　　: 配置 tnsnamesora在文件尾增加以下內容
　　tgmsql =
　　(DESCRIPTION =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = dmserver)(PORT = ))
　　)
　　(CONNECT_DATA =
　　(SID = tgmsql)
　　)
　　(HS=OK)
　　)
　　
　　: 在SQL Plus或SQLPlus Worksheet中通過如下語句建立數據庫連接
　　CREATE PUBLIC DATABASE LINK DB_SQL CONNECT TO User IDENTIFIED by User USING tgmsql;
　　
　　: 在SQL Plus或SQLPlus Worksheet中測試
　　select * from test@DB_SQL;
　　
　　取消數據庫連接
　　DROP PUBLIC DATABASE LINK DB_SQL;
　　
　　FW:多謝！我也剛剛搞定
　　原來是一個低級錯誤——tnsnamesora文件配置錯誤（直接拷貝後修改結果沒改主機地址！指到別的地方了）））另外注意lsnrctl中顯示status=unknow不必驚訝正常
　　
　　今天閱讀了Oracle i附帶的Oracle Transparent Gateways文檔整理了一下供參考！
　　
　　搜索關鍵字
　　Oracle Transparent Gateways
　　透明網關
　　
　　Oracle database applications can be executed against nonOracle database servers using SQL*Connect or the Oracle Open Gateway
　　Oracle Transparent Gateways
　　The capabilities SQL mappings data type conversions and interface to the remote nonOracle system are contained in the gateway The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and nonOracle systems
　　The gateway can be installed on any machine It can be on the same machine as the Oracle database or on the same machine as the nonOracle system or on a third machine as a standalone Each configuration has its advantages and disadvantages The issues to consider when determining where to install the gateway are network traffic operating system platform availability hardware resources and storage
　　
　　Configuring the Gateway
　　After installing the gateway(supplied with Oracle i setup package) perform the following tasks to configure the gateway for Microsoft SQL Server:
　　Configuring the Gateway(ORACLE_HOME\tgmsql\admin\initora on gateway side)
　　Configuring Oracle Net Services Listener for the Gateway(listenerora on gateway side)
　　Configuring the Oracle Database Server for Gateway Access(tnsnamesora on oracle side)
　　Creating Database Links(on oracle side)
　　
　　Configuring the Gateway
　　Task : Choose a System Identifier for the Gateway
　　The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instanceThe SID is used as part of the file name for the initialization parameter file
　　Task : Customize the Initialization Parameter File
　　The initialization parameter file must be available when the gateway is started
　　如ORACLE_HOME\tgmsql\admin\initmytgmsqlora
　　注意HS_FDS_CONNECT_INFO= server_namedatabase_name
　　HS_FDS_DEFAULT_OWNER = 缺省用戶
　　
　　Configuring Oracle Net Services Listener for the Gateway
　　The gateway requires Oracle Net Services to provide transparent data access After configuring the gateway configure Oracle Net Services to work with the gateway
　　Configure Oracle Net Services TNS Listener for the Gateway
　　If you are already running a TNS listener that listens on multiple database SIDs add only the following syntax to SID_LIST in the existing listenerora file:
　　SID_LIST_LISTENER=
　　(SID_LIST=
　　(SID_DESC=
　　
　　)
　　(SID_DESC=
　　
　　)
　　(SID_DESC=
　　(SID_NAME=mytgmsql)
　　(ORACLE_HOME=oracle_home_directory)
　　(PROGRAM=tgmsql)
　　)
　　)
　　Configuring the Oracle Database Server for Gateway Access
　　Before you use the gateway to access Microsoft SQL Server data you must configure the Oracle database server to enable communication with the gateway over Oracle Net Services
　　Configuring Oracle Net Services for the Oracle Database Server
　　The tnsnamesora file is required by the Oracle database server accessing the gateway but not by the gateway Edit the tnsnamesora file to add a connect descriptor for the gateway:
　　connect_descriptor=
　　(DESCRIPTION=
　　(ADDRESS=
　　(PROTOCOL=TCP)
　　(HOST= host_name)
　　(PORT= port_number)
　　)
　　(CONNECT_DATA=
　　(SID= mytgmsql))
　　(HS=OK))(HS=OK) specifies that this connect descriptor uses the Oracle Heterogeneous Services option
　　
　　Creating Database Links
　　
　　
　　接下來Configuring the Gateway for Multiple Microsoft SQL Server Databases
　　Configuring the Gateway
　　Create Two Initialization Parameter Files
　　> cd ORACLE_HOME\tgmsql\admin
　　> copy inittgmsqlora inittgmsqlora
　　> copy inittgmsqlora inittgmsqlora
　　Change the value of the HS_FDS_CONNECT_INFO parameter in the new files
　　For inittgmsqlora enter the following:
　　HS_FDS_CONNECT_INFO=msql_ntdb
　　For inittgmsqlora enter the following:
　　HS_FDS_CONNECT_INFO=msql_ntdb
　　Configuring Oracle Net Services Listener for the Gateway
　　Add Entries to listenerora
　　Add two new entries to the TNS listener configuration file listenerora
　　SID_LIST_LISTENER=
　　(SID_LIST=
　　(SID_DESC=
　　(SID_NAME=tgmsql)
　　(ORACLE_HOME= oracle_home_directory)
　　(PROGRAM=tgmsql)
　　)
　　(SID_DESC=
　　(SID_NAME=tgmsql)
　　(ORACLE_HOME= oracle_home_directory)
　　(PROGRAM=tgmsql)
　　)
　　(SID_DESC=
　　(SID_NAME=tgmsql)
　　(ORACLE_HOME= oracle_home_directory)
　　(PROGRAM=tgmsql)
　　)
　　)
　　Configuring the Oracle Database Server for Gateway Access
　　tnsnamesora
　　old_db_using=(DESCRIPTION=
　　(ADDRESS=
　　(PROTOCOL=TCP)
　　(PORT=)
　　(HOST=gtwhost))
　　(CONNECT_DATA=
　　(SID=tgmsql))
　　(HS=OK))
　　new_db_using=(DESCRIPTION=
　　(ADDRESS=
　　(PROTOCOL=TCP)
　　(PORT=)
　　(HOST=gtwhost))
　　(CONNECT_DATA=
　　(SID=tgmsql))
　　(HS=OK))
　　new_db_using=(DESCRIPTION=
　　(ADDRESS=
　　(PROTOCOL=TCP)
　　(PORT=)
　　(HOST=gtwhost))
　　(CONNECT_DATA=
　　(SID=tgmsql))
　　(HS=OK))
　　Creating Database Links
　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/18459.html