安装、配置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;
注意:查询字段名和表名都要用引号引起来,还要注意大小写;