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

简介: 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


目录
相关文章
|
5天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。
|
5天前
|
Oracle 关系型数据库
oracle asm 磁盘显示offline
oracle asm 磁盘显示offline
55 2
|
5天前
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
94 0
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
5天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句:数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句(LOOP、EXIT WHEN、FOR、WHILE)是处理数据的关键工具,用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环,可通过EXIT WHEN设定退出条件;FOR循环适用于固定次数迭代,WHILE循环基于条件判断执行。有效使用循环能提高效率,但需注意避免无限循环和优化大数据处理性能。掌握循环语句,将使数据处理更加高效和便捷。
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制:数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中,IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯,依据条件决定程序执行路径;ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路,按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺,通过巧妙运用能实现高效程序逻辑,保障数据正确流转,支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。
|
5天前
|
SQL Oracle 关系型数据库
Oracle系列十一:PL/SQL
Oracle系列十一:PL/SQL
|
5天前
|
SQL Oracle 关系型数据库
Oracle系列之八:SQL查询
Oracle系列之八:SQL查询
|
5天前
|
存储 Oracle 关系型数据库
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例
oracle数据库ASM磁盘组掉线,ASM实例不能挂载。数据库管理员尝试修复数据库,但是没有成功。
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例