FILE# BLOCK# BLOCKS CORRUPTION_TYPE
———- ———- ———- ————–
4 1024 1 ALL ZERO
4 2048 1 FRACTURED
5 4096 1 CHECKSUM

# 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. 使用RMAN恢复单个损坏块
RMAN> blockrecover datafile 4 block 1024;

Starting blockrecover 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: 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:00:15

# 4. 使用RMAN恢复多个损坏块
RMAN> blockrecover datafile 4 block 1024, 2048;

Starting blockrecover at 2026-03-31 10:05: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:15
starting media recovery
media recovery complete, elapsed time: 00:00:10
Finished blockrecover at 2026-03-31 10:05:25

# 5. 使用RMAN恢复所有损坏块
RMAN> blockrecover corruption list;

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
restoring blocks of datafile 00005
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:20
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished blockrecover at 2026-03-31 10:10:35

# 6. 验证修复结果
SQL> select * from v$database_block_corruption;

no rows selected

# 7. 使用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
Finished validate at 2026-03-31 10:15:30

# 8. 查询修复后的数据
SQL> select count(*) from scott.emp;

COUNT(*)
———-
14

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: 2

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
EMP 2048 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 2 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
EMP 2048 6268 TRUE

# 7. 设置跳过损坏块
SQL> exec dbms_repair.skip_corrupt_blocks(‘SCOTT’, ‘EMP’);

PL/SQL procedure successfully completed.

# 8. 查询数据（跳过损坏块）
SQL> select count(*) from scott.emp;

COUNT(*)
———-
12

# 9. 查看孤儿键
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: 2

PL/SQL procedure successfully completed.

# 10. 查看孤儿键信息
SQL> select schema_name, object_name, index_name, key_rowid
from orphan_key_table;

SCHEMA_NAME OBJECT_NAME INDEX_NAME KEY_ROWID
———– ———– ———- ————-
SCOTT EMP PK_EMP AAASdqAAEAAA
SCOTT EMP PK_EMP AAASdqAAEAAA

# 11. 重建索引
SQL> alter index scott.pk_emp rebuild;

Index altered.

# 12. 清理修复表
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.

#!/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/repair_monitor.log”
EMAIL=”admin@fgedu.net.cn”

# 检查损坏块
check_corruption() {
echo “$(date): Checking for corruption…” >> $LOG_FILE

local corrupt_count=$(sqlplus -s / as sysdba < exit
EOF
)

echo “$(date): Corrupt blocks: $corrupt_count” >> $LOG_FILE

if [ “$corrupt_count” -gt 0 ]; then
echo “$(date): CORRUPTION FOUND!” >> $LOG_FILE

# 执行RMAN块恢复
rman target / <> $LOG_FILE
blockrecover corruption list;
exit
EOF

# 验证修复结果
verify_repair
fi
}

# 验证修复结果
verify_repair() {
echo “$(date): Verifying repair…” >> $LOG_FILE

local remaining_corrupt=$(sqlplus -s / as sysdba < exit
EOF
)

if [ “$remaining_corrupt” -eq 0 ]; then
echo “$(date): Repair successful!” >> $LOG_FILE
echo “Data corruption repair successful” | mail -s “REPAIR SUCCESS” “$EMAIL”
else
echo “$(date): Repair failed! Remaining corrupt blocks: $remaining_corrupt” >> $LOG_FILE
echo “Data corruption repair failed. Manual intervention required.” | mail -s “REPAIR FAILED” “$EMAIL”
fi
}

# 主函数
main() {
check_corruption

echo “$(date): Repair monitoring completed” >> $LOG_FILE
}

# 执行主函数
main

# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_repair.sh

# 3. 测试脚本
$ /home/oracle/scripts/monitor_repair.sh

# 4. 设置定期监控
$ crontab -e

# 每小时检查数据损坏
0 * * * * /home/oracle/scripts/monitor_repair.sh >> /home/oracle/scripts/repair_monitor.log 2>&1

— 获取已修复块数量
select count(*) into v_repair_count
from repair_table
where marked_corrupt = ‘TRUE’;

dbms_output.put_line(‘— Repair Verification —‘);
dbms_output.put_line(‘Remaining corrupt blocks: ‘ || v_corrupt_count);
dbms_output.put_line(‘Marked corrupt blocks: ‘ || v_repair_count);

if v_corrupt_count = 0 then
dbms_output.put_line(‘Status: All corruption repaired’);
else
dbms_output.put_line(‘Status: Corruption still exists’);

— 显示剩余损坏块
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);
end loop;
end if;
end verify_repair_result;
/

Procedure created.

# 2. 执行验证存储过程
SQL> set serveroutput on
SQL> exec verify_repair_result;

— Repair Verification —
Remaining corrupt blocks: 0
Marked corrupt blocks: 2
Status: All corruption repaired

PL/SQL procedure successfully completed.

# 3. 创建数据验证存储过程
SQL> create or replace procedure verify_data_integrity(
p_owner in varchar2,
p_table_name in varchar2
) as
v_count number;
v_status varchar2(20);
begin
— 验证表是否可访问
begin
execute immediate ‘select count(*) from ‘ || p_owner || ‘.’ || p_table_name
into v_count;
v_status := ‘ACCESSIBLE’;
exception
when others then
v_count := 0;
v_status := ‘CORRUPT’;
end;

dbms_output.put_line(‘Table: ‘ || p_owner || ‘.’ || p_table_name);
dbms_output.put_line(‘Status: ‘ || v_status);
dbms_output.put_line(‘Row Count: ‘ || v_count);

