Oracle教程FG450-恢复场景汇总

本文档风哥主要介绍Oracle恢复场景汇总相关知识，包括恢复场景的概念、恢复类型、恢复场景使用、恢复场景配置、恢复场景监控、恢复场景故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 恢复场景的概念

Oracle恢复场景是指在数据库发生故障时，根据故障类型和备份策略，选择合适的恢复方法来恢复数据库的过程。恢复场景包括实例恢复、介质恢复、块恢复、表空间恢复、时间点恢复等多种类型，是保障数据库可用性和数据安全的重要手段。更多视频教程www.fgedu.net.cn

Oracle恢复场景的特点：

多种类型：支持多种恢复类型
灵活选择：根据故障类型选择恢复方法
数据安全：保障数据安全和可用性
快速恢复：快速恢复数据库服务
完整方案：提供完整的恢复方案

1.2 恢复类型

Oracle恢复类型：

实例恢复：自动恢复实例故障
介质恢复：恢复介质故障
块恢复：恢复损坏的数据块
表空间恢复：恢复表空间
时间点恢复：恢复到指定时间点

# 恢复类型说明

1. 实例恢复
– 自动恢复实例故障
– 使用联机重做日志
– 前滚已提交事务
– 回滚未提交事务
– 无需DBA干预

2. 介质恢复
– 恢复介质故障
– 使用备份和归档日志
– 完全恢复或不完全恢复
– 需要DBA干预
– 可能需要时间点恢复

3. 块恢复
– 恢复损坏的数据块
– 使用RMAN blockrecover
– 在线恢复数据块
– 不影响其他数据
– 快速恢复

4. 表空间恢复
– 恢复表空间
– 使用RMAN或用户管理备份
– 可以在线或离线恢复
– 不影响其他表空间
– 灵活恢复

5. 时间点恢复
– 恢复到指定时间点
– 使用备份和归档日志
– 可以恢复到SCN、时间或日志序列
– 不完全恢复
– 需要resetlogs

1.3 恢复场景使用

Oracle恢复场景使用：

实例故障：实例恢复
介质故障：介质恢复
块损坏：块恢复
表空间损坏：表空间恢复
误操作：时间点恢复

风哥提示：恢复场景是保障数据库可用性和数据安全的重要手段，建议掌握各种恢复场景。

Part02-生产环境规划与建议

2.1 恢复场景规划

Oracle恢复场景规划要点：

# 恢复场景规划
– 备份策略：制定完善的备份策略
– 恢复策略：制定详细的恢复策略
– 测试计划：定期测试恢复方案
– 文档记录：记录恢复过程和结果

# 备份策略规划
– 全备份：定期进行全备份
– 增量备份：定期进行增量备份
– 归档日志：定期备份归档日志
– 控制文件：定期备份控制文件
– 参数文件：定期备份参数文件

# 恢复策略规划
– 实例恢复：自动恢复，无需干预
– 介质恢复：制定详细的恢复步骤
– 块恢复：制定块恢复流程
– 表空间恢复：制定表空间恢复流程
– 时间点恢复：制定时间点恢复流程

# 测试计划规划
– 定期测试：定期测试恢复方案
– 测试环境：在测试环境中测试
– 测试记录：记录测试结果
– 测试改进：根据测试结果改进

# 文档记录规划
– 恢复文档：编写恢复文档
– 操作步骤：记录详细操作步骤
– 注意事项：记录注意事项
– 经验总结：总结恢复经验

2.2 恢复场景分类

Oracle恢复场景分类：

实例故障：实例崩溃、断电等
介质故障：磁盘故障、文件损坏等
块损坏：数据块损坏
表空间损坏：表空间损坏
误操作：误删除、误更新等

# 恢复场景分类

1. 实例故障
– 实例崩溃
– 断电
– 操作系统故障
– Oracle进程异常终止
– 实例恢复自动处理

2. 介质故障
– 磁盘故障
– 文件损坏
– 文件丢失
– 存储故障
– 需要介质恢复

3. 块损坏
– 物理损坏
– 逻辑损坏
– 坏块
– 校验失败
– 需要块恢复

4. 表空间损坏
– 表空间离线
– 数据文件损坏
– 表空间不可用
– 需要表空间恢复

