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.
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 —
