1 Reply Latest reply: Apr 14, 2010 3:18 AM by Kai-Oracle RSS

    ORA-28545: error diagnosed by Net8 when connecting to an agent

    766533
      Hi,


      I've to setup the Oracle Gateway service in order to access the SQL Server 2005 db from Oracle11g 11.1.0.7.0 db which is running on a LINUX os.

      Basically, i've to periodically PULL data from SQL Server to oracle.

      Have done the necessary setup as specified in Oracle docs.


      1. Oracle ini file: initdg4msql.ora

      HS_FDS_CONNECT_INFO=999.168.0.1:1433//SqlServerDb
      HS_FDS_TRACE_LEVEL=OFF
      HS_FDS_RECOVERY_ACCOUNT=sqlserver_user_id
      HS_FDS_RECOVERY_PWD=sqlserver_user_password



      2. listener.ora entry

      LISTENER_GATEWAY =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.portal.internal)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      )
      )

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = OBIM1)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      )
      (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      (ENVS=LD_LIBRARY_PATH=/app/oracle/product/11.1.0/db_1/dg4msql/driver/lib;/app/oracle/product/11.1.0/db_1/lib)
      (PROGRAM = dg4msql)
      )
      )


      3. tnsnames.ora entry

      dg4msql =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = tcp)(HOST = oracle.portal.internal)(PORT = 1522))
      (CONNECT_DATA =(SID = dg4msql))
      (HS = OK)
      )


      4. Create database link on oracle

      create public database link sqlserver connect to "sqlserver_user_id" identified by "sqlserver_user_password" using 'dg4msql';

      Database link created.


      -------------------------------------------------------------------------------------

      Upon the completion of the configuration, I did the following and all are successful and working:

      - starting the listener by

      lsnrctl start LISTENER_GATEWAY

      - tnsping my gateway

      tnsping dg4msql


      However, when I tried to retreive data and got the following error:

      SQL> desc myTestingTable@sqlserver;
      ERROR:
      ORA-28545: error diagnosed by Net8 when connecting to an agent
      Unable to retrieve text of NETWORK/NCR message 65535
      ORA-02063: preceding 2 lines from SQLSERVER


      I have stepped through the doc of "How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11" but unable to figure out where the error lies.

      Any inputs on this?? please help, I am in urgent.