當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

【DG】DG的3种保护模式

發布時間：2024/1/8 编程问答 32 豆豆

生活随笔收集整理的這篇文章主要介紹了【DG】DG的3种保护模式小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

【DG】DG的3種保護模式

DG提供了3種數據保護模式：最大保護（Maximum Protection）、最高性能（Maximum Performance）和最高可用（Maximum Availability），如下表所示：

表 3-32?DG三種模式的區別

數據庫異地備份方案

在對數據庫計劃實施異地備份時，需要理解異地備份的目的。一般情況下有兩種異地備份的需求：一是確保業務的實時以及最大程度減少由于單點故障導致業務中斷；二是確保數據的安全，當生產節點出現物理故障時能通過異地備份及時的恢復數據。

?非實時異地備份

? ? ?如果只需實現數據的安全，而毋須關注業務的恢復時間以及數據的丟失情況。?那么可以通過非實時的異地備份而簡易實現。

? ? 非實時的異地備份可以通過磁盤或者磁帶介質來實現。

? ? 采用磁帶來實現數據的安全性是傳統的異地備份方式，采用此種方式，主要受限于磁帶的讀寫速度偏慢，當需要執行恢復時，如果數據量很大，恢復的時間那是相當漫長。另外，需要使用第三方備份軟件實現數據與磁帶之間的交互；

? ?采用磁盤來實現數據庫的安全性備份是近年來相對流行的異地備份方式，主要由于目前磁盤價格便宜，并且讀寫速度相對較高，因而比較受用戶的青睞。經常的做法是通過異地的存儲或者主機做為備份節點，當生產節點的數據備份出來之后，通過網絡傳輸至備份節點，確保備份的安全；采用此種方式備份，對網絡穩定要求較高。如果在執行數據傳輸出現網絡故障，那么傳輸將會中斷。另外，如果采用異地主機作為備份節點，建議備份節點的操作系統類型、芯片架構與生產節點的一致。

?實時異地備份

近年來，由于業務的發展以及技術的飛越。很多實時的異地備份方案出現，極大程度確保了數據的安全以及業務的穩定性。針對 Oracle 數據庫，目前主流使用 Oracle DataGuard、Oracle Goldengate 作為災備技術實現數據庫的實時異地備份。當然還有 Vertias 的相關存儲鏡像管理軟件來實現。

? ? 以下著重了解 Oracle DataGuard 的實現方式。

Oracle DataGuard 結構

?如上圖所述，結構中的備用數據庫分成物理備用數據庫和邏輯備用數據庫。

在物理備用數據庫中，將重做日志使用數據庫恢復功能來應用。

? ?在邏輯備用數據庫中，將重做日志轉換成 SQL，使用這些 SQL 在邏輯備用數據庫上自動操作。

DataGuard 的保護模式

? ?在 Data Guard 模式下，數據庫的保護模式分成下列三種：

最大保護模式

1）這種模式提供了最高級別的數據保護能力；

2）要求至少一個物理備庫收到重做日志后，主庫的事務才能夠提交；

3）主庫找不到合適的備庫寫入時，主庫會自動關閉，防止未受保護的數據出現；

?4）優點：該模式可以保證備庫沒有數據丟失；

5）缺點：備庫的自動關閉會影響到主庫的可用性，同時需要備庫恢復后才能提交，對網絡等客觀條件要求非常的高，主庫的性能會因此受到非常大的沖擊。

最大可用性模式

1）該模式提供了僅次于“最大保護模式”的數據保護能力；

2）要求至少一個物理備庫收到重做日志后，主庫的事務才能夠提交；

3）主庫找不到合適的備庫寫入時，主庫不會關閉，而是臨時降低到“最大性能模式”模式，直到問題得到處理；

4）優點：該模式可以在沒有問題出現的情況下，保證備庫沒有數據丟失，是一種折中的方法；

5）缺點：在正常運行的過程中缺點是主庫的性能受到諸多因素的影響。

最大性能模式

1）該模式是默認模式，可以保證主數據庫的最高可用性；

