DB / Oracle / 备份与迁移

Oracle 备份恢复实战：常见场景与脚本

2026.09.06

Oracle 备份恢复实战：常见场景与脚本

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

本文总结 Oracle 备份恢复常见场景与脚本[1]：

2. 完全恢复场景

2.1 数据文件丢失

-- 1. 检查损坏文件
SELECT file#, status, name FROM v$datafile WHERE status='RECOVER';

-- 2. 离线损坏文件
ALTER DATABASE DATAFILE 5 OFFLINE;

-- 3. 打开数据库
ALTER DATABASE OPEN;

-- 4. 恢复数据文件
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;

-- 5. 在线
ALTER DATABASE DATAFILE 5 ONLINE;

2.2 表空间丢失

-- 1. 离线表空间
ALTER TABLESPACE users OFFLINE IMMEDIATE;

-- 2. 恢复
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;

-- 3. 在线
ALTER TABLESPACE users ONLINE;

2.3 控制文件丢失

-- 1. 启动到 nomount
STARTUP NOMOUNT;

-- 2. 恢复控制文件
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- 3. Mount
RMAN> ALTER DATABASE MOUNT;

-- 4. 恢复数据库
RMAN> RECOVER DATABASE;

-- 5. 打开 RESETLOGS
RMAN> ALTER DATABASE OPEN RESETLOGS;

2.4 Redo Log 丢失

-- 1. 检查状态
SELECT group#, status FROM v$log;

-- 2. 如果 INACTIVE
ALTER DATABASE CLEAR LOGFILE GROUP 3;

-- 3. 如果 ACTIVE/CURRENT
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
ALTER DATABASE OPEN RESETLOGS;

2.5 参数文件丢失

-- 1. 启动到 nomount
STARTUP NOMOUNT;

-- 2. 恢复 SPFILE
RMAN> RESTORE SPFILE FROM AUTOBACKUP;

-- 3. 重启
SHUTDOWN IMMEDIATE;
STARTUP;

3. 不完全恢复场景

3.1 时间点恢复

-- 1. 关闭数据库
SHUTDOWN IMMEDIATE;

-- 2. 启动到 mount
STARTUP MOUNT;

-- 3. 恢复到指定时间
RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2026-07-21 14:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

-- 4. 打开 RESETLOGS
ALTER DATABASE OPEN RESETLOGS;

3.2 SCN 恢复

RMAN> RUN {
  SET UNTIL SCN 1234567;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

3.3 CANCEL 恢复

-- 1. 启动到 mount
STARTUP MOUNT;

-- 2. 恢复（手动提供归档）
RECOVER DATABASE UNTIL CANCEL;
-- 输入归档日志路径，最后 CANCEL

-- 3. 打开
ALTER DATABASE OPEN RESETLOGS;

3.4 表删除恢复（12c+）

-- 19c+ 表级恢复
RMAN> RECOVER TABLE scott.employees 
  UNTIL TIME "TO_DATE('2026-07-21 14:00:00','YYYY-MM-DD HH24:MI:SS')" 
  AUXILIARY DESTINATION '/u01/aux' 
  REMAP TABLE scott.employees:employees_recovered;

4. 块介质恢复（BMR）

4.1 检查损坏块

-- 查看损坏块
SELECT * FROM v$database_block_corruption;

-- RMAN 检查
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 5;

4.2 恢复损坏块

-- 单块恢复
RMAN> RECOVER DATAFILE 5 BLOCK 123;

-- 恢复所有损坏块
RMAN> RECOVER CORRUPTION LIST;

4.3 优势

  • 在线恢复
  • 仅恢复损坏块
  • 业务影响最小

5. 自动备份脚本

5.1 RMAN 全备份脚本

#!/bin/bash
# rman_full_backup.sh

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/u01/backup

rman target / log=${BACKUP_DIR}/backup_${DATE}.log <<EOF
RUN {
  CONFIGURE BACKUP OPTIMIZATION ON;
  CONFIGURE CONTROLFILE AUTOBACKUP ON;
  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUP_DIR}/%F';
  
  CROSSCHECK BACKUP;
  DELETE NOPROMPT EXPIRED BACKUP;
  DELETE NOPROMPT OBSOLETE;
  
  BACKUP INCREMENTAL LEVEL 0 DATABASE 
    FORMAT '${BACKUP_DIR}/db_%d_%T_%s_%p.bkp' 
    TAG 'FULL_BACKUP_${DATE}';
    
  BACKUP ARCHIVELOG ALL 
    FORMAT '${BACKUP_DIR}/arch_%d_%T_%s_%p.bkp' 
    DELETE INPUT 
    NOT BACKED UP 1 TIMES;
    
  SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
  
  BACKUP CURRENT CONTROLFILE 
    FORMAT '${BACKUP_DIR}/ctrl_%d_%T_%s_%p.bkp';
    
  BACKUP SPFILE 
    FORMAT '${BACKUP_DIR}/spfile_%d_%T_%s_%p.bkp';
}
EXIT;
EOF

