内容简介：又近年末，各种事情忙的不可开交， 但最近的BUG又突然接二连三， 争取把在2018年的最后几天习惯性简单的总结了结， 这篇简单的记录一下上个月一套11.2.0.4 2-ndes RAC的案例， 问题发生的第二天说有个表还是不能insert和grant， insert报ora-600[qesmaGetTblSeg1] 和grant 报 ora-7445 [kss_first_child] ，得知事前有对该表做truncate, 这是一个HASH分区表， 相同的表结构在用其它表名一切正常，但是rename
又近年末，各种事情忙的不可开交， 但最近的BUG又突然接二连三， 争取把在2018年的最后几天习惯性简单的总结了结， 这篇简单的记录一下上个月一套11.2.0.4 2-ndes RAC的案例， 问题发生的第二天说有个表还是不能insert和grant， insert报ora-600[qesmaGetTblSeg1] 和grant 报 ora-7445 [kss_first_child] ，得知事前有对该表做truncate, 这是一个HASH分区表， 相同的表结构在用其它表名一切正常，但是rename tabler后问题又能重现, 包括把表删了重建问题依旧。
下面来还原这个问题:
SQL> CREATE TABLE ANBOB.LOC_ANBOB_VARLOG
2 ( "OID" NUMBER(14,0) NOT NULL ENABLE,
3 "VARIABLEID" VARCHAR2(32) NOT NULL ENABLE,
4 "SQLBANDVAL" VARCHAR2(256),
5 "RUNTIME" VARCHAR2(32) NOT NULL ENABLE,
6 "INTIME" DATE DEFAULT sysdate
7 ) PARTITION BY HASH ("OID")
8 partitions 8
9 store in(DATA310,DATA312,DATA312,DATA313,DATA314,DATA315,DATA316,DATA317,DATA318);
Table created.
SQL> analyze table ANBOB.LOC_ANBOB_VARLOG validate structure cascade;
Table analyzed.
SQL> insert into ANBOB.loc_ANBOB_varlog
2 (oid, variableid, sqlbandval, runtime, intime)
3 values
4 (ANBOB.seq_oid.nextval, '40000001', ':RECDATE:20180905;', '2.272908', sysdate);
(ANBOB.seq_oid.nextval, '40000001', ':RECDATE:20180905;', '2.272908', sysdate)
*
ERROR at line 4:
ORA-00600: internal error code, arguments: [qesmaGetTblSeg1], [2], [0], [0], [0x1D782F1EC0], [0x000000000], [1], [], [], [], [], []
SQL> grant select ,insert on ANBOB.LOC_ANBOB_VARLOG to WEEJAR;
grant select ,insert on ANBOB.LOC_ANBOB_VARLOG to dbmt
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7685
对grant做10046内容最后如下:
*** 2018-10-25 21:27:57.068 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x60] [PC:0x94D33F6, kss_first_child()+8] [flags: 0x0, count: 1] Incident 480505 created, dump file: /oracle/app/oracle/diag/rdbms/anbob/anbob2/incident/incdir_480505/anbob2_ora_18829_i480505.trc ORA-07445: exception encountered: core dump [kss_first_child()+8] [SIGSEGV] [ADDR:0x60] [PC:0x94D33F6] [Address not mapped to object] [] ssexhd: crashing the process... Shadow_Core_Dump = PARTIAL
ORA-600 [qesmaGetTblSeg1]
# ORA-600 [qesmaGetTblSeg1] trace file
1> ***** Error Stack *****
ORA-00600: internal error code, arguments: [qesmaGetTblSeg1], [6], [0], [0], [0x1D782F1EC0], [0x000000000], [1], [], [], [], [], []
1< ***** Error Stack ***** 1> ***** Dump for incident 600824 (ORA 600 [qesmaGetTblSeg1]) *****
2> ***** Beginning of Customized Incident Dump(s) *****
qesmaDump: pnum 6 ph 0x1d782f2010 ktid 0x1d782f2018 (tsn 25 objn 683886 objd 683886 dba 0 inc 0 enc 0)
qesmaDump: special context ctx 0x1ddf0e7688 rtctx 0x7f7382688f90 (PARTITIONED, NON-INTERVAL)
type 0 objn 683934 baseobjn 683934 colid 0 pcnt 8 hipnum 0
vobjHd 0x205b0db490, obsoleted 0
Wrapper context over a PAM 0
qesmaDump: ctx 0x1ddf0e7688
qesmaDump: pnum 0 ph 0x1d782f1ec0 ktid 0x1d782f1ec8 (tsn 19 objn 683880 objd 683880 dba 0 inc 0 enc 0)
qesmaDump: pnum 1 ph 0x1d782f1ef8 ktid 0x1d782f1f00 (tsn 21 objn 683881 objd 683881 dba 0 inc 0 enc 0)
qesmaDump: pnum 2 ph 0x1d782f1f30 ktid 0x1d782f1f38 (tsn 21 objn 683882 objd 683882 dba 0 inc 0 enc 0)
qesmaDump: pnum 3 ph 0x1d782f1f68 ktid 0x1d782f1f70 (tsn 66 objn 683883 objd 683883 dba 0 inc 0 enc 0)
qesmaDump: pnum 4 ph 0x1d782f1fa0 ktid 0x1d782f1fa8 (tsn 23 objn 683884 objd 683884 dba 0 inc 0 enc 0)
qesmaDump: pnum 5 ph 0x1d782f1fd8 ktid 0x1d782f1fe0 (tsn 65 objn 683885 objd 683885 dba 0 inc 0 enc 0)
qesmaDump: pnum 6 ph 0x1d782f2010 ktid 0x1d782f2018 (tsn 25 objn 683886 objd 683886 dba 0 inc 0 enc 0)
qesmaDump: pnum 7 ph 0x1d782f2048 ktid 0x1d782f2050 (tsn 63 objn 683887 objd 683887 dba 0 inc 0 enc 0)
kkpomDump: No sort_ctx
kkpodDumpOcd begin: hd=0x201d279da0, "ANBOB"."LOC_ANBOB_VARLOG", kglobtyp=2 (TABLE)
ocd=0x1d5f5c8470
parttype_kkpacocd = 2
subptype_kkpacocd = 0
partcnt_kkpacocd = 8
flags_kkpacocd = 0x0
mflags_kkpacocd = 0x0
status_kkpacocd = 0x0
hasSageTsn_kkpacocd = 0
noDMOffload_kkpacocd = 0
noDIDTSOffload_kkpac = 0
partKCols_kkpacocd:
colCnt=1
[0] id=1, knl=1, dty=2, usr=1, csid=0, csform=0
pcd=0x1d5f5c87d8, partnum=1, dictnum=1, objn=683935 ## start 1 not 0
Owner = 68
Name = LOC_ANBOB_VARLOG
SubName = SYS_P132
objn_kkpacfrag = 683935
objd_kkpacfrag = 683935
tsn_kkpacfrag = 19
dba_kkpacfrag = 0x21809509
fragnum_kkpacfrag = 1
dictnum_kkpacfrag = 1
status_kkpacfrag = 0x0
flags_kkpacfrag = 0x0
fragtype_kkpacfrag = 1
hibnd_kkpacfrag = ""
bhibnd_kkpacfrag : len=0, key=
subpcnt_kkpacpcd = 0
status_kkpacpcd = 0
...
pcd=0x1d5f5c8dd8, partnum=7, dictnum=7, objn=683941
Owner = 68
Name = LOC_ANBOB_VARLOG
SubName = SYS_P138
objn_kkpacfrag = 683941
objd_kkpacfrag = 683941
tsn_kkpacfrag = 25
dba_kkpacfrag = 0x1f000509
fragnum_kkpacfrag = 7
dictnum_kkpacfrag = 7
status_kkpacfrag = 0x0
flags_kkpacfrag = 0x0
fragtype_kkpacfrag = 1
hibnd_kkpacfrag = ""
bhibnd_kkpacfrag : len=0, key=
subpcnt_kkpacpcd = 0
status_kkpacpcd = 0
...
kkpodDumpOcd end: hd=0x201d279da0
2< ***** End of Customized Incident Dump(s) *****
*** 2018-10-26 08:53:48.096 dbkedDefDump():
Starting incident default dumps (flags=0x2, level=3, mask=0x0) 2>
***** Current SQL Statement for this session (sql_id=cv8jdxdy7nuru) *****
insert into ANBOB.loc_ANBOB_varlog
(oid, variableid, sqlbandval, runtime, intime)
values
(ANBOB.seq_oid.nextval, '40000001', ':RECDATE:20180905;', '2.272908', sysdate)
2< ***** current_sql_statement *****
# 使用 shell 简单的格式化 trace文件中的call stack
[root@localhost ~]# cat trace_file.txt |sed -n '/Call Stack Trace/,/ main()/p'|sed -n '5,/*/p'|cut -c1-29|sed '/^[[:space:]]*$/d'|awk -F"(" '{printf $1 " <-" } NR%5==0{printf "\n"}'
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <-
ksfdmp <- dbgexPhaseII <- dbgexExplicitEndInc <- dbgeEndDDEInvocationImpl
<- dbgeEndDDEInvocation <- qesmaGetTblSeg_INT <
kdtgetpart <- qesltcBeforeRowProcessing <- qerltcNoKdtBuffered <- InsRowCBK <-
qerltcSingleRowLoad <- <- qerltcFetch <- insexe <- opiexe <-
kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <-
opiodr <- opidrv <- sou2o <- opimai_real <- ssthrdmain <-main
SQL> show parameter segment_cr
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ------------------
deferred_segment_creation boolean FALSE
TIP：
该库的延迟段创建特性系统级是禁用的， 解决这个问题，可以flush shared_pool解决, 后确认符合BUG 19689979. 据MOS描述也可以调整访问分表的一个隐参回退到11.2.0.3的行为避免该问题, 通常还伴有ora-8103和ORA-600 [ktecgsc:kcbz_objdchk]出现，甚至出现Wrong results.
Solution:
a) apply 11.2.0.4.170718 (Jul 2017) Database Patch Set Update (DB PSU) or later;
OR
b) apply Interim patch 19689979;
An alternative is to restart the instance with the hidden parameter “_part_access_version_by_number” set to FALSE. (RAC sites should restart *all* instances with this parameter set to the same value).
Using “_part_access_version_by_number” = FALSE will revert back to the 11.2.0.3 partitioned handling behaviour and will avoid the problem.
it is caused by a process trying to update SEG$ contents with on-disk info where the segment has undergone an object ID change (due to a TRUNCATE occurring).
ORA 7445 [kss_first_child()+8]
# ORA 7445 [kss_first_child()+8] trace file
1> ***** Error Stack *****
ORA-07445: exception encountered: core dump [kss_first_child()+8] [SIGSEGV] [ADDR:0x60] [PC:0x94D33F6] [Address not mapped to object] []
1< ***** Error Stack ***** 1> ***** Dump for incident 600812 (ORA 7445 [kss_first_child()+8]) *****
2> ***** Beginning of Customized Incident Dump(s) *****
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x60] [PC:0x94D33F6, kss_first_child()+8] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000060 %rbx: 0x00007fff152e8910 %rcx: 0x0000000000000001
...
*** 2018-10-26 08:53:52.380
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
3> ***** Current SQL Statement for this session (sql_id=1t4pgns6fq5nv) *****
grant select on ANBOB.LOC_ANBOB_VARLOG to dbmt
3< ***** current_sql_statement ***** 3>
***** Call Stack Trace *****
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <-
ssexhd <- __sighandler <- kss_first_child <- kglpin <- kzdopcl <-
kzpcaob <- kzpgop1 <- kzpgop <- gradrv <- opiexe <-
opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <-
opitsk <- opiino <- opiodr <- opidrv <- sou2o <-
opimai_real <- ssthrdmain <- main
# search key "LOC_ANBOB_VARLOG"
----------------------------------------
SO: 0x201d2ef648, type: 79, owner: 0x1f2a90d6d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x1fa2247d10, name=LIBRARY OBJECT PIN, file=kgl.h LINE:8755, pg=0
LibraryObjectPin: Address=0x201d2ef648 Handle=0x1ff6d8c678 Mode=X ClusterLock=0x1f84859288 User=0x1f42908b20 Session=0x1f42908b20 Count=2 Mask=0341 Flags=[00] SavepointNum=0xa9
LibraryHandle: Address=0x1ff6d8c678 Hash=5a76f26 LockMode=X PinMode=X LoadLockMode=0 Status=VALD
ObjectName: Name=ANBOB.LOC_ANBOB_VARLOG
FullHashValue=0e71e3ae3d88e7a084eb290d05a76f26 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=683865 OwnerIdn=68
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=9 ActiveLocks=1 TotalLockCount=78 TotalPinCount=88
Counters: BrokenCount=37 RevocablePointer=12 KeepDependency=0 Version=22 BucketInUse=367 HandleInUse=367 HandleReferenceCount=0
Concurrency: DependencyMutex=0x1ff6d8c728(0, 298, 0, 0) Mutex=0x1ff6d8c7b8(0, 1827, 0, 0)
Flags=PIN/TIM/HOT/[00002c01] ######### HOT
WaitersLists:
Lock=0x1ff6d8c708[0x1ff6d8c708,0x1ff6d8c708]
Pin=0x1ff6d8c6e8[0x1ff6d8c6e8,0x1ff6d8c6e8]
LoadLock=0x1ff6d8c760[0x1ff6d8c760,0x1ff6d8c760]
TIP:
该表曾被MARKHOT过，难怪只是这个表名提示出错， 后来同样找到了数据，确实同事之前解决library cache: mutex x时曾把表标准为HOT， 对于软解析高并发调用on mutex x确实用MARKHOT是一种解决手段，另外有时适当增加hard parse修改 SQL 文本也可以减少此类wait. 这不markhot就遇到了bug, 解决方法当前只能是重启实例，解决grant 报ORA-7445的问题。
Bug 22293453 : ORA-07445:[KSS_FIRST_CHILD] WHEN REFERENCING OBJECT MARKED HOT
The customer is having serious production database/site issues due to mutex
contention on the library cache. They are planning to keep highly used
objects as HOT in memory to avoid mutex issues. However, this triggers the
behavior as described in this bug.
RELATED ISSUES (bugs, forums, RFAs)
===================================
Bug 10257433 – ORA-600 after using DBMS_SHARED_POOL.MARKHOT for a sequence (Doc ID 10257433.8)
Bug 11677728 : ERRORS FOLLOWING GRANT/REVOKE DDL ON TABLE WHEN USING DBMS_SHARED_POOL.MARKHOT
Bug 17784200 : DOC FOR MARKHOT PROCEDURE DOES NOT DESCRIBE THE USE CASE
因为前一天我没在现场， 通过DASH回顾当天数据库负载，结果有个时间段的wait较为新奇。
SQL> select INSTANCE_NUMBER,event,count(*)*10 Total_sec from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time between to_date('20181025 15:00','yyyymmdd hh24:mi')
and to_date('20181025 15:15','yyyymmdd hh24:mi')
group by INSTANCE_NUMBER,event
order by 3 desc;
INSTANCE_NUMBER EVENT TOTAL_SEC
--------------- ---------------------------------------------------------------- ----------
1 log file sequential read 207230
1 force-cr-override flush 16620
1 control file sequential read 10380
1 db file sequential read 7280
1 db file parallel write 7030
Tip:
log file sequential read等待事件较少出现，这个时间段占用了那么高的DBTIME是不正常的, log file sequential read通常发生在：
1. LGWR doing a log switch
2. A Streams/GoldenGate/LogMiner/etc.. log mining operation
3. Manual dumping of redo log contents
4. Automatic Block Media Recovery
下一步查询一下DB alert LOG 是否当时有错误？ 这里我习惯先看下incident使用adrci.
adrci> show incident -all ADR Home = /oracle/app/oracle/diag/rdbms/anbob/anbob1: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- ---------------------------------------- 577726 ORA 603 2018-10-09 17:00:08.572000 +08:00 600691 ORA 600 [kcbzwfcro_2] 2018-10-25 14:52:37.570000 +08:00 601027 ORA 600 [kcbzwfcro_2] 2018-10-25 14:52:37.629000 +08:00 600915 ORA 600 [kcbzwfcro_2] 2018-10-25 14:52:37.762000 +08:00 600963 ORA 600 [kcbzwfcro_2] 2018-10-25 14:52:37.827000 +08:00 600947 ORA 600 [kcbzwfcro_2] 2018-10-25 14:52:37.967000 +08:00 601116 ORA 600 [ktecgsc:kcbz_objdchk] 2018-10-25 15:23:09.438000 +08:00 601012 ORA 600 [ktecgsc:kcbz_objdchk] 2018-10-25 15:23:17.050000 +08:00 603371 ORA 600 [ktecgsc:kcbz_objdchk] 2018-10-25 15:23:28.001000 +08:00 603915 ORA 600 [ktecgsc:kcbz_objdchk] 2018-10-25 15:23:28.040000 +08:00 601332 ORA 600 [ktecgsc:kcbz_objdchk] 2018-10-25 15:23:40.544000 +08:00 601111 ORA 600 2018-10-25 15:41:40.661000 +08:00 601421 ORA 600 2018-10-25 15:41:41.237000 +08:00 600982 ORA 600 2018-10-25 15:41:41.275000 +08:00 600965 ORA 600 2018-10-25 15:41:41.441000 +08:00 601119 ORA 600 2018-10-25 15:42:06.527000 +08:00 601518 ORA 600 [ktecgsc:objdchk_kcbgcur_3] 2018-10-25 17:30:37.434000 +08:00 601414 ORA 600 [ktecgsc:objdchk_kcbgcur_3] 2018-10-25 17:30:43.395000 +08:00 602323 ORA 600 [ktecgsc:objdchk_kcbgcur_3] 2018-10-25 17:30:49.479000 +08:00 602315 ORA 600 [ktecgsc:objdchk_kcbgcur_3] 2018-10-25 17:30:54.848000 +08:00 600819 ORA 600 [ktecgsc:objdchk_kcbgcur_3] 2018-10-25 17:31:00.938000 +08:00 600363 ORA 7445 [kss_first_child()+8] 2018-10-25 19:41:22.829000 +08:00 600811 ORA 7445 [kss_first_child()+8] 2018-10-25 19:48:36.925000 +08:00 600822 ORA 600 [kgltti-no-dep1] 2018-10-26 08:41:31.481000 +08:00 600803 ORA 600 [kgltti-no-dep1] 2018-10-26 08:42:08.734000 +08:00 600804 ORA 600 [kgltti-no-dep1] 2018-10-26 08:42:15.420000 +08:00 600805 ORA 600 [kgltti-no-dep1] 2018-10-26 08:42:19.513000 +08:00 600806 ORA 600 [kgltti-no-dep1] 2018-10-26 08:42:22.610000 +08:00 600611 ORA 600 [qesmaGetTblSeg1] 2018-10-26 08:44:46.575000 +08:00 600823 ORA 600 [qesmaGetTblSeg1] 2018-10-26 08:50:54.939000 +08:00 600824 ORA 600 [qesmaGetTblSeg1] 2018-10-26 08:53:48.055000 +08:00 600812 ORA 7445 [kss_first_child()+8] 2018-10-26 08:53:52.368000 +08:00
TIP:
问题前有发生ora-600 [kcbzwfcro_2] .
ora-600 [kcbzwfcro_2]
KCBZWFCRO==> Kernel Cache Buffers subroutines for kcb Waiting Force CR Override
# ora-600 [kcbzwfcro_2] trace file
1> ***** Error Stack *****
ORA-00600: internal error code, arguments: [kcbzwfcro_2], [615692], [4], [0], [8], [], [], [], [], [], [], []
1< ***** Error Stack *****
1> ***** Dump for incident 600691 (ORA 600 [kcbzwfcro_2]) *****
2> ***** Beginning of Customized Incident Dump(s) *****
Potentially stale force-CR-override buffer found before OBJD MISMATCH check.
This issue should be investigated by both cache fusion and space layer.
BH (0x1ca0761878) file#: 118 rdba: 0x1d804c09 (118/19465) class: 4 ba: 0x1cab818000
set: 175 pool: 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 6 obj: 683812 objn: -1 tsn: 65 afn: 118 hint: f
hash: [0x1f8542a420,0x1f8542a420] lru: [0x2feda5248,0x18ff4f39e8]
ckptq: [NULL] fileq: [NULL] objq: [0x1ba3735688,0x1ba3735688] objaq: [0x1ba3735678,0x1ba3735678]
st: SCURRENT md: NULL fpin: 'ktewh26: kteinpscan' tch: 47 le: 0x43fee8e28
flags: remote_transfered force_cr_override
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
GLOBAL CACHE ELEMENT DUMP (address: 0x43fee8e28):
id1: 0x4c09 id2: 0x76 pkey: OBJ#683812 block: (118/19465)
lock: S rls: 0x0 acq: 0x0 latch: 7
flags: 0x20 fair: 0 recovery: 0 fpin: 'ktewh26: kteinpscan'
bscn: 0xe9e.a1fc180c bctx: (nil) write: 0 scan: 0x0
lcp: (nil) lnk: [NULL] lch: [0x1ca07619b0,0x1ca07619b0]
seq: 539 hist: 397 334 144:0 213 7 144:5 192 491 352 197 48
LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE ELEMENT:
flg: 0x08000000 sflg: 0x2000 state: SCURRENT tsn: 65 tsh: 47
addr: 0x1ca0761878 obj: 683812 cls: SEG HEAD bscn: 0xe9e.a1fc180c
buffer tsn: 65 rdba: 0x1d804c09 (118/19465)
scn: 0x0e9e.a1fc180c seq: 0x01 flg: 0x04 tail: 0x180c2301
frmt: 0x02 chkval: 0x49bb type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
2018-10-25 14:54:31.460834 : kjbmsassert(0x5fd89.149)(to 2)(lvl 4)
DUMP REDO <<<<<<<<<
Opcodes *.*
DBAs (file#, block#):
(329, 392585) .
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
**NOTE: Only Dumping Redo less then 12 hours**
Times: 10/25/2018 02:54:31 thru eternity
Initial buffer sizes: read 1024K, overflow 832K, change 805K
...
Tip:
原来SYSTEMIO是因为ORA-600 [kcbzwfcro_2] 错误在系统自动在dump redo log. 根据MOS 2085507.1 发生在一个陈旧的BUFFER在buffer cache中，同样又是一个ORACLE BUG, 通常flush buffer_cache可以避免， 这个表同样也是上面的表头天删除过操作。
Solution:
Flush buffer cache OR to restart the instance
This is because the error will continue whilst the stale buffer is in cache. We can try flushing the buffer cache to invalidate this.
If that doesn’t help the instance will need to be restarted to clear the cache.
– OR –
Set “_gc_override_force_cr=false” to avoid this particular issue:
SQL> alter system set “_gc_override_force_cr”=false scope=both sid=’*’;
There should not be any noticeable performance impact.
ORA 600 [ktecgsc:kcbz_objdchk]
KTECGSC ==>> kernel transaction extent management concurrency get segment header and auto-convert
========= Dump for incident 601116 (ORA 600 [ktecgsc:kcbz_objdchk]) ========
----- Beginning of Customized Incident Dump(s) -----
Input data (nil), 0, 0
Formatted dump of block:
buffer tsn: 25 rdba: 0x291bcbc9 (164/1821641)
scn: 0x0e9e.a1ec409b seq: 0x01 flg: 0x04 tail: 0x409b2301
frmt: 0x02 chkval: 0x49aa type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 512
last map 0x00000000 #maps: 0 offset: 5452
Highwater:: 0x291bcbca ext#: 0 blk#: 10 ext size: 512
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
...
...
BH (0xcbf5a7d58) file#: 164 rdba: 0x291bcbc9 (164/1821641) class: 4 ba: 0xcbe44c000
set: 182 pool: 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 5 obj: 615693 objn: 615575 tsn: 25 afn: 164 hint: f
hash: [0x1fc7033098,0x1fc7033098] lru: [0x5bf34d6c8,0xc7eba5448]
ckptq: [NULL] fileq: [NULL] objq: [0x1ba1b133d0,0x1ba1b133d0] objaq: [0x1ba1b133c0,0x1ba1b133c0]
use: [0x1f27b4e4f0,0x1f27b4e4f0] wait: [NULL]
st: READING md: EXCL tch: 0 le: 0x187ffb4778
flags: only_sequential_access
# search "Current SQL"
*** 2018-10-25 15:31:32.512
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=13g13246mcntg) -----
insert into loc_ANBOB_varlog(oid,variableid,sqlbandval,runtime,intime) values (seq_oid.nextval,:variableid,:sqlbandval,:runtime,sysdate)
----- Call Stack Trace -----
-------------------- >
skdstdst()+41 >ksedst1()+103 >
ksedst()+39 >dbkedDefDump()+2746 >
ksedmp()+41 >ksfdmp()+69 >
dbgexPhaseII()+1764 >dbgexExplicitEndInc() >
dbgeEndDDEInvocationImpl()+769 >dbgeEndDDEInvocation()+52 >
kcbz_check_objd_typ() >kcbzib()+2933 >kcbgcur()+11330 >
ktecgsc()+78 >ktecgetsh()+182 >ktecgshx()+31 >ktspisc()+1154 >
ktspgsp_main()+851 >kdtgsp()+3214 >
kdtgsph()+2253 >kdtgrs()+451 >kdtInsRow()+811 >qerltcNoKdtBuffered >
InsRowCBK()+297 >qerltcSingleRowLoad >qerltcFetch()+379 >
insexe()+691 >opiexe()+5632 >kpoal8()+2118 >
opiodr()+917 >ttcpip()+2183 >
opitsk()+1710 >opiino()+969 >
opiodr()+917 >opidrv()+570 >
sou2o()+103 >opimai_real()+133 >
ssthrdmain()+265 >main()+201 >
__libc_start_main() >+230
TIP：
同样还是上面的表，错误原因同样是上面的BUG 19689979.
ora-600 [ktecgsc:objdchk_kcbgcur_3]
ORA-00600: internal error code, arguments: [ktecgsc:objdchk_kcbgcur_3], [615688], [4], [8192], [0], [], [], [], [], [], [], [] BH (0x131eee7098) file#: 287 rdba: 0x47c03289 (287/12937) class: 4 ba: 0x130da54000 *** 2018-10-25 17:32:07.876 set: 184 pool: 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 7 obj: 683808 objn: 615570 tsn: 61 afn: 287 hint: f hash: [0xb3f30a470,0x1f46c6acb0] lru: [0x115f58c448,0x163f2a8b08] ckptq: [NULL] fileq: [NULL] objq: [0x1d9cbb6a10,0x1d9cbb6a10] objaq: [0x1d9cbb6a00,0x1d9cbb6a00] use: [0x20c7324e68,0x20c7324e68] wait: [0x1f6a5c8f90,0x1f47d5c120] st: SCURRENT md: SHR fpin: 'ktewh25: kteinicnt' tch: 0 le: 0xcff9b72e8 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] Dump of buffer cache at level 10 for tsn=61 rdba=1203778185 BH (0x131eee7098) file#: 287 rdba: 0x47c03289 (287/12937) class: 4 ba: 0x130da54000 set: 184 pool: 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 7 obj: 683808 objn: 615570 tsn: 61 afn: 287 hint: f hash: [0xb3f30a470,0x1f46c6acb0] lru: [0x115f58c448,0x163f2a8b08] ckptq: [NULL] fileq: [NULL] objq: [0x1d9cbb6a10,0x1d9cbb6a10] objaq: [0x1d9cbb6a00,0x1d9cbb6a00] use: [0x20c7324e68,0x20c7324e68] wait: [0x1f6a5c8f90,0x1f47d5c120] st: SCURRENT md: SHR fpin: 'ktewh25: kteinicnt' tch: 0 le: 0xcff9b72e8 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] Using State Objects ---------------------------------------- SO: 0x20c7324de8, type: 38, owner: 0x1ba1cadec0, flag: INIT/-/-/0x80 if: 0x1 c: 0x1 proc=0x1fc2212fa8, name=buffer handle, file=kcb2.h LINE:2761, pg=0 (buffer) (CR) PR: 0x1fc2212fa8 FLG: 0x2000 lock rls: (nil), class bit: 0x0 cr[0]: sh[0]: kcbbfbp: [BH: 0x131eee7098, LINK: 0x20c7324e68] type: normal pin where: ktewh25: kteinicnt, why: 0 Waiting State Objects ---------------------------------------- SO: 0x1f6a5c8f10, type: 38, owner: 0x1ba1dfd318, flag: INIT/-/-/0xc0 if: 0x1 c: 0x1 proc=0x1f6222fb80, name=buffer handle, file=kcb2.h LINE:2761, pg=0 (buffer) PR: 0x1f6222fb80 FLG: 0x0 lock rls: 0x131eee7098, class bit: 0x0 cr[0]: sh[0]: kcbbfbp: [BH: 0x131eee7098, LINK: 0x1f6a5c8f90] (WAITING) type: normal pin where: ktsphwh39: ktspisc, why: 0 ---------------------------------------- *** 2018-10-25 17:31:58.116 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=4nwnys17ztz5x) ----- insert into loc_ANBOB_varlog(oid,variableid,sqlbandval,runtime,intime) values (seq_oid.nextval,:variableid,:sqlbandval,:runtime,sysdate) ----- Call Stack Trace ----- skdstdst()+41 >ksedst1()+103 > ksedst()+39 >dbkedDefDump()+2746 > ksedmp()+41 >ksfdmp()+69 > dbgexPhaseII()+1764 >dbgeEndDDEInvocationImpl() > dbgeEndDDEInvocation() >kcbgcur()+15684 > ktecgsc()+78 >ktecgetsh()+182 > ktecgshx()+31 >ktspisc()+1154 > ktspgsp_main()+851 >kdtgsp()+3214 > kdtgsph()+2253 >kdtgrs()+451 > kdtInsRow()+811 >qerltcNoKdtBuffered > InsRowCBK()+297 >qerltcSingleRowLoad > >qerltcFetch()+379 > insexe()+691 > opiexe()+5632 >kpoal8()+2118 > opiodr()+917 >ttcpip()+2183 > opitsk()+1710 >opiino()+969 > opiodr()+917 >opidrv()+570 > sou2o()+103 >opimai_real()+133 > ssthrdmain()+265 >main()+201 > __libc_start_main() >+230 > _start()+41 >
Note:
同样是一个表，看来当时该表进行了多次尝试，应该是Bug 18259031
The above ORA-600 errors can occur during a DROP TABLE statement when select queries are executed concurrently.
ora 600 [kgltti-no-dep1]
这个错误是我在尝试应用不修改代码表名前提下，是否能通过同义词解决，结果还是有BUG.
SQL> alter table ANBOB.LOC_ANBOB_VARLOG rename to LOC_ANBOB_VARLOG_link; Table altered. SQL> create or replace synonym ANBOB.LOC_ANBOB_VARLOG for ANBOB.LOC_ANBOB_VARLOG_link; Synonym created. SQL> grant select on ANBOB.LOC_ANBOB_VARLOG to WEEJAR; Grant succeeded. ORA-00600: internal error code, arguments: [kgltti-no-dep1], [], [], [], [], [], [], [], [], [], [], [] ========= Dump for incident 600822 (ORA 600 [kgltti-no-dep1]) ======== ----- Beginning of Customized Incident Dump(s) ----- LibraryHandle: Address=0x201d279da0 Hash=e80a0ae9 LockMode=S PinMode=S LoadLockMode=X Status=VALD ObjectName: Name=ANBOB.LOC_ANBOB_VARLOG 32 FullHashValue=d467058dffece0e20735682be80a0ae9 Namespace=TABLE/PROCEDURE(01) Type=SYNONYM(05) Identifier=683913 OwnerIdn=68 Statistics: InvalidationCount=1 ExecutionCount=0 LoadCount=12 ActiveLocks=1 TotalLockCount=13 TotalPinCount=12 Counters: BrokenCount=46 RevocablePointer=16 KeepDependency=0 Version=34 BucketInUse=82 HandleInUse=82 HandleReferenceCount=0 Concurrency: DependencyMutex=0x201d279e50(0, 0, 0, 0) Mutex=0x201d279ee0(2559, 370, 0, 6) Flags=PIN/TIM/HOC/[02002801] ... LibraryHandle: Address=0x1ff6d8c678 Hash=5a76f26 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=ANBOB.LOC_ANBOB_VARLOG FullHashValue=0e71e3ae3d88e7a084eb290d05a76f26 Namespace=TABLE/PROCEDURE(01) Type=SYNONYM(05) Identifier=683913 OwnerIdn=68 Statistics: InvalidationCount=1 ExecutionCount=0 LoadCount=14 ActiveLocks=0 TotalLockCount=89 TotalPinCount=101 Counters: BrokenCount=80 RevocablePointer=18 KeepDependency=0 Version=57 BucketInUse=539 HandleInUse=539 HandleReferenceCount=0 Concurrency: DependencyMutex=0x1ff6d8c728(0, 331, 0, 0) Mutex=0x1ff6d8c7b8(2559, 2319, 0, 6) Flags=PIN/TIM/HOT/[00002c01] WaitersLists: Lock=0x1ff6d8c708[0x1ff6d8c708,0x1ff6d8c708] Pin=0x1ff6d8c6e8[0x1ff6d8c6e8,0x1ff6d8c6e8] LoadLock=0x1ff6d8c760[0x1ff6d8c760,0x1ff6d8c760] ----- End of Customized Incident Dump(s) ----- *** 2018-10-26 08:41:31.569 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=4mdfyhzt1fg66) ----- select count(*) from ANBOB.loc_ANBOB_varlog ----- Call Stack Trace ----- calling call entry location type point -------------------- -------- -------------------- skdstdst()+41 >ksedst1()+103 > ksedst()+39 >dbkedDefDump()+2746 > ksedmp()+41 >ksfdmp()+69 > dbgexPhaseII()+1764 >dbgexExplicitEndInc > dbgeEndDDEInvocationImpl() >dbgeEndDDEInvocation() > kgltti()+832 >kqlCompileSynonym() > kqllod()+1236 >kglobld()+1251 >kglobpn()+2564 > kglpim()+450 >kglpin()+1765 > kglgob()+381 >qcdlgbo()+525 > qcdlgob()+901 >qcsfgob()+241 > qcsprfro()+620 >qcsprfro_tree()+325 > qcsprfro_tree()+394 >qcspafq()+255 > qcspqbDescendents() >qcspqb()+438 >kkmdrv()+182 > opiSem()+1996 >opiDeferredSem()+40 > opitca()+250 >kksFullTypeCheck()+ > rpiswu2()+1776 >kksLoadChild()+1142 > kxsGetRuntimeLock() > kksfbc()+15108 >
TIP:
通过同义词grant的sql不在报错，但是查询报错了， 又是一个新的BUG
Bug 19620099 – ORA-600 [KGLTTI-NO-DEP1] WHEN USING HOTCOPY ON A SYNONYM.
解决方法 重启实例好像可以临时规避该错误
The bug has been fixed in 12.2
— or —
apply on-off Patch 19620099
SUMMARY:
前期因为优化mutex on x 争用，使用了DBMS_SHARED_POOL.MARKHOT, 结果后来truncate，drop ,re-create等都出因BUG出错，如果使用markhot的优化时当前版本需要注意。当遇到8103或坏块类错误时需要使用validate structure \rman\dbv验证是否真的发现corrupted block, 注意validate structure会加x LOCK影响业务, 如果使用online 选项可能会有检查项缺失。
–查看对象是否标识为HOT
SQL> select kglnahsv,KGLNAOBJ,KGLHDNSP,kglobprop from x$kglob where KGLNAOBJ='PROPS$'; KGLNAHSV KGLNAOBJ KGLHDNSP KGLOBPROP -------------------------------- ------------------------------ ---------- ------------------------------- 555672211a52f68448f2220b58cba2b0 PROPS$ 1 HOT
— 标识对象为HOT
SQL> select kglnahsv,KGLNAOBJ,KGLHDNSP from x$kglob where KGLNAOBJ='PROPS$'; KGLNAHSV KGLNAOBJ KGLHDNSP -------------------------------- ---------------------- ---------------- 555672211a52f68448f2220b58cba2b0 PROPS$ 1 SQL> exec DBMS_SHARED_POOL.MARKHOT(hash => '555672211a52f68448f2220b58cba2b0', namespace => 1);
— 标识对象为非HOT
SQL> exec DBMS_SHARED_POOL.UNMARKHOT(hash => '883830cc853d0f37585f7bd06e738665', namespace => 0);
以上所述就是小编给大家介绍的《Troubleshooting ora-600[ktecgsc:kcbz_objdchk]&ora-600[qesmaGetTblSeg1] when inserting and o...》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
RGB转16进制工具
RGB HEX 互转工具
图片转BASE64编码
在线图片转Base64编码工具