Oracle报错ORA-1653问题分析

IT那活儿 2025-01-20

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

一

故障现象

一套Oracle数据库出现大量ORA-1653 报错，增加datafile后，报错消失，数据库恢复正常。

二

分析总结

巡检发现大量ORA-1653 报错，增加datafile后，报错消失，数据库恢复正常。

数据库的SMON进程的一个作用是回收合并空闲空间(整理区碎片)。SMON负责把那些在表空间中空闲的并且互相是邻近的Extent接合成一个较大的空闲扩展区，并回收空闲空间，以减少数据库的碎片化，每5分钟检查执行一次。

当有大量insert数据的对象在碎片较多，free space 较少的表空间时，扩展空间申请的量很大，并发很高，会出现ORA-1653,申请空间失败，导致session hang。

表空间TS_HIS 有16168GB，free space 437GB，空闲率只有2.7%，碎片数量高达290436，大量碎片<1MB,实际可用空间只有360GB左右，应用运行一个复杂的，大批量insert数据的 sql，根据报错信息，用户1.SHENJI_TMP 类对象需要申请64MB的连续空间扩展，其它生产对象也基本申请64MB的连续空间，出现报错时，TS_HIS表可用free space 只有200GB只有，碎片有29万多，没有大于64MB的连续空间可用，出现ORA-1653报错，session hang。

整改建议：

1）生产业务高峰期，避免大批量的DML操作。尤其是占用大量空间的sql，操作前需提前检查空间使用情况。
2）生产业务表空间，free space 建议保持在15%以上。
3）改进对碎片化的监控。

三

详细问题分析

数据库alert日志：

13：39：12 用户1. SHENJI_TMP_JIAOFEI_TMP_42_43_2024 申请空间失败，TS_HIS 表空间已满。

根据报错信息，13：39：12后：

2024-05-06T13:39:12.295789+08:00
PDB(7):ORA-1653: unable to extend table 用户1.SHENJI_TMP_JIAOFEI_TMP_42_43_2024 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:16.091717+08:00
PDB(7):ORA-1653: unable to extend table SO1.ORD_DTL_INFO_F_857_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:19.815584+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:25.693664+08:00
PDB(7):ORA-1653: unable to extend table SO1.ORD_DTL_INFO_F_857_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:27.184040+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:27.635436+08:00
PDB(7):ORA-1653: unable to extend table SO1.ORD_DTL_INFO_F_857_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:28.400713+08:00
ARC1 (PID:106468): Archived Log entry 1718076 added for T-4.S-110212 ID 0x76cf3e74LAD:1

2024-05-06T13:39:12.329852+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:12.943266+08:00
PDB(7):ORA-1653: unable to extend table 用户1.SHENJI_TMP_ZHANGDAN_TMP_41_2024 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:13.767757+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:13.984101+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_857_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:15.657226+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:15.986671+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:17.652073+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:17.973579+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:20.127737+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_853_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T13:39:20.553303+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_858_202405 by 8192in tablespace TS_HIS [PDB]

PDB(7):ORA-1653: unable to extend table SO1.SMS_FAST_PENDING_H_859_202405 by 1024in tablespace TS_HIS [PDB] 
2024-05-06T14:12:05.793902+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_859_202405 by 8192in tablespace TS_HIS [PDB] 


?ORA-1653
2024-05-06T14:12:05.248952+08:00
PDB(7):ORA-1654: unable to extend index SO1.PK_ORD_CUST_F_854_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:05.437470+08:00
PDB(7):ORA-1654: unable to extend index SO1.IDX_CRM2SFC_INFO2_851_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:05.481344+08:00
PDB(7):ORA-1653: unable to extend table SO1.ORD_USER_F_858_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:05.515585+08:00
PDB(7):ORA-1653: unable to extend table SO1.SMS_FAST_PENDING_H_859_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:05.793902+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_859_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T14:12:05.851373+08:00
PDB(7):ORA-1653: unable to extend table SO1.ORD_USER_F_858_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:06.290733+08:00
PDB(7):ORA-1653: unable to extend table SO1.I_OUTDATA_NEW_LOG_859_202405 by 8192in tablespace TS_HIS [PDB]
2024-05-06T14:12:06.406731+08:00
PDB(7):ORA-1653: unable to extend table SO1.SMS_REAL_PENDING_H_859_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:06.771361+08:00
PDB(7):ORA-1653: unable to extend table SO1.SMS_REAL_PENDING_H_859_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:06.867751+08:00
PDB(7):ORA-1654: unable to extend index SO1.PK_ORD_CUST_F_854_202405 by 1024in tablespace TS_HIS [PDB]
2024-05-06T14:12:06.919765+08:00
PDB(7):Completed: ALTER TABLESPACE TS_HIS ADD DATAFILE '+DATAC1' SIZE 30G AUTOEXTEND OFF
2024-05-06T14:12:07.802832+08:00
Network Resource Management enabled for Process LG01 (pid 236824) for Exadata I/O
2024-05-06T14:12:22.174450+08:00
Process termination requested for pid 3416 [source = rdbms], [info = 2] [request issued by pid:236785, uid:1001]
2024-05-06T14:12:24.801805+08:00
PDB(7):ALTER TABLESPACE TS_HIS ADD DATAFILE '+DATAC1' SIZE 30G AUTOEXTEND OFF
复制

