一、需求

疑问？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 的备库也是一样的。