archive_lag_target with real time apply for data guard

Discussion:

max scalf

2015-12-20 18:48:20 UTC

Hello list,

We are in process of setting up data guard and one of the thing i was
thinking was to set "archive_lag_target" to 1800 seconds, so that i do not
loose my redo data on the standby site if there was a failure on primary,
but then it also got me thinking, I am using real time apply(this is
specific to 11g system). From what i understand about real-time apply is
"it allows Data Guard to recover redo data from the current standby redo
log as it is being filled by the RFS process", if that is the case why
would i need to set archive_lag_target as my data is being pushed out
anyways ??

thoughts ??

Andrew Kerber

2015-12-20 19:09:35 UTC

Permalink

As I understand the feature, it only applies to log switching. It was probably named that when asynchronous data guard was more common. It really only applies to recovery on the primary.

Sent from my iPad

max scalf

2015-12-20 19:13:54 UTC

Permalink

Hi andrew,

so are you saying its okay to ignore that parameter on primary ?? as i am
using asynchronous(max perf mode) with real time apply ?

Post by Andrew Kerber
As I understand the feature, it only applies to log switching. It was
probably named that when asynchronous data guard was more common. It really
only applies to recovery on the primary.
Sent from my iPad
Hello list,
We are in process of setting up data guard and one of the thing i was
thinking was to set "archive_lag_target" to 1800 seconds, so that i do not
loose my redo data on the standby site if there was a failure on primary,
but then it also got me thinking, I am using real time apply(this is
specific to 11g system). From what i understand about real-time apply is
"it allows Data Guard to recover redo data from the current standby redo
log as it is being filled by the RFS process", if that is the case why
would i need to set archive_lag_target as my data is being pushed out
anyways ??
thoughts ??

Andrew Kerber

2015-12-20 20:21:23 UTC

Permalink

Well, not quite. If you are in max availability mode, and your primary falls back into asynchronous mode, then you need to be concerned. Also, oracle still recommends that log switches still occur at least 3 times/hour the last time I checked, and I use the parameter regularly to ensure switches happen often enough.

Sent from my iPad

Post by max scalf
Hi andrew,
so are you saying its okay to ignore that parameter on primary ?? as i am using asynchronous(max perf mode) with real time apply ?

max scalf

2015-12-20 21:18:09 UTC

Permalink

okay that sound good to me, so i will have to put that parameter in place
and also fix archivelagtarget parameter in dgmgrl... or setting parameter
in dgmgrl would also put those change in spfile file ?

Post by Andrew Kerber
Well, not quite. If you are in max availability mode, and your primary
falls back into asynchronous mode, then you need to be concerned. Also,
oracle still recommends that log switches still occur at least 3 times/hour
the last time I checked, and I use the parameter regularly to ensure
switches happen often enough.
Sent from my iPad
Hi andrew,
so are you saying its okay to ignore that parameter on primary ?? as i am
using asynchronous(max perf mode) with real time apply ?

kathryn axelrod

2015-12-20 21:39:57 UTC

Permalink

Adding to what Andrew said, real time apply sends online log info..this may
be sent anyway depending on other configs.

With max performance, it sends online log info when it's comitted on the
primary..but the primary pays no attention to when the standby applies it.

You may want to consider switching to max availability with at least one
standby using sync/affirm. It is a compromise between absolute 0 data loss
and no primary downtime with minimal primary waits.

With max availability, as long as a standby is available, it sends info to
the standby as soon as it's comitted to the primary's online log. (If no
standby is available, it temporarily switches to max performance so the
primary can continue).
With async/ noaffirm, the primary waits for the response that that standby
received the data. Sync/ affirm = wait to hear the standby Applied the data.

Alternately, with max protection, it is 0 data loss..but the primary may
shut down when it doesn't get a data confirmation from the standbys.

https://docs.oracle.com/database/121/SBYDB/protection.htm#SBYDB4743

Post by max scalf
Hi andrew,
so are you saying its okay to ignore that parameter on primary ?? as i am
using asynchronous(max perf mode) with real time apply ?

Mladen Gogala

2015-12-20 23:00:54 UTC

Permalink

