Oracle Dataguard Architecture HSBC TECHNOLOGY AND SERVICES Agenda

Oracle Dataguard – Architecture

} HSBC TECHNOLOGY AND SERVICES Agenda } History of Dataguard } Redo Shipping Methods } Protection Modes } Physical Standby Overview } Logical Standby Overview } Summary Physical Vs. Logical } Physical Standby Features – Active Dataguard – Snapshot Standby © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Dataguard History In 7. 3 the redo’s were shipped manually using jobs and like manually copying to standby and recover using rman periodically From 8 i onwards we have redo logs shipped to standby automatically by arch process 9 i onwards we can have protection modes and also read/write logical standby © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Transporting the Logs } Redo is the primary source to re apply the transactions on standby } Redo Transport from Primary to standby is done by – LNS Process, is a slave process for LGWR – Arch Process } Uses TNS and TCP/IP packets to transfer Redo } Redo is shipped using SYNC or ASYNC mode, by setting the relevant in archive destination parameter © HSBC Holdings plc, 2009. All Rights Reserved.

HSBC TECHNOLOGY AND SERVICES } Redo Transport Modes Synchronous A transaction cannot commit until all redo generated by that transaction has been successfully sent to every enabled redo transport destination that uses the synchronous redo transport mode. Ø Used by the Maximum Protection and Maximum Availability modes Asynchronous (default) A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport destination that uses the asynchronous redo transport mode. Used by the Maximum Performance mode © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Synchronous Redo Transport Mode The user commits a transaction creating a redo record. The LGWR reads the redo record from the log buffer, writes it to the online redo log file, and waits for confirmation from the LNS. The LNS reads the same redo record from the log buffer and transmits it to the standby database using Oracle Net Services. The RFS receives the redo at the standby database and writes it to a standby redo log file. When the RFS receives a write-complete from the disk, it transmits an acknowledgment back to the LNS process on the primary database, which in turn notifies the LGWR that transmission is complete. The LGWR then sends a commit acknowledgment to the user. © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Asynchronous Redo Transport Mode LGWR does not wait for acknowledgment from the LNS i. e. LGWR will continue to acknowledge commit success even if the redo of previous transactions is delayed/not sent (due to some reasons) to the standby database immediately. If the LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby, the LNS automatically transitions to reading and sending from the ORL (Data Guard 11 g onward). Once the LNS has caught up, it automatically transitions back to reading/sending directly from the log buffer. © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Setting Transport Destination in Primary } Alter system set log_archive_dest_2=‘ service=Prod 1 DR SYNC AFFIRM Valid_for(All_Logfiles, PRIMARY_ROLE) Db_unique_name=PROD 1 DR’ } AFFIRM and NOAFFIRM: AFFIRM - specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log. NOAFFIRM - specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log. } VALID_FOR=(redo_log_type, database_role) redo_log_type keyword can assume following values: – ONLINE_LOGFILE—This destination is valid only when archiving ORL files. – STANDBY_LOGFILE—This destination is valid only when archiving SRL files. – ALL_LOGFILES— This destination is valid when archiving either online redo log files or standby redo log files. database_role keyword identifies the role in which this destination is valid for archiving: – PRIMARY_ROLE—This destination is valid only when the database is running in the primary role. – STANDBY_ROLE—This destination is valid only when the database is running in the standby role. – ALL_ROLES—This destination is valid when the database is running in either the primary or the standby role. © HSBC Holdings plc, 2009. All Rights Reserved.

HSBC TECHNOLOGY AND SERVICES } Protection Modes – Max Protection Mode Primary ORL Standby SRL COMMIT Max Protection provides the highest protection mode that guarantees no data loss if primary database fail To achieve this, the redo data needed to recover each transaction must be written to both the local (online) redo log and to a standby redo log on at least one standby database before the transaction can be committed. In order to guarantee no loss of data, the primary database will shut down if a fault prevents it from writing its redo data to at least one remote standby redo log. © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Protection Mode – Max Performance Standby Primary ORL COMMI T SRL Fault preventing rdo to be written to standby Max Availability Max Performance fault corrected and archive gaps resolved © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Protection Modes – Performance Primary ORL Standby SRL COMMI T Command to put standby database in one of the protection modes: © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Physical Standby - Overview © HSBC Holdings plc, 2009.

} HSBC TECHNOLOGY AND SERVICES Logical Standby Overview © HSBC Holdings plc, 2009. All

HSBC TECHNOLOGY AND SERVICES } Summary – Physical vs. logical Physical Logical Open Mode Mount/Read Only Read Write Redo Ship By LNS/Arch Redo Receive By RFS MRPn Reader Preparer Builder Coordinator Applier Ships What Redo Block SQL Statements in means of Logical Change Record Realtime Apply Yes Same Version of Oracle Yes Not Required Same Platform Yes Not Required Same Bit Yes Not Required View V$managed_standby V$logstby_process Apply Process © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Physical Standby Features – Snapshot Standby Used to temporarily allow the standby to open in read-write and allow changes and later flashback converting physical standby © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Physical Standby Features – Active Dataguard } Usually without Active Dataguard if a physical standby is read only , the redo will be shipped but not applied, hence users cannot query the real time data. } But with active data guard, it allows the database to be read only and MRP process can start and apply, hence users can query the real time data in physical standby } No write operations performed © HSBC Holdings plc, 2009. All Rights Reserved.

} HSBC TECHNOLOGY AND SERVICES Physical Standby Features – Block Corruption Handling } When you enable Active Dataguard with physical standby, whenever a block is corrupted in primary/standby it will recover automatically by shipping the good block from opposite instance and recover it. Alert. log file extract Corrupt block relative dba: 0 x 018005 da (file 5, block 188) Fractured block found during user buffer read Data in bad block: type: 6 format: 2 rdba: 0 x 018005 da last change scn: 0 x 0000. 001 b 3 ebf seq: 0 x 2 flg: 0 x 04 spare 1: 0 x 0 spare 2: 0 x 0 spare 3: 0 x 0 consistency value in tail: 0 x 0000 check value in block header: 0 x 1 b 14 computed block checksum: 0 xb 2 a 8 Reading datafile '/u 01/oracle/testdr 01. dbf' for corruption at rdba: 0 x 018005 da (file 5, block 188) Reread (file 5, block 188) found same corrupt data Requesting Auto BMR for (file# 5, block# 188) Waiting Auto BMR response for (file# 5, block# 188) Auto BMR successful © HSBC Holdings plc, 2009. All Rights Reserved.