Oracle XE 10.2.0.1 (x86) на Windows XP (5.1)

При запуске база падает с  ORA-03113: end-of-file on communication channel в alert-логе ошибки:
©Bobrovsky Dmitry
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
и
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kghstack_err+101] [PC:0x603CDC81] [ADDR:0x93149F18] [UNABLE_TO_READ] []
©Bobrovsky Dmitry
далее экземпляр просто рушится
©Bobrovsky Dmitry

Dump file c:oraclexeapporacleadminxebdumpalert_xe.log
Tue Mar 06 11:48:26 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows XP Version V5.1 Service Pack 3
CPU : 1 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1337M/2013M, Ph+PgF:3325M/3903M, VA:1945M/2047M
Tue Mar 06 11:48:26 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
 sessions = 49
 __shared_pool_size = 163577856
 __large_pool_size = 4194304
 __java_pool_size = 4194304
 __streams_pool_size = 0
 spfile = C:ORACLEXEAPPORACLEPRODUCT10.2.0SERVERDBSSPFILEXE.ORA
 sga_target = 599785472
 control_files = C:ORAC
LEXEORADATAXECONTROL.DBF
 __db_cache_size = 423624704
 compatible = 10.2.0.1.0
 db_recovery_file_dest = C:oraclexeapporacleflash_recovery_area
 db_recovery_file_dest_size= 10737418240
 undo_management = AUTO
 undo_tablespace = UNDO
 remote_login_passwordfile= EXCLUSIVE
 dispatchers = (PROTOCOL=TCP) (SERVICE=XEXDB)
 shared_servers = 4
 job_queue_processes = 4
 audit_file_dest = C:ORACLEXEAPPORACLEADMINXEADUMP
 background_dump_dest = C:ORACLEXEAPPORACLEADMINXEBDUMP
 user_dump_dest = C:ORACLEXEAPPORACLEADMINXEUDUMP
 core_dump_dest = C:ORACLEXEAPPORACLEADMINXECDUMP
 db_name = XE
 open_cursors = 300
 os_authent_prefix =
 pga_aggregate_target = 199229440
PMON started with pid=2, OS id=2720
PSP0 started with pid=3, OS id=1176
MMAN started with pid=4, OS id=2912
DBW0 started with pid=5, OS id=3064
LGWR started with pid=6, OS id=2908
CKPT started with pid=7, OS id=3148
SMON started with pid=8, OS id=3132
RECO started with pid=9, OS id=528
CJQ0 started with pid=10, OS id=32
MMON started with pid=11, OS id=3352
MMNL started with pid=12, OS id=2932
Tue Mar 06 11:48:27 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
Tue Mar 06 11:48:27 2012
alter database mount exclusive
Tue Mar 06 11:48:31 2012
Setting recovery target incarnation to 2
Tue Mar 06 11:48:31 2012
Successful mount of redo thread 1, with mount id 2658360907
Tue Mar 06 11:48:31 2012
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Tue Mar 06 11:48:31 2012
alter database open
Tue Mar 06 11:48:31 2012
Beginning crash recovery of 1 threads
Tue Mar 06 11:48:31 2012
Started redo scan
Tue Mar 06 11:48:32 2012
Completed redo scan
58 redo blocks read, 5 data blocks need recovery
Tue Mar 06 11:48:32 2012
Started redo application at
Thread 1: logseq 358, block 3
Tue Mar 06 11:48:32 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 358 Reading mem 0
 Mem# 0 errs 0: C:ORACLEXEAPPORACLEFLASH_RECOVERY_AREAXEONLINELOGO1_MF_1_6WLHFG7B_.LOG
