Monday, October 17, 2011

Data Guard Architecture Oracle 11g Part-III

The redo data transmitted from the primary database is written to the standby redo log on the standby database. Apply services automatically apply the redo data on the standby database to maintain consistency with the primary database. It also allows read-only access to the data.The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data.

There are two methods in which to apply redo  i.e,
1.)  Redo Apply (physical standby)     and
2.) SQL Apply   (logical standby).

They both have the same common features:
  • Both synchronize the primary database
  • Both can prevent modifications to the data
  • Both provide a high degree of isolation between the primary and the standby database
  • Both can quick transition the standby database into the primary database
  • Both offer a productive use of the standby database which will have no impact on the primary database

1.) Redo Apply (Physical Standby) :  Redo apply is basically a block-by-block physical replica of the primary database, redo apply uses media recovery to read records from the SRL into memory and apply change vectors directly to the standby database. Media recovery does parallel recovery for very high performance, it comprises a media recovery coordinator (MRP0) and multiple parallel apply rocesses(PR0?). The coordinator manages the recovery session, merges the redo by SCN from multiple instances (if in a RAC environment) and parses redo into change mappings partitioned by the apply process. The apply processes read data blocks, assemble redo changes from mappings and then apply redo changes to the data blocks.

This method allows us to be able to use the standby database in a read-only fashion, Active Data Guard solves the read consistency problem in previous releases by the use of a "query" SCN. The media recovery process on the standby database advances the query SCN after all dependant changes in a transaction have been fully applied. The query SCN is exposed to the user via the current_scn column of the v$databaseview  Read-only use will only be able to see data up to the query SCN and thus the standby database can be open in read-only mode while the media recovery is active, which make this an ideal reporting database.

We can use SYNC or ASYNC and is isolated from I/O physical corruptions, corruption-detections checks occur at the following key interfaces:

On the primary during redo transport - LGWR, LNS, ARCH use the DB_UTRA_SAFE parameter
On the standby during redo apply       - RFS, ARCH, MRP, DBWR use the DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT parameters .

If Data Guard detects any corruption it will automatically fetch new copies of the data from the primary using gap resolution process in the hope of that the original data is free of corruption.The key features of this solution are
  • Complete application and data transparency - no data type or other restrictions
  • Very high performance, least managed complexity and fewest moving parts
  • End-to-end validation before applying, including corruptions due to lost writes
  • Able to be utilized for up-to-date read-only queries and reporting while providing DR
  • Able to execute rolling database upgrades beginning with Oracle Database 11g 

2.) SQL Apply (Logical Standby)  SQL apply uses the logical standby process (LSP) to coordinate the apply of changes to the standby database. SQL apply requires more processing than redo apply, the processes that make up SQL apply, read the SRL and "mine" the redo by converting it to logical change records and then building SQL transactions and applying SQL to the standby database and because there are more moving parts it requires more CPU, memory and I/O then redo apply .

SQL apply does not support all data types, such as XML in object relational format and Oracle supplied types such as Oracle spatial, Oracle inter-media and Oracle text .

The benefits to SQL apply is that the database is open to read-write while apply is active, while we can not make any changes to the replica data we can insert, modify and delete data from local tables and schemas that have been added to the database, we can even create materialized views and local indexes. This makes it ideal for reporting tools, etc to be used.

The key features of this solution are :
  • A standby database that is opened for read-write while SQL apply is active
  • A guard setting that prevents the modification of data that is being maintained by the SQL apply
  • Able to execute rolling database upgrades beginning with Oracle Database 11g using the KEEP IDENTITY clause

Click Here for Data Guard Architecture Oracle 11g Part-IV

Enjoy    :-)

No comments: