8 Replies Latest reply on Apr 21, 2017 1:52 PM by user12050753

    ORA-04030: out of process memory when sending large volume of data to mysql using HS

    user12050753

      From our 11g Oracle development and test database

      we're trying to copy data from another oracle database (source) to a MySQL database (target),

      using HS(Hetrogenous services) ODBC links. The query (below) works fine for smaller amounts of data,

      but once we hit >2m(2 million) rows it falls over with following errors

      "ORA-02055: distributed update operation failed; rollback required" and "ORA-04030: out of process memory" errors.

      Error as below:-

      ORA-02055: distributed update operation failed; rollback required

      ORA-04030: out of process memory when trying to allocate 1016 bytes (session

      heap,kgsc:kxsc)

      ORA-06512: at line 210

      ORA-04030: out of process memory when trying to allocate 448 bytes (session

      heap,hssr horss3/1)

       

       

      Any and all suggestions much appreciated!

       

       

      oRacle version:-11.2.0.3.0

      MySQL vesrsion:-

      Driver      = /home/oracle/lms/myodbc-5.3.6/lib/libmyodbc5a.so

      DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample

      PORT        = 3306

       

       

       

       

      Please find the below memeory parameters;

       

       

      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

      Connected as pbatch

       

      SQL> show parameter memory;

       

      NAME                                 TYPE        VALUE

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

      hi_shared_memory_address             integer     0

      memory_max_target                    big integer 55G

      memory_target                        big integer 55G

      shared_memory_address                integer     0

       

      SQL> show parameter pga;

       

      NAME                                 TYPE        VALUE

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

      pga_aggregate_target                 big integer 0

       

      SQL> show parameter sga;

       

      NAME                                 TYPE        VALUE

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

      lock_sga                             boolean     FALSE

      pre_page_sga                         boolean     FALSE

      sga_max_size                         big integer 55G

      sga_target                           big integer 0

       

      SQL> show parameter sort;

       

      NAME                                 TYPE        VALUE

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

      nls_sort                             string      BINARY

      sort_area_retained_size              integer     0

      sort_area_size                       integer     65536

       

      SQL> show parameter hash;

       

      NAME                                 TYPE        VALUE

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

      hash_area_size                       integer     131072

       

       

      The code used is pasted below:-

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

      DECLARE

         PROCEDURE PROC_LOAD_DAILY_TX_DATA_LMS(p_run_id IN NUMBER)

         --  AUTHID CURRENT_USER

         IS

            v_systimestamp       VARCHAR (16) := TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');

            logfilehandle        UTL_FILE.file_type;

            log_file_name        VARCHAR2 (100) := NULL;

            v_count              NUMBER := 0;

            v_csc_lookup_key     NUMBER := 0;

            v_err_message        VARCHAR2 (300) := NULL;

            error_exception      EXCEPTION;

            v_run_id             NUMBER (3) := 0;

            v_t_type_exclude     VARCHAR2 (100) := NULL;

            v_run_date           DATE := NULL;

            v_job_offset_value   NUMBER := 0;

            v_count_id           number:=0;

          

            CURSOR cur_daily_txn_rec

            IS

               SELECT Post_txn_balance AFTER_CARD_BAL,

                      toll_revenue_type APP_SECTOR,

                      Extern_file_id BATCH_NUMBER,

                      Card_issuer_id CARD_ISSUER_ID,

                      Card_no CARD_NUMBER,

                      'ACTIVE' CARD_STATUS,

                      Tx_seq_number CARD_TXN_NUMBER,

                      'C' DATA_PUSH_SOURCE,

                      'D' DEBIT_CREDIT_INDICATOR,

                      '503' ENTRY_LOC_ID,

                      'MALYASY' ENTRY_LOC_NAME,

                      Recv_extn_loc_id EXIT_LOC_ID,

                      'MALYASY' EXIT_LOC_NAME,

                      '06-jan-2016' EXPIRY_DATE,

                      'ABC123' ID_NUMBER,

                      'DFG123' ID_TYPE,

                      Device_no MFG_NUMBER,

                      '06-jan-2016' OPERATIONAL_DATE,

                      '06-jan-2016' POSTED_DATE,

                      '04' PRINT_SP,

                      '01-jan-2016' LMS_PROCESS_DATE,

                      'P' PROCESS_STATUS,

                      Purse_ind PURSE_FLAG,

                      Recv_extn_sp_id SP_ID,

                      Lane_tx_type T_TYPE,

                      Lane_id TERMINAL_ID,

                      Lane_type TERMINAL_TYPE,

                      Full_fare_amount TXN_AMOUNT,

                      '06-jan-2016 12:12:12' TXN_DATE_TIME,

                      'T' TXN_SOURCE,

                      csc_lookup_key TXN_UNIQUE_ID,

                      'P' UR_FLAG,

                      '12345' VECTOR_ACCT_NUMBER,

                      csc_lookup_key

                 FROM t_account_toll

                 where rownum<=3000000;

                   

       

       

            TYPE tab IS TABLE OF cur_daily_txn_rec%ROWTYPE

               INDEX BY BINARY_INTEGER;

       

       

            v_daily_txn_coll     tab;

         BEGIN

            v_run_id := p_run_id;

       

       

       

       

            log_file_name :=

               'PROC_LOAD_DAILY_TX_DATA_LMS_' || v_systimestamp || '.LOG';

            logfilehandle := UTL_FILE.FOPEN ('VECTOR_LOG', log_file_name, 'W');

            UTL_FILE.PUTF (logfilehandle,

                           '\n %s%s',

                           'Vector Load Daily Transaction Process Started @',

                           v_systimestamp);

            v_t_type_exclude := NULL;

            UTL_FILE.FFLUSH (logfilehandle);

       

       

       

       

            OPEN cur_daily_txn_rec ;

       

       

            LOOP

          

               FETCH cur_daily_txn_rec

               BULK COLLECT INTO v_daily_txn_coll

               LIMIT 1000;

       

       

               v_csc_lookup_key := 0;

       

       

               IF v_daily_txn_coll.COUNT > 0

               THEN

                  FOR i IN 1 .. v_daily_txn_coll.COUNT

                  LOOP

                v_count_id:=v_count_id+1;

                     INSERT

                       INTO "vector_staging_txn"@lms(

                                                  "after_card_bal",

                                                          "app_sector",

                                                          "batch_number",

                                                          "card_issuer_id",

                                                          "card_number",

                                                          "card_status",

                                                          "card_txn_number",

                                                          "data_push_source",

                                                          "debit_credit_indicator",

                                                          "entry_loc_id",

                                                          "entry_loc_name",

                                                          "exit_loc_id",

                                                          "exit_loc_name",

                                                          "expiry_date",

                                                          "id_number",

                                                          "id_type",

                                                          "mfg_number",

                                                          "operational_date",

                                                          "posted_date",

                                                          "print_sp",

                                                          "lms_process_date",

                                                          "process_status",

                                                          "purse_flag",

                                                          "sp_id",

                                                          "t_type",

                                                          "terminal_id",

                                                          "terminal_type",

                                                          "txn_amount",

                                                          "txn_date_time",

                                                          "txn_source",

                                                          "txn_unique_id",

                                                          "ur_flag",

                                                          "vector_acct_number",

                                "lms_error_type")

                     VALUES (

                       v_daily_txn_coll (i).AFTER_CARD_BAL,

                             v_daily_txn_coll (i).APP_SECTOR,

                             v_daily_txn_coll (i).BATCH_NUMBER,

                             v_daily_txn_coll (i).CARD_ISSUER_ID,

                             v_daily_txn_coll (i).CARD_NUMBER,

                             v_daily_txn_coll (i).CARD_STATUS,

                             v_daily_txn_coll (i).CARD_TXN_NUMBER,

                             v_daily_txn_coll (i).DATA_PUSH_SOURCE,

                             v_daily_txn_coll (i).DEBIT_CREDIT_INDICATOR,

                             v_daily_txn_coll (i).ENTRY_LOC_ID,

                             v_daily_txn_coll (i).ENTRY_LOC_NAME,

                             v_daily_txn_coll (i).EXIT_LOC_ID,

                             v_daily_txn_coll (i).EXIT_LOC_NAME,

                             '01-mar-2017',      --v_daily_txn_coll (i).EXPIRY_DATE,

                             v_daily_txn_coll (i).ID_NUMBER,

                             v_daily_txn_coll (i).ID_TYPE,

                             v_daily_txn_coll (i).MFG_NUMBER,

                             NULL,          --v_daily_txn_coll (i).OPERATIONAL_DATE,

                             '01-mar-2016',      --v_daily_txn_coll (i).POSTED_DATE,

                             v_daily_txn_coll (i).PRINT_SP,

                             '01-mar-2016', --   v_daily_txn_coll (i).LMS_PROCESS_DATE,

                             v_daily_txn_coll (i).PROCESS_STATUS,

                             v_daily_txn_coll (i).PURSE_FLAG,

                             v_daily_txn_coll (i).SP_ID,

                             v_daily_txn_coll (i).T_TYPE,

                             v_daily_txn_coll (i).TERMINAL_ID,

                             v_daily_txn_coll (i).TERMINAL_TYPE,

                             v_daily_txn_coll (i).TXN_AMOUNT,

                             '01-mar-2016',    --v_daily_txn_coll (i).TXN_DATE_TIME,

                             v_daily_txn_coll (i).TXN_SOURCE,

                             v_daily_txn_coll (i).TXN_UNIQUE_ID,

                             v_daily_txn_coll (i).UR_FLAG,

                             v_daily_txn_coll (i).VECTOR_ACCT_NUMBER,

                   1);

       

       

                     v_count := v_count + SQL%ROWCOUNT;

                     v_csc_lookup_key := v_daily_txn_coll (i).csc_lookup_key;

       

       

                     IF MOD (v_count, 1000) = 0

                     THEN

                        COMMIT;

         v_daily_txn_coll.delete;

                     END IF;

       

       

                      if MOD (v_count, 10000)=0 then

                     UTL_FILE.PUTF (logfilehandle,

                                    '\n No Records processed [%s] @ [%s]',

                                    v_count,

                                    TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'));

       

       

                     UTL_FILE.FFLUSH (logfilehandle);

               end if;

                  END LOOP;

               ELSE

                  EXIT;

               END IF;

            END LOOP;

       

       

            CLOSE cur_daily_txn_rec;

       

       

            COMMIT;

            UTL_FILE.PUTF (logfilehandle,

                           '\n Total No Records Processed [%s] @ [%s]',

                           v_count,

                           TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'));

            --      UTL_FILE.FFLUSH (logfilehandle);

            UTL_FILE.PUTF (

               logfilehandle,

               '\n \n ---------------------END OF DAILY TRANSACTION LOAD FOR LMS---------------------');

            UTL_FILE.FCLOSE (logfilehandle);

         EXCEPTION

            WHEN error_exception

            THEN

               ROLLBACK;

               UTL_FILE.PUTF (logfilehandle,

                              '%s ==> %s',

                              SQLCODE,

                              SQLERRM);

               UTL_FILE.FCLOSE (logfilehandle);

               RAISE_APPLICATION_ERROR (-20001, v_err_message || SQLERRM);

            WHEN OTHERS

            THEN

               ROLLBACK;

               UTL_FILE.PUTF (logfilehandle,

                              '%s ==> %s',

                              SQLCODE,

                              SQLERRM);

               UTL_FILE.FCLOSE (logfilehandle);

               DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLCODE || '==>' || SQLERRM);

               RAISE_APPLICATION_ERROR (-20002, v_err_message || SQLERRM);

         END PROC_LOAD_DAILY_TX_DATA_LMS;

      BEGIN

         PROC_LOAD_DAILY_TX_DATA_LMS(133);

      END;

      /

       

      Like (0) Reply

      Show more

      Actions Hide new activityMark unread

      0 replies