安装、配置Oracle11g透明网关连接Sqlserver2005数据库
安装、配置Oracle11g透明网关连接Sqlserver2005数据库
Oracle透明网关安装在oracle服务器上
Oracle服务器版本如下：
1、用root用户登录oracle服务器的图形化界面，打开终端；
2、解压linux.x64_11gR2_gateways.zip；
3、进入gateways目录，运行
./runInstaller命令；
4、点击下一步，一步步进行；
只勾选需要连接的数据库
5、进入／product/11.2.0/dbhome_1/dg4msql/admin目录，配置inidg4msql.ora；
HS_FDS_CONNECT_INFO=[Sqlserver2005 IP]:Port//实例
# alternate connect format is
hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF (如果有问题可以设置为DEBUG，查看log下日志)
HS_FDS_RECOVERY_ACCOUNT=Sqlserver2005 用户名
HS_FDS_RECOVERY_PWD= Sqlserver2005密码
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK（这个是Sqlserver2005的字符编码）
6、进入／product/11.2.0/dbhome_1/dg4msql/admin目录，配置listener.ora.sample；
# This is a sample listener.ora that
contains the NET8 parameters that are
# needed to connect to an HS Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4msql)
)
)
#CONNECT_TIMEOUT_LISTENER = 0
7、进入／product/11.2.0/dbhome_1/dg4msql/admin目录，配置tnsnames.ora.sample；
# This is a sample tnsnames.ora that
contains the NET8 parameters that are
# needed to connect to an HS Agent
dg4msql
=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
8、进入／product/11.2.0/dbhome_1/network/admin，配置sqlnet.ora；
SQLNET.AUTHENTICATION_SERVICES=
(NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)
SQLNET.EXPIRE_TIME=10
注意：SQLNET.AUTHENTICATION_SERVICES=
(NTS)，透明网关一定要这行配置，但是有这行配置，就用不了”sqlplus
conn as sysdba”这个命令，要使用这个命令时，要去掉这行配置
9、进入／product/11.2.0/dbhome_1/network/admin，配置listener.ora；
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME =
/opt/oracle/product/11.2.0/dbhome_1)
(ENV="LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1/dg4msql/driver/lib:/usr/lib")
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT = 1521))
)
ADR_BASE_LISTENER = /opt/oracle
10、进入／product/11.2.0/dbhome_1/network/admin，配置
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
dg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID=dg4msql)
)
(HS = OK)
)
11、创建dblink连接；
create database link dg4msql connect to "用户" identified by "密码" using 'dg4msql';
注意：用户和密码要用双引号引起来；
12、通过dblink查询；
select “opendate”
from “logasset”@dg4msql;
注意：查询字段名和表名都要用引号引起来，还要注意大小写；