Tue Mar 06 11:48:32 2012
Completed redo application
Tue Mar 06 11:48:32 2012
Completed crash recovery at
Thread 1: logseq 358, block 61, scn 11868841
5 data blocks read, 5 data blocks written, 58 redo blocks read
Tue Mar 06 11:48:32 2012
Thread 1 advanced to log sequence 359
Thread 1 opened at log sequence 359
 Current log# 2 seq# 359 mem# 0: C:ORACLEXEAPPORACLEFLASH_RECOVERY_AREAXEONLINELOGO1_MF_2_6WLHFGTV_.LOG
Successful open of redo thread 1
Tue Mar 06 11:48:32 2012
SMON: enabling cache recovery
Tue Mar 06 11:48:33 2012
Successfully onlined Undo Tablespace 1.
Tue Mar 06 11:48:33 2012
SMON: enabling tx recovery
Tue Mar 06 11:48:33 2012
Database Characterset is CL8MSWIN1251
Tue Mar 06 11:48:34 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_smon_3132.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []

replication_dependency_tracking turned off (no async multimaster replication found)
Tue Mar 06 11:48:36 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_smon_3132.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kghstack_err+101] [PC:0x603CDC81] [ADDR:0x93149F18] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []

Starting background process QMNC
QMNC started with pid=19, OS id=3924
Tue Mar 06 11:48:39 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_pmon_2720.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:39 2012
PMON: terminating instance due to error 474
Tue Mar 06 11:48:39 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_lgwr_2908.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:39 2012
Error 0 in kwqmnpartition(), aborting txn
Tue Mar 06 11:48:40 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_reco_528.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:40 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_ckpt_3148.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:41 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_dbw0_3064.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:41 2012
Errors in file c:oraclexeapporacleadminxebdumpxe_mman_2912.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:41 2012
Errors in file c:
oraclexeapporacleadminxebdumpxe_psp0_1176.trc:
ORA-00474: SMON process terminated with error

Tue Mar 06 11:48:41 2012
Instance terminated by PMON, pid = 2720

©Bobrovsky Dmitry
Нашел только одну грамотную статью в тему — One Long and Nasty Recovery. Причина падения — попытка Oracle откатить (rollback) данные не зафиксированных транзакций (uncommitted data). Далее автор пытается разобраться в причинах крушения базы при применении данных из UNDO (подробно статью дальше не читал, т.к. не было времени).

Я проверил все файлы с помощью dbv. Только одно табличное пространство оказалось битым — SYSAUX. Поэтому я для себя сделал вывод, что при попытке отката транзакции идет обращение именно к этим битым блокам и здесь экземпляр почему-то падает, хотя по идее не должен (но это на совести программистов).
©Bobrovsky Dmitry
Предлагается использовать событие event=»10513 trace name context forever, level 2″, чтобы отключить восстановление транзакций.
©Bobrovsky Dmitry

SQL> conn sys/passwd@xe as sysdba
SQL> shutdown immediate;
SQL> ALTER SYSTEM SET EVENTS '10513 trace name context forever, level 2';
SQL> startup

Но это не помогло БД всё равно падала. Далее то что называется «чуйка» 🙂 При просмотре лога меня смутило «sessions = 49«. Чисто интуитивно добавил еще установку sessions и processes. (Если  ALTER SYSTEM SET EVENTS не срабатывает, можно добавить параметр event в spfile).

SQL> conn sys/passwd@xe as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> alter system set processes=150 scope=spfile;
SQL> alter system set sessions=170 scope=spfile;
SQL> shutdown immediate;
SQL> ALTER SYSTEM SET EVENTS '10513 trace name context forever, level 2';
SQL> startup mount
SQL> alter database open

Это помогло и база запустилась. Но при этом есть данные не зафиксированных транзакций и битые блоки в SYSAUX. Т.к. рабочая схема находится в ТП USERS и она небольшая, то я решил не заморачиваться, а слить дамп с рабочей схемы, создать новую БД и залить его туда, а эту БД удалить.
Запись ORA-00600 kghstack_free2 ORA-07445 _kghstack_err впервые появилась Dmitry Bobrovsky Blog