一.环境介绍：操作系统 redhat6.6，数据库 11.2.0.4
二.问题描述：数据库后台报错 ORA-1688: unable to extend table CPUB.SXTC84100 partition P1 by 8192 in tablespace TBSSPACES
三.问题定位及处理
1.查看表空间大小,表空间剩余还有100多G
2.查看磁盘组大小，还余1.1TB
3.尝试扩表空间大小报错
ora-01119,ora-17502,ora-15041
4.查看v$asm_diskgroup,发现error_code 是ora-15041
5.查看磁盘组的free_mb，可以看出，disk的free_mb大小不一,手工重新 rebalance，均衡之后还是报错 ora-15041
SQL> select b.name,b.type,t.disk_number,t.total_mb/1024,t.free_mb/1024,b.usable_file_mb/1024 from v$asm_disk t,v$asm_diskgroup b
where t.group_number=b.group_number order by t.disk_number; 2
NAME TYPE DISK_NUMBER T.TOTAL_MB/1024 T.FREE_MB/1024 B.USABLE_FILE_MB/1024
------------------------------------- ------------ ----------- --------------- -------------
OCR NORMAL 0 2.9296875 2.89648438 1.3046875
DATA NORMAL 0 900 25.4296875 122.724609
ARCH NORMAL 0 900 806.773438 403.386719
ARCH NORMAL 1 900 806.773438 403.386719
DATA NORMAL 1 900 50.6523438 122.724609
OCR NORMAL 2 3 2.61035156 1.3046875
DATA NORMAL 2 900 50.6953125 122.724609
DATA NORMAL 3 900 50.6796875 122.724609
OCR NORMAL 3 3 2.61035156 1.3046875
DATA NORMAL 4 900 50.6132813 122.724609
DATA NORMAL 5 900 430.816406 122.724609
DATA NORMAL 6 900 14.4023438 122.724609
DATA NORMAL 7 900 0 122.724609
DATA NORMAL 8 900 50.5703125 122.724609
DATA NORMAL 9 900 25.3007813 122.724609
DATA NORMAL 10 900 25.28125 122.724609
DATA NORMAL 11 900 25.2578125 122.724609
DATA NORMAL 12 900 10.7773438 122.724609
DATA NORMAL 13 900 14.4179688 122.724609
DATA NORMAL 14 900 10.8085938 122.724609
DATA NORMAL 15 900 10.8164063 122.724609
DATA NORMAL 16 900 25.3007813 122.724609
DATA NORMAL 17 900 25.296875 122.724609
DATA NORMAL 18 900 25.265625 122.724609
DATA NORMAL 19 900 21.9023438 122.724609
DATA NORMAL 20 900 14.4570313 122.724609
DATA NORMAL 21 900 18.296875 122.724609
DATA NORMAL 22 900 25.359375 122.724609
DATA NORMAL 23 900 50.6367188 122.724609
DATA NORMAL 24 900 14.6953125 122.724609
DATA NORMAL 25 900 21.953125 122.724609
DATA NORMAL 26 900 14.3710938 122.724609
DATA NORMAL 27 900 25.4921875 122.724609
DATA NORMAL 28 900 18.1132813 122.724609
DATA NORMAL 29 900 25.4648438 122.724609
DATA NORMAL 30 900 18.328125 122.724609
DATA NORMAL 31 900 25.5742188 122.724609
DATA NORMAL 32 900 21.8710938 122.724609
DATA NORMAL 33 900 25.546875 122.724609
DATA NORMAL 34 900 21.8945313 122.724609
DATA NORMAL 35 900 17.9960938 122.724609
DATA NORMAL 36 900 50.828125 122.724609
DATA NORMAL 37 900 18.0078125 122.724609
DATA NORMAL 38 900 50.8398438 122.724609
DATA NORMAL 39 900 18.0507813 122.724609
DATA NORMAL 40 900 25.53125 122.724609
DATA NORMAL 41 900 21.625 122.724609
DATA NORMAL 42 900 25 122.724609
DATA NORMAL 43 900 14.21875 122.724609
DATA NORMAL 44 900 27.515625 122.724609
DATA NORMAL 45 900 20.3125 122.724609
手工重新 rebalance data 磁盘组
SQL> alter diskgroup data rebalance power 11;
Diskgroup altered.
SQL> select * from v$asm_operation;
GROUP_NUMBER OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ---------- -------- ---------- ---------- ---------- ---------- ---------- ----------- -----------
2 REBAL RUN 11 11 745 2308467 9727 237
6.在asmcmd下lsdg 发现每个磁盘组有一块offline_disks
[grid@dbn01 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 4194304 921600 824836 0 412418 1 N ARCH/
MOUNTED NORMAL Y 512 4096 4194304 41472000 1166476 921600 122438 1 N DATA/
MOUNTED NORMAL N 512 4096 1048576 6072 5639 0 1336 1 Y OCR/
ASMCMD>
7.找到被踢出去的失败盘，手工加到磁盘组中
SQL> alter diskgroup DATA add failgroup FAL2 disk '/dev/asmdisk/sdj' force rebalance power 11;
Diskgroup altered.
SQL> alter diskgroup ARCH add failgroup FAL4 disk '/dev/asmdisk/sdq' force rebalance power 11;
Diskgroup altered.
SQL> alter diskgroup OCR add failgroup OCR_0002 disk '/dev/asmdisk/sdn' rebalance power 11;
Diskgroup altered.
恢复正常，可以正常扩表空间大小，后台也不再产生ORA的错误。