5. 误操作
– 误删除数据
– 误更新数据
– 误删除表
– 误删除用户
– 需要时间点恢复

2.3 恢复场景最佳实践

Oracle恢复场景最佳实践：

定期备份：定期进行备份
测试恢复：定期测试恢复方案
文档记录：记录恢复过程
快速响应：快速响应故障
经验总结：总结恢复经验

生产环境建议：恢复场景需要建立完善的流程，建议定期备份、测试恢复、文档记录。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 恢复场景配置

3.1.1 实例恢复场景

# 1. 实例恢复概述
实例恢复是Oracle自动进行的恢复过程，当实例发生故障时，Oracle会自动使用联机重做日志进行恢复。

# 2. 实例恢复过程
SQL> startup

ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size 8710176 bytes
Variable Size 1090519040 bytes
Database Buffers 4261412864 bytes
Redo Buffers 8069120 bytes
Database mounted.
Database opened.

# 3. 查看实例恢复状态
SQL> select instance_name, status, startup_time from v$instance;

INSTANCE_NAME STATUS STARTUP_TIME
—————- ———— ——————-
FGEDUDB OPEN 2026-03-31 10:00:00

# 4. 查看恢复进度
SQL> select * from v$recovery_progress;

# 5. 查看实例恢复统计
SQL> select name, value from v$sysstat
where name like ‘%recovery%’;

NAME VALUE
——————– ———-
recovery blocks read 1000
recovery blocks written 1000

# 6. 调整实例恢复参数
SQL> alter system set fast_start_mttr_target = 300 scope=spfile;

System altered.

# 7. 查看MTTR建议
SQL> select target_mttr, estimated_mttr from v$instance_recovery;

TARGET_MTTR ESTIMATED_MTTR
———– ————–
300 250

# 8. 监控实例恢复
SQL> select * from v$instance_recovery;

RECOVERY_ESTIMATED_MTTR ACTUAL_RECLMT_MTTR TARGET_MTTR
———————– —————— ———–
250 300 300

3.1.2 介质恢复场景

# 1. 介质恢复概述
介质恢复是指使用备份和归档日志恢复数据库的过程，通常用于介质故障。

# 2. 完全恢复场景

# 2.1 恢复整个数据库
RMAN> startup mount;

RMAN> restore database;

Starting restore at 2026-03-31 10:00:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/FGEDUDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/FGEDUDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/FGEDUDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/FGEDUDB_full_20260331_100000
channel ORA_DISK_1: piece handle=/backup/FGEDUDB_full_20260331_100000 tag=TAG20260331T100000
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:00
Finished restore at 2026-03-31 10:05:00

RMAN> recover database;

Starting recover at 2026-03-31 10:05:00
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 100 is already on disk as file /oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc
archived log for thread 1 with sequence 101 is already on disk as file /oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_101_abc124.arc
archived log file name=/oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc thread=1 sequence=100
archived log file name=/oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_101_abc124.arc thread=1 sequence=101
media recovery complete, elapsed time: 00:00:30
Finished recover at 2026-03-31 10:05:30

RMAN> alter database open;

Statement processed

# 2.2 恢复表空间
RMAN> sql ‘alter tablespace users offline’;

sql statement: alter tablespace users offline

RMAN> restore tablespace users;

Starting restore at 2026-03-31 10:00:00
using channel ORA_DISK_1

RMAN> recover tablespace users;

Starting recover at 2026-03-31 10:01:00
using channel ORA_DISK_1

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

RMAN> sql ‘alter tablespace users online’;

sql statement: alter tablespace users online

# 2.3 恢复数据文件
RMAN> sql ‘alter database datafile 4 offline’;

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 2026-03-31 10:00:00
using channel ORA_DISK_1

RMAN> recover datafile 4;

Starting recover at 2026-03-31 10:01:00
using channel ORA_DISK_1

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

RMAN> sql ‘alter database datafile 4 online’;

sql statement: alter database datafile 4 online

3.1.3 块恢复场景

# 1. 块恢复概述
块恢复是指恢复损坏的数据块，不影响其他数据块。

# 2. 检测损坏块
SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
4 100 1 1234567 FRACTURED

