今天检查经营分析数据库alert日志,发现报有如下错误:
Sat Feb 11 06:00:22 2012
GATHER_STATS_JOB encountered errors. Check the trace file.
Sat Feb 11 06:00:22 2012
Errors in file /home/oracle/admin/tjdb/bdump/tjdb1_j000_23954.trc:
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
再查看/home/oracle/admin/tjdb/bdump/tjdb1_j000_23954.trc:
Unix process pid: 23954, image: oracle@tjdb01 (J000)
*** 2012-01-16 22:00:46.607
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-16 22:00:46.606
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-16 22:00:46.606
*** SERVICE NAME:(SYS$USERS) 2012-01-16 22:00:46.606
*** CLIENT ID:() 2012-01-16 22:00:46.606
*** SESSION ID:(376.6707) 2012-01-16 22:00:46.606
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-16 22:00:46.607
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_OPTSTAT_HISTHEAD_HISTORY"','""', ...)
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-17 22:01:06.841
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-17 22:01:06.841
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-17 22:01:06.841
*** SERVICE NAME:(SYS$USERS) 2012-01-17 22:01:06.841
*** CLIENT ID:() 2012-01-17 22:01:06.841
*** SESSION ID:(376.7745) 2012-01-17 22:01:06.841
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-17 22:01:06.851
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_OPTSTAT_HISTHEAD_HISTORY"','""', ...)
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
这是oracle每天晚上自动收集统计信息报出来的,查看数据文件编号36的文件,发现这个文件是去年当初sysaux表空间不够,为其添加的数据文件,文件编号正是36,一段时间后将sysaux表空间下的一些数据清掉后,将这个数据文件offline了,查看:
sql>SELECT t.owner,t.segment_name,t.tablespace_name,t.relative_fno
FROM dba_segments t
WHERE t.segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
owner segment_name tablespace_name relative_fno
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY SYSAUX 3
WRI$_OPTSTAT_HISTHEAD_HISTORY表根本不在第36号文件里面的啊,
sql> select * from WRI$_OPTSTAT_HISTHEAD_HISTORY ;
1 177551 16 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 41 21 0.0476190476190476 80 C12A 3 21 11783 2011-7-2 6:00:39
2 177551 17 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 1764 81 0.0123456790123457 80 C21241 3 81 11783 2011-7-2 6:00:39
3 177551 18 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 13 10 0.1 80 C10E 3 10 11783 2011-7-2 6:00:39
能查出数据来,但是通过select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY ;
却也是报ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
说明表WRI$_OPTSTAT_HISTHEAD_HISTORY ;有一部分数据是存储在36号数据文件里面了,而收集统计信息时是对全表做的,因此当读到36号数据文件时,就报这个错误了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12129601/viewspace-716156/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12129601/viewspace-716156/