The ASM password directory

Password file authentication for Oracle Database or ASM can work for both local and remote connections. In Oracle version 12c, the password files can reside in an ASM disk group. The ASM metadata structure for managing the passwords is the ASM Password Directory - ASM metadata file number 13.

Note that the password files are accessible only after the disk group is mounted. One implication of this is that no remote SYSASM access to ASM and no remote SYSDBA access to database is possible, until the disk group with the password file is mounted.

The password file

The disk group based password file is managed by ASMCMD commands, ORAPWD tool and SRVCTL commands. The password file can be created with ORAPWD and ASMCA (at the time ASM is configured). All other password file manipulations are performed with ASMCMD or SRVCTL commands.

The COMPATIBLE.ASM disk group attribute must be set to at least 12.1 for the disk group where the password is to be located. The SYSASM privilege is required to manage the ASM password file and the SYSDBA privilege is required to manage the database password file.

Let's create the ASM password file in disk group DATA.

First make sure the COMPATIBLE.ASM attribute is set to the minimum required value:

$ asmcmd lsattr -G DATA -l compatible.asm

Name            Value

compatible.asm  12.1.0.0.0

Create the ASM password file:

$ orapwd file='+DATA/orapwasm' asm=y

Enter password for SYS: *******

$

Get the ASM password file name:

$ asmcmd pwget --asm

+DATA/orapwasm

And finally, find the ASM password file location and fully qualified name:

$ asmcmd find +DATA "*" --type password

+DATA/ASM/PASSWORD/pwdasm.256.837972683

+DATA/orapwasm

From this we see that +DATA/orapwasm is an alias to the actual file that has a special (+[DISK GROUP NAME]/ASM/PASSWORD) placeholder.

The ASM password directory

The ASM metadata structure for managing the disk group based passwords is the ASM Password Directory - ASM metadata file number 13. Note that the password file is also managed by the ASM File Directory, like any other ASM based file. I guess this redundancy just highlights the importance of the password file.

Let's locate the ASM Password Directory. As that is file number 13, we can look it up in the ASM File Directory. That means, we first need to locate the ASM File Directory itself. The pointer to the first AU of the ASM File Directory is in the disk header of disk 0, in the field f1b2locn:

First match the disk numbers to disk paths for disk group DATA:

$ asmcmd lsdsk -p -G DATA | cut -c12-21,78-88

Disk_Num  Path

      0  /dev/sdc1

      1  /dev/sdd1

      2  /dev/sde1

      3  /dev/sdf1

Now get the starting point of the disk directory:

$ kfed read /dev/sdc1 | grep f1b1locn

kfdhdb.f1b1locn:                     10 ; 0x0d4: 0x0000000a

This is telling us that the file directory starts at AU 10 on that disk. Now look up block 13 in AU 10 - that will be the directory entry for ASM file 13, i.e. the ASM Password Directory.

$ kfed read /dev/sdc1 aun=10 blkn=13 | egrep "au|disk" | head

kfffde[0].xptr.au:                   47 ; 0x4a0: 0x0000002f

kfffde[0].xptr.disk:                  2 ; 0x4a4: 0x0002

kfffde[1].xptr.au:                   45 ; 0x4a8: 0x0000002d

kfffde[1].xptr.disk:                  1 ; 0x4ac: 0x0001

kfffde[2].xptr.au:                   46 ; 0x4b0: 0x0000002e

kfffde[2].xptr.disk:                  3 ; 0x4b4: 0x0003

kfffde[3].xptr.au:           4294967295 ; 0x4b8: 0xffffffff

kfffde[3].xptr.disk:              65535 ; 0x4bc: 0xffff

...

The output is telling us that the ASM Password Directory is in AU 47 on disk 2 (with copies in AU 45 on disk 1, and AU 46 on disk 3). Note that the ASM Password Directory is triple mirrored, even in a normal redundancy disk group.

Now look at AU 47 on disk 2:

$ kfed read /dev/sde1 aun=47 blkn=1 | more

kfbh.endian:                          1 ; 0x000: 0x01

kfbh.hard:                          130 ; 0x001: 0x82

kfbh.type:                           29 ; 0x002: KFBTYP_PASWDDIR

...

kfzpdb.block.incarn:                  3 ; 0x000: A=1 NUMM=0x1

kfzpdb.block.frlist.number:  4294967295 ; 0x004: 0xffffffff

kfzpdb.block.frlist.incarn:           0 ; 0x008: A=0 NUMM=0x0

kfzpdb.next.number:                  15 ; 0x00c: 0x0000000f

kfzpdb.next.incarn:                   3 ; 0x010: A=1 NUMM=0x1

kfzpdb.flags:                         0 ; 0x014: 0x00000000

kfzpdb.file:                        256 ; 0x018: 0x00000100

kfzpdb.finc:                  837972683 ; 0x01c: 0x31f272cb

kfzpdb.bcount:                       15 ; 0x020: 0x0000000f

kfzpdb.size:                        512 ; 0x024: 0x00000200

...

The ASM metadata block type is KFBTYP_PASWDDIR, i.e. the ASM Password Directory. We see that it points to ASM file number 256 (kfzpdb.file=256). From the earlier asmcmd find command we already know the actual password file is ASM file 256 in disk group DATA:

$ asmcmd ls -l +DATA/ASM/PASSWORD

Type      Redund  Striped  Time             Sys  Name

PASSWORD  HIGH    COARSE   JAN 27 18:00:00  Y    pwdasm.256.837972683

Again, note that the ASM password file is triple mirrored (Redund=HIGH) even in a normal redundancy disk group.

