Oracle ASM磁盘和磁盘组的常用SQL语句

创建ASM磁盘

请参考：

ASM磁盘

export ORACLE_HOME=/u01/app/12.2.0/grid
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
/u01/app/12.2.0/grid/bin/kfod.bin asm_diskstring='/dev/asm-'


$ORACLE_HOME/bin/kfod disk=asm s=true ds=true c=true  asm_diskstring='/dev/raw/raw','/dev/asm-','ORCL:'
/grid/stage/ext/bin/kfod disk=asm s=true ds=true c=true  asm_diskstring='/dev/raw/raw','/dev/asm-','ORCL:'

18c

kfod disks=asm  ds=true cluster=true

create diskgroup DATA  external redundancy disk '/dev/raw/raw*';
create diskgroup FRA  external redundancy disk '/dev/rhdisk3';  --创建磁盘组FRA
CREATE DISKGROUP ACFSDG external redundancy DISK '/dev/oracleasm/disks/VOL1' ATTRIBUTE 'compatible.asm' = '11.2','compatible.rdbms' = '11.2','compatible.advm'='11.2';
create diskgroup OCR external redundancy disk 'ORCL:OVDISK' attribute 'compatible.asm'='11.2','compatible.rdbms'='11.2';

CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY  disk '/dev/raw/raw6', '/dev/raw/raw7';
CREATE DISKGROUP dgroup1 HIGH REDUNDANCY  disk '/dev/raw/raw6', '/dev/raw/raw7', '/dev/raw/raw8';



create diskgroup DG1 external  redundancy
  failgroup FG1 disk '/dev/raw/raw6' name DG2_FG1_VOL1
  failgroup FG2 disk '/dev/raw/raw7' name DG2_FG2_VOL2;

create diskgroup DG2 normal redundancy
  failgroup FG1 disk '/dev/raw/raw6' name DG2_FG1_VOL1
  failgroup FG2 disk '/dev/raw/raw7' name DG2_FG2_VOL2;

create diskgroup DG2 normal redundancy
  failgroup FG1 disk '/dev/raw/raw6','/dev/raw/raw7' name DG2_FG1_VOL1
  failgroup FG2 disk '/dev/raw/raw8','/dev/raw/raw9' name DG2_FG2_VOL2;



---修改磁盘组的兼容属性
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.asm' = '11.1';
ALTER DISKGROUP asm_dg SET ATTRIBUTE 'compatible.rdbms' = '11.1';

COLUMN name FORMAT A10
COLUMN compatibility FORMAT A20
COLUMN database_compatibility FORMAT A20
SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup;

set line 9999
set pagesize 9999
col path format a60
SELECT a.group_number, disk_number,mount_status, a.name, path FROM v$asm_disk a order by a.disk_number;
select instance_name,status from v$instance;

set line 999
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number, disk_number,mount_status, path from v$asm_diskgroup a;
select a.group_number,name,TYPE,state,TOTAL_MB,free_mb from v$asm_diskgroup a;
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number from v$asm_diskgroup a;



alter diskgroup DG1 mount;

---nomount状态下强制删除磁盘组
drop diskgroup oradg force including contents;  
alter diskgroup DG1 drop disk DG1_VOL5;   --删除磁盘组DG1中的磁盘VOL5



alter system set asm_diskstring='','ORCL:','/dev/raw/raw','/dev/oracleasm/disks/VOL';
alter system set asm_diskstring='/dev/asm-disk','/dev/raw/raw*';
alter diskgroup DATA add disk '/dev/raw/raw1';

-- 修改数值显示长度，不显示科学计数法
set numwidth 16

export GRID_HOME=$ORACLE_HOME

其它SQL

select * from V$ASM_ATTRIBUTE;
select * from v$asm_diskgroup; 
select * from v$asm_diskgroup_stat; 
select * from gv$asm_disk;
select * from v$asm_disk_stat;
select * from v$asm_template;
select * from v$asm_operation;
select * from v$asm_client;
select * from v$asm_file;
select * from v$asm_filesystem;  

set line 9999
set pagesize 9999
col path format a60
SELECT a.group_number, disk_number,mount_status, a.name, path FROM v$asm_disk a order by a.disk_number;
select instance_name,status from v$instance;

set line 999
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number, disk_number,mount_status, path from v$asm_diskgroup a;
select a.group_number,name,TYPE,state,TOTAL_MB,free_mb from v$asm_diskgroup a;
select name,state,free_mb,required_mirror_free_mb,usable_file_mb,a.group_number from v$asm_diskgroup a;


 SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                               V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
           1 ARCH                                   512       4096              1048576 MOUNTED     EXTERN   9999           8905           0         1094                       0           8905             0 11.2.0.0.0                                                   10.1.0.0.0                                           N
           2 DATA                                   512       4096              1048576 CONNECTED   EXTERN  19999          12965           0         7034                       0          12965             0 11.2.0.0.0                                                   10.1.0.0.0                                           N
           3 OVDISK                                 512       4096              1048576 MOUNTED     EXTERN   3099           2703           0          396                       0           2703             0 11.2.0.0.0                                                   10.1.0.0.0                                           N
           4 TEST                                   512       4096              1048576 MOUNTED     EXTERN   1024            929           0           95                       0            929             0 11.2.0.0.0                                                   10.1.0.0.0                                           N

