配置和測試了Oracle 23 ai的Flashback Log Placement后，

剛好身邊11g,19c的環境都在，還是把從前的flashback整理下，溫故知新，循序漸進。

?一、閃回技術

Flashback Database 允許將整個數據庫回退到過去的某個時間點/SCN，基于以下核心機制：?

閃回日志 (Flashback Logs)???

閃回恢復區 (Flash Recovery Area)????

四、開啟Flashback?

??開啟歸檔模式?：

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;-- 1. 設置閃回恢復區
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/flash_recovery_area' SCOPE=BOTH;
-- 根據磁盤空間調整
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH;-- 2. 設置閃回保留時間（默認 1440 分鐘）
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;-- 3. 啟用閃回（MOUNT 狀態下執行）
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;-- 4. 驗證狀態
SELECT LOG_MODE, FLASHBACK_ON FROM V$DATABASE;
LOG_MODE      FLASHBACK_ON
_____________ _______________
ARCHIVELOG    YES

五、數據庫、表、行、事務、閃回的腳本

1）、數據庫級閃回 (FLASHBACK DATABASE)??

?1. 基于時間點閃回

-- 步驟1: 強制日志切換并記錄當前時間點
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT SYSTIMESTAMP AS current_time FROM DUAL; -- 記錄此時間-- 步驟2: 關閉并掛載數據庫
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;-- 步驟3: 執行閃回 (RMAN環境)
RMAN> RUN {SET UNTIL TIME "TO_DATE('2025-06-13 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";FLASHBACK DATABASE;ALTER DATABASE OPEN RESETLOGS;
}

2. 基于 SCN 閃回??

-- 獲取當前SCN并記錄，示例: 7654321
SELECT CURRENT_SCN FROM V$DATABASE;-- 執行閃回 (SQL*Plus)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN 7654321;
ALTER DATABASE OPEN RESETLOGS;

3. 基于還原點閃回??

-- 創建擔保還原點
CREATE RESTORE POINT b_migration GUARANTEE FLASHBACK DATABASE;-- 執行閃回
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT b_migration;
ALTER DATABASE OPEN RESETLOGS;

2）、表級閃回 (FLASHBACK TABLE)??

?1. 恢復誤刪表?

-- 查看回收站
SELECT object_name, original_name, droptime 
FROM user_recyclebin 
WHERE original_name = 'test_EMP';-- 恢復表到刪除前狀態
FLASHBACK TABLE test_EMP TO BEFORE DROP;-- 重命名恢復的表
FLASHBACK TABLE "BIN$skjdsf93jksdf$" TO BEFORE DROP RENAME TO test_EMP_recovered;

?2. 回滾表數據到指定時間點?

-- 啟用行移動
ALTER TABLE test_ORDERS ENABLE ROW MOVEMENT;-- 閃回到特定時間
FLASHBACK TABLE test_ORDERS TO TIMESTAMP TO_TIMESTAMP('2025-06-13 09:00:00', 'YYYY-MM-DD HH24:MI:SS');-- 驗證數據
SELECT COUNT(*) FROM test_ORDERS AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

3. 恢復表到指定SCN??

-- 獲取當前SCN
SELECT CURRENT_SCN FROM V$DATABASE; -- 示例: 2345678-- 執行閃回
FLASHBACK TABLE test_ORDERS TO SCN 2345678;

3）、行級閃回 (FLASHBACK QUERY)??

?1. 查詢歷史數據?

-- 查詢1小時前的數據
SELECT * FROM test_EMP 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
WHERE department_id = 50;-- 查詢特定SCN的數據
SELECT * FROM test_EMP 
AS OF SCN 1234567 
WHERE employee_id = 100;

2. 恢復誤刪數據?

-- 恢復1小時內刪除的數據
INSERT INTO test_EMP
SELECT * FROM test_EMP 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE employee_id NOT IN (SELECT employee_id FROM test_EMP
);

3. 恢復誤更新數據??

-- 恢復被誤更新的薪資
UPDATE test_EMP e
SET salary = (SELECT salary FROM test_EMP AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)WHERE employee_id = e.employee_id
)
WHERE department_id = 60;

4）、事務級閃回 (FLASHBACK TRANSACTION)??

?1. 查詢事務歷史?

-- 查找最近2小時的事務
SELECT xid, start_scn, commit_scn, operation, table_name
FROM flashback_transaction_query
WHERE TABLE_NAME = 'test_ORDERS'
AND START_TIMESTAMP > SYSTIMESTAMP - INTERVAL '120' MINUTE
ORDER BY start_scn DESC;

?2. 撤銷特定事務?

BEGINDBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns    => 1,txnnames   => SYS.ANYDATA.MAKEVARCHAR2('08000F00A8030000'), -- 替換為實際XIDoptions    => DBMS_FLASHBACK.CASCADE);
END;
/

?3. 增補SQL??

SELECT undo_sql 
FROM flashback_transaction_query 
WHERE xid = HEXTORAW('08000F00A8030000');

六、RMAN 空間競爭與清理腳本

閃回恢復區存儲：

#!/bin/bash
export ORACLE_SID=your_sid
export ORACLE_HOME=/u01/app/oracle/product/19.3/dbhome_1# 使用 RMAN 刪除過期備份和歸檔
--可以將命令在sqlplus單獨執行
rman target / << EOF
CROSSCHECK BACKUP;          -- 校驗備份有效性
DELETE NOPROMPT OBSOLETE;   -- 刪除過期的備份
--腳本中的 SYSDATE-7 可根據需求調整歸檔保留天數。
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
EXIT;
EOF# 釋放操作系統空間（清理殘留文件）定期執行（如每天）避免空間不足。
find ${ORACLE_BASE}/flash_recovery_area -name "*.bak" -mtime +7 -exec rm {} \;

七、監控與優化建議?

-- 閃回恢復區使用情況
SELECT * FROM V$RECOVERY_FILE_DEST;  -- 空間占用明細（按文件類型）
SELECT FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE 
FROM V$FLASH_RECOVERY_AREA_USAGE;-- 減少閃回保留時間（按需）調整保留策略
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720;  -- 12小時-- 停止記錄 users 表空間的閃回日志排除非關鍵表空間
ALTER TABLESPACE users FLASHBACK OFF;-- 擴容至100GB擴展閃回恢復區
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G;?

?TIPS：