3 Replies Latest reply: Jun 26, 2013 12:31 AM by Kgronau-Oracle RSS

    how to resolve ora-28500 & ora-02063?

    user1122898

      HI guys, recently, I have to read the data in MS SQL2008 into the oracle11gr2. At the first glance, I thought about the GateWay.

      there are two servers in my team(run on vmware), which are:

      server A:  192.168.184.130, winserver2003, 64 edition, MS server2008 installed, SP2 installed

      server B:  192.168.184.134, winserver2003, 64 edition,  oracle11gr2 and gateway installed, SP1.

       

      on server A, the table of msdb.dbo.user_sqlserver2008 is the one the Oracle plans to read.

       

      on server B, the ORACLE_HOME is C:\app\Administrator\product\11.2.0\dbhome_1, and the GATEWAY_HOME is C:\product\11.2.0\tg_1, both are default directories.

       

      after the gateway component was installed, I configured the following files:

      1. created C:\product\11.2.0\tg_1\dg4msql\admin\initmsdb.ora, the contents in this file are:

      # This is a customized agent init file that contains the HS parameters

      # that are needed for the Database Gateway for Microsoft SQL Server

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO=192.168.184.130//msdb

      HS_FDS_TRACE_LEVEL=OFF

      HS_FDS_RECOVERY_ACCOUNT=RECOVER

      HS_FDS_RECOVERY_PWD=RECOVER

       

      2. compiled the file of C:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora, and the contents are:

      # listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora

      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = msdb)

            (ORACLE_HOME = C:\product\11.2.0\tg_1)   

            (PROGRAM = dg4msql)

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.134)(PORT = 1523))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))

          )

        )

       

      ADR_BASE_LISTENER = C:\product\11.2.0\tg_1

       

      3. modified the tns file in C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN, namely tnsnames.ora, the contents are:

      # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

      # Generated by Oracle configuration tools.

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      ORCL =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.134)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = orcl)

          )

        )

       

      msdb =

      (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.184.134)(PORT = 1523))

          )

          (CONNECT_DATA =

            (SID = msdb)

          )

          (HS = OK)

      )

       

      after that, restarted the listeners for gateway and for orcl instance and the orcl instance as well,  then on server B, logon as the user msdb possessing the resource and dba role,(what's more, there is another user id msdb on sqlserver2008, who is the owner of msdb database)

      ran the following sql script via plsql:

      1. create database link msdb connect to msdbadmin identified by "oracle_4u" using 'msdb';

      2. select * from dbo.user_sqlserver2008@msdb

      the script No.1 was successfully completed, while an error message was got in script 2. the message is:

      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

      [Oracle][[ODBC SQL Server Driver][DBNETLIB]SQL Server dose not exist or access denied. {08001, NativeErr = 17}[Oracle][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()). {01000, NativeErr = 2}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute{01S000}

      ORA-02063: preceding 2 lines from MSDB.

       

      Could anyone familiar with this help me figure this out? Thanks, it made me crazy.

        • 1. Re: how to resolve ora-28500 & ora-02063?
          Kgronau-Oracle

          The relevant part of the error message is "SQL Server dose not exist or access denied" which means that the SQL Server you configured in the gateway init file does not exist or uses a different port.

          So before you continue to investigate check out the SQL Server details you configured:"192.168.184.130//msdb"

          Is the SQl Server you want to access really running on host: 192.168.184.130

          You didn't specify a port number in the connect string so it is assume that your SQL Server listens on 1433 port - is this correct? Can you telnet to 192.168.184.130 on port 1433?

          Can you connect from any PC to the SQL Server using a MS tool or using MS ODBC Test?

           

          - Klaus

          • 2. Re: how to resolve ora-28500 & ora-02063?
            user1122898

            according to the information I searched on internet, it said that you do not need to specify the port number if your sqlserver uses the default port number, namely 1433, so if just let it blank, and these kinda configuration did not work.

            thanks for you answer giving me some fresh air, this time, I specified the port number (1433) on both server, namely HS_FDS_CONNECT_INFO=192.168.184.130:1433//msdb and changed port from blank to 1433 for TCP port attribution, so , it works.

            so here is the conclusion, according to my desperate experience, it seems that you have to configure the port number both on gateway ini file and on sqlserver side, not use the default value(blank).

            • 3. Re: how to resolve ora-28500 & ora-02063?
              Kgronau-Oracle

              On the gateway side you don't need to specify the port number when your SQL Server is listening on the default port. But in your update you also mentioned that you had to configure also the SQL Server to use port 1433 - this indicates that it wasn't configured earlier ... so the SQL Server was using dynamic ports and when you use dynamic ports and the gateway you have to make sure on the SQL Server side the SQL Browser service is running AND you specify the instance name in your HS_FDS_CONNECT_INFO like HS_FDS_CONNECT_INFO=192.168.184.130/<instance name of the SQL Server>/msdb

              - Klaus



              P.S.: Please mark the thread as answered if your issue is solved.