# 3. 使用RMAN恢复块
RMAN> blockrecover datafile 4 block 100;

Starting blockrecover at 2026-03-31 10:00:00
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile backup set
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_full_20260331_100000
channel ORA_DISK_1: piece handle=/backup/FGEDUDB_full_20260331_100000 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:00:15

# 4. 恢复多个损坏块
RMAN> blockrecover corruption list;

Starting blockrecover at 2026-03-31 10:00:00
using channel ORA_DISK_1

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

# 5. 验证块恢复
SQL> select * from v$database_block_corruption;

no rows selected

# 6. 使用DBVERIFY检测块损坏
$ 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

DBVERIFY – Verification complete

Total Pages Examined : 64000
Total Pages Processed (Data) : 56000
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8000
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 : 1234567 (0.1234567)

3.1.4 时间点恢复场景

# 1. 时间点恢复概述
时间点恢复是指将数据库恢复到指定的时间点，通常用于误操作恢复。

# 2. 恢复到指定时间点
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 5368709120 bytes

RMAN> restore database until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

Starting restore at 2026-03-31 10:00:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK

RMAN> recover database until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

Starting recover at 2026-03-31 10:05:00
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 100 is already on disk as file /oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc
archived log file name=/oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc thread=1 sequence=100
media recovery complete, elapsed time: 00:00:30
Finished recover at 2026-03-31 10:05:30

RMAN> alter database open resetlogs;

Statement processed

# 3. 恢复到指定SCN
RMAN> restore database until scn 1234567;

RMAN> recover database until scn 1234567;

RMAN> alter database open resetlogs;

# 4. 恢复到指定日志序列
RMAN> restore database until sequence 100 thread 1;

RMAN> recover database until sequence 100 thread 1;

RMAN> alter database open resetlogs;

# 5. 表空间时间点恢复（TSPITR）
RMAN> recover tablespace users until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)” auxiliary destination ‘/tmp/aux’;

Starting recover at 2026-03-31 10:00:00
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified time point

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID=’aux’

initialization parameters used for automatic instance:
db_name=FGEDUDB
db_unique_name=aux_pitr_FGEDUDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle/app/oracle
_system_trig_enabled=FALSE
db_domain=world
sga_target=1G
processes=200
db_create_file_dest=/tmp/aux
log_archive_dest_1=’location=/tmp/aux’

starting up automatic instance FGEDUDB

Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 8710176 bytes
Variable Size 281018368 bytes
Database Buffers 784334848 bytes
Redo Buffers 8069120 bytes

Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2026-03-31 10:00:00
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=162 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /backup/FGEDUDB_ctrl_20260331_100000
channel ORA_AUX_DISK_1: piece handle=/backup/FGEDUDB_ctrl_20260331_100000 tag=TAG20260331T100000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
Finished restore at 2026-03-31 10:00:05

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone database ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/aux/FGEDUDB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2026-03-31 10:00:10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/aux/FGEDUDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/aux/FGEDUDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/aux/FGEDUDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/FGEDUDB_full_20260331_100000
channel ORA_AUX_DISK_1: piece handle=/backup/FGEDUDB_full_20260331_100000 tag=TAG20260331T100000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:05:00
Finished restore at 2026-03-31 10:05:10

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;
# Online the datafiles restored or moved
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 3 online”;
sql clone “alter database datafile 4 online”;
# recover and open resetlogs
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “USERS” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 4 online

Starting recover at 2026-03-31 10:05:15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 100 is already on disk as file /oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc
archived log file name=/oracle/app/oracle/fast_recovery_area/FGEDUDB/archivelog/2026_03_31/o1_mf_1_100_abc123.arc thread=1 sequence=100
media recovery complete, elapsed time: 00:00:30
Finished recover at 2026-03-31 10:05:45

database opened

contents of Memory Script:
{
# export the tables to be imported
export clone
tablespace “USERS”
until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”
datapump destination ‘/tmp/aux’
dump file ‘tspitr_dump_12345.dmp’;
# shutdown clone before import
shutdown clone immediate
# import the tables
import
tablespace “USERS”
dump file ‘/tmp/aux/tspitr_dump_12345.dmp’
from user “SCOTT”
to user “SCOTT”;
}
executing Memory Script

