手工生成快照又报错:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();sysaux快满了(一共3G),其中AWR的信息占了1.5G左右
alert 中:
Wed Sep 08 09:00:47 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (15357) and older
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (15358) and older
Wed Sep 08 09:01:05 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in tablespace SYSAUX
$
所以想删除一部快照
但奇怪的是
SQL> select count(*) from dba_hist_snapshot;
COUNT(*)
----------
0
BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-01688: unable to extend table ORA-01688: unable to extend table
SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096
in tablespace SYSAUX
. partition by in tablespace
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 14
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 37
ORA-06512: at line 1
我又想把保留时间减小点
SQL> select * from dba_hist_wr_control;
DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
1153364305
+00000 01:00:00.0
+00008 00:00:00.0
DEFAULT
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(259200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: at line 1
SQL>exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(259200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: at line 1
在网上查了查,有篇文章说是跟baseline设置有关,可我这边还是没有记录
SQL> select count(*) from dba_hist_baseline;
COUNT(*)
----------
0
SQL>
查这个V$SYSAUX_OCCUPANTS半天没有返回值,一会我查下被谁给阻塞了
之前用dba_segments查的
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
SYS WRH$_SYSMETRIC_HISTORY_INDEX index 172
SYS WRH$_SYSMETRIC_HISTORY TABLE 237
SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 1030
我现在是把AWR自动收集给停了,ALERT里面不报那个1688错误了
但我开始怀疑AWR的数据到底存放在哪里
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
0
SQL> select bytes/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
BYTES/1024/1024
---------------
1030
.125
SQL>
这难道不奇怪吗,还有v$sysaux_occupants这个视图一带上 SPACE_USAGE_KBYTES 列的SELECT就挂在那不动。
我在自己的本上建了一个新库,倒是可以查了,找到 SM/AWR 对应的大小,一开始是16M,我手工建快照,这个值慢慢增加到20M,这时已经建了37个snapshot
但这个时候查 wrh$_active_session_history,大小一直没变,是64K。我测试环境是 vm redhat 10.2.0.1, 真是弄不懂。
WRH$_ACTIVE_SESSION_HISTORY有个日期字段,根据retention看下是不是有超出retention范围的数据没有purge掉
要是没purge掉可能就是bug了,或者purge的时候没有成功,或者报错
空间满了看上去就会不正常
今天上午把一些WRH$的表truncate了(没有新加文件)
空间释放了,问题就基本解决了
可以生成新的快照了
我就手工生成了两个快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.wrh$_active_session_history;
COUNT(*)
----------
18504
SQL> select count(*) from dba_hist_snapshot;
COUNT(*)
----------
2
问了个朋友,他是经常清理WRH$这些表的,看来要保持SYSAUX一定的free空间。
现在还有个遗留问题,v$sysaux_occupants视图,一SELECT space那列就被挂在那,先放放了,还有别的报告要写...