2）保證主庫運行過程中不受備庫的影響，主庫事務正常提交，不因備庫的任何問題影響到主庫的運行；

4）優點：避免了備庫對主數據庫的性能和可用性影響；

5）缺點：如果與主庫提交的事務相關的恢復數據沒有發送到備庫，這些事務數據將被丟失，不能保證數據無損失；

? ?采用 Oracle DataGuard，最大的受限在于主備數據庫主機需要采用同芯片架構的主機。鑒于此，不能跨操作系統平臺實現 Oracle DataGuard 的應用。另外，Oracle DataGuard 是 Oracle 企業版數據庫的一種特性，毋須其它 license。

Data Guard是Oracle高可用性HA的重要解決方案。針對不同的系統保護需求，DG提供了三種不同類型的保護模式（Protection Mode），分別為：最大保護（Maximum Protection）、最大可用（Maximum Availability）和最大性能（Maximum performance）。在實際應用場景下，我們需要根據不同的業務場景和數據可用性需求，來設置DG環境的保護類型。

1、三種保護模式Protection Mode

三種保護模式是DG的核心概念。DG本質上是一種基于Redo Log的數據同步機制。Undo和Redo是Oracle早期奠定行業地位的核心技術。Undo負責記錄事務操作的前鏡像，而Redo負責記錄事務操作的后鏡像。在Oracle事務commit的動作中，寫入日志文件是一個一定需要完成的動作。寫入日志文件之后，即使立刻出現嚴重的實例終止事件，在重新啟動實例的時候也會進行實例恢復動作，將事務落實。

在DG環境中，無論采用何種初始化方法，都是確保一個Primary和Standby的初始化數據一致，之后Primary一端接收的事務類型操作，均會以歸檔日志串列的方式傳遞到Standby端的standby redo log和歸檔日志列表中，最后重復應用這些日志，實現Primary和Standby端一致。

Primary和Standby是相互為備份的冗余結構，Standby跟隨Primary的情況，反映了HA結構的可用性級別。理論上，最保險的策略是一個事務要保證在Primary和Standby上都提交了，才返回給用戶說已經完成。這樣是可以保證主備庫完全一致的最保險做法。另一個極端情況，就是主庫“自顧自”進行事務處理，獨立將日志進行傳輸，也不用管日志是否傳輸到或者應用到。

針對不同的傳輸情況，DG區分為三種保護類型：

ü??最大可用模式Maximum Availability

在官方文檔中，對這種模式的描述如下：

“This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.”

Maximum Availability模式下，事務只有在所有相關日志都被傳輸到至少一個Standby端日志的時候，才可以正式提交。但是，如果Primary在傳輸日志的過程中，發現所有standby端都不能進行傳輸，模式會退化到最大性能模式（Maximum Performance）工作方式。應該說，Maximum Availability是一種自適應的保護模式，當出現問題的時候，DG會退而求其次，確保Primary主庫事務進行。

ü??最大性能模式（Maximum Performance）

官方文檔中介紹如下：“This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.”

最大性能模式是在不影響主庫工作情況下，可以提供的最高數據保護級別。當事務進行提交的時候，主庫不會去確認日志是否寫入到備庫中，更不會確認是否被apply。這種方式下，主庫的工作性能是不會收到備庫提交應用的影響的。當然，這種保護模式會有一定的事務數據丟失，但是絕對不會出現數據誤提交的情況。

對DG而言，最大性能模式是默認的保護模式。當我們完成了DG安裝之后，就自動進入了Maximum Performance模式。

ü??最大保護模式（Maximum Protection）

最大保護模式在官方中的描述為：

“This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.

Transactions on the primary are considered protected as soon as Data Guard has written the redo data to persistent storage in a standby redo log file. Once that is done, acknowledgment is quickly made back to the primary database so that it can proceed to the next transaction. This minimizes the impact of synchronous transport on primary database throughput and response time. To fully benefit from complete Data Guard validation at the standby database, be sure to operate in real-time apply mode so that redo changes are applied to the standby database as fast as they are received. Data Guard signals any corruptions that are detected so that immediate corrective action can be taken.”

