0

Oracle RAC 11.2 with asm storage, all type = EXTERN redundancy.

I've been cleaning up some tablespaces and shrinking datafiles back down to sizes that they're not likely to grow much larger than (processes no longer used had blown them up).

The sum of the bytes of datafiles from dba_data_files reflects this shrink, however, USED_MB in v$asm_diskgroup has not fallen and it doesn't match to what adding up the datafiles bytes is?

What would account for this difference? We typically use v$asm_diskgroup to monitor if we're running low on space but this doesn't appear to be correct.

  SELECT name group_name ,
  type type ,
  total_mb total_mb ,
  (total_mb - free_mb) used_mb ,
  ROUND(
  (SELECT SUM(bytes)/1024/1024
  FROM dba_data_files
  WHERE file_name LIKE '+DATA02%'
  ),0) AS datafile_used
FROM v$asm_diskgroup w
WHERE name = 'DATA02'


GROUP_NAME  TYPE  TOTAL_MB  USED_MB  DATAFILE_USED
______________________________________
DATA02  EXTERN  542712  516902  506447
  • is this any help? prutser.wordpress.com/2013/01/03/… – Philᵀᴹ Oct 27 '16 at 14:41
  • @Philᵀᴹ - nope, because that mainly talks about the differences due to redundancy, which doesn't apply if TYPE = EXTERN – rfusca Oct 27 '16 at 15:15
  • Sometimes it takes a bit of time for USED_MB to catch up, in my experience. After 10 to 15 mins it shows the right values. – Andrew Brennan Oct 27 '16 at 15:26
  • @AndrewBrennan - its been like 12+ hours – rfusca Oct 27 '16 at 16:28
0

dba_data_files does not include:

  • tempfiles
  • controlfiles
  • spfiles
  • redo logs
  • archived logs
  • backups/copies
  • ADVM volumes (ACFS)
  • OCR
  • reserved space for ASM metadata

Include v$asm_alias and v$asm_file in your queries, or just simply list the files in the diskgroup (asmcmd, ls).

  • If I go into asmcmd and ls the directory, its the exact same like from dba_data_files. That file group only has data files in it. – rfusca Oct 27 '16 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.