十条ASM下令，让你对Oracle数据库中ASM情况管窥蠡测

概述

之以是写这篇文章由于平常用的RAC数据库中ASM的一些下令总记不住，以是就对ASM这块做了一些总结，主要分红10个下令，稀有的一些ASM下令基本都包含了，各位一同看看吧~

1、查察ASM使用情况

SQL>select group_number,name,total_mb,free_mb from v$asm_diskgroup;

2、查察磁盘途径

SQL> col path for a50
SQL> select group_number,name, path from v$asm_disk_stat;

3、查察磁盘组信息

SQL> select state,name,type,total_mb, free_mb from v$asm_diskgroup_stat;

磁盘组形态为Mounted分析磁盘组形态正常;

磁盘组的total_mb,free_mb为非0分析可以读取磁盘信息,获取磁盘轻重。

4、查察磁盘组利用

select * from v$asm_operation;

假如有信息分析正在加盘大概减盘。

5、查察磁盘组属性

查询V$ASM_ATTRIBUTE视图查察磁盘组属性

SQL>col diskgroup for a30
SQL>col name for a50
SQL>col value for a30
SQL>col read_only for a30
SQL> SELECT dg.name AS diskgroup,SUBSTR(a.name,1,18) AS name,SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP_STAT dg,V$ASM_ATTRIBUTE a WHERE dg.name = ‘RFDATA’ AND dg.group_number = a.group_number;

6、查察磁盘组兼容属性

SQL> col name for a30
SQL> col COMPATIBILITY for a30
SQL> col DATABASE_COMPATIBILITY for a30
SQL> SELECT name,COMPATIBILITY,DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP_STAT;

7、查察磁盘形态

SQL> col ASMDISK for a30
SQL> col MOUNT_STATUS for a30
SQL> col HEADER_STATUS for a30
SQL> col state for a20
SQL> col DISKGROUP for a30
SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk,d.mount_status,HEADER_STATUS, d.state, dg.name AS diskgroup FROM V$ASM_DISKGROUP_STAT dg, V$ASM_DISK_STAT d WHERE dg.group_number = d.group_number;

8、查察磁盘客户端信息

SQL> col INSTANCE for a30
SQL> col dbname for a20
SQL> col SOFTWARE for a30
SQL> col COMPATIBLE for a30
SQL> 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_STAT dg, V$ASM_CLIENT c WHERE dg.group_number=c.group_number;

9、查察磁盘拜候控制用户信息

SQL> SELECT dg.name AS diskgroup, u.group_number,u.user_number, u.os_id, u.os_name FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u WHERE dg.group_number = u.group_number AND dg.name =’RFDATA’;

10、查察智能数据分布信息

SQL> SELECT dg.name AS diskgroup, f.file_number, f.primary_region,f.mirror_region, f.hot_reads,f.hot_writes, f.cold_reads, f.cold_writes FROM V$ASM_DISKGROUP_STAT dg, V$ASM_FILE f WHERE dg.group_number = f.group_number and dg.name =’RFDATA’;

后方会分享更多devops和DBA方面的内容，感兴致的伙伴可以眷注下！