一、问题现象:报表数据没有产生,查看oracle小时表发现时间点停止在前一天的22:55分。
二、处理步骤:
1、查看syslog日志,22:55分前后log日志产生正常;
2、查看logparser日志,提示入库失败(log文件产生wrong错误日志)。
日志如下:
653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:8728)
at cn.com.asb.stat.logparser.DbHandler.commit(DbHandler.java:130)
at cn.com.asb.stat.logparser.Worker.commitLines(Worker.java:418)
at cn.com.asb.stat.logparser.Worker.processFile(Worker.java:333)
at cn.com.asb.stat.logparser.Worker.run(Worker.java:508)
at cn.com.asb.stat.logparser.LogParser.run(LogParser.java:36)
at cn.com.asb.stat.logparser.Main.run(Main.java:132)
at com.nextenso.mgmt.reporter.ReportingHandler.start(ReportingHandler.java:124)
at cn.com.asb.stat.logparser.Main.main(Main.java:154)
3、查看oracle表空间使用率(表空间已设置为自动扩展,所以不是该问题),
序号
4、查看当时oracle告警日志(文件目录为/bases/oracle/admin/CDMAWAP/bdump/alert_CDMAWAP.log),发现单个Datafile不足,大小超过3G,导致入库失败:
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
create tablespace ts20110513 DATAFILE '/bases/oradata/CDMAWAP/20110513.dbf' SIZE 3000M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1M
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
ORA-1653: unable to extend table WAP_USER.TAB20110512_23 by 128 in tablespace TS20110512
三、解决方案:
(1)通过在表空间增加一个datafile,临时性解决该问题:
ALTER tablespace TS20110411 ADD DATAFILE /StatOracle/CDMAWAP/CTWAP/20110411_1.dbf' SIZE 1024M autoextend on;
(2)修改创建表空间和表的procedure,确保以后的表空间建立之初就是2个datafile。