5 Replies Latest reply on Aug 8, 2019 2:37 PM by 2706580

    DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace

    Ayman2

      I have 14 database but only one of them is causing this error. Anyway I can run a gather stats command on a table to get this sorted?

       

      Oracle 11gRAC 11.2.0.4, linux 6

       

      Anyone know which tables

       

      I've attached the query that DBSNMP is running to cause this, it's based on RMAN tables

       

      thanks

       

      I think there was a similar issue in previous release but not sure if this would even solve it

      High temp space consumption by DBSNMP (Doc ID 1389377.1)

      https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=569714749957873

       

      sql code below

      SELECT DISTINCT j.session_key                                                        ,
      j.session_recid                                                              ,
      j.session_stamp                                                              ,
      j.command_id                                                                 ,
      j.STATUS                                                                     ,
      j.input_type                                                                 ,
      j.output_device_type                                                         ,
      TO_CHAR(j.start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time                 ,
      TO_CHAR(j.end_time, 'YYYY-MM-DD HH24:MI:SS')   AS end_time                   ,
      j.time_taken_display                                                         ,
      j.input_bytes                                                                ,
      j.output_bytes                                                               ,
      sp.media                                                                     ,
      sp.keep_until                                                                ,
      sp.keep                                                                      ,
      sp.keep_options                                                              ,
      j.compression_ratio                                                          ,
      DECODE(sp.incremental_level,-1, NULL, sp.incremental_level) incremental_level,
      j.elapsed_seconds                                                            ,
      NVL(sp.tag, 'NO_TAG_'
      ||
      TO_CHAR(systimestamp,'ddmmyyyyhh24missFF')) tag,
      sp.CONTAINER                                   ,
      sp.compressed                                  ,
      sp.encrypted                                   ,
      j.input_bytes_display                          ,
      j.output_bytes_display                         ,
      j.output_bytes_per_sec                         ,
      j.output_bytes_per_sec_display
      FROM    v$rman_backup_job_details j
      LEFT JOIN
      (
      SELECT  *
      FROM
      (
      SELECT  d.*,
      COUNT(tag) over (partition BY tag) tag_count_final
      FROM
      (
      SELECT  t.* ,
      COUNT(tag) over (partition BY tag) tag_count
      FROM
      (
      SELECT DISTINCT session_recid                                 ,
      session_stamp                                         ,
      incremental_level                                     ,
      TO_CHAR(keep_until,'YYYY-MM-DD HH24:MI:SS') keep_until,
      keep                                                  ,
      keep_options                                          ,
      tag                                                   ,
      media                                                 ,
      compressed                                            ,
      ENCRYPTED                                             ,
      CONTAINER
      FROM
      (
      SELECT  s.session_recid                               ,
      s.session_stamp                               ,
      NVL(s.incremental_level, -1) incremental_level, -- change null to -1 to enable the comparisions for distinct.
      keep_until                                    ,
      s.keep                                        ,
      NVL(s.keep_options, ' ') keep_options         ,
      p.tag                                         ,
      NVL(p.media,' ') media                        ,
      p.compressed                                  ,
      p.ENCRYPTED                                   ,
      p.bs_key                                      ,
      p.handle                                      ,
      NVL(
      CASE
      WHEN p.device_type != 'DISK'
      THEN NVL2(p.handle, NVL2(SUBSTR(p.handle, 1, INSTR(p.handle, '/', -1, 1) - 1), SUBSTR(p.handle, 1, INSTR(p.handle, 
      '/', -1, 1) - 1), p.MEDIA), p.MEDIA)
      ELSE p.MEDIA
      END, ' ') AS CONTAINER
      FROM    v$backup_set_details s,
      v$backup_piece_details p
      WHERE   s.session_recid     = p.session_recid
      AND s.bs_key        = p.bs_key
      AND p.session_stamp = s.session_stamp
      )
      )
      t -- This will get the distinct data per session from the piece and set details.
      )
      d -- This will add the tag_count column at the end of data fetched in "t"
      WHERE   d.tag_count <= 1
      OR
      (
      d.tag_count              > 1
      AND d.incremental_level != -1
      ) --Filter out the rows with null incremental levels only if there are other rows corresponding to same tag.
      )
      WHERE   tag_count_final <=1
      )
      sp -- Filter out all the duplicate tag rows.
      ON      sp.session_recid     = j.session_recid
      AND sp.session_stamp = j.session_stamp
      ORDER BY NVL(end_time, start_time) DESC
      
        • 1. Re: DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace
          EdStevens

          Ayman2 wrote:

           

          I have 14 database but only one of them is causing this error. Anyway I can run a gather stats command on a table to get this sorted?

           

          Anyone know which tables

           

          I've attached the query that DBSNMP is running to cause this, it's based on RMAN tables

           

          thanks

           

          I think there was a similar issue in previous release but not sure if this would even solve it

          High temp space consumption by DBSNMP (Doc ID 1389377.1)

          https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=569714749957873

           

          Many sites block attachments.  Many members of this forum refuse to open them for the same reason others block them.  You need to paste your evidence directly into your message.

           

          An ora-1652 will actually identify the TS having the issue but you failed to show that.

           

          Looking at the solution presented in the cited document, maybe it would solve it, maybe it wouldn't. But what would it cost you to try it?

          • 2. Re: DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace
            Ayman2

            hi EdStevens I tried it and will know by tomorrow if it goes well, I've also edited reply according to your suggestion by including version and placing code instead of attachment, thanks

            • 3. Re: DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace
              EricvdS

              Hi Ayman2,

               

              I think you are using Enterprise Manager version 13.2 or 13.3. This issue I ran into also. The query runs a long time and uses all the TEMP tablespace (32 GB in my case) if it runs on a 11.2 database, on a 12 database no problem is seen. This query and also an other (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) that runs not as long and uses less TEMP tablespace is the result of a metric introduced in EM 13.2, see

              New Database metrics introduced with Enterprise Manager Cloud Control 13.2 (Doc ID 2235938.1). The two metrics involved are High Availability Metrics: High Availability Disk Backup and High Availability Media Backup.

              Since I did not want the heavy queries running on my 11.2 database and filling up all the TEMP tablespace I had to disable the two mentioned High Availability Metrics. And while you are at it and if you don't use a Recovery Appliance you could also disable the metric High Availability Recovery Window.

               

              See also: After Upgrade to Enterprise Manager 13.2 Cloud Control, a Recurring Query in 11.2.0.4 Target Database (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) Takes Five Minutes, Using 10GB Temp Space (Doc ID 2201982.1). The in the note mentioned solutions did not work for me.

               

              Regards,

              Eric

               

              ps Is this the only 11 database of the 14 you have.

              • 4. Re: DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace
                Ayman2

                hi EricvdS

                ps Is this the only 11 database of the 14 you have.

                All 14 database are in 11.2.0.4

                I loved your response and going to start working on this issue again and see why that one database is causing issues, perhaps with other dbs the query consumes less

                We are using OEM 13c3 and 12cR5 for monitoring each database twice via 13c3 and 12cR5, but all 14 database are 11.2.0.4 and the issue happens with one of our RAC databases

                 

                edit: I forgot to mention that only 13cR3 is installed on 12cR1 single instance, single tenant

                • 5. Re: DBSNMP causing ORA-1652 unable to extend temp segment by 128 in tablespace
                  2706580

                  DROPING AND LOCKING STATS FOR ALL X$ TABLES REFERENCIED WORKED FOR ME. SELECT IS NOW NOT USING TEMP TBS.