Export: Release 19.0.0.0.0 – Production on Tue Mar 31 10:05:50 2026

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYS”.”SYS_EXPORT_TABLESPACE_01″:
tablespace=”USERS”
until time “to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”
datapump destination ‘/tmp/aux’
dump file ‘tspitr_dump_12345.dmp’
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SCOTT”.”EMP” 8.5 KB 14 rows
Master table “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/tmp/aux/tspitr_dump_12345.dmp
Job “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully completed at Tue Mar 31 10:06:00 2026 elapsed 0 00:00:10

database closed
database dismounted
Oracle instance shut down

Import: Release 19.0.0.0.0 – Production on Tue Mar 31 10:06:05 2026

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Master table “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TABLESPACE_01″:
tablespace=”USERS”
dump file ‘/tmp/aux/tspitr_dump_12345.dmp’
from user “SCOTT”
to user “SCOTT”
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”EMP” 8.5 KB 14 rows
Job “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully completed at Tue Mar 31 10:06:10 2026 elapsed 0 00:00:05

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed

auxiliary instance file /tmp/aux/FGEDUDB/datafile/o1_mf_system_abc123.dbf deleted
auxiliary instance file /tmp/aux/FGEDUDB/datafile/o1_mf_undotbs1_abc124.dbf deleted
auxiliary instance file /tmp/aux/FGEDUDB/datafile/o1_mf_users_abc125.dbf deleted
auxiliary instance file /tmp/aux/FGEDUDB/datafile/o1_mf_temp_abc126.tmp deleted
auxiliary instance file /tmp/aux/FGEDUDB/controlfile/o1_mf_abc127.ctl deleted
auxiliary instance file /tmp/aux/FGEDUDB/onlinelog/o1_mf_1_abc128.log deleted
auxiliary instance file /tmp/aux/FGEDUDB/onlinelog/o1_mf_2_abc129.log deleted
auxiliary instance file /tmp/aux/FGEDUDB/onlinelog/o1_mf_3_abc130.log deleted
Finished recover at 2026-03-31 10:06:15

3.2 恢复场景监控

3.2.1 配置恢复场景监控脚本

# 1. 创建恢复场景监控脚本
$ vi /home/oracle/scripts/monitor_recovery.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/recovery_monitor”
mkdir -p $LOG_DIR

DATE=$(date +%Y%m%d)
LOG_FILE=”$LOG_DIR/recovery_monitor_$DATE.log”

echo “$(date): Starting recovery monitor…” > $LOG_FILE

# 检查恢复状态
sqlplus -s / as sysdba <> $LOG_FILE
set linesize 200
set pagesize 100

— 检查实例状态
select instance_name, status, startup_time from v\$instance;

— 检查数据库状态
select name, open_mode, database_role from v\$database;

— 检查数据文件状态
select file#, status, error from v\$datafile_header where status != ‘ONLINE’;

— 检查块损坏
select * from v\$database_block_corruption;

— 检查恢复进度
select * from v\$recovery_progress;

exit
EOF

echo “$(date): Recovery monitor completed.” >> $LOG_FILE

# 发送报告
mail -s “Recovery Monitor Report – $DATE” admin@fgedu.net.cn < $LOG_FILE # 2. 设置脚本权限 $ chmod +x /home/oracle/scripts/monitor_recovery.sh # 3. 测试脚本 $ /home/oracle/scripts/monitor_recovery.sh # 4. 设置定期监控 $ crontab -e # 每小时监控恢复状态 0 * * * * /home/oracle/scripts/monitor_recovery.sh >> /home/oracle/recovery_monitor/cron.log 2>&1

3.3 恢复场景故障处理

3.3.1 恢复场景常见问题

# 问题1：恢复失败

# 错误信息
RMAN-06026: some targets not found – aborting restore

# 解决方案
# 1. 检查备份是否存在
RMAN> list backup;

# 2. 检查备份有效性
RMAN> validate backupset ;

# 3. 使用crosscheck检查
RMAN> crosscheck backup;

# 4. 重新备份
RMAN> backup database;

# 问题2：归档日志丢失

