实际问题描述:
我已经有了SQL 和 sybase 数据库最为数据源,用oracle 的透明网关,写好建立dblink,
inittg4msql,listener.ora,tnsname.ora 三个脚本都已经 建立,并且和oracle连接正常,数据访问可行。
现在新增需求,oracle需要在保留现有访问能力情况下,访问其他 IP地址下的SQL 和 sybase 数据源的数据(基本可以视作不同地址、不同数据库、不同数据结构)。
我希望了解:如何在 inittg4msql 的脚本写ip地址部分,或者是重新建立一个利斯的脚本?举我现在的连接sql脚本如下:
用于 inittg4msql 的编码:
# 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=10.XXX.14.32;DATABASE=tjzz"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER用于 listener.ora的编码:
# LISTENER.ORA Network Configuration File: E:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ETLSVR.XXXXX.NCPG)
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = ETLSVR)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = tg4msql)
(PROGRAM=tg4msql)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase1)
(PROGRAM=hsodbc)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase2)
(PROGRAM=hsodbc)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase3)
(PROGRAM=hsodbc)
)
)用于 ltnsname.ora的编码:
# TNSNAMES.ORA Network Configuration File: E:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.SYBASE3.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase3)
)
(HS = OK)
)INST1_HTTP.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)SYBASE1.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase1)
)
(HS = OK)
)ORA9_10.120.19.99 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.19.99)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORA9)
(SERVER = DEDICATED)
)
)SYBASE2.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase2)
)
(HS = OK)
)SQLSERVER.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)ETLSVR_xx.xxx.4.128 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.4.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = etlsvr)
(SERVER = DEDICATED)
)
)ETLSVR.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ETLSVR.xxxxx.NCPG)
)
)EXTPROC_CONNECTION_DATA.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)FKZXSQLSERVER.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.4.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)
我已经有了SQL 和 sybase 数据库最为数据源,用oracle 的透明网关,写好建立dblink,
inittg4msql,listener.ora,tnsname.ora 三个脚本都已经 建立,并且和oracle连接正常,数据访问可行。
现在新增需求,oracle需要在保留现有访问能力情况下,访问其他 IP地址下的SQL 和 sybase 数据源的数据(基本可以视作不同地址、不同数据库、不同数据结构)。
我希望了解:如何在 inittg4msql 的脚本写ip地址部分,或者是重新建立一个利斯的脚本?举我现在的连接sql脚本如下:
用于 inittg4msql 的编码:
# 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=10.XXX.14.32;DATABASE=tjzz"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER用于 listener.ora的编码:
# LISTENER.ORA Network Configuration File: E:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ETLSVR.XXXXX.NCPG)
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = ETLSVR)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = tg4msql)
(PROGRAM=tg4msql)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase1)
(PROGRAM=hsodbc)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase2)
(PROGRAM=hsodbc)
)
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = sybase3)
(PROGRAM=hsodbc)
)
)用于 ltnsname.ora的编码:
# TNSNAMES.ORA Network Configuration File: E:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.SYBASE3.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase3)
)
(HS = OK)
)INST1_HTTP.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)SYBASE1.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase1)
)
(HS = OK)
)ORA9_10.120.19.99 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.19.99)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORA9)
(SERVER = DEDICATED)
)
)SYBASE2.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sybase2)
)
(HS = OK)
)SQLSERVER.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)ETLSVR_xx.xxx.4.128 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.4.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = etlsvr)
(SERVER = DEDICATED)
)
)ETLSVR.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = b91sc1x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ETLSVR.xxxxx.NCPG)
)
)EXTPROC_CONNECTION_DATA.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)FKZXSQLSERVER.xxxxx.NCPG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.4.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)
我应该怎么把他也加到dblink中来? 以前我只处理过同一个ip地址下的多个数据库,对于不同地址下的多个数据库还没有处理的经验。
我现在考虑:是在现有的 inittg4msql 的编码中,有Ip地址的一行下面直接换行,加入新的数据库描述,如: HS_FDS_CONNECT_INFO="SERVER=11.222.33.44 ;DATABASE=new_sqldb" ;
还是应该另外重新写一个类似的脚本, 把 HS_FDS_CONNECT_INFO="SERVER=11.222.33.44 ;DATABASE=new_sqldb" 写到另外的新脚本中?或者是其他的处理办法? 希望诸位大虾帮忙啊!
急求!
那么我是不失可以理解为 我已经有了 inittg4msql 文件,其中写了例如 192.168.0.1 的ip地址,那里的数据库名字是 aaa。 我现在想要实现新建和 192.168.0.2 地址下的数据库 bbb 的连接,应该重新创建一个 类似于 inittg4msql 的脚本,例如建立一个 inittg5msql, 在 ip地址部分写入 192.168.0.2,然后数据库名字是 bbb。 tnsname.ora 和listener.ora 里面的 sid 都写入inittg5msql。
是不是这样,希望指点一下,谢谢了!
NCRO:无法执行 RSLV 连接
ORA-02063: 紧接着2 lines(源于CDA_SQLDB)出现了这种错误,不知是不是脚本写的错误,还是其他什么问题。我再仔细检查一下看看。
listener 里的一个 program = 指出写错了,现在把整体可以运行的代码间断的写出来,分享一下。
在我原有的tg4msql文件夹下,另外建立init5msql.ora脚本,代码如下:
HS_FDS_CONNECT_INFO="SERVER=XX.XXX.15.130;DATABASE=sql_db"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER在listner.ora下面,加入这样的一段:
(SID_DESC =
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = tg5msql) --这个是ora脚本的名字
(PROGRAM=tg4msql) --这里是文件夹的名字,这里原来也写作tg4msql 就出了ORA-28545错误
)在tnsnames.ora下面,加入这样的一段:
SQLDATABASE.xxx.xxx =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.4.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg5msql)
)
(HS = OK)
)
主要就是listener和tnsnames的路径不可以写错。谢谢 bobfang了!