1 2 Previous Next 21 Replies Latest reply: Jan 23, 2013 11:18 PM by J-Oracle Go to original post RSS
      • 15. Re: High "ACTIVE" undo blocks..ORA-30036..
        J-Oracle
        @JohnWatson:
        select * from (select sid,value from v$sesstat where statistic#= (select statistic# from v$statname where name='undo change vector size') order by 2 desc) where rownum<10;
        
               SID      VALUE
        ---------- ----------
               155 2395474300
               378   20503980
               380   18789984
               400   15907312
        and SID 155 is the FBDA bg process.

        How do i see what is happening with the FBDA. There are few tables with flashback data archive enabled. They were like that since quite long time.

        Is this FBDA session 'killable' ??


        Well, the space consumed by FBDA comes only to !2gb. So that should not be the one..


        @Jonathan:

        Env: 11.2.0.3 on Linux x-86 64bit.

        Ran
        select segment_name, count(*) from DBA_UNDO_EXTENTS
        where status = 'ACTIVE'
        group by segment_name 
        order by count(*);
        
        The growth doesn't happen only on one segment...but 2 segments among the 134 are quite big..and no change on the last two..
        
        SEGMENT_NAME                               COUNT(*)
        ---------------------------------------- ----------
        :
        _SYSSMU10_1216189513$                           239
        _SYSSMU27_1216190033$                           240
        _SYSSMU47_1216190405$                           240
        _SYSSMU34_1216190102$                           247
        _SYSSMU63_1217577604$                           252
        _SYSSMU62_1217577603$                           265
        _SYSSMU59_1217577549$                           307
        _SYSSMU55_1216190699$                           381
        _SYSSMU40_1216190258$                           639
        _SYSSMU4_1216189346$                           8552
        TIA,
        Jon

        Edited by: J on 21-Jan-2013 05:17

        Edited by: J on 21-Jan-2013 05:26
        • 16. Re: High "ACTIVE" undo blocks..ORA-30036..
          Mihael
          You can debug the issue as follows:

          1. Temporary stop adding space to undo tablespace.

          2. Temporary enable resumable space allocation, for example :

          ALTER SYSTEM set RESUMABLE_TIMEOUT=900;

          3. Check alert.log for sessions that are suspended.
          • 17. Re: High "ACTIVE" undo blocks..ORA-30036..
            Jonathan Lewis
            J wrote:
            @JohnWatson:
            SEGMENT_NAME                               COUNT(*)
            ---------------------------------------- ----------
            :
            _SYSSMU10_1216189513$                           239
            _SYSSMU27_1216190033$                           240
            _SYSSMU47_1216190405$                           240
            _SYSSMU34_1216190102$                           247
            _SYSSMU63_1217577604$                           252
            _SYSSMU62_1217577603$                           265
            _SYSSMU59_1217577549$                           307
            _SYSSMU55_1216190699$                           381
            _SYSSMU40_1216190258$                           639
            _SYSSMU4_1216189346$                           8552
            Given the small number of active transactions you keep finding, this looks as if it might be a bug. I know that Oracle used to have problems registering that an extent had expired, but that was a couple of versions ago and I though the problems had been fixed. It's possible, of course, that there are transactions that are active, but not visible in v$transaction, so if you have SYS access you could check that by looking at the undo segment headers.
            select
                 KTUXEUSN,
                 KTUXESLT,
                 KTUXESQN,
                 KTUXESCNB,
                 KTUXESCNW
            from
                 x$ktuxe 
            where 
                 ktuxesta = 'ACTIVE'
            ;
            This should report the active transactions recorded in the transaction tables in the undo segment headers.
            The first three columns should match the xidusn, xidslot and xidsqn in v$transaction. The last two should match the start_scnb, start_scnw.
            What you're looking for are entries in x$ktuxe that don't appear in v$transaction, and have start SCN that is much older than the current SCN. (I will be a little surprised if you find any - but if you do they would be a cause of the very large number of active extents).

            Regards
            Jonathan Lewis
            • 18. Re: High "ACTIVE" undo blocks..ORA-30036..
              J-Oracle
              There is not any ..
              select
                      KTUXEUSN,
                      KTUXESLT,
                      KTUXESQN,
                      KTUXESCNB,
                      KTUXESCNW
              from
                      x$ktuxe
              where
                      ktuxesta = 'ACTIVE';  2    3    4    5    6    7    8    9   10
              
              no rows selected
              
              
              SYS:htmlprd > select * from (select sid,value from v$sesstat where statistic#= (select statistic# from v$statname where name='undo change vector size') order by 2 desc) where rownum<10;
              
                     SID      VALUE
              ---------- ----------
                     155 6721949584  - oracle@aapexdb02 (FBDA)
                     380   25161420  - OEM.SystemPool    
                     378   24015652  - oracle@aapexdb02 (SMON)
              
              **FBDA has grown 6.5gb since the last 'crash and restart' of fbda bg process due to ORA-30036 . Since then i 'am feeding them with more undo space..
              
              SYS:htmlprd > select DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
              
              STATUS                      SUM(BYTES)/1024/1024   COUNT(*)
              --------------------------- -------------------- ----------
              ACTIVE                                   28531.5      21534
              UNEXPIRED                                    949       1988
              EXPIRED                                  47.1875        725
              We had an OS kernel upgrade last weekend and this issue came up after that..
              If I speculate with the data I ‘ve:
              
              Sat Jan 19 05:28:03 2013 : OS Kernel upgrade. Ie: Last database bounce
                   Current UNDO Size: 12,288 MB
                   DBA_UNDO_EXTENTS - ACTIVE undo extents = 0 mb
              
                   Increasing(some process consume) ACTIVE undo extents at the rate of ~150mb/10 mins
                   12288/(150*6)= 13.30 hrs(approx) the system should run.
                   05:28 + 13.30 = ~18 hours
              
              Sun Jan 20 17:00:55 2013 : First ORA-30036 for some dbms job
                   Current UNDO Size: 12,288 MB
                   DBA_UNDO_EXTENTS - ACTIVE undo extents = 12,000 MB
              
              Mon Jan 21 13:02:36 2013 :
                   Current UNDO Size: 40,960 MB
                   DBA_UNDO_EXTENTS - ACTIVE undo extents = 29,083 MB     
              FBDA has grown 6.5gb since the last 'crash and restart' of fbda bg process due to ORA-30036 .
              FBDA crashed and restarted many times. Since 8 hrs 'am feeding them and no error.

              Not sure if the ACTIVE undo extents during the earlier runs of FBDA are still up in there.

              TIA,
              Jon
              • 19. Re: High "ACTIVE" undo blocks..ORA-30036..
                Jonathan Lewis
                J wrote:
                @JohnWatson:
                select * from (select sid,value from v$sesstat where statistic#= (select statistic# from v$statname where name='undo change vector size') order by 2 desc) where rownum<10;
                
                SID      VALUE
                ---------- ----------
                155 2395474300
                378   20503980
                380   18789984
                400   15907312
                and SID 155 is the FBDA bg process.

                How do i see what is happening with the FBDA. There are few tables with flashback data archive enabled. They were like that since quite long time.
                I spent the evening trying to figure out why flashback logging would generate undo - and I woke up this morning remembering that flashback logging and flashback data archive (total recall) were completely different features.

                I haven't paid a lot of attention to detail on flashback data archive - it had too many bugs the first time I tried it - but I think the FBDA process is allowed to lag behind your runtime system and uses the undo tablespace to catch up - reading the undo to generate the data that it needs to store in the "flashback archive" tablespaces. If the fbda process falls behind, Oracle has to be sure that undo that's needed for the archive doesn't get stolen and over-written. If the only possible states for unod extents are expired, unexpired, and active, it would make sense for extents that fbda needs to be left as active until fbda has cleared them.

                Based on this thinking, your active extents are constantly growing because either (a) FBDA can't keep up with your rate of change or (b) FBDA is failing to set the extents as expired when it has finished with them. Take a closer look at what FBDA is doing, and check MOS to see if you can find any bugs relating to FBDA failing to clean up properly. (It might be checking what happens if a flashback archive tablespace gets full - does FBDA report errors in the alert, or trace files, does it then simply let undo accumulate in the undo tablespace until the free space problem is resolved)

                Note - flashback data archive READS undo to create the archive, but also CREATES undo as it populates the archive. The size of the undo tablespace and the value of "undo change vector size" recorded by fbda aren't directly related.

                Regards
                Jonathan Lewis
                • 20. Re: High "ACTIVE" undo blocks..ORA-30036..
                  J-Oracle
                  Jonathan, thx for coming back !


                  We did keep the fbda process in the debugging mode using *"_fbda_debug_mode=1"*

                  and all we could see was:
                  *** 2013-01-22 19:33:24.496
                  WARNING: kcbz_log_block_read - failed to record BRR for 1/196369 (0x42ff11) SCN 0xbba.9a0bc254 SEQ 1
                  
                  *** 2013-01-22 19:33:24.553
                  WARNING: kcbz_log_block_read - failed to record BRR for 1/196389 (0x42ff25) SCN 0xbd9.59e31340 SEQ 1
                  
                  *** 2013-01-22 19:33:24.555
                  WARNING: kcbz_log_block_read - failed to record BRR for 1/288223 (0x4465df) SCN 0xbae.bd7ff46a SEQ 2
                  :
                  :
                  :
                  :
                  
                  *** 2013-01-22 19:33:24.754
                  WARNING: kcbz_log_block_read - failed to record BRR for 1/196441 (0x42ff59) SCN 0xbac.1136df22 SEQ 1
                  0 transactions, 0 changes being archived
                  
                  *** 2013-01-23 04:10:29.990
                  0 transactions, 0 changes being archived
                  0 transactions, 0 changes being archived
                  0 transactions, 0 changes being archived
                  0 transactions, 0 changes being archived
                  ::
                  : <a lot occurred in 1 min>
                  :
                  There is enough room in the tablespace where Flashback Archive resides.
                  These flashback archived tables are here since quite long time and there ain't any heavy transaction happening on them.
                  The below states that much recent changes have happened onthe archive table and this rules out the possibility that fbda ain't writing it to the archive tables.
                  select scn_to_timestamp(max(ENDSCN)) from SYS_FBA_HIST_1809900;
                  
                  SCN_TO_TIMESTAMP(MAX(ENDSCN))
                  ---------------------------------------------------------------------------
                  23-JAN-13 01.37.03.000000000 AM
                  • 21. Re: High "ACTIVE" undo blocks..ORA-30036..
                    J-Oracle
                    We did a bounce and disabled the FBDA using "_disable_flashback_archiver"=1 .

                    FBDA bg process is NOT running now..but the ACTIVE undo segments keep growing. So FDBA process was clean.

                    It is something else.

                    select * from (select sid,value from v$sesstat where statistic#= (select statistic# from v$statname where name='undo change vector size') order by 2 desc) where rownum<10;
                    
                           SID      VALUE
                    ---------- ----------
                           146    2428176
                           378    2197916
                           518    2055752
                           896    1590028
                    
                     SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
                    
                    STATUS                      SUM(BYTES)/1024/1024   COUNT(*)
                    --------------------------- -------------------- ----------
                    ACTIVE                                 106323.75      33374
                    UNEXPIRED                              8990.5625       6197
                    EXPIRED                                    41.75        623
                    Sigh !!!
                    1 2 Previous Next