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