我通过oracle 的dblink 连接到db2数据库
下面配置的是Db2的生产库:
第一步配置Odbc名称为db2toora(连接db2正常)第二步先在ORacle目录下的hs/admin 创建文件名为initdb2toora.ora文件 内容如下:
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. #
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2toora
HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>第三步在Network下的admin文件下的listener.ora的加入内容如下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = db2toora)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2toora) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
)
红色为新加的第四步是在Network下的admin文件下的tnsnames.ora加入的内容如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
db2toora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2toora) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)红色为新加的第五部创建DBlink测试 错误如下:ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][IBM][CLI Driver] SQL30082N 安全处理失败,因为 "24"("USERNAME AND/OR PASSWORD INVALID")。 SQLSTATE=08001
(SQL State: 08001; SQL Code: -30082)
ORA-02063: 紧接着 3 lines (起自 DB2TOORA)下面配置的是Db2的测试库:
第一步配置Odbc名称为db2(连接db2正常)第二步先在ORacle目录下的hs/admin 创建文件名为initdb2.ora文件 内容如下:
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. #
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2
HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>第三步在Network下的admin文件下的listener.ora的加入内容如下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = db2toora)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2toora) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
) )红色为新加的第四步是在Network下的admin文件下的tnsnames.ora加入的内容如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
db2toora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2toora) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)
db2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)红色为新加的
第五部创建DBlink测试 正常
大家帮忙看一下第一次测试不正常的原因主要出现在哪?
下面配置的是Db2的生产库:
第一步配置Odbc名称为db2toora(连接db2正常)第二步先在ORacle目录下的hs/admin 创建文件名为initdb2toora.ora文件 内容如下:
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. #
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2toora
HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>第三步在Network下的admin文件下的listener.ora的加入内容如下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = db2toora)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2toora) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
)
红色为新加的第四步是在Network下的admin文件下的tnsnames.ora加入的内容如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
db2toora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2toora) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)红色为新加的第五部创建DBlink测试 错误如下:ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][IBM][CLI Driver] SQL30082N 安全处理失败,因为 "24"("USERNAME AND/OR PASSWORD INVALID")。 SQLSTATE=08001
(SQL State: 08001; SQL Code: -30082)
ORA-02063: 紧接着 3 lines (起自 DB2TOORA)下面配置的是Db2的测试库:
第一步配置Odbc名称为db2(连接db2正常)第二步先在ORacle目录下的hs/admin 创建文件名为initdb2.ora文件 内容如下:
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. #
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2
HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>第三步在Network下的admin文件下的listener.ora的加入内容如下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = db2toora)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2toora) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
)
(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = db2) #这里的sid_name必须与tnsnames.ora里的sid保持一致
(PROGRAM = hsodbc) #这是通过odbc连接还有通过oledb连接的方式
) )红色为新加的第四步是在Network下的admin文件下的tnsnames.ora加入的内容如下:
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
db2toora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2toora) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)
db2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hc-zyj)(PORT = 1521))
)
(CONNECT_DATA =
(sid = db2) #这里的sid必须与listener.ora里的sid_name保持一致
)
(hs=ok) #这里必须添加,这是oracle打开transparent gateway的命令
)红色为新加的
第五部创建DBlink测试 正常
大家帮忙看一下第一次测试不正常的原因主要出现在哪?
解决方案 »
- 求助一个简单的存储过程题目,在线等~
- 求大神帮忙,oracle 存储过程,我有一张税率表。
- plsql的小问题
- 請問rowid , rownum, UROWID有甚麼分別?
- 求一句sql语句(小妹急需,在线等待)
- 请问那儿可以下到Oracle9i或者Oracle8i企业版??
- 如何在windows2000上安装Oracle9i
- 表的方案和表的表空间有什么区别和联系?
- oracle存储过程该如何快速的掌握?如何成长为一名优秀的DBA,求大神指点
- 请问企业部署Oracle数据库用Solaris或者Oracle Linux还是其他操作系统?我要学哪个操作系统比较保守一点?
- 游标打开多次 但关闭一次的问题
- oracle update 更新问题
创建dblink有那么复杂吗?例:
Drop database link "ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM";create database link "ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
connect to RSSZB
identified by "943492088"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.66.149.197)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
))';