Post by kathryn axelrod
Adding to what Andrew said, real time apply sends online log
info..this may be sent anyway depending on other configs.
With max performance, it sends online log info when it's comitted on
the primary..but the primary pays no attention to when the standby
applies it.
You may want to consider switching to max availability with at least
one standby using sync/affirm. It is a compromise between absolute 0
data loss and no primary downtime with minimal primary waits.
With max availability, as long as a standby is available, it sends
info to the standby as soon as it's comitted to the primary's online
log. (If no standby is available, it temporarily switches to max
performance so the primary can continue).
With async/ noaffirm, the primary waits for the response that that
standby received the data. Sync/ affirm = wait to hear the standby
Applied the data.
Alternately, with max protection, it is 0 data loss..but the primary
may shut down when it doesn't get a data confirmation from the standbys.
https://docs.oracle.com/database/121/SBYDB/protection.htm#SBYDB4743

There is a significant performance penalty for both maximum availability
and maximum protection. Most of the places I've seen run in maximum
performance mode. The choice depends on the business needs.

--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l

kathryn axelrod

2015-12-20 23:55:11 UTC

Permalink

Indeed, 'significant' depends on the environment and business needs. In
this case, he seemed more concerned about data loss, so max availability
may be worth looking into.

Post by Mladen Gogala

Post by kathryn axelrod
Adding to what Andrew said, real time apply sends online log info..this
may be sent anyway depending on other configs.
With max performance, it sends online log info when it's comitted on the
primary..but the primary pays no attention to when the standby applies it.
You may want to consider switching to max availability with at least one
standby using sync/affirm. It is a compromise between absolute 0 data loss
and no primary downtime with minimal primary waits.
With max availability, as long as a standby is available, it sends info
to the standby as soon as it's comitted to the primary's online log. (If no
standby is available, it temporarily switches to max performance so the
primary can continue).
With async/ noaffirm, the primary waits for the response that that
standby received the data. Sync/ affirm = wait to hear the standby Applied
the data.
Alternately, with max protection, it is 0 data loss..but the primary may
shut down when it doesn't get a data confirmation from the standbys.
https://docs.oracle.com/database/121/SBYDB/protection.htm#SBYDB4743
There is a significant performance penalty for both maximum availability

and maximum protection. Most of the places I've seen run in maximum
performance mode. The choice depends on the business needs.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
--
http://www.freelists.org/webpage/oracle-l

Mark W. Farnham

2015-12-21 14:12:49 UTC

Permalink

Has anyone mentioned in this thread the primary purpose of archive_lag_target to allow you to safely send the updates off site without applying them so that in a case of application accident or malicious attack against the primary you may be able to intercept the application on the standby so you can instantiate the âbefore accidentâ state of data to evaluate the best possible result without doing a full recovery to a point in time?

Sigh. Yes, it also mitigates the production throughput overhead of max availability and max protection.

mwf

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of kathryn axelrod
Sent: Sunday, December 20, 2015 4:40 PM
To: ***@gmail.com
Cc: Andrew Kerber; Oracle Mailing List
Subject: archive_lag_target with real time apply for data guard

Adding to what Andrew said, real time apply sends online log info..this may be sent anyway depending on other configs.

With max performance, it sends online log info when it's comitted on the primary..but the primary pays no attention to when the standby applies it.

You may want to consider switching to max availability with at least one standby using sync/affirm. It is a compromise between absolute 0 data loss and no primary downtime with minimal primary waits.

With max availability, as long as a standby is available, it sends info to the standby as soon as it's comitted to the primary's online log. (If no standby is available, it temporarily switches to max performance so the primary can continue).

With async/ noaffirm, the primary waits for the response that that standby received the data. Sync/ affirm = wait to hear the standby Applied the data.

Alternately, with max protection, it is 0 data loss..but the primary may shut down when it doesn't get a data confirmation from the standbys.

https://docs.oracle.com/database/121/SBYDB/protection.htm#SBYDB4743

On Sunday, December 20, 2015, max scalf <***@gmail.com> wrote:

Hi andrew,

so are you saying its okay to ignore that parameter on primary ?? as i am using asynchronous(max perf mode) with real time apply ?

On Sun, Dec 20, 2015 at 1:09 PM, Andrew Kerber <***@gmail.com> wrote:

As I understand the feature, it only applies to log switching. It was probably named that when asynchronous data guard was more common. It really only applies to recovery on the primary.

Sent from my iPad

On Dec 20, 2015, at 12:48 PM, max scalf <***@gmail.com> wrote:

Hello list,

We are in process of setting up data guard and one of the thing i was thinking was to set "archive_lag_target" to 1800 seconds, so that i do not loose my redo data on the standby site if there was a failure on primary, but then it also got me thinking, I am using real time apply(this is specific to 11g system). From what i understand about real-time apply is "it allows Data Guard to recover redo data from the current standby redo log as it is being filled by the RFS process", if that is the case why would i need to set archive_lag_target as my data is being pushed out anyways ??

thoughts ??

Mladen Gogala

2015-12-20 22:57:02 UTC

Permalink

Hi Max!
Real-time apply is so-called "active data guard", which allows you to
query the standby DB in read only mode. Very useful for reporting.
Beware that loading data in GTT's does not work with the Active DG.
Also, beware that active DG is not free, there is a license fee.

--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

max scalf

2015-12-20 23:40:02 UTC

Permalink

Hello mladen,

I am not opening database in read only mode, only using "using current
logfile"...are you saying using that counts against active data guard? I
thought real time apply was free/included feature of enterprise edition...

Post by max scalf
Hello list,
We are in process of setting up data guard and one of the thing i was
thinking was to set "archive_lag_target" to 1800 seconds, so that i do not
loose my redo data on the standby site if there was a failure on primary,
but then it also got me thinking, I am using real time apply(this is
specific to 11g system). From what i understand about real-time apply is
"it allows Data Guard to recover redo data from the current standby redo
log as it is being filled by the RFS process", if that is the case why
would i need to set archive_lag_target as my data is being pushed out
anyways ??
thoughts ??
Hi Max!
Real-time apply is so-called "active data guard", which allows you to
query the standby DB in read only mode. Very useful for reporting. Beware
that loading data in GTT's does not work with the Active DG. Also, beware
that active DG is not free, there is a license fee.
--
Mladen Gogala
Oracle DBAhttp://mgogala.freehostia.com

max scalf

2015-12-20 23:50:48 UTC

Permalink

I am not using real time query only the real time apply, that is count as
active data guard?

Post by max scalf
Hello mladen,
I am not opening database in read only mode, only using "using current
logfile"...are you saying using that counts against active data guard? I
thought real time apply was free/included feature of enterprise edition...

kathryn axelrod

2015-12-21 00:02:08 UTC

Permalink

No.

Post by max scalf
I am not using real time query only the real time apply, that is count as
active data guard?

kathryn axelrod

2015-12-21 00:01:10 UTC

Permalink

You are correct; real time apply is a free feature of ee data guard. It
simply changes the process from waiting for actual archive logs to sending
'in real time' using redo logs.

Mladen Gogala

2015-12-21 06:53:59 UTC

Permalink

I am not quite sure about that. Whenever I opened the standby in
read-only mode, "real time apply" went from "NO" to "YES". Standby was
in the maximum performance mode, with DG broker configured.

--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l

Mark W. Farnham

2015-12-21 14:26:49 UTC

Permalink

Active data guard switches on real time apply. (Because it needs to be up to date to fulfill the feature description.)

Switching on real time apply does not switch on active data guard. (And serves maximum protection without adding to license fees.)

Your observation was accurate, but in this instance the application of converse logic was in error. The license fees possible in audit due to accidental temporary usage of a feature tend to make us justifiably paranoid about this stuff.

mwf

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Mladen Gogala
Sent: Monday, December 21, 2015 1:54 AM
To: kathryn axelrod; ***@gmail.com
Cc: oracle-***@freelists.org
Subject: Re: archive_lag_target with real time apply for data guard

I am not quite sure about that. Whenever I opened the standby in read-only mode, "real time apply" went from "NO" to "YES". Standby was in the maximum performance mode, with DG broker configured.

--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Mladen Gogala

2015-12-21 19:55:14 UTC

Permalink