最大保護模式是完全HA架構理想中的事務模式。如果Primary數據庫進行一個事務，連帶Standby數據庫也要同步進行操作。如果由于網絡、執行模式等原因，Standby不能夠跟上主庫的操作，那么主庫會放棄事務，并且強制停庫。

保護模式的三種和數據庫之間傳輸日志的機制是密切相關的。主要體現是否同步傳輸Redo日志和對日志進行確認兩個方面。我們配置三種日志模式，一定要以Log_Archive_Config參數配置為基礎。

Maximum Availability	Maximum Performance	Maximum Protection
AFFIRM	NOAFFIRM	AFFIRM
SYNC	ASYNC	SYNC
DB_UNIQUE_NAME	DB_UNIQUE_NAME	DB_UNIQUE_NAME

下面通過一系列的測試，來分析三種保護模式的工作行為方式。

2、環境介紹

筆者使用環境為Oracle 11gR2，具體版本為11.2.0.4。主備庫環境已經搭建完成，同步保護模式是采用默認方式。

主庫信息：

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ WRITE???????????PRIMARY??????????MAXIMUM PERFORMANCE

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

vlife

主庫與備庫連接方式，采用默認的異步非確認方式。

SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME from v$archive_dest where status<>'INACTIVE';

???DEST_ID DEST_NAME????????????TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW????????VALID_TYPE??????VALID_ROLE???DB_UNIQUE_NAME

---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ---------------

?????????1 LOG_ARCHIVE_DEST_1???SYNCHRONOUS??????????????0 NO???YES??????????????ALL_LOGFILES????ALL_ROLES????NONE

?????????2 LOG_ARCHIVE_DEST_2???ASYNCHRONOUS?????????61440 NO???YES??????????????ONLINE_LOGFILE??PRIMARY_ROLE vlifesb

此時，傳輸通道配置。

SQL> show parameter LOG_ARCHIVE_DEST_2;

NAME?????????????????????????????????TYPE????????VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2???????????????????string??????SERVICE=vlifesb valid_for=(online_logfiles,primary_role) db_unique_name=vlifesb

log_archive_dest_20??????????????????string?????

log_archive_dest_21??????????????????string?????

備庫信息如下：

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ ONLY WITH APPLY?PHYSICAL STANDBY?MAXIMUM PERFORMANCE

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

vlifesb

SQL> col dest_name for a20;

SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME from v$archive_dest where status<>'INACTIVE';

???DEST_ID DEST_NAME????????????TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW????????VALID_TYPE??????VALID_ROLE???DB_UNIQUE_NAME

---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ------------------------------

?????????1 LOG_ARCHIVE_DEST_1???SYNCHRONOUS??????????????0 NO???YES??????????????ALL_LOGFILES????ALL_ROLES????NONE

?????????2 LOG_ARCHIVE_DEST_2???ASYNCHRONOUS?????????61440 NO???WRONG VALID_TYPE ONLINE_LOGFILE??PRIMARY_ROLE vlife

????????32 STANDBY_ARCHIVE_DEST SYNCHRONOUS??????????????0 NO???YES??????????????ALL_LOGFILES????ALL_ROLES????NONE

SQL> show parameter LOG_ARCHIVE_DEST_2;

NAME?????????????????????????????????TYPE????????VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2???????????????????string??????SERVICE=vlife valid_for=(online_logfiles,primary_role) db_unique_name=vlife

log_archive_dest_20??????????????????string?????

log_archive_dest_21??????????????????string?????

3、最大可用模式Maximum Availability Mode測試

當前從默認的最大性能切換到最大可用模式，首先需要滿足將日志傳輸模式進行修改。

SQL> alter system set log_archive_dest_2='SERVICE=vlifesb?sync affirm?net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=vlifesb';

System altered

SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME, NET_TIMEOUT from v$archive_dest where status<>'INACTIVE';

???DEST_ID DEST_NAME????????????TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW????????VALID_TYPE??????VALID_ROLE???DB_UNIQUE_NAME?????????????????NET_TIMEOUT

---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ------------------------------ -----------

