Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] what differtent between logical standby database and physical standby DB?

Re: [Q] what differtent between logical standby database and physical standby DB?

From: Carel-Jan Engel <>
Date: Thu, 08 Apr 2004 17:11:10 +0200
Message-Id: <>

Dear dba1,
I'm sorry I have to diappoint you, but redolog does _not_ contain SQL statements.
Redo log just contains 'change vectors'. Simplified, these vectors hold an id of a datablock, an offset where the change should be made and the sequence of bytes that should be written to that block/offset. When redo lg would conatin SQL statements, they all had to be parsed, validated, optimized and executed during a recovery operation. Of course, that would make a recovery rather slow.

So, physical standby (PSB) is a database that is in recovery mode. Due to that, it cannot be read, let alone changed, It just receives changes from the primary database, either send by the logwriter or the archiver. Of course, having the changes send by the logwriter will give you better results: (redo log) data will arrive earlier at the standby. The archiver will just send the redo log information at a log switch, either forced or whenever the logfile is full. That might take some time: today I was at a site where some ignorants implemented a High Available system. The database is 500 MB, the redologfiles 100MB. It might take some time before the logswitch will occur. In the meantime your transactions are not safe! You can open a PSB database in Read Only mode. Forwarding of redolog will continue while it is R/O, but the redo logs will not be applied until it is put back into managed recovery mode.

Now to logical standby (LSB). Opposed to PSB, the standby in an LSB envrionment is not in recovery mode, it is opened for read/write. However, objects, involved in the LSB configuration cannot be changed. Now we will encounter some other software parts, where Oracle might have reused some of there previously developed features. First we have to enrich the redolog info with the so-called supplemental logging. This extra information is needed to be able to reverse engineer SQL statements from the redo log information, just change vectors will not be enough. Then we see streams. this can be used for forwarding messages/database changes to other databases. It all looks pretty similar to a streams-wise set-up, however, the redolog information gets forwarded to the standby site first. At the standby site, the redo log information is used to create SQL statements again. Thes SQL statements will be applied to the database by one or more SQL Apply processes. You can have other indexes on your tables at the standby as you have at the primary. These indexes can serve reporting purposes, where the indexes on the primary are optimized for OLTP.

In 9iR2 the information in the redo log files doesn't get applied until a log switch is performed (PSB and LSB). So, you cannot enter an order in the primary, press the button and have the invoice printed immediately from the stanby: It will not be available at the standby (talking LSB now). Oracle claims that immediate apply is available in 10g.

I've encountered LSB in 9iR2 to be pretty unstable, and at least not good enough for High Availability purposes. It comes with a lot of restrictions, too. PSB works very good. There are some nasty bugs in it in and 3, so upgrade to (or 5?) whenever possible. I have not been able to test the DG-stuff in 10g yet.

Regards, and give my regards to dba2 as well, Carel-Jan

If you think education is expensive, try ignorance. (Derek Bok) ===

At 02:32 PM 4/8/2004, you wrote:
>I read document it mention logical standby DB use "sql
>statements apply" and physical standby use "redo log
>apply". I got confuse. "redo log aplly" should same
>like "sql statements apply", due to "redo log"
>includes hundreds SQL statements.
>Can anyone give me a hint?
>Do you Yahoo!?
>Yahoo! Small Business $15K Web Design Giveaway
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Apr 08 2004 - 10:08:56 CDT

Original text of this message