oracle11ggateway连接sqlserver2005

安装好oracle 11g transport gateway，数据库实例版本为oracle 10g database,同是windows 2008 R2 64bit平台上的。

1.在tg_1\dg4msql\admin新建一个init+[sid].ora文件，命名为initefCQQC.ora,实例名为CQQC。编辑文件内容如下:

HS_FDS_CONNECT_INFO=100.100.1.11//CQQC
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

其中100.100.1.10为sqlserver所在的host，CQQC为databasename。

2.因为和原先的database为同一台，安装完gateway后，会自动新建一个11g的listener。编辑内容如下:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.11)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CQQC)
      (ORACLE_HOME = d:\oracle\product\11.2.0\tg_1)
      (PROGRAM = dg4msql)
    )
  )

3.tnsnames.ora 添加如下内容

CQQC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CQQC)
    )
    (HS = OK)
  )

4建立dblink

create public database link CQQC
  connect to test indentified by test
  using 'CQQC';

 

建立dblink，查询相关语句后报ORA-28545:Error Diagnosed By Net8 When Connecting To An Agent

解决方式如下:

将gateway的listener换个端口号：listener内容编辑如下:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.11)(PORT =1523))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CQQC)
      (ORACLE_HOME = d:\oracle\product\11.2.0\tg_1)
      (PROGRAM = dg4msql)
    )
  )

tnsnames.ora中的服务端口号也改掉

CQQC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.11)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SID = CQQC)
    )
    (HS = OK)
  )

 

重启监听后OK,可以查询。