— 验证索引
for idx in (
select index_name
from dba_indexes
where owner = p_owner
and table_name = p_table_name
) loop
begin
execute immediate ‘analyze index ‘ || p_owner || ‘.’ || idx.index_name ||
‘ validate structure’;
dbms_output.put_line(‘Index: ‘ || idx.index_name || ‘ – VALID’);
exception
when others then
dbms_output.put_line(‘Index: ‘ || idx.index_name || ‘ – CORRUPT’);
end;
end loop;
end verify_data_integrity;
/

Procedure created.

# 4. 执行数据验证
SQL> set serveroutput on
SQL> exec verify_data_integrity(‘SCOTT’, ‘EMP’);

Table: SCOTT.EMP
Status: ACCESSIBLE
Row Count: 12
Index: PK_EMP – VALID

PL/SQL procedure successfully completed.

# 5. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘VERIFY_REPAIR_RESULT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘VERIFY_REPAIR_RESULT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;BYHOUR=8’,
enabled => true
);
end;
/

PL/SQL procedure successfully completed.

# 分析步骤

# 1. 查看损坏块信息
SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_TYPE
———- ———- ———- ————–
4 1024 1 ALL ZERO
4 1025 1 ALL ZERO
4 1026 1 ALL ZERO

# 2. 查看损坏块所属对象
SQL> select
segment_type,
owner,
segment_name,
partition_name
from dba_extents
where file_id = 4
and 1024 between block_id and block_id + blocks – 1;

SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
————– ——- ————- ————–
TABLE SCOTT EMP

# 3. 查看备份可用性
RMAN> list backup of datafile 4;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
123 Full 1024.00M DISK 00:05:00 2026-03-30 10:00:00
BP Key: 456 Status: AVAILABLE Compressed: NO Tag: TAG20260330T100000
Piece Name: /backup/FGEDUDB/backup_12345.bak
List of Datafiles in backup set 123
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
4 Full 12345678 2026-03-30 10:00:00 /oracle/app/oracle/oradata/FGEDUDB/users01.dbf

# 4. 分析错误原因
# 错误代码：ORA-01578
# 错误信息：ORACLE data block corrupted
# 文件号：4
# 块号：1024, 1025, 1026
# 损坏类型：ALL ZERO
# 所属对象：SCOTT.EMP表
# 备份状态：可用
# 错误原因：数据块被清零，可能是硬件故障

# 5. 解决方案1：使用RMAN块恢复
RMAN> blockrecover datafile 4 block 1024, 1025, 1026;

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:15
starting media recovery
media recovery complete, elapsed time: 00:00:10
Finished blockrecover at 2026-03-31 10:20:25

# 6. 解决方案2：使用DBMS_REPAIR（如果RMAN无法恢复）
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.

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: ‘ || v_corrupt_count);
end;
/

Corrupt blocks: 3

PL/SQL procedure successfully completed.

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 3 corrupt blocks

PL/SQL procedure successfully completed.

SQL> exec dbms_repair.skip_corrupt_blocks(‘SCOTT’, ‘EMP’);

PL/SQL procedure successfully completed.

# 7. 验证问题解决
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
…

# 8. 预防措施
# – 定期备份数据库
# – 启用块检查
# – 监控硬件状态
# – 定期验证备份

FILE_NAME STATUS ERROR
——————————————– ——— ———
/oracle/app/oracle/oradata/FGEDUDB/users01.dbf AVAILABLE

# 2. 查看数据文件损坏情况
SQL> select file#, status, error, recover
from v$datafile
where file# = 4;

FILE# STATUS ERROR REC
———- ——- ——– —
4 ONLINE NO

# 3. 使用RMAN恢复数据文件
RMAN> run {
allocate channel ch1 type disk;
sql ‘alter tablespace users offline immediate’;
restore datafile 4;
recover datafile 4;
sql ‘alter tablespace users online’;
release channel ch1;
}

allocated channel: ch1
channel ch1: SID=150 device type=DISK

sql statement: alter tablespace users offline immediate

Starting restore at 2026-03-31 10:30:00
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00004 to /oracle/app/oracle/oradata/FGEDUDB/users01.dbf
channel ch1: reading from backup piece /backup/FGEDUDB/backup_12345.bak
channel ch1: piece handle=/backup/FGEDUDB/backup_12345.bak tag=TAG20260330T100000
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:02:00
Finished restore at 2026-03-31 10:32:00

Starting recover at 2026-03-31 10:32:00
starting media recovery
media recovery complete, elapsed time: 00:00:30
Finished recover at 2026-03-31 10:32:30

sql statement: alter tablespace users online

released channel: ch1

# 4. 验证恢复结果
SQL> select file_name, status, error
from dba_data_files
where file_id = 4;

FILE_NAME STATUS ERROR
——————————————– ——— ———
/oracle/app/oracle/oradata/FGEDUDB/users01.dbf AVAILABLE

# 5. 验证数据完整性
SQL> select count(*) from scott.emp;

COUNT(*)
———-
14

# 6. 验证损坏块已清除
SQL> select * from v$database_block_corruption;

no rows selected

# 7. 使用DBVERIFY验证数据文件
$ dbv file=/oracle/app/oracle/oradata/FGEDUDB/users01.dbf

DBVERIFY: Release 19.0.0.0.0 – Production on Tue Mar 31 10:35:00 2026

DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/FGEDUDB/users01.dbf

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 : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 12345678 (0.12345678)

# 8. 验证应用功能
SQL> select * from scott.emp where deptno = 10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10