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部落格 ” ，連結：http://blog.itpub.net/73920/viewspace-1007528/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 解決了一例Shutdown時碰到Ora-600錯誤的問題
- oracle 死鎖解決方法一例Oracle
- sqlplus hang 解決方法一例SQL
- MySQL的root密碼忘記或丟失的解決方法一例MySql密碼
- DRM引起的問題解決一例
- ORA-600(17069)錯誤的解決過程
- Oracle聯機日誌檔案丟失解決方法一例Oracle
- tns-12560 tns-00530 解決方法一例
- Oracle DRM引起的問題解決一例Oracle
- Rownum分頁故障解決一例
- ORA-00959 解決一例
- 解決mysql innodb page corrupt一例MySql
- 執行create table as 報ora-600的錯誤的解決方案
- 解決ORA-600(16164)錯誤的過程（二）
- 解決ORA-600(16164)錯誤的過程（一）
- 解決掉電導致的ORA-600(4194)錯誤
- ora-02049解決一例
- ora-1105 解決一例
- ORA-03232故障解決一例
- ORA-01652解決一例
- ora600 4194解決一例
- ORA-27301解決一例
- Processes引數設定引起的故障解決一例
- keepalived啟動報錯解決一例
- VMWARE WORKSATTION 中 “the device is curreently in use” 解決一例dev
- [Oracle]--Library cache lock 故障解決一例Oracle
- mysql 匯入中文亂碼解決一例MySql
- 使用errorstack解決ORA-00904一例Error
- 行遷移檢測及解決一例
- solaris單使用者解決故障一例
- 解決HIS叢集系統的效能問題一例
- TNS-12547,TNS-12537錯誤解決方法一例
- TabError的解決方法Error
- ArtifactDescriptorException的解決方法Exception
- MySQL主從複製問題解決一例MySql
- composer包依賴衝突解決一例
- 解決一例latch:cache buffers chains小記AI
- ORA-00904故障分析與解決一例