登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›故障处理：Oracle表空间异常增长后又恢复正常的故障模拟 ...

故障处理：Oracle表空间异常增长后又恢复正常的故障模拟与分析

心麾浪 3 天前
我们的文章会在微信公众号IT民工的龙马人生和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
今天谈谈很早之前朋友问到的一个案例，表空间使用率异常增加，新增对象都是临时字段，并且段名是很奇怪，由数字和.构成，如（11.123）这种方式。这个案例相对来说比较极端，客户采用的文件系统，并且数据文件为自动扩张，最后出现空间耗尽，数据文件无法收缩的现象。今天就好大家一起来模拟一下这种现象：
环境准备

由于是自己的环境，也为了保证后续的操作不报错，所以这里看起了归档日志强制删除的脚本。
  1. [oracle@oracleadg sql]$ sh ./arch_delete_force_all.sh
  2. 2025-08-23 21:46:09: Using ORACLE_SID=htz191, interval=10 seconds.
  3. 2025-08-23 21:46:09: Press Ctrl+C to stop the script.
  4. 2025-08-23 21:46:09: Starting RMAN archived log cleanup.
  5. 2025-08-23 21:46:14: Cleanup finished. Sleeping for 10 seconds.
  6. 2025-08-23 21:46:24: Starting RMAN archived log cleanup.
  7. 2025-08-23 21:46:28: Cleanup finished. Sleeping for 10 seconds.
  8. 2025-08-23 21:46:38: Starting RMAN archived log cleanup.
复制代码
创建测试表
  1. create table htz as select * from dba_objects;
  2. 多次执行下面语句
  3. insert into htz as select * from htz;
  4. commit;
复制代码
确保测试表足够的大，下面是我环境中的容量大小。
  1. SQL> @segment_size.sql
  2. Enter Search owner Name (i.e. SCOTT|ALL) :
  3. Enter Search Segment Name (i.e. DEPT|ALL) :
  4. Enter Search Tablespace Name (i.e. DEPT|ALL) : system
  5. Enter Display rows Name (i.e. 20) :
  7.                                                                          Name                                    Total
  8. OWNER                SEGMENT_NAME                        SEGMENT_TYPE    Partition                             size(M)
  9. -------------------- ----------------------------------- --------------- ----------------------------------- ---------
  10. SYS                  HTZ                                 TABLE                                                   11518
  11. SYS                  IDL_UB1$                            TABLE                                                     398
  12. SYS                  SYS_LOB0000022516C00008$$           LOBSEGMENT                                                192
  13. SYS                  C_TOID_VERSION#                     CLUSTER                                                    49
  14. SYS                  SOURCE$                             TABLE                                                      40
复制代码
我这里是11G左右的大小。
确认数据库状态
  1. SQL> @db_status.sql
  3.                                               flashback  Switchover           Database                                  Force
  4. OPEN_MODE            LOG_MODE                 On         Status               Role                 PROTECTION_MODE      Loggin
  5. -------------------- ------------------------ ---------- -------------------- -------------------- -------------------- ------
  6. READ WRITE           ARCHIVELOG               YES        NOT ALLOWED          LOGICAL STANDBY      MAXIMUM PERFORMANCE  YES
复制代码
确认表空间的容量
  1. SQL> @db_size.sql
  2. *******************tablespace size**************************
  4. Tablespace                                                    Size (MB)  Free (MB)     % Free     % Used
  5. ------------------------------------------------------------ ---------- ---------- ---------- ----------
  6. TEST                                                               1024       1023        100         .1
  7. TEMP                                                                224        190         85      15.18
  8. UNDOTBS1                                                            995     542.75         55      45.45
  9. UNDOTBS2                                                             25    13.1875         53      47.25
  10. USERS                                                             18.75     2.0625         11         89
  11. SYSAUX                                                             1140     59.625          5      94.77
  12. SYSTEM                                                            12688    15.1875          0      99.88
复制代码
取消归档日志自动删除

将前面的归档日志自动删除脚本取消，因为后面我们要用到归档日志文件去解析。
开启表空间自动扩容脚本

因为后续的操作要生成11G的表，所以开启表空间自动扩容脚本。
  1. [oracle@oracleadg sql]$ watch -n 2 ./tbscale -l TEST:50
  4. Every 2.0s: ./tbscale -l TEST:50                                                                                                                                                                                                        Sat Aug 23 22:13:44 2025
  6. [INFO] 2025/08/23 22:13:44 This script will connect to the database using 'sqlplus / as sysdba'. Please ensure OS authentication is configured correctly.
  7. [INFO] 2025/08/23 22:13:44 Database status: OPEN
  8. [INFO] 2025/08/23 22:13:44 Threshold mode: Only checking tablespaces and thresholds specified by -l
  9. [INFO] 2025/08/23 22:13:44 ========== Processing tablespace: TEST (threshold 50.00%) ==========
  10. [INFO] 2025/08/23 22:13:44 Tablespace [TEST] free space 48.34% < threshold 50.00%, expansion required.
  11. [INFO] 2025/08/23 22:13:44 Underlying storage has sufficient space for tablespace [TEST] (Available 99065.52 MB >= Needed 1024 MB)
  12. [INFO] 2025/08/23 22:13:44 Expansion SQL: ALTER TABLESPACE TEST ADD DATAFILE '/oracle/app/oracle/oradata/HTZ19TWO/datafile/TEST_14.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
  13. [INFO] 2025/08/23 22:14:02 Successfully added a new datafile to tablespace [TEST].
  14. [INFO] 2025/08/23 22:14:02 Script execution finished.
