dataguard replication apply log speed

[elmaazouz kamal]

Hello,

I made up a 10.2.0.3 DataGuard Configuration MAX PERFORMANCE MODE.(The two sites are separated with 100Km).

But when I launch a workload on the primary database, the log are transfered to the standby site but their apply by the redo log apply process (MRP) is very slow.

I tried to use parallel managed recovery but it generated a bug.

do any one have an idea  that can help?

Thanks a lot.
--
Amicalement
Kamal ELMAAZOUZ

[ddf]

Comments embedded.

On Dec 15, 9:44 am, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> Hello,
> I made up a 10.2.0.3 DataGuard Configuration MAX PERFORMANCE MODE.(The two
> sites are separated with 100Km).
> But when I launch a workload on the primary database, the log are transfered
> to the standby site but their apply by the redo log apply process (MRP) is
> very slow.

Define 'very slow'.

> I tried to use parallel managed recovery but it generated a bug.

I can believe it encountered a bug, but it didn't generate one. What
error did you receive and how do you know this is a bug? Post this
information if you really want assistance.

> do any one have an idea  that can help?

Provide more information so we can.

> Thanks a lot.
> --
> Amicalement
> Kamal ELMAAZOUZ

David Fitzjarrell

[elmaazouz kamal]

Hello David,

Primary Site:

==========

Oracle 10.2.0.3  RAC  3 nodes Linux RHEL AS 4.7 (intel Xeon 64bit).

SAN IBM DS4700 

 

Standby Sites:

===========

Oracle 10.2.0.3 one node Linux RHEL AS 4.4 (intel Xeon 64bit).

NEtAPP  fas 250.

 

The apply process is very slow because the database take a lot of time to be synchrounous.

the logs applied exceed 100 log files.

this the query i used  to check this :

select name, sequence#, applied,thread#,COMPLETION_TIME from gv$archived_log order by THREAD#, sequence# ;

when I launched the parallel managed recovery by the folowing sql :

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION NODELAY PARALLEL 2;

the error I got is the following :

ORA-16401: archivelog rejected by RFS

the bug is discussed in this metalink  URL

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:3807830372595872763::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,245840.1,1,0,1,helvetica

and :

 

RFS[7]: No standby redo logfiles of size 199777 blocks available

this is unbelievable : the standby logfiles are the same size as the primary ones.

 

for information the configuration is going to be made in a production sites  and the SLA is that  20min of service loss in the case of a SWITCHOVER / FAILOVER.

 

hanks for Help.

Kamal ELMAAZOUZ

[ddf]

Comments embedded.

On Dec 15, 10:16 am, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> Hello David,
> Primary Site:
> ==========
> Oracle 10.2.0.3  RAC  3 nodes Linux RHEL AS 4.7 (intel Xeon 64bit).
> SAN IBM DS4700
>
> Standby Sites:
> ===========
> Oracle 10.2.0.3 one node Linux RHEL AS 4.4 (intel Xeon 64bit).
> NEtAPP  fas 250.
>
> The apply process is very slow because the database take a lot of time to be
> synchrounous.
> the logs applied exceed 100 log files.
> this the query i used  to check this :
>

> *select name, sequence#, applied,thread#,COMPLETION_TIME from
> gv$archived_log order by THREAD#, sequence# ;*

>
> when I launched the parallel managed recovery by the folowing sql :
>

> *ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
> DISCONNECT FROM SESSION NODELAY PARALLEL 2;*

>
> the error I got is the following :

> *ORA-16401: archivelog rejected by RFS*
> *the bug is discussed in this metalink  URL *
> **
>
> https://metalink2.oracle.com/metalink/plsql/f?p=130:14:38078303725958...
>

Sorry, no, it isn't, as that page is simply a discussion of bug
description articles. Searching Metalink for this error produces this
page

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:8465824135674249338::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,4376276.8,1,1,1,helvetica

which clearly states the problem exists in version 10.1.0.4, fixed in
10.1.0.5 and 10.2.0.1. No other release is shown to be affected.

> and :
>
> *RFS[7]: No standby redo logfiles of size 199777 blocks available
> *
> *this is unbelievable : the standby logfiles are the same size as the
> primary ones.*
> **

I believe you misread the error, as it does NOT state logs of that
size do not exist, it states no log is AVAILABLE, and with 100 redo
logs to process that does not surprise me. Having a log available
means it's able to accept input; apparently the RFS process is writing
to the logs much faster than Oracle can process the data (which is no
surprise, as this is your complaint). The recovery process using redo
logs is not intended to be a 'bulk load' operation.

> *for information the configuration is going to be made in a production

> sites  and the SLA is that  20min of service loss in the case of a

> SWITCHOVER / FAILOVER.*
> **
> *hanks for Help.
> *

