(转载)磁盘组操作
1.查看现有磁盘组信息
select group_number gno,name,state,type,total_mb,free_mb,required_mirror_free_mb rmfmb,usable_file_mb ufmb from v$asm_diskgroup;
2.查看磁盘信息
select group_number gno,name,failgroup fgno,state,total_mb,free_mb,header_status from v$asm_disk;
方法二：
select name,path,state,mount_status,header_status from v$asm_disk;
ignored状态也是不正常的。
closed表示磁盘未被当前实例使用，member表示这个磁盘已经是某个diskgroup的成员了
方法三：fdisk -l查看磁盘状态
查看磁盘组列表
[root@racdb1 /]# oracleasm listdisks
ARCH1
ARCH2
ARCH3
DATA1
DATA2
DATA3
DATA4
OCR1
OCR2
OCR3
在节点2查看
[root@racdb2 /]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@racdb2 /]# oracleasm listdisks
[root@racdb2 /]#pwd
/dev/oracleasm/disk/
3.查看ASM磁盘路径
select group_number,name,path,state from v$asm_disk;
红色框中为新添加的磁盘，未分配的磁盘group为0，NAME为空
4.添加磁盘到磁盘组(+ASM实例中操作)
SQL> alter diskgroup ARCH add disk '/dev/oracleasm/disks/ARCH3';
Diskgroup altered.
补充：
SQL> alter diskgroup ARCH add disk '/dev/oracleasm/disks/ARCH3' rebalance power 10;
因为磁盘要做rebalance，最好在rebalance完后再使用磁盘组
select * from v$asm_operation;
查看磁盘组操作
SQL> select * from v$asm_operation;
5.查看磁盘状态
select group_number,name,path,state from v$asm_disk
红色部分自动分配了NAME，并制定了ARCH组对应的组号
6.磁盘组的创建和删除
创建磁盘组
create diskgroup dg3 normal redundancy disk 'ORCL:VOL9','ORCL:VOL10';
删除磁盘
SQL> alter diskgroup dg1 drop disk VOL9;
Diskgroup altered.
取消删除
alter diskgroup dg1 undrop disks;
调整大小
ALTER DISKGROUP DG1 RESIZE DISK VOL4 SIZE 1024 M
7.unmount和mount磁盘组
SQL> alter diskgroup dg3 dismount;
Diskgroup altered.
SQL> select group_number gno,name,state,type,total_mb,free_mb,required_mirror_free_mb rmfmb,usable_file_mb ufmb from v$asm_diskgroup;
GNO NAME STATE TYPE TOTAL_MB FREE_MB RMFMB UFMB
---------- ------------------------------ ---------------------- ------------------------------ ---------- ---------- ---------- ----------
1 DG1 MOUNTED NORMAL 4076 575 879 -152
2 DG2 MOUNTED NORMAL 4076 3531 139 1696
0 DG3 DISMOUNTED
SQL> alter diskgroup dg3 mount;
Diskgroup altered.
8.查看磁盘组属性
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.group_number = a.group_number;
9.查看磁盘组兼容属性
SQL> SELECT name,COMPATIBILITY,DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP_STAT;
10.查看磁盘客户端信息
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;
11.查看磁盘访问控制信息
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 ='DATA';
12.查看磁盘访问控制组信息
SQL> SELECT dg.name AS diskgroup, ug.group_number,ug.owner_number, u.os_name,
ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u, V$ASM_USERGROUP ug
WHERE dg.group_number = ug.group_number AND dg.name ='DATA'
AND ug.owner_number = u.user_number;
13.查看数据分布信息
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 ='DATA';
14.ASM 磁盘的相关视图
v$asm_disk(_stat) --查看磁盘及其状态信息
v$asm_diskgroup(_stat) --查看磁盘组及其状态信息
v$asm_operation --查看当前磁盘的操作信息
v$asm_client --返回当前连接的客户端实例信息
v$asm_file --返回asm文件的相关信息
v$asm_template --返回asm文件样本的相关信息
v$asm_alias --返回asm文件的别名信息