Post by Mark W. Farnham
Active data guard switches on real time apply. (Because it needs to be up to date to fulfill the feature description.)
Switching on real time apply does not switch on active data guard. (And serves maximum protection without adding to license fees.)
Your observation was accurate, but in this instance the application of converse logic was in error. The license fees possible in audit due to accidental temporary usage of a feature tend to make us justifiably paranoid about this stuff.
mwf
-----Original Message-----
Sent: Monday, December 21, 2015 1:54 AM
Subject: Re: archive_lag_target with real time apply for data guard

I am not quite sure about that. Whenever I opened the standby in read-only mode, "real time apply" went from "NO" to "YES". Standby was in the maximum performance mode, with DG broker configured.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
--
http://www.freelists.org/webpage/oracle-l

Mark, I confused real time apply with real time query. The names are
slightly confusing and holiday atmosphere and late responding to my
email don't really help. Darned eggnog doesn't help either.
I wish happy holidays to everybody.

--
Mladen GogalaMark
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l

Mladen Gogala

2015-12-21 06:56:32 UTC

Permalink

Yes, you are correct:

7.2.1 Using Real-Time Apply to Apply Redo Data Immediately

If the real-time apply feature is enabled, apply services can apply redo
data as it is received, without waiting for the current standby redo log
file to be archived. This results in faster switchover and failover
times because the standby redo log files have been applied already to
the standby database by the time the failover or switchover begins.

Use the |ALTER DATABASE| statement to enable the real-time apply
feature, as follows:

*

For physical standby databases, issue the |ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE USING CURRENT LOGFILE| statement.

*

For logical standby databases, issue the |ALTER DATABASE START
LOGICAL STANDBY APPLY IMMEDIATE| statement.

(https://docs.oracle.com/cd/E11882_01/server.112/e41134/log_apply.htm#SBYDB0050)

--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

Nassyam Basha

2015-12-21 09:41:03 UTC

Permalink

Hello Max,
I don't think this parameter can be considered in this generation when
thinking of high availability solutions. As simple if you want to achieve
real-time apply then ignore the parameter.
There are other options available to overcome the parameter
archive_lag_target....they are
1) Switching time --> In that case why can't you consider redo log file
sizing based on the switch count per hour?
2) Delay --> If you want to ensure your standby should lag with primary for
15 or 30 minutes, then you can consider delay_mins attribute for each
destination... The best part is if you have two standby databases...then
one standby you can configure Real-Time apply and the other with delay_mins
, so that data can be restored even though you have missed/truncate the
table on primary.
archive_lag_target where can be used in various situations like downstream
capture but it doesn't make sense for your requirement and not think of it.
Please let us know if any questions.
Thank You.

Post by kathryn axelrod
You are correct; real time apply is a free feature of ee data guard. It
simply changes the process from waiting for actual archive logs to sending
'in real time' using redo logs.
7.2.1 Using Real-Time Apply to Apply Redo Data Immediately
If the real-time apply feature is enabled, apply services can apply redo
data as it is received, without waiting for the current standby redo log
file to be archived. This results in faster switchover and failover times
because the standby redo log files have been applied already to the standby
database by the time the failover or switchover begins.
Use the ALTER DATABASE statement to enable the real-time apply feature,
-
For physical standby databases, issue the ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.
-
For logical standby databases, issue the ALTER DATABASE START LOGICAL
STANDBY APPLY IMMEDIATE statement.
(
https://docs.oracle.com/cd/E11882_01/server.112/e41134/log_apply.htm#SBYDB0050
)
--
Mladen Gogala
Oracle DBAhttp://mgogala.freehostia.com

--
Nassyam Basha.
*Oracle Database Consultant*| *Pythian * <http://www.pythian.com/>
[image: ACED Profile]
<https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:13140>
Oracle 11g Certified Master
<http://education.oracle.com/education/otn/NassyamBasha.htm>
Co-Author: Oracle Data Guard 11gR2
<http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
Co-founder of Oraworld-team <http://www.oraworld-team.com>

[image: Facebook] <https://www.facebook.com/nassyambasha> [image: Twitter]
<https://twitter.com/oracle_ckpt> [image: LinkedIn]
<https://in.linkedin.com/in/nassyambasha> [image: Google +]
<https://plus.google.com/+NassyamBasha> [image: CKPT Blog]
<http://www.oracle-ckpt.com/>
<https://www.facebook.com/nassyambasha>