Databáze neustále padala na ORA-600. V alert.logu se pravidelně objevovaly tyto ORA chyby.

Tue Jan 19 22:42:49 2016
 Errors in file /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_smon_5609.trc  (incident=173343):
 ORA-00600: internal error code, arguments: [13013], [5001], [260], [8466145], [9], [8466145], [17], [], [], [], [], []
 Incident details in: /oracle/ora/diag/rdbms/orcl/orcl/incident/incdir_173343/orcl_smon_5609_i173343.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
 SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Jan 19 22:45:42 2016
Errors in file /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_m000_6269.trc  (incident=173567):
ORA-00600: internal error code, arguments: [12811], [288185], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/ora/diag/rdbms/orcl/orcl/incident/incdir_173567/orcl_m000_6269_i173567.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_m000_6269.trc:
ORA-00600: internal error code, arguments: [12811], [288185], [], [], [], [], [], [], [], [], [], []

Tue Jan 19 23:07:30 2016
Errors in file /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_m000_7023.trc  (incident=173388):
ORA-00600: internal error code, arguments: [13011], [5999], [8494209], [156], [55060610], [0], [], [], [], [], [], []
Incident details in: /oracle/ora/diag/rdbms/orcl/orcl/incident/incdir_173388/orcl_m000_7023_i173388.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_m000_7023.trc  (incident=173389):
ORA-00600: internal error code, arguments: [kewrose_1], [600], [ORA-00600: internal error code, arguments: [13011], [5999], [8494209], [156], [55060610], [0], [], [], [], [], [], []
], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/ora/diag/rdbms/orcl/orcl/incident/incdir_173389/orcl_m000_7023_i173389.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dump continued from file: /oracle/ora/diag/rdbms/orcl/orcl/trace/orcl_smon_5609.trc
ORA-00600: internal error code, arguments: [13013], [5001], [260], [8466145], [9], [8466145], [17], [], [], [], [], []

V trace souboru orcl_smon_5609.trc jsem dohledal, že problémy dělá jeden kontrétní update.

========= Dump for incident 173343 (ORA 600 [13013]) ========

*** 2016-01-19 22:42:49.811
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=dma0vxbwh325p) -----
update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn_wrp=:4,  scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where scn =  (select min(scn) from smon_scn_time)

Na MOS jsem našel celou řadu různých notes, ale tyto dvě poukazovali přímo na náš problém.
Instance Terminated With Error ORA-00474: SMON Process Terminated With Error (Doc ID 1361872.1)
New and Improved: ORA-600 [13013] „Unable to get a Stable set of Records“ (Doc ID 1438920.1)

SYS@orcl> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE;

Table analyzed.

SYS@orcl> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE;
ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Na vině byly corruptované indexy, tak jsem je rebuildnul.

SYS@orcl> ALTER INDEX SMON_SCN_TIME_TIM_IDX REBUILD ONLINE;
Index altered.

SYS@orcl> ALTER INDEX SMON_SCN_TIME_SCN_IDX REBUILD  ONLINE;
Index altered.

SYS@orcl> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE;

Table analyzed.

Pokud by to nepomohlo, tak tu bylo i další možné řešení.

SMON Signals ORA-00600 [ktspfupdst-1] and Instance Terminates (Doc ID 1353076.1)
How to Resolve ORA-08102 Reported on Table SMON_SCN_TIME (Doc ID 978502.1)
Understanding and Diagnosing ORA-00600 [13011] Errors (Doc ID 1392778.1)

Cause
SMON_SCN_TO_TIME table is corrupted .

Solution
Recreate the SMON_SCN_TIME table and related cluster with the assistance of Oracle Support.
SMON_SCN_TIME is updated by SMON every 5 minutes with a timestamp and the current SCN.
It holds 5 days worth of records (1440); data older than 5 days is aged out. This table makes it possible to roughly find an SCN for a point in time in the last 5 days



Komentáře