Oracle ASM – Mapping ASM Files to Physical Linux Devices

oracleoracle-11g-r2oracle-asm

We are having problems with log file sync waits periodically. We have a 2 node RAC cluster using ASM. I'd like to monitor the physical device(s) containing the REDO log files using iostat, but I'm not sure which physical device contains the redo log groups. We have something like 150 physical devices so I'd like to narrow this down as much as possible. How would I map the ASM files containing the redo logs to the physical linux devices?

Best Answer

First of all, people often fall for this, but high log file sync waits do not necessarily mean I/O problem.

Second, this could be really troublesome, if you have many physical devices in the ASM diskgroup where your redo logs are, because the extents of your files will be evenly distributed on several disks.

Anyway, you need to find the ASM diskgroup number and file number for your redo logs. For example my redo logs:

select member from v$logfile

MEMBER
--------------------------------------------------
+O6CA_DATA/BRIX/ONLINELOG/group_1.258.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_1.257.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_2.259.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_2.258.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_3.266.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_3.259.915374563
+O6CA_DATA/BRIX/ONLINELOG/group_4.267.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_4.260.915374563

Since I use OMF, I can easily recognize the file number, because it is part of the name (258, 257, ...), but you can get this as:

select
  af.group_number,
  af.file_number
from
  v$asm_file af
  join v$asm_alias aa on (af.group_number = aa.group_number
                          and af.file_number = aa.file_number)
  join v$logfile lf on (substr(lf.member, instr(lf.member, '/', -1) + 1) = aa.name)
;

GROUP_NUMBER FILE_NUMBER
------------ -----------
           1         258
           2         257
           1         259
           2         258
           1         266
           2         259
           1         267
           2         260

Now I am curious about the first redo logfile (group 1, file 258). In the ASM instance (so not the database instance), I can query as:

select
  group_kffxp,
  disk_kffxp,
  size_kffxp,
  count(*)
from
  x$kffxp
where
  group_kffxp = 1
  and number_kffxp = 258
group by
  group_kffxp,
  disk_kffxp,
  size_kffxp
;

GROUP_KFFXP DISK_KFFXP SIZE_KFFXP   COUNT(*)
----------- ---------- ---------- ----------
          1          0          1         51

So this single redo log file has 51 extents in diskgroup number 1, disk number 0, each extent the size of 1 allocation unit (and the allocation unit I used is 1 MB, and I created a redo log with the size of 50 MB, but that is not relevant here). And that single disk, in my case, is:

select name, path from v$asm_disk where group_number = 1 and disk_number = 0

NAME            PATH
--------------- ------------------------------
O6CA_DATA_0000  /dev/oracleasm/asm-o6ca_data1
Related Question