This week, I was teaching an 11g Data Guard course in Duesseldorf – my home location. One question that arose in that course was, whether it is possible to run a Logical Standby Database not beeing in archive mode.
Brief explanation: Unlike Physical Standby Databases, the Logical Standby is a quite independent Database that tries to do the same SQL Statements that arose at the Primary to keep up to date with it. In order to be able to do so, it gets the redo protocol transmitted from the primary and analyzes it (with Logminer technique). That redo protocol is stored in Archive Logs on the Logical Standby, which can’t be turned off, of course.
But also, when the SQL Apply on the standby changes blocks, it writes its own redo protocol into its own Online Logfiles and (by default) archives them. Sometimes, this is not needed, because customers use their Logical Standby as kind of „cheap replication“ and do not use it for HA purpose. So can we turn off Archive Logging on the Logical Standby? I saw that in place already at a (not reference, unfortunately) customer site, so the short answer was: Yes, we can 🙂
I tested it then myself and it really works. We can turn off archivelogging on the Logical Standby even with usage of the Data Guard Broker and with no warnings from it. But it seems, as if that is only possible with archiver transmission from the Primary to the Logical Standby. Otherwise (with LogXptMode async or sync), the transmission to the Logical Standby stops.
Pickleball spielen 
#1 von Joaquin Gonzalez am September 14, 2010 - 12:06
Hi Uwe,
One question comes to my mind. In case of failover, you should change to archivelog in standby (now primary) in order to synchronize later with the restored previous primary. Am I wright?
Thanks.
Joaquin Gonzalez
#2 von LRT am September 15, 2010 - 02:00
Hi Uwe,
When I altered logical standby db i8n noarchivelog mode, mining at standby is happening oly when there is a logswitch on primary. No realtime appy. It is not mining from online redo logs. Any idea how make it work?
PS: It is applyng Real-time, if stanby is in archivelog mode.
LRT
#3 von LRT am September 15, 2010 - 02:04
Never mind! I saw your at http://forums.oracle.com/forums/thread.jspa?threadID=702752
————————————————————–
Re: Logical standby in noarchive mode
Posted: Jun 22, 2009 11:04 AM in response to: BoB78 Reply
Indeed, this is possible. I have seen customers running in this configuration and I have tested it myself also. Of course, when you create the logical standby, you have to have it in archivemode. But later on you may turn it off.
That may be even reasonable, if you intend to use the logical standby for reporting purpose only and not for HA purpose. However, it seems as if that is only possible with archiver transmission from the primary. Else (with LGWR SYNC or ASYNC) the transmission to the logical standby stops.
Kind regards
Uwe
————————————————————————–
#4 von Uwe Hesse am September 20, 2010 - 09:14
@Joaquin: Yes, that is true: If you failover to a Logical Standby on which you have turned off Archivelog Mode previously – a rare case, probably, because usually this setup is more intended as „cheap replication“ than as DR-solution – then you need to turn on Archivelog Mode again. Else you cannot even recover ordinary and of course you couldn’t setup another standby DB for this new primary.
#5 von Uwe Hesse am September 20, 2010 - 09:15
@LRT: You’re welcome 🙂
#6 von satya am Juli 15, 2019 - 19:55
Hi Uwe hesse,
Say for ex we disabled archive log mode on primary for some reason and enabled it later.
What will happen at standby side and what to do at standby side to re-sync
Thanks
#7 von Uwe Hesse am Juli 16, 2019 - 15:26
Hi,
if you disable archive logging on the primary, the standby will no longer be able to sync with the primary.
Just enabling archive logging later is not sufficient to get it re-synced, since it cannot bridge the gap of missing archive logs.
You will probably have to re-create the standby afterwards.