--------------alert告警日志报错信息--------------
Dumping diagnostic data in directory=[cdmp_20210421040755], requested by (instance=3, osid=381138), summary=[incident=86135].
Wed Apr 21 04:51:28 2021
Thread 1 advanced to log sequence 66714 (LGWR switch)
Current log# 2 seq# 66714 mem# 0: +DATAC1/X6CDB1/ONLINELOG/group_2.336.929027285
Wed Apr 21 04:51:56 2021
Archived Log entry 267863 added for thread 1 sequence 66713 ID 0xe3f1c691 dest 1:
Wed Apr 21 04:59:55 2021
Hex dump of (file 1519, block 2537666) in trace file /u01/app/oracle/diag/rdbms/x6cdb1/X6CDB11/trace/X6CDB11_ora_343765.trc
Corrupt block relative dba: 0x7c26b8c2 (file 1519, block 2537666)
Fractured block found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x7c26b8c2
last change scn: 0x0fcf.f055a3da seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x60f3257a
check value in block header: 0x54a2
computed block checksum: 0xbcee
Reading datafile ‘+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf’ for corruption at rdba: 0x7c26b8c2 (file 1519, block 2537666)
Read datafile mirror ‘DATAC1_CD_04_X601CELADM10’ (file 1519, block 2537666) found same corrupt data (no logical check)
Read datafile mirror ‘DATAC1_CD_02_X601CELADM09’ (file 1519, block 2537666) found same corrupt data (no logical check)
Read datafile mirror ‘DATAC1_CD_00_X601CELADM08’ (file 1519, block 2537666) found same corrupt data (no logical check)
Wed Apr 21 04:59:55 2021
Corrupt Block Found
CONT = 14, TSN = 162, TSNAME = QMCB_441900_DATA
RFN = 496, BLK = 2537666, RDBA = 2082912450
OBJN = 182596, OBJD = 347212, OBJECT = INX_QMCB_AC43_210, SUBOBJECT =
SEGMENT OWNER = QMCB_441900, SEGMENT TYPE = Index Segment
Errors in file /u01/app/oracle/diag/rdbms/x6cdb1/X6CDB11/trace/X6CDB11_ora_343765.trc (incident=153738) (PDBNAME=QMCB):
ORA-01578: ORACLE data block corrupted (file # 1519, block # 2537666)
ORA-01110: data file 1519: ‘+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf’
Incident details in: /u01/app/oracle/diag/rdbms/x6cdb1/X6CDB11/incident/incdir_153738/X6CDB11_ora_343765_i153738.trc
Wed Apr 21 05:07:42 2021
Sweep [inc][153738]: completed
Wed Apr 21 05:08:39 2021
Dumping diagnostic data in directory=[cdmp_20210421050839], requested by (instance=1, osid=343765), summary=[incident=153738].
Wed Apr 21 05:08:39 2021
Hex dump of (file 1519, block 2537666) in trace file /u01/app/oracle/diag/rdbms/x6cdb1/X6CDB11/incident/incdir_153738/X6CDB11_m000_209140_i153738_a.trc
Corrupt block relative dba: 0x7c26b8c2 (file 1519, block 2537666)
Fractured block found during validation
Data in bad block:
type: 6 format: 2 rdba: 0x7c26b8c2
last change scn: 0x0fcf.f055a3da seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x60f3257a
check value in block header: 0x54a2
computed block checksum: 0xbcee
Trying mirror side DATAC1_CD_04_X601CELADM10.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_02_X601CELADM09.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_00_X601CELADM08.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_04_X601CELADM10.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_02_X601CELADM09.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_00_X601CELADM08.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_04_X601CELADM10.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_02_X601CELADM09.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_00_X601CELADM08.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_04_X601CELADM10.
Reread of blocknum=2537666, file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf. found same corrupt data
Trying mirror side DATAC1_CD_02_X601CELADM09.
---------------------------------检查整个库---------------------------------
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
3 88705 1 331202991 CORRUPT 0
3 88707 1 331202994 CORRUPT 0
3 88833 1 331203056 CORRUPT 0
3 88835 2 331203061 CORRUPT 0
3 88838 5 331203076 CORRUPT 0
3 88844 5 331203099 CORRUPT 0
3 88850 3 331203112 CORRUPT 0
84 3431972 1 0 CORRUPT 0
481 1115244 4 0 CORRUPT 0
1549 3158142 2 0 CORRUPT 0
1519 2537666 1 0 FRACTURED 0
11 rows selected.
SQL> blockrecover datafile 1519 block 2537666; --有备份的情况下rman修复坏块命令
查询所有存在坏块的数据文件(每个数据文件中可能存在有多个坏块)
SQL> set lin 200
SQL> col name for a80
SQL> col pwd for a110
SQL> select file#,‘dbv file=’||name||’ blocksize=8192’ pwd from v$datafile where file# in (‘3’,‘84’,‘481’,‘1549’,‘1519’);
查询坏块在哪个PDB中？
SQL> set lin 200
SQL> col file_name for a100
SQL> col pdb_name for a8
SQL> select a.CON_ID,a.NAME as pdb_name,b.name as file_name,b.FILE#,b.STATUS,b.bytes/1024/1024/1024 G from v
p
d
b
s
a
,
v
pdbs a,v
pdbsa,vdatafile b where a.CON_ID = b.CON_ID and b.file#=‘1519’;
CON_ID PDB_NAME FILE_NAME FILE# STATUS G
14 QMCB +DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf 1519 onLINE 30
SQL>
SQL> set lin 200
SQL> col FILE_NAME for a85
SQL> col AUTOEXTENSIBLE for a20
SQL> select FILE_ID,TABLESPACE_NAME,BYTES/1024/1024,FILE_NAME,autoextensible from dba_data_files where FILE_NAME like ‘%995297097%’;
---------------------------------dbv检查---------------------------------
[oracle@x601db01 trace]$ dbv file=+DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf blocksize=8192
DBVERIFY: Release 12.1.0.2.0 - Production on Wed Apr 21 14:42:01 2021
Copyright © 1982, 2014, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +DATAC1/X6CDB1/DATAFILE/qmcb_441900_data_02.dbf
Page 2537666 is marked corrupt
Corrupt block relative dba: 0x7c26b8c2 (file 496, block 2537666)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x7c000001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xc3f5
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 1227219
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2700022
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4917
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
[oracle@x601db01 trace]$
-----------------查看坏块的对象类型，如果是索引，那么删除重建即可-----------------
语句: SELECt tablespace_name,segment_type,owner,segment_name,PARTITION_NAME FROM dba_extents WHERe file_id = &fileid and &blockid between block_id AND block_id + blocks - 1;
SQL> set lin 200
SQL> col owner for a25
SQL> col segment_name for a40
SQL> col PARTITION_NAME for a40
SQL> SELECt tablespace_name,segment_type,owner,segment_name,PARTITION_NAME FROM dba_extents WHERe file_id = 1519 and 2537666 between block_id AND block_id+blocks-1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
QMCB_441900_DATA INDEX QMCB_441900 INX_QMCB_AC43_210
SQL> create index QMCB_441900.INX_QMCB_AC43_210 on QMCB_441900.QMCB_AC43_210(QAB301,AAZ223) parallel 4 online tablespace QMCB_441900_DATA;
SQL> select owner,INDEX_NAME,TABLE_NAME from dba_indexes where INDEX_NAME=‘INX_QMCB_AC43_210’ and owner=‘QMCB_441900’;
