Oracle ASM常用命令

set lines 150 pages 200
col path for a40
col HEADER_STATUS for a9
col disk_name for a12
col type for a16
col bytes for 999,999,999,999
col name for a52
col name2 for a15
col group_number heading 'Group|_NO' format 99
col file_number heading 'FILE|NO' format 9999
col redundancy format a6 noprint
col striped format a6 noprint
col FAILGROUP for a10
col disk_number heading 'Disk|_NO' format 9999
col MOUNT_STATUS heading 'Mount|_Status'
col FAILGROUP heading 'FAIL|_GROUP'
col TOTAL_MB heading 'Total|_MB' format 99,999,999

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

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

select disk_number,free_mb,total_mb,FAILGROUP,MOUNT_STATUS,STATE,HEADER_STATUS,PATH
from v$asm_disk order by 4,8;

--------如果有别名，查看磁盘情况：
/etc/init.d/oracleasm listdisks

/etc/init.d/oracleasm querydisk -v -p -d OCR01
/etc/init.d/oracleasm querydisk -v -p -d VOT04

--------查看磁盘情况(kfod)
$ kfod disks=all

--------查看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;

--------
select a.group_number, a.file_number, a.compound_index, a.incarnation,
a.block_size, a.blocks, a.bytes/(1024*1024) mbytes, a.space/(1024*1024) mspace,
a.type, a.redundancy, a.striped, a.creation_date, a.modification_date, b.name
from v$asm_file a, v$asm_alias b
where a.type != 'ARCHIVELOG'      --为了易读性
and a.group_number = b.group_number
and a.file_number = b.file_number
and a.incarnation = b.file_incarnation
order by a.type, a.file_number;

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

alter diskgroup datadg check all repair ;

ALTER DISKGROUP datadg REBALANCE POWER 11;

--------查看asm实例操作变化（只记录结构变化操作）
select * from v$asm_operation;


--------创建磁盘组
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';

--------为asm磁盘组添加failgroup
alter diskgroup datadg add failgroup failgp1 disk '/dev/vx/rdsk/asmdg1/asmvol19' ;

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

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

ALTER SYSTEM SET
asm_diskstring=
'/dev/vx/rdsk/oradata122/*','/dev/vx/rdmp/emc*'
scope=both sid='+ASM2';

--------创建表空间，空间指定ASM磁盘组
create tablespace data01 datafile '+DATA2' size 10200M;

echo SPFILE='/oracle/10g/dbs/spfileASM.ora' > /oracle/10g/dbs/init\+ASM1.ora
echo SPFILE='/oracle/10g/dbs/spfileASM.ora' > /oracle/10g/dbs/init\+ASM2.ora

chown oracle:dba /oracle/10g/dbs/*