<\/p>\n
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.<\/p>\n
You can collect the necessary information by using the following script. With this information, you can observe whether everything is OK.<\/p>\n
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.<\/p>\n
You can connect to the ASM instance as follows and run the script.<\/p>\n
[oracle@oradb01 ~]$ sqlplus \/ as sysasm\r\n\r\nSQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 21 15:46:08 2016\r\n\r\nCopyright (c) 1982, 2011, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Real Application Clusters and Automatic Storage Management options\r\n\r\nSQL> @\/home\/oracle\/asmdebug.sql<\/pre>\n<\/p>\n
The script contents are as follows. You can run the following file by adding the below script into it.<\/p>\n
\/home\/oracle\/asmdebug.sql<\/p>\n
set newpage none \r\nset linesize 100\r\nspool \/tmp\/asmdebug.out\r\n--\r\n-- Get a timestamp\r\nselect rpad('>', 10, '>'), to_char(sysdate, 'MON DD HH24:MM:SS') from dual;\r\n--\r\n-- Diskgroup information\r\nset head off\r\nselect 'Diskgroup Information' from dual;\r\nset head on\r\ncolumn name format a15\r\ncolumn DG# format 99\r\nselect group_number DG#, name, state, type, total_mb, free_mb from\r\nv$asm_diskgroup;\r\n--\r\n-- Get the # of Allocation Units per DG\r\nset head off\r\nselect 'Number of AUs per diskgroup' from dual;\r\nset head on\r\nselect count(number_kfdat) AU_count, group_kfdat DG# from x$kfdat\r\ngroup by group_kfdat;\r\n--\r\n-- Get the # of Allocation Units per DiskGroup and Disk\r\nset head off\r\nselect 'Number of AUs per Diskgroup,Disk' from dual;\r\ncol \"group#,disk#\" for a30\r\nset head on\r\nselect count(*)AU_count, GROUP_KFDAT||','||number_kfdat \"group#,disk#\" from x$kfdat group by GROUP_KFDAT,number_kfdat;\r\n--\r\n-- Get the # of allocated (V) and free (F) Allocation Units\r\nset head off\r\nselect 'Number of allocated (V) and free (F) Allocation Units' from dual;\r\ncol \"VF\" for a2\r\nset head on\r\nselect GROUP_KFDAT \"group#\", number_kfdat \"disk#\", v_kfdat \"VF\", count(*)\r\nfrom x$kfdat\r\ngroup by GROUP_KFDAT, number_kfdat, v_kfdat;\r\n\r\n\r\n--\r\n-- Get the # of Allocation Units per ASM file\r\nset head off\r\nselect 'Number of AUs per ASM file ordered by AU count for metadata only'\r\nfrom dual;\r\nset head on\r\nselect count(XNUM_KFFXP) AU_count, NUMBER_KFFXP file#, GROUP_KFFXP DG# from x$kffxp where NUMBER_KFFXP < 256\r\ngroup by NUMBER_KFFXP, GROUP_KFFXP\r\norder by count(XNUM_KFFXP) ;\r\n--\r\n-- Get the # of Allocation Units per ASM file by file alias. Change the\r\n-- system_created Y|N depending if you want the short or long ASM name\r\nset head off\r\nselect 'Number of AUs per ASM file ordered by AU count. This is for non\r\nmetadata' from dual;\r\ncol name format a60\r\nset head on\r\nselect GROUP_KFFXP, NUMBER_KFFXP, name, count(*)\r\nfrom x$kffxp, v$asm_alias\r\nwhere GROUP_KFFXP=GROUP_NUMBER and NUMBER_KFFXP=FILE_NUMBER and\r\nsystem_created='Y'\r\ngroup by GROUP_KFFXP, NUMBER_KFFXP, name\r\norder by GROUP_KFFXP, NUMBER_KFFXP;\r\n--\r\n-- Get partner information. This is really only useful if redundancy is other than\r\n-- external.\r\nset head off\r\nselect 'The following shows the disk to partner relationship. This is really only\r\nuseful if using normal or high redundancy.' from dual;\r\nset head on\r\nselect grp DG#, disk, NUMBER_KFDPARTNER partner, PARITY_KFDPARTNER parity, ACTIVE_KFDPARTNER active\r\nfrom x$kfdpartner;\r\n--\r\n-- Another look at file utilization.\r\nset head off\r\nset linesize 132\r\nselect 'bytes is the sum of AUs with data in them * 1024^2\r\nspace is the sum of all AUs allocated for this file * 1024^2'\r\nfrom dual;\r\nset head on\r\ncol Name format a60\r\nselect f.group_number, f.file_number, bytes, space, space\/(1024*1024) \"InMB\", a.name \"Name\"\r\nfrom v$asm_file f, v$asm_alias a\r\nwhere f.group_number=a.group_number and f.file_number=a.file_number\r\nand system_created='Y'\r\norder by f.group_number, f.file_number;\r\n--\r\n-- Get robust disk information\r\nset linesize 400\r\ncol failgroup format a20\r\ncol label format a20\r\ncol name format a40\r\ncol path format a40\r\nset head off\r\nselect 'Robust disk information' from dual;\r\nset head on\r\nselect GROUP_NUMBER, DISK_NUMBER, INCARNATION, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, LIBRARY, TOTAL_MB, FREE_MB,\r\nNAME, FAILGROUP, LABEL, PATH, CREATE_DATE, MOUNT_DATE, READS,\r\nWRITES, READ_ERRS, WRITE_ERRS, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN\r\nfrom v$asm_disk;\r\n--\r\nspool off<\/pre>\n\n<\/div>\n