一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九)
本文文件結構圖：
本篇釋出的有些晚，之前的8篇文章見 http://blog.itpub.net/26736162/viewspace-1290405/
1.1 測試DATAGUARDSWITCHOVER功能
1.1.1 RAC主庫、ActiveDataguard角色切換
即rac主庫切換為物理備庫，物理備庫切換為rac主庫。
1.1.1.1 ①主庫修改相關引數fal_client、fal_server：
fal_server 指定為主庫即primary的網路服務名
fal_client 指定為備庫即standby的網路服務名
通常來說，主庫和備庫是反過來的，便於主備庫的切換。
首先需要確保主庫採用spfile啟動，然後在其中任何一個節點執行以下命令:
alter system set fal_client='racdb1' sid='racdb1';
alter system set fal_client='racdb2' sid='racdb2';
alter system set fal_server='phydb' sid='*';
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/racdb/spfileracdb.ora
SQL> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string
fal_server string
SQL> alter system set fal_client='racdb1' sid='racdb1';
System altered.
SQL> alter system set fal_client='racdb2' sid='racdb2';
System altered.
SQL> alter system set fal_server='phydb' sid='*';
System altered.
---rac1檢視結果：
SQL> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string racdb1
fal_server string phydb
SQL>
---rac2檢視結果：
SQL> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string racdb2
fal_server string phydb
SQL>
1.1.1.2 ②主庫建立standbylogfile：
建立standby_logfile 之前檢視可以得到已經存在4組日誌，且無standby_logfile，每個大小為50M
SQL> col member for a60
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
2 ONLINE +DATA/racdb/onlinelog/group_2.262.859849495 NO
2 ONLINE +FRA/racdb/onlinelog/group_2.258.859849495 YES
1 ONLINE +DATA/racdb/onlinelog/group_1.261.859849493 NO
1 ONLINE +FRA/racdb/onlinelog/group_1.257.859849493 YES
3 ONLINE +DATA/racdb/onlinelog/group_3.266.859850179 NO
3 ONLINE +FRA/racdb/onlinelog/group_3.259.859850181 YES
4 ONLINE +DATA/racdb/onlinelog/group_4.267.859850183 NO
4 ONLINE +FRA/racdb/onlinelog/group_4.260.859850187 YES
已選擇8行。
SQL> select * from v$standby_log;
未選定行
SQL> select group#, bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
4 50
SQL>
開始建立 standby logfile：
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;
SQL> alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
資料庫已更改。
SQL> alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;
資料庫已更改。
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
2 ONLINE +DATA/racdb/onlinelog/group_2.262.859849495 NO
2 ONLINE +FRA/racdb/onlinelog/group_2.258.859849495 YES
1 ONLINE +DATA/racdb/onlinelog/group_1.261.859849493 NO
1 ONLINE +FRA/racdb/onlinelog/group_1.257.859849493 YES
3 ONLINE +DATA/racdb/onlinelog/group_3.266.859850179 NO
3 ONLINE +FRA/racdb/onlinelog/group_3.259.859850181 YES
4 ONLINE +DATA/racdb/onlinelog/group_4.267.859850183 NO
4 ONLINE +FRA/racdb/onlinelog/group_4.260.859850187 YES
5 STANDBY +DATA/racdb/onlinelog/group_5.269.863272613 NO
5 STANDBY +FRA/racdb/onlinelog/group_5.368.863272615 YES
6 STANDBY +DATA/racdb/onlinelog/group_6.270.863272617 NO
6 STANDBY +FRA/racdb/onlinelog/group_6.369.863272619 YES
7 STANDBY +DATA/racdb/onlinelog/group_7.271.863272621 NO
7 STANDBY +FRA/racdb/onlinelog/group_7.372.863272621 YES
8 STANDBY +DATA/racdb/onlinelog/group_8.272.863272637 NO
8 STANDBY +FRA/racdb/onlinelog/group_8.375.863272637 YES
9 STANDBY +DATA/racdb/onlinelog/group_9.273.863272639 NO
9 STANDBY +FRA/racdb/onlinelog/group_9.379.863272641 YES
10 STANDBY +DATA/racdb/onlinelog/group_10.274.863272643 NO
10 STANDBY +FRA/racdb/onlinelog/group_10.381.863272643 YES
已選擇20行。
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ------ -------------------- ------------- -------------- ------------ -------------- ------------ --------------
5 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
6 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
8 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
9 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
10 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
已選擇6行。
SQL>
1.1.1.3 ③主庫修改相關引數standby_file_management、db_file_name_convert 、log_filename_convert
修改前：
SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
standby_file_management string MANUAL
SQL> show parameter db_file_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_name_convert string
SQL> show parameter log_file_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_file_name_convert string
SQL>
修改：
SQL> alter system set db_file_name_convert='+DATA/phydb/','+DATA/racdb/' sid='*' scope=spfile;
系統已更改。
SQL> alter system set log_file_name_convert='+DATA/phydb/','+DATA/racdb/' sid='*' scope=spfile;
系統已更改。
修改後檢視：
SELECT * FROM v$spparameter a WHERE a.NAME like '%file_name_convert';
1.1.1.4 ④停止RAC節點2：
主庫狀態：
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2809285 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
857466254 RACDB 2809285 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL>
備庫狀態：
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2813423 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL>
停掉rac2主庫：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
再次檢視rac主庫：
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2814040 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL>
1.1.1.5 ⑤RAC 節點 1 切換原 RAC 主庫到備庫：
rac主庫執行：
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string racdb1
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2814245 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2814358 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ WRITE RECOVERY NEEDED
SQL>
----原物理備庫檢視：
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2834309 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY SESSIONS ACTIVE
SQL>
1.1.1.6 ⑥ 切換原物理備庫到主庫角色：
原物理備庫下執行，注意執行該步驟的時候不能有其它的回話連線到dg庫，否則報錯：
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string phydb
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2834309 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 0 MAXIMUM PERFORMANCE PRIMARY YES MOUNTED NOT ALLOWED
SQL> alter database open;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2834936 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE FAILED DESTINATION
SQL>
1.1.1.7 ⑦ 將原 RAC 主庫 2 個例項都啟動到 MOUNT 狀態：
[grid@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora.FRA.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac1
ora.OCR.dg ora....up.type ONLINE ONLINE rac1
ora.asm ora.asm.type ONLINE ONLINE rac1
ora.cvu ora.cvu.type ONLINE ONLINE rac2
ora.gsd ora.gsd.type ONLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE OFFLINE
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.racdb.db ora....se.type OFFLINE OFFLINE
ora.scan1.vip ora....ip.type ONLINE ONLINE rac1
[grid@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
[grid@rac1 ~]$ srvctl start database -d racdb -o mount
[grid@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora.FRA.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac1
ora.OCR.dg ora....up.type ONLINE ONLINE rac1
ora.asm ora.asm.type ONLINE ONLINE rac1
ora.cvu ora.cvu.type ONLINE ONLINE rac2
ora.gsd ora.gsd.type ONLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE OFFLINE
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.racdb.db ora....se.type ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type ONLINE ONLINE rac1
[grid@rac1 ~]$
-------檢視狀態
SQL> set line 9999
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2834308 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED RECOVERY NEEDED
857466254 RACDB 2834308 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED RECOVERY NEEDED
SQL>
1.1.1.8 ⑧ 原 RAC 主庫啟動 redo apply：
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
1.1.1.9 ⑨ 原 RAC 主庫停止 redo apply，並將 RAC 主庫所有節點以 READ ONLY 開啟：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED
857466254 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED
SQL> alter database open;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED
857466254 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 2 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
857466254 1 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED
SQL>
---- 啟動第二個節點後檢視：
SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 2 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
857466254 1 RACDB 2838735 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SQL>
----dg庫檢視：
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2839188 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL>
1.1.1.10 ⑩測試切換後的效果
dg主機建表：
SQL> create table test_dg(id number);
Table created.
rac主機庫檢視：
SQL> desc test_dg
ERROR:
ORA-04043: object test_dg does not exist
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> desc test_dg
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
dg主機插入資料：
SQL> insert into test_dg values(100);
1 row created.
SQL> commit;
Commit complete.
SQL>
rac庫檢視：
SQL> select * from test_dg;
ID
----------
100
SQL>
---dg庫刪除
SQL> drop table test_dg purge;
Table dropped.
SQL>
--新備庫檢視
SQL> select * from test_dg;
select * from test_dg
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
至此，說明切換成功完成。
1.1.2 單例項主庫，RAC備庫角色切換
即，將新 RAC 備庫切換為主庫，新單例項主庫切換為備庫：
1.1.2.1 ① 新主庫（單例項庫）狀態檢視：
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string phydb
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2840504 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL>
1.1.2.2 ② 新備庫（RAC 庫）狀態檢視：
SQL> select inst_id, dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 857466254 RACDB 2840572 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
2 857466254 RACDB 2840572 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL>
1.1.2.3 ③ 新備庫（RAC 庫）停止節點 2：
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string racdb2
SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 2 RACDB 2840675 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
857466254 1 RACDB 2840675 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select inst_id, dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 857466254 RACDB 2861126 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY TO PRIMARY
SQL>
1.1.2.4 ④ 新主庫（單例項庫）切換到備庫：
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string phydb
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2841031 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2841153 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ WRITE RECOVERY NEEDED
SQL>
1.1.2.5 ⑤新備庫（RAC 庫）節點 1 切換到主庫：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select inst_id, dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 857466254 RACDB 2861126 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY TO PRIMARY
--- 這裡最好先把庫修改為mount狀態再切換，不然事務很大的話會非常的慢
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select inst_id, dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 857466254 RACDB 0 MAXIMUM PERFORMANCE PRIMARY YES MOUNTED NOT ALLOWED
SQL>
1.1.2.6 ⑥ 新備庫(RAC 庫)全部啟動所有節點：
節點一：
SQL> alter database open;
Database altered.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string racdb1
SQL>
節點二：
SQL> startup
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2228144 bytes
Variable Size 1107296336 bytes
Database Buffers 218103808 bytes
Redo Buffers 8548352 bytes
Database mounted.
Database opened.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string racdb2
SQL>
SQL> select dbid,inst_id,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID INST_ID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 1 RACDB 2864440 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE RESOLVABLE GAP
857466254 2 RACDB 2864440 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE RESOLVABLE GAP
SQL>
1.1.2.7 ⑦ 新備庫（單例項庫）重新啟動並開始 redo apply：
注意備庫必須關閉然後重啟：
[oracle@dg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 10 17:18:12 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 574623064 bytes
Database Buffers 260046848 bytes
Redo Buffers 2379776 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> host ps -ef|grep mrp
oracle 31367 1 0 17:19 ? 00:00:00 ora_mrp0_phydb
oracle 31375 31168 0 17:20 pts/3 00:00:00 /bin/bash -c ps -ef|grep mrp
oracle 31377 31375 0 17:20 pts/3 00:00:00 grep mrp
SQL>
至此，完成一次角色切換，即將新的備庫（RAC 庫）切換成主庫，新的備庫（單例項庫）又重新切換為物理備庫，回到最初的狀態！！！
1.1.2.8 ⑧ 測試切換後的效果
--rac主庫
SQL> select inst_id, dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ---------- ----------- -------------------- ---------------- --- -------------------- --------------------
1 857466254 RACDB 2865147 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
2 857466254 RACDB 2865147 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
SQL>
--dg備庫
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
857466254 RACDB 2861129 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL>
-------rac主庫建表
SQL> create table test_dg(id number);
Table created.
SQL> select * from test_dg;
no rows selected
----dg備庫檢視，由於是剛切換過來的原因，這裡可能需要等待幾分鐘才可以查詢到：
SQL> select * from test_dg;
no rows selected
SQL>
---rac主庫插入資料
SQL> insert into test_dg values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_dg;
ID
----------
200
--dg庫檢視：
SQL> select * from test_dg;
ID
----------
200
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29337971/viewspace-1819893/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一步一步搭建11gR2 rac+dg之DG 機器配置(七)
- 一步一步搭建11gR2 rac+dg之結尾篇(十)
- 一步一步搭建11gR2 rac+dg之配置單例項的DG(八)單例
- 一步一步搭建oracle 11gR2 rac+dg之database安裝(五)OracleDatabase
- 一步一步搭建oracle 11gR2 rac+dg之環境準備(二)Oracle
- 一步一步搭建oracle 11gR2 rac+dg之共享磁碟設定(三)Oracle
- 一步一步搭建 oracle 11gR2 rac+dg之grid安裝(四)Oracle
- 一步一步搭建11gR2 rac+dg之安裝rac出現問題解決(六)
- 一步一步搭建 oracle 11gR2 rac + dg 之前傳 （一）Oracle
- ORACLE 11gR2 11.2.0.4 一步一步 物理DGOracle
- 一步一步學DataGuard(14)邏輯standby之switchover
- Oracle RAC+DG搭建Oracle
- 一步一步搭建Oracle 11g RAC+ DG詳解Oracle
- 11G RAC+DG搭建
- oracle11g 搭建 rac+dgOracle
- 【DG】[三思筆記]一步一步學DataGuard筆記
- 使用DG_broker工具管理DG之switchover
- 一步一步搭建，功能最全的許可權管理系統之動態路由選單（一）路由
- 一步一步搭建腳手架
- 【DG】DG之Switchover和Failover的區別AI
- 一步一步分析vue之observeVue
- 一步一步搭建 PHP 伺服器環境PHP伺服器
- OEL6.3 64位部署ORACLE 11gR2(11.2.0.4) RAC+DG（9）建立DGOracle
- 一步一步分析vue之$mount(1)Vue
- Oracle：DG 的 switchoverOracle
- 一步一步分析vue之_data屬性Vue
- 一步一步給你的 Android app 加入聊天功能AndroidAPP
- Oracle11gR2搭建ADG一步一步操作Oracle
- 一步一步教你如何搭建自己的視訊聚合站
- 一步一步使用ABP框架搭建正式專案系列教程框架
- 11gR2 RAC DB switchover using DG broker (文件 ID 880017.1)
- ORACLE19C RAC+DGOracle
- RAC+DG(asm單例項)ASM單例
- 一步一步搭建 springboot-2.1.3+dubbo-2.7.1 專案Spring Boot
- 一步一步搭建react應用-前後端初始化React後端
- 一步一步學DataGuard(5)物理standby之建立示例
- 小白折騰伺服器（九）：一步一步搭個自己的 Composer 包伺服器
- 物理DG角色轉換：switchover