Conclusion

Starting with ASM version 12c we can store ASM and database password files in an ASM disk group. The ASM password can be created at the time of Grid Infrastructure installation or later with the ORAPWD command. The disk group based password files are managed with ASMCMD, ORAPWD and SRVCTL commands.

Tell me about your ASM

When diagnosing ASM issues, it helps to know a bit about the setup - disk group names and types, the state of disks, ASM instance initialisation parameters and if any rebalance operations are in progress. In those cases I usually ask for an HTML report, that gets produced by running the SQL script against one of the ASM instances. This post is about that script with the comments about the output.

The script

First, here is the script, that may be saved as asm_report.sql:

spool /tmp/ASM_report.html
set markup html on
set echo off
set feedback off
set pages 10000
break on INST_ID on GROUP_NUMBER
prompt ASM report
select to_char(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') "Time" from dual;
prompt Version
select * from V$VERSION where BANNER like '%Database%' order by 1;
prompt Cluster wide operations
select * from GV$ASM_OPERATION order by 1;
prompt
prompt Disk groups, including the dismounted disk groups
select * from V$ASM_DISKGROUP order by 1, 2, 3;
prompt All disks, including the candidate disks
select GROUP_NUMBER, DISK_NUMBER, FAILGROUP, NAME, LABEL, PATH, MOUNT_STATUS, HEADER_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, CREATE_DATE, MOUNT_DATE, SECTOR_SIZE, VOTING_FILE, FAILGROUP_TYPE
from V$ASM_DISK
where MODE_STATUS='ONLINE'
order by 1, 2;
prompt Offline disks
select GROUP_NUMBER, DISK_NUMBER, FAILGROUP, NAME, MOUNT_STATUS, HEADER_STATUS, STATE, REPAIR_TIMER
from V$ASM_DISK
where MODE_STATUS='OFFLINE'
order by 1, 2;
prompt Disk group attributes
select GROUP_NUMBER, NAME, VALUE from V$ASM_ATTRIBUTE where NAME not like 'template%' order by 1;
prompt Connected clients
select * from V$ASM_CLIENT order by 1, 2;
prompt Non-default ASM specific initialisation parameters, including the hidden ones
select KSPPINM "Parameter", KSPFTCTXVL "Value"
from X$KSPPI a, X$KSPPCV2 b
where a.INDX + 1 = KSPFTCTXPN and (KSPPINM like '%asm%' or KSPPINM like '%balance%' or KSPPINM like '%auto_manage%') and kspftctxdf = 'FALSE'
order by 1 desc;
prompt Memory, cluster and instance specific initialisation parameters
select NAME "Parameter", VALUE "Value", ISDEFAULT "Default"
from V$PARAMETER
where NAME like '%target%' or NAME like '%pool%' or NAME like 'cluster%' or NAME like 'instance%'
order by 1;
prompt Disk group imbalance
select g.NAME "Diskgroup",
100*(max((d.TOTAL_MB-d.FREE_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576))/(d.TOTAL_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576)))-min((d.TOTAL_MB-d.FREE_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576))/(d.TOTAL_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576))))/max((d.TOTAL_MB-d.FREE_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576))/(d.TOTAL_MB + (128*g.ALLOCATION_UNIT_SIZE/1048576))) "Imbalance",
count(*) "Disk count",
g.TYPE "Type"
from V$ASM_DISK_STAT d , V$ASM_DISKGROUP_STAT g
where d.GROUP_NUMBER = g.GROUP_NUMBER and d.STATE = 'NORMAL' and d.MOUNT_STATUS = 'CACHED'
group by g.NAME, g.TYPE;
prompt End of ASM report
set markup html off
set echo on
set feedback on
exit

To produce the report, that will be saved as /tmp/ASM_report.html, run the following command as the OS user that owns the Grid Infrastructure home (usually grid or oracle), against an ASM instance (say +ASM1), like this:

$ sqlplus -S / as sysasm @asm_report.sql

To save the output in a different location or under a different name, just modify the spool command (line 1 in the script).

The report

The reports first shows the time of the report and ASM version.

It then shows if there are any ASM operations in progress. In this excerpt we see a rebalance running in ASM instance 1. It can also be seen that the resync and rebalance have completed and that the compacting is the only outstanding operation:

Next we see the information about all disk groups, including the dismounted disk groups. This is then followed by the info about disks, again with the note that this includes the candidate disks.

I have separated the info about offline disks, as this may be of interest when dealing with disk issues. That section looks like this:

Next are the disk group attributes, with the note that this will be displayed only for ASM version 11.1 and later, as we did not have the disk group attributes in earlier versions.

This is followed by the list of connected clients, usually database instances served by that ASM instance.

The section with ASM initialisation parameters includes hidden and some Exadata specific (_auto_manage) parameters. Here is a small sample:

I have also separated the memory, cluster and instance specific initialisation parameters as they are often of special interest.

The last section shows the disk group imbalance report.

Conclusion

While I use this report for a quick overview of the ASM, it can also be used as a 'backup' info about your ASM setup. You are welcome to modify the script to produce a report that suits your needs. Please let me know if you find any issues with the script or if you have suggestions for improvements.

Acknowledgments

The bulk of the script is based on My Oracle Support (MOS) Doc ID 470211.1, by Oracle Support engineer Esteban D. Bernal.

The imbalance SQL is based on the Reporting Disk Imbalances script from Oracle Press book Oracle Automatic Storage Management, Under-the-Hood & Practical Deployment Guide, by Nitin Vengurlekar, Murali Vallath and Rich Long.