Oracle Data guard - 主備切換

環境說明

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;

 SEQUENCE# STA APPLIED
---------- --- ---------
        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;

 SEQUENCE# STA APPLIED
---------- --- ---------
        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>



--資料成功恢復