检查free space：

已经增加了两个datafile 之后的：

SQL> select tablespace_name,sum(bytes)/1024/1024,count(*) from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024 COUNT(*)
------------------------------ -------------------- ----------
SYSTEM 26922.0625 15
QUEST_SM 8185.875 3
SYSAUX 25282.4375 300
UNDOTBS1 153667.188 1196
UNDO_8 113737.75 1273
OTESTDATA 7917.6875 4
TS_INX 2099316.38 186483
UNDO_10 120677 32
USERS 120439.438 1719
TS_DATA 365072.25 121392
TS_HIS 334967.938 290436

TABLESPACE_NAME SUM(BYTES)/1024/1024 COUNT(*)
------------------------------ -------------------- ----------
UNDO_9 53246.1875 20
SQL> selectcount(*) from dba_free_space where (blocks - ((floor((block_id + 127)  128) * 128) - block_id) ) >= 8192 and tablespace_name='TS_HIS';
   
  COUNT(*)
----------
        15
复制

四

总结

生产业务高峰期，避免大批量的DML操作。尤其是占用大量空间的sql，操作前需提前检查空间使用情况。

生产业务表空间，free space 建议保持在15%以上。

基于表空间分组，小于64m的所有连续区加起来的总空间除以该表空间所有连续区的总空间，超过85%需特别关注。

监控脚本参考如下：

select TOT_FREE_MB.tablespace_name,to_char(TOT_FREE_MB.MB,'999,999,999.99') T_FREE_MB,
to_char(TOTAL_AVAL_MB.MB,'999,999,999.99') T_AVA_MB,
to_char((1-TOTAL_AVAL_MB.mb/TOT_FREE_MB.mb)*100,'99.99')||'%'  USED_PERCENT 
from
(select tablespace_name,sum(bytes)/1024/1024  MB from dba_free_space 
where tablespace_name in
(select TABLESPACE_NAME from dba_tablespaces 
where ALLOCATION_TYPE='SYSTEM'and tablespace_name notlike'UNDOTBS%') 
groupby tablespace_name) TOT_FREE_MB,
(select tablespace_name,sum(bytes)/1024/1024  MB from dba_free_space 
wherebytes/1024/1024 > 64groupby tablespace_name) TOTAL_AVAL_MB
where TOT_FREE_MB.tablespace_name=TOTAL_AVAL_MB.tablespace_name orderby4;
复制

注：由于碎片太多，该脚本运行很慢。

调整原监控脚本：

select count(*),sum(bytes)/1024/1024/1024 GB from 
dba_free_space a where tablespace_name='TS_HIS' and bytes/1024/1024 < 64;
复制

清理目前表空间碎片的建议：

1）新建uniform的表空间；
2）把现有的表和索引move或者重建到新的表空间。

参考文档:

ORA-1654, ORA-1653, ORA-1658, ORA-1683 When Tablespace has plenty of free space (Doc ID 1539699.1)

现有碎片：

SQL> select count(*),sum(bytes)/1024/1024/1024 GB from dba_free_space a where tablespace_name='TS_HIS' and bytes/1024/1024 < 64;


  COUNT(*) GB
---------- ----------
    336978 944.643311

select count(*),sum(bytes)/1024/1024/1024 GB from dba_free_space a where tablespace_name='TS_HIS' and bytes/1024/1024 >= 64;
  COUNT(*) GB
---------- ----------
     14479 1186.26807
复制