CNDBA社区
1 故障现象
数据库实例宕机，查看日志信息如下：
Sun Aug 22 09:02:17 2021
SMCO started with pid=27, OS id=12247
Sun Aug 22 09:03:41 2021
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2459712562_0 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (5684) and older
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2459712562_0 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge - nothing to purge, pgsid=0.
Sun Aug 22 09:09:39 2021
Errors in file /u01/app/oracle/diag/rdbms/llt_pd/llt/trace/llt_ora_16131.trc:
Errors in file /u01/app/oracle/diag/rdbms/llt_pd/llt/trace/llt_ora_16131.trc:
Errors in file /u01/app/oracle/diag/rdbms/llt_pd/llt/trace/llt_ora_16131.trc:
因为SYSAUX 是辅助表空间，所以直接启动实例后查看表空间信息：
[dave@www.cndba.cn ~]# orz tsfree
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| TABLESPACE_NAME | TOTAL_SIZE(Mb) | FREE_SIZE(Mb) | USED_SIZE(Mb) | USED_RATE(%) |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| UNDOTBS1 | 287 | 284.56 | 2.44 | .85% |
| USERS | 500 | 499 | 1 | .2% |
| SYSTEM | 325 | 59.06 | 265.94 | 81.83% |
| JLB | 8192 | 8190 | 2 | .02% |
| TEMPTS1 | 20 | 14 | 6 | 30% |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
但是没有查看到SYSAUX 表空间。 查看等待事件：
[dave@www.cndba.cn ~]# orz event
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| EVENT | COUNT |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| resmgr:cpu quantum | 63 |
| read by other session | 56 |
| SQL*Net message from client | 18 |
| rdbms ipc message | 16 |
| direct path write temp | 10 |
| direct path read | 3 |
| Space Manager: slave idle wait | 3 |
| DIAG idle wait | 2 |
| Streams AQ: qmn slave idle wait | 1 |
| smon timer | 1 |
| SQL*Net message to client | 1 |
| VKTM Logical Idle Wait | 1 |
| pmon timer | 1 |
| LGWR real time apply sync | 1 |
| Streams AQ: waiting for time management or cleanup tasks | 1 |
| VKRM Idle | 1 |
| Streams AQ: qmn coordinator idle wait | 1 |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[dave@www.cndba.cn ~]#
排第一的是：resmgr:cpu quantum 这个在MOS中有说明，在11.2.0.3 之前有bug会导致这个问题，但我们这里是11.2.0.4。 所以不应该是bug。 但关联的对象是一致的，都和资源管理有关，问题的根源还是在SYSAUX 表空间满了有关。 但奇怪的是通过命令，查询不到SYSAUX 表空间。
因为这个实例部署在云主机上，是通过静默安装的，当时并没有配置SYSAUX 表空间的自动扩展。 先配置自动扩展：
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 22 09:51:19 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database datafile 2 autoextend on;
Database altered.
修改后还是无法查看到SYSAUX 表空间的使用率。 手工删除表空间中的一些对象。
truncate table WRH$_ACTIVE_SESSION_HISTORY;
truncate table WRH$_EVENT_HISTOGRAM;
truncate table WRH$_SQLSTAT;
truncate table WRH$_LATCH_MISSES_SUMMARY;
truncate table WRH$_LATCH;
truncate table WRH$_SYSSTAT;
truncate table WRH$_SEG_STAT;
truncate table WRH$_PARAMETER;
truncate table WRH$_SYSTEM_EVENT;
truncate table WRH$_SQL_PLAN;
truncate table WRH$_DLM_MISC;
truncate table WRH$_SERVICE_STAT;
truncate table WRH$_TABLESPACE_STAT;
truncate table WRH$_ROWCACHE_SUMMARY;
truncate table WRH$_MVPARAMETER;
SQL> truncate table SYS.WRH$_ACTIVE_SESSION_HISTORY;
Table truncated.
在SQL 命令中也可以查询到表空间的信息了：
[dave@www.cndba.cn ~]# orz tsfree
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| TABLESPACE_NAME | TOTAL_SIZE(Mb) | FREE_SIZE(Mb) | USED_SIZE(Mb) | USED_RATE(%) |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| SYSAUX | 325 | .88 | 324.13 | 99.73% |
| UNDOTBS1 | 287 | 284.56 | 2.44 | .85% |
| USERS | 500 | 499 | 1 | .2% |
| SYSTEM | 325 | 59.06 | 265.94 | 81.83% |
| JLB | 8192 | 8190 | 2 | .02% |
| TEMPTS1 | 20 | 14 | 6 | 30% |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[dave@www.cndba.cn ~]#
[dave@www.cndba.cn ~]# orz datafile sysaux
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| FILE# | NAME | SIZE(Mb) | AUTOEXTENSIBLE | MAXBYTES(Mb) | STATUS |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 2 | /u01/app/oracle/oradata/jlb/sysaux01.dbf | 325 | YES | 32767.98 | ONLINE |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[dave@www.cndba.cn ~]#
等待事件也恢复正常。
[dave@www.cndba.cn llt]# orz event
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| EVENT | COUNT |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| SQL*Net message from client | 34 |
| rdbms ipc message | 16 |
| direct path read | 6 |
| DIAG idle wait | 2 |
| Space Manager: slave idle wait | 2 |
| SQL*Net message to client | 1 |
| VKTM Logical Idle Wait | 1 |
| Streams AQ: qmn coordinator idle wait | 1 |
| Streams AQ: waiting for time management or cleanup tasks | 1 |
| Streams AQ: qmn slave idle wait | 1 |
| pmon timer | 1 |
| smon timer | 1 |
| LGWR real time apply sync | 1 |
| VKRM Idle | 1 |
| log file sync | 1 |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[dave@www.cndba.cn llt]#
其实问题的根源还是SYSAUX 表空间满了导致的后续问题。 小问题随笔记之。
版权声明：本文为博主原创文章，未经博主允许不得转载。
