Switchover，無損切換，通常是使用者手動觸發或者有計劃地讓其自動觸發，如硬體升級等。
步驟：
1、Primary資料庫轉換為StandBy角色
2、StandBy資料庫（之一）轉換為Primary角色
準備工作：
1、檢查待轉換角色的資料庫引數配置正確
2、檢查即將成為Primay的物理Standby伺服器是否是歸檔模式
3、確保所有的歸檔日誌已經傳送到物理StandBy資料庫
主庫可以手動切換一次logfile
Primary>alter system switch logfile;
查詢主庫當前的歸檔日誌sequence#
Primary>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 22
查詢備庫當前的歸檔日誌sequence#
StandBY>select max(sequence#) from v$archived_log; MAX(SEQUENCE#)-------------- 22
兩者相同，代表已經完全同步
然後再暫停備庫的redo應用
StandBy>alter database recover managed standby database cancel; Database altered.
一、查詢是否可以轉換
查詢 主庫 是否支援switchover操作
Primary> select file_name, bytes from dba_temp_files; SWITCHOVER_STATUS ----------------------------------------
如果是SWICHOVER_STATUS顯示為SESSIONS ACTIVE, 說明當前有人連線Primary資料庫
查詢備庫是否支援switchover操作
StandBy> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- NOT ALLOWED
NOT ALLOWED是因為主庫還未切換為standby
二、primary資料庫切換為物理standby
Primary —> StandBy
Database altered.
此時Primary資料庫變為mount狀態
Primary>select open_mode from v$database; OPEN_MODE ---------------------------------------- MOUNTED
角色也變為physical standby
Primary>select database_role from v$database; DATABASE_ROLE -------------------------------- PHYSICAL STANDBY
如果此時開啟資料庫，為只讀狀態
Primary>alter database open;Database altered.Primary>select open_mode from v$database; OPEN_MODE ---------------------------------------- READ ONLY Primary>
三、StandBy資料庫轉換為Primary角色
StandBy ---> Primary
StandBy>select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- TO PRIMARY Database altered. StandBy>alter database open; Database altered. StandBy>select open_mode from v$database; OPEN_MODE ---------------------------------------- READ WRITE StandBy>select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY StandBy>
四、同步測試
1、當前的Primay資料庫插入一條資料
Primay>select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS6 rows selected.
Primay>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('14', 'OPERATIONS', 'OPERATIONS');
1 row created.
StandBY>commit;
Commit complete.
Primay>select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
14 OPERATIONS OPERATIONS 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS
7 rows selected.
2、當前的StandBy啟用redo應用
實時redo應用的情況
Primary> Database altered.
非實時redo應用的情況
-----應用redo
alter database recover managed standby database disconnect from session;
-----暫停redo應用
alter database recover managed standby database cancel;
查詢
Primary>select * from scott.dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 14 OPERATIONS OPERATIONS 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS 7 rows selected.
無法同步的情況問題解決
1、如果無法同步，切換日誌試試
StandBY>alter system switch logfile;
查詢Priamry和StandBy的歸檔日誌編號是否相同
StandBY>select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 22
2、檢視配置的服務名中的service_name 和 lsnrctl status中的是否相同