環境說明
Oracle 11.2.0.1
PRIMARY IP : 192.168.1.101
STANDBY IP : 192.168.1.102
先看一下目前主備庫的腳色
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORCL PRIMARY
SQL>
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORCL PHYSICAL STANDBY
SQL>
1.把主庫(1.101)切換為備庫
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
--這時 Primary 上所有Redo 資料都會傳送到 Standby db 上
2.把原主庫(1.101)開到 Standby mount
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2175328 bytes
Variable Size 264244896 bytes
Database Buffers 41943040 bytes
Redo Buffers 4796416 bytes
SQL> alter database mount standby database;
Database altered.
SQL>
3.檢查 Standby db (1.102)是否都有 apply
SQL> select sequence# , STANDBY_DEST, APPLIED from v$archived_log;
SEQUENCE# STA APPLIED
---------- --- ---------
52 NO YES
54 NO YES
55 NO YES
53 NO YES
56 NO YES
57 NO YES
58 NO YES
59 NO YES
107 rows selected.
SQL>
4.把 Standby db 轉為 Primary
SQL> alter database commit to switchover to primary;
Database altered.
SQL>
--檢查一下(1.102)是否轉為Primary
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORCL PRIMARY
SQL>
--檢查一下(1.101)是否為Standby
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORCL PHYSICAL STANDBY
SQL>
5.檢查完後 把 (1.102) shutdown 並開到 open
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2175328 bytes
Variable Size 255856288 bytes
Database Buffers 50331648 bytes
Redo Buffers 4796416 bytes
Database mounted.
Database opened.
SQL>
6.剛剛 (1.101) 已在Standby mount ,所以現在只要開啟應用實作就行
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
7.檢查是否轉換成功
--在主庫(1.102)進行 swtich logfile
SQL> alter system switch logfile;
System altered.
SQL> select sequence# , STANDBY_DEST, APPLIED from v$archived_log;
---------- --- ---------
61 NO NO
62 YES NO
62 NO NO
113 rows selected.
SQL>
--查看備庫(1.101) 是否有接收到並且應用
SQL> select sequence# , STANDBY_DEST, APPLIED from v$archived_log;
---------- --- ---------
58 YES NO
58 NO YES
59 NO YES
59 YES NO
60 NO YES
61 NO YES
62 NO YES
106 rows selected.
SQL>
--測試成功
--在做一個測試
--在(1.102)主庫建立一個 table
SQL> create table test_dg100 as select * from dba_objects;
Table created.
SQL> select count(*) from test_dg100;
COUNT(*)
----------
72623
SQL> alter system switch logfile;
System altered.
SQL>
--查詢(1.101)備庫是否此Table 訊息 (必須開啟到 open read only 狀態
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2175328 bytes
Variable Size 264244896 bytes
Database Buffers 41943040 bytes
Redo Buffers 4796416 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from test_dg100;
COUNT(*)
----------
72623
SQL>
--測試成功
上面是Primary DB還能開啟做的切換(Switchover)，以下介紹的是Primary DB連開啟都不能開啟的切換(Failover)
Failover 最重要的是Redo file 的處理，要是處理的不當就會造成資料遺失
Data guard 保護模式有分成三種 Maximum Protection 最大保護、Maximum Availability 最高可用、Maximum Performance 最高性能
最大保護以及最高可用是以 LGWR SYNC AFFIRM 模式傳遞，主備庫兩邊的Redo file是一樣的，這邊就可以不用處理Redo file，不過在主庫還沒有Commit的交易當然就遺失了
假如最高性能是以 ARCH ASYNC 傳遞，這邊就要DBA手動去處理了
以下是針對 PERFORMANCE 的 Failover 小小測試
1.先看一下主備庫的模式
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL>
2.先在主庫創建 Table 好等等來驗證看看是否成功
SQL> create table test_dg101 as select * from dba_objects;
Table created.
SQL> select count(*) from test_dg101;
COUNT(*)
----------
72624
SQL> commit;
Commit complete.
--因為傳輸模式是ARCH，備庫還沒接收到 Archive log file，備庫所以當然就沒有此Table
SQL> select count(*) from test_dg101;
select count(*) from test_dg101
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
3.模擬主庫 crash
SQL> shutdown abort
ORACLE instance shut down.
SQL>
4.首先先把主庫的Redo log file傳送到Standby db上，這邊archive log file 已經都傳送過來Standby 所以就只處理Redo log 的部份，要是有archive log file 還未傳送到Standby 可以手動拉檔案過來再利用指令 SQL> ALTER DATABASE REGISTER LOGFILE '路径' 註冊進去
--關閉實做應用
SQL> alter database recover managed standby database cancel;
Database altered.
--利用Redo log file 恢復 Standby db
SQL> recover standby database until cancel;
ORA-00279: change 1982951 generated at 09/04/2014 15:31:55 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000069_0857233024.0001
ORA-00280: change 1982951 for thread 1 is in sequence #69
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\test\REDO01.LOG
Log applied.
Media recovery complete.
SQL>
--有三個Redo log ，很幸運的試一次就成功
5.接著激活Standby db 讓它變成 Primary
SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2175328 bytes
Variable Size 264244896 bytes
Database Buffers 41943040 bytes
Redo Buffers 4796416 bytes
Database mounted.
SQL>
SQL> alter database activate standby database;
Database altered.
SQL> alter database open;
Database altered.
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
--成功轉為 Primary
6.驗證
SQL> select count(*) from test_dg101;
COUNT(*)
----------
72624
SQL>
--資料成功恢復
