Logical Standby in NOARCHIVELOG mode

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 protocolinto 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.

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?

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.

@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.