问一个关于Job得问题

canyon

我有一个job,主要就是定时删除过期得数据,但是最近经常发现这个job执行失败16次后被oracle停掉了,
查看了alert日志,只有一些checkpoint not complete 还有redo file之类得错误, 到后期又出现了回滚区太小得错误.
不知道这几个错误是否能引起job执行失败, 能否消除这个问题?

告警日志基本都是下面这下内容了:

Thread 1 advanced to log sequence 23655
  Current log# 3 seq# 23655 mem# 0: /export/home/oracle/oradata/ORADB/redo01.log
Thread 1 advanced to log sequence 23656
  Current log# 1 seq# 23656 mem# 0: /export/home/oracle/oradata/ORADB/redo03.log
Thread 1 cannot allocate new log, sequence 23657
Checkpoint not complete
  Current log# 1 seq# 23656 mem# 0: /export/home/oracle/oradata/ORADB/redo03.log
Thread 1 advanced to log sequence 23657
  Current log# 2 seq# 23657 mem# 0: /export/home/oracle/oradata/ORADB/redo02.log
Thread 1 advanced to log sequence 23658
  Current log# 3 seq# 23658 mem# 0: /export/home/oracle/oradata/ORADB/redo01.log
Thread 1 advanced to log sequence 23659

Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4
Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4
Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4
Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4
Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4
Failure to extend rollback segment 4 because of 1562 condition
Failure to extend rollback segment 4





谢谢了!

[ 本帖最后由 canyon 于 2007-9-18 21:00 编辑 ]

numenhuang

一次清理的记录数有多少，如果很多，应该分批次提交，不应该就一次提交。

canyon

每条删除语句后就提交.

numenhuang

数据库运行在什么模式，archivelog 还是 nonarchivelog

日志是否能正常切换。

可能的话，把job执行的proedure贴出来看看（可以先处理一下敏感部分）

canyon

嘿嘿,前面可能发现的问题不全面,后来发现了如下错误:
ORA-12012: error on auto execute of job 21
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","DECLARE job BINARY_INTEGER :...","PL/SQL MPCODE","BAMIMA: Bam Buffer")
请帮忙看看应该从哪几个方面解决,我看实际配置的sharemem好像也不是很小

canyon

我的shared_pool_size设置是50M, 物理内存2G, 是否太小了.
但是不知道这个4096字节的内存是为什么?
谢谢了, 还望指点.

numenhuang

数据库版本多少

shared_pool_size=50m 对于生产库来说小了点。

整个Oracle SGA可以开到物理内存的50-60%，具体要看服务器的用途。
至于shared_pool_size的设置，则依赖于应用的类型和规模，可以尝试开到300-400m

canyon

谢谢, oracle是8.1.7, 回头马上试试.