|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Oracle data guard
Data Guard promises:
Primary and standby databases (database roles)
In a standby environment, one database is a primary database while all others are standby databases. Standby databases are either physical or logical standby databases.
Query the database_role column in v$database in order to find out the role of a database
A standby database environment is meant for disasterous failures. It protects the data even if a machine is wrecked beyond recognition. Therefore, it makes no sense to put a primary and a standby database on the same hardware.
A logical standby turns redo into inserts, updates and deletes, while a physical standby directly applies redo to its datafiles.
In a standby environment, exactly one database is a primary database. All the other databases are standby databases.
Logical standby database
The logical standby database is new with Oracle 9iR2.
The key advantage for logical standby databases is that they're opened read/write, even while they're in applied mode. That is, they can be used to generate reports and the like. It is indeed a fully functional database. Also, additional indexes, materialized views and so on can be created.
However (this being a disadvantage) not all datatypes are supported.
Oracle (or more exactly the log apply services) uses the primary database's redo log, transforms them into SQL statements and replays them on the logical standby database.
Physical standby database
A physical standby database is a byte for byte exact copy of the primary database. This also means that rowids stay the same in a physical standby database environment.
Oracle (or more exactly the log apply services) uses the primary database's redo log to recover the physical database.
A physical standby database might be opened read only, however, the received logs are in this case not applied. When the logs are applied, the database is not accessible (it is then in a managed recovery state).
See also Switching a standby database to read only (and vice versa)
A standby environment
It is possible for some standby databases in a standby environment to be logical standby databases while the other standby databases are physical standby databases.