Switchover (計劃性角色互換,零資料遺失)
Primary → Standby
-- 1. 確認可切換
SELECT switchover_status FROM v$database; -- 應為 TO STANDBY
-- 2. 提交切換
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
-- 3. 原 Primary 以 Standby 身份啟動至 MOUNT
STARTUP MOUNT;
Standby → Primary
-- 4. 確認可切換
SELECT switchover_status FROM v$database; -- 應為 TO PRIMARY
-- 5. 提交切換並開啟
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;
驗證
-- 原 Primary
SELECT database_role FROM v$database; -- PHYSICAL STANDBY
-- 新 Primary
SELECT database_role FROM v$database; -- PRIMARY
-- 檢查 log_archive_dest_2 參數指向舊 Primary
ALTER SYSTEM SWITCH LOGFILE;
SELECT MAX(sequence#) FROM v$archived_log; -- 兩邊序號一致
Failover (非計劃性,Primary 故障不可恢復)
前置檢查
-- 1. 確認無歸檔缺口
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;
-- 若有缺口,從 Primary 複製缺失歸檔至 Standby,並註冊
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec';
-- 2. 確認歸檔完整性
SELECT UNIQUE thread# AS thread, MAX(sequence#) OVER (PARTITION BY thread#) AS last
FROM v$archived_log;
-- 若 Primary 有更新序號,同樣複製並註冊
執行 Failover
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;
驗證
SELECT database_role FROM v$database; -- PRIMARY