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操作定義了資料庫行為，特別是主庫工作行為。

聊聊Dataguard的三種保護模式實驗（下）

相關文章