ORA-00600: internal error code, arguments: [kghstack_free2]一例

Liu Maclean(刘相兵

ORA-00600: internal error code, arguments: [kghstack_free2]一例

数据库 出现ORA-00600 [kghstack_free2]内部错误导致
实例意外终止，重启数据库后仍不断遇到该错误并导致instance crash

数据库后台日志文件alert.log报以下异常：

Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Thu Sep 20 09:46:09 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_856120.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Starting background process QMNC
QMNC started with pid=53, OS id=1229222
Thu Sep 20 09:46:11 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:12 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:13 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:15 2012
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Thu Sep 20 09:46:16 2012
Completed: ALTER DATABASE OPEN
Thu Sep 20 09:46:18 2012
Doing block recovery for file 370 block 140818
Block recovery from logseq 336560, block 264 to scn 12971246479133
Thu Sep 20 09:46:18 2012
Recovery of Online Redo Log: Thread 1 Group 4 Seq 336560 Reading mem 0
  Mem# 0: /oradata4/CDMADB/redolog/redolog4a.log
  Mem# 1: /oradata4/CDMADB/redolog/redolog4b.log
Block recovery completed at rba 336560.27935.16, scn 3020.445245214
Thu Sep 20 09:46:18 2012
SMON: slave died unexpectedly, downgrading to serial recovery
Thu Sep 20 09:46:18 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_smon_729208.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:20 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:21 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:22 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:24 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_pmon_786638.trc:
ORA-00474: SMON process terminated with error
Thu Sep 20 09:46:24 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 786638
Thu Sep 20 10:38:31 2012
Starting ORACLE instance (normal)
sskgpgetexecname failed to get name
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =61
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 500
  sessions                 = 555
  __shared_pool_size       = 637534208
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  sga_target               = 21474836480
  control_files            = /oradata1/CDMADB/control1/control01.ctl, /oradata1/CDMADB/control2/control02.ctl, /oradata1/CDMADB/control3/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 20786970624
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = LOCATION=/oraarch/cdma
  log_archive_format       = %t_%s_%r.dbf
  db_files                 = 4000
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS2
  undo_retention           = 7200
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = TRUE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=CDMAXDB)
  job_queue_processes      = 10
  background_dump_dest     = /user/oracle/admin/CDMA/bdump
  user_dump_dest           = /user/oracle/admin/CDMA/udump
  core_dump_dest           = /user/oracle/admin/CDMA/cdump
  audit_file_dest          = /user/oracle/admin/CDMA/adump
  audit_trail              = DB, EXTENDED
  db_name                  = CDMA
  open_cursors             = 300
  pga_aggregate_target     = 10737418240
MMAN started with pid=4, OS id=368872
PSP0 started with pid=3, OS id=184496
PMON started with pid=2, OS id=94512
DBW0 started with pid=5, OS id=331874
DBW1 started with pid=6, OS id=418128
LGWR started with pid=7, OS id=397498
CKPT started with pid=8, OS id=508388
SMON started with pid=9, OS id=459050
RECO started with pid=10, OS id=471536
CJQ0 started with pid=11, OS id=442822
MMON started with pid=12, OS id=180548
Thu Sep 20 10:38:38 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=13, OS id=352574
Thu Sep 20 10:38:38 2012
starting up 1 shared server(s) ...
Thu Sep 20 10:38:42 2012
ALTER DATABASE   MOUNT
Thu Sep 20 10:38:47 2012
Setting recovery target incarnation to 2
Thu Sep 20 10:38:47 2012
Successful mount of redo thread 1, with mount id 2376692082
Thu Sep 20 10:38:47 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Sep 20 10:38:47 2012
ALTER DATABASE OPEN
Thu Sep 20 10:38:49 2012
Beginning crash recovery of 1 threads
parallel recovery started with 15 processes
Thu Sep 20 10:38:50 2012
Started redo scan
Thu Sep 20 10:38:51 2012
Completed redo scan
27994 redo blocks read, 8334 data blocks need recovery
Thu Sep 20 10:38:52 2012
Started redo application at
Thread 1: logseq 336560, block 3
Thu Sep 20 10:38:52 2012
Recovery of Online Redo Log: Thread 1 Group 4 Seq 336560 Reading mem 0
  Mem# 0: /oradata4/CDMADB/redolog/redolog4a.log
  Mem# 1: /oradata4/CDMADB/redolog/redolog4b.log
