How to check I/O of Data files Device

This morning, There was a question from Free-Lists, To find out device names of a specific mount point where datafiles resides. There may be many mount points attached to the system for example from “/u01” to “/u06” , You can get all the mount point I/O statuses of Blocks read, writes so on information from “iostat“. Then it can be time taking you to look at all the devices. If you have database only on “/u03” mount point and you may have to look at I/O response because of wait events such as[db file sequential read/scattered read, so on..], Probably you will go for “iostat” command from linux to see the variance across the devices as below example.

oracle-ckpt> iostat
Linux 2.6.18-238.12.1.el5 (oracle-ckpt)     02/26/2013
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.22    0.02    1.38    3.52    0.00   88.86
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
cciss/c0d0       18.73       341.71       203.77 3468377811 2068198608
cciss/c0d0p1      0.00         0.00         0.00       7680       2024
cciss/c0d0p2     18.73       341.71       203.76 3468369891 2068196584
cciss/c0d1        2.80        59.40        30.57  602886236  310269440
sda               0.00         0.00         0.00       1484          0
sda1              0.00         0.00         0.00         86          0
sdb               0.00         0.00         0.00       1516          0
sdb1              0.00         0.00         0.00         86          0
sdc               0.00         0.00         0.00       1512          0
sdc1              0.00         0.00         0.00         84          0
sdd               0.00         0.00         0.00       1516          0
sdd1              0.00         0.00         0.00         86          0
sde               0.00         0.00         0.00       1516          0
sde1              0.00         0.00         0.00         86          0
sdf               0.00         0.00         0.00       1512          0
sdf1              0.00         0.00         0.00         84          0
sdg               0.00         0.00         0.00       1484          0

From the above output you will get all the devices that are available for the system[i have listed only few]. Now the question comes, You will review all the devices when having issue only with one database which resides on “/u03“, Lets see how to find out which device belongs to “/u03” mount point.

Data files Location:-

NAME
--------------------------------------------------------------------------------
/u03/oradata/mydb/hrapp_tde.dbf
/u03/oradata/mydb/poapp_tde.dbf
/u03/oradata/mydb/polarge_tde.dbf
/u03/oradata/mydb/psindex_tde_01.dbf
oracle@oracle-ckpt> df -h /u03
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vgemc5-lvu03
                      1.5T  904G  499G  65% /u03
oracle@oracle-ckpt>

The above output confirms, data files are situated on mount point “/u03”. Now to scan all the disks for physical volumes, pvscan scans all supported LVM block devices in the system for physical volumes. ensure you are running this command from “root” user.

[root@oracle-ckpt ~]# pvscan |grep vgemc5
 PV /dev/emcpowere1     VG vgemc5   lvm2 [1.47 TB / 0    free]
 [root@l119132dbss3002 ~]#

In my case, output showing as “emcpower” using emc san storage. to know which storage you can also use “cat /proc/scsi/scsi” command to verify.

Storage_type

 

 

 

 

 

 

 

 

 

Here storage can be either it can be multi path or native multi path, of course Power path is nothing but multi path of EMC storage. The benefits of multipathing is if any one path is down still you can continue work on other path and data load balance for fast transfer (Active/Active) 🙂

[root@oracle-ckpt ~]# powermt display dev=emcpowere
Pseudo name=emcpowere
Symmetrix ID=000192602126
Logical device ID=2DE9
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
---------------- Host ---------------   - Stor -   -- I/O Path -  -- Stats ---
###  HW Path                I/O Paths    Interf.   Mode    State  Q-IOs Errors
==============================================================================
   3 qla2xxx                   sdi       FA  9fB   active  alive      0      0
   2 qla2xxx                   sdl       FA  8fB   active  alive      0      0

Now you can able to get the I/O paths from the above output as “sdi” & “sdl“, Now we are almost at the last step to see “iostat” for the only mount point required.

[root@oracle-ckpt ~]# iostat -xn |egrep 'sdi|sdl'
-x and -n options are mutually exclusive
sdi               0.00     0.00  0.00  0.00     0.00     0.00    21.65     0.00    0.57   0.57   0.00
sdi1              0.00     0.00  0.00  0.00     0.00     0.00    10.75     0.00    0.19   0.19   0.00
sdl               0.00     0.00  0.00  0.00     0.00     0.00    21.36     0.00    0.69   0.68   0.00
sdl1              0.00     0.00  0.00  0.00     0.00     0.00    10.75     0.00    1.25   1.25   0.00
[root@oracle-ckpt ~]#

From above output lets compare the devices “sdi” and “sdl“, There is no such variance, Which means data flow between devices are same. It mean load balanced[as per basic analysis]. If you see any variance then you do not have any option other than contacting your platform team to check any issues at storage level. This is the basic investigation DBA can do to find out there is any I/O issues specific to any mount point. As usual any comments are always welcome.

— Happy Reading —