This discussion is archived
11 Replies Latest reply: Oct 4, 2013 5:55 AM by EdStevens RSS

Free vs. usable

EdStevens Guru
Currently Being Moderated

Oracle 11.2.0.2 SE-One

ASM 11.2.0.2

Oracle Linux 5.6 x86-64

 

Every morning at 0600 I run a report that includes this query:

 

asmcmd lsdg | awk '{print $13 " " $2 " " $7 " " $8 " " $10 }' >> $logfile

 

Normally I will see a very slight decrease in "free" and "usable" over the course of a week or so.  This is a DW app, so data constantly goes in but rarely leaves, but the system in question is the test system.  It gets refreshed from prod via an rman duplicate on the weekend but typically that is the only time this system would show any changes.

 

But this morning I saw a distinct drop in 'usable'.  That was unusual in and of itself, but there was no correlating drop in 'free', which I find very puzzling.

 

Yesterday's report had this:

 

Name Type Total_MB Free_MB Usable_file_MB

DEV_LARGE/ EXTERN 972800 439422 439422

DEV_SMALL/ EXTERN 307200 44301 44301

LARGE/ EXTERN 972800 247864 247864

SMALL/ NORMAL 920308 271714 81508

 

And today I have

 

Name Type Total_MB Free_MB Usable_file_MB

DEV_LARGE/ EXTERN 972800 439422 439422

DEV_SMALL/ EXTERN 307200 44301 44301

LARGE/ EXTERN 972800 247864 247864

SMALL/ NORMAL 920308 271714 81508

 

Notice the change in the fourth DG -- "SMALL"

 

FWIW, between these two reports I installed PSU 11.

 

So I'm trying to determine

1 - the cause of the abnormal drop in 'usable' space on the DB, and

