Script to Find Unopened files in ASM

Find Unopened files in ASM


set pagesize 0
set linesize 200
col full_alias_path format a80

select * from (
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
/

Sample Output
1      13460 +MYDG1/LEGACYDB1/DATAFILE/indx01.dbf          DATAFILE

1      12440 +MYDG2/LEGACYDB2/DATAFILE/temp01.dbf          TEMPFILE

Reading ASM Disk Header

How to Read an ASM Disk Header

As root,

/u01/app/11.2.0/grid/bin/kfed read /dev/mapper/mydisk1|egrep "(dsksize|provstr|dskname|grpname|fgname)"

kfdhdb.driver.provstr:  ORCLDISKMYDISK1 ; 0x000: length=15

kfdhdb.dskname:                 MYDISK1 ; 0x028: length=7

kfdhdb.grpname:               MYDISKGROUP ; 0x048: length=9

kfdhdb.fgname:                  MYDISK1 ; 0x068: length=7

kfdhdb.dsksize:                  524294 ; 0x0c4: 0x00080006

To list all ASM devices using blkid:

blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;

Rename ASM diskgroup (11.2)

Rename ASM diskgroup

$GRID_HOME/bin/renamedg phase=both verbose=true dgname=OLD_FRA newdgname=NEW_FRA asm_diskstring='ORCL:DISK01','ORCL:DISK02'

Important Note: This does not change the file names/locations in the controlfile. 

1. List all data/temp/control/redo files.

2. Relocate the controlfiles (search this blog for "Multiplexing Controlfiles"). 

3. Rename the diskgroup.

4. Rename datafiles, tempfiles, online/standby redo logfiles using "alter database rename file 'x' to 'y';" with the database in mount mode.

ASM Debugging Info

ASM Debugging Information

If you have an ASM issue, you'll want these commands handy.

cat /etc/*release

uname -a

rpm -qa|grep oracleasm

/usr/sbin/oracleasm configure

/sbin/modinfo oracleasm

/etc/init.d/oracleasm status​

/usr/sbin/oracleasm-discover

oracleasm scandisks

oracleasm listdisks

ls -l /dev/oracleasm/disks

/sbin/blkid

ls -l /dev/mpath/*

ls -l /dev/mapper/*

ls -l /dev/dm-*

another approach...

1. uname -a
2. rpm -qa | grep oracleasm
3 cat /etc/sysconfig/oracleasm
4. upload /var/log/oracleasm
5. cat /proc/partitions
6. ls -la /dev/oracleasm/disks
7. /etc/init.d/oracleasm scandisks
8. /etc/init.d/oracleasm listdisks
9. Run "sosreport" from command line. That will generate a bzip file. Attach it to SR
10. Send me following command outputs
a. cat /proc/partitions |grep sd|while read a b c d;do echo -n $d$'\t'" scsi_id=";(echo $d|tr -d [:digit:]|xargs -i scsi_id -g -s /block/{})done
b. blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;
11. Also you can verify the disk has correct header as follows:
# dd if=/dev/path/to/disk bs=16 skip=2 count=1 | hexdump -C
example:
# dd if=/dev/mapper/data0p1 bs=16 skip=2 count=1 | hexdump -C
1+0 records in
1+0 records out
16 bytes (16 B) copied, 0.037821 seconds, 0.4 kB/s
00000000 4f 52 43 4c 44 49 53 4b 44 41 54 41 30 00 00 00 |ORCLDISKDATA0...|

Reference note: Troubleshooting a multi-node ASMLib installation (Doc ID 811457.1)

Query ASM Disks and Diskgroups

List all ASM devices:
/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
 cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;'


List all AVAILABLE ASM disks:
col path format a20
col header_status format a13
col os_mb format 999,999,999 heading 'Size (MB)'
SELECT inst_id, 
       path, 
       header_status, 
       os_mb 
  FROM GV$ASM_DISK 
 WHERE header_status in ('FORMER','PROVISIONED')
 ORDER BY path,
          inst_id;

Sample Output:
INST_ID  PATH                 HEADER_STATUS    Size (MB)
-------- -------------------- ------------- ------------
       1 ORCL:DISK25          FORMER             524,294
       2 ORCL:DISK25          FORMER             524,294
       1 ORCL:DISK26          FORMER             524,294
       2 ORCL:DISK26          FORMER             524,294
       1 ORCL:DISK30          FORMER             524,294
       2 ORCL:DISK30          FORMER             524,294
       1 ORCL:DISK33          PROVISIONED        524,294
       2 ORCL:DISK33          PROVISIONED        524,294


List All ASM DISKGROUPS:
set pagesize 60
set linesize 132
column aa format 99999 heading "DiskGroup"
column ab format a15 heading "DiskGroup"
column ac format a20 heading "Disk"
column ad format a15 heading "DiskGroup State"
column ae format a15 heading "Disk State"
break on ab skip 1
select substr(to_char(a.group_number),1,5) aa, substr(a.name,1,15) ab, substr(b.name,1,20) ac , b.total_mb, b.free_mb, a.state ad, b.state ae from
v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number
--and a.group_number = 4
order by 2,3
/

Sample Output:
DiskG DiskGroup       Disk              TOTAL_MB    FREE_MB DiskGroup State Disk State
----- --------------- --------------- ---------- ---------- --------------- ---------------
1     DATA            DATA1               517893     183780 MOUNTED         NORMAL
1                     DATA2               517893     183783 MOUNTED         NORMAL
1                     DATA3               517893     183781 MOUNTED         NORMAL
1                     DATA4               517893     183781 MOUNTED         NORMAL
1                     DATA5               517893     183780 MOUNTED         NORMAL
2     FRA             FRA1                517893     426005 MOUNTED         NORMAL
3     VOTING          VOTING                8631       8235 MOUNTED         NORMAL


Rebalance Operations:
11g
select inst_id, 
       operation, 
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c
select inst_id, 
       pass,
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c added a "COMPACT" pass to improve disk seek performance.

Sample Output:
   INST_ID OPERA STAT      POWER      SOFAR   EST_WORK   EST_RATE EST_MINUTES
---------- ----- ---- ---------- ---------- ---------- ---------- -----------
         1 REBAL RUN           5     314121     314121       1029           0
         1 REBAL WAIT          5
         2 REBAL RUN           5       9724     188000       1289         138
         2 REBAL WAIT          5

Query Disk Compatibility:
col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20
select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

Additional reference:
How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1? (Doc ID 470211.1)

ASM Diskgroup Space Used / Free

SQL:
set linesize 140
col group_number heading 'Diskgroup|Number' format 999
col diskgroup heading 'Name' format a20
col total_mb heading 'Allocated (MB)' format 999,999,999
col free_mb heading 'Available (MB)' format 999,999,999
col tot_used heading 'Used (MB)' format 999,999,999
col pct_used heading '% Used' format 999
col pct_free heading '% Free' format 999
select group_number,
       name diskgroup,
       total_mb,
       free_mb,
       total_mb-free_mb tot_used,
       pct_used,
       pct_free
  from (select group_number,name,total_mb,free_mb,
             round(((total_mb-nvl(free_mb,0))/decode(total_mb,0,1,total_mb))*100) pct_used,
             round((free_mb/total_mb)*100) pct_free
      from v$asm_diskgroup
      where total_mb >0
      order by pct_free
     )
/

SAMPLE OUTPUT:
Diskgroup
   Number Name            Allocated (MB) Available (MB)   Used (MB) % Used % Free
--------- --------------- -------------- -------------- ----------- ------ ------
        2 DATA2                5,767,234      1,860,008   3,907,226     68     32
        1 DATA1                5,767,234      1,996,305   3,770,929     65     35