asm 健康检查乱入-凯发app官方网站

-- 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相关信息需要以下脚本

调用示例：
su - grid
sqlplus / as sysasm @asm_meta
然后下载当前路径下的文件asm_metadata.html，用浏览器查看

调用示例：
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)

阅读(555) | 评论(0) | 转发(0) |

上一篇：ocr 恢复

下一篇：诊断gridsetup.sh

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们

asm 健康检查 乱入-凯发app官方网站

asm 健康检查乱入-凯发app官方网站