首页 > 数据库 > Oracle > ora-600一例的解决方法
数据库版本:oracle 9201企业版
操作系统:RedHat linux 9
用户反映
select count(*)
from storage_charge a
where a.bill_start_date >= to_date('2008/07/10', 'yyyy/mm/dd')
and a.bill_start_date <= to_date('2008/07/10', 'yyyy/mm/dd')
and a.customer_code = 'FGCE01'
执行报错:ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
但执行
select count(*)
from storage_charge a
where a.bill_start_date >= to_date('2008/07/09', 'yyyy/mm/dd')
and a.bill_start_date <= to_date('2008/07/09', 'yyyy/mm/dd')
and a.customer_code = 'FGCE01'
则可以正常执行。
[@more@]
检查数据库的alert日志,发现频繁出现下列错误:
Sun Jul 20 10:02:02 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p002_5992.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:02:02 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p003_5994.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:02:02 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:02:02 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:02:21 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:02:21 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 35896.
Sun Jul 20 10:02:28 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:04:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p002_5992.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:04:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:04:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:04:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p003_5994.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:05:14 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:05:14 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 35896.
Sun Jul 20 10:05:25 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:07:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p002_5992.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:07:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p003_5994.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:07:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:07:54 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:08:15 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:08:15 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 35896.Sun Jul 20 10:08:27 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:09:53 2008
ORA-01555 caused by SQL statement below (Query Duration=1216519790 sec, SCN: 0x0
000.2aa16e27):
Sun Jul 20 10:09:53 2008
alter index epz.SYS_C008308 rebuild
Sun Jul 20 10:10:41 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p003_5994.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:10:41 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p002_5992.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:10:41 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:10:41 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:11:01 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:11:02 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 35896.
Sun Jul 20 10:11:13 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:14:23 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p003_5994.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:14:23 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:14:23 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:14:23 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p002_5992.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:14:42 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:14:42 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 35896.
Sun Jul 20 10:14:49 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:17:04 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p001_5990.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:17:04 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p004_5996.trc:
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [
], []
Sun Jul 20 10:17:14 2008
SMON: Restarting fast_start parallel rollback
Sun Jul 20 10:17:16 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p000_5988.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:17:30 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:17:30 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 56811.
Sun Jul 20 10:17:41 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:20:59 2008
SMON: Restarting fast_start parallel rollback
Sun Jul 20 10:20:59 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p000_5988.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:21:21 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:21:21 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 56811.
Sun Jul 20 10:21:40 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:21:47 2008
Thread 1 advanced to log sequence 5757
Current log# 3 seq# 5757 mem# 0: /opt/ora9/oradata/WMS/redo03.log
Sun Jul 20 10:21:47 2008
ARC0: Evaluating archive log 2 thread 1 sequence 5756
ARC0: Beginning to archive log 2 thread 1 sequence 5756
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15756.arc'
Sun Jul 20 10:21:59 2008
ARC0: Completed archiving log 2 thread 1 sequence 5756
Sun Jul 20 10:22:15 2008
Thread 1 advanced to log sequence 5758
Current log# 1 seq# 5758 mem# 0: /opt/ora9/oradata/WMS/redo01.log
Sun Jul 20 10:22:15 2008
ARC0: Evaluating archive log 3 thread 1 sequence 5757
ARC0: Beginning to archive log 3 thread 1 sequence 5757
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15757.arc'
ARC0: Completed archiving log 3 thread 1 sequence 5757
Sun Jul 20 10:22:40 2008
Thread 1 advanced to log sequence 5759
Current log# 2 seq# 5759 mem# 0: /opt/ora9/oradata/WMS/redo02.log
Sun Jul 20 10:22:40 2008
ARC0: Evaluating archive log 1 thread 1 sequence 5758
ARC0: Beginning to archive log 1 thread 1 sequence 5758
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15758.arc'
ARC0: Completed archiving log 1 thread 1 sequence 5758
Sun Jul 20 10:23:02 2008
Thread 1 advanced to log sequence 5760
Current log# 3 seq# 5760 mem# 0: /opt/ora9/oradata/WMS/redo03.log
Sun Jul 20 10:23:02 2008
ARC0: Evaluating archive log 2 thread 1 sequence 5759
ARC0: Beginning to archive log 2 thread 1 sequence 5759
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15759.arc'
ARC0: Completed archiving log 2 thread 1 sequence 5759
Sun Jul 20 10:23:31 2008
Thread 1 advanced to log sequence 5761
Current log# 1 seq# 5761 mem# 0: /opt/ora9/oradata/WMS/redo01.log
Sun Jul 20 10:23:31 2008
ARC0: Evaluating archive log 3 thread 1 sequence 5760
ARC0: Beginning to archive log 3 thread 1 sequence 5760
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15760.arc'
ARC0: Completed archiving log 3 thread 1 sequence 5760
Sun Jul 20 10:23:46 2008
SMON: Restarting fast_start parallel rollback
Sun Jul 20 10:23:46 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_p000_5988.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:23:55 2008
Thread 1 advanced to log sequence 5762
Current log# 2 seq# 5762 mem# 0: /opt/ora9/oradata/WMS/redo02.log
Sun Jul 20 10:23:55 2008
ARC0: Evaluating archive log 1 thread 1 sequence 5761
ARC0: Beginning to archive log 1 thread 1 sequence 5761
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15761.arc'
Sun Jul 20 10:24:00 2008
ARC1: Evaluating archive log 1 thread 1 sequence 5761
ARC1: Unable to archive log 1 thread 1 sequence 5761
Log actively being archived by another process
Sun Jul 20 10:24:01 2008
ARC0: Completed archiving log 1 thread 1 sequence 5761
Sun Jul 20 10:24:04 2008
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun Jul 20 10:24:04 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
ORACLE Instance WMS (pid = 6) - Error 600 encountered while recovering transacti
on (10, 11) on object 56811.
Sun Jul 20 10:24:14 2008
Errors in file /opt/ora9/admin/WMS/bdump/wms_smon_5870.trc:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [],
[]
Sun Jul 20 10:24:27 2008
Thread 1 advanced to log sequence 5763
Current log# 3 seq# 5763 mem# 0: /opt/ora9/oradata/WMS/redo03.log
Sun Jul 20 10:24:27 2008
ARC0: Evaluating archive log 2 thread 1 sequence 5762
ARC0: Beginning to archive log 2 thread 1 sequence 5762
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15762.arc'
ARC0: Completed archiving log 2 thread 1 sequence 5762
Sun Jul 20 10:24:49 2008
Thread 1 advanced to log sequence 5764
Current log# 1 seq# 5764 mem# 0: /opt/ora9/oradata/WMS/redo01.log
Sun Jul 20 10:24:49 2008
ARC0: Evaluating archive log 3 thread 1 sequence 5763
ARC0: Beginning to archive log 3 thread 1 sequence 5763
Creating archive destination LOG_ARCHIVE_DEST_1: '/home/oracle/WMS/archivewms/wm
s15763.arc'
ARC0: Completed archiving log 3 thread 1 sequence 5763
发现一些对象上有报错:
object id为 56811和35896
通过查询dba_objects,找到对应的对象类型和对象名称
select owner,object_type,object_name from dba_objects where object_id=56811 or bojiect_id=35896;
发现该对象类型为表STORAGE_CHARGE上主键约束和索引
1、删除主键
alter table STORAGE_CHARGEdrop constraint SYS_C008308;
重建主键约束
alter table STORAGE_CHARGE
add primary key (BILL_START_DATE, BILL_END_DATE, CUSTOMER_CODE, PRODUCT_CODE, STORING_ORDER_NO, BILL_FACTOR)
using index
tablespace INXWMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128M
minextents 1
maxextents unlimited
);
2、删除索引
drop index SYS_C008309;
创建索引
create index SYS_C008309 on STORAGE_CHARGE (CUSTOMER_CODE, PRODUCT_CODE, STORING_ORDER_NO)
tablespace INXWMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128M
minextents 1
maxextents unlimited
);
监控alert日志30分钟,没有发现报错信息,至此,问题解决。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/73920/viewspace-1007528/ ,如需转载,请注明出处,否则将追究法律责任。