2 - why the drop in 'usable' was not also reflected in 'free'.

  • 1. Re: Free vs. usable
    Dude! Guru
    Currently Being Moderated

    Your two outputs look identical. But anyway, the amount of usable free space is adjusted for mirroring, that is available for new files. Free shows the space without redundancy. Perhaps you have a disk failure or some rebalancing operation?

  • 2. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Dude wrote:

     

    Your two outputs look identical.

     

    Oops.  Must have fat-fingered my copy/paste operation from two separate log files.  Let's try that again

     

    Check asm diskgroup usage

    Name Type Total_MB Free_MB Usable_file_MB

    DEV_LARGE/ EXTERN 972800 439422 439422

    DEV_SMALL/ EXTERN 307200 44301 44301

    LARGE/ EXTERN 972800 247864 247864

    SMALL/ NORMAL 920308 271714 81508      <-------------

     

    Finished at Mon Sep 16 06:00:13 CDT 2013

     

     

    Check asm diskgroup usage

    Name Type Total_MB Free_MB Usable_file_MB

    DEV_LARGE/ EXTERN 972800 439422 439422

    DEV_SMALL/ EXTERN 307200 44301 44301

    LARGE/ EXTERN 972800 247864 247864

    SMALL/ NORMAL 920308 271714 59057      <-------------

     

    Finished at Tue Sep 17 06:00:08 CDT 2013

     

     

    But anyway, the amount of usable free space is adjusted for mirroring, that is available for new files. Free shows the space without redundancy.

    But I would expect them to move relatively in tandem.

     

     

    Perhaps you have a disk failure or some rebalancing operation?

     

    No rebalance. Just checked again - the usage numbers are the same and no re-balance going  on.

     

    Disk failure.  Hmm.  This DG has two failure groups, one on SSD, the other on "spinning rust".    We are now out on the edges of my understanding of storage management vis a vis ASM.  Any pointers on what I should check, or have my SA check?

  • 3. Re: Free vs. usable
    Dude! Guru
    Currently Being Moderated

    One of the the most important facts to keep in mind about ASM is that it does not mirror disks; it is not RAID and data redundancy is depends on available free space in disk failure groups. By default, every disk is a failure group, but a failure group can also consist of several disks acting as one failure group. When a disk in a failure group fails, it will reduce the amount of disk space available for data redundancy of the disk group.

     

     

    The easiest way to check is probably to analyze the alert log file of the ASM instance.


  • 4. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Dude wrote:

     

    One of the the most important facts to keep in mind about ASM is that it does not mirror disks; it is not RAID and data redundancy is depends on available free space in disk failure groups. By default, every disk is a failure group, but a failure group can also consist of several disks acting as one failure group. When a disk in a failure group fails, it will reduce the amount of disk space available for data redundancy of the disk group.

     

     

    The easiest way to check is probably to analyze the alert log file of the ASM instance.

     

    Nothing in the alert log of any interest.  Certainly nothing from the post-PSU startup forward that is any different from pre-PSU application.

    At some level I'm willing to accept that I may be chasing nothing, but that one-day difference in 'available' on this system is pretty significant .. too much so to be ignored without getting a better grip on what it means.

  • 5. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Just thinking out loud here as I comb through things.

     

    Looking at the usage graph in dbcontrol console, it looks like the jump in usage (drop in 'available') happened about the time I was applying the PSU yesterday.  I'll start digging through the fine print notes on the PSU, but at this point I'm wondering if the patches made a change in the way free/available are allocated and/or calculated.

  • 6. Re: Free vs. usable
    Dude! Guru
    Currently Being Moderated

    I would not think so. If you check the output of the other diskgroups you can see that the free and unused free disk space is the same. The difference is the space required for data redundancy. The problem might be your storage layout. Perhaps you have disk failure groups with different sizes.

  • 7. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Dude wrote:

     

    I would not think so. If you check the output of the other diskgroups you can see that the free and unused free disk space is the same. The difference is the space required for data redundancy. The problem might be your storage layout. Perhaps you have disk failure groups with different sizes.

     

     

     

    I think we're getting somewhere

     

     

     

    SQL> select adg.name DG_NAME,

      2         adg.type,

      3         ad.failgroup,

      4         ad.name DISK_NAME,

      5         ad.os_mb,

      6         ad.total_mb,

      7         ad.free_mb,

      8         ad.hot_used_mb,

      9         ad.cold_used_mb

    10  from v$asm_diskgroup adg

    11  join v$asm_disk ad on adg.group_number = ad.group_number

    12  --where adg.name='SMALL'

    13  order by adg.name,

    14           ad.failgroup,

    15           ad.name;

     

     

    DG_NAME    TYPE   FAILGROUP      DISK_NAME           OS_MB   TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB

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

    DEV_LARGE  EXTERN DEV_LARGE_0000 DEV_LARGE_0000     972800     972800     439422           0       533378

    DEV_SMALL  EXTERN DEV_SMALL_0000 DEV_SMALL_0000     153600     153600      22142           0       131458

    DEV_SMALL  EXTERN DEV_SMALL_0001 DEV_SMALL_0001     153600     153600      22149           0       131451

    LARGE      EXTERN LARGE_0000     LARGE_0000         972800     972800     247864           0       724936

    SMALL      NORMAL SAN_GROUP      SMALL_0002         153600     153600      45208           0            0

    SMALL      NORMAL SAN_GROUP      SMALL_0004         153600     153600      45987           0            0

    SMALL      NORMAL SAN_GROUP      SMALL_0005         153600     153600      46186           0            0

    SMALL      NORMAL SSD_GROUP      SMALL_FIOA         153460     153460      44758           0            0

    SMALL      NORMAL SSD_GROUP      SMALL_FIOB         153460     153460      44945           0            0

    SMALL      NORMAL SSD_GROUP      SMALL_FIOC         152588     152588      44610           0            0

     

    10 rows selected.

     

     

     

    The DG in question is "SMALL".  I see that failgroup SSD_GROUP has an odd man out on the disk sizes, and there is also a mismatch between SAN_GROUP and SSD_GROUP.

     

    I see your point about the other dg's not showing the usage change I saw on SMALL, but they don't have the mis-matched disks.  I don't have a good enough grasp of it all to see how that might play out. 

     

    What I do know is

    - I never see 'mid-week' changes in disk usage on this system.  Only as a result of the weekend refresh from prod.

    - I never see a jump as large as what I saw from yesterday to today.

    - The only other variable I can account for is the application of the PSU, and the graph of disk usage from OEM correlates to the PSU.

     

    BTW, there are two ASM based db's on this system.  One uses the 'DEV_*' groups, the other uses simply SMALL and LARGE.  That is the one that gets refreshed from prod. 

  • 8. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Just as an update/follow-up ..

     

    I just completed installing the PSU 11.2.0.2.11(patchset 16742329).  The last command I issued before the 'opatch auto' was this

     

    oracle:+ASM$  asmcmd lsdg | awk '{print $13 " " $2 " " $7 " " $8 " " $10 }'

    Name     Type      Total_MB     Free_MB     Usable_file_MB

    SAN/     EXTERN     1024000      299060             299060

    SMALL/   NORMAL      910348      259944              73422

     

    Immediately upon completion of the PSU (even before running the SQL scripts on the databases) I repeated the command and got this:

     

     

    Name     Type      Total_MB     Free_MB     Usable_file_MB

    SAN/     EXTERN     1024000      299060             299060

    SMALL/   NORMAL      910348      259944              48052


    Notice the drop in 'usable' on "SMALL"


  • 9. Re: Free vs. usable
    Dude! Guru
    Currently Being Moderated

    Perhaps it is the result of another bug, or a new issue introduced with the patchset you installed, affecting the disk space reporting. For  instance, bug 12378512, which results in wrong calculations of required_mirror_free_mb and usable_file_mb.

     

    I think it might be best to file a SR to resovle the problem.

  • 10. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    Dude wrote:

     

    Perhaps it is the result of another bug, or a new issue introduced with the patchset you installed, affecting the disk space reporting. For  instance, bug 12378512, which results in wrong calculations of required_mirror_free_mb and usable_file_mb.

     

    I think it might be best to file a SR to resovle the problem.

    Yes, I had just about decided it was an issue/change with how 'available' is calculated, and was starting to gather evidences to open an SR.

     

    Thx.

  • 11. Re: Free vs. usable
    EdStevens Guru
    Currently Being Moderated

    To bring final closure to this thread ...

     

    I ended up opening an SR.  After some back and forth trying to get the tech to actually pay attention to what I was saying, the final resolution seems to be that the calculation of usable_file_mb was incorrect all along, and the change I observed when installing PSU 11.2.0.2.11 was actually the correct number.  The description in the bug was pretty sparse, and the 'corrected in' only listed v11.2.0.3 and 12.1 --- no mention of correction in any PSU.

     

    The reason the problem only showed up on one DG was due to the fact that the incorrect calculation was tied up with having disks of various sizes in the DG.  The DG that did NOT show a change is a single disk with external redundancy.  The DG that did show a change has six disks, three each in two fail groups, and there is some mismatch in disk size.  (Don't ask!  Well, do ask if you want, all I can say is that's what I inherited.  I can expand a bit if anyone is curious)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points