asm 常用操作命令

select B.NAME, T.name, T.path, T.total_mb/1024/1024
from v$asm_disk t, v$asm_diskgroup b
where T.GROUP_NUMBER = B.GROUP_NUMBER
order BY B.NAME,T.name;
select name,state,total_mb/1024/1024,free_mb/1024/1024,usable_file_mb/1024 from v$asm_diskgroup;
select total_mb/1024/1024,free_mb/1024/1024,usable_file_mb/1024 from v$asm_diskgroup;
select * from v$asm_diskgroup;

select name,library,path, os_mb,state from v$asm_disk t order by name ;

alter diskgroup OCR mount;
select path, os_mb,state from v$asm_disk t where t.mount_status='CLOSED' order by name ;
--剩余存储
select sum(os_mb)/1024/1024 from v$asm_disk t where t.mount_status='CLOSED' order by name ;

select * from v$asm_disk t order by name ;
su - grid
sqlplus / as sysasm
alter diskgroup SSDDG add disk '/dev/mapper/asm-disk0036' name SSDDG_0003 rebalance;
alter diskgroup SSDDG01 add disk '/dev/mapper/asm-disk0035' name SSDDG01_0007 rebalance;

alter diskgroup SSDDG add disk '/dev/mapper/asm-disk0034' name SSDDG_0004 rebalance;
alter diskgroup SSDDG01 add disk '/dev/mapper/asm-disk0033' name SSDDG01_0008 rebalance;

alter diskgroup SSDDG add disk '/dev/mapper/asm-disk0032' name SSDDG_0005 rebalance;
alter diskgroup SSDDG01 add disk '/dev/mapper/asm-disk0031' name SSDDG01_0009 rebalance;

select * from v$asm_file t where t."FILE_NUMBER" in(309,441,283);
select * from v$asm_volume;
select name,path,os_mb/1024/1024,total_mb/1024/1024,free_mb/1024/1024 from v$asm_disk_stat order by name;

select sum(total_mb)/1024/1024 from v$asm_disk_stat;
select * from v$asm_disk_stat t where t."GROUP_NUMBER"=3 and t."DISK_NUMBER" in(2,4) order by name ;
select name,total_mb,free_mb from v$asm_diskgroup;

select sum(bytes)/1024/1024/1024 from SYS.DBA_DATA_FILES t

select * from SYS.DBA_DATA_FILES t where t.FILE_ID in(1579,1518,1582,1441,1599,1700,1467,1522,1593,1439,1550,1578,1514,1663,1697);

--修改磁盘组属性11g
alter diskgroup DATA set attribute 'compatible.asm' = '11.2.0.4';
alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2.0.4';
alter diskgroup DATA set attribute 'disk_repair_time'='36h';

alter diskgroup OCRVOTE set attribute 'compatible.asm' = '11.2.0.4';
alter diskgroup OCRVOTE set attribute 'compatible.rdbms' = '11.2.0.4';
alter diskgroup OCRVOTE set attribute 'disk_repair_time'='36h';

---创建ASM磁盘组force 、size 为可选参数
---冗余模式：normal
create diskgroup ssddg normal redundancy
failgroup sto7 disk
'/dev/qdata/mpath-s01.3262.01.qcache1-1' size 1836704M,
'/dev/qdata/mpath-s01.3263.01.qcache2-1' size 1836704M,
'/dev/qdata/mpath-s01.3264.01.qcache3-1' size 1836704M

failgroup sto8 disk
'/dev/qdata/mpath-s02.3262.01.P0B00S06' size 1836704M,
'/dev/qdata/mpath-s02.3263.01.P0B00S07' size 1836704M,
'/dev/qdata/mpath-s02.3264.01.P0B00S08' size 1836704M

failgroup sto10 disk
'/dev/qdata/mpath-s03.3262.01.n0000da000' size 1836704M,
'/dev/qdata/mpath-s03.3263.01.n0000db000' size 1836704M,
'/dev/qdata/mpath-s03.3264.01.n0000dc000' size 1836704M
attribute 'au_size'='1M';