# 保留 7 天
find $BACKUP_DIR -name "*.bkp" -mtime +7 -delete
find $BACKUP_DIR -name "*.log" -mtime +30 -delete

5.2 增量备份脚本

#!/bin/bash
# rman_incr_backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/u01/backup

rman target / log=${BACKUP_DIR}/incr_${DATE}.log <<EOF
RUN {
  BACKUP INCREMENTAL LEVEL 1 DATABASE 
    FORMAT '${BACKUP_DIR}/incr_db_%d_%T_%s_%p.bkp' 
    TAG 'INCR_BACKUP_${DATE}';
    
  BACKUP ARCHIVELOG ALL 
    FORMAT '${BACKUP_DIR}/arch_%d_%T_%s_%p.bkp' 
    DELETE INPUT 
    NOT BACKED UP 1 TIMES;
    
  SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
  
  DELETE NOPROMPT OBSOLETE;
}
EXIT;
EOF

5.3 Crontab 配置

# 周日 2:00 全备份
0 2 * * 0 /u01/scripts/rman_full_backup.sh

# 周一至周六 2:00 增量
0 2 * * 1-6 /u01/scripts/rman_incr_backup.sh

# 每小时归档
0 * * * * /u01/scripts/archive_backup.sh

6. 恢复演练脚本

6.1 测试环境恢复

#!/bin/bash
# restore_test.sh

# 恢复到测试环境
export ORACLE_SID=testdb

rman target / <<EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

RUN {
  RESTORE DATABASE;
  RECOVER DATABASE UNTIL SEQUENCE 100 THREAD 1;
}

ALTER DATABASE OPEN RESETLOGS;
EXIT;
EOF

# 验证数据
sqlplus / as sysdba <<EOF
SELECT COUNT(*) FROM scott.employees;
SELECT file_name, status FROM dba_data_files;
EXIT;
EOF

7. Data Guard 备库备份

7.1 备库备份（不影响主库）

-- 在备库执行
RMAN> BACKUP DATABASE;

-- 备份元数据导入到主库
RMAN> CATALOG START WITH '/u01/backup/';

7.2 主库恢复使用备库备份

-- 主库使用备库的备份恢复
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

8. 常见错误处理

8.1 ORA-01113: 文件需要介质恢复

-- 1. 检查文件状态
SELECT file#, status, name FROM v$datafile;

-- 2. 恢复
RMAN> RECOVER DATAFILE 5;

-- 3. 在线
ALTER DATABASE DATAFILE 5 ONLINE;

8.2 ORA-01194: 文件需要更多恢复

-- 1. 检查归档
SELECT sequence#, archived FROM v$archived_log ORDER BY sequence# DESC;

-- 2. 提供缺失归档
RMAN> RECOVER DATABASE;

-- 3. 不完全恢复（如有必要）
RMAN> RUN {
  SET UNTIL SEQUENCE xxx THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

8.3 ORA-19870: 备份片段无效

-- 1. 检查备份
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE EXPIRED BACKUP;

-- 2. 重新备份
RMAN> BACKUP DATABASE;

8.4 ORA-19612: 加密备份无法解密

-- 设置解密密码
RMAN> SET DECRYPTION IDENTIFIED BY ******
RMAN> RESTORE DATABASE;

9. 最佳实践

  1. 定期备份：周日全备+日常增量
  2. 归档实时备份：减少数据丢失
  3. 备份验证：RESTORE DATABASE VALIDATE
  4. 恢复演练：定期测试
  5. 多目的地备份：避免单点
  6. 备份监控：dba_datapump_jobs
  7. 保留策略：7-30 天
  8. 压缩加密：节省空间+保护数据
  9. 自动化脚本：减少人工错误
  10. 应急文档：DBA 手册

10. 参考资料

[1] Oracle Database Backup and Recovery User’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/

[2] Oracle Database Backup and Recovery Reference 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/