1

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?

2

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
  • Sorry about the misleading post (deleted), but to make up for it :-), there is what appears to be an excellent post here. – Vérace Jul 26 '16 at 19:04
  • @Verace, Yeah, that is next step, mapping ASMLib disks to physical disks, if ASMLib is used at all. Fortunately in newer versions of ASMLib, we can use the new -p option for this as oracleasm querydisk -p ASMLIB_DISK_NAME. My output may be misleading because of /dev/oracleasm, but this is not an ASMLib setup, I created udev rules to rename the devices and put them under /dev/oracleasm. – Balazs Papp Jul 26 '16 at 19:12

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.