如果切換環境是RAC，無論主或備，切換前均只保留一個節點，切換後正常啓動即可。

一、當前架構檢查 (A→B→C)

1. 檢查主庫A狀態

-- 在主庫A執行
SELECT DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS, PROTECTION_MODE FROM V$DATABASE;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

預期結果:

DATABASE_ROLE = 'PRIMARY'
OPEN_MODE = 'READ WRITE'
SWITCHOVER_STATUS = 'TO STANDBY' 或 SESSION ACTIVE

2. 檢查備庫B狀態

-- 在備庫B執行
SELECT DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS FROM V$DATABASE;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES';

預期結果:

DATABASE_ROLE = 'PHYSICAL STANDBY'
OPEN_MODE = 'READ ONLY'
與主庫A的日誌序列號差距應小於3

3. 檢查備庫C狀態

-- 在備庫C執行
SELECT DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS FROM V$DATABASE;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES';

預期結果:

DATABASE_ROLE = 'PHYSICAL STANDBY'
OPEN_MODE = 'READ ONLY'
與備庫B的日誌序列號差距應小於3

二、切換為C→B→A架構

1. 將C庫轉換MOUNT

-- 停止當前恢復進程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

2. 將A庫轉換為B的備庫

-- 停止當前恢復進程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 檢查切換狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

-- 轉換為B的備庫
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- 啓動新的恢復進程(指向B庫)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=B_primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=B' SCOPE=BOTH;
ALTER DATABASE MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

3. 將B庫提升為主庫

-- 停止當前恢復進程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 檢查切換狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

-- 切換為主庫
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

-- 啓動新主庫
ALTER DATABASE OPEN;

-- 驗證主庫狀態
SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

預期結果:

DATABASE_ROLE = 'PRIMARY'
OPEN_MODE = 'READ WRITE'

A和C 都是MOUNT 狀態，且都是 B 的從庫，查看日誌，是否同步正常

ALTER SYSTEM SWITCH LOGFILE;

4. 將B庫降為備庫

-- 停止當前恢復進程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 檢查切換狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

-- 轉換為C的備庫
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- 啓動新的恢復進程(指向C庫)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=C_primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=C' SCOPE=BOTH;
ALTER DATABASE MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

5. 將C庫提升為主庫

-- 停止當前恢復進程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 檢查切換狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

-- 切換為主庫
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

-- 啓動新主庫
ALTER DATABASE OPEN;

-- 驗證主庫狀態
SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

預期結果:

DATABASE_ROLE = 'PRIMARY'
OPEN_MODE = 'READ WRITE'

6. 驗證新架構(C→B→A)

在C庫(新主庫)檢查:

SELECT DEST_ID, STATUS, ERROR, TYPE, DESTINATION FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'INACTIVE';

在B庫檢查:

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC;
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

在A庫檢查:

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC;
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;