?????????1 LOG_ARCHIVE_DEST_1???SYNCHRONOUS??????????????0 NO???YES??????????????ALL_LOGFILES????ALL_ROLES???NONE?????????????????????????????????????0

?????????2 LOG_ARCHIVE_DEST_2???PARALLELSYNC?????????????0 YES??YES??????????????ONLINE_LOGFILE??PRIMARY_ROLE vlifesb?????????????????????????????????30

此時，將保護模式使用alter database進行設置。

SQL>?alter database set standby database to maximize availability;

Database altered

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ WRITE???????????PRIMARY??????????MAXIMUM AVAILABILITY

在切換動作的時候，主庫日志情況如下：

Wed Oct 21 15:13:48 2015

alter database set standby database to maximize availability

Completed: alter database set standby database to maximize availability

Wed Oct 21 15:13:49 2015

Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED –發現沒有同步，需要補充。

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Wed Oct 21 15:13:49 2015

NSS2 started with pid=34, OS id=9186

LGWR: Standby redo logfile selected to archive thread 1 sequence 82

LGWR: Standby redo logfile selected for thread 1 sequence 82 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 82 (LGWR switch)

??Current log# 3 seq# 82 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_3_c1kb1c24_.log

??Current log# 3 seq# 82 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_3_c1kb1c43_.log

Wed Oct 21 15:13:53 2015

Archived Log entry 104 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:

Wed Oct 21 15:13:54 2015

ARC3: Archive log rejected (thread 1 sequence 81) at host 'vlifesb'

FAL[server, ARC3]: FAL archive failed, see trace file.

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance vlife - Archival Error. Archiver continuing.

Wed Oct 21 15:14:42 2015

Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED

LGWR: Standby redo logfile selected to archive thread 1 sequence 83

LGWR: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 83 (LGWR switch)

??Current log# 1 seq# 83 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_1_c1kb19q4_.log

??Current log# 1 seq# 83 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_1_c1kb19sb_.log

Wed Oct 21 15:14:42 2015

Archived Log entry 107 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:

在Primary端，在進行切換之后，Oracle發現傳輸狀態不是同步情況。于是自動加快進行日志傳輸和同步動作。在Standby端，也可以看到后續追趕動作。

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY

Standby端上的日志追趕動作。

Wed Oct 21 08:27:05 2015

Primary database is in MAXIMUM PERFORMANCE mode

Re-archiving standby log 4 thread 1 sequence 80

Wed Oct 21 08:27:05 2015

Media Recovery Waiting for thread 1 sequence 81

RFS[14]: Assigned to RFS process 31500

RFS[14]: Selected log 5 for thread 1 sequence 81 dbid -87496857 branch 892734889

Wed Oct 21 08:27:05 2015

Archived Log entry 76 added for thread 1 sequence 80 ID 0xfad4f44b dest 1:

Recovery of Online Redo Log: Thread 1 Group 5 Seq 81 Reading mem 0

??Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_5_c265gqd8_.log

??Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_5_c265gqj0_.log

Wed Oct 21 15:13:52 2015

Primary database is in MAXIMUM AVAILABILITY mode

Changing standby controlfile to MAXIMUM AVAILABILITY mode

Changing standby controlfile to RESYNCHRONIZATION level

Standby controlfile consistent with primary

RFS[15]: Assigned to RFS process 969

RFS[15]: Selected log 4 for thread 1 sequence 82 dbid -87496857 branch 892734889

Wed Oct 21 15:13:53 2015

Archived Log entry 77 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:

Wed Oct 21 15:13:53 2015

Media Recovery Waiting for thread 1 sequence 82 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 82 Reading mem 0

??Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

??Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

Wed Oct 21 15:14:41 2015

Archived Log entry 78 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:

Wed Oct 21 15:14:41 2015

Media Recovery Waiting for thread 1 sequence 83

Wed Oct 21 15:14:42 2015

Primary database is in MAXIMUM AVAILABILITY mode

Changing standby controlfile to MAXIMUM AVAILABILITY level

Standby controlfile consistent with primary

