dataguard角色轉換—switchover

   10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
一 將主庫切換為standby role
確定primary資料庫是否支援switchover操作
SYS>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
顯示結果表示primary資料庫支援轉換為standby角色
將primary資料庫轉換為standby角色
SYS>alter database commit to switchover to physical standby with session shutdown;
Database altered.
將primary資料庫重啟到mount狀態
SYS>shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.
Total System Global Area  595591168 bytes
Fixed Size            1220748 bytes
Variable Size          180359028 bytes
Database Buffers      406847488 bytes
Redo Buffers            7163904 bytes
Database mounted.
二 將備庫切換為primary role
確定待轉換的standby資料庫是否支援switchover操作
SYS>select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
顯示結果表示standby資料庫支援轉換為primary角色
將standby轉換為primary角色
SYS>alter database commit to switchover to primary;
Database altered.
角色轉換完成，啟動資料庫
SYS>shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS>startup
ORACLE instance started.
Total System Global Area  268435456 bytes
Fixed Size            1218868 bytes
Variable Size           92276428 bytes
Database Buffers      167772160 bytes
Redo Buffers            7168000 bytes
Database mounted.
Database opened.
原主庫（新的備庫）以REDO LOG實時APPLY的方式開啟和主資料庫的同步
SYS>alter database recover managed standby database using current logfile disconnect from session;
Database altered
三 同步測試
檢視當前資料庫的角色及資料庫名
select database_role,db_unique_name from v$database;
新primary
scott使用者下有這些表
SYS>select table_name from all_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
新的standby
SYS>alter database recover managed standby database cancel;
Database altered.
SYS>alter database open read only;
Database altered.
scott使用者下有這些表
SYS>select table_name from all_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
新primary
在scott使用者下建立表e
SYS>create table scott.e as select * from scott.emp;
Table created.
SYS>select table_name from all_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
E
新standby
應用redo log日誌
SYS>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
檢視同步時間
SYS>select name,value from v$dataguard_stats;
NAME                 VALUE
-------------------------------- ----------------------------------------------------------------
apply finish time
apply lag             +00 00:03:19
estimated startup time         8
standby has been open         Y
transport lag             +00 00:00:00
待同步完成即apply lag值為+00 00:00:00時停止日誌應用
SYS>alter database recover managed standby database cancel;
Database altered.
以只讀方式開啟資料庫
SYS>alter database open read only;
Database altered.
檢視是否有e表生成
SYS>select table_name from all_tables where owner='SCOTT';

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
E
standby有e表生成，結果表明同步成功
四 錯誤處理
SYS>alter database commit to switchover to primary with session shutdown wait;
alter database commit to switchover to primary with session shutdown wait
*
ERROR at line 1:
ORA-16139: media recovery required
解決：
alter database recover managed standby database finish
alter database commit to switchover to primary;