SQL>



set line 9999
col HOST_NAME format a10　
select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
select INST_ID,name , open_mode, log_mode,force_logging from gv$database;


SQL> create diskgroup DG1 normal redundancy disk '/dev/raw/raw6','/dev/raw/raw7';  --创建磁盘组DG1


CREATE DISKGROUP ACFSDG external redundancy DISK '/dev/oracleasm/disks/VOL1' ATTRIBUTE 'compatible.asm' = '11.2','compatible.rdbms' = '11.2','compatible.advm'='11.2';



alter diskgroup DG1 mount;

---强制删除磁盘组
drop diskgroup oradg force including contents;  





 alter system set asm_diskstring='/dev/asm-disk*','/dev/raw/raw*';
alter diskgroup DATA add disk '/dev/raw/raw1';






----------------------- AIX下所有磁盘
if [ 1 = 1 ] ;then
  sum=0;asmnum=0
  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n"; printf "%-43s %-18s %-14s %-8s %-15s %-14s\n","|                 disk              ","|        PVID      ","|  no_reserve ","| size(G)","|    disktype     ","|  disk_storage  |"; printf "------------------------------------------------------------------------------------------------------------------------------\n";}'
  for diskname in `lspv | grep disk | awk '{print $1}'`;do
    mydiskname=`ls -l /dev/r$diskname |grep -w /dev/r$diskname| cut -c 1-12,17-38,59-76`
    mydiskpvid=`lquerypv -H /dev/$diskname | cut -c 1-16`
    if  [ "${mydiskpvid}" = "" ];then mydiskpvid="0000000000000000" ;  fi 2>/dev/null
    mydiskreserve=`lsattr -El $diskname | grep -i reserve_policy | cut -c 17-30`
    mydisksize=`bootinfo -s $diskname 2>/dev/null`  ;   let "mydisksize1=$mydisksize/1024" 2>/dev/null
    mydiskvg=`lspv | grep -w $diskname | awk '{print $3}'`
    mydiskasmgroup=`lquerypv -h /dev/r$diskname|head -n 7|tail -n 1|awk -F ' ' '{print $NF}'|sed -e 's/\.//g' -e 's/\|//g' | awk '{ if ($1 != "") printf "+"$1 ; else print "NULL"}'`
    mydiskflag=`lquerypv -h /dev/r$diskname 2>/dev/null|grep -i orcldisk|wc -l`
    if  [ ${mydisksize} -lt 1000  ];then mydisktype="HeadDisk" ; elif  [ ${mydisksize} -gt 1000 -a ${mydiskflag} -gt 0  ];then mydisktype="ASM:"$mydiskasmgroup; elif  [ ${mydisksize} -gt 1000 -a ${mydiskflag} -eq 0 -a $mydiskvg != "None" ];then mydisktype=$mydiskvg ; else  mydisktype="Not_Used"; fi 2>/dev/null
    mydiskpath=`lspath -l $diskname 2>/dev/null|head -1|awk '{print $NF}'|sed "s/.$//"`
    mydiskstring=`odmget -q attribute="unique_id" CuAt|egrep "name|value"|paste - -|tr  '\t' ' '|grep -w ${diskname}|sed 's/\"//g'`
    mydiskstorage=`echo ${mydiskstring} 2> /dev/null|awk '{ if($NF ~ /EMC/) {print "EMC"} else if ($NF ~ /NETAPP/) {print "NETAPP"} else if($NF ~ /HITACHI/) {print "HDS"}}'`
    mydiskdepth=`lsattr -El ${diskname}|grep queue_depth|awk '{print $2}'`
    mydiskstorage1=$mydiskstorage","$mydiskpath","$mydiskdepth
    [ $mydisksize1 -gt 1 -a ${mydiskflag} -gt 0 ] && { (( sum=sum+$mydisksize1 )) ; (( asmnum=$asmnum+1 )) ;} 
    echo  "$mydiskname"  "$mydiskpvid"  "$mydiskreserve"  "${mydisksize1%.*}"  "$mydisktype" "$mydiskstorage1" | awk '{printf "| %-10s %-6s %-8s %-14s | %-17s | %-12s | %-8s|  %-15s | %-14s |\n",$1,$2,$3,$4,$5,$6,$7,$8,$9}'
  done    
  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}'
  echo  "ASMDISK_TOTAL:$asmnum" "TOTAL_SIZE(GB):$sum" |awk '{printf "| %-20s %-101s |\n", $1,$2}'
  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}'
fi