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

綠茶有點甜發表於2021-04-23
Oracle模式

一、需求

疑問？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 的備庫也是一樣的。

 

相關文章