11 Replies Latest reply: May 13, 2013 5:01 AM by 1007719 RSS

    DB-Link from Oracle DB to Sybase

    1007719
      We need to create DB-link from Oracle DB to Sybase (DB supports and changes on Sybase, but we need to get reports from the DB from Oracle)

      I`ve tried:
      1.     I use two machines: gateway and DB server.
      2.     On gateway: install Oracle Gateway for Sybase with right parameters(hostName,port,databaseName)
      3.     On gateway: infill listener.ora

      LISTENER_GW =
      (ADDRESS_LIST =
      (ADDRESS= (PROTOCOL=tcp)
      (HOST = localhost)
      (PORT = 1521)
      )

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (PROGRAM = dg4sybs)
      (ORACLE_HOME = D:\oracle\11.2.0\gw)
      (SID_NAME = GW)
      )
      )
      4.     On gateway: restart listener
      5.     On gateway: infill initdg4sybs.ora:
      HS_FDS_CONNECT_INFO=[10.250.67.166]:2640/dwhdb
      HS_FDS_TRACE_LEVEL=OFF
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER
      6.     On DBServer: add to tnsnames.ora:
      GSMLD=
      (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=<Hostname>)(PORT=<Port>))
      (CONNECT_DATA=(SID=GW))
      (HS=OK) ) )
      7.     On DBServer: test
      tnsping gsmld

      OK (10 msec)

      8.     On DBServer: Create db-link:
      create public database link gsmld connect to <UserName> identified by "<Password>" using 'GSMLD';
      9.     Test db-link:
      select * from dba_tab_columns@gsmld;
      Error is occurred:
      ERROR at line 1:
      ORA-28513: internal error in heterogeneous remote agent
      ORA-02063: preceding line from GSMLD
      10.     From DBServer:
      $sqlplus /NOLOG


      SQL> conn dcbo@gsmld
      Enter password:
      ERROR:
      ORA-28547: connection to server failed, probable Oracle Net admin error

      11.     From gateway:
      sqlplus /NOLOG
      SQL> conn <UserName>@gsmld
      Enter password:
      ERROR:
      ORA-28547: connection to server failed, probable Oracle Net admin error
      SQL> quit

      Help me please to understand where my mistake is.
      Thank you in advance.
        • 1. Re: DB-Link from Oracle DB to Sybase
          Mkirtley-Oracle
          Hi,
          There is a problem with your listener setup. You have -

          LISTENER_GW =

          but then -

          SID_LIST_LISTENER =

          THis needs to be changed to -

          SID_LIST_LISTENER_GW =

          then stop and start the listener.
          It would also be better to change the HSOT in the listener setup from 'localhost' to the actual host name, and make ssure that the host and port in the tnsnames.ora match those in the listener.ora.
          There is also a problem with the gateway init file.
          In listener.ora you have -

          (SID_NAME = GW)

          so the init file should be called -

          initGW.ora

          to match the SID in the listener.
          If you want to call the file initdg4sybs.ora then change the listener to -
          (SID_NAME = dg4sybs)
          and stop and start the listener.

          You have also tried -

          SQL> conn dcbo@gsmld

          but this is not supported for gateway connections. You cannot connect directly to the non-Oracle database and only use the connection in database links.
          You tried this and got the error -

          select * from dba_tab_columns@gsmld;
          Error is occurred:
          ERROR at line 1:
          ORA-28513: internal error in heterogeneous remote agent
          ORA-02063: preceding line from GSMLD

          Can you try this again after changing the listener setup but try it as -

          select * from dual@gsmld;

          Regards,
          Mike
          • 2. Re: DB-Link from Oracle DB to Sybase
            1007719
            Thank you, Mike.

            I’ve made as you advise me.
            But anyway I receive the error:
            sqlplus.exe /NOLOG
            SQL*Plus: Release 11.2.0.1.0 Production on Wed May 8 15:35:30 2013

            Copyright (c) 1982, 2010, Oracle. All rights reserved.

            SQL> conn system@chelrepdb
            Enter password:
            Connected.

            SQL> select * from dba_tab_columns@gsmld;
            select * from dba_tab_columns@gsmld
            *
            ERROR at line 1:
            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 GSMLD

            The dblink “gsmld” was created in chelrepdb.

            What I can see additionally?
            • 3. Re: DB-Link from Oracle DB to Sybase
              1007719
              And for the statement:
              SQL> select * from dual@gsmld;
              select * from dual@gsmld
              *
              ERROR at line 1:
              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 GSMLD

              Does the table dual exist in Sybase?

              Edited by: user12008359 on 08.05.2013 3:27
              • 4. Re: DB-Link from Oracle DB to Sybase
                Mkirtley-Oracle
                Hi,
                Thanks for making the suggested changes.
                The table 'dual' is in effect a virtual table with the gateways and a select usually works.
                However, the ORA_28545 error you are seeing is usually caused by configuration errors and you should see the same error if you tried something like -

                select * from "owner"."table_name"@gsmld;

                To check further could you post the following information -

                - gateway listener.ora
                - output from lsnrctl status <gtw_listener>
                - gateway init<sid>.ora
                - tnsnames.ora - this should be on the RDBMS side, usually in $ORACLE_HOME/network/admin
                - full create gateway database link statement. You should replace the actual user and password with dummy words but post the actual statement.

                Regards,
                Mike
                • 5. Re: DB-Link from Oracle DB to Sybase
                  1007719
                  Gateway listener.ora:

                  LISTENER_GW =
                  (ADDRESS_LIST =
                  (ADDRESS= (PROTOCOL=tcp)
                  (HOST = 10.182.5.162)
                  (PORT = 1521)
                  )


                  SID_LIST_LISTENER_GW =
                  (SID_LIST =
                  (SID_DESC =
                  (PROGRAM = dg4sybs)
                  (ORACLE_HOME = D:\oracle\11.2.0\gw)
                  (SID_NAME = GW)
                  )
                  )

                  - output from lsnrctl status <gtw_listener>

                  C:\Users\statistic>lsnrctl

                  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 08-MAY-2013 16:37
                  :43

                  Copyright (c) 1991, 2010, Oracle. All rights reserved.

                  Welcome to LSNRCTL, type "help" for information.

                  LSNRCTL> status
                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                  STATUS of the LISTENER
                  ------------------------
                  Alias LISTENER
                  Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
                  ction
                  Start Date 08-MAY-2013 15:34:33
                  Uptime 0 days 1 hr. 3 min. 14 sec
                  Trace Level off
                  Security ON: Local OS Authentication
                  SNMP OFF
                  Listener Parameter File D:\oracle\11.2.0\gw\network\admin\listener.ora
                  Listener Log File d:\oracle\11.2.0\gw\log\diag\tnslsnr\gsm-statistic\lis
                  tener\alert\log.xml
                  Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gsm-statistic)(PORT=1521)))
                  The listener supports no services
                  The command completed successfully
                  LSNRCTL>

                  - gateway init<sid>.ora
                  HS_FDS_CONNECT_INFO=[<HostName>]:<Port>/<DatabaseName>
                  HS_FDS_TRACE_LEVEL=OFF
                  HS_FDS_RECOVERY_ACCOUNT=RECOVER
                  HS_FDS_RECOVERY_PWD=RECOVER-

                  tnsnames.ora - this should be on the RDBMS side, usually in $ORACLE_HOME/network/admin
                  GSMLD =
                  (DESCRIPTION=
                  (ADDRESS=(PROTOCOL=tcp)(HOST==[<HostName>)(PORT=<Port>))
                  (CONNECT_DATA=(SID=GW))
                  (HS=OK)
                  )

                  - full create gateway database link statement
                  create public database link gsmld
                  connect to <UserName> identified by "<Password>" using 'GSMLD';
                  • 6. Re: DB-Link from Oracle DB to Sybase
                    Mkirtley-Oracle
                    Hi,
                    Your listener is called LISTENER_GW so you should issue the commands as -

                    lsnrctl
                    status LISTENER_GW
                    or also
                    stop LISTENER_GW
                    start LISTENER_GW

                    This should show the 'GW' service running.

                    The gateway init file should be called initGW.ora so can you check that is the actual name.

                    In tnsnames.ora the values for -
                    (HOST==[<HostName>)(PORT=<Port>)
                    should be -
                    (HOST=10.182.5.162)(PORT=1521)
                    Note that you had an extra '=' sign which would cause problems.
                    Finally, the database link statement should have double quotes round the username and password to preserve the case sent to Sybase -

                    connect to "<UserName>" identified by "<Password>" using 'GSMLD';

                    Regards,
                    Mike
                    • 7. Re: DB-Link from Oracle DB to Sybase
                      1007719
                      1.
                      LSNRCTL> status LISTENER_GW
                      TNS-01150: The address of the specified listener name is incorrect
                      NL-00303: syntax error in NV string

                      2. The init.ora file called initGW.ora. By the way, it must be in D:\oracle\11.2.0\gw\hs\admin or in D:\oracle\11.2.0\gw\dg4sybs\admin\ ?

                      3. With the tnsnames.ora all right, without errors, as you advice.
                      • 8. Re: DB-Link from Oracle DB to Sybase
                        Mkirtley-Oracle
                        Hi,
                        It looks like there is a problem with the syntax of the listener.ora file.
                        In the thread it looks like this -

                        LISTENER_GW =
                        (ADDRESS_LIST =
                        (ADDRESS= (PROTOCOL=tcp)
                        (HOST = 10.182.5.162)
                        (PORT = 1521)
                        )

                        SID_LIST_LISTENER_GW =
                        (SID_LIST =
                        (SID_DESC =
                        (PROGRAM = dg4sybs)
                        (ORACLE_HOME = D:\oracle\11.2.0\gw)
                        (SID_NAME = GW)
                        )
                        )

                        but there should be at least 1 space at the beginning of each line in the actual file except the first in each section, so it should look like -

                        LISTENER_GW =
                        <space>(ADDRESS_LIST =
                        <space>(ADDRESS= (PROTOCOL=tcp)
                        <space>(HOST = 10.182.5.162)
                        <space>(PORT = 1521)
                        <space>)
                        <space>) <=== extra bracket here

                        SID_LIST_LISTENER_GW =
                        <space>(SID_LIST =
                        <space>(SID_DESC =
                        <space>(PROGRAM = dg4sybs)
                        <space>(ORACLE_HOME = D:\oracle\11.2.0\gw)
                        <space>(SID_NAME = GW)
                        <space>)
                        <space>)

                        and it also needs an extra bracket in the 'address list' section.
                        You can add as many spaces "<space>" as you prefer to format the file to make it more readable.

                        Could you also make sure that the tnsnames.ora entry has spaces on all lines except the first ?

                        Please make the change and stop and start the LISTENER_GW and check for services.

                        Because you are using DG4Sybase the initGW.ora should be in D:\oracle\11.2.0\gw\dg4sybs\admin. The "hs/admin" directory is for DG4ODBC configuration files.
                        The listener.ora should be in D:\oracle\11.2.0\gw\network/admin.

                        Regards,
                        Mike
                        • 9. Re: DB-Link from Oracle DB to Sybase
                          1007719
                          Hi, Mike!
                          My listener.ora file is:
                          LISTENER_GW =
                          <spaces> (ADDRESS_LIST =
                          <spaces> (ADDRESS= (PROTOCOL=tcp)
                          <spaces> (HOST = 10.182.5.162)
                          <spaces> (PORT = 1521)
                          <spaces> )
                          <spaces> )
                          SID_LIST_LISTENER_GW =
                          <spaces> (SID_LIST =
                          <spaces> (SID_DESC =
                          <spaces> (PROGRAM = dg4sybs)
                          <spaces> (ORACLE_HOME = D:\oracle\11.2.0\gw)
                          <spaces> (SID_NAME = GW)
                          <spaces> )
                          <spaces> )
                          (with spaces and “excess” right bracket).
                          And:
                          lsnrctl status listener_gw
                          LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 13-MAY-2013 09:10
                          :30

                          Copyright (c) 1991, 2010, Oracle. All rights reserved.

                          Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=10.182.5.162)(PORT=1521))
                          STATUS of the LISTENER
                          ------------------------
                          Alias LISTENER
                          Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
                          ction
                          Start Date 13-MAY-2013 09:08:54
                          Uptime 0 days 0 hr. 1 min. 35 sec
                          Trace Level off
                          Security ON: Local OS Authentication
                          SNMP OFF
                          Listener Parameter File D:\oracle\11.2.0\gw\network\admin\listener.ora
                          Listener Log File d:\oracle\11.2.0\gw\log\diag\tnslsnr\gsm-statistic\lis
                          tener\alert\log.xml
                          Listening Endpoints Summary...
                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gsm-statistic)(PORT=1521)))
                          The listener supports no services
                          The command completed successfully
                          LSNRCTL> services
                          Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                          The listener supports no services
                          The command completed successfully
                          tnsping gsmld
                          TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 13-MAY-2
                          013 09:10:40

                          Copyright (c) 1997, 2010, Oracle. All rights reserved.

                          Used parameter files:
                          D:\oracle\11.2.0\gw\network\admin\sqlnet.ora


                          Used TNSNAMES adapter to resolve the alias
                          Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=10.182.5.162)(P
                          ORT=1521)) (CONNECT_DATA=(SID=GW)) (HS=OK))
                          OK (0 msec)
                          sqlplus /NOLOG
                          SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 09:17:16 2013

                          Copyright (c) 1982, 2010, Oracle. All rights reserved.

                          SQL> conn system
                          Enter password:
                          ERROR:
                          ORA-12560: TNS:protocol adapter error


                          What is wrong?
                          Thank you in advance.

                          Edited by: AlexeyKuzmin on 12.05.2013 20:27
                          • 10. Re: DB-Link from Oracle DB to Sybase
                            Mkirtley-Oracle
                            Alexey,
                            What is the output from -

                            lsnrctl
                            stop listener_gw
                            start listener_gw
                            status listener_gw
                            services listener_gw

                            If you still have no services then are there any errors in the log file -

                            d:\oracle\11.2.0\gw\log\diag\tnslsnr\gsm-statistic\listener\alert\log.xml

                            Regards,
                            Mike
                            • 11. Re: DB-Link from Oracle DB to Sybase
                              1007719
                              Hi, Mike!
                              Thank you VERY MUCH for your help!
                              I create listener:
                              LSNRCTL> status
                              Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                              STATUS of the LISTENER
                              ------------------------
                              Alias LISTENER_GW
                              Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
                              ction
                              Start Date 13-MAY-2013 15:33:46
                              Uptime 0 days 0 hr. 11 min. 3 sec
                              Trace Level off
                              Security ON: Local OS Authentication
                              SNMP OFF
                              Listener Parameter File D:\oracle\11.2.0\gw\network\admin\listener.ora
                              Listener Log File d:\oracle\11.2.0\gw\diag\tnslsnr\gsm-statistic\listene
                              r_gw\alert\log.xml
                              Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gsm-statistic)(PORT=1521)))
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
                              Services Summary...
                              Service "GW" has 1 instance(s).
                              Instance "GW", status UNKNOWN, has 1 handler(s) for this service...
                              The command completed successfully
                              tnsping works ok from gateway and from DB server:
                              tnsping gsmld
                              Used TNSNAMES adapter to resolve the alias
                              Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=10.182.5.162)(P
                              ORT=1521)) (CONNECT_DATA=(SID=GW)) (HS=OK))
                              OK (0 msec)
                              But when I try to create DBlink:
                              create public database link gsmld connect to <…> identified by "<…>" using 'GSMLD';
                              select * from dba_tab_columns@gsmld;
                              ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                              [Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]SQL Anywhere Error -143:
                              Column '@@maxpagesize' not found {42S22,NativeErr = 207}[Oracle][ODBC Sybase
                              Wire Protocol driver][Sybase ASE]SQL Anywhere Error -265: Procedure
                              'sp_server_info' not found {HY000,NativeErr = 504}
                              ORA-02063: preceding 2 lines from GSMLD
                              What does it mean?
                              Could you help me?