--冗余模式：high
CREATE DISKGROUP high01 HIGH REDUNDANCY
FAILGROUP h1 DISK '/dev/qdata/mpath-s01.5002.01.P5B00S04' SIZE 102400M
FAILGROUP h2 DISK'/dev/qdata/mpath-s02.5002.01.P5B00S04' SIZE 102400M
FAILGROUP h3 DISK'/dev/qdata/mpath-s02.5002.01.P5B00S02p3' SIZE 102400M
attribute 'au_size'='1M';

--冗余模式：external
CREATE DISKGROUP redodg2 External REDUNDANCY
DISK '/dev/qdata/mpath-s02.5002.01.P5B00S02p3' SIZE 102400M ,'/dev/qdata/mpath-s02.5002.01.P5B00S04' SIZE 102400M;

---查看磁盘组的信息
asmcmd lsdg
asmcmd lsdsk -k

-- 使用SQL 查看
set lines 400
col group_name for a20
col MOUNT_STATUS for a10
col mode_status for a10
col disk_name for a30
col path for a50
col failgroup for a30

select b.name GROUP_NAME,a.name DISK_NAME,a.failgroup,a.path,a.mount_status,a.mode_status
from v$asm_disk a,v$asm_diskgroup b where a.group_number = b.group_number order by 2;

select failgroup,name,path,mode_status,mount_status from v$asm_disk;
select group_number, db_name, status from v$asm_client;

set pages 100
set lines 400
col disk_group_name for a10
col disk_file_path for a60
col disk_file_name for a15
col disk_file_fail_group for a15
break on disk_group_name skip 2
compute sum of used_mb on disk_group_name

SELECT NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
where b.total_mb <> 0
ORDER BY a.name

--往ASM中添加磁盘
--冗余模式：Nomalforce、name 为可选操作
--往 ASM 里的某个 failgroup 中添加磁盘
alter diskgroup [DG_NAME] add failgroup sto7 disk '/dev/qdata/mpath-s01.3261.01.P0B00S08' size 22831832M;
alter diskgroup MGMT add failgroup MGMT disk '/dev/qdata/mpath-s02.4444.01.mgmt2';

--执行以上语句后，如果报 ORA-15033 错误，可先将所加磁盘 dd（抹掉盘头信息） 后再添加
--dd 抹盘
--## 示例：

dd if=/dev/zero of=/dev/qdata/mpath-s01.5005.01.P5B00S02p2 bs=1M count=1024 oflag=direct

--往 ASM 里的所有 failgroup 中同时添加磁盘
alter diskgroup SSDDG add failgroup datasto12 disk '/dev/qdata/mpath-s01.5002.01.P5B00S05' name SSDDG_0001 size 102400M failgroup datasto13 disk'/dev/qdata/mpath-s02.5002.01.P5B00S05' name SSDDG_0006 size 102400M;

--冗余模式：External
alter diskgroup [DG_NAME] add disk '/dev/raw/raw3' name DATADG_0003 size 92160M; alter diskgroup [DG_NAME] add disk '/dev/asm-diskh' rebalance power 8;

--online磁盘组
--online 指定的磁盘

alter diskgroup [DG_NAME] online disk DATA_0004,DATA_0005;
--online 所有的磁盘
asmcmd online -G [DG_NAME] -a

--offline 磁盘
alter diskgroup SSDDG offline disk SSDDG_0000;

--mount磁盘组
alter diskgroup [DG_NAME] mount;

--dismount 磁盘组
dismount 磁盘组的话务必慎重操作，要确保 dismount 的磁盘组中保存的文件对应的数据库当前未启动，而且该操作也会直接导致数据库SHUTDOWN

alter diskgroup SSDNEW dismount;

--删除磁盘组，在删除之前将一个节点上的磁盘组dismount

drop diskgroup [DG_NAME]; drop diskgroup [DG_NAME] including contents;

--如果无法drop，可尝试在所有节点 dismount 后，再 mount 其中一个节点，然后再 drop

--删除ASM中的磁盘
force 为可选参数

alter diskgroup [DG_NAME] drop disk [DISK_NAME] force; alter diskgroup datadg drop disk DATADG_0000 force;

--调整重平衡的 power 值
alter diskgroup datadg rebalance power 11;