Oracle 闪回性能优化

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

---

1. 概述

闪回（Flashback） 是快速恢复技术[1]：

类型：

• Flashback Query
• Flashback Version Query
• Flashback Transaction Query
• Flashback Table
• Flashback Drop
• Flashback Database

---

2. Flashback Database

2.1 配置

-- 启用
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

-- FRA 大小
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

2.2 闪回日志

SELECT 
  oldest_flashback_scn,
  oldest_flashback_time,
  retention_target
FROM v$flashback_database_log;

2.3 闪回

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
ALTER DATABASE OPEN RESETLOGS;

2.4 性能

• 闪回日志写入
• 影响性能 5-10%

---

3. Flashback Query

3.1 基本用法

-- 查询过去数据
SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE id = 100;

3.2 SCN

SELECT * FROM employees AS OF SCN 1234567 WHERE id = 100;

3.3 性能

• 依赖 Undo
• undo_retention 影响

---

4. Flashback Table

4.1 启用行迁移

ALTER TABLE employees ENABLE ROW MOVEMENT;

4.2 闪回

FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
FLASHBACK TABLE employees TO SCN 1234567;

---

5. Flashback Drop

-- 误删除表
DROP TABLE employees;

-- 闪回
FLASHBACK TABLE employees TO BEFORE DROP;

-- 重命名
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

---

6. 性能优化

6.1 Undo 优化

-- undo_retention
ALTER SYSTEM SET undo_retention = 3600;

-- Undo 表空间
ALTER TABLESPACE undotbs1 ADD DATAFILE '...' SIZE 5G;

-- GUARANTEE
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

6.2 闪回日志

-- 闪回保留
ALTER SYSTEM SET db_flashback_retention_target = 1440;  -- 分钟（1 天）

6.3 监控

-- Undo 使用
SELECT 
  tablespace_name,
  status,
  SUM(bytes) / 1024 / 1024 AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;

-- 闪回日志
SELECT * FROM v$flashback_database_log;

---

7. Flashback Database 性能

7.1 影响因素

• FRA 空间
• 闪回日志写入
• 数据变更率

7.2 优化

-- 1. 足够 FRA
ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

-- 2. 保留时间
ALTER SYSTEM SET db_flashback_retention_target = 1440;

-- 3. 监控
SELECT 
  estimated_flashback_size / 1024 / 1024 AS mb
FROM v$flashback_database_log;

7.3 关闭某些表空间

ALTER TABLESPACE users FLASHBACK OFF;

---

8. Flashback Data Archive（11g+）

8.1 创建

-- 表空间
CREATE FLASHBACK ARCHIVE DEFAULT fla1 
  TABLESPACE users 
  QUOTA 10G 
  RETENTION 1 YEAR;

-- 启用
ALTER TABLE employees FLASHBACK ARCHIVE fla1;

8.2 优势

• 长期历史
• 不依赖 Undo
• 自动管理

8.3 查询

SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' YEAR)
WHERE id = 100;

---

9. 应用场景

9.1 误操作恢复

-- 误 UPDATE
UPDATE employees SET salary = 0;
COMMIT;

-- 闪回
FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

9.2 误删除

DROP TABLE important_table;
FLASHBACK TABLE important_table TO BEFORE DROP;

9.3 数据对比

SELECT 
  curr.id,
  curr.salary AS current_sal,
  hist.salary AS old_sal
FROM employees curr
JOIN employees AS OF TIMESTAMP (SYSDATE - 1) hist
  ON curr.id = hist.id
WHERE curr.salary != hist.salary;

9.4 数据库回退

-- 测试后回退
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);

---

10. 常见坑与排错

10.1 ORA-08180: 无快照

-- Undo 数据已过期
-- 1. 增大 undo_retention
-- 2. 增大 Undo 表空间
-- 3. Flashback Data Archive

10.2 ORA-01555: 快照过旧

-- 同上

10.3 Flashback Database 失败

-- 1. 检查 FRA 空间
-- 2. 检查闪回日志
-- 3. 检查是否开启
SELECT flashback_on FROM v$database;

---

11. 最佳实践

1. 关键表启用 Flashback Archive：长期
2. undo_retention 合理：业务
3. FRA 足够：闪回 DB
4. ENABLE ROW MOVEMENT：Flashback Table
5. 监控 Undo：避免 1555
6. 定期测试：验证
7. 闪回优先：替代 PITR
8. 关闭非关键表空间：性能
9. 文档化：流程
10. 结合备份：综合

---

12. 参考资料

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