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

DBVERIFY: Release 19.0.0.0.0 – Production on Mon 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/users01.dbf
DBVERIFY – Verification complete

Total Pages Examined : 64000
Total Pages Processed (Data) : 50000
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 10000
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4000
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

# 2. 验证特定表的数据块
SQL> analyze table employees validate structure cascade;

Table analyzed.

# 3. 验证特定索引的数据块
SQL> analyze index employees_pk validate structure;

Index analyzed.

# 4. 验证特定表空间的数据块
SQL> begin
dbms_space_admin.tablesspace_verify(‘USERS’);
end;
/

PL/SQL procedure successfully completed.

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

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
4 12345 1 0 CORRUPT

# 6. 查看损坏的数据块详细信息
SQL> select file_name, tablespace_name, blocks, status
from dba_data_files
where file_id = 4;

FILE_NAME TABLESPACE_NAME BLOCKS STATUS
——————————————————– —————————— ———- ———
/oracle/app/oracle/oradata/FGEDUDB/users01.dbf USERS 64000 AVAILABLE

# 1. 创建修复表
SQL> begin
dbms_repair.admin_tables(
table_name => ‘REPAIR_TABLE’,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 创建孤立键表
SQL> begin
dbms_repair.admin_tables(
table_name => ‘ORPHAN_KEY_TABLE’,
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’
);
end;
/

PL/SQL procedure successfully completed.

# 3. 检查损坏的数据块
SQL> declare
v_corrupt_count number;
begin
v_corrupt_count := dbms_repair.check_object(
schema_name => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
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_COR
———— ———- ———— ———-
EMPLOYEES 123 6148 NO

# 5. 修复损坏的数据块
SQL> declare
v_fix_count number;
begin
v_fix_count := dbms_repair.fix_corrupt_blocks(
schema_name => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
repair_table_name => ‘REPAIR_TABLE’,
fix_count => v_fix_count
);

dbms_output.put_line(‘Fixed blocks: ‘ || v_fix_count);
end;
/

Fixed blocks: 1

PL/SQL procedure successfully completed.

# 6. 跳过损坏的数据块
SQL> declare
v_skip_count number;
begin
v_skip_count := dbms_repair.skip_corrupt_blocks(
schema_name => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag
);

dbms_output.put_line(‘Skipped blocks: ‘ || v_skip_count);
end;
/

Skipped blocks: 1

PL/SQL procedure successfully completed.

# 1. 创建ORA-01578错误监控脚本
$ vi /home/oracle/scripts/monitor_ora01578.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
# ORA-01578错误监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

# 定义变量
ALERT_LOG=”/oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log”
ERROR_LOG=”/home/oracle/scripts/ora01578_errors.log”
EMAIL=”admin@fgedu.net.cn”

# 检查ORA-01578错误
check_ora01578_errors() {
local last_check_file=”/home/oracle/scripts/last_ora01578_check.txt”
local current_time=$(date +%s)

if [ -f “$last_check_file” ]; then
local last_check=$(cat “$last_check_file”)
local time_diff=$((current_time – last_check))

if [ $time_diff -lt 3600 ]; then
echo “Last check was $time_diff seconds ago, skipping…”
return 0
fi
fi

# 检查最近1小时的ORA-01578错误
local ora01578_errors=$(grep “ORA-01578” “$ALERT_LOG” | tail -100)

if [ -n “$ora01578_errors” ]; then
echo “Found ORA-01578 errors:” >> “$ERROR_LOG”
echo “$ora01578_errors” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

# 发送告警邮件
echo “ORA-01578 errors detected in alert log” | mail -s “ORA-01578 Alert” “$EMAIL”

# 分析数据块损坏
analyze_block_corruption
fi

# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}

# 分析数据块损坏
analyze_block_corruption() {
local block_corruption=$(sqlplus -s / as sysdba < select ‘File#: ‘ || file# || ‘, Block#: ‘ || block# || ‘, Blocks: ‘ || blocks ||
‘, Corruption Change#: ‘ || corruption_change#
from v\$database_block_corruption;
EOF
)

echo “$block_corruption” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora01578_errors

echo “ORA-01578 error monitoring completed at $(date)” >> “$ERROR_LOG”
}

# 执行主函数
main

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

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

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

# 每小时检查ORA-01578错误
0 * * * * /home/oracle/scripts/monitor_ora01578.sh >> /home/oracle/scripts/monitor_ora01578.log 2>&1

# 1. 创建ORA-01578错误监控视图
SQL> create or replace view ora01578_error_monitor as
select
to_char(timestamp, ‘YYYY-MM-DD HH24:MI:SS’) as error_time,
error_code,
error_message,
file_number,
block_number,
username,
osuser,
machine,
program,
sql_text
from (
select
to_date(substr(message, 1, 19), ‘YYYY-MM-DD HH24:MI:SS’) as timestamp,
substr(message, instr(message, ‘ORA-01578’), 9) as error_code,
substr(message, instr(message, ‘ORA-01578’), instr(message, chr(10)) – instr(message, ‘ORA-01578’)) as error_message,
to_number(substr(message, instr(message, ‘FILE ‘) + 5, instr(message, ‘BLOCK ‘) – instr(message, ‘FILE ‘) – 5)) as file_number,
to_number(substr(message, instr(message, ‘BLOCK ‘) + 6, instr(message, chr(10)) – instr(message, ‘BLOCK ‘) – 6)) as block_number,
null as username,
null as osuser,
null as machine,
null as program,
null as sql_text
from (
select column_value as message
from xmltable((‘”‘ || replace(
dbms_metadata.get_ddl(‘TABLE’, ‘DUMMY’),
chr(10),
‘” “‘
) || ‘”‘))
)
);

View created.

# 2. 创建数据块损坏监控视图
SQL> create or replace view block_corruption_monitor as
select
file#,
block#,
blocks,
corruption_change#,
corruption_type,
tablespace_name,
segment_type,
segment_name,
partition_name
from v$database_block_corruption;

View created.

# 3. 查询数据块损坏信息
SQL> select * from block_corruption_monitor;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME
———- ———- ———- —————— ——— —————- ————- ———— —————-
4 12345 1 0 CORRUPT USERS TABLE EMPLOYEES

# 4. 创建ORA-01578错误告警
SQL> create or replace procedure ora01578_error_alert as
v_error_count number;
v_block_corruption_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 检查ORA-01578错误
select count(*) into v_error_count
from ora01578_error_monitor
where error_time > sysdate – 1;

— 检查数据块损坏
select count(*) into v_block_corruption_count
from block_corruption_monitor;

if v_error_count > 0 or v_block_corruption_count > 0 then
— 发送告警
utl_smtp.open_connection(
host => ‘smtp.fgedu.net.cn’,
port => 25,
c => v_conn
);

utl_smtp.helo(v_conn, ‘localhost’);
utl_smtp.mail(v_conn, ‘oracle@fgedu.net.cn’);
utl_smtp.rcpt(v_conn, v_email);
utl_smtp.open_data(v_conn);

utl_smtp.write_data(v_conn, ‘Subject: ORA-01578 Error Alert’ || utl_tcp.crlf);
utl_smtp.write_data(v_conn, ‘To: ‘ || v_email || utl_tcp.crlf);
utl_smtp.write_data(v_conn, utl_tcp.crlf);
utl_smtp.write_data(v_conn, ‘ORA-01578 errors detected: ‘ || v_error_count || utl_tcp.crlf);
utl_smtp.write_data(v_conn, ‘Block corruption count: ‘ || v_block_corruption_count || utl_tcp.crlf);

utl_smtp.close_data(v_conn);
utl_smtp.quit(v_conn);
end if;
end ora01578_error_alert;
/

Procedure created.

# 5. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA01578_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA01578_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/

PL/SQL procedure successfully completed.

# 问题现象
SQL> select * from employees where id = 100;

select * from employees where id = 100
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 12345)
ORA-01110: data file 4: ‘/oracle/app/oracle/oradata/FGEDUDB/users01.dbf’

# 分析步骤

# 1. 查看告警日志
$ grep “ORA-01578” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log

2026-03-31T10:00:00.123456+08:00
ORA-01578: ORACLE data block corrupted (file # 4, block # 12345)

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

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
4 12345 1 0 CORRUPT

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

DBVERIFY: Release 19.0.0.0.0 – Production on Mon 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/users01.dbf
Page 12345 is marked corrupt

DBVERIFY – Verification complete

Total Pages Examined : 64000
Total Pages Processed (Data) : 50000
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 10000
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4000
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 1
Total Pages Influx : 0

# 4. 分析错误原因
# 错误代码：ORA-01578
# 错误信息：ORACLE data block corrupted (file # 4, block # 12345)
# 数据文件：4
# 数据块：12345
# 错误原因：数据块损坏

# 5. 解决方案：使用RMAN恢复数据块
$ rman target /

RMAN> blockrecover datafile 4 block 12345;

Starting blockrecover at 31-MAR-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from datafile 4
channel ORA_DISK_1: restoring block(s) from backup piece
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 31-MAR-26

# 6. 验证问题解决
SQL> select * from employees where id = 100;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
100 风哥2号 lisi@fgedu.net.cn 13900139000 9876543210987654 987654321 20000

# 7. 预防措施
# – 定期备份数据库
# – 定期验证数据块
# – 定期检查硬件
# – 定期检查系统日志

# 1. 使用RMAN恢复数据块
$ rman target /

RMAN> blockrecover datafile 4 block 12345;

Starting blockrecover at 31-MAR-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from datafile 4
channel ORA_DISK_1: restoring block(s) from backup piece
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 31-MAR-26

# 2. 验证数据块恢复
SQL> select * from employees where id = 100;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
100 风哥2号 lisi@fgedu.net.cn 13900139000 9876543210987654 987654321 20000

# 3. 使用DBMS_REPAIR修复数据块
SQL> declare
v_fix_count number;
begin
v_fix_count := dbms_repair.fix_corrupt_blocks(
schema_name => ‘SCOTT’,
object_name => ‘EMPLOYEES’,
repair_table_name => ‘REPAIR_TABLE’,
fix_count => v_fix_count
);

dbms_output.put_line(‘Fixed blocks: ‘ || v_fix_count);
end;
/

Fixed blocks: 1

PL/SQL procedure successfully completed.

# 4. 验证数据块修复
SQL> select * from employees where id = 100;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
100 风哥2号 lisi@fgedu.net.cn 13900139000 9876543210987654 987654321 20000

# 5. 使用ANALYZE验证数据块
SQL> analyze table employees validate structure cascade;

Table analyzed.

# 6. 验证数据块完整性
SQL> select * from v$database_block_corruption;

no rows selected