本文档风哥主要介绍Oracle数据损坏检测相关知识，包括数据损坏的概念、数据损坏的常见原因、数据损坏分析方法、数据损坏检测配置、数据损坏检测监控、数据损坏检测故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 数据损坏的概念
Oracle数据损坏是指数据库中的数据块或数据文件由于硬件故障、软件错误或人为操作失误等原因导致的数据不一致或不可读状态。数据损坏可能导致查询失败、数据丢失或数据库无法启动。数据损坏检测是数据库管理的重要任务，需要定期进行检测和修复。更多视频教程www.fgedu.net.cn
- 物理损坏：数据块物理结构损坏
- 逻辑损坏：数据逻辑不一致
- 数据文件损坏：数据文件头部或内容损坏
- 控制文件损坏：控制文件损坏导致数据库无法启动
- 日志文件损坏：重做日志文件损坏导致恢复失败
1.2 数据损坏的常见原因
Oracle数据损坏的常见原因：
- 硬件故障：磁盘故障、内存故障、控制器故障
- 操作系统错误：文件系统损坏、I/O错误
- Oracle软件错误：Oracle软件Bug、版本兼容性问题
- 人为操作失误：误删除文件、误操作数据
- 存储系统故障：存储阵列故障、RAID损坏
- 网络问题：网络中断导致数据传输错误
- 电源故障：突然断电导致数据写入不完整
1.3 数据损坏分析方法
Oracle数据损坏分析方法：
- DBVERIFY工具：验证数据文件完整性
- ANALYZE命令：分析表和索引结构
- RMAN工具：备份和恢复时检测损坏
- DBMS_REPAIR包：检测和修复损坏块
- 数据字典视图：查看损坏信息
- 告警日志：查看错误信息
Part02-生产环境规划与建议
2.1 数据损坏检测规划
Oracle数据损坏检测规划要点：
– 检测频率：定期进行数据损坏检测
– 检测范围：检测所有数据文件和表空间
– 检测工具：选择合适的检测工具
– 检测记录：记录检测结果
# 检测频率规划
– 每日检测：检测关键数据文件
– 每周检测：检测所有数据文件
– 每月检测：全面检测数据库
# 检测范围规划
– 数据文件：检测所有数据文件
– 表空间：检测所有表空间
– 表和索引：检测关键表和索引
– 控制文件：检测控制文件
– 日志文件：检测重做日志文件
# 检测工具规划
– DBVERIFY：验证数据文件完整性
– ANALYZE：分析表和索引结构
– RMAN：备份时检测损坏
– DBMS_REPAIR：检测和修复损坏块
# 检测记录规划
– 记录方式：选择记录方式
– 记录格式：定义记录格式
– 记录保留：设置记录保留时间
– 记录备份：备份检测记录
2.2 数据损坏检测工具
Oracle数据损坏检测工具：
- DBVERIFY：验证数据文件完整性
- ANALYZE：分析表和索引结构
- RMAN：备份和恢复时检测损坏
- DBMS_REPAIR：检测和修复损坏块
- V$DATABASE_BLOCK_CORRUPTION：损坏块视图
- DBA_EXTENTS：区信息视图
– DBVERIFY：快速验证数据文件完整性
– ANALYZE：分析表和索引结构
– RMAN：备份时自动检测损坏
– DBMS_REPAIR：检测和修复损坏块
– V$DATABASE_BLOCK_CORRUPTION：查看损坏块信息
– DBA_EXTENTS：查看区信息
2.3 数据损坏检测最佳实践
Oracle数据损坏检测最佳实践：
- 定期检测：定期进行数据损坏检测
- 及时修复：及时修复发现的损坏
- 文档记录：记录检测和修复过程
- 经验积累：积累检测和修复经验
- 预防措施：制定预防措施
Part03-生产环境项目实施方案
3.1 数据损坏检测配置
3.1.1 配置DBVERIFY工具
$ dbv file=/oracle/app/oracle/oradata/FGEDUDB/system01.dbf feedback=100
DBVERIFY: Release 19.0.0.0.0 – Production on Tue Mar 31 10:00:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/FGEDUDB/system01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 51200
Total Pages Processed (Data) : 32768
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8192
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10240
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 12345678 (0.12345678)
# 2. 验证多个数据文件
$ dbv file=/oracle/app/oracle/oradata/FGEDUDB/system01.dbf blocksize=8192 start=1 end=1000
DBVERIFY: Release 19.0.0.0.0 – Production on Tue Mar 31 10:00:00 2026
DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/FGEDUDB/system01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 1000
Total Pages Processed (Data) : 500
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 200
Total Pages Failing (Index): 0
Total Pages Processed (Other): 300
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 12345678 (0.12345678)
# 3. 验证损坏块
$ dbv file=/oracle/app/oracle/oradata/FGEDUDB/users01.dbf
DBVERIFY: Release 19.0.0.0.0 – Production on Tue Mar 31 10:00:00 2026
DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/FGEDUDB/users01.dbf
Page 1024 is marked corrupt
Corrupt block relative dba: 0x01000400, file 4, block 1024
Completely zero block found during dbv:
DBVERIFY – Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 8192
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2048
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2559
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 12345678 (0.12345678)
# 4. 使用DBVERIFY验证段
$ dbv userid=scott/tiger segment_id=4.12345
DBVERIFY: Release 19.0.0.0.0 – Production on Tue Mar 31 10:00:00 2026
DBVERIFY – Verification starting : SEGMENT_ID = 4.12345
DBVERIFY – Verification complete
Total Pages Examined : 100
Total Pages Processed (Data) : 80
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 20
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 12345678 (0.12345678)
# 5. 验证所有数据文件脚本
$ vi /home/oracle/scripts/dbv_all_datafiles.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 验证所有数据文件
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/scripts/dbv_logs”
mkdir -p $LOG_DIR
# 获取所有数据文件
sqlplus -s / as sysdba <
set heading off
set feedback off
set linesize 200
select file_name from dba_data_files;
exit
EOF
# 验证每个数据文件
while read file_name; do
file_basename=$(basename $file_name)
echo “Verifying $file_name…”
dbv file=$file_name feedback=1000 > $LOG_DIR/${file_basename}.log 2>&1
# 检查是否有损坏
if grep -q “Total Pages Marked Corrupt” $LOG_DIR/${file_basename}.log; then
corrupt_count=$(grep “Total Pages Marked Corrupt” $LOG_DIR/${file_basename}.log | awk ‘{print $NF}’)
if [ “$corrupt_count” -gt 0 ]; then
echo “CORRUPTION FOUND in $file_name: $corrupt_count corrupt pages”
echo “$(date): CORRUPTION FOUND in $file_name: $corrupt_count corrupt pages” >> $LOG_DIR/corruption_alert.log
fi
fi
done < /tmp/datafiles.txt
echo "DBVERIFY completed for all data files"
# 6. 执行验证脚本
$ chmod +x /home/oracle/scripts/dbv_all_datafiles.sh
$ /home/oracle/scripts/dbv_all_datafiles.sh
Verifying /oracle/app/oracle/oradata/FGEDUDB/system01.dbf...
Verifying /oracle/app/oracle/oradata/FGEDUDB/sysaux01.dbf...
Verifying /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf...
CORRUPTION FOUND in /oracle/app/oracle/oradata/FGEDUDB/users01.dbf: 1 corrupt pages
Verifying /oracle/app/oracle/oradata/FGEDUDB/users01.dbf...
DBVERIFY completed for all data files
3.1.2 配置ANALYZE工具
SQL> analyze table scott.emp validate structure;
Table analyzed.
# 2. 使用ANALYZE验证表和索引
SQL> analyze table scott.emp validate structure cascade;
Table analyzed.
# 3. 使用ANALYZE验证索引
SQL> analyze index scott.pk_emp validate structure;
Index analyzed.
# 4. 使用ANALYZE验证分区表
SQL> analyze table scott.fgfgfgsales validate structure into invalid_rows;
Table analyzed.
# 5. 创建INVALID_ROWS表
SQL> @?/rdbms/admin/utlvalid.sql
Table created.
# 6. 查看验证结果
SQL> select * from invalid_rows;
OWNER_NAME TABLE_NAME PARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
———- ———- ————— —————— ——————–
SCOTT EMP AAASdqAAEAAAAInAAA 2026-03-31 10:00:00
# 7. 验证所有表
SQL> begin
for rec in (select owner, table_name from dba_tables where owner = ‘SCOTT’) loop
execute immediate ‘analyze table ‘ || rec.owner || ‘.’ || rec.table_name || ‘ validate structure cascade’;
end loop;
end;
/
PL/SQL procedure successfully completed.
# 8. 验证所有索引
SQL> begin
for rec in (select owner, index_name from dba_indexes where owner = ‘SCOTT’) loop
execute immediate ‘analyze index ‘ || rec.owner || ‘.’ || rec.index_name || ‘ validate structure’;
end loop;
end;
/
PL/SQL procedure successfully completed.
3.2 数据损坏检测监控
3.2.1 配置数据损坏检测监控脚本
$ vi /home/oracle/scripts/monitor_corruption.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 数据损坏检测监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
# 定义变量
LOG_FILE=”/home/oracle/scripts/corruption_monitor.log”
EMAIL=”admin@fgedu.net.cn”
# 检查损坏块
check_corruption() {
echo “$(date): Starting corruption check…” >> $LOG_FILE
# 检查V$DATABASE_BLOCK_CORRUPTION视图
local corrupt_count=$(sqlplus -s / as sysdba <
EOF
)
echo “$(date): Corrupt blocks found: $corrupt_count” >> $LOG_FILE
if [ “$corrupt_count” -gt 0 ]; then
echo “$(date): CORRUPTION DETECTED!” >> $LOG_FILE
# 获取损坏块详细信息
local corrupt_details=$(sqlplus -s / as sysdba <
EOF
)
echo “$(date): Corruption Details:” >> $LOG_FILE
echo “$corrupt_details” >> $LOG_FILE
# 发送告警邮件
echo “Data corruption detected in database. Check log file for details.” | mail -s “CORRUPTION ALERT” “$EMAIL”
# 记录损坏信息
record_corruption_info “$corrupt_details”
fi
}
# 记录损坏信息
record_corruption_info() {
local details=”$1″
sqlplus -s / as sysdba <
exit
EOF
echo “$(date): Corruption info recorded” >> $LOG_FILE
}
# 检查告警日志中的损坏错误
check_alert_log() {
local alert_log=”/oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log”
# 检查ORA-01578错误
local ora1578=$(grep “ORA-01578” $alert_log | tail -10)
if [ -n “$ora1578” ]; then
echo “$(date): ORA-01578 errors found in alert log:” >> $LOG_FILE
echo “$ora1578” >> $LOG_FILE
fi
# 检查ORA-01110错误
local ora1110=$(grep “ORA-01110” $alert_log | tail -10)
if [ -n “$ora1110” ]; then
echo “$(date): ORA-01110 errors found in alert log:” >> $LOG_FILE
echo “$ora1110” >> $LOG_FILE
fi
}
# 主函数
main() {
check_corruption
check_alert_log
echo “$(date): Corruption monitoring completed” >> $LOG_FILE
}
# 执行主函数
main
# 2. 创建损坏历史表
SQL> create table corruption_history (
id number primary key,
detection_time date,
file_id number,
block_id number,
blocks number,
corruption_type varchar2(20),
status varchar2(20) default ‘NEW’,
resolution_time date,
resolution_method varchar2(100)
);
Table created.
SQL> create sequence corruption_history_seq start with 1 increment by 1;
Sequence created.
SQL> create or replace trigger corruption_history_trg
before insert on corruption_history
for each row
begin
:new.id := corruption_history_seq.nextval;
end;
/
Trigger created.
# 3. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_corruption.sh
# 4. 测试脚本
$ /home/oracle/scripts/monitor_corruption.sh
# 5. 设置定期监控
$ crontab -e
# 每小时检查数据损坏
0 * * * * /home/oracle/scripts/monitor_corruption.sh >> /home/oracle/scripts/corruption_monitor.log 2>&1
3.2.2 配置RMAN损坏检测
RMAN> backup validate check logical database;
Starting backup at 2026-03-31 10:00:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/app/oracle/oradata/FGEDUDB/system01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/FGEDUDB/sysaux01.dbf
input datafile file number=00003 name=/oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/FGEDUDB/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:00
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
1 OK 0 10240 51200 12345678
File Name: /oracle/app/oracle/oradata/FGEDUDB/system01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 32768
Index 0 8192
Other 0 0
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 1 2048 12800 12345678
File Name: /oracle/app/oracle/oradata/FGEDUDB/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 8192
Index 0 2048
Other 0 2559
Finished backup at 2026-03-31 10:05:00
# 2. 查看RMAN检测到的损坏
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
101 HIGH OPEN 2026-03-31 Datafile 4 contains 1 corrupt blocks
# 3. 查看损坏块详细信息
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_TYPE
———- ———- ———- ————–
4 1024 1 ALL ZERO
# 4. 使用RMAN修复损坏块
RMAN> blockrecover datafile 4 block 1024;
Starting blockrecover at 2026-03-31 10:10:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /backup/FGEDUDB/backup_12345.bak
channel ORA_DISK_1: piece handle=/backup/FGEDUDB/backup_12345.bak tag=TAG20260331T100000
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:10
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 2026-03-31 10:10:15
# 5. 验证修复结果
SQL> select * from v$database_block_corruption;
no rows selected
# 6. 使用RMAN验证数据文件
RMAN> validate datafile 4;
Starting validate at 2026-03-31 10:15:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/oracle/app/oracle/oradata/FGEDUDB/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:30
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 OK 0 2048 12800 12345678
File Name: /oracle/app/oracle/oradata/FGEDUDB/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 8192
Index 0 2048
Other 0 2559
Finished validate at 2026-03-31 10:15:30
3.3 数据损坏检测故障处理
3.3.1 数据损坏检测处理
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1024)
ORA-01110: data file 4: ‘/oracle/app/oracle/oradata/FGEDUDB/users01.dbf’
# 分析步骤
# 1. 查看损坏块信息
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_TYPE
———- ———- ———- ————–
4 1024 1 ALL ZERO
# 2. 查看损坏块所属对象
SQL> select
segment_type,
owner,
segment_name
from dba_extents
where file_id = 4
and 1024 between block_id and block_id + blocks – 1;
SEGMENT_TYPE OWNER SEGMENT_NAME
————– ——- ————
TABLE SCOTT EMP
# 3. 查看损坏块详细信息
SQL> select
file_name,
bytes/1024/1024 as size_mb,
status
from dba_data_files
where file_id = 4;
FILE_NAME SIZE_MB STATUS
——————————————– ———- ———
/oracle/app/oracle/oradata/FGEDUDB/users01.dbf 100 AVAILABLE
# 4. 分析错误原因
# 错误代码：ORA-01578
# 错误信息：ORACLE data block corrupted
# 文件号：4
# 块号：1024
# 损坏类型：ALL ZERO
# 所属对象：SCOTT.EMP表
# 错误原因：数据块被清零，可能是硬件故障或操作系统错误
# 5. 解决方案1：使用RMAN恢复损坏块
RMAN> blockrecover datafile 4 block 1024;
Starting blockrecover at 2026-03-31 10:20:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /backup/FGEDUDB/backup_12345.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:10
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 2026-03-31 10:20:15
# 6. 解决方案2：使用DBMS_REPAIR标记损坏块
SQL> exec dbms_repair.skip_corrupt_blocks(‘SCOTT’, ‘EMP’);
PL/SQL procedure successfully completed.
# 7. 解决方案3：使用DBMS_REPAIR修复损坏块
SQL> declare
v_fix_count number;
begin
dbms_repair.fix_corrupt_blocks(
schema_name => ‘SCOTT’,
object_name => ‘EMP’,
fix_count => v_fix_count
);
dbms_output.put_line(‘Fixed ‘ || v_fix_count || ‘ corrupt blocks’);
end;
/
Fixed 1 corrupt blocks
PL/SQL procedure successfully completed.
# 8. 验证问题解决
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
3.3.2 使用DBMS_REPAIR处理损坏
SQL> exec dbms_repair.admin_tables(
table_name => ‘REPAIR_TABLE’,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’
);
PL/SQL procedure successfully completed.
# 2. 创建孤儿键表
SQL> exec dbms_repair.admin_tables(
table_name => ‘ORPHAN_KEY_TABLE’,
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’
);
PL/SQL procedure successfully completed.
# 3. 检查损坏块
SQL> declare
v_corrupt_count number;
begin
dbms_repair.check_object(
schema_name => ‘SCOTT’,
object_name => ‘EMP’,
repair_table_name => ‘REPAIR_TABLE’,
corrupt_count => v_corrupt_count
);
dbms_output.put_line(‘Corrupt blocks found: ‘ || v_corrupt_count);
end;
/
Corrupt blocks found: 1
PL/SQL procedure successfully completed.
# 4. 查看损坏块信息
SQL> select object_name, block_id, corrupt_type, marked_corrupt
from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_CORRUPT
———— ———- ———— ————–
EMP 1024 6268 FALSE
# 5. 标记损坏块
SQL> declare
v_fix_count number;
begin
dbms_repair.fix_corrupt_blocks(
schema_name => ‘SCOTT’,
object_name => ‘EMP’,
repair_table_name => ‘REPAIR_TABLE’,
fix_count => v_fix_count
);
dbms_output.put_line(‘Fixed ‘ || v_fix_count || ‘ corrupt blocks’);
end;
/
Fixed 1 corrupt blocks
PL/SQL procedure successfully completed.
# 6. 验证标记结果
SQL> select object_name, block_id, corrupt_type, marked_corrupt
from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_CORRUPT
———— ———- ———— ————–
EMP 1024 6268 TRUE
# 7. 重建索引
SQL> alter index scott.pk_emp rebuild;
Index altered.
# 8. 查询孤儿键
SQL> declare
v_orphan_count number;
begin
dbms_repair.dump_orphan_keys(
schema_name => ‘SCOTT’,
object_name => ‘PK_EMP’,
repair_table_name => ‘REPAIR_TABLE’,
orphan_table_name => ‘ORPHAN_KEY_TABLE’,
key_count => v_orphan_count
);
dbms_output.put_line(‘Orphan keys found: ‘ || v_orphan_count);
end;
/
Orphan keys found: 1
PL/SQL procedure successfully completed.
# 9. 查看孤儿键信息
SQL> select * from orphan_key_table;
SCHEMA_NAME OBJECT_NAME INDEX_ID INDEX_NAME KEY_ROWID KEY_ID
———– ———– ——– ——— ————- ——
SCOTT EMP 123 PK_EMP AAASdqAAEAAA 7369
# 10. 清理修复表
SQL> exec dbms_repair.admin_tables(
table_name => ‘REPAIR_TABLE’,
table_type => dbms_repair.repair_table,
action => dbms_repair.purge_action
);
PL/SQL procedure successfully completed.
Part04-生产案例与实战讲解
4.1 数据损坏检测分析案例
在生产环境中分析数据损坏检测的完整案例：
4.1.1 场景描述
某企业生产数据库出现数据损坏，需要分析损坏原因并修复数据。
4.1.2 分析步骤
SQL> select * from fgfgfgsales.orders where order_id = 12345;
select * from fgfgfgsales.orders where order_id = 12345
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 2048)
ORA-01110: data file 5: ‘/oracle/app/oracle/oradata/FGEDUDB/fgfgfgsales01.dbf’
# 2. 查看损坏块信息
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_TYPE
———- ———- ———- ————–
5 2048 1 FRACTURED
# 3. 查看损坏块所属对象
SQL> select
segment_type,
owner,
segment_name,
partition_name
from dba_extents
where file_id = 5
and 2048 between block_id and block_id + blocks – 1;
SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
————– —— ————- ————–
TABLE SALES ORDERS
# 4. 查看损坏块详细信息
SQL> select
file_name,
bytes/1024/1024 as size_mb,
status,
tablespace_name
from dba_data_files
where file_id = 5;
FILE_NAME SIZE_MB STATUS TABLESPACE_NAME
——————————————– ———- ——— —————-
/oracle/app/oracle/oradata/FGEDUDB/fgfgfgsales01.dbf 10240 AVAILABLE SALES
# 5. 分析错误原因
# 错误代码：ORA-01578
# 错误信息：ORACLE data block corrupted
# 文件号：5
# 块号：2048
# 损坏类型：FRACTURED（块断裂）
# 所属对象：SALES.ORDERS表
# 错误原因：块头部和尾部不一致，可能是I/O错误
# 6. 解决方案：使用RMAN恢复损坏块
RMAN> blockrecover datafile 5 block 2048;
Starting blockrecover at 2026-03-31 10:30:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /backup/FGEDUDB/backup_12346.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:10
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 2026-03-31 10:30:15
# 7. 验证问题解决
SQL> select * from fgfgfgsales.orders where order_id = 12345;
ORDER_ID ORDER_DATE CUSTOMER_ID ORDER_STATUS TOTAL_AMOUNT
———- ———– ———– ————- ————
12345 2026-03-31 10001 COMPLETED 1500.00
# 8. 验证损坏块已清除
SQL> select * from v$database_block_corruption;
no rows selected
4.2 数据损坏检测故障处理
在数据损坏检测故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 故障识别
# – 监控数据损坏错误
# – 检查错误信息
# – 确认损坏位置
# 2. 故障分析
# – 查看损坏块信息
# – 确定损坏对象
# – 分析损坏原因
# 3. 故障处理
# – 使用RMAN恢复损坏块
# – 使用DBMS_REPAIR修复损坏
# – 重建损坏对象
# 4. 故障预防
# – 定期检测数据损坏
# – 保持备份完整
# – 监控硬件状态
# 示例：数据损坏检测故障处理
# 1. 故障识别
SQL> select * from large_table;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 4096)
# 2. 故障分析
SQL> select segment_type, owner, segment_name
from dba_extents
where file_id = 6 and 4096 between block_id and block_id + blocks – 1;
SEGMENT_TYPE OWNER SEGMENT_NAME
————– ——- ————
TABLE APP LARGE_TABLE
# 3. 故障处理
RMAN> blockrecover datafile 6 block 4096;
Starting blockrecover at 2026-03-31 10:40:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /backup/FGEDUDB/backup_12347.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:10
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 2026-03-31 10:40:15
# 4. 验证处理结果
SQL> select count(*) from large_table;
COUNT(*)
———-
1000000
# 5. 故障预防
# – 定期检测数据损坏
# – 保持备份完整
# – 监控硬件状态
# – 启用块检查
4.3 数据损坏检测优化
优化数据损坏检测配置的最佳实践：
4.3.1 优化数据损坏检测管理
SQL> create or replace procedure optimize_corruption_detection as
v_corrupt_count number;
v_last_check date;
begin
— 获取损坏块数量
select count(*) into v_corrupt_count
from v$database_block_corruption;
— 获取最后检测时间
select max(detection_time) into v_last_check
from corruption_history;
dbms_output.put_line(‘— Corruption Detection Status —‘);
dbms_output.put_line(‘Corrupt blocks found: ‘ || v_corrupt_count);
dbms_output.put_line(‘Last detection time: ‘ || v_last_check);
if v_corrupt_count > 0 then
dbms_output.put_line(‘— CORRUPTION DETECTED —‘);
— 显示损坏块详细信息
for rec in (
select file#, block#, blocks, corruption_type
from v$database_block_corruption
) loop
dbms_output.put_line(‘File: ‘ || rec.file# ||
‘, Block: ‘ || rec.block# ||
‘, Type: ‘ || rec.corruption_type);
— 获取损坏块所属对象
for obj in (
select segment_type, owner, segment_name
from dba_extents
where file_id = rec.file#
and rec.block# between block_id and block_id + blocks – 1
) loop
dbms_output.put_line(‘Object: ‘ || obj.owner || ‘.’ || obj.segment_name);
end loop;
end loop;
dbms_output.put_line(‘Recommendation: Use RMAN BLOCKRECOVER to fix corruption’);
else
dbms_output.put_line(‘No corruption detected’);
end if;
end optimize_corruption_detection;
/
Procedure created.
# 2. 执行优化存储过程
SQL> set serveroutput on
SQL> exec optimize_corruption_detection;
— Corruption Detection Status —
Corrupt blocks found: 1
Last detection time: 2026-03-31 10:00:00
— CORRUPTION DETECTED —
File: 4, Block: 1024, Type: ALL ZERO
Object: SCOTT.EMP
Recommendation: Use RMAN BLOCKRECOVER to fix corruption
PL/SQL procedure successfully completed.
# 3. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘OPTIMIZE_CORRUPTION_DETECTION_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘OPTIMIZE_CORRUPTION_DETECTION’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;BYHOUR=8’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
# 4. 启用块检查
SQL> alter system set db_block_checking = true scope=both;
System altered.
SQL> alter system set db_block_checksum = true scope=both;
System altered.
# 5. 验证块检查设置
SQL> show parameter db_block_checking;
NAME TYPE VALUE
———————————— ———– ——————————
db_block_checking string TRUE
SQL> show parameter db_block_checksum;
NAME TYPE VALUE
———————————— ———– ——————————
db_block_checksum string TRUE
Part05-风哥经验总结与分享
5.1 数据损坏检测总结
Oracle数据损坏检测是数据库管理的重要任务，具有以下特点：
- 物理损坏：数据块物理结构损坏
- 逻辑损坏：数据逻辑不一致
- 数据文件损坏：数据文件头部或内容损坏
- 控制文件损坏：控制文件损坏导致数据库无法启动
- 日志文件损坏：重做日志文件损坏导致恢复失败
5.2 数据损坏检测检查清单
Oracle数据损坏检测检查清单：
- 故障识别：识别损坏错误信息
- 故障分析：分析损坏块和对象
- 故障处理：处理损坏问题
- 故障验证：验证处理结果
- 故障预防：制定预防措施
- 故障记录：记录处理过程
5.3 数据损坏检测工具推荐
Oracle数据损坏检测工具推荐：
- DBVERIFY：验证数据文件完整性
- ANALYZE：分析表和索引结构
- RMAN：备份和恢复时检测损坏
- DBMS_REPAIR：检测和修复损坏块
- V$DATABASE_BLOCK_CORRUPTION：损坏块视图
- DBA_EXTENTS：区信息视图
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
