13 Replies Latest reply on Apr 10, 2012 10:05 AM by 885802

    Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545

    885802
      Hi,
      I'm having problems for connection an Oracle 10.2.0.1 x86-64 under Linux (S.O. 64 bits) to a SQL Server 2008 64 bits, I have ORA-28545 error.
      I'm using unixodbc and odbc driver of microsoft for linux. If I do "isql -v datasource_name user password", it works, it connects and I can run querys.
      The next is configuring Oracle for using hsodbc, I've modified init file of hs, listener and tnsnames.ora of my oracle instance. I put here the changes done:

      inithsconnect.ora ($ORACLE_HOME/hs/admin). I've put trace level 4 because it doesn't work, but when I play the error, trace file is not generated.
      -----------------------------------------------------------------
      HS_FDS_CONNECT_INFO = srvxxx
      HS_FDS_TRACE_LEVEL = 4
      HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/etc/odbc.ini
      #
      # Environment variables required for the non-Oracle system
      #
      set <envvar>=<value>

      listener.ora ($ORACLE_HOME/network/admin). I've created a SID_DESC within SID_LIST_LISTENER
      --------------------------------------------------------------------------------------------------------------------------------------------
      (SID_DESC =
      (SID_NAME = hsconnect)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = hsodbc)
      (ENVS = LD_LIBRARY_PATH = /u01/app/oracle/product/10.2.0/db_1/lib32:/usr/lib:/u01/app/oracle/product/10
      .2.0/db_1/hs/lib32)
      )

      tnsnames.ora ($ORACLE_HOME/network/admin)
      --------------------------------------------------------------------
      SRVXXX=
      (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=hostname_oracle)(PORT=1521))
      (CONNECT_DATA=(SID=hsconnect))
      (HS=OK)
      )

      tnsping is working. The log of listener.log shows TNS-12518 error.
      Thanks beforehand.

      Cheers...
        • 1. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
          Mkirtley-Oracle
          Hi,
          You are seeing this problem because 10.2 HSODBC was never ported to Linux x86-64 and will not run on that platform.
          Your only alternative is to install the 11g Database Gateway for ODBC (DG4ODBC) which is the direct replacement for 10.2 HSODBC and is available on Linux x86-64.
          However, to use that version of the gateway your RDBMS needs to be at least 10.2.0.4.

          Regards,
          Mike
          1 person found this helpful
          • 2. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
            885802
            Thank you very much. I will look DG4ODBC, I will have to patch database of 10.2.0.1 to 10.2.0.4.
            Thanks.
            • 3. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
              Mkirtley-Oracle
              Okay - let us know if you have any further problems.
              Have a look at this note in My Oracle Support for further information -

              Master Note for Oracle Gateway Products (Doc ID 1083703.1)

              Regards,
              Mike
              • 4. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                885802
                One question please.
                There is dg4odbc for oracle 10g? I see a download for gateways in 10.2.0.1 here:

                http://www.oracle.com/technetwork/database/10201linx8664soft-092456.html

                It isn't dg4odbc :-?
                Or I have to download it for oracle 11g and install it in a separated home of my instance in 10g (and install patchset for my 10g instance?
                Thanks beforehand.

                Cheers...
                • 5. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                  Mkirtley-Oracle
                  Hi,
                  DG4ODBC is available from My Oracle Support as -

                  Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

                  - logon to My Oracle Support
                  - go to Patches & Updates section
                  - search for patch number 10404530 and your platform Linux x86-64
                  - click on the patch number
                  - from the download screen you only need to download the following for the gateways -

                  p10404530_112030_platform_5of7.zip

                  And yes, you install it in a completely separate ORACLE_HOME from the existing 10.2 install. You then need to install the 10.2.0.4 patchset to your 10.2.0.1 RDBMS. You need 10.2.0.4 because it includes a compulsory patch (5965763) necessary to use 11g gateways with a 10.2 RDBMS.
                  The note referred earlier has links to setup notes -

                  Master Note for Oracle Gateway Products (Doc ID 1083703.1)
                  -
                  How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) (Doc ID 561033.1)

                  and well as other download and documentation links.

                  Regards,
                  Mike
                  • 6. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                    885802
                    Hi,
                    what can you recommend me, dg4odbc or dg4msql? It is separated or I have to install always dg4odbc?
                    I've patched my 10.2.0.1 to 10.2.0.5, it is ok. I'm downloading p10404530_112030_platform_5of7.zip now.
                    Thanks beforehand.
                    • 7. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                      Mkirtley-Oracle
                      Hi,
                      There are differences between DG4MSQL and DG4ODBC which are explained in this note available in My Oracle Support -

                      Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)

                      but one of the main issues is that DG4MSQL needs a separate license whereas DG4ODBC is included in your RDBMS license, although you need to supply the third party ODBC for DG4ODBC.
                      So, you could install DG4MSQL instead of DG4ODBC but you will need to pay for a license for DG4MSQL.
                      Now your RDBMS is at 10.2.0.5 it can be used with DG4OBDC but remember to install DG4ODBC into a separate ORACLE_HOME from the existing 10.2 install.

                      Regards,
                      Mike
                      • 8. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                        885802
                        Thanks.
                        I've installed it in a separated oracle_home, it is ok. I have 10g database in /u01/app/oracle/product/10.2.0/db_1 and 11g gateway in /u01/app/oracle/product/11.2.0/gw_1.
                        I've modified the listener of gw_1 now, but how can I restart it? Both are name as LISTENER, may I change the listener name of gw? I've tried run lsnrctl of the absolute path of gw_1\bin (with ./lsnrctl from gw_1\bin path), but it shows status of my database again ...
                        Thanks beforehand.

                        P.D.: They are listening in different ports.

                        Cheers...
                        • 9. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                          Mkirtley-Oracle
                          Hi,
                          Changing the listener name would make maintenance easier. However, if you set the following ENV variables -

                          ORACLE_HOME to 11.2 OH
                          PATH to 11.2_OH/bin + any other settings but 11.2 first
                          LD_LIBRARY_PATH 11.2_OH/lib

                          then you should be able to start the 11.2 listener. If you give it a different name then you can make sure by issuing -

                          lsnrctl
                          start listener_112

                          That will avoid any problems with the 10.2 listener.

                          Regards,
                          Mike
                          • 10. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                            885802
                            Thank you very much.
                            I have all configured, but I'm having a problem, I think that it can be a bad characterset configuration.
                            I'm looking trace file and I have in the last lines:

                            OSGIP for "HS_OPEN_CURSORS" returned "50"
                            HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                            HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                            HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                            HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
                            HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
                            HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
                            HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
                            HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
                            Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
                            treat_SQLLEN_as_compiled = 1
                            Exiting hgoinit, rc=0 at 2012/04/09-13:54:47
                            Entered hgolgon at 2012/04/09-13:54:47
                            reco:0, name:xxx, tflag:0
                            Exiting hgolgon, rc=28513 at 2012/04/09-13:54:47 with error ptr FILE:hgolgon.c LINE:165 FUNCTION:hgolgon() ID
                            :Getting unicode encoding
                            Entered hgoexit at 2012/04/09-13:54:47
                            Exiting hgoexit, rc=0
                            ----------------

                            My init* file:

                            #
                            # HS init parameters
                            #
                            HS_FDS_CONNECT_INFO = xxx
                            HS_FDS_TRACE_LEVEL = DEBUG
                            HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

                            #
                            # ODBC specific environment variables
                            #
                            set ODBCINI=/etc/odbc.ini

                            #
                            # Environment variables required for the non-Oracle system
                            #
                            # set <envvar>=<value>

                            HS_NLS_NCHAR = WE8ISO8859P15
                            HS_LANGUAGE = SPANISH_SPAIN.WE8ISO8859P15
                            • 11. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                              885802
                              Update: I've deleted lines of nls in my init.ora and a "select * from dual@dblink" works! but If I do a select for a table it fails with the next error:

                              ORA-28500 - and it shows invalid columnames ...

                              I added nls lines for that error before ... Thanks beforehand.
                              • 12. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                                885802
                                Update 2: It is working with some tables ... but with another tables shows the error commented before, invalid column name.
                                Thanks beforehand.
                                • 13. Re: Oracle 10.2.0.1 x86-64 to SQL Server 2008 (64bits) - ORA-28545
                                  885802
                                  I've fixed all finally.
                                  Querys to tables with error of invalid column name, it is because the column name has 30 characters or more ... For fixing it you have to put the owner before table name, for example "dbo"."table_name". Another better solution is creating a view ... I'm using this second solution, because in some tables, although I put owner, it doesn't work ...
                                  Thanks for all!