RFS[16]: Assigned to RFS process 976

RFS[16]: Selected log 4 for thread 1 sequence 83 dbid -87496857 branch 892734889

Recovery of Online Redo Log: Thread 1 Group 4 Seq 83 Reading mem 0

??Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

??Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

此時，兩個庫由于網絡通暢，同步狀態正常，同步測試正常。

（Maximium Availiablity模式下使用）

--主庫下

SQL> create table t_m as select * from dba_objects where rownum<10;

Table created

--Standby下

SQL> select count(*) from t_m;

??COUNT(*)

----------

?????????9

如果此時中斷應用日志，Standby情況如下：

SQL> alter database recover managed standby database cancel;

Database altered

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ ONLY????????????PHYSICAL STANDBY?MAXIMUM AVAILABILITY

日志情況如下：

Wed Oct 21 15:20:49 2015

alter database recover managed standby database cancel

Wed Oct 21 15:20:49 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/vlifesb/vlifesb/trace/vlifesb_pr00_17539.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 1692263

Wed Oct 21 15:20:49 2015

MRP0: Background Media Recovery process shutdown (vlifesb)

Managed Standby Recovery Canceled (vlifesb)

Completed: alter database recover managed standby database cancel

如果此時再中斷監聽器，中斷連接。此時數據庫不能做到實時同步。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 15:24:17

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

The command completed successfully

--主庫

***********************************************************************

Fatal NI connect error 12541, connecting to:

?(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.19.90)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=vlifesb)(CID=(PROGRAM=oracle)(HOST=vLIFE-URE-OT-DB-PRIMARY)(USER=oracle))))

??VERSION INFORMATION:

????????TNS for Linux: Version 11.2.0.4.0 - Production

????????TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

??Time: 21-OCT-2015 15:24:38

??Tracing not turned on.

??Tns error struct:

????ns main err code: 12541

???

TNS-12541: TNS:no listener

????ns secondary err code: 12560

????nt main err code: 511

???

TNS-00511: No listener

????nt secondary err code: 111

????nt OS err code: 0

Error 12541 received logging on to the standby

Check whether the listener is up and running.

PING[ARC2]: Heartbeat failed to connect to standby 'vlifesb'. Error is 12541.

強制日志切換。

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> select * from v$archive_dest_status;

???DEST_ID DEST_NAME????????????STATUS????TYPE???????????DATABASE_MODE???RECOVERY_MODE???????????PROTECTION_MODE?????DESTINATION??????????????????????????????????????????????????????????????????????STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR????????????????????????????????????????????????????????????????????????????SRL DB_UNIQUE_NAME?????????????????SYNCHRONIZATION_STATUS SYNCHRONIZED GAP_STATUS

---------- -------------------- --------- -------------- --------------- ----------------------- -------------------- -------------------------------------------------------------------------------- --------------------- ---------------------- ---------------- ------------- --------------- ------------ -------------------------------------------------------------------------------- --- ------------------------------ ---------------------- ------------ ------------------------

?????????1 LOG_ARCHIVE_DEST_1???VALID?????LOCAL??????????OPEN????????????IDLE????????????????????MAXIMUM PERFORMANCE?/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch?????????????????????????????????????????????????????0?????????????????????0????????????????1????????????85???????????????0????????????0?????????????????????????????????????????????????????????????????????????????????NO??NONE???????????????????????????CHECK CONFIGURATION???NO??????????

?????????2 LOG_ARCHIVE_DEST_2???ERROR?????PHYSICAL???????OPEN_READ-ONLY??IDLE????????????????????RESYNCHRONIZATION????vlifesb?????????????????????????????????????????????????????????????????????????????????????????????3??????????????????????0????????????????1????????????82???????????????1???????????82 ORA-12541: TNS: ???à?????ò???????????????????????????????????????????????????????YES vlifesb????????????????????????CHECK CONNECTIVITY????NO???????????RESOLVABLE GAP

SQL> select recid, name, sequence# from v$archived_log where sequence#>82;

?????RECID NAME??????????????????????????????????????????????????????????????????????????????SEQUENCE#