>
>
>
>
>
> On Mon, Dec 15, 2008 at 3:54 PM, ddf <orat...@msn.com> wrote:
>
> > Comments embedded.
>
> > On Dec 15, 9:44 am, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> > > Hello,
> > > I made up a 10.2.0.3 DataGuard Configuration MAX PERFORMANCE MODE.(The
> > two
> > > sites are separated with 100Km).
> > > But when I launch a workload on the primary database, the log are
> > transfered
> > > to the standby site but their apply by the redo log apply process (MRP)
> > is
> > > very slow.
>
> > Define 'very slow'.
>
> > > I tried to use parallel managed recovery but it generated a bug.
>
> > I can believe it encountered a bug, but it didn't generate one.  What
> > error did you receive and how do you know this is a bug?  Post this
> > information if you really want assistance.
>
> > > do any one have an idea  that can help?
>
> > Provide more information so we can.
>
> > > Thanks a lot.
> > > --
> > > Amicalement
> > > Kamal ELMAAZOUZ
>
> > David Fitzjarrell
>

> Kamal ELMAAZOUZ- Hide quoted text -
>
> - Show quoted text -


David Fitzjarrell

[elmaazouz kamal]

No sir, I think that the version I use is affected, please click the link "Physical Standby Database / Dataguard" look at

More Information about Versions Affected

the 10.2.0.3 version is affected

any way, I made a workload on the primary using a script, the logs are transfered to standby site so no problem with the network bandwidth, the MRP process is applying them but the apply is very slow compared to the speed in wich the standby receives the logs.

If I have a hardware problem in the primary in the Busy hour ( the daily  workload period) I'll have to switchover or failover to a standby database that is not synchronous with the primary with a gap of archive logs that is not applied.
I'm trying to set up an other configuration with the same sizing of redo log/standby log in production.
Do you have have any advice to speed up the MRP process ?
Best regards.

--
Amicalement
Kamal ELMAAZOUZ

[ddf]

Comments embedded.

On Dec 15, 11:33 am, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> No sir, I think that the version I use is affected, please click the
> link "Physical

> Standby Database / Dataguard <javascript:taghelp('TAGS_STANDBY')>" look at
> *More Information about Versions Affected
> *the 10.2.0.3 version is affected

No, it isn't. That link is to a page which EXPLAINS the prior page
information, it does not provide additional detail. And, the source
page clearly states the issue is FIXED in 10.2.0.1, the base release
of your 10.2.0.3 installation.

> any way, I made a workload on the primary
> using a script, the logs are transfered to standby site so no problem with
> the network bandwidth, the MRP process is applying them but the apply is
> very slow compared to the speed in wich the standby receives the logs.

Writing a log to a filesystem is much different from reading the
contents of that log and applying the changes to a large database.

> If I have a hardware problem in the primary in the Busy hour ( the daily
> workload period) I'll have to switchover or failover to a standby database
> that is not synchronous with the primary with a gap of archive logs that is
> not applied.

And you have again misread documentation from Oracle, as MAXIMUM
PERFORMANCE MODE is measured on the PRIMARY, not the standby, as
transactions on the primary are committed as soon as the redo data is
written to the logs:

"5.6.1.3 Maximum Performance Mode
This protection mode (the default) provides the highest level of data
protection that is possible without affecting the performance of the
primary database. This is accomplished by allowing a transaction to
commit as soon as the redo data needed to recover that transaction is
written to the local online redo log. The primary database's redo data
stream is also written to at least one standby database, but that redo
stream is written asynchronously with respect to the commitment of the
transactions that create the redo data."

It has nothing at all to do with the 'performance' of the redo process
on the standby. To ensure there is minimal data loss between the
standby and the primary you need to configure MAXIMUM PROTECTION mode;
this writes to both the primary and the standby and no transaction on
the primary can commit until the redo logs on the standby have been
written:

"5.6.1.1 Maximum Protection Mode
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 each transaction must be written to both the
local online redo log and to the standby redo log on at least one
standby database before the transaction commits. To ensure data loss
cannot occur, the primary database shuts down if a fault prevents it
from writing its redo stream to at least one remote standby redo log.
For multiple-instance RAC databases, Data Guard shuts down the primary
database if it is unable to write the redo records to at least one
properly configured database instance. The maximum protection mode
requires that at least one standby instance has a standby redo log and
the LGWR, SYNC, and AFFIRM attributes be used on the
LOG_ARCHIVE_DEST_n parameter for this destination."

> I'm trying to set up an other configuration with the same sizing of redo
> log/standby log in production.

You're fighting a losing battle since you clearly have the modes
confused. I say again you want MAXIMUM PROTECTION mode, not MAXIMUM
PERFORMANCE mode.

> Do you have have any advice to speed up the MRP process ?

Use the proper mode?

> Best regards.
>
> *

> *
>
>
>
>
>
> On Mon, Dec 15, 2008 at 5:18 PM, ddf <orat...@msn.com> wrote:
>
> > Comments embedded.
>

> >https://metalink2.oracle.com/metalink/plsql/f?p=130:14:84658241356742...

[elmaazouz kamal]

