12.1版本dg切换导致的部分数据块scn超前

定位对象

根据alert日志的ora600报错trc

[oracle@db01dbadm01 trace]$ vi /u01/app/oracle/diag/rdbms/slyy/slyy1/trace/slyy1_ora_336476.trc

根据报错trc找到incdir的trc，找到具体sql

[oracle@db01dbadm01 trace]$ vi /u01/app/oracle/diag/rdbms/slyy/slyy1/incident/incdir_8091949/slyy1_ora_336476_i8091949.trc

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", TABLE, "P#0", ROWS=38799.805) OPT_ESTIMATE(@"innerQuery", INDEX_FILTER, "P#0", "IDX_DISP_SICK_PRICE_ITEM_TIME", ROWS=67623) OPT_ESTIMATE(@"innerQuery", INDEX_SCAN, "P#0", "IDX_DISP_SICK_PRICE_ITEM_TIME", ROWS=67623) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "P#0") */ 1 AS C1 FROM "OUTPSICK"."DISPENSARY_SICK_PRICE_ITEM" SAMPLE BLOCK(0.141753, 8) SEED(3) "P#0", ( (SELECT "A"."APPLY_NO" "APPLY_NO","A"."SICK_ID" "SICK_ID","A"."NULLAH_NUMBER" "NULLAH_NUMBER","A"."APPLY_CLASS_CODE" "APPLY_CLASS_CODE","A"."ITEM_NAME" "ITEM_NAME","A"."RESULT_STATUS" "RESULT_STATUS","A"."APPLY_TIMES" "APPLY_TIMES","A"."ITEM_CODE" "ITEM_CODE" FROM COMM."APPLY_SHEET_POOL" "A",COMM."SICK_BASIC_INFO" "C","DICT"."BILL_KIND_DICT" "E" WHERE "A"."SICK_ID"="C"."SICK_ID" AND "A"."APPLY_CLASS_CODE"="E"."BILL_CODE" AND ("A"."RESULT_STATUS"=:B1 OR "A"."RESULT_STATUS"=:B2 OR "A"."RESULT_STATUS"=:B3 OR "A"."RESULT_STATUS"=:B4 OR "A"."RESULT_STATUS"=:B5) AND "A"."SICK_ID"=:B6 AND "C"."SICK_ID"=:B6 AND "E"."BILL_CODE"<>:B8 AND "A"."APPLY_CLASS_CODE"<>:B8) UNION ALL (SELECT "B"."APPLY_NO" "APPLY_NO","B"."SICK_ID" "SICK_ID","B"."NULLAH_NUMBER" "NULLAH_NUMBER","B"."APPLY_CLASS_CODE" "APPLY_CLASS_CODE","B"."ITEM_NAME" "ITEM_NAME","B"."RESULT_STATUS" "RESULT_STATUS","B"."APPLY_TIMES" "APPLY_TIMES","B"."ITEM_CODE" "ITEM_CODE" FROM COMM."APPLY_SHEET" "B",COMM."SICK_BASIC_INFO" "D","DICT"."BILL_KIND_DICT" "E" WHERE "B"."SICK_ID"="D"."SICK_ID" AND "B"."APPLY_CLASS_CODE"="E"."BILL_CODE" AND ("B"."RESULT_STATUS"=:B1 OR "B"."RESULT_STATUS"=:B2 OR "B"."RESULT_STATUS"=:B3 OR "B"."RESULT_STATUS"=:B4 OR "B"."RESULT_STATUS"=:B5) AND "B"."SICK_ID"=:B6 AND "D"."SICK_ID"=:B6 AND "E"."BILL_CODE"<>:B8 AND "B"."APPLY_CLASS_CODE"<>:B8)) "A#1" WHERE ("A#1"."NULLAH_NUMBER"="P#0"."RESIDENCE_NO") AND ("A#1"."APPLY_NO"="P#0"."APPLY_NO") AND ("P#0"."COST_MODE"=:B19) AND ("P#0"."ARCHIVE" IS NULL) AND ("P#0"."OPERATION_TIME">=TRUNC(SYSDATE@!)) AND ("P#0"."OPERATION_TIME"<TRUNC(SYSDATE@!)+:B20)) innerQuery

从sql的hint部分，找到使用的索引

IDX_DISP_SICK_PRICE_ITEM_TIME

再次确认

因为ora600报错很宽泛，为了定位是索引块超前，需要使用dbv工具确认

SQL> alter session set container=SLYYHIS;

Session altered.

Elapsed: 00:00:00.01

SQL> select index_name,tablespace_name from dba_indexes where index_name='IDX_DISP_SICK_PRICE_ITEM_TIME';

INDEX_NAME TABLESPACE_NAME

-------------------------------------------------------------------------------------------------------------------------------- -------------------------

IDX_DISP_SICK_PRICE_ITEM_TIME OUTPSICK_PRICE_LONG_IDX

Elapsed: 00:00:00.31

找到该索引的表空间

SQL> select RELATIVE_FNO,file_name from dba_data_files where tablespace_name='OUTPSICK_PRICE_LONG_IDX';

RELATIVE_FNO

------------

FILE_NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+DATAC1/SLYY/3AF713CA266E9482E05301D2140A9EF8/DATAFILE/outpsick_price_long_idx01.ora

Elapsed: 00:00:00.03

找到该表空间的数据文件

Dbv检查数据文件

[oracle@db01dbadm01 trace]$ dbv file=+DATAC1/SLYY/3AF713CA266E9482E05301D2140A9EF8/DATAFILE/outpsick_price_long_idx01.ora

DBVERIFY: Release 12.1.0.2.0 - Production on Tue Nov 3 18:46:19 2020

DBVERIFY - Verification starting : FILE = +DATAC1/SLYY/3AF713CA266E9482E05301D2140A9EF8/DATAFILE/outpsick_price_long_idx01.ora

itl[2] has higher commit scn(0x0ddf.9ed48224) than block scn (0x0ddf.5fe10989)

Page 1705413 failed with check code 6056

DBVERIFY - Verification complete

Total Pages Examined : 2621440

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 1907697

Total Pages Failing (Index): 1

Total Pages Processed (Other): 4406

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 709337

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 0 (0.0)

根据提示的page号最后定位对象

SQL> select * from dba_extents where relative_fno=96 and 1705413 between block_id and block_id+blocks;

OWNER

--------------------------------------------------------------------------------------------------------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------------------------------------------------------

PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID

-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------------- ---------- ----------

BLOCK_ID BYTES BLOCKS RELATIVE_FNO

---------- ---------- ---------- ------------

OUTPSICK

IDX_DISP_SICK_PRICE_ITEM_AN

INDEX OUTPSICK_PRICE_LONG_IDX 157 96

1704448 67108864 8192 96

Elapsed: 00:00:04.65

SQL>