Using SQL Script Get Information about ASM

當診斷ASM問題時，如果知道ASM磁碟組名，磁碟組型別，磁碟狀態，ASM例項初始化引數，與是否有rebalance操作在執行，對於診斷都是有幫助的。在這些情況下，通常會生成一個html格式的報告，通過在ASM例項中執行SQL指令碼來生成。該指令碼如下:

[grid@jyrac1 ~]$ cat asm_report.sql

spool /home/grid/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

為了生成報告並儲存為/home/grid/ASM_report.html，以Grid Infrastructure使用者(通常為grid或oracle)來執行

[grid@jyrac1 ~]$ sqlplus / as sysasm @asm_report.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 12 09:44:24 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

ASM report

 
  

Time
12-Jan-2017 09:44:24

Version

 
  

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

.....

NORMAL

End of ASM report
SQL> set feedback on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options

報告內容
報告首先顯示了報告生成的時間與ASM的版本。

如果存在執行的任何ASM操作將會顯示，我這裡沒有正在執行的ASM操作。所以Cluster wide operations下面沒有內容。

接下來可以看到所有磁碟組，包含dismounted磁碟組。

接下來可以看到磁碟，也會包含那些狀態為candidate的磁碟

接下來是關於離線磁碟的資訊，因為我這裡沒有離線磁碟，所以該部分內容為空

再接下來就是磁碟組屬性

接下來是連線到ASM的客戶端

ASM初始化引數包含隱含引數與一些Exadata特定引數(_auto_manage)

記憶體，叢集與例項特定引數

最後是磁碟組不平衡資訊

小結:
使用這個報告可以快速檢視ASM相關資訊，它也可以作為ASM設定的一種備份。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2132311/，如需轉載，請註明出處，否則將追究法律責任。