---------- -------------------------------------------------------------------------------- ----------

???????108 vlifesb??????????????????????????????????????????????????????????????????????????????????83

???????109 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_83_c2ghkz?????????83

???????110 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_84_c2ghl0?????????84

???????111 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_85_c2ghl4?????????85

而standby端，歸檔日志就沒有傳輸到。

SQL> select recid, name, sequence# from v$archived_log where sequence#>82;

?????RECID NAME??????????????????????????????????????????????????????????????????????????????SEQUENCE#

---------- -------------------------------------------------------------------------------- ----------

????????79 /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_21/o1_mf_1_83_c2gh?????????83

SQL> select group#, dbid, archived from v$standby_log;

????GROUP# DBID?????????????????????????????????????ARCHIVED

---------- ---------------------------------------- --------

?????????4 UNASSIGNED???????????????????????????????NO

?????????5 UNASSIGNED???????????????????????????????NO

?????????6 UNASSIGNED???????????????????????????????YES

此時，Primary和Standby的連接明顯被中斷，日志不能傳送，也就達不到同步確認的設置要求。但是此時，Primary還是可以進行事務操作。

（事務可以進行）

SQL> insert into t_m select * from dba_objects where rownum<10;

9 rows inserted

SQL> commit;

Commit complete

此時，如果恢復兩者連接，啟動監聽器和日志應用。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 15:51:46

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to

（篇幅原因，有省略…….）

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered

之后主庫和從庫日志上進行歸檔日志傳輸和后續同步動作，篇幅原因，日志信息省略。

從上面實驗中，我們可以看到最大可用性模式的核心即使“可用”。所謂可用，即使保證Primary和Standby整體的可用。如果在日志傳輸通路順暢，兩者之間會維持嚴格的同步關系，行為類似于最大保護模式。但是，如果連接或者同步動作不能滿足要求，DG是不會終止實例運行，而是退而求其次，進行一種類似最大性能模式的工作方式。

4、最大保護模式Maximum Protection

最大保護模式是DG可以提供的最高保護級別，建立在日志同步傳輸和確認的基礎上。同樣，可以使用alter database方法進行設置。

SQL> alter database set standby database to maximize protection;

Database altered

主庫上，可以查看到狀態變化和日志情況。

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ WRITE???????????PRIMARY??????????MAXIMUM PROTECTION

Wed Oct 21 16:17:46 2015

alter database set standby database to maximize protection

Completed: alter database set standby database to maximize protection

Wed Oct 21 16:17:50 2015

Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Standby redo logfile selected to archive thread 1 sequence 89

LGWR: Standby redo logfile selected for thread 1 sequence 89 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 89 (LGWR switch)

??Current log# 1 seq# 89 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_1_c1kb19q4_.log

??Current log# 1 seq# 89 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_1_c1kb19sb_.log

Wed Oct 21 16:17:50 2015

Archived Log entry 119 added for thread 1 sequence 88 ID 0xfad4f44b dest 1:

備庫上信息也是同樣變化信息。

SQL>??select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ ONLY WITH APPLY PHYSICAL STANDBY?MAXIMUM PROTECTION

Wed Oct 21 15:52:45 2015

Primary database is in MAXIMUM AVAILABILITY mode

Changing standby controlfile to MAXIMUM AVAILABILITY level

Standby controlfile consistent with primary

RFS[21]: Assigned to RFS process 1172

RFS[21]: Selected log 4 for thread 1 sequence 88 dbid -87496857 branch 892734889

Recovery of Online Redo Log: Thread 1 Group 4 Seq 88 Reading mem 0

??Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

??Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

Wed Oct 21 16:17:49 2015

Archived Log entry 84 added for thread 1 sequence 88 ID 0xfad4f44b dest 1:

Wed Oct 21 16:17:49 2015

Media Recovery Waiting for thread 1 sequence 89

Wed Oct 21 16:17:50 2015

Primary database is in MAXIMUM PROTECTION mode

Changing standby controlfile to MAXIMUM PROTECTION mode

Standby controlfile consistent with primary

