ORACLE _11G_R2_ASM 常用命令

--------查看磁盘组，(空间情况)
select name,state,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;

--------查看磁盘情况
select GROUP_NUMBER,free_mb,total_mb,disk_number,MOUNT_STATUS,mode_status,
HEADER_STATUS,name,PATH
from v$asm_disk order by 4,5;

select g.group_number,g.name,d.name,d.path from v$asm_diskgroup g,v$asm_disk d where g.group_number=d.group_number;

select GROUP_NUMBER,free_mb,total_mb,disk_number,
HEADER_STATUS,name,PATH
from v$asm_disk order by 4;

---查看是否有数据库实例连接上ASM实例
select instance_name,db_name,status from v$asm_client;

--------查看asm存储文件的层次图(树状)
select lpad(' ', 4*(level-1))||name name ,REFERENCE_INDEX,PARENT_INDEX,GROUP_NUMBER
from v$asm_alias
start with ALIAS_INDEX = 0
connect by  prior REFERENCE_INDEX=PARENT_INDEX ;

--------查看层次图-类型和大小
select a.GROUP_NUMBER,b.name,a.file_number,a.bytes,a.type ,a.modification_date
from v$asm_file a ,v$asm_alias b
where a.file_number=b.file_number
  and a.GROUP_NUMBER = b.GROUP_NUMBER
order by 1,5;

--------查看及修改asm重新平衡速度
show parameter power

alter diskgroup data check all repair ;;--检测一致性，并修复错误

ALTER DISKGROUP data REBALANCE POWER 11;--设置最高速度来做平衡的操作

--------查看asm实例操作变化平衡的进度（只记录结构变化操作）
 select group_number,operation,power,est_minutes from v$asm_operation;

#############################################################################

---手动平衡磁盘组
alter diskgroup data rebalance power 5;

--------创建磁盘组
create diskgroup DATA2 external redundancy disk
    '/dev/vx/rdmp/Disk_0s2',
    '/dev/vx/rdmp/Disk_4s2',  
    '/dev/vx/rdmp/Disk_5s2'

create diskgroup DATA2 external redundancy disk
    '/dev/rdisk/ora_data_01' ATTRIBUTE  'compatible.asm' = '11.2';;

--------删除磁盘组的某个磁盘
alter diskgroup datadg drop disk  DATADG_0000  ;
alter diskgroup datadg drop disk  DATADG_0001  ;

SQL> alter diskgroup DATA drop disk A4; 
SQL> alter diskgroup DATA undrop disks; 

--------修改asm实例搜索可用asm磁盘路径，不同路径用逗号隔开
ALTER SYSTEM SET
asm_diskstring=
'/dev/vx/rdsk/oradata122/*','/dev/vx/rdmp/emc*'
scope=both sid='+ASM1';

使用asmlib时磁盘的格式：
alter diskgroup  DATA2 add disk 'ORCL:ASMDISK09';
SQL> alter diskgroup fra add disk 'ORCL:ASMDISK06' NAME A6;

--卸载所有磁盘组
SQL> alter diskgroup all dismount; 
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup; 

--挂载所有磁盘组
SQL> alter diskgroup all mount;
select group_number,name,state,total_mb,free_mb from v$asm_diskgroup; 

--如果当前磁盘组有实例在访问，则不能被卸载 
SQL> alter diskgroup data dismount; 

#############################################################################
a、在DG上创建细粒度模板：
   alter diskgroup data add template stp_fine attributes (unprotected fine);
   
   b、创建使用细粒度可调条带的表空间
   create tablespace test datafile '+data(stp_fine)/test.dbf' size 10m;

c、创建非标准大小AU的磁盘组，一般在OLAP系统上建议使用。
CREATE DISKGROUP DATA2 external redundancy DISK '/dev/raw/raw11', '/dev/raw/raw10',
'/dev/raw/raw9' ATTRIBUTE 'au_size' = '16M', 'compatible.asm' = '11.2';

CREATE DISKGROUP DATA external redundancy DISK 'ORCL:ASMDISK01', 'ORCL:ASMDISK02'
 ATTRIBUTE  'compatible.asm' = '11.2';

select name,state,compatibility,ALLOCATION_UNIT_SIZE from v$asm_diskgroup;