asm 健康检查 乱入

通过一些脚本检查asm 磁盘状态

-- check asm disk
-- example ：@ac.sql DATADG

set ver off
set lin 200 pages 100
col DISKGROUP for a12
col name for a30
col value for a15
SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
     SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg,
     V$ASM_ATTRIBUTE a WHERE dg.name = '&1' AND dg.group_number = a.group_number
     AND a.name NOT LIKE '%template%';

col ASM_COMPAT for a20
col DB_COMPAT for a20
col FAILGROUP for a20
SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
     substr(database_compatibility,1,12) AS db_compat,ALLOCATION_UNIT_SIZE/1024/1024 au_size_M,type,state,total_mb,free_mb,OFFLINE_DISKS,VOTING_FILES FROM V$ASM_DISKGROUP;

col path for a30
col name for a20
col mount_status for a10
col header_status for a12
select GROUP_NUMBER,name,path,MOUNT_STATUS,HEADER_STATUS,STATE,VOTING_FILE,total_mb,free_mb from v$asm_disk order by 1,2;

col disk_name for a20
SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS disk_name,
     d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup
     FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

 SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk,
       ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time,
       bytes_read, bytes_written
       FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds
       WHERE dgs.group_number = ds.group_number AND dgs.name = '&1';

col INSTANCE for a15
col DBNAME for a15
col SOFTWARE for a20
col COMPATIBLE for a20
SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
    SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
    SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
    FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
    WHERE dg.group_number = c.group_number;

SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u
     WHERE dg.group_number = u.group_number AND dg.name = '&1';

SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
     ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug
     WHERE dg.group_number = ug.group_number AND dg.name = '&1'
     AND ug.owner_number = u.user_number;

SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name,
     um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um,
     V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND
     dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = '&1'
     AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
       f.usergroup_number, ug.name
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug, V$ASM_FILE f, V$ASM_ALIAS a
     WHERE dg.name = '&1' AND dg.group_number = u.group_number AND
       u.group_number = ug.group_number AND ug.group_number = f.group_number AND
       f.group_number = a.group_number AND
       f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number AND
       f.file_number = a.file_number;

col error_code for a10
select * from v$asm_operation;

--自动扩展是否合理（每次扩512M）
col file_name for a60
SELECT distinct df.file_name FROM dba_data_files df, v$asm_alias a,
(SELECT ad.group_number, count(*) DG_num_disks, att.value, (count(*) * att.value) Ext_Size
FROM v$asm_disk ad, V$ASM_ATTRIBUTE att
WHERE ad.group_number=att.group_number
AND att.name = 'au_size'
GROUP BY ad.group_number,att.value) v
WHERE autoextensible = 'YES'
AND status = 'AVAILABLE'
AND a.group_number=v.group_number
AND upper (df.file_name) in
(SELECT concat('+'||gname,sys_connect_by_path(aname, '/'))
FROM (SELECT vg.name gname, va.parent_index pindex, upper(va.name) aname, va.reference_index rindex, va.group_number gnum,va.file_number filnum
FROM v$asm_alias va,v$asm_diskgroup vg
WHERE va.group_number = vg.group_number)
START WITH (MOD(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
AND MOD((df.increment_by * (SELECT value FROM v$parameter WHERE name = 'db_block_size')), v.Ext_Size) > 0;

以上方法不太完整，如果想获取所有asm相关信息需要以下脚本
asm_meta.sql
调用示例：
su - grid
sqlplus / as sysasm @asm_meta
然后下载当前路径下的文件ASM_METADATA.html，用浏览器查看

asmcmd_script.zip
调用示例：
su - grid
sh ./asmcmd_script.sh
然后查看 /tmp/asmcmd_script.out

参考：
怎样格式化收集以及备份 10.1，10.2，11.1，11.2 , 12.1和12.2 的 ASM、ACFS 元数据？ (Doc ID 2226530.1)