1 / 76

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC suttonbi@saic

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC suttonbi@saic.com. Oracle10g Data Guard Overview. Objectives. Explain the benefits of Data Guard and why we might use it Describe the basic components of Oracle Data Guard

red
Download Presentation

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC suttonbi@saic

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle10g Data GuardOverview / Logical StandbyBill SuttonSAICsuttonbi@saic.com

  2. Oracle10g Data GuardOverview

  3. Objectives • Explain the benefits of Data Guard and why we might use it • Describe the basic components of Oracle Data Guard • Understand the difference between Physical and Logical Standby databases • Gain detailed insight into Logical Standby databases: • Things-you-need-to-be-aware-of • Creation • Maintenance • Tuning

  4. Benefits of Oracle Data Guard • Configure the system to meet business protection and recovery requirements • Continuous service through a disaster or crippling data failure • Complete data protection against corruptions and data loss • Reduce overhead on primary systems by offloading: • adhoc queries • reporting • backups • Centralized management (Data Guard Broker, Data Guard Manager via Enterprise Manager)

  5. 44% Human Error 32% Computer Viruses 7% Software Corruption 14% Natural Disasters 3% Data Loss Causes… Hardware & System Error * Protect Data 2006

  6. What is Data Guard? Primary Database Standby Database Redo Oracle Net Database Database Copy Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)“Ch. 1 Introduction to Oracle Data Guard “

  7. Support/Compliment High Availability… Failover System Failure Failover Data Failureand Disaster UnplannedDowntime Standby Lag/Failover Human Error Switchover SystemMaintenance Concurrent Update of Standby Data Maintenance PlannedDowntime Backout Support (Failover) Oracle Upgrade

  8. Standby Databases • There are two types of standby databases: • Physical standby database • Logical standby database

  9. Data Guard Services • There are three types of services provided between the Primary and Standby databases: • Redo Transport: (2 types: Archival or Real-Time) • Controls the transfer of redo data from the primary database to the standby database. • Log Apply: (2 types: Redo Apply or SQL Apply) • Applies redo data on the standby database to maintain synchronization with the primary database. • Role Transitions: (2 types: Switchover or Failover) • Change the role of a database: SWITCHOVER: standby to primary + primary to standby FAILOVER: standby to primary

  10. Data Protection Modes • There are three types of Data Protection Modes: • Maximum Protection • Maximum Availability (hybrid) • Maximum Performance REMEMBER: 2323... There will be a test sooner or later…

  11. 2323 Test… • 2, Types of standby databases: • Physical and Logical • 3, Types of services: • Redo Transport • Log Apply • Role Transitions • 2, Types of features for each service: • Redo Transport (Archival, Real-Time) • Log Apply (Redo Apply, SQL Apply) • Role Transitions (Switchover, Failover) • 3, Types of Protection Modes • Maximum Protection • Maximum Availability • Maximum Performance

  12. Physical Standby Database Physical standby database: • Identical to the primary database on a block-for-block, byte-for-byte, bit-for-bit basis • Redo Transport : Archival or Real-Time • Log Apply : Redo Apply only • Role transition : Switchover or Failover • Maintained in a managed recovery mode (MOUNT) • Can be opened as READ ONLY • Can be opened as READ/WRITE * Must use FLASHBACK DATABASE to return to time prior to READ/WRITE in order to resume managed recovery mode OR restore from backup made prior to activation.

  13. Logical Standby Database Logical standby database: • NOT physically identical to primary but instead data identical, or data subset. • Redo Transport : Archival or Real-Time • Log Apply : SQL Apply only • Role transition : Switchover or Failover (atypical) • Maintained in an non-recovery mode (OPEN) • Replicated primary data is READ ONLY • Non-Primary data is READ WRITE • Contains some, or all, of the primary schema definition • Some data types, storage types, PL/SQL supplied packages, structures, and DDL are not supported

  14. Data Guard ServicesRedo Transport • Redo transport services perform the following tasks: • Transmits redo data from the primary to one or more standbys (9 max*) using ARCH or LGWR (LNSn) • Enforces the database protection mode • Automatically detects missing or corrupted archived redo log files on the standby and retrieves replacement archived redo log files from the primary, or another standby database i.e.: “GAP” RESOLUTION • Archival or Real-Time

  15. Data Guard ServicesNo Standby – No REDO Transport Primary Database Transactions LGWR Online Redo Log Files ARC0 Archived Redo Log Files

  16. Data Guard Services Redo Transport (Archival) Primary Database Transactions Physical Standby Database LGWR RFS MSRP Archival Transport Online Redo Log Files Redo Apply ARC1 ARC0 ARC0 Archived Redo Log Files Archived Redo Log Files

  17. Data Guard Services Redo Transport (Real-Time) Logical Standby Database Primary Database Transactions LGWR RFS LSN0 LSP LGWR Online Redo Log Files Standby Redo Log Files (Required) SQL Apply Real-Time Transport ARC0 ARC0 Archived Redo Log Files Archived Redo Log Files

  18. Data Guard ServicesRedo Transport Configuration • Redo transport is configured via parameters: • LOG_ARCHIVE_DEST_n (n = { 1..10 }) • One destination must be LOCAL and must be MANDATORY. This destination, usually n = 1, is always archived first when using ARCH for redo transport • Destination n = 10 is the default FLASHBACK RECOVERY area. • LOG_ARCHIVE_DEST_STATE_n • Allows one to ENABLE, DEFER, or RESET a specific destination • STANDBY_ARCHIVE_DEST (standby only) • If no LOG_ARCHIVE_DEST_n locations specified then provides default location for archived redo logs

  19. Data Guard ServicesRedo Transport Configuration LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] = { null_string | { LOCATION=path_name | SERVICE=service_name } [ { MANDATORY | OPTIONAL } ] [ REOPEN[=seconds] ] [ DELAY[=minutes] ] [ NOREGISTER ] [ TEMPLATE=template ] [ ALTERNATE=LOG_ARCHIVE_DEST_n ] [ DEPENDENCY=LOG_ARCHIVE_DEST_n ] [ MAX_FAILURE=count ] [ ARCH | LGWR ] [ SYNC | ASYNC ] [ AFFIRM | NOAFFIRM ] [ NET_TIMEOUT=seconds ] [ VALID_FOR=(redo_log_type,database_role) ] [ DB_UNIQUE_NAME ] [ VERIFY ] } Parameter Defs

  20. Data Guard ServicesLog Apply (Redo Apply) • For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database. • I.e. Block-for-block, byte-for-byte, bit-for-bit identical to primary.

  21. Data Guard ServicesLog Apply (SQL Apply) • For logical standby databases, Data Guard uses SQL Apply technology, which transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database. • I.e. Use Log Mining to extract the redo, transform the redo into SQL statements, then execute the SQL statements against the logical standby.

  22. SQL Apply Engine Primary Redo Shared Pool Logical Change Records not Grouped into Transactions Redo Records LCR LCR LCR LCR LCRs Builder Preparer Reader Log Mining Transaction Groups Coordinator Analyzer Apply Processing Applier Transactions Sorted in Dependency Order Transactions To be Applied Standby Database

  23. SQL Apply Engine Steps • Primary ARCH or LGWR (LNS0) sends redo to standby RFS • READER mines redo (LogMiner), then sends redo to PREPARER • PREPARER transforms redo into Logical Change Records (LCRs), then stores LCRs in the LCR CACHE of the SHARED_POOL • BUILDER retrieves LCRs, groups them into Transaction Groups, then sends Transaction Groups to ANALYZER • ANALYZER sorts Transactions (by SCN), then sends sorted Transaction Groups to COORDINATOR • COORDINATOR assigns sorted Transaction Groups to one or more APPLIERS • APPLIERS execute transactions against Logical Standby

  24. SQL ApplyConsiderations • SHARED_POOL_SIZE will need to increase to accommodate the Logical Change Records (LCRs) • PREPARER and/or APPLIER processes may be over-worked (… add more) • Transaction sizes (Oracle determines...) • Small : Applies LCRs once COMMIT is read • Large : Breaks transaction into transaction chunks and applies immediately, before COMMIT is read. WHY? • Pageouts : Not enough memory in LCR Cache of the SHARED POOL • Restarts : Can require the SQL Apply process to “re-mine” the redo for any transactions not committed and time of standby shutdown. WHY?

  25. SQL ApplyConsiderations Primary DML Statements: • Batch updates are performed one row at a time • Direct path inserts are performed in a conventional manner • Parallel DML is not executed in parallel Primary DDL Statements: • Parallel DDL is not executed in parallel • CREATE TABLE AS SELECT is performed as: • CREATE TABLE … • INSERT, INSERT, INSERT, etc… What does this imply?

  26. Role TransitionsSwitchover and Failover • Not automatically invoked (but can be…) • Switchover • Planned role reversal • Used for OS or hardware maintenance • Failover • Unplanned role reversal • Use in an emergency • Minimal or zero data loss depending on choice of data protection mode

  27. Data Protection ModesMaximum Protection • Ensures that no data loss will occur if the primary database fails. • Redo needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. • If a fault prevents the primary database from writing its redo stream to the standby redo log of at least one transactionally-consistent standby database, the primary database shuts down.

  28. Data Protection ModesMaximum Availability • Highest level of data protection possible without compromising availability of the primary database. • Transactions will not commit until the redo needed to recover them is written to the local online redo log and to the standby redo log of at least one transactionally-consistent standby database. • If a fault prevents the writing of the redo stream to a remote standby redo log, the protection mode downgrades to maximum performance, thus preventing the shutdown of the primary database. • Once fault is corrected, and all gaps in redo log files are resolved, the mode upgrades back to maximum availability. • No data loss is guaranteed unless a second fault occurs prior to the correction of the first fault.

  29. Data Protection ModesMaximum Performance • Default. Provides highest level of data protection possible without affecting the performance of the primary database. • Transactions may commit as soon as the redo data needed to recover them transaction is written to the primary’s online redo log. • Redo data stream written asynchronously to at least one standby database. • If sufficient bandwidth exists, provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.

  30. Setting the Protection Mode • To set up redo transport services and specify a level of data protection for the Data Guard configuration, perform the following steps. • Step 1: Configure the LOG_ARCHIVE_DEST_n parameters on the primary database.

  31. Setting the Protection Mode • Step 1: Bounce the primary database: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; * If RAC, shutdown all instances, restart/mount one instance. • Step 2: Set the mode: SQL> ALTER DATABASE SET STANDBY DATABASE TO PROTECTION MODE; • Step 3: Open the Primary Database: SQL> ALTER DATABASE OPEN; • Step 4: Confirm LOG_ARCHIVE_DEST_n parameter(s) on Standby • Database: SQL> SHOW PARAMETER log_archive_dest_ • Step 5: Confirm configuration: SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

  32. Oracle10g Data GuardBriefs…

  33. Cascaded Destinations • A primary database can directly support up to nine standby databases in any combination of physical and/or logical configurations. • A standby database can directly support up to nine standby databases in any combination of physical and/or logical configurations. • Indirectly, there is no technical limit.

  34. Standbys from Standbys from Standbys…. Physical Standby Logical Standby Physical Standby Primary Logical Standby Logical Standby

  35. Data Guard Broker Primary Database Standby Database DataGuardBroker DataGuardBroker CLI Management Client

  36. Data Guard Manager Oracle Management Server Repository Intelligent Agent Intelligent Agent Standby Database Primary Database DataGuardBroker DataGuardBroker Data Guard Manager (GUI) CLI Management Client

  37. Data Guard Manager (GUI)

  38. Oracle Data Guard and RealApplication Clusters • Oracle Data Guard and Real Application Clusters are complementary and can be used together • Real Application Clusters provides high availability • Oracle Data Guard provides disaster protection and prevents data loss • RAC to Data Guard can be All:1 or All:M • All RAC instances must stream their redo to the standby database, The standby database will apply the redo in proper order based on SCN.

  39. Oracle10g Data GuardLogical Standby

  40. Logical StandbyBenefits • Provides disaster recovery, high availability, and data protection • Can be altered to have a different structure from the Primary • Is maintained while in an OPEN state • Allows for offloading of reports and adhoc queries • Easy to implement

  41. Logical Standby DatabaseConsiderations • Data types • Storage types • PL/SQL Supplied Packages • Unsupported Tables, Sequences, and Views • DDL Support • SKIP Handlers • Tuning

  42. Logical Standby Database Data Type Considerations • Not all Data Types are supported: • BFILE • Collections (including VARRAYS and nested tables) • Encrypted columns • Multimedia data types (including Spatial, Image, and Context) • ROWID, UROWID • User-defined types • XML Type

  43. Logical Standby DatabaseStorage Type Considerations • Not all Storage Types are supported: • Segment compression

  44. Logical Standby DatabasePL/SQL Supplied Packages Considerations • Typically, any supplied Oracle PL/SQL that modifies system metadata is not supported • e.g.: • DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT • DBMS_SPACE_ADMIN, DBMS_REFRESH • DBMS_REDEFINITION, DBMS_SCHEDULER,DBMS_AQ. • DBMS_JOB is supported as follows • Job execution is suspended on the logical standby • Jobs cannot be scheduled on the logical standby database • Jobs submitted on the primary are replicated in the logical standby • In the event of a switchover or failover, jobs scheduled on the primary database will begin running on the new primary

  45. Logical Standby DatabaseUnsupported Tables, Sequences, and Views • Identify unsupported structures: SQL> SELECT DISTINCT OWNER,TABLE_NAME 2> FROM DBA_LOGSTDBY_UNSUPPORTED 3> ORDER BY OWNER,TABLE_NAME; OWNER TABLE_NAME ----------- -------------------------- HR COUNTRIES OE ORDERS OE CUSTOMERS OE WAREHOUSES

  46. Logical Standby DatabaseUnsupported Tables, Sequences, and Views • Identify unsupported columns: SQL> SELECT COLUMN_NAME,DATA_TYPE 2> FROM DBA_LOGSTDBY_UNSUPPORTED 2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS'; COLUMN_NAME DATA_TYPE ------------------------------- ------------------- CUST_ADDRESS CUST_ADDRESS_TYP PHONE_NUMBERS PHONE_LIST_TYP CUST_GEO_LOCATION SDO_GEOMETRY

  47. Logical Standby DatabaseUnsupported Tables, Sequences, and Views • So what we do when we have structures that are unsupported? • Redefine the structure • Leverage other means to move the data across • Export/Import • DataPump • Database links • SQL*Loader • Program • Don’t move the data…

  48. Logical Standby DatabaseSQL Apply : Skipped SQL Statements • Not all SQL Statements are supported by SQL Apply: • ALTER DATABASE • ALTER SESSION, ALTER SYSTEM • CREATE CONTROL FILE • CREATE DATABASE • CREATE/DROP DATABASE LINK • CREATE PFILE FROM SPFILE • CREATE/DROP/ALTER MATERIALIZED VIEW • CREATE/DROP/ALTER MATERIALIZED VIEW LOG • CREATE SCHEMA AUTHORIZATION • CREATE SPFILE FROM PFILE • EXPLAIN • LOCK TABLE • SET CONSTRAINTS, SET ROLE, SET TRANSACTION

  49. Logical Standby DatabaseHandling Materialized Views • For primary MViews created before creation of logical standby: • ON-COMMITs are maintained as normal • ON-DEMANDs must be manually refreshed • For primary MViews created after creation of logical standby: • Recreate logical standby • Create MView manually • For standby MViews: • Create MView manually

  50. Logical Standby DatabaseSKIP Handlers • Allow programmatic control over replicated tables • Prevent changes to a specific schema • Prevent changes to a specific schema object • Replace SQL Apply supported DDL statements • Created via DBMS_LOGSTDBY.SKIP

More Related