11gR2 ASM – Inside story – Part5 (Read database file from OS)

11gR2 ASM – Inside story – Part5 (Read database file from OS)

Introduction:
In the previous post of “11gR2 ASM – Inside story – Part4 (ASM Preferred Read Failure Group)”, we have seen how to configure the ASM instance with a list of preferred disk failure group names to use when accessing ASM disks.
In this section, we will see how to access to the database file managed within the ASM instance via OS command.
In this note, we will use the undocumented X$KFFXP table, which contains the physical allocation table for files managed via an ASM instance.

The demonstration:

In this demonstration, we will try to read the content of the spfile managed by the ASM instance, using the dd command.
First, we query the view V$ASM_FILE to get the diskgroup number and the file number. Then, we will get the disk number where the spfile is located (Query the view X$KFFXP). Once we get the disk numbers, we query the view v$asm_disk to get the OS path, where exactly the spfile is located,. In the end, using the OS command “dd”, we will read the 10 first lines of the spfile, starting from the offset 717M, which is the result shown in AU_KFFXP for the disk number 0.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

[oracle@wissem ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 08:59:26 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option SQL> SQL> select GROUP_NUMBER, FILE_NUMBER, BYTES from v$ASM_FILE where type='PARAMETERFILE'; GROUP_NUMBER   FILE_NUMBER          BYTES      ------------                 -----------                   ----------            1                      265                         2560 SQL> SELECT DISK_KFFXP, AU_KFFXP, PXN_KFFXP, XNUM_KFFXP, LXN_KFFXP from   2  X$KFFXP WHERE GROUP_KFFXP=1 AND NUMBER_KFFXP=265; DISK_KFFXP         AU_KFFXP         PXN_KFFXP        XNUM_KFFXP             LXN_KFFXP ----------                   ----------                 ----------                 ----------                         ----------          0              717                           0                                0                                 0          2              710                           1                                0                                 1 SQL>  SELECT FAILGROUP, DISK_NUMBER, PATH from   2  V$ASM_DISK WHERE GROUP_NUMBER=1 AND DISK_NUMBER IN (0, 2); FAILGROUP                      DISK_NUMBER      PATH ------------------------------             -----------                -------------------------------------------------------------------------------- DISK3                                    2                        /dev/raw/raw3 DISK1                                    0                        /dev/raw/raw1 SQL>         [oracle@wissem ~]$ dd if=/dev/raw/raw1 bs=1M skip=717|strings|head -10 ORAW.__db_cache_size=37748736 ORAW.__java_pool_size=4194304 ORAW.__large_pool_size=4194304 ORAW.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment ORAW.__pga_aggregate_target=146800640 ORAW.__sga_target=167772160 ORAW.__shared_io_pool_size=0 ORAW.__shared_pool_size=104857600 ORAW.__streams_pool_size=8388608 *.audit_file_dest='/u01/app/oracle/admin/ORAW/adump' [oracle@wissem ~]$