RFS[22]: Assigned to RFS process 1358

RFS[22]: Selected log 4 for thread 1 sequence 89 dbid -87496857 branch 892734889

Recovery of Online Redo Log: Thread 1 Group 4 Seq 89 Reading mem 0

??Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

??Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

如果此時，我們終止Standby端的同步動作。

SQL> alter database recover managed standby database cancel;

Database altered

SQL>??select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ ONLY????????????PHYSICAL STANDBY?MAXIMUM PROTECTION

此時，如果主庫有事務執行，是否可以執行成功呢？

主庫事務：

SQL> delete t_m;

9 rows deleted

SQL> commit;

Commit complete

備庫

SQL> select count(*) from t_m;

??COUNT(*)

----------

?????????9

事務操作并沒有能夠傳遞到備庫上。當前的日志傳輸機制是正常的，如果我們切斷了這種連接，處在最大保護模式下的Primary端如何。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 16:25:30

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

The command completed successfully

主庫中立刻就有中斷信息的顯示：

***********************************************************************

Fatal NI connect error 12541, connecting to:

?(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.19.90)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=vlifesb)(CID=(PROGRAM=oracle)(HOST=vLIFE-URE-OT-DB-PRIMARY)(USER=oracle))))

??VERSION INFORMATION:

????????TNS for Linux: Version 11.2.0.4.0 - Production

????????TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

??Time: 21-OCT-2015 16:25:41

??Tracing not turned on.

??Tns error struct:

????ns main err code: 12541

???

TNS-12541: TNS:no listener

????ns secondary err code: 12560

????nt main err code: 511

???

TNS-00511: No listener

????nt secondary err code: 111

????nt OS err code: 0

Error 12541 received logging on to the standby

Check whether the listener is up and running.

PING[ARC2]: Heartbeat failed to connect to standby 'vlifesb'. Error is 12541.

此時，我們在Primary上進行事務操作。

SQL> insert into t_m select * from dba_objects where rownum<100;

99 rows inserted

SQL> commit;

Commit complete

SQL> select group#, sequence#, status from v$log;

????GROUP#??SEQUENCE# STATUS

---------- ---------- ----------------

?????????1?????????89 CURRENT

?????????2?????????87 INACTIVE

?????????3?????????88 INACTIVE

事務操作成功。如果我們嘗試重新啟動standby，是不被允許的。

SQL> SQL> shutdown immediate

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

日志：

Wed Oct 21 16:34:41 2015

Attempt to shut down Standby Database

Standby Database operating in NO DATA LOSS mode

Detected primary database alive, shutdown primary first, shutdown aborted

但是，切換日志強制歸檔過程的時候，數據庫立即發現Standby存在問題，停庫。

SQL> alter system switch logfile;

alter system switch logfile

ORA-03113:?通信通道的文件結尾

進程?ID: 9478

會話?ID: 394?序列號: 33

Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED

LGWR: All standby destinations have failed

******************************************************

WARNING: All standby database destinations have failed

WARNING: Instance shutdown required to protect primary

******************************************************

LGWR (ospid: 30597): terminating the instance due to error 16098

Wed Oct 21 16:43:44 2015

