Service Level Agreements: Determine your Recovery Point Objective (RPO). RPO is the maximum amount of data that can be lost and still satisfy Service Level Requirements (SLAs). Determine your Recovery Time Objective (RTO). RTO is the maximum amount of time that the database can be unavailable and still satisfy SLAs [4].
Workload: Data Guard ships redo data generated by the production database to the standby destination and applies redo data to the standby database. This makes redo volume one of the key considerations when implementing a Data Guard configuration. Determine the peak redo generation rate of the primary database; measured in Megabytes/second. Understand both the frequency and duration of these peak periods. Also determine the average redo generation. Note: if your Data Guard configuration includes a RAC primary database, the redo rate is the sum of the redo generated by all nodes
Network Requirements: The efficiency with which Data Guard can ship data to a remote DR site is dependent upon network bandwidth and latency. Determine the network bandwidth available to Data Guard for shipping redo data. Determine the round-trip network latency (RTT) between production and standby sites, measured in milliseconds. Make sure that these are adequate for Data Guard to ship redo data to the DR site at peak redo generation intervals on the production database. Data Guard best practices describe several database and OS network parameters that be tuned to enhance and optimize network transmission throughput [5].
Redo Apply or SQL Apply: Determine which type of Standby Database to implement. Redo Apply (physical standby database) is preferred by more conservative users where the emphasis is on scalability and/or where data types are not supported by SQL Apply (logical standby database). These preferences are rooted in the simplicity and efficiency of the media recovery mechanism used by Redo Apply. A physical standby database can also be used to offload the primary of doing online backups (RMAN can do online backups of a standby database while Redo Apply is on [6]), and it can be open read-only to enable reporting against a static copy of the production database.
SQL Apply (logical standby database) is preferred if the standby database must also serve as an up-to-date reporting database (read/write access is allowed while updates from the production database are being applied). By definition, SQL Apply requires more standby resources (both CPU and I/O) than Redo Apply, because it has to mine the incoming redo on the standby database, generate SQL, and apply this SQL to the standby database, and therefore is typically used for less demanding performance requirements.
Protection Mode: Determine if you will use Maximum Performance (asynchronous redo shipping, a lower level of data protection with least overhead on the production database), Maximum Availability (synchronous zero data loss protection, higher performance impact on the production database, but availability is NOT impacted should the production database be unable to communicate with the standby database), or Maximum Protection (the highest level of data protection possible that will, by definition, cause the production database to shut down if Data Guard can not confirm that redo data has been written to at least one standby location). The choice of protection mode is driven by the combination of RPO/RTO SLAs and the capacity of systems and network to ship and apply the required redo volumes.
Manual or Automatic Failover: All Data Guard configurations include role management services for the orderly transition of a standby database to the production role [7]. Starting with Oracle Database 10g Release 2, Data Guard configurations using Maximum Availability mode have the option of implementing unattended, automatic failover upon primary database failures [8].
Management Interface: Select one of three options:
- Enterprise Manager Grid Control provides a GUI interface to automate the creation and simplify the centralized management of a Data Guard configuration.
- Alternatively, the Data Guard Broker [9] command line interface (DGMGRL) can be used if Enterprise Manager is not available. The Broker enables single commands that perform the equivalent work of multiple SQL*Plus statements, simplifying the management of a Data Guard configuration. Broker creation of a Data Guard Configuration requires only 5 parameters to be configured (three on the production database and two on the standby database). The Broker requires no separate install.
- Finally, for SQL*Plus "jockeys" - all Data Guard configuration and management can also be implemented using the SQL*Plus command line interface.
Tuning Redo Apply: Redo Apply uses Oracle Media Recovery to apply change records to the standby database. Default settings will address most requirements. If tuning is required refer to Oracle Database 10g Best Practices [10].
Tuning SQL Apply: The SQL Apply process mines incoming redo at the standby database, converts it to SQL, and applies SQL to the standby database. The SQL Apply process requires more CPU and I/O resources than does Redo Apply. Plan accordingly by referencing SQL Apply Best Practices [11] and applicable MetaLink notes [12].
Maximum Availability Architecture: The Oracle Maximum Availability Architecture (MAA) provides a best practices blueprint for deploying the full range of Oracle HA technologies. If, in addition to Data Guard you are using RAC, RMAN, Flash Recovery Area, Flashback Database, or ASM, please refer to the MAA home page on OTN for more information [13].
References:
- Oracle Data Guard and Remote Mirroring Solutions - http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardRemoteMirroring.html
- Data Guard, It's Not Just About Disasters - http://www.oracle.com/technology/deploy/availability/htdocs/dataguardprotection.html
- Oracle Data Guard Concepts and Administration, 10g Release 2 - http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm
- Oracle Database High Availability Architecture and Best Practices, 10g Release 1 - http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10726/toc.htm
- Using Recovery Manager (RMAN) with Oracle Data Guard in Oracle Database 10g - http://www.oracle.com/technology/deploy/availability/pdf/RMAN_DataGuard_10g_wp.pdf
- Oracle Data Guard 10g Release 2, Switchover and Failover Best Practices - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_SwitchoverFailoverBestPractices.pdf
- Fast-Start Failover Best Practices: Oracle Data Guard 10g Release 2 - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_FastStartFailoverBestPractices.pdf
- Oracle Data Guard Broker , 10g Release 2 - http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14230/toc.htm
- Data Guard Redo Apply and Media Recovery Best Practices - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gRecoveryBestPractices.pdf
- SQL Apply Best Practices, Oracle Database 10g Release 2 - http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_SQLApplyBestPractices.pdf
- Oracle MetaLink Note 274170.1: "Logical Standby Master Index Page" - https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=274170.1
- Maximum Availability Architecture (MAA) - http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm
- Implementing Data Guard?, by Joseph Meeks, Server Technologies, Oracle Corporation
let me share my experience with regard to the service of recover sql data, it automatically eliminates data corruption issues in selected databases
ReplyDelete