误操作,没写完就发出去了
搭10g gateway的时候, 创建好dblink后查询数据,没有结果返回,也没有报错信息.不知道该从哪里查起. 所以,请做成功的达人帮忙劳心看下是哪里配置的不对了.谢谢
一 环境
oracle数据库环境:
RHEL5U4 + oracle database 10.2.0.4
IP:172.21.29.222
10g gateway 环境:
win srv 2003 sp2 + 10201_gateways_win32
IP:172.21.29.55
ORACLE_HOME: c:\oracle\product\10.2.0\tg_1
注: win 2003 系统是全新安装,除gateway软件外无其它软件
sql server 2005 环境:
系统未知 + sql server 2005
IP:172.21.27.31
db_name:CenterDB_Developer
二 配置过程
1.gateway机配置文件: (安装gateway软件的时候什么都没有配置)
c:\oracle\product\10.2.0\tg_1\tg4msql\admin\inittg4msql.ora
[table=98%,none][tr][td]
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=172.21.27.31;DATABASE=CenterDB_Developer"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER[/td][/tr][/table]
c:\oracle\product\10.2.0\tg_1\NETWORK\ADMIN\listener.ora
[table=98%,none][tr][td]# listener.ora Network Configuration File: C:\oracle\product\10.2.0\tg_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = tg4msql)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM=tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.29.55)(PORT = 1521))
)
)
[/td][/tr][/table]
到 管理工具-服务 中关闭/重启OracleOraTg10g_home1TNSListener服务.查看服务状态
[table=98%,none][tr][td]C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-5月 -2010 11:06:00
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
启动日期 06-5月 -2010 11:04:37
正常运行时间 0 天 0 小时 1 分 23 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\oracle\product\10.2.0\tg_1\network\admin\listener.ora
监听程序日志文件 C:\oracle\product\10.2.0\tg_1\network\log\listener.log
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.21.29.55)(PORT=1521)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "tg4msql" 包含 1 个例程。
例程 "tg4msql", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
[/td][/tr][/table]
查看 sql server 2005 端口是否开放
[table=98%,none][tr][td]C:\Documents and Settings\Administrator>telnet 172.21.27.31 1433[/td][/tr][/table]
2. oracle database机tnsname.ora 文件
localhost-> cat /oracle/database/10.2.0/db_1/network/admin/tnsnames.ora
[table=98%,none][tr][td]# tnsnames.ora Network Configuration File: /oracle/database/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = app_server9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora102)
)
)
ORA101 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = app_server9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora101)
)
)
tg4msql=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=172.21.29.55)
(PORT=1521)
)
(CONNECT_DATA=
(SID=tg4msql))
(HS=OK)
)
[/td][/tr][/table]
测试gateway监听情况
localhost-> tnsping tg4msql
[table=98%,none][tr][td]TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 06-MAY-2010 11:22:25
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=172.21.29.55) (PORT=1521)) (CONNECT_DATA= (SID=tg4msql)) (HS=OK))
OK (30 msec)
localhost->
[/td][/tr][/table]
建立dblink
[table=98%,none][tr][td]SQL>show user
USER is "SCOTT"
SQL> create database link tg4msql connect to aaaa identified by "aaaa" using 'tg4msql';
Database link created.
SQL> select sysdate from dual@tg4msql;
然后就挂在这里不动了
[/td][/tr][/table]
补充:
http://space.itpub.net/13023909/viewspace-584312 这个贴子上说
在訪問SQLServer2005的時候出現了一個問題,就是在oracle執行查詢SQLServer過程中沒有結果返回,就是錯誤信息也沒有.解決方式為運行$ORACLE_HOME/rdbms/admin/caths.sql 腳本. 此腳本為創建HS所需表及視圖表
我已经用sysdba运行caths.sql脚本了.但依然查询无结果返回
[ 本帖最后由 ilsyx 于 2010-5-6 13:16 编辑 ]
搭10g gateway的时候, 创建好dblink后查询数据,没有结果返回,也没有报错信息.不知道该从哪里查起. 所以,请做成功的达人帮忙劳心看下是哪里配置的不对了.谢谢
一 环境
oracle数据库环境:
RHEL5U4 + oracle database 10.2.0.4
IP:172.21.29.222
10g gateway 环境:
win srv 2003 sp2 + 10201_gateways_win32
IP:172.21.29.55
ORACLE_HOME: c:\oracle\product\10.2.0\tg_1
注: win 2003 系统是全新安装,除gateway软件外无其它软件
sql server 2005 环境:
系统未知 + sql server 2005
IP:172.21.27.31
db_name:CenterDB_Developer
二 配置过程
1.gateway机配置文件: (安装gateway软件的时候什么都没有配置)
c:\oracle\product\10.2.0\tg_1\tg4msql\admin\inittg4msql.ora
[table=98%,none][tr][td]
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=172.21.27.31;DATABASE=CenterDB_Developer"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER[/td][/tr][/table]
c:\oracle\product\10.2.0\tg_1\NETWORK\ADMIN\listener.ora
[table=98%,none][tr][td]# listener.ora Network Configuration File: C:\oracle\product\10.2.0\tg_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = tg4msql)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM=tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.29.55)(PORT = 1521))
)
)
[/td][/tr][/table]
到 管理工具-服务 中关闭/重启OracleOraTg10g_home1TNSListener服务.查看服务状态
[table=98%,none][tr][td]C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-5月 -2010 11:06:00
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
启动日期 06-5月 -2010 11:04:37
正常运行时间 0 天 0 小时 1 分 23 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\oracle\product\10.2.0\tg_1\network\admin\listener.ora
监听程序日志文件 C:\oracle\product\10.2.0\tg_1\network\log\listener.log
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.21.29.55)(PORT=1521)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "tg4msql" 包含 1 个例程。
例程 "tg4msql", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
[/td][/tr][/table]
查看 sql server 2005 端口是否开放
[table=98%,none][tr][td]C:\Documents and Settings\Administrator>telnet 172.21.27.31 1433[/td][/tr][/table]
2. oracle database机tnsname.ora 文件
localhost-> cat /oracle/database/10.2.0/db_1/network/admin/tnsnames.ora
[table=98%,none][tr][td]# tnsnames.ora Network Configuration File: /oracle/database/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = app_server9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora102)
)
)
ORA101 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = app_server9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora101)
)
)
tg4msql=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=172.21.29.55)
(PORT=1521)
)
(CONNECT_DATA=
(SID=tg4msql))
(HS=OK)
)
[/td][/tr][/table]
测试gateway监听情况
localhost-> tnsping tg4msql
[table=98%,none][tr][td]TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 06-MAY-2010 11:22:25
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=172.21.29.55) (PORT=1521)) (CONNECT_DATA= (SID=tg4msql)) (HS=OK))
OK (30 msec)
localhost->
[/td][/tr][/table]
建立dblink
[table=98%,none][tr][td]SQL>show user
USER is "SCOTT"
SQL> create database link tg4msql connect to aaaa identified by "aaaa" using 'tg4msql';
Database link created.
SQL> select sysdate from dual@tg4msql;
然后就挂在这里不动了
[/td][/tr][/table]
补充:
http://space.itpub.net/13023909/viewspace-584312 这个贴子上说
在訪問SQLServer2005的時候出現了一個問題,就是在oracle執行查詢SQLServer過程中沒有結果返回,就是錯誤信息也沒有.解決方式為運行$ORACLE_HOME/rdbms/admin/caths.sql 腳本. 此腳本為創建HS所需表及視圖表
我已经用sysdba运行caths.sql脚本了.但依然查询无结果返回
[ 本帖最后由 ilsyx 于 2010-5-6 13:16 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-662053/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-662053/