alert日志报错信息：
[oracle@hd04 trace]$ cat alert_db12c.log |grep "corrupt" -A 2 -B 2|more
Hex dump of (file 11, block 704337) in trace file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_p006_20208.trc
Reading datafile '/app/oracle/oradata/orcl/db12cpdb/undotbs01.dbf' for corruption at rdba: 0x024abf51 (file 11, block 704337)
2019-08-22T15:30:39.594783+08:00
Hex dump of (file 3, block 97921) in trace file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_p004_20204.trc
Reading datafile '/app/oracle/oradata/orcl/sysaux01.dbf' for corruption at rdba: 0x00c17e81 (file 3, block 97921)
Reread (file 3, block 97921) found same corrupt data (logically corrupt)
2019-08-22T15:30:39.614714+08:00
Reread (file 11, block 704337) found same corrupt data (logically corrupt)
2019-08-22T15:30:39.637609+08:00
*****************************************************************
--
2019-08-22T15:30:48.232309+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_p003_20202.trc (incident=146194) (PDBNAME=CDB$ROOT):
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-10564: tablespace SYSAUX
--
2019-08-22T15:30:49.761279+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_p003_20202.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-10564: tablespace SYSAUX
--
2019-08-22T15:30:54.735880+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_ora_20194.trc (incident=146162) (PDBNAME=CDB$ROOT):
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-10564: tablespace SYSAUX
--
2019-08-22T15:30:56.296743+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_ora_20194.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-10564: tablespace SYSAUX
--
Hex dump of (file 3, block 97921) in trace file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_ora_20194.trc
Reading datafile '/app/oracle/oradata/orcl/sysaux01.dbf' for corruption at rdba: 0x00c17e81 (file 3, block 97921)
Reread (file 3, block 97921) found same corrupt data (logically corrupt)
*****************************************************************
An internal routine has requested a dump of selected redo.
--
2019-08-22T15:35:00.962427+08:00
Completed redo application of 0.96MB
Reading datafile '/app/oracle/oradata/orcl/sysaux01.dbf' for corruption at rdba: 0x00c17e81 (file 3, block 97921)
Reread (file 3, block 97921) found same corrupt data (logically corrupt)
*****************************************************************
1.查询Oracle坏块方法
set lines 120
select * from v$database_block_corruption;
[oracle@hd04 trace]$ sqlplus / as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 18 14:45:29 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
3 60630 1 115606079 CORRUPT 1
SQL>2.分析Oracle坏块对应的对象内容
执行如下命令：
set pagesize 2000
set linesize 250
col OWNER for a15
col SEGMENT_NAME for a25
col PARTITION_NAME for a25
SELECT e.owner,
e.segment_type,
e.segment_name,
e.partition_name,
c.file#,
greatest(e.block_id, c.block#) corr_start_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
greatest(e.block_id, c.block#) + 1 blocks_corrupted,
null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner,
s.segment_type,
s.segment_name,
s.partition_name,
c.file#,
header_block corr_start_block#,
header_block corr_end_block#,
1 blocks_corrupted,
'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner,
null segment_type,
null segment_name,
null partition_name,
c.file#,
greatest(f.block_id, c.block#) corr_start_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
greatest(f.block_id, c.block#) + 1 blocks_corrupted,
'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;结果：
3.查看创建索引语句。
查看创建索引语句：
set linesize 200
set pagesize 200
set long 999999
select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX') from dual;?
?4.重建索引
mark一下，不建议使用rebuild online选项。
alter index "SYS"."SMON_SCN_TIME_TIM_IDX" rebuild ;
analyze index "SYS"."SMON_SCN_TIME_TIM_IDX" validate structure;
select * from v$database_block_corruption;
?
5.再次查看alert 日志，不再报错。
2021-09-18T14:59:56.023510+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_smon_17951.trc (incident=587538) (PDBNAME=CDB$ROOT):
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
2021-09-18T14:59:56.031705+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_smon_17951.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
2021-09-18T15:04:29.724393+08:00
--
2021-09-18T15:05:06.491927+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_smon_17951.trc (incident=587539) (PDBNAME=CDB$ROOT):
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
2021-09-18T15:05:06.495228+08:00
Errors in file /app/oracle/diag/rdbms/orcl/db12c/trace/db12c_smon_17951.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 3, block # 60630)
ORA-01110: data file 3: '/app/oracle/oradata/orcl/sysaux01.dbf'
2021-09-18T15:05:28.860120+08:00
[oracle@hd04 trace]$ date
Sat Sep 18 15:24:34 CST 2021
[oracle@hd04 trace]$