Friday, May 18, 2012

Where is my space on DBFS


I just ran into an issue on DBFS where I ran out of space.

First here is the df -k

Filesystem           1K-blocks      Used Available Use% Mounted on
dbfs-dbfs_admin2@:/   20983808  11443696   9540112  55% /dbfs/dba


OK, everything looks good.. I am using 11g and I have 9.5g available.

I go to copy a file on the os (you can see it is 240m).  Lots of room

 ls -al bsg.out
-rw-r--r-- 1 oracle oinstall 240794862 May 18 11:37 bsg.out


cp bsg.out bsg.out1
cp: writing `bsg.out1': No space left on device
cp: closing `bsg.out1': No space left on device


So where is my space.  ?? I find this query..

set serveroutput on;
declare
v_segment_size_blocks number;
v_segment_size_bytes number;
v_ number;
v_used_blocks number;
v_used_bytes number;
v_expired_blocks number;
v_expired_bytes number;
v_unexpired_blocks number;
v_unexpired_bytes number;
begin
dbms_space.space_usage ('DBFS_OWNER', 'LOB_SFS$_FST_12345', 'LOB', 
v_segment_size_blocks, v_segment_size_bytes,
v_used_blocks, v_used_bytes, v_expired_blocks, v_expired_bytes, 
v_unexpired_blocks, v_unexpired_bytes );
dbms_output.put_line('Segment Size blocks = '||v_segment_size_blocks);
dbms_output.put_line('Segment Size bytes = '||v_segment_size_bytes);
dbms_output.put_line('Used blocks = '||v_used_blocks);
dbms_output.put_line('Used bytes = '||v_used_bytes);
dbms_output.put_line('Expired Blocks = '||v_expired_blocks);
dbms_output.put_line('Expired Bytes = '||v_expired_bytes);
dbms_output.put_line('UNExpired Blocks = '||v_unexpired_blocks);
dbms_output.put_line('UNExpired Bytes = '||v_unexpired_bytes);
end;
/



And I see this output

Segment Size blocks = 2619024
Segment Size bytes = 21455044608
Used blocks = 1425916
Used bytes = 11681103872
Expired Blocks = 1190111
Expired Bytes = 9749389312
UNExpired Blocks = 0
UNExpired Bytes = 0


So.. according to this.. The segment is 21.4 g

11.7g is used space
  9.7g is Expired space
   0g   is unexpired space.

So if I have 9.7g of Expired space why can't I use it ??  My file is only 244m, and I should have 9.7 g available.

So my questions out of this are (if anyone knows the answer).

1) How does this happen and how do I avoid it ?

2) How do I size tablespaces for DBFS ?  They need more space to be available then I need for the file system.

3) How do I monitor the sizing since the DF -k does not report unexpired bytes that are available to be used ?

4) How does the clause "retention" fit into this ?  retention defaults to "auto" rather than "none".  Can I set it to "none", but what happens and does this solve my problem ?


Oh, and I wanted to make sure that I included the ouput of  whats in the tablespace.

SEGMENT_NAME                                             SEGMENT_TYPE       SEGMENT_SIZE
-------------------------------------------------------- ------------------ ------------
LOB_SFS$_FST_12345                                       LOBSEGMENT                20461
T_ADMIN                                                  TABLE                        17
IP_SFS$_FST_12345                                        INDEX                         4
IPG_SFS$_FST_12345                                       INDEX                         3
IG_SFS$_FST_12345                                        INDEX                         2
SYS_IL0000095835C00007$$                                 LOBINDEX                      0




UPDATE (6/13/12)  --

After working with support on this, it was filed as a bug.  This occured because I was using DBFS as a filesytem for my dbreplay capture.  After thinking about it the dbcapture is probably the most intensive workload I could throw at DBFS.  Not only does it simultaneously write to multiple files, but it writes to those files across multiple nodes.  In my capture there were 4 nodes writing to 100's of files at the same time.
   I will be testing the patch, and see if it corrects the problem.  Support is telling me that the writing across multiple nodes is causing some of the issues..

1 comment:

  1. Hi Bryan,

    Thanks for sharing.

    I do face the same issue on DBFS in 11.2.0.3 BP20 Exadata platform.

    Was the issue resolved on your end after patching ?

    could you share the patch details as well.

    I did open the SR with Oracle but all i got from Oracle was "Shrink LOB segment via online reorg option".

    Appreciate your input.

    LOB usage on my database:

    SQL> set serveroutput on
    SQL> .
    SQL> declare
    2 v_segment_size_blocks number;
    3 v_segment_size_bytes number;
    4 v_ number;
    5 v_used_blocks number;
    6 v_used_bytes number;
    7 v_expired_blocks number;
    8 v_expired_bytes number;
    9 v_unexpired_blocks number;
    10 v_unexpired_bytes number;
    11 begin
    12 dbms_space.space_usage ('DBFS', 'LOB_SFS$_FST_1', 'LOB',
    13 v_segment_size_blocks, v_segment_size_bytes,
    14 v_used_blocks, v_used_bytes, v_expired_blocks, v_expired_bytes,
    15 v_unexpired_blocks, v_unexpired_bytes );
    16 dbms_output.put_line('Expired Blocks = '||v_expired_blocks);
    17 dbms_output.put_line('Expired Bytes = '||v_expired_bytes);
    18 dbms_output.put_line('UNExpired Blocks = '||v_unexpired_blocks);
    19 dbms_output.put_line('UNExpired Bytes = '||v_unexpired_bytes);
    20 end;
    21 /
    Expired Blocks = 43685076
    Expired Bytes = 357868142592
    UNExpired Blocks = 0
    UNExpired Bytes = 0

    PL/SQL procedure successfully completed.

    $ df -h .
    Filesystem Size Used Avail Use% Mounted
    dbfs-@LBFSBP.local:/ 495G 31G 464G 7% /dbfs_direct


    ==
    Thanks,
    Bal

    ReplyDelete