Oracle ASM


24.02.2015
by Kamil Stawiarski

There is some kind of wrong understanding, regarding what Oracle ASM really is. A lot of people thinks, that this is some kind of file system, provided by Oracle and that Oracle Database reads data "from ASM", while the truth is, that the database reads data directly from the physical raw device with assistance of the ASM instance.

This short example will demonstrate what I mean:

I have table "EMPLOYEES", located on "DATA2″ diskgroup which uses the "/dev/sdc2″ device:

SQL> ;
  1  select t.table_name, d.file_name, dg.name, dk.path
  2  from dba_tables t, dba_data_files d, v$asm_diskgroup dg, v$asm_disk dk
  3  where t.tablespace_name=d.tablespace_name
  4  and    regexp_substr(d.file_name,'\w+',1,1)=dg.name
  5  and    dg.GROUP_NUMBER=dk.GROUP_NUMBER
  6  and    t.owner='HR'
  7* and    t.table_name='EMPLOYEES'
SQL> /

TABLE_NAME		       FILE_NAME				     NAME     PATH
------------------------------ --------------------------------------------- -------- ----------
EMPLOYEES		       +DATA2/orcl/datafile/tbs_sdc.257.872537323    DATA2    /dev/sdc2

In the other session I logged in as a HR user and performed a SELECT statement on the EMPLOYEES table.
Now let’s locate the SPID of the server process, correlated with the HR session.

SQL> ;
  1  select spid
  2  from v$process p, v$session s
  3  where p.addr=s.paddr
  4* and   s.username='HR'
SQL> /

SPID
------------------------
6675

Now as "root" I’ll check the file descriptors, that were created by the server process.

[root@dupa ~]# cd /proc/6675/fd
[root@dupa fd]# ls -al
total 0
dr-x------. 2 root   root  0 Feb 24 19:42 .
dr-xr-xr-x. 8 oracle dba   0 Feb 24 19:42 ..
lr-x------. 1 root   root 64 Feb 24 19:43 0 -> /dev/null
l-wx------. 1 root   root 64 Feb 24 19:43 1 -> /dev/null
l-wx------. 1 root   root 64 Feb 24 19:43 12 -> pipe:[31069]
l-wx------. 1 root   root 64 Feb 24 19:43 2 -> /dev/null
lrwx------. 1 root   root 64 Feb 24 19:43 256 -> /dev/sdc2
lr-x------. 1 root   root 64 Feb 24 19:43 3 -> /dev/null
lr-x------. 1 root   root 64 Feb 24 19:43 4 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------. 1 root   root 64 Feb 24 19:43 5 -> /proc/6675/fd
lr-x------. 1 root   root 64 Feb 24 19:43 6 -> /dev/zero
lrwx------. 1 root   root 64 Feb 24 19:43 7 -> /dev/oracleasm/iid/0000000000000003
lr-x------. 1 root   root 64 Feb 24 19:43 9 -> pipe:[31068]

As we can see, the file descriptor "256″ is pointing directly to the "/dev/sdc2″ device.
And this is what we can see in the strace output for the physical reads of database blocks:

[root@dupa fd]# strace -p 6675 -o /tmp/employees_read.trc
Process 6675 attached - interrupt to quit
[root@dupa fd]# cat /tmp/employees_read.trc | grep "pread(256"
pread(256, "#\242\0\0\202\0@\2\371\301\20\0\0\0\1\4\250\361\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 190857216) = 8192
pread(256, "\6\242\0\0\203\0@\2\371\301\20\0\0\0\1\4\2227\0\0\1\0\0\0002Z\1\0\371\301\20\0"..., 16384, 190865408) = 16384

The above example shows that function called "pread" has read 3 database blocks (8192+16384 => 3X 8k blocks). We can see those blocks in the buffer cache:

SQL> select b.file#, b.block#
  2  from v$bh b, dba_objects o
  3  where b.objd=o.data_object_id
  4  and   o.object_name='EMPLOYEES'
  5  and   b.status!='free';

     FILE#     BLOCK#
---------- ----------
	 9	  130
	 9	  132
	 9	  131

We can use dd to extract those blocks from physical device, using the offset values, provided by the above trace.
As we can see in the second occurrence of the "pread" function, we can use the 8k block size, skip first 23299 blocks (190865408/8192=23299) and copy two blocks.

[root@dupa fd]# dd if=/dev/sdc2 of=/tmp/employees.blocks bs=8192 skip=23299 count=2
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.00639561 s, 2.6 MB/s

The contents of those blocks has data from my table:

[root@dupa fd]# xxd /tmp/employees.blocks | grep -v 0000
0001000: 6f74 6b65 7908 455a 4c4f 544b 4559 1230  otkey.EZLOTKEY.0
0001010: 3131 2e34 342e 3133 3434 2e34 3239 3031  11.44.1344.42901
0001020: 3807 786c 011d 0101 0106 5341 5f4d 414e  8.xl......SA_MAN
0001030: 03c3 0206 02c0 1502 c202 02c1 512c 000b  ............Q,..
0001040: 03c2 0231 0647 6572 616c 6409 4361 6d62  ...1.Gerald.Camb
0001050: 7261 756c 7408 4743 414d 4252 4155 1230  rault.GCAMBRAU.0
0001060: 3131 2e34 342e 3133 3434 2e36 3139 3236  11.44.1344.61926
0001070: 3807 786b 0a0f 0101 0106 5341 5f4d 414e  8.xk......SA_MAN
0001080: 03c3 020b 02c0 1f02 c202 02c1 512c 000b  ............Q,..
0001090: 03c2 0230 0741 6c62 6572 746f 0945 7272  ...0.Alberto.Err
00010a0: 617a 7572 697a 0841 4552 5241 5a55 5212  azuriz.AERRAZUR.
00010b0: 3031 312e 3434 2e31 3334 342e 3432 3932  011.44.1344.4292
00010c0: 3738 0778 6903 0a01 0101 0653 415f 4d41  78.xi......SA_MA
00010d0: 4e03 c302 1502 c01f 02c2 0202 c151 2c00  N............Q,.
00010e0: 0b03 c202 2f05 4b61 7265 6e08 5061 7274  ..../.Karen.Part
00010f0: 6e65 7273 084b 5041 5254 4e45 5212 3031  ners.KPARTNER.01
0001100: 312e 3434 2e31 3334 342e 3436 3732 3638  1.44.1344.467268
0001110: 0778 6901 0501 0101 0653 415f 4d41 4e03  .xi......SA_MAN.
0001120: c302 2402 c01f 02c2 0202 c151 2c00 0b03  ..$........Q,...
0001130: c202 2e04 4a6f 686e 0752 7573 7365 6c6c  ....John.Russell
0001140: 074a 5255 5353 454c 1230 3131 2e34 342e  .JRUSSEL.011.44.
0001150: 3133 3434 2e34 3239 3236 3807 7868 0a01  1344.429268.xh..
0001160: 0101 0106 5341 5f4d 414e 03c3 0229 02c0  ....SA_MAN...)..
0001170: 2902 c202 02c1 512c 000b 03c2 022d 0550  ).....Q,.....-.P
0001180: 6574 6572 0656 6172 6761 7307 5056 4152  eter.Vargas.PVAR
0001190: 4741 530c 3635 302e 3132 312e 3230 3034  GAS.650.121.2004
00011a0: 0778 6a07 0901 0101 0853 545f 434c 4552  .xj......ST_CLER
00011b0: 4b02 c21a ff03 c202 1902 c133 2c00 0b03  K..........3,...
00011c0: c202 2c07 5261 6e64 616c 6c05 4d61 746f  ..,.Randall.Mato
00011d0: 7306 524d 4154 4f53 0c36 3530 2e31 3231  s.RMATOS.650.121
00011e0: 2e32 3837 3407 786a 030f 0101 0108 5354  .2874.xj......ST
00011f0: 5f43 4c45 524b 02c2 1bff 03c2 0219 02c1  _CLERK..........
0001200: 332c 000b 03c2 022b 0643 7572 7469 7306  3,.....+.Curtis.
0001210: 4461 7669 6573 0743 4441 5649 4553 0c36  Davies.CDAVIES.6
0001220: 3530 2e31 3231 2e32 3939 3407 7869 011d  50.121.2994.xi..
0001230: 0101 0108 5354 5f43 4c45 524b 02c2 20ff  ....ST_CLERK.. .

(......output removed for clarity......)

Search:


Kontakt

Database Whisperers sp. z o. o. sp. k.
Al. Jerozolimskie 200
Wejście B, III piętro/ pokój 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter zapisz się żeby być na bieżąco