Oracle-DG最大保護模式下,dg備庫出現問題對主庫有什麼影響？

一、需求

疑問？Oracle最大保護模式下,dg備庫出現問題，影響主庫嗎？

我們都知道Oracle最大保護模式的意思是oracle不允許資料丢失，1條記錄都不行！ 那麼備庫有問題？ oracle主庫還能讀寫嗎？

二、測試

2.1 現有情況查詢

--查詢保護模式：最大保護
SQL> select name,dbid,database_role,protection_mode from v$database;
NAME            DBID DATABASE_ROLE    PROTECTION_MODE
--------- ---------- ---------------- --------------------
DINGDING  2042277967 PRIMARY          MAXIMUM PROTECTION 最大保護


最大保護模式下日志傳輸途徑：
-打開主庫的告警日志：
[oracle@bj /]$ tail -f /picclife/app/oracle/diag/rdbms/bj/dingding/trace/alert_dingding.log
LGWR: Standby redo logfile selected to archive thread 1 sequence 22
LGWR: Standby redo logfile selected for thread 1 sequence 22 for destination LOG_ARCHIVE_DEST_2
LGWR: RFS destination opened for reconnect at host 'sh'    =>LGWR歸檔線程通過主機sh傳輸遠端
Tue Jan 09 22:54:42 2018                                                   =>備庫RFS程序接收

--DG保護模式使用最大保護：主庫的日志通過LGWR程序通過TNSNAMES.ORA檔案遠端傳輸-備庫端RFS程序接收，按照備庫參數指定路徑存儲歸檔檔案：      

2.2 備庫關閉監聽

--正常情況下：主庫操作不受影響：
SQL> insert into a select * from emp where rownum=1;
SQL> commit;

制造異常：備庫：将監聽關閉:
$lsnrctl stop   
--主庫dml 
不受影響： ?????      

2.3 備庫關閉網絡

備庫停止網絡服務：
root: service network stop       

主庫
異常狀态一：主庫的事物操作，無法結束：DML操作無法commit;
SQL>  insert into a select * from emp where rownum=1;
1 row created.
SQL> commit;

異常狀态二：建立會話無法連接配接使用者
SQL> conn scott/tiger


異常狀态三：查詢告警日志：  10分鐘後，由于最大保護模式下，主庫發起多次重連，沒有結果，資料庫abort強制關閉

TNS-00513: Destination host unreachable
    nt secondary err code: 113
    nt OS err code: 0
--  警告：所有的備用資料庫的目的地都失敗了
WARNING: All standby database destinations have failed
--  警告：保護主節點需要關閉執行個體
WARNING: Instance shutdown required to protect primary
******************************************************
LGWR (ospid: 15287): terminating the instance due to error 16098
Tue Jan 09 23:24:17 2018
System state dump requested by (instance=1, osid=15287 (LGWR)), summary=[abnormal instance termination].      

2.4 主庫挂了，dg immediate

主庫shutdown abort;
最大保護模式，主庫挂了，備庫不允許immediate方式關閉
備庫
SYS >shutdown immediate;
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
 SYS >select status from v$instance;
STATUS
------------
OPEN
---主庫挂了，備庫無法immediate
>shutdown abort      

2.5 dg無法連接配接，主庫startup

---備庫啟動資料庫
>startup

--備庫監聽狀态查詢：關閉狀态
[oracle@sh ~]$ lsnrctl status
TNS-12541: TNS:no listener

--啟動主庫：
SQL> startup

Error 12541 received logging on to the standby
Check whether the listener is up and running.
LGWR: Error 12541 creating archivelog file 'sh'
Crash Recovery Foreground: All standby destinations have failed
******************************************************
WARNING: All standby database destinations have failed
WARNING: Instance shutdown required to protect primary
******************************************************
USER (ospid: 16354): terminating the instance due to error 16098
System state dump requested by (instance=1, osid=16354), summary=[abnormal instance termination].

特性：實驗證明：如果之前建立了連接配接，主庫與備庫直接，監聽程式斷開不受影響【監聽：提供新的會話連接配接】
---如果資料庫關閉狀态，備庫監聽關閉、網絡無法連接配接，則主庫無法open,最大保護模式      

三、閱讀官方文檔

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.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that
 a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent
 a single standby database failure from causing the primary database to shut down.
Note:
Asynchronously committed transactions are not protected by Data Guard against loss until the redo generated by those transactions
 has been written to the standby redo log of at least one synchronized standby database.
最大保護模式下，主庫的事務的日志 commit送出，除了需要本地 log buffer 資料 落盤寫入online reodo logfile中之外；
  還需要寫入到dg online reodolog 已確定資料不丢失，随後dg最快速度回報給主庫，通知dg備庫端日志寫完成，主庫commit 可以異步送出回報了。
假設有2個最大保護模式的dg,壞一個沒關系,主庫的日志保障寫入到任意一個的dg就可以進行回報。 
    這個與最近聽華為的gauss db 的備庫也是一樣的。