.
\nI see lot of databases with two members for redo logs and also two members for standby redo logs. Why not, but when asking I realized that there are some mis-comprehension about it. And what was recommended 10 years ago may be different today.
\n<\/p>\n
Your transactions happen on the primary database and are written to the online redo logs before the modification is done on datafiles. And when you commit you wait to be sure that the redo is on persistence storage. If you loose the current redo log group, then your database crashes and you loose the latest transactions. This is why we multiplex the online redo logs. Even if you are 100% confident on your storage high availability the risk of human error dropping a file exists and is considerably lower if there a two files.
\nFor additional protection, in case you loose all the primary redo members, Data Guard synchronizes the transaction to a second site by shipping the redo stream. There, on the standby site, the redo is written to the standby redo logs.<\/p>\n
The online redo logs are used only on the primary site, and should better be named primary redo logs. You create them on the standby site only to be prepared for failover, when it will become the primary and opened read-write. But let’s be clear: online redo logs are not used when database is not online, and mount is not online.<\/p>\n
The standby redo logs are not standby at all. They are actively used on the standby site and this is why thew are called ‘standby. On the primary, they are not used, just there to be ready when the primary becomes a standby after a failover.<\/p>\n
We have seen why we multiplex the online redo logs:<\/p>\n
But this is different with standby redo logs. <\/p>\n
Of course, if in Maximum Protection mode the availability of the primary is compromised when the standby cannot apply the redo in SYNC. But in this protection mode you probably have multiple standby and the loss of one standby redo log on one standby site it not a problem.<\/p>\n
I said that transactions are still persisted on the primary, but even without standby redo logs they are still shipped to standby site, but in ASYNC mode. This means that in order to loose transactions in case of the loss of a standby redo log group, you need to experience this file loss, and primary site failure and network failure at the same time. The probability for this is very low and having an additional member do not lower the risk.<\/p>\n
Of course, I’ve tested what happens. I have two standby redo log members and I removed all of them one minute ago:<\/p>\n
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Mar 17 14:47:45 2017\n \nCopyright (c) 1982, 2016, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type \"help\" for information.\nConnected to \"ORCLA\"\nConnected as SYSDBA.\n \nDatabase - orclb\n \n Role: PHYSICAL STANDBY\n Intended State: APPLY-ON\n Transport Lag: 0 seconds (computed 0 seconds ago)\n Apply Lag: 1 minute 30 seconds (computed 0 seconds ago)\n Average Apply Rate: 0 Byte\/s\n Active Apply Rate: 0 Byte\/s\n Maximum Apply Rate: 0 Byte\/s\n Real Time Query: ON\n Instance(s):\n ORCLB\n \n Database Warning(s):\n ORA-16853: apply lag has exceeded specified threshold\n ORA-16857: member disconnected from redo source for longer than specified threshold\n ORA-16826: apply service state is inconsistent with the DelayMins property\n ORA-16789: standby redo logs configured incorrectly\n<\/code><\/pre>\nAs you can see, when there is no member remaining, the APPLY is stuck but transport still happens, in ASYNC to archived logs.
\nThe standby alert log mentions the failure:<\/p>\n
2017-03-17T14:51:21.209611+01:00\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/orclb\/ORCLB\/trace\/ORCLB_rsm0_6568.trc:\nORA-00313: open failed for members of log group 5 of thread 1\nORA-00312: online log 5 thread 1: '\/u01\/oradata\/ORCLB\/onlinelog\/m5.log'\nORA-27048: skgfifi: file header information is invalid\nAdditional information: 2\nORA-00312: online log 5 thread 1: '\/u01\/oradata\/ORCLB\/onlinelog\/o1_mf_5_dbvmxd52_.log'\nORA-27048: skgfifi: file header information is invalid\nAdditional information: 2<\/code><\/pre>\nand the SYNC mode cannot continue without standby redo logs:<\/p>\n
03\/17\/2017 14:58:15\nFailed to open SRL files. ORA-313\nRedo Apply is running without NODELAY option while DelayMins=0\nFailed to open SRL files. ORA-313\nError: LogXptMode value 'SYNC' of requires this database to have standby redo logs, but they are not configured correctly.\n03\/17\/2017 14:59:15\nFailed to open SRL files. ORA-313\nRedo Apply is running without NODELAY option while DelayMins=0\nFailed to open SRL files. ORA-313\nError: LogXptMode value 'SYNC' of requires this database to have standby redo logs, but they are not configured correctly.<\/code><\/pre>\nSure, you don’t want to loose the standby redo member. But the risk is not higher than loosing any other files, and this is why there is no reason to multiplex it. Standby redo logs are not the same as the primary online redo logs. On similar hardware, you need same size and you need one more group, but no reason to multiplex the same.<\/p>\n
Documentation<\/h3>\n
The confusion may come from old documentation. The 10g<\/em> documentation<\/a> says:
\nFor increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.<\/em>
\nThis documentation dates from 2005 and systems have changed about availability of files. <\/p>\nMore recent documentation is the white paper on Best Practices for Synchronous Redo Transport<\/a> which mentions: It is critical for performance that standby redo log groups only contain a single member<\/em><\/p>\nSo what?<\/h3>\n
At the time of 10g<\/em> we had a LUN for redo logs and were not concerned by the size, but more by its unavailability. Things change. Losing a file, and only one file, today is extremely rare. We are more concerned about consolidation and performance. Having 4 online groups, 200MB or 500MB, and 5 standby groups, all multiplexed, for many databases will take space. And this space you want to allocate it on the fastest disks because user commits wait on log writes (on primary and standby except in Max Performance). You don’t want to over-allocate the space here. Better have larger online redo logs. And your goal is that network shipping + standby log writing takes not longer than local write to online redo logs, so that Data Guard protection do not increase commit latency. Multiplexing standby redo logs increases the risk to get longer writes on standby site. <\/p>\nSo if you have your standby redo logs multiplexed, it’s not wrong. But things change and today you may prefer to save space and performance overhead with only one member.<\/p>\n
Before writing this blog post, my poll on twitter had 40 votes. Only 28% mentioned no multiplexing. But twitter poll is not exact science as you can see that 8 people answered 42 members \ud83d\ude09<\/p>\n
\nBecause I've seen lot of misunderstanding about it, I'm curious to know how many members you have in your standby redo logs<\/p>\n
— Franck Pachot (@FranckPachot) March 17, 2017<\/a><\/p><\/blockquote>\n