Oracle透明网关连接sqlserver金沙js娱乐场官方网站,如何在Oracle里设置访问多个SQL

Transparent Gateway)里访问Microsoft SQL Server数据库,假设我们要在ORACLE里同时能访问SQL,用户名密码自己在sqlserver检测正确,Oracle透明网关连接sqlserver,Oracle透明网关安装在oracle服务器上,安装、配置Oracle11g透明网关连接Sqlserver2005数据库,-Uvh mysql-connector-odbc-5.1.5-0.i386.rpm 3. 安装oracle,用root用户 rpm -Uvh

如何在Oracle里设置访问多个SQL
Server数据库?假设我们要在ORACLE里同时能访问SQL
Server里默认的pubs和Northwind两个数据库。

Oracle透明网关连接sqlserver,ora-01017
oracle11g 透明网关 连接 sqlserver2000

安装、配置Oracle11g透明网关连接Sqlserver2005数据库

一、环境 OS:Linux myhostname 2.6.9-42.ELsmp #1 SMP Sat Aug 12
09:39:11 CDT 2006 i686 i686 i386 GNU/Linux CentOS release 4.4 (Final)
Oracle:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Prod,数据库已经安装好 mysql:5.1.34-community for windows
二、安装配置 1. 安装unixODBC,用root用户 rpm -Uvh
unixODBC-2.2.12-1.el4s1.1.i386.rpm 2. 安装mysql ODBC,用root用户 rpm
-Uvh mysql-connector-odbc-5.1.5-0.i386.rpm 3. 安装oracle
gateway,用oracle用户 我装的是10201_gateways_linux32.zip unzip
10201_gateways_linux32.zip cd gateways ./runInstaller 安装方法和oracle
db 软件一样,我把gateway和db装一起了,共用一个OracleHOME 4.
配置/etc/odbc.ini [DSName] Driver =/usr/lib/libmyodbc5.so Description
=MySQL Server =xxx.xxx.xxx.xxx Port =3306 User =root UID =root Password
= mypass Database =mysqldbname Option =3 Socket = charset =utf8 测试ODBC
isql -v DSName root mypass 5. 配置$ORACLE_HOME/hs/admin/initDSName.ora
HS_FDS_CONNECT_INFO = DSName HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so 6.
配置listener.ora,加红色部分 SID_LIST_LISTENER = (SID_LIST =
(SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =
/usr/u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) )
(SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME =
/usr/u01/app/oracle/product/10.2.0/db_1) (SID_NAME = BOSS) )
(SID_DESC = (SID_NAME = phpcms) (ORACLE_HOME =
/usr/u01/app/oracle/product/10.2.0/db_1) (PROGRAM = hsodbc) ) ) 7.
配置tnsnames.ora,添加 DSName = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521)) ) (CONNECT_DATA
= (SERVICE_NAME = DSName)) (HS = OK) ) 8. 重启监听器并测试 lsnrctl
reload lsnrctl service Service “DSName” has 1 instance(s). Instance
“DSName”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s): “DEDICATED” established:3 refused:0 LOCAL SERVER The command
completed successfully tnsping DSName Attempting to contact (DESCRIPTION
= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.125)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DSName))
(HS = OK)) OK (0 msec) 9. 建立dblink CREATE PUBLIC DATABASE LINK
linkname CONNECT TO “root” IDENTIFIED BY PWD USING ‘DSName’; 10. 测试
select “name” from t1@linkname; 三、遗留问题 1.
字符集问题,最好oracle和mysql是utf8,否则中文有问题 2. text字段会报错:
select “textcol” from t1@linkname; ORA-28500: 连接 ORACLE 到非 Oracle
系统时返回此信息: [Generic Connectivity Using ODBC][MySQL][ODBC 5.1
Driver][mysqld-5.1.34-community]You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near ‘”t1″ WHERE “id”=1’ at line 1 (SQL State:
37000; SQL Code: 1064) ORA-02063: 紧接着 2 lines (起自 DSName)
本来想用datadirect的mysql ODBC试试,可只支持mysql
enterprise版本,实在不好找,以后有机会再说吧。

