DB / Oracle / 备份与迁移

Oracle Flashback 技术全集

2026.09.06

Oracle Flashback 技术全集

适用版本：Oracle Database 9i / 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

详细见：Oracle 闪回技术。

2. Flashback Query

2.1 AS OF TIMESTAMP

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

2.2 AS OF SCN

SELECT current_scn FROM v$database;
SELECT * FROM employees AS OF SCN 1234567 WHERE id = 100;

2.3 恢复误删

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

3. Flashback Version Query

3.1 查询历史版本

SELECT 
  versions_xid, 
  versions_starttime, 
  versions_endtime,
  versions_operation,
  salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
  (SYSTIMESTAMP - INTERVAL '1' DAY) AND SYSTIMESTAMP
WHERE id = 100;

3.2 字段

  • versions_xid：事务 ID
  • versions_starttime：开始时间
  • versions_endtime：结束时间
  • versions_operation：操作（I/U/D）

4. Flashback Transaction Query

4.1 查询事务

SELECT 
  xid, operation, undo_sql, 
  table_name, table_owner
FROM flashback_transaction_query
WHERE xid = HEXTORAW('04000F00A12345');

4.2 恢复 SQL

- undo_sql 字段
- 反向操作

5. Flashback Table

5.1 启用

ALTER TABLE employees ENABLE ROW MOVEMENT;

5.2 闪回

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

FLASHBACK TABLE employees TO SCN 1234567;

FLASHBACK TABLE employees, departments TO TIMESTAMP 
  (SYSTIMESTAMP - INTERVAL '1' HOUR);

5.3 限制

  • DDL 后不可
  • 表空间未减少
  • 约束保留

6. Flashback Drop

6.1 回收站

SHOW RECYCLEBIN;
SELECT * FROM dba_recyclebin;
SELECT * FROM user_recyclebin;

6.2 恢复

FLASHBACK TABLE employees TO BEFORE DROP;

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

6.3 清空

PURGE TABLE employees;
PURGE RECYCLEBIN;
PURGE DBA_RECYCLEBIN;
PURGE TABLESPACE users;

6.4 禁用

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF DEFERRED;

7. Flashback Database

7.1 启用

SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

7.2 参数

ALTER SYSTEM SET db_flashback_retention_target = 1440;
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

7.3 查看

SELECT flashback_on, oldest_flashback_time, oldest_flashback_scn
FROM v$database;

7.4 闪回

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

8. Flashback Data Archive（Total Recall）

8.1 创建

CREATE FLASHBACK ARCHIVE DEFAULT fla1
  TABLESPACE users
  QUOTA 10G
  RETENTION 1 YEAR;

8.2 启用

ALTER TABLE employees FLASHBACK ARCHIVE fla1;

8.3 查询

SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MONTH);

8.4 停止

ALTER TABLE employees NO FLASHBACK ARCHIVE;

9. Flashback 与 PITR

9.1 Flashback 优先

- 快
- 不影响其他
- 无需 RESETLOGS

9.2 RMAN PITR

- 备份恢复
- RESETLOGS
- 最后手段

详细见：Oracle 数据库 PITR 时间点恢复。

10. Undo 依赖

10.1 Undo Retention

ALTER SYSTEM SET undo_retention = 3600;

10.2 GUARANTEE

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

详细见：Oracle-Undo深度解析。

11. 监控

11.1 Flashback Database

SELECT 
  oldest_flashback_scn, 
  oldest_flashback_time,
  flashback_size,
  retention_target
FROM v$flashback_database_log;

SELECT * FROM v$flashback_database_stat;

11.2 回收站

SELECT owner, object_name, original_name, type, droptime
FROM dba_recyclebin;

11.3 FRA

SELECT 
  name, 
  space_limit / 1024 / 1024 / 1024 AS limit_gb,
  space_used / 1024 / 1024 / 1024 AS used_gb,
  space_reclaimable / 1024 / 1024 / 1024 AS reclaimable_gb
FROM v$recovery_file_dest;

12. 实战案例

12.1 误 DELETE

1. 13:00 误 DELETE FROM employees;
2. 14:00 发现
3. Flashback Query 查 12:59 数据
4. INSERT 恢复

12.2 误 DROP TABLE

1. DROP TABLE important;
2. 检查回收站
3. FLASHBACK TABLE important TO BEFORE DROP;

12.3 误 TRUNCATE

1. TRUNCATE 不能 Flashback Table
2. 必须 Flashback Database 或 PITR

12.4 误 UPDATE

1. UPDATE employees SET salary = 0;
2. Flashback Table 恢复

13. 常见坑与排错

13.1 ORA-08180

- 快照太旧
- Undo 不足
- 增大 undo_retention

13.2 ORA-01466

- DDL 后无法 Flashback
- 表结构改变

13.3 ORA-38705

- Flashback Database 未启用
- 启用 Flashback

13.4 ORA-38712

- SCNs 范围外
- 增大 retention

14. 性能影响

14.1 Flashback Database

- RVWR 进程
- FRA 写入
- 性能轻微影响

详细见：Oracle 闪回性能优化。

14.2 Flashback Data Archive

- 写入额外
- 历史保存
- 空间占用

15. 最佳实践

  1. Flashback Database：快速恢复
  2. Undo Retention：查询能力
  3. GUARANTEE：关键表
  4. 回收站：DROP 恢复
  5. Data Archive：长期历史
  6. 优先 Flashback：快
  7. FRA 充足：空间
  8. 监控空间：避免满
  9. 测试恢复：可行
  10. 文档化：策略

16. 参考资料

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