System state dump requested by (instance=1, osid=30597 (LGWR)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/vlife/vlife/trace/vlife_diag_30587_20151021164344.trc

Dumping diagnostic data in directory=[cdmp_20151021164344], requested by (instance=1, osid=30597 (LGWR)), summary=[abnormal instance termination].

Instance terminated by LGWR, pid = 30597

此時，standby端日志信息：

--Standby端

Wed Oct 21 16:43:42 2015

Archived Log entry 85 added for thread 1 sequence 89 ID 0xfad4f44b dest 1:

Wed Oct 21 16:43:43 2015

RFS[20]: Possible network disconnect with primary databaseRFS[17]: Possible network disconnect with primary database

說明：在最大保護狀態的時候，如果Primary發現備庫傳輸日志有問題，出于保護的必要，就會自動停機。

下面可以進行操作恢復，備庫首先恢復應用日志狀態：

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered

Wed Oct 21 16:46:19 2015

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (vlifesb)

Wed Oct 21 16:46:19 2015

MRP0 started with pid=25, OS id=1495

MRP0: Background Managed Standby Recovery process started (vlifesb)

?started logmerger process

Wed Oct 21 16:46:24 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_21/o1_mf_1_89_c2gn1ylm_.arc

Media Recovery Waiting for thread 1 sequence 90

Completed: alter database recover managed standby database using current logfile disconnect from session

注意，此時第二個事務對應數據表t_m的數據已經發生變化。這說明之前雖然已經停止監聽活動，但是依賴之前的已經確立連接，還是將日志傳輸過去。

SQL> select count(*) from t_m;

??COUNT(*)

----------

????????99

嘗試啟動主庫：

[oracle@vLIFE-URE-OT-DB-PRIMARY trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 21 16:48:23 2015

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2471931904 bytes

Fixed Size??????????????????2255752 bytes

Variable Size?????????????738198648 bytes

Database Buffers?????????1711276032 bytes

Redo Buffers???????????????20201472 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 9617

Session ID: 580 Serial number: 5

Error 12541 received logging on to the standby

Check whether the listener is up and running.

LGWR: Error 12541 creating archivelog file 'vlifesb'

Crash Recovery Foreground: All standby destinations have failed

******************************************************

WARNING: All standby database destinations have failed

WARNING: Instance shutdown required to protect primary

******************************************************

USER (ospid: 9617): terminating the instance due to error 16098

System state dump requested by (instance=1, osid=9617), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/vlife/vlife/trace/vlife_diag_9581_20151021164839.trc

Dumping diagnostic data in directory=[cdmp_20151021164839], requested by (instance=1, osid=9617), summary=[abnormal instance termination].

Instance terminated by USER, pid = 9617

在mount到open的過程中，啟動被終止。這個是可以理解的，在Standby端，監聽器沒有啟動。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$?lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 16:50:20

（篇幅原因，有省略……）

Service "vlifesb" has 1 instance(s).

??Instance "vlifesb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

此時，再次啟動數據庫主庫，操作成功。

SQL> startup

ORACLE instance started.

Total System Global Area 2471931904 bytes

Fixed Size??????????????????2255752 bytes

Variable Size?????????????738198648 bytes

Database Buffers?????????1711276032 bytes

Redo Buffers???????????????20201472 bytes

Database mounted.

Database opened.

最后，從最大保護“退化”到最大性能模式。

SQL> alter database set standby database to maximize performance

??2??;

Database altered

SQL> select name, open_mode, database_role, protection_mode from v$database;

NAME??????OPEN_MODE????????????DATABASE_ROLE????PROTECTION_MODE

--------- -------------------- ---------------- --------------------

VLIFE?????READ WRITE???????????PRIMARY??????????MAXIMUM PERFORMANCE

5、結論

Oracle DG的三種模式，是與歸檔日志傳輸機制緊密相關的。歸檔日志傳輸確定了保護模式的基礎前提，通過alter database set操作定義了數據庫行為，特別是主庫工作行為。

About Me

 ............................................................................................................................... 
 ● 本文作者：小麥苗，只專注于數據庫的技術，更注重技術的運用 
 ● 本文在itpub（http://blog.itpub.net/26736162）、博客園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 
 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/  
 ● 本文博客園地址：http://www.cnblogs.com/lhrbest  
 ● 本文pdf版及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/  
 ● 數據庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/  
 ● QQ群：230161599???? 微信群：私聊  
 ● 聯系我請加QQ好友(646634621)，注明添加緣由 
 ● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成 
 ● 文章內容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解 
 ● 版權所有，歡迎分享本文，轉載請保留出處 
 ............................................................................................................................... 
 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的數據庫技術。 
 

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/26736162/viewspace-2141200/，如需轉載，請注明出處，否則將追究法律責任。

總結

以上是生活随笔為你收集整理的【DG】DG的3种保护模式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

模式
DG

上一篇：行测-判断推理-图形推理-样式规律-黑白
下一篇：春运浙江运送旅客量将达1.38亿人次同