1、在安装了ORACLE9i Standard Edition或者ORACLE9i Enterprise
Edition的windows机器上(IP:192.168.0.2), 产品要选了透明网关(Oracle
Transparent Gateway)里访问Microsoft SQL Server数据库

create database link DBFCSERVER
connect to sa
IDENTIFIED BY “ldfc”
using ‘ldfc’;

Oracle透明网关安装在oracle服务器上

$ORACLE9I_HOMEg4msqladmin下新写initpubs.ora和initnorthwind.ora配置文件.

select sysdate from dual@DBFCSERVER;

Oracle服务器版本如下:

initpubs.ora内容如下:

提示
ORA-01017,用户名密码不对,用户名密码自己在sqlserver检测正确,请大家帮忙看看是什么原因

 

HS_FDS_CONNECT_INFO=”SERVER=SQLSERVER_HOSTNMAE;DATABASE=pubs”

环境
197.168.0.20 计算机名:ldfc-server1

1、用root用户登录oracle服务器的图形化界面,打开终端;

HS_DB_NAME=pubs

sql server2000
数据库名称:测试数据库
用户名:sa
密码:ldfc

2、解压linux.x64_11gR2_gateways.zip;

HS_FDS_TRACE_LEVEL=OFF

Oracle11g datebase
安装目录:F:\app\Administrator\product\11.2.0\dbhome_1
SID:ldfc
端口:1521

3、进入gateways目录,运行
./runInstaller命令;

HS_FDS_RECOVERY_ACCOUNT=RECOVER

oracle Database Gateway for Microsoft SQL Server 11.2.0.1.0
安装目录:F:\app\Administrator\product\11.2.0\dbhome_1

4、点击下一步,一步步进行;

HS_FDS_RECOVERY_PWD=RECOVER

F:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin下增加文件:initldfc.ora
HS_FDS_CONNECT_INFO=[197.168.0.20]//林甸房产
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

金沙js娱乐场官方网站 1

initnorthwind.ora内容如下:

listener.ora文件修改

 

HS_FDS_CONNECT_INFO=”SERVER=sqlserver_hostname;DATABASE=Northwind”

listener.ora Network Configuration File: F:\app\Administrator\product\11.2.0\dbhome_1

\network\admin\listener.ora

金沙js娱乐场官方网站 2

HS_DB_NAME=Northwind

Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS =
“EXTPROC_DLLS=ONLY:F:\app\Administrator\product\11.2.0\dbhome_1

\bin\oraclr11.dll”)
)
)
LDFC
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ldfc-server1)(PORT = 1521))
)
)

ADR_BASE_LISTENER = F:\app\Administrator

tnsnames.ora文件修改

 只勾选需要连接的数据库

HS_FDS_TRACE_LEVEL=OFF

tnsnames.ora Network Configuration File: F:\app\Administrator\product\11.2.0\dbhome_1

\network\admin\tnsnames.ora

 金沙js娱乐场官方网站 3

HS_FDS_RECOVERY_ACCOUNT=RECOVER

Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

LDFC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ldfc-server1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ldfc)
)
)

金沙js娱乐场官方网站 4

HS_FDS_RECOVERY_PWD=RECOVER

金沙js娱乐场官方网站 5

$ORACLE9I_HOMEetworkadmin 下listener.ora内容如下:

5、进入/product/11.2.0/dbhome_1/dg4msql/admin目录,配置inidg4msql.ora;

LISTENER =

HS_FDS_CONNECT_INFO=[Sqlserver2005 IP]:Port//实例

(DESCRIPTION_LIST =

# alternate connect format is
hostname/serverinstance/databasename

(DESCRIPTION =

HS_FDS_TRACE_LEVEL=OFF 
(如果有问题可以设置为DEBUG,查看log下日志)

(ADDRESS_LIST =

HS_FDS_RECOVERY_ACCOUNT=Sqlserver2005 用户名

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))

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

SID_LIST_LISTENER =

# needed to connect to an HS Agent

(SID_LIST =

 

(SID_DESC =

LISTENER =

(GLOBAL_DBNAME = test9)

 (ADDRESS_LIST=

(ORACLE_HOME = d:oracleora92)

     
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

(SID_NAME = test9)

 )

)