楼上这位兄弟好!以下是alert_sid.log日志 Wed Jul 4 21:29:50 2012 delete from "NGVES3"."OPERATION_ALLEGE_NUM_MV" Wed Jul 4 21:29:50 2012 Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_j003_4517.trc: ORA-12012: error on auto execute of job 444 ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461 ORA-06512: at "SYS.DBMS_IREFRESH", line 683 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 Wed Jul 4 21:30:03 2012
上面的错误,是undo表空间设置太小,或者undo保留时间太长
ORA-12012: error on auto execute of job 4441、查看 job号为444所对应的过程。 2、到v$session_wait 表里看看系统等待信息 3、把等待对应的session kill掉。看结果
谢谢楼上的兄弟,目前原因已找到 1、因为创建了物化视图的缘故。 2、其中有定时每1分钟执行一次,将查询数据插入到另一个表中。 3、在昨天中午突然断电,造成oracle数据库服务器直接宕掉。 4、重启之后undo tablspace空间太小,cpu一直100% 删除该物化视图后恢复cpu正常值请各位有相关经验的或相同经历的DBA们指出专业的原因分析!----------------------------- Wed Jul 4 21:29:50 2012 delete from "NGVES3"."OPERATION_ALLEGE_NUM_MV" Wed Jul 4 21:29:50 2012 Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_j003_4517.trc: ORA-12012: error on auto execute of job 444 ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461 ORA-06512: at "SYS.DBMS_IREFRESH", line 683 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 Wed Jul 4 21:30:03 2012
4、重启之后undo tablspace空间太小,cpu一直100% 重启后undo表空间原则上不会自动变小 cpu 100%可能是在做回滚,但是因为你的停掉导致undo异常回滚不能通过,一直尝试
Wed Jul 4 21:29:50 2012
delete from "NGVES3"."OPERATION_ALLEGE_NUM_MV"
Wed Jul 4 21:29:50 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_j003_4517.trc:
ORA-12012: error on auto execute of job 444
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Wed Jul 4 21:30:03 2012
2、到v$session_wait 表里看看系统等待信息
3、把等待对应的session kill掉。看结果
1、因为创建了物化视图的缘故。
2、其中有定时每1分钟执行一次,将查询数据插入到另一个表中。
3、在昨天中午突然断电,造成oracle数据库服务器直接宕掉。
4、重启之后undo tablspace空间太小,cpu一直100% 删除该物化视图后恢复cpu正常值请各位有相关经验的或相同经历的DBA们指出专业的原因分析!-----------------------------
Wed Jul 4 21:29:50 2012
delete from "NGVES3"."OPERATION_ALLEGE_NUM_MV"
Wed Jul 4 21:29:50 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_j003_4517.trc:
ORA-12012: error on auto execute of job 444
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Wed Jul 4 21:30:03 2012
cpu 100%可能是在做回滚,但是因为你的停掉导致undo异常回滚不能通过,一直尝试