合 Oracle ASM磁盘和磁盘组的常用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

 

 

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

  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}'

  echo  "ASMDISK_TOTAL:$asmnum" "TOTAL_SIZE(GB):$sum" |awk '{printf "| %-20s %-101s |\n", $1,$2}'