Hi,
At our site we are having difficulty getting approval from business to
enable force logging on database objects.
We plan to implement DR using manual method ie (shipping archive logs to DR
site through cronjob, performing manual recovery on mounted standby
controlfile)
I understand without forcelogging all nologging transactions wont be
recoverable. Is this true during recovery or after activating the standby?
Is forcelogging a mandatory pre-requisite for implementing Dataguard? Has
any of you have experience implementing Dataguard without force logging
enabled.
Will there be any issues during managed recovery if it encounters a
nologging change? Wont we be hitting same issue if we are doing the recovery
manually as opposed to MRP process?
Regards,
Sanjeev.
RE: Manual Standby as alternative to dataguard by Ric Van Dyke on
2009-06-16T18:59:47+00:00
Yeap. If forced logging is not turned on then anything done with the
nologging option will not be in the redo stream. This makes no
difference if you're using a standby database or not. Since it's not in
the redo stream it will not be applied to the standby database and it
will not be there if you do any sort of recovery on the primary database
as well. NOLOGGING means certain actions are not written to the redo
log buffer; hence they don't get into the online redo log files so that
means they are not in the archive log files.=20
=20
Why is it that the biz folks are so apposed to turning on forced
logging? I presume they think there will be a performance hit, of
course it all depends on what you are doing, but there is a good chance
no one will notice.=20
=20
Also force logging is a database level setting, not the individual
objects. It's one parameter that over rides the setting on an object,
unlike most other settings. Just reading your text it sounds like you
want to do this at the object level, you don't have to. Even if an
object is set to NOLOGGING, if you set forced logging on then everything
gets logged even for the objects with NOLOGGING.=20
=20
=20
=20
=20
enable force logging on database objects.
=20
We plan to implement DR using manual method ie (shipping archive logs to
DR site through cronjob, performing manual recovery on mounted standby
controlfile)
=20
I understand without forcelogging all nologging transactions wont be
recoverable. Is this true during recovery or after activating the
standby?
=20
Is forcelogging a mandatory pre-requisite for implementing Dataguard?
Has any of you have experience implementing Dataguard without force
logging enabled.
=20
Will there be any issues during managed recovery if it encounters a
nologging change? Wont we be hitting same issue if we are doing the
recovery manually as opposed to MRP process?
=20
Regards,
Sanjeev.
Re: Manual Standby as alternative to dataguard by Mathias Magnusson on
2009-06-16T19:00:45+00:00
What kind of problem does the business see with force logging? What
transactions do you have that they require nologging on that is critical to
the business?
That is, are you fighting a theoretical or real problem? Force logging is
that because it is needed. You cannot invent data that is now written to the
log files. Manual or not, you'll have the same need to have logging occur.
I think you are better off dealing with the actual issue, rather than
something that seems to an opinion not based on hard data.
Mathias
On Tue, Jun 16, 2009 at 8:41 PM, sanjeev m <sanjeevorcle@gmail.com> wrote:
> Hi,
>
> At our site we are having difficulty getting approval from business to
> enable force logging on database objects.
>
> We plan to implement DR using manual method ie (shipping archive logs to DR
> site through cronjob, performing manual recovery on mounted standby
> controlfile)
>
> I understand without forcelogging all nologging transactions wont be
> recoverable. Is this true during recovery or after activating the standby?
>
> Is forcelogging a mandatory pre-requisite for implementing Dataguard? Has
> any of you have experience implementing Dataguard without force logging
> enabled.
>
> Will there be any issues during managed recovery if it encounters a
> nologging change? Wont we be hitting same issue if we are doing the recovery
> manually as opposed to MRP process?
>
> Regards,
> Sanjeev.
>
Re: Manual Standby as alternative to dataguard by Jared Still on
2009-06-16T19:36:32+00:00
On Tue, Jun 16, 2009 at 11:41 AM, sanjeev m <sanjeevorcle@gmail.com> wrote:
> Hi,
>
> At our site we are having difficulty getting approval from business to
> enable force logging on database objects.
>
It is simple really.
This isn't a technical problem.
IT (you and your mgr) need to present to the business community
that without this, there will be no disaster recovery.
Then you put the responsibility on them:
a) yes, we will do DR.
b) no, we will not do DR.
They have to agree to the choice they make in writing.
(email will do)
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Re: Manual Standby as alternative to dataguard by sanjeev m on
2009-06-16T19:45:47+00:00
They are expecting performance issues. I agree this has to be tested with
them after enabling it in non-production evironment and if there are still
performance issues really then move off those segments to a different
tablespace,Enable force logging for the rest of the tablespaces atleast.We
will set the expectation with business that those objects with nologging
cant be recovered on activation of standby
My question is
(*) will the Dataguard managed recovery itself have any issues if there is
noforce logging on primary?
(*) In other words is there any difference in implementing using manual
method versus Dataguard (MRP) with nologging as long as there is no impact
to log apply(recovery) on standby.
On Tue, Jun 16, 2009 at 11:59 AM, Mathias Magnusson <
mathias.magnusson@gmail.com> wrote:
> What kind of problem does the business see with force logging? What
> transactions do you have that they require nologging on that is critical to
> the business?
>
> That is, are you fighting a theoretical or real problem? Force logging is
> that because it is needed. You cannot invent data that is now written to the
> log files. Manual or not, you'll have the same need to have logging occur.
>
> I think you are better off dealing with the actual issue, rather than
> something that seems to an opinion not based on hard data.
>
> Mathias
>
>
> On Tue, Jun 16, 2009 at 8:41 PM, sanjeev m <sanjeevorcle@gmail.com> wrote:
>
>> Hi,
>>
>> At our site we are having difficulty getting approval from business to
>> enable force logging on database objects.
>>
>> We plan to implement DR using manual method ie (shipping archive logs to
>> DR site through cronjob, performing manual recovery on mounted standby
>> controlfile)
>>
>> I understand without forcelogging all nologging transactions wont be
>> recoverable. Is this true during recovery or after activating the standby?
>>
>> Is forcelogging a mandatory pre-requisite for implementing Dataguard? Has
>> any of you have experience implementing Dataguard without force logging
>> enabled.
>>
>> Will there be any issues during managed recovery if it encounters a
>> nologging change? Wont we be hitting same issue if we are doing the recovery
>> manually as opposed to MRP process?
>>
>> Regards,
>> Sanjeev.
>>
>
>
RE: Manual Standby as alternative to dataguard by Ric Van Dyke on
2009-06-16T20:07:16+00:00
The apply process will apply what ever it gets from the primary. If
there are data integrity issues, then you will have to sort that out
when you try to active the standby.
=20
So the answers to your direct questions are NO and NO.=20
=20
Like Jared said, this is not a technical issue. Dataguard will run just
happy as a pig in slop no matter what the setting of FORCE LOGGING on
the primary. You just might not be able to use the database when you
try to active it if it's not turned on. If they want to run with out
turning on FORCED LOGGING then they are kidding them selves about having
a DG site. And you and your IT staff better have that in writing so
they don't think they do have a DG site when they really don't. =20
=20
How much stuff is running with NOLOGGING anyway? And does everyone
really know what NOLOGGING does? It may not be doing what they think it
is doing anyway.=20
=20
=20
=20
objects with nologging cant be recovered on activation of standby
My question is
=20
(*) will the Dataguard managed recovery itself have any issues if there
is noforce logging on primary?
(*) In other words is there any difference in implementing using manual
method versus Dataguard (MRP) with nologging as long as there is no
impact to log apply(recovery) on standby.
=20
On Tue, Jun 16, 2009 at 11:59 AM, Mathias Magnusson
<mathias.magnusson@gmail.com> wrote:
What kind of problem does the business see with force logging? What
transactions do you have that they require nologging on that is critical
to the business?
=20
That is, are you fighting a theoretical or real problem? Force logging
is that because it is needed. You cannot invent data that is now written
to the log files. Manual or not, you'll have the same need to have
logging occur.
=20
I think you are better off dealing with the actual issue, rather than
something that seems to an opinion not based on hard data.
Mathias=20
=20
On Tue, Jun 16, 2009 at 8:41 PM, sanjeev m <sanjeevorcle@gmail.com>
wrote:
Hi,
=20
At our site we are having difficulty getting approval from business to
enable force logging on database objects.
=20
We plan to implement DR using manual method ie (shipping archive logs to
DR site through cronjob, performing manual recovery on mounted standby
controlfile)
=20
I understand without forcelogging all nologging transactions wont be
recoverable. Is this true during recovery or after activating the
standby?
=20
Is forcelogging a mandatory pre-requisite for implementing Dataguard?
Has any of you have experience implementing Dataguard without force
logging enabled.
=20
Will there be any issues during managed recovery if it encounters a
nologging change? Wont we be hitting same issue if we are doing the
recovery manually as opposed to MRP process?
=20
Regards,
Sanjeev.
=20
=20
Re: Manual Standby as alternative to dataguard by sanjeev m on
2009-06-16T21:12:15+00:00
Thanks to all for the clarifications.
There were only small fraction of tables that do no have forcelogging
enabled.
Regards.
On Tue, Jun 16, 2009 at 1:07 PM, Ric Van Dyke <ric.van.dyke@hotsos.com>wrot=
e:
> The apply process will apply what ever it gets from the primary. If ther=
e
> are data integrity issues, then you will have to sort that out when you t=
ry
> to active the standby.
>
>
>
> So the answers to your direct questions are NO and NO.
>
>
>
> Like Jared said, this is not a technical issue. Dataguard will run just
> happy as a pig in slop no matter what the setting of FORCE LOGGING on the
> primary. You just might not be able to use the database when you try to
> active it if it=92s not turned on. If they want to run with out turning =
on
> FORCED LOGGING then they are kidding them selves about having a DG site.
> And you and your IT staff better have that in writing so they don=92t th=
ink
> they do have a DG site when they really don=92t.
>
>
>
> How much stuff is running with NOLOGGING anyway? And does everyone reall=
y
> know what NOLOGGING does? It may not be doing what they think it is doin=
g
> anyway.
>
>
>
> >
>
>
> *Hotsos Symposium *
>
> *March 7 =96 11, 2010 *
>
> *Be there.*
>
>
>
>
> > *Subject:* Re: Manual Standby as alternative to dataguard
>
>
>
> They are expecting performance issues. I agree this has to be tested with
> them after enabling it in non-production evironment and if there are stil=
l
> performance issues really then move off those segments to a different
> tablespace,Enable force logging for the rest of the tablespaces atleast.W=
e
> will set the expectation with business that those objects with nologging
> cant be recovered on activation of standby
>
> My question is
>
>
>
> (*) will the Dataguard managed recovery itself have any issues if there i=
s
> noforce logging on primary?
>
> (*) In other words is there any difference in implementing using manual
> method versus Dataguard (MRP) with nologging as long as there is no impac=
t
> to log apply(recovery) on standby.
>
>
>
>
> On Tue, Jun 16, 2009 at 11:59 AM, Mathias Magnusson <
> mathias.magnusson@gmail.com> wrote:
>
> What kind of problem does the business see with force logging? What
> transactions do you have that they require nologging on that is critical =
to
> the business?
>
>
>
> That is, are you fighting a theoretical or real problem? Force logging is
> that because it is needed. You cannot invent data that is now written to =
the
> log files. Manual or not, you'll have the same need to have logging occur=
.
>
>
>
> I think you are better off dealing with the actual issue, rather than
> something that seems to an opinion not based on hard data.
>
>
> Mathias
>
>
>
> On Tue, Jun 16, 2009 at 8:41 PM, sanjeev m <sanjeevorcle@gmail.com> wrote=
:
>
> Hi,
>
>
>
> At our site we are having difficulty getting approval from business to
> enable force logging on database objects.
>
>
>
> We plan to implement DR using manual method ie (shipping archive logs to =
DR
> site through cronjob, performing manual recovery on mounted standby
> controlfile)
>
>
>
> I understand without forcelogging all nologging transactions wont be
> recoverable. Is this true during recovery or after activating the standby=
?
>
>
>
> Is forcelogging a mandatory pre-requisite for implementing Dataguard? Has
> any of you have experience implementing Dataguard without force logging
> enabled.
>
>
>
> Will there be any issues during managed recovery if it encounters a
> nologging change? Wont we be hitting same issue if we are doing the recov=
ery
> manually as opposed to MRP process?
>
>
>
> Regards,
>
> Sanjeev.
>
>
>
>
>
Re: Manual Standby as alternative to dataguard by Niall Litchfield on
2009-06-16T21:28:15+00:00
I suspect with Ric that you don't understand what force logging does, and/o=
r
what nologging does in an object creation statement. All your tables can be
declared logging and still non recoverable transactions can happen against
them. Similarly all your objects may be defined with the NOLOGGING clause
and alter database force logging will still be effective. Put simply
forcelogging is not a table attribute, nologging is and it does specific
things. forcelogging is a database attribute and it does related but
different things, which are required for DR using the redo stream.
It isn't true by the way that you can't do DR for databases without force
logging, just that it's more complicated and can't rely on the redo stream.
Niall
On Tue, Jun 16, 2009 at 10:11 PM, sanjeev m <sanjeevorcle@gmail.com> wrote:
> Thanks to all for the clarifications.
>
> There were only small fraction of tables that do no have forcelogging
> enabled.
>
> Regards.
>
> On Tue, Jun 16, 2009 at 1:07 PM, Ric Van Dyke <ric.van.dyke@hotsos.com>=
wrote:
>
>> The apply process will apply what ever it gets from the primary. If
>> there are data integrity issues, then you will have to sort that out whe=
n
>> you try to active the standby.
>>
>>
>>
>> So the answers to your direct questions are NO and NO.
>>
>>
>>
>> Like Jared said, this is not a technical issue. Dataguard will run just
>> happy as a pig in slop no matter what the setting of FORCE LOGGING on th=
e
>> primary. You just might not be able to use the database when you try to
>> active it if it=92s not turned on. If they want to run with out turning=
on
>> FORCED LOGGING then they are kidding them selves about having a DG site.
>> And you and your IT staff better have that in writing so they don=92t t=
hink
>> they do have a DG site when they really don=92t.
>>
>>
>>
>> How much stuff is running with NOLOGGING anyway? And does everyone real=
ly
>> know what NOLOGGING does? It may not be doing what they think it is doi=
ng
>> anyway.
>>
>>
>>
>>
Re: Manual Standby as alternative to dataguard by Nuno Souto on
2009-06-17T09:39:22+00:00
Niall Litchfield wrote,on my timestamp of 17/06/2009 7:27 AM:
>
> It isn't true by the way that you can't do DR for databases without
> force logging, just that it's more complicated and can't rely on the
> redo stream.
>
I thought so as well and was going to jump in,
thanks for mentioning it.
For a number of reasons to do for example with large batch
ops, some sites might want to turn off logging. For example:
direct loads.
What, sites that do these can't do DR? I don't think so.
This is why I am a bit against blanket statements along
the lines of "one either does forced logging or no DR".
IIRC, it involves using incremental RMAN backups
(level 0 plus regular level 1) then shipping intervening
redo logs to DR site. That should allow the site to
recover just like the primary one would after data loss.
Or am I missing something?
RE: Manual Standby as alternative to dataguard by Ric Van Dyke on
2009-06-17T11:31:27+00:00
I believe that the original problem is that the business folks are under
the impression that NOLOGGING does something that it doesn't.
=20
The bottom line is that once an object has the NOLOGGING attribute
turned on, the only things that are not logged are actions that involve
the direct load code. So actions like a direct load via SQL Loader, or
an append insert. Other then that all actions are still logged even
with NOLOGGING turned on. So unless the business it doing a lot of
direct load type work, turning on FORCE LOGGING will likely not change
the way the application works at all. If it does do a lot of direct load
work then yes it will be affected. How much would need to be tested to
see, but yes the direct loads will slow down because they are now being
logged with FORCE LOGGING turned on. There are some other nuances to
having it off, like if you create it with it set to off then the objects
creation isn't logged, so the object would get created on the standby,
the apply process will need to skip any actions on that object if they
appear in the redo stream.=20
=20
As to the argument about can you have a DR solution with FORCE LOGGING
off, I believe that it's creating a lot more work to have it turned off
then its worth. To make it work you have to be able to now manually do
the action that wasn't logged on the primary. Maybe thru RMAN backups,
or recreating the standby once and a while, or keeping track of all the
direct path operations and applying them just after activating the
standby but before you allow users to connect, any choice you pick is
not going to be easy and has a possibility for a mistake which leaves
you with inconsistent or flat out wrong data. If you're serious about
having a DR site then FORCE LOGGING turned on isn't optional. From a
technical point of view, yes the apply process will run just fine with
it on or off, but from a business and logical point of view it is very
important which setting FORCE LOGGING is set to.=20
=20
=20
=20
=20
And by the way, I believe posting data recieved in MetaLink of cound in
articles there is not allowed and could cause your support contract to
be revoked.
Mathias
=20
Re: Manual Standby as alternative to dataguard by Joey D'Antoni on
2009-06-17T11:49:14+00:00
I agree with these sentiments completely just go ahead and do it.=0A=0AJoe D'Antoni=0ASynthes USA=C2=A0=0A=0A=0A=0A=
=0A..=0A=C2=A0=0AThe bottom line is that once an object has the NOLOGGING attri=
bute turned on, the only things that are not logged are actions that involv=
e the direct load code. =C2=A0So actions like a direct load via SQL Loader,=
or an append insert. =C2=A0Other then that all actions are still logged ev=
en with NOLOGGING turned on. =C2=A0So unless the business it doing a lot of=
direct load type work, turning on FORCE LOGGING will likely not change the=
way the application works at all. If it does do a lot of direct load work =
then yes it will be affected.=C2=A0 How much would need to be tested to see=
, but yes the direct loads will slow down because they are now being logged=
with FORCE LOGGING turned on.=C2=A0 There are some other nuances to having=
it off, like if you create it with it set to off then the objects creation=
isn=E2=80=99t logged, so the object would get created on the standby, the =
apply process will need to skip any actions on that object if they appear i=
n the redo stream. =0A=C2=A0=0AAs to the argument about can you have a DR s=
olution with FORCE LOGGING off, I believe that it=E2=80=99s creating a lot =
more work to have it turned off then its worth.=C2=A0 To make it work you h=
ave to be able to now manually do the action that wasn=E2=80=99t logged on =
the primary.=C2=A0 Maybe thru RMAN backups, or recreating the standby once =
and a while, or keeping track of all the direct path operations and applyin=
g them just after activating the standby but before you allow users to conn=
ect, any choice you pick is not going to be easy and has a possibility for =
a mistake which leaves you with inconsistent or flat out wrong data.=C2=A0 =
=C2=A0If you=E2=80=99re serious about having a DR site then FORCE LOGGING t=
urned on isn=E2=80=99t optional. =C2=A0From a technical point of view, yes =
the apply process will run just fine with it on or off, but from a business=
and logical point of view it is very important which setting FORCE LOGGING=
is set to. =0A=C2=A0=0Ato dataguard=0A=C2=A0=0ALet's go back to the actual problem. What do you do=
in the transactions that causes this to be a problem?=C2=A0No-logging=C2=
=A0and forcing it only affects a limited number of operations. Could it be =
that it is not clearly=C2=A0understood=C2=A0by the business users when this=
=C2=A0actually=C2=A0would have an effect on the performance?=0A=C2=A0=0AAnd=
by the way, I believe posting data recieved in MetaLink of cound in articl=
es there is not allowed and could cause your support contract to be revoked=
..=0A=0AMathias=0A=0A=0A
Re: Manual Standby as alternative to dataguard by Ravi Madabhushanam on
2009-06-17T16:09:40+00:00
Sanjeev,
We are running exactly similar kind of DR site for one of our
production. I prefer to not call it a DR, but management is ok with
it. In our environment there are many temporary (in the way they are
used) tables which are loaded & deleted in NOLOGGING operation. These
operations are very critical to the application and operations on
these tables need to be fast enough.
Coming to your question of a DR with primary not in FORCE LOGGING
mode, is perfectly ok, if the management expectations are properly
set.
We just need to make sure that NOLOGGING operations performed in a
day/business cycle are well documented. Also the tables involved in
these operations are identified in advance. If there is a predefined
pattern for NOLOGGING operations that would also help.
Management should be notified that any operations performed on these
tables is unrecoverable and there is no DR for that.
Setup the standby normally as we would do for any other NORMAL
database. MRP process/ manual recovery process will apply archive logs
normally and mark all data blocks identified with nologging operations
are marked corrupted on standby database.
Run DBV on all data files periodically at standby site and sync up
those files manually ( just copy those files to standby site and
bounce the standby instance) once every few days. This interval can be
decreased depending on the business requirement and hardware/network
resources availability.
During all this process entire data that doesn't involve NOLOGGING is
still secure and applications depending on those information will
still work in case of switchover/fail over. Any operation on the
NOLOGGING data will result in "corrupted datablock" errors.
This process involves so much of manual work. This might not be a
perfect solution, but this works.
HTH,
Ravi.M