复制代码
创建新表

这里要保证htz这张表足够的大，这样才方便后续的实验和观察。
  1. SQL> create table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a;
复制代码
确认对象大小
  1. SQL> @segment_size.sql
  2. Enter Search owner Name (i.e. SCOTT|ALL) :
  3. Enter Search Segment Name (i.e. DEPT|ALL) :
  4. Enter Search Tablespace Name (i.e. DEPT|ALL) : TEST
  5. Enter Display rows Name (i.e. 20) :
  7.                                                                          Name                                    Total
  8. OWNER                SEGMENT_NAME                        SEGMENT_TYPE    Partition                             size(M)
  9. -------------------- ----------------------------------- --------------- ----------------------------------- ---------
  10. SYS                  11.130                              TEMPORARY                                                5760
复制代码
这里看到生成一个临时段，对象表位11.130，现象跟之前朋友说的案例一模一样。
取消创建表语句
  1. SQL> create table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a;
  3. ^Ccreate table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a
  4.                                                                         *
  5. ERROR at line 1:
  6. ORA-01013: user requested cancel of current operation
复制代码
这里直接终止命令的运行。
查看表空间对象

已经没有任何对象了
  1. SQL> @segment_size.sql
  2. Enter Search owner Name (i.e. SCOTT|ALL) :
  3. Enter Search Segment Name (i.e. DEPT|ALL) :
  4. Enter Search Tablespace Name (i.e. DEPT|ALL) : TEST
  5. Enter Display rows Name (i.e. 20) :
  7. no rows selected
复制代码
表空间的使用率也将下来了。
  1. SQL> @db_size.sql
  2. *******************tablespace size**************************
  4. Tablespace                                                    Size (MB)  Free (MB)     % Free     % Used
  5. ------------------------------------------------------------ ---------- ---------- ---------- ----------
  6. TEST                                                              11264      11253        100         .1
  7. TEMP                                                                224        190         85      15.18
  8. UNDOTBS1                                                            995     542.75         55      45.45
  9. UNDOTBS2                                                             25    13.1875         53      47.25
  10. USERS                                                             18.75     2.0625         11         89
  11. SYSTEM                                                            13618   945.1875          7      93.06
  12. SYSAUX                                                             1140    51.5625          5      95.48
复制代码
上面的现象跟朋友说的一模一样了，临时段，表空间使用率增加后又恢复正常。
解析归档日志
  1. SQL> @logmnr_arch.sql '2025-08-23 23:03:57' '2025-08-23 23:04:53' 'Y'
  2. Starting LogMiner...
  3. LogMiner started successfully.
复制代码
这里通过脚本来解析归档日志，查询解析记录，可以得到下面这条唯一的SQL语句。
  1. SQL_REDO
  2. --------------------------------------------------------------------------------
  3. update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS
  4. " = '8', "MINEXTS" = '1', "MAXEXTS" = '2147483645', "EXTSIZE" = '128', "EXTPCT"
  5. = '0', "USER#" = '0', "LISTS" = '0', "GROUPS" = '0', "BITMAPRANGES" = '214748364
  6. 5', "CACHEHINT" = '0', "SCANHINT" = '0', "HWMINCR" = '77446', "SPARE1" = '419456
  7. 1' where "FILE#" = '13' and "BLOCK#" = '130' and "TYPE#" = '3' and "TS#" = '6' a
  8. nd "BLOCKS" = '8' and "EXTENTS" = '1' and "INIEXTS" = '8' and "MINEXTS" = '1' an
  9. d "MAXEXTS" = '2147483645' and "EXTSIZE" = '128' and "EXTPCT" = '0' and "USER#"
  10. = '0' and "LISTS" = '0' and "GROUPS" = '0' and "BITMAPRANGES" = '2147483645' and
  11. "CACHEHINT" = '0' and "SCANHINT" = '0' and "HWMINCR" = '77446' and "SPARE1" = '
  12. 4325633' and "SPARE2" IS NULL and ROWID = 'AAAAAIAABAAAMgSAAj';
复制代码
前面的13.130这个名字就是由FILE#.BLOCK#构成的。
结束

到这里面我们基本演示朋友说到这个案例的现象，在这个现象背后其实有一个Oracle的基础知识就是create table  as select 这种方式创建表时，Oracle是先创建临时段，临时段创建成功后，Oracle才会去做rename的操作和表定义的信息，这里可以通过去解析归档日志可以获得更多详细的信息。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
1.png


来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
心麾浪
3 天前

0

粉丝关注

13

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