Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
It is useful to check occasionally whether everything is normal in the Oracle ASM instance. To do this, some information should be collected and the asm instance’s alert log file should be examined.
You can collect the necessary information by using the following script. With this information, you can observe whether everything is OK.
You must connect to the ASM instance and run the script. The script will produce an output of /tmp/asmdebug.our. You can change the location and name of this file from the spool line.
You can connect to the ASM instance as follows and run the script.
1 2 3 4 5 6 7 8 9 10 11 12 | [oracle@oradb01 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 21 15:46:08 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options SQL> @/home/oracle/asmdebug.sql |
The script contents are as follows. You can run the following file by adding the below script into it.
/home/oracle/asmdebug.sql
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 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | set newpage none set linesize 100 spool /tmp/asmdebug.out -- -- Get a timestamp select rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual; -- -- Diskgroup information set head off select 'Diskgroup Information' from dual; set head on column name format a15 column DG# format 99 select group_number DG#, name, state, type, total_mb, free_mb from v$asm_diskgroup; -- -- Get the # of Allocation Units per DG set head off select 'Number of AUs per diskgroup' from dual; set head on select count(number_kfdat) AU_count, group_kfdat DG# from x$kfdat group by group_kfdat; -- -- Get the # of Allocation Units per DiskGroup and Disk set head off select 'Number of AUs per Diskgroup,Disk' from dual; col "group#,disk#" for a30 set head on select count(*)AU_count, GROUP_KFDAT||','||number_kfdat "group#,disk#" from x$kfdat group by GROUP_KFDAT,number_kfdat; -- -- Get the # of allocated (V) and free (F) Allocation Units set head off select 'Number of allocated (V) and free (F) Allocation Units' from dual; col "VF" for a2 set head on select GROUP_KFDAT "group#", number_kfdat "disk#", v_kfdat "VF", count(*) from x$kfdat group by GROUP_KFDAT, number_kfdat, v_kfdat; -- -- Get the # of Allocation Units per ASM file set head off select 'Number of AUs per ASM file ordered by AU count for metadata only' from dual; set head on select count(XNUM_KFFXP) AU_count, NUMBER_KFFXP file#, GROUP_KFFXP DG# from x$kffxp where NUMBER_KFFXP < 256 group by NUMBER_KFFXP, GROUP_KFFXP order by count(XNUM_KFFXP) ; -- -- Get the # of Allocation Units per ASM file by file alias. Change the -- system_created Y|N depending if you want the short or long ASM name set head off select 'Number of AUs per ASM file ordered by AU count. This is for non metadata' from dual; col name format a60 set head on select GROUP_KFFXP, NUMBER_KFFXP, name, count(*) from x$kffxp, v$asm_alias where GROUP_KFFXP=GROUP_NUMBER and NUMBER_KFFXP=FILE_NUMBER and system_created='Y' group by GROUP_KFFXP, NUMBER_KFFXP, name order by GROUP_KFFXP, NUMBER_KFFXP; -- -- Get partner information. This is really only useful if redundancy is other than -- external. set head off select 'The following shows the disk to partner relationship. This is really only useful if using normal or high redundancy.' from dual; set head on select grp DG#, disk, NUMBER_KFDPARTNER partner, PARITY_KFDPARTNER parity, ACTIVE_KFDPARTNER active from x$kfdpartner; -- -- Another look at file utilization. set head off set linesize 132 select 'bytes is the sum of AUs with data in them * 1024^2 space is the sum of all AUs allocated for this file * 1024^2' from dual; set head on col Name format a60 select f.group_number, f.file_number, bytes, space, space/(1024*1024) "InMB", a.name "Name" from v$asm_file f, v$asm_alias a where f.group_number=a.group_number and f.file_number=a.file_number and system_created='Y' order by f.group_number, f.file_number; -- -- Get robust disk information set linesize 400 col failgroup format a20 col label format a20 col name format a40 col path format a40 set head off select 'Robust disk information' from dual; set head on select GROUP_NUMBER, DISK_NUMBER, INCARNATION, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, LIBRARY, TOTAL_MB, FREE_MB, NAME, FAILGROUP, LABEL, PATH, CREATE_DATE, MOUNT_DATE, READS, WRITES, READ_ERRS, WRITE_ERRS, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN from v$asm_disk; -- spool off |
