配置透明網關的步驟
2024-07-21 02:37:44
供稿：網友
基本步驟
說明：
HostName：dmserver
Win2000 Server Oracle Database 9.2.0.1.0 Microsoft SQL Server 2000
UserName:User PassWord:User
Database:CFDai TableName:Test
都使用 tcp/ip 默認端口1521
基本步驟：
1: 安裝 TRANSPARENT GATEWAY FOR MSSQL選件，輸入主機名和數據庫名
安裝完后，在d:/oracle/ora92的目錄下產生一個目錄D:/oracle/ora92/tg4msql
同時，自動生成文件：inittg4msql.ora
其中內容為：
HS_FDS_CONNECT_INFO=dmserver.CFDai #服務器名.數據庫名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
注：第一行可寫為HS_FDS_CONNECT_INFO="dmserver;DATABASE=CFDai"
2: 配置 LISTENER.ORA
SID_LIST_LISTENER =
(SID_LIST =
……
(SID_DESC =
(GLOBAL_DBNAME = tg4msql) #可重命名
(PROGRAM = tg4msql)
(SID_NAME = tg4msql) #可重命名
(ORACLE_HOME = D:/oracle/ora92) #數據庫主目錄名
)
)
3: 停止并重啟 DATABASE 和 LISTENER服務
4: 配置 tnsnames.ora，在文件尾增加以下內容：
tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dmserver)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK)
)
5: 在SQL Plus或SQLPlus Worksheet中通過如下語句建立數據庫連接
CREATE PUBLIC DATABASE LINK DB_SQL CONNECT TO User IDENTIFIED by User USING 'tg4msql';
6: 在SQL Plus或SQLPlus Worksheet中測試
select * from test@DB_SQL;
7：取消數據庫連接
DROP PUBLIC DATABASE LINK DB_SQL;
FW:多謝！我也剛剛搞定。
原來是一個低級錯誤——tnsnames.ora文件配置錯誤（直接拷貝后修改，結果沒改主機地址！指到別的地方了：）：））。另外注重：lsnrctl中顯示status=unknow不必驚奇，正常。
今天閱讀了Oracle 9i附帶的Oracle Transparent Gateways文檔，整理了一下，供參考！
搜索要害字：
Oracle Transparent Gateways
透明網關
Oracle database applications can be executed against non-Oracle 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 non-Oracle system are contained in the gateway. The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle 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 non-Oracle 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 9i setup package), perform the following tasks to configure the gateway for Microsoft SQL Server:
1.Configuring the Gateway(ORACLE_HOME/tg4msql/admin/init.ora on gateway side)
2.Configuring Oracle Net Services Listener for the Gateway(listener.ora on gateway side)
3.Configuring the Oracle Database Server for Gateway access(tnsnames.ora on oracle side)
4.Creating Database Links(on oracle side)
1.Configuring the Gateway
Task 1: Choose a System Identifier for the Gateway
The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance.The SID is used as part of the file name for the initialization parameter file.
Task 2: Customize the Initialization Parameter File
The initialization parameter file must be available when the gateway is started.
如：ORACLE_HOME/tg4msql/admin/initmytg4msql.ora
注重：HS_FDS_CONNECT_INFO= server_name.database_name
HS_FDS_DEFAULT_OWNER = 缺省用戶
2.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 listener.ora file:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=.
.
)
(SID_DESC=.
.
)
(SID_DESC=
(SID_NAME=mytg4msql)
(ORACLE_HOME=oracle_home_Directory)
(PROGRAM=tg4msql)
)
)
3.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 tnsnames.ora file is required by the Oracle database server accessing the gateway, but not by the gateway. Edit the tnsnames.ora file to add a connect descriptor for the gateway.:
connect_descriptor=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= host_name)
(PORT= port_number)
)
(CONNECT_DATA=
(SID= mytg4msql))
(HS=OK))--(HS=OK) specifies that this connect descriptor uses the Oracle Heterogeneous Services option.
4.Creating Database Links
接下來：Configuring the Gateway for Multiple Microsoft SQL Server Databases
1.Configuring the Gateway
Create Two Initialization Parameter Files
> cd ORACLE_HOME/tg4msql/admin
> copy inittg4msql.ora inittg4msql2.ora
> copy inittg4msql.ora inittg4msql3.ora
Change the value of the HS_FDS_CONNECT_INFO parameter in the new files.
For inittg4msql2.ora,
enter the following:
HS_FDS_CONNECT_INFO=msql_nt2.db2
For inittg4msql3.ora, enter the following:
HS_FDS_CONNECT_INFO=msql_nt3.db3
2.Configuring Oracle Net Services Listener for the Gateway
Add Entries to listener.ora
Add two new entries to the TNS listener configuration file, listener.ora.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=tg4msql)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
(SID_DESC=
(SID_NAME=tg4msql2)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
(SID_DESC=
(SID_NAME=tg4msql3)
(ORACLE_HOME= oracle_home_directory)
(PROGRAM=tg4msql)
)
)
3.Configuring the Oracle Database Server for Gateway Access
tnsnames.ora ：
old_db_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql))
(HS=OK))
new_db2_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql2))
(HS=OK))
new_db3_using=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=1541)
(HOST=gtwhost))
(CONNECT_DATA=
(SID=tg4msql3))
(HS=OK))
4.Creating Database Links
