Database Software

Logical backups in addition to physical backups are often recommended to recover easily from human errors (dropping a table by mistake,modifying data by mistake),although flashback facilities in newer database versions offer alternatives.
Unlike a physical standby a logical standby is not a clone of the primary. That means a backup taken from a logical standby has to be customized to order to recover the primary.
If you need the features of a logical standby (and have enough resources and money) you should build up both - a physical and a logical standby. So you can take the backups from the primary standby.
'physical standby database provides zero data loss without any allowance for data divergence' means running a dataguard configuration in Maximum Protection mode. Disadvantage is, when the standby cannot receive and apply redo information from the primary,whole system goes down.

Werner

Thanks Werner for the reply.

1. If there is a human error (dropping a table by mistake), it will be applied to the standby database irrespective of whether it is physical standby or Logical standby. I am unable to see any advantage with Logical standby database in this case. Am I missing something here ?

Or are there any other type of user errors where Logical standby provides an advantage ? Can you please name some ?

2. If I backup from the logical database and if I am willing to "restore" (instead of recover) the primary from this backup in case of a primary failure, is it not the same effort ?

3. Can't we set up a Logical Standby with Maximum Protection Mode ?

I am aware physical standby offers a better performance and it has no data type restrictions. I do not care about these two. Also, I am aware of the extra features provided by Logical database.

Overall, I am trying to see whether physical standby database has any "feature" which I will miss with Logical standby database. I tried my best with Oracle documentation, but I could not get a clear answer.

Any help is appreciated.

At least up to 10g, physical standby databases are 'idle',they can be opened only read-only and no log applying takes place. The latter has been changed in 11g. But still read-write is not possible. Logical standby databases are more than a failover in case of a crashed primary databases. They are open for read-write,that means they are more than a clone. And they can be used for rolling upgrade,no shutdown of the whole dataguard environment is necessary.
In 10g and higher maximum protection mode is possible for logical standbys,too.

Details in this metalink note:

Data Guard Protection Modes
Doc ID: Note:239100.1

Werner

Hi Werner,

I think I did not explain myself clearly.

I perfectly understand the advantages of logical standby database. But what I am unable to understand is the advantages of physical standby database. However, Oracle says iI should "always" take a physical backup and logical backup is "optional". Why it is so ? Why not the other way round ?

I am aware physical standby offers a better performance and it has no data type restrictions. But these advantages are irrelevant in my case. Does the physical standby database offer any other significant advantage ?

Physical backup and logical backup is not related to physical and logical standby. In both cases you are strongly advised to perform physical backups to protect against disaster failures or to be able to go back in time. Logical backups are exports/datapumps,these are only add-ons to protect against human errors. There are not only datatype restrictions for logical standbys as pointed out in this document:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/data_support.htm#CHDCIACJ

While you normally let simply run a physical standby after creation,a logical standby has to be maintained. In well-defined situations SQL Apply has to be stopped.

An important advantage of a physical standby is you can take physical backups from the physical standby. Because this standby is a real clone of the primary,has the same DBID and is always from the same incarnation,in case of a failure you can use this backup to restore/recover the primary without any modifications.

Werner

Werner,

A physical standby database can be opened for Read/Write from 10gR2 using the "alter database activate standby database" command on the standby database.

But to use this option and revert back "Flashback database" needs to be enabled and should be used used in combination with the "Create Restore point" option.

Suren

But 'activate standby database' means role transition, the standby becomes the primary.

Werner

Werner,

<< But 'activate standby database' means role transition, the standby becomes the primary.>> -- True

... but like i mentioned before with combination of Flashback technologies we can revert it back to become a physical standby database.

1) on the standby database "Create a restore point using guarantee flashback database option"
2) defer the log_arhive_dest_2 to 'defer' on the standby
2) activate standby database
3) open database

To revert back to the point where it was a standby db
1) shutdown database
2) mount the database
3) flashback the database to the "Restore point created before activating".
4) alter database convert to physical standby and let the fal client/server take over to start applying the missing archivelogs

You need to make sure that the archivelogs on standby are deleted which were created after opening the standby for read/write and before flashbacking to the restore point.

We did this as part of our Disaster Recovery testing.

Suren