验证数据库和备份是否存在损坏（VALIDATE）

命令

# Physical + logical block check (without producing a backup):
VALIDATE CHECK LOGICAL DATABASE;
# Scan large datafiles in parallel (11g+, needs multiple channels):
VALIDATE CHECK LOGICAL DATABASE SECTION SIZE 16G;
# A single datafile / tablespace:
VALIDATE DATAFILE 7;
VALIDATE TABLESPACE users;
# Read as if taking a backup and check (including archivelogs):
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
# Readability of a specific backup set:
LIST BACKUP SUMMARY;
VALIDATE BACKUPSET <bs_key>;
-- Corrupt blocks found and the affected segment (SQL):
SELECT file#, block#, blocks, corruption_change#, corruption_type
FROM   v$database_block_corruption ORDER BY file#, block#;
SELECT DISTINCT e.owner, e.segment_name, e.segment_type
FROM   dba_extents e
JOIN   v$database_block_corruption c
  ON   e.file_id = c.file#
 AND   c.block# BETWEEN e.block_id AND e.block_id + e.blocks - 1;

作用

读取数据文件和备份集以检测物理块损坏以及（使用 CHECK LOGICAL 时）逻辑块损坏，并将结果记录到 v$database_block_corruption。SQL 查询显示损坏块属于哪个表/索引。

适用场景

出现 ORA-01578/ORA-08103 等损坏错误后、存储问题后，以及作为常规（每周）完整性检查；用于证明备份确实可恢复。

注意

它会执行全量读取；在大型数据库上会产生大量 I/O，请在高峰时段之外运行。dba_extents 查询在大型数据库上可能很慢。如果损坏块位于临时（TEMP）空间或空闲空间中，则不会返回匹配的对象。