向已有磁盘组添加asm磁盘 创建磁盘组 drop一块磁盘

--监控ASM使用率

SELECT

    name group_name

  , sector_size sector_size

  , block_size block_size

  , allocation_unit_size allocation_unit_size

  , state state

  , type type

  , total_mb total_mb

  , (total_mb - free_mb) used_mb

  , ROUND((1- (free_mb / total_mb))*100, 2) pct_used

FROM

    v$asm_diskgroup

ORDER BY name

--查看ASM硬盘信息

select group_number, disk_number, name, mount_status, header_status,

mode_status, state, path, failgroup, mount_date, total_mb, free_mb

from v$asm_disk order by group_number, disk_number;

--查看diskgroup空间大小，以及ASM状态

select name, total_mb, free_mb, usable_file_mb, state from v$asm_diskgroup

--查看现行的操作

select * from v$asm_operation;

--查看DISKGROUP的兼容性

SELECT name AS diskgroup, compatibility AS asm_compat,database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

--查看DISKGROUP属性：

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name,SUBSTR(a.value,1,24) AS value, read_only

FROM V$ASM_DISKGROUP dg,V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA' AND dg.group_number = a.group_number;

 select group_number,name,BLOCK_SIZE,ALLOCATION_UNIT_SIZE,STATE,type from v$asm_diskgroup;

 

 --查看当前可用asm磁盘

 select name,header_status,mount_status,total_mb,failgroup,path from v$asm_disk;

 select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;

 

 

 -- asm 用户直接把可用磁盘添加到可用的磁盘组

 grid user : alter diskgroup data add disk '/dev/asm-diskc','/dev/asm-diskf' rebalance power 5;

--监控ASM使用率

SELECT

    name group_name

  , sector_size sector_size

  , block_size block_size

  , allocation_unit_size allocation_unit_size

  , state state

  , type type

  , total_mb total_mb

  , (total_mb - free_mb) used_mb

  , ROUND((1- (free_mb / total_mb))*100, 2) pct_used

FROM

    v$asm_diskgroup

ORDER BY name

--查看ASM硬盘信息

select group_number, disk_number, name, mount_status, header_status,

mode_status, state, path, failgroup, mount_date, total_mb, free_mb

from v$asm_disk order by group_number, disk_number;

--查看diskgroup空间大小，以及ASM状态

select name, total_mb, free_mb, usable_file_mb, state from v$asm_diskgroup

--查看现行的操作

select * from v$asm_operation;

--查看DISKGROUP的兼容性

SELECT name AS diskgroup, compatibility AS asm_compat,database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

--查看DISKGROUP属性：

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name,SUBSTR(a.value,1,24) AS value, read_only

FROM V$ASM_DISKGROUP dg,V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA' AND dg.group_number = a.group_number;

 select group_number,name,BLOCK_SIZE,ALLOCATION_UNIT_SIZE,STATE,type from v$asm_diskgroup;

 

 --查看当前可用asm磁盘

 select name,header_status,mount_status,total_mb,failgroup,path from v$asm_disk;

 select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;

 

 

 -- asm 用户直接把可用磁盘添加到可用的磁盘组

 grid user : alter diskgroup data add disk '/dev/asm-diskc','/dev/asm-diskf' rebalance power 5;

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

###################################### 从磁盘组drop一块磁盘

-- 发现磁盘组是normal 类型 ，为了节省空间删掉一些磁盘创建 external的磁盘组

select group_number, name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;

select disk_number, path, name, state,total_mb, free_mb from v$asm_disk where group_number = 1;

drop table TEST_PART1 purge;

alter diskgroup data drop disk '/dev/asm-diskc' ;

SQL> alter diskgroup data drop disk '/dev/asm-diskc' ; -- 应该根据name来drop

alter diskgroup data drop disk '/dev/asm-diskc'

*

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15054: disk "/DEV/ASM-DISKC" does not exist in diskgroup "DATA"

SQL> alter diskgroup data drop disk DATA_0003 ; --成功 ,这时磁盘的状态为DROPPING

Diskgroup altered.

SQL>

SQL> alter diskgroup data drop disk DATA_0004; --失败 ，原因是rebalance的空间都不够

alter diskgroup data drop disk DATA_0004

*

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15250: insufficient diskgroup space for rebalance completion

alter diskgroup data drop disk DATA_0000 ;

-- state 是 DROPPING DATA_0003 (/dev/asm-diskc) ,等到数据都已经rebalance 后，该磁盘会退出该磁盘组

select group_number, disk_number, state, name, path,total_mb, free_mb from v$asm_disk where group_number = 1;

alter diskgroup data rebalance power 5;

####################################### asm 新建一个磁盘组

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

SQL> create diskgroup data01 external redundancy disk '/dev/asm-diskg','/dev/asm-diskh' attribute 'au_size'='4M'; --但是磁盘组是 DISMOUNTED

Diskgroup created.

SQL>

--确保 diskgroup -g data01 服务正常启动，然后在两个节点确保磁盘组都已经mount

srvctl enable diskgroup -g data01

alter diskgroup data01 dismount;

alter diskgroup data01 mount;

select * from v$asm_diskgroup;

--对data01磁盘组进行属性修改

ALTER DISKGROUP data01 SET ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';

ALTER DISKGROUP data01 SET ATTRIBUTE 'compatible.rdbms' = '10.1.0.0.0';

### 注意这里新建的磁盘组 居然开始不会自动mount

[grid@rac201 ~]$ crsctl start resource ora.DATA01.dg       --或者grid 用户asm实例下重新挂在
CRS-2672: Attempting to start 'ora.DATA01.dg' on 'rac201' 
CRS-2672: Attempting to start 'ora.DATA01.dg' on 'rac202' 
CRS-2676: Start of 'ora.DATA01.dg' on 'rac202' succeeded 
CRS-2676: Start of 'ora.DATA01.dg' on 'rac201' succeeded 
[grid@rac201 ~]$ c

 

解决方法： crsctl 把添加的磁盘组属性设置自动启动 （我这里的环境比较特殊,asm实例的spfile在asm磁盘组上，直接通过
修改asm实例参数的方式并不合适）

[grid@rac201 ~]$ crsctl modify resource ora.DATA01.dg -attr "AUTO_START=always" 
[grid@rac201 ~]$ crsctl modify resource ora.DATA.dg -attr "AUTO_START=always"