Thu Sep 20 10:38:53 2012
Completed redo application
Thu Sep 20 10:38:56 2012
Completed crash recovery at
Thread 1: logseq 336560, block 27997, scn 12971246499154
8334 data blocks read, 8334 data blocks written, 27994 redo blocks read
Thu Sep 20 10:38:57 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=32, OS id=770284
ARC1 started with pid=33, OS id=762350
Thu Sep 20 10:38:57 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thu Sep 20 10:38:58 2012
Thread 1 advanced to log sequence 336561 (thread open)
Thread 1 opened at log sequence 336561
  Current log# 2 seq# 336561 mem# 0: /oradata1/CDMADB/redolog/redolog2a.log
  Current log# 2 seq# 336561 mem# 1: /oradata1/CDMADB/redolog/redolog2b.log
Successful open of redo thread 1
Thu Sep 20 10:38:58 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Sep 20 10:38:58 2012
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thu Sep 20 10:38:58 2012
ARC0: Becoming the heartbeat ARCH
Thu Sep 20 10:38:58 2012
SMON: enabling cache recovery
Thu Sep 20 10:39:03 2012
Successfully onlined Undo Tablespace 5.
Thu Sep 20 10:39:03 2012
SMON: enabling tx recovery
Thu Sep 20 10:39:03 2012
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
Thu Sep 20 10:39:04 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_491552.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=52, OS id=770310
Thu Sep 20 10:39:06 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:08 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:08 2012
Errors in file /user/oracle/admin/CDMA/bdump/cdma_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:11 2012
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818




  现场工程师设置10513 level 2内部事件后，SMON不再recover      dead         transaction ，打开数据库后实例不再意外终止。

SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10513 trace name context forev
                                                 er, level 2

  通过RMAN validate命令和dbv检测数据文件，发现CK.CKDT_CDMA_SNAP表上存在一个逻辑讹误块和一个物理讹误块。


SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
       370     140818          1         1.2971E+13 LOGICAL
        84      23875          1                  0 CORRUPT


鉴于用户可以接受少量数据的损失，且blockrecover以上物理讹误块在RMAN中实际运行时寻找该FILE 84 BLOCK 23875耗费大量时间，所以考虑利用ROWID的方式绕过讹误块，拷贝出绝大多数的表数据。

SQL> select dbms_rowid.rowid_create(1,353223,370,140818,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAh1CAFyAAAiYSAAA


SQL> select dbms_rowid.rowid_create(1,353223,370,140819,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AABWPHAFyAAAiYTAAA



alter session set events '10231 trace name context forever, level 10';


SQL> select count(*) from CK.CKDT_CDMA_SNAP;

  COUNT(*)
----------
   8450840



insert /*+ APPEND */ into CKDT_CDMA_SNAP_BAK   select  /*+ ROWID(A) */ *  from  CK.CKDT_CDMA_SNAP A  where rowid <'AABWPHAFyAAAiYSAAA';

insert /*+ APPEND */ into CKDT_CDMA_SNAP_BAK   select  /*+ ROWID(A) */ *  from  CK.CKDT_CDMA_SNAP A  where rowid >='AABWPHAFyAAAiYTAAA';


SQL> select count(*) from CKDT_CDMA_SNAP_BAK;


  COUNT(*)
----------
   8450796

alter table CK.CKDT_CDMA_SNAP rename to CKDT_CDMA_SNAP_20120920_BAK;


alter table CK.CKDT_CDMA_SNAP_BAK rename to CKDT_CDMA_SNAP;



SQL>
SQL> select count(*) from CK.CKDT_CDMA_SNAP;

  COUNT(*)
----------
   8450796

SQL> analyze table CK.CKDT_CDMA_SNAP validate structure;

Table analyzed.

drop table CK.CKDT_CDMA_SNAP_20120920_BAK;


原CKDT_CDMA_SNAP共计8450840条数据，实际修复出8450796条记录，丢失44条。

后续使用CREATE TABLE命令将原存在逻辑坏块的数据块覆盖，避免上述ORA-600问题再次发生。


create table LARGE_TABLE (t1 int) tablespace CKDT_REPORT_TBS ;

alter table LARGE_TABLE  allocate extent (datafile '/oradata4/CDMADB/CDMA/CDMA_32.dbf' size 10M);


后续建议：

建议用户对CDMA全库做数据文件的验证操作,找出数据库中潜在的坏块，避免此类问题再次发生，若使用RMAN可以参考如下脚本：

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}


以上会将找到的坏块信息汇总到V$DATABASE_BLOCK_CORRUPTION视图中：

select * from V$DATABASE_BLOCK_CORRUPTION ;