No sir,
all of this is available in official documentation.
You  misunderstood.
I want the MAXIMUM PERFORMANCE MODE.
the fact of choosing this mode must'nt affect the speed apply in the standby.
I know that the process of reading a  log and processing applying the data in is different from writing the log to a file system or ASM directory but I don't find any explnanation of the slow in the apply process, do you have any suggestion please let me know.
Just for your information, I passed from the maximum PERFORMANCE MODE to maximum AVAILABILITY MODE and I had the same problem.
when I'm runing a single SQL in the primary and then I stop the replication and open the standby DB I find the SQL appliyed in standby, but when I'm generating a workload the apply process is processing logs very slowly.
My only question is how to speed it up?
Best Regards.

--
Amicalement
Kamal ELMAAZOUZ

[ddf]

Comments embedded.

On Dec 15, 12:16 pm, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> No sir,

> all of this is available in official documentation.

Which I posted relevant portions of in my last post. Here is the link
to the entire page, including the explanations I posted, proving you
have misread the documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#sthref562

> You  misunderstood.

No, I haven't.

> I want the MAXIMUM PERFORMANCE MODE.

No, you don't, you want MAXIMUM PROTECTION mode to ensure that you
have no lag between the primary and the standby.

> the fact of choosing this mode must'nt affect the speed apply in the
> standby.

You don't understand the modes, sorry. When you do you'll see that I
am correct.

> I know that the process of reading a  log and processing applying the data
> in is different from writing the log to a file system or ASM directory but I
> don't find any explnanation of the slow in the apply process, do you have
> any suggestion please let me know.

I've given my suggestion: USE MAXIMUM PROTECTION MODE. You, of
course, ignore this since you believe you know better.

> Just for your information, I passed from the maximum PERFORMANCE MODE to
> maximum AVAILABILITY MODE and I had the same problem.

"5.6.1.2 Maximum Availability Mode
This protection mode provides the highest level of data protection
that is possible without compromising the availability of the primary
database. Like maximum protection mode, a transaction will not commit
until the redo needed to recover that transaction is written to the
local online redo log and to at least one remote standby redo log.
Unlike maximum protection mode, the primary database does not shut
down if a fault prevents it from writing its redo stream to a remote
standby redo log. Instead, the primary database operates in maximum
performance mode until the fault is corrected and all gaps in redo log
files are resolved. When all gaps are resolved, the primary database
automatically resumes operating in maximum availability mode."

Both MAXIMUM PERFORMANCE MODE and MAXIMUM AVAILABILITY MODE are
designed to ensure the PRIMARY performs as expected; neither one
affects the performance of the STANDBY. The ONLY mode that will keep
both databases synchronized in real time is MAXIMUM PROTECTION MODE,
which, as a side benefit, keeps the redo apply process on the standby
running as quickly as possible. And THAT is also in the official
documentation.

> when I'm runing a single SQL in the primary and then I stop the replication
> and open the standby DB I find the SQL appliyed in standby, but when I'm
> generating a workload the apply process is processing logs very slowly.

And I say yet AGAIN that you are using the INCORRECT MODE to apply
your standby redo as quickly as possible. Know, however, that if an
error occurs which prevents the redo from being written to the standby
in MAXIMUM PROTECTION MODE then the primary database shuts down until
the issue is resolved. This does NOT occur with the other two modes,
but that means that MAXIMUM AVAILABILITY MODE automatically switches
to MAXIMUM PERFORMAQNCE MODE to keep the PRIMARY running until the
remote redo write problem is resolved.

[elmaazouz kamal]

The max protection mode is generating performance issues on my primary database since the commit of each transaction is waiting for the commit acknowledgement in the standby, so this is not a very useful mode for me even if it guaranties the synchronisation of the two databases etc ....
The mode I accept to use is maximum avaliability: but unfortunately in the case of a workload, the redo apply is slow and there's a lot of logs transmitted to the standby site which are not applied.
Is there any way to speed up the apply process in THIS MODE (maximum availability) ?

--
Amicalement
Kamal ELMAAZOUZ

[ddf]

On Dec 15, 12:44 pm, "elmaazouz kamal" <elmaaz...@gmail.com> wrote:
> The max protection mode is generating performance issues on my primary
> database since the commit of each transaction is waiting for the commit
> acknowledgement in the standby, so this is not a very useful mode for me
> even if it guaranties the synchronisation of the two databases etc ....
> The mode I accept to use is maximum avaliability: but unfortunately in the
> case of a workload, the redo apply is slow and there's a lot of logs
> transmitted to the standby site which are not applied.
> Is there any way to speed up the apply process in THIS MODE (maximum
> availability) ?
>
>
>

Not without adding CPUs.. According to the official documentation:

"The managed recovery process (MRP) applies archived redo log files to
the physical standby database, and automatically determines the
optimal number of parallel recovery processes at the time it starts.
The number of parallel recovery slaves spawned is based on the number
of CPUs available on the standby server."

The above text from the same link I provided in this thread.


David Fitzjarrell

[elmaazouz kamal]

Thank you so much.

--
Amicalement
Kamal ELMAAZOUZ