Database Reference
In-Depth Information
file
spfile.txt
is an ASCII text file, ready for editing. The commands
head
and
tail
, which display
the beginning and end of the file, confirm that the entire contents of the
SPFILE
were retrieved.
$ head -3 spfile.txt
*.audit_file_dest='/opt/oracle/obase/admin/TEN/adump'
*.background_dump_dest='/opt/oracle/obase/admin/TEN/bdump'
*.compatible='10.2.0.3.0'
$ tail -3 spfile.txt
TEN1.undo_tablespace='UNDOTBS1'
TEN2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/opt/oracle/obase/admin/TEN/udump'
Mapping Segments to ASM Storage
Now that we have successfully completed the warm-up exercise, we are ready to tackle the
more difficult task of mapping a block in a database segment to the corresponding block in
an ASM disk. Beyond the mapping to the correct allocation unit in an ASM disk, this requires
finding the correct block within the allocation unit. A single ASM allocation unit may contain
blocks from several database segments. Remember that the smallest extent size in a locally
managed tablespace with
AUTOALLOCATE
option is 64 KB. The case study that follows uses the
segment
LOCATIONS
in tablespace USERS as an example. We repeat the query from the previous
section, however this time pass the single data file of tablespace USERS as input.
SQL> SELECT x.xnum_kffxp AS extent, a.group_number AS grp#, a.file_number AS file#,
f.bytes, allocation_unit_size AS au_size, au_kffxp AS au,
decode(x.lxn_kffxp, 0, 'PRIMARY', 1, 'MIRROR') AS type,
d.failgroup AS failgrp, d.path
FROM v$asm_alias a, v$asm_file f, x$kffxp x, v$asm_disk d, v$asm_diskgroup dg
WHERE lower(a.name)=lower('USERS.263.628550085')
AND a.group_number=f.group_number
AND a.file_number=f.file_number
AND f.group_number=x.group_kffxp
AND f.file_number=x.number_kffxp
AND x.disk_kffxp=d.disk_number
AND f.group_number=dg.group_number
ORDER BY x.xnum_kffxp;
The size of the file
USERS.263.628550085
is 175 MB. The result of the query, which is depicted
in the next code listing, shows how ASM has striped the file across five disks. By default, ASM
stripes across eight disks (parameter
_ASM_STRIPEWIDTH
), given that the disk group contains
enough disks to accomplish this.
EXTENT GRP# FILE# BYTES AU_SIZE AU TYPE FAILGRP PATH
------ ---- ----- --------- ------- --- ------- ------- --------------------------
0 1 263 183508992 1048576 123 PRIMARY SDA11 /dev/oracleasm/disks/SDA11
1 1 263 183508992 1048576 126 PRIMARY SDA6 /dev/oracleasm/disks/SDA6
2 1 263 183508992 1048576 123 PRIMARY SDA9 /dev/oracleasm/disks/SDA9
3 1 263 183508992 1048576 125 PRIMARY SDA10 /dev/oracleasm/disks/SDA10
4 1 263 183508992 1048576 125 PRIMARY SDA5 /dev/oracleasm/disks/SDA5
