首页 » Cloud, ORACLE 9i-23ai » Oracle 12c Alert log show ” ADVISORY: Please collect redo for investigation of ORA-8103″ frequently

Oracle 12c Alert log show ” ADVISORY: Please collect redo for investigation of ORA-8103″ frequently

  • 2020/05/21
  • Cloud, ORACLE 9i-23ai
  • 1,336 views
  • Oracle 12c Alert log show ” ADVISORY: Please collect redo for investigation of ORA-8103″ frequently已关闭评论

叕一个oracle 12c频繁生成日志文件的问题，最近一套12.2的RAC突然又文件系统告警，生成了大量的trace文件，db alert log也在不停的显示如下内容。该表分区较多，前一晚有做该表move操作。

# alert log

ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042430 dba min 32 2094058 dba max 32 2094058;
2020-05-20T13:36:19.202533+08:00
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042540 dba min 32 2093731 dba max 32 2093731;
2020-05-20T13:36:19.254255+08:00
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042621 dba min 32 2093862 dba max 32 2093862;
2020-05-20T13:36:19.368628+08:00
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042798 dba min 32 2093822 dba max 32 2093822;
2020-05-20T13:36:19.478326+08:00
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042961 dba min 32 2093976 dba max 32 2093976;
2020-05-20T13:36:19.534631+08:00
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
   ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635043070 dba min 32 2093944 dba max 32 2093944;
2020-05-20T13:36:19.584696+08:00

# trace file contents

*** SESSION ID:(1994.64263) 2020-05-20T11:47:30.893213+08:00
OBJD MISMATCH typ=6, seg.obj=286445, diskobj=286437, dsflg=100000, dsobj=286445, tid=286445, cls=1
kcbz_exec_ckf: check function 0x10b053f0 returned error=8103:1
Suspect buffer header:
BH (0xb9e386738) file#: 29 rdba: 0x07600b64 (29/2100068) class: 1 ba: 0xb93f1a000
set: 161 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,25
dbwrid: 0 obj: 286445 objn: 286445 tsn: [0/18] afn: 29 hint: f
hash: [0x1ede6dc30,0x1ede6dc30] lru: [0xbfe2bf630,0x69e3717d0]
ckptq: [NULL] fileq: [NULL]
objq: [0xafe4d5b38,0xc24f76b0] objaq: [0xafe4d5b48,0xc24f76a0]
use: [0x20cb44d20,0x20cb44d20] wait: [NULL]
st: READING md: EXCL tch: 0 le: 0x5bef2df68
flags: only_sequential_access
Printing buffer operation history (latest change first):
cnt: 4
01. sid:10 L122:zgb:set:st 02. sid:10 L830:olq1:clr:WRT+CKT
03. sid:10 L951:zgb:lnk:objq 04. sid:10 L372:zgb:set:MEXCL
05. sid:10 L123:zgb:no:FEN 06. sid:10 L896:z_mkfr:ulnk:objq
07. sid:10 L083:zgb:ent:fn 08. sid:10 L836:z_age_hot:mv:cld
09. sid:01 L192:kcbbic2:bic:FBD 10. sid:01 L191:kcbbic2:bic:FBW
11. sid:01 L822:bic1_int:ent:rtn 12. sid:01 L832:oswmqbg1:clr:WRT
13. sid:01 L930:kubc:sw:mq 14. sid:01 L913:bxsv:sw:objq
15. sid:01 L608:bxsv:bis:FBW 16. sid:01 L607:bxsv:bis:FFW
buffer tsn: 18 rdba: 0x07600b64 (29/2100068)

Block header dump: 0x07600b64
Object id on Block? Y
seg/obj: 0x45ee5 csc: 0x00000ef425897404 itc: 40 flg: E typ: 1 - DATA
brn: 0 bdba: 0x7600a83 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0230.006.00002453 0x00c34b45.0c15.20 C--- 0 scn 0x00000ef425895b78
0x02 0x01fd.002.00003d1e 0x2301879f.1322.16 C--- 0 scn 0x00000ef425896acf
0x03 0x00a2.006.000055fe 0x2301855a.30b6.37 --U- 5 fsc 0x0000.2589744a
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x10 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x11 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x12 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x13 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x14 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x15 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x16 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x17 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x18 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x19 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x1f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x20 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x21 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x22 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x23 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x24 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x25 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x26 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x27 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x28 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x07600b64
data_block_dump,data header at 0xb93f1a3f4
===============
tsiz: 0x1c08
hsiz: 0x26
pbl: 0xb93f1a3f4
76543210
flag=--------
ntab=1
nrow=10
frre=-1
fsbo=0x26
fseo=0x71e
avsp=0x6f8
tosp=0x6f8
0xe:pti[0] nrow=10 offs=0
0x12:pri[0] offs=0x14a8
0x14:pri[1] offs=0x19e0
0x16:pri[2] offs=0x1a32
0x18:pri[3] offs=0x1b17
0x1a:pri[4] offs=0x10a1
0x1c:pri[5] offs=0x71e
0x1e:pri[6] offs=0xbb9
0x20:pri[7] offs=0xe1a
0x22:pri[8] offs=0xe80
0x24:pri[9] offs=0xf8d
block_row_dump:

Dumping Short Stack
ksedsts()+346<-kcbz_exec_ckf_debug()+1422<-kcbz_check_objd_typ()+1756<-kcbzib()+3884<-kcbgtcr()+11492<-ktrget2()+1056<-kdsgrp()+527<-qetlbr()+835<-qertbFetchByRowID()+1216<-qergiFetch()+567<-qersoProcessULS()+300<-qersoFetchSimple()+1433<-qersoFetch()+210<-opifch2()+3267
<-kpoal8()+3490<-opiodr()+1229<-ttcpip()+1257<-opitsk()+1940<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+245Cursor valid: 1
Dumping kcb descriptor and buffer header(s):
kcbds 0x7fc8aa04cec0: pdb 0, tsn 18, rdba 0x081fe6ac, afn 32, objd 286445, cls 1, tidflg 0xc8 0x80 0x0
dsflg 0x100000, dsflg2 0x4000, lobid 0x0:0, cnt 0, addr 0x682a68014, exf 0x10b02510, dx 0x0, ctx 0
whr: 'kdswh05: kdsgrp'
env [0x7fc8aa04e95c]: (scn: 0x00000f3421175bae xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000000000000000 hi-scn: 0x0000000000000000 ma-scn: 0x00000f342117338c flg: 0x00000660)
Dump of buffer cache at level 8 for pdb=0 tsn=18 rdba=136308396

查看对象

SQL> @oid 286445,286437
owner                     object_name                    object_type        SUBOBJECT_NAME       CREATED           LAST_DDL_TIME     status    DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ -------------------- ----------------- ----------------- --------- --------------
ANBOB                      CS_REC_TABXXX                 TABLE PARTITION    PART_336_20190602    20190523 23:42:03 20190523 23:42:03 VALID             286445

SQL> @doid 286445,286437
owner                     object_name                    O_PARTITION                                                                                                                      object_type        CREATED           LAST_DDL_TIME     status    DATA_OBJECT_ID
------------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------ ----------------- ----------------- --------- --------------
ANBOB                      CS_REC_TABXXX                 PART_336_20190602                                                                                                                TABLE PARTITION    20190523 23:42:03 20190523 23:42:03 VALID             286445

SQL> select /*+full(t)*/count(*) from ANBOB.CS_REC_TABXXX PARTITION();
--无报错

SQL> @hex 45ee5
                               DEC                  HEX
----------------------------------- --------------------
                      286437.000000                45EE5

刷新内存，无法解决

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH Shared_pool;

验证数据文件

SQL> select block_size,name,ts# from v$datafile where file#=29

BLOCK_SIZE NAME                                                                                    TS#
---------- -------------------------------------------------------------------------------- ----------
      8192 +DATADG/ORDER/DATAFILE/datacommon.304.992108133                                          18

oracle@ANBOB.COM:/home/oracle> dbv userid=system/xxxxxxxx blocksize=8192 file='+DATADG/ORDER/DATAFILE/datacommon.304.992108133'
DBVERIFY: Release 12.2.0.1.0 - Production on Wed May 20 14:01:17 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATADG/ORDER/DATAFILE/datacommon.304.992108133
DBVERIFY - Verification complete
Total Pages Examined         : 3932032
Total Pages Processed (Data) : 3516720
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 193799
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 28892
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 192621
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 622502163 (0.622502163)

重启实例应该也可以解决，但是不允许。MOS 中与Bug 28844866 较匹配，该BUG在12c，18c， 19c中都存在。

尝试使用12c online move table partition解决。

SQL> alter table ANBOB.CS_REC_TABXXX move partition PART_336_20190602 online  tablespace xxx;
Table altered.

注意如果做以上操作在高并发业务时还是会出现短时的library cache lock.

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：