# 错误信息
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 100 and starting SCN of 1234567

# 解决方案
# 1. 检查归档日志是否存在
RMAN> list archivelog all;

# 2. 如果归档日志丢失，使用不完全恢复
RMAN> recover database until sequence 99 thread 1;

# 3. 打开数据库
RMAN> alter database open resetlogs;

# 问题3：块损坏无法恢复

# 错误信息
ORA-01578: ORACLE data block corrupted (file # 4, block # 100)

# 解决方案
# 1. 检查损坏块
SQL> select * from v$database_block_corruption;

# 2. 尝试块恢复
RMAN> blockrecover datafile 4 block 100;

# 3. 如果块恢复失败，使用表空间恢复
RMAN> sql ‘alter tablespace users offline immediate’;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online’;

# 4. 如果表空间恢复失败，使用全库恢复
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

风哥提示：恢复场景是保障数据库可用性和数据安全的重要手段，建议掌握各种恢复场景。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 恢复场景案例

在生产环境中使用恢复场景的完整案例：

4.1.1 场景描述

某企业生产数据库出现数据文件损坏，需要使用恢复场景进行恢复。

4.1.2 分析步骤

# 1. 创建恢复场景自动化脚本
$ vi /home/oracle/scripts/auto_recovery.sh

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

LOG_DIR=”/home/oracle/recovery”
mkdir -p $LOG_DIR

DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/recovery_$DATE.log”

echo “========================================” > $LOG_FILE
echo “Recovery Report” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE

# 检查数据库状态
echo “” >> $LOG_FILE
echo “Checking database status…” >> $LOG_FILE
sqlplus -s / as sysdba <> $LOG_FILE
set linesize 200
set pagesize 100

select name, open_mode, database_role from v\$database;
select file#, status, error from v\$datafile_header;
select * from v\$database_block_corruption;
EOF

# 执行恢复
echo “” >> $LOG_FILE
echo “Starting recovery…” >> $LOG_FILE
rman target / <> $LOG_FILE
run {
allocate channel ch1 type disk;
restore database;
recover database;
release channel ch1;
}
EOF

# 验证恢复
echo “” >> $LOG_FILE
echo “Verifying recovery…” >> $LOG_FILE
sqlplus -s / as sysdba <> $LOG_FILE
set linesize 200
set pagesize 100

select name, open_mode from v\$database;
select file#, status from v\$datafile_header;
select count(*) from v\$database_block_corruption;
EOF

echo “” >> $LOG_FILE
echo “Recovery completed at $(date)” >> $LOG_FILE

# 发送报告
mail -s “Recovery Report – $DATE” admin@fgedu.net.cn < $LOG_FILE # 2. 设置脚本权限 $ chmod +x /home/oracle/scripts/auto_recovery.sh # 3. 测试脚本 $ /home/oracle/scripts/auto_recovery.sh

4.2 恢复场景故障处理

在恢复场景故障处理过程中的方法和技巧：

4.2.1 故障处理流程

# 恢复场景故障处理流程

# 1. 识别问题
# – 检查数据库状态
# – 检查数据文件状态
# – 检查块损坏

# 2. 收集信息
# – 检查备份状态
# – 检查归档日志
# – 检查恢复进度

# 3. 分析原因
# – 分析故障类型
# – 分析恢复选项
# – 制定恢复方案

# 4. 制定方案
# – 选择恢复方法
# – 准备恢复资源
# – 执行恢复操作

# 5. 执行修复
# – 执行恢复操作
# – 验证恢复结果
# – 记录恢复过程

# 示例：处理数据文件损坏

# 1. 识别问题
SQL> select file#, status, error from v$datafile_header;

# 2. 收集信息
RMAN> list backup of datafile 4;

# 3. 分析原因
— 数据文件损坏

# 4. 制定方案
— 使用RMAN恢复数据文件

# 5. 执行修复
RMAN> sql ‘alter database datafile 4 offline’;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> sql ‘alter database datafile 4 online’;

4.3 恢复场景优化

优化恢复场景配置的最佳实践：

4.3.1 优化恢复场景管理

# 1. 创建恢复场景管理存储过程
SQL> create or replace procedure manage_recovery_results(
p_recovery_date in date,
p_recovery_type in varchar2,
p_recovery_status in varchar2
) as
begin
dbms_output.put_line(‘— Recovery Results Management —‘);
dbms_output.put_line(‘Recovery Date: ‘ || to_char(p_recovery_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Recovery Type: ‘ || p_recovery_type);
dbms_output.put_line(‘Recovery Status: ‘ || p_recovery_status);

— 可以扩展为记录到表中
end manage_recovery_results;
/

Procedure created.

# 2. 执行管理存储过程
SQL> set serveroutput on
SQL> exec manage_recovery_results(sysdate, ‘DATABASE’, ‘SUCCESS’);

— Recovery Results Management —
Recovery Date: 2026-03-31 10:00:00
Recovery Type: DATABASE
Recovery Status: SUCCESS

PL/SQL procedure successfully completed.

# 3. 创建恢复场景历史表
SQL> create table recovery_history (
id number primary key,
recovery_date date,
recovery_type varchar2(50),
recovery_status varchar2(50),
recovery_duration number,
notes varchar2(4000)
);

Table created.

SQL> create sequence recovery_history_seq;

Sequence created.

# 4. 记录恢复历史
SQL> insert into recovery_history values (
recovery_history_seq.nextval,
sysdate,
‘DATABASE’,
‘SUCCESS’,
300,
‘Full database recovery’
);

1 row created.

SQL> commit;

Commit complete.

# 5. 查看恢复历史趋势
SQL> column recovery_date format a20
SQL> select recovery_date, recovery_type, recovery_status, recovery_duration
from recovery_history
order by recovery_date desc
fetch first 12 rows only;

RECOVERY_DATE RECOVERY_TYPE RECOVERY_STATUS RECOVERY_DURATION
——————– ————- ————— —————–
2026-03-31 10:00:00 DATABASE SUCCESS 300
2026-03-30 10:00:00 TABLESPACE SUCCESS 120
2026-03-29 10:00:00 BLOCK SUCCESS 30

生产环境建议：恢复场景优化需要建立完善的管理流程，建议定期备份、测试恢复、记录结果。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 恢复场景总结

Oracle恢复场景是保障数据库可用性和数据安全的重要手段，具有以下特点：

多种类型：支持多种恢复类型
灵活选择：根据故障类型选择恢复方法
数据安全：保障数据安全和可用性
快速恢复：快速恢复数据库服务
完整方案：提供完整的恢复方案

5.2 恢复场景检查清单

Oracle恢复场景检查清单：

定期备份：定期进行备份
测试恢复：定期测试恢复方案
文档记录：记录恢复过程
快速响应：快速响应故障
经验总结：总结恢复经验
定期检查：定期检查备份有效性

5.3 恢复场景工具推荐

Oracle恢复场景工具推荐：

RMAN：Oracle恢复管理器
Data Pump：数据泵导入导出
Flashback：闪回技术
DBVERIFY：数据库验证工具
Shell脚本：自动化恢复过程

风哥提示：恢复场景是保障数据库可用性和数据安全的重要手段，建议掌握各种恢复场景。from:www.itpux.com www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

Oracle教程FG450-恢复场景汇总

Part01-基础概念与理论知识

1.1 恢复场景的概念

1.2 恢复类型

1.3 恢复场景使用

Part02-生产环境规划与建议

2.1 恢复场景规划

2.2 恢复场景分类

2.3 恢复场景最佳实践

Part03-生产环境项目实施方案

3.1 恢复场景配置

3.1.1 实例恢复场景

3.1.2 介质恢复场景

3.1.3 块恢复场景

3.1.4 时间点恢复场景

3.2 恢复场景监控

3.2.1 配置恢复场景监控脚本

3.3 恢复场景故障处理

3.3.1 恢复场景常见问题

Part04-生产案例与实战讲解

4.1 恢复场景案例

4.1.1 场景描述

4.1.2 分析步骤

4.2 恢复场景故障处理

4.2.1 故障处理流程

4.3 恢复场景优化

4.3.1 优化恢复场景管理

Part05-风哥经验总结与分享

5.1 恢复场景总结

5.2 恢复场景检查清单

5.3 恢复场景工具推荐

相关推荐

联系我们