9 Replies Latest reply: Mar 11, 2013 7:00 AM by Kgronau-Oracle RSS

    HS to postgre ORA-28511

    Robo [Maind]
      Hi,

      select from the postgre databse works just fine (however when i select data column alone it returns as date, if i select it together with som condition in where clause it
      returns varchar).


      -- this runs ok
      select
      t."idbill",
      t."idpoint"
      from "crm"."bills2points"@dl_carecloud t
      ,"crm"."bills"@dl_carecloud brg
      where 1=1
      and t."idbill" = brg."idbill"
      and t."idbill" = 9685;

      -- fails on commit with ORA-28511
      insert into rho_INT_BILLS2POINTS
      select
      t."idbill",
      t."idpoint"
      from "crm"."bills2points"@dl_carecloud t
      ,"crm"."bills"@dl_carecloud brg
      where 1=1
      and t."idbill" = brg."idbill"
      and t."idbill" = 9685;

      commit;

      INIT file:
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = carecloud
      #HS_FDS_TRACE_LEVEL = DEBUG - chyba
      #HS_FDS_TRACE_LEVEL = OFF #255 #ON
      HS_FDS_TRACE_LEVEL = ODBC
      HS_FDS_FETCH_ROWS = 1
      HS_RPC_FETCH_REBLOCKING = OFF

      HS_FDS_SUPPORT_STATISTICS = FALSE
      HS_FDS_TRANSACTION_MODEL = READ_ONLY
      HS_FDS_TRANSACTION_ISOLATION = NONE
      HS_KEEP_REMOTE_COLUMN_SIZE = REMOTE
      HS_FDS_QUOTE_IDENTIFIER = TRUE

      #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbcpsql.so
      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
      HS_LANGUAGE = SLOVAK_SLOVAKIA.EE8ISO8859P2
      HS_NLS_NCHAR = UCS2
      HS_FDS_SQLLEN_INTERPRETATION = 64

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

      set LD_LIBRARY_PATH=/usr/lib:/usr/lib64:/usr/local/lib:/usr/pgsql-9.1/lib:/u01/app/oracle/product/11.2.0.3/dbhome_1/lib




      TRACE:
      Oracle Corporation --- THURSDAY FEB 14 2013 14:28:38.705

      Version 11.2.0.3.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ODBC"
      Entered hgosdip
      setting HS_OPEN_CURSORS to default of 50
      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
      setting HS_FDS_RECOVERY_PWD to default value
      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
      setting HS_IDLE_TIMEOUT to default of 0
      HOSGIP returned value of "NONE" for HS_FDS_TRANSACTION_ISOLATION
      HOSGIP returned value of "UCS2" for HS_NLS_NCHAR
      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
      setting HS_FDS_DATE_MAPPING to default of "DATE"
      HOSGIP returned value of "OFF" for HS_RPC_FETCH_REBLOCKING
      HOSGIP returned value of "1" for HS_FDS_FETCH_ROWS
      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
      setting HS_FDS_MAP_NCHAR to default of "TRUE"
      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
      setting HS_FDS_QUERY_DRIVER to default of "TRUE"
      HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
      HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
      HOSGIP returned value of "REMOTE" for HS_KEEP_REMOTE_COLUMN_SIZE
      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
      HOSGIP returned value of "64" for HS_FDS_SQLLEN_INTERPRETATION
      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
      setting HS_FDS_DELAYED_OPEN to default of "TRUE"
      setting HS_FDS_WORKAROUNDS to default of "0"
      Exiting hgosdip, rc=0
      ORACLE_SID is "carecloud"
      Product-Info:
      Port Rls/Upd:3/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.3.0_0011, Instance:carecloud
      Exiting hgogprd, rc=0
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=32
      HOCXU_DRV_NCHAR=1000
      HOCXU_DB_CSET=873
      HS_LANGUAGE is SLOVAK_SLOVAKIA.EE8ISO8859P2
      LANG=en_US.UTF-8
      HOCXU_SEM_VER=112000
      Entered hgolofn at 2013/02/14-14:28:38
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
      Entered hgolofns at 2013/02/14-14:28:38
      libname=/usr/lib64/libodbc.so, funcname=SQLAllocHandle
      symbol_peflctx=0x6bf3f300
      hoaerr:0
      Exiting hgolofns at 2013/02/14-14:28:38
      Entered hgolofns at 2013/02/14-14:28:38
      libname=/usr/lib64/libodbc.so, funcname=SQLBindCol
      symbol_peflctx=0x6bf3f390
      hoaerr:0
      Exiting hgolofns at 2013/02/14-14:28:38
      Entered hgolofns at 2013/02/14-14:28:38
      libname=/usr/lib64/libodbc.so, funcname=SQLBindParameter
      symbol_peflctx=0x6bf3fb50
      hoaerr:0

      ***********************

      Entered hgoftch, cursor id 1 at 2013/02/14-14:28:40
      hgoftch, line 133: Printing hoada @ 0x269d9b8
      MAX:4, ACTUAL:4, BRC:1, WHT=5 (SELECT_LIST)
      DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
      4 INTEGER Y 4 4 0/ 0 0 0 0 idbill
      91 DATE Y 16 16 0/ 0 0 0 0 statestamp
      4 INTEGER Y 4 4 0/ 0 0 0 0 idbill
      4 INTEGER Y 4 4 0/ 0 0 0 0 idpoint
      SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
      SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)
      SQLFetch: row: 1, column 2, bflsz: 16, bflar: 16
      SQLFetch: row: 1, column 2, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)
      SQLFetch: row: 1, column 3, bflsz: 4, bflar: 4
      SQLFetch: row: 1, column 3, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)
      SQLFetch: row: 1, column 4, bflsz: 4, bflar: 4
      SQLFetch: row: 1, column 4, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)
      1 rows fetched
      Exiting hgoftch, rc=0 at 2013/02/14-14:28:40
      Entered hgoftch, cursor id 1 at 2013/02/14-14:28:40
      hgoftch, line 133: Printing hoada @ 0x269d9b8
      MAX:4, ACTUAL:4, BRC:1, WHT=5 (SELECT_LIST)
      DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
      4 INTEGER Y 4 4 0/ 0 0 0 0 idbill
      91 DATE Y 16 16 0/ 0 0 0 0 statestamp
      4 INTEGER Y 4 4 0/ 0 0 0 0 idbill
      4 INTEGER Y 4 4 0/ 0 0 0 0 idpoint
      0 rows fetched
      Exiting hgoftch, rc=1403 at 2013/02/14-14:28:40
      Entered hgoclse, cursor id 1 at 2013/02/14-14:28:40
        • 1. Re: HS to postgre ORA-28511
          Kgronau-Oracle
          It would be good to get a full ODBC trace to see the binding.
          When you're using unixODBC Driver Manager, please add to your odbcinst.ini an [ODBC] section containing:

          [ODBC]
          TraceFile=/tmp/sql.log
          Trace=1

          This will create a trace file in /tmp called sql.log

          Now make sure you open a new SQL*Plus session and perform again the select (w/o insert). Afterwards do the insert as select.

          - Klaus
          • 2. Re: HS to postgre ORA-28511
            Robo [Maind]
            Hi Klaus,

            I have prepared another example with "shorter" output:

            /etc/odbcinst.ini
            [PostgreSQL2]
            Description = ODBC for PostgreSQL
            Driver = /usr/local/lib/psqlodbcw.so
            Driver64 = /usr/local/lib/psqlodbcw.so
            Setup = /usr/lib64/libodbcpsqlS.so
            Setup64 = /usr/lib64/libodbcpsqlS.so
            FileUsage = 1

            /etc/odbc.ini
            [carecloud]
            Driver = PostgreSQL2
            Database = tmr_backup
            Servername = 172.18.100.1
            Port = 5432
            ReadOnly = Yes
            Username = maind
            Password = *****
            UseDeclareFetch = 1

            /u01/app/oracle/product/11.2.0.3/dbhome_1/hs/admin/
            HS_FDS_CONNECT_INFO = carecloud
            HS_FDS_TRACE_LEVEL = ODBC
            HS_FDS_FETCH_ROWS = 1

            HS_FDS_SUPPORT_STATISTICS = FALSE
            HS_FDS_TRANSACTION_MODEL = READ_ONLY
            HS_FDS_TRANSACTION_ISOLATION = NONE
            HS_KEEP_REMOTE_COLUMN_SIZE = REMOTE
            HS_FDS_QUOTE_IDENTIFIER = TRUE

            HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
            HS_LANGUAGE = SLOVAK_SLOVAKIA.EE8ISO8859P2
            HS_FDS_SQLLEN_INTERPRETATION = 64

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

            set LD_LIBRARY_PATH=/usr/lib:/usr/lib64:/usr/local/lib:/usr/pgsql-9.1/lib:/u01/app/oracle/product/11.2.0.3/dbhome_1/lib

            /u01/app/11.2.0.3/grid/network/admin/listener.ora
            SID_LIST_LISTENERDG4=
            (SID_LIST=
            (SID_DESC=
            (SID_NAME=carecloud)
            (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1)
            (PROGRAM=dg4odbc)
            (ENVS=LD_LIBRARY_PATH=/usr/lib:/usr/lib64:/usr/pgsql-9.1/lib:/u01/app/oracle/product/11.2.0.3/dbhome_1/lib)
            )
            )

            /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/tnsnames.ora
            CARECLOUD =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = odamaind1)(PORT = 1621))
            (CONNECT_DATA =
            (SID = carecloud)
            )
            (HS = OK)
            )

            sqlplus sys@orcl as sysdba
            SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 7 14:50:29 2013

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

            Enter password:

            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
            Data Mining and Real Application Testing options

            SQL> insert into carecloud_owner.msl_test_x SELECT * FROM "crm"."bills"@dl_carecloud WHERE "statestamp" >= to_date('20130307', 'yyyymmdd') AND "statestamp" < to_date('20130308', 'yyyymmdd');

            44 rows created.

            SQL> commit;
            commit
            *
            ERROR at line 1:
            ORA-28511: lost RPC connection to heterogeneous remote agent using
            SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
            SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=odamaind1)(PORT=1621))(CONNECT_DAT
            A=(SID=carecloud)))
            ORA-02063: preceding line from DL_CARECLOUD
            Process ID: 23822
            Session ID: 638 Serial number: 31779

            sql log and gateway trace are too big, so I have linked them:

            /tmp/sql.log
            [http://62.168.68.20/sql.log]

            /u01/app/oracle/product/11.2.0.3/dbhome_1/hs/log/carecloud_agt_23912.trc
            [http://62.168.68.20/carecloud_agt_23912.trc]
            • 3. Re: HS to postgre ORA-28511
              Kgronau-Oracle
              Looking at the trace shows:
              ...
              DriverName:psqlodbcw.so, DriverVer:09.01.0200
              DBMS Name:PostgreSQL, DBMS Version:9.1.4
              ...
              DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
              4 INTEGER Y 4 4 0/ 0 0 0 0 idbill
              91 DATE Y 16 16 0/ 0 0 0 0 paymentdate
              4 INTEGER Y 4 4 0/ 0 0 0 0 count_transactions
              7 REAL Y 4 4 24/ 0 0 0 0 totalprice
              4 INTEGER Y 4 4 0/ 0 0 0 0 idcard
              4 INTEGER Y 4 4 0/ 0 0 0 0 idcont
              4 INTEGER Y 4 4 0/ 0 0 0 0 idstore
              12 VARCHAR Y 255 255 0/ 0 0 0 200 external_idbill
              12 VARCHAR Y 255 255 0/ 0 0 0 200 numberofchashdesk
              4 INTEGER Y 4 4 0/ 0 0 0 0 idcurrency
              4 INTEGER Y 4 4 0/ 0 0 0 0 idpartner
              4 INTEGER Y 4 4 0/ 0 0 0 0 idtype
              4 INTEGER Y 4 4 0/ 0 0 0 0 stateuser
              91 DATE Y 16 16 0/ 0 0 0 0 statestamp

              it executes the select:
              SQL text from hgopars, id=1, len=288 ...
              000: 53454C45 43542022 69646269 6C6C222C [SELECT "idbill",]
              010: 22706179 6D656E74 64617465 222C2263 ["paymentdate","c]
              020: 6F756E74 5F747261 6E736163 74696F6E [ount_transaction]
              030: 73222C22 746F7461 6C707269 6365222C [s","totalprice",]
              040: 22696463 61726422 2C226964 636F6E74 ["idcard","idcont]
              050: 222C2269 6473746F 7265222C 22657874 [","idstore","ext]
              060: 65726E61 6C5F6964 62696C6C 222C226E [ernal_idbill","n]
              070: 756D6265 726F6663 68617368 6465736B [umberofchashdesk]
              080: 222C2269 64637572 72656E63 79222C22 [","idcurrency","]
              090: 69647061 72746E65 72222C22 69647479 [idpartner","idty]
              0A0: 7065222C 22737461 74657573 6572222C [pe","stateuser",]
              0B0: 22737461 74657374 616D7022 2046524F ["statestamp" FRO]
              0C0: 4D202263 726D222E 2262696C 6C732220 [M "crm"."bills" ]
              0D0: 57484552 45202273 74617465 7374616D [WHERE "statestam]
              0E0: 70223E3D 27323031 332D3033 2D303720 [p">='2013-03-07 ]
              0F0: 30303A30 303A3030 2720414E 44202273 [00:00:00' AND "s]
              100: 74617465 7374616D 70223C27 32303133 [tatestamp"<'2013]
              110: 2D30332D 30382030 303A3030 3A303027 [-03-08 00:00:00']

              and the gateway trace clearly shows it fetches row by row until there's no more data to fetch and the gateway returns the 1403 "error":
              Exiting hgoftch, rc=1403 at 2013/03/07-14:51:30


              When you do the insert as select, do you get anything logged into the alert.log file of the Oracle database? Was the Oracle database 11.2 migrated from an earlier release like 10.2 to 11.2?



              12 VARCHAR Y 255 255 0/ 0 0 0 200 idtype
              12 VARCHAR Y 255 255 0/ 0 0 0 200 stateuser
              12 VARCHAR Y 255 255 0/ 0 0 0 200 statestamp
              • 4. Re: HS to postgre ORA-28511
                Robo [Maind]
                Hi,

                this is part of alert.log from that time:

                <msg time='2013-03-07T14:51:33.404+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>HS: Lost RPC connection to remote Agent...
                </txt>
                </msg>
                <msg time='2013-03-07T14:51:33.404+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=odamaind1)(PORT=1621))(CONNECT_DATA=(SID=carecloud))), NCR status = -2147385341
                </txt>
                </msg>
                <msg time='2013-03-07T14:51:33.404+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>Error 28511 trapped in 2PC on transaction 9.9.330303. Cleaning up.
                </txt>
                </msg>
                <msg time='2013-03-07T14:51:33.405+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>Error stack returned to user:
                </txt>
                </msg>
                <msg time='2013-03-07T14:51:33.405+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=odamaind1)(PORT=1621))(CONNECT_DATA=(SID=carecloud)))
                </txt>
                </msg>
                <msg time='2013-03-07T14:51:33.405+01:00' org_id='oracle' comp_id='rdbms'
                client_id='' type='UNKNOWN' level='16'
                host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                pid='23822'>
                <txt>ORA-02063: preceding line from DL_CARECLOUD
                </txt>
                </msg>


                Database "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production" was installed as a part of Oracle Database Appliance, there was no migration. Do you have any idea how to solve this problem?
                Thank you.
                • 5. Re: HS to postgre ORA-28511
                  Kgronau-Oracle
                  No details yet why the gateway fails with the error.

                  When you look at $ORACLE_HOME/rdbms/log, do you have a core/trace file with a timestamp that matches the entry in the alert.log:
                  <msg time='2013-03-07T14:51:33.404+01:00' org_id='oracle' comp_id='rdbms'
                  client_id='' type='UNKNOWN' level='16'
                  host_id='odamaind1' host_addr='192.168.22.71' module='sqlplus@odamaind1 (TNS V1-V3)'
                  pid='23822'>
                  <txt>HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=odamaind1)(PORT=1621))(CONNECT_DATA=(SID=carecloud))), NCR status = -2147385341
                  • 6. Re: HS to postgre ORA-28511
                    Robo [Maind]
                    Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23822.trc
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                    Data Mining and Real Application Testing options
                    ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1
                    System name: Linux
                    Node name: odamaind1
                    Release: 2.6.32-300.11.1.el5uek
                    Version: #1 SMP Wed Mar 7 09:55:50 EST 2012
                    Machine: x86_64
                    Instance name: orcl
                    Redo thread mounted by this instance: 1
                    Oracle process number: 100
                    Unix process pid: 23822, image: oracle@odamaind1


                    *** 2013-03-07 14:51:33.404
                    *** SESSION ID:(638.31779) 2013-03-07 14:51:33.404
                    *** CLIENT ID:() 2013-03-07 14:51:33.404
                    *** SERVICE NAME:(orcl) 2013-03-07 14:51:33.404
                    *** MODULE NAME:(sqlplus@odamaind1 (TNS V1-V3)) 2013-03-07 14:51:33.404
                    *** ACTION NAME:() 2013-03-07 14:51:33.404

                    HS: RPC error reported on server, SendCommitTrans: ncrorou_recv_out_args; NCR code 1003

                    *** 2013-03-07 14:51:33.404
                    HS: Lost RPC connection to remote Agent...
                    HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=odamaind1)(PORT=1621))(CONNECT_DATA=(SID=carecloud))), NCR status = -2147385341
                    • 7. Re: HS to postgre ORA-28511
                      Kgronau-Oracle
                      also just a generic error message w/o a root cause.
                      Is there a chance to get a testcase? Best would be if you create a test table in your env and fill it with data, as soon as you can reproduce the issue post the create table statement as well as your insert commands to fill the table with content and I'll check. Alternatively feel free to log a service request.

                      - Klaus
                      • 8. Re: HS to postgre ORA-28511
                        Robo [Maind]
                        Thanks again, could you please review our configuration parameters (init___.ora, odbc,...)? If you won't see there any issue we will post a service request.
                        • 9. Re: HS to postgre ORA-28511
                          Kgronau-Oracle
                          Without any indication from the trace files I would go ahead and just play with these parameters:

                          HS_KEEP_REMOTE_COLUMN_SIZE = REMOTE by setting it to LOCAL or ALL
                          and
                          HS_FDS_SQLLEN_INTERPRETATION = 64 to 32

                          In addition i would comment out:
                          #HS_FDS_TRANSACTION_MODEL = READ_ONLY
                          #HS_FDS_TRANSACTION_ISOLATION = NONE

                          Please make sure to close the database link (or just exit from SQL*Plus) every time before you change a gateway parameter in the init file as only when a new gateway session is started the init file is read.