Friday, March 18, 2016

RDBMS -- A High level look at Dataguard, also looking from migration perspective, explaining the terms..

This post will be a little different than the others, because I will give general terms used in Oracle Data guard environment. The information will be delivered using an QA based approach. The information provided below are filtered to give you a high level look by introducing the dataguard concepts. So , you may consider this as an introduction, as I will also make a demo of cascading standby databases and switchover operations in my next blog posts.

You may also read my other standby posts. Here are a few links for your;

What Dataguard requires? 
  • Requires source database to be in forcelogging mode. If the source database cant be in forcelogging (because of performance reasons) , then when a nologging operation happens, the datafiles should be syncronized with the primary using backups.an incremental backup created from the primary database can be applied or the affected standby data files can be replaced with a backup of the primary data files taken after the nologging operation .
  • Enterprise Edition license. So if the database is a standard edition, then it is impossible to use Dataguard. An alternative may be using manuel transport and recovery method...Note that: Active dataguard requires extra cost, while dataguard is included in the Enterprise Edition license. 
  • License for the standby database. 
  • Primary database must be in archivelog mode. 
  • Primary and Standby database hardware resources are recommended to be identical. (performance reasons) 
  • Heterogeneus configurations (for primary: Windows, standby: Linux) is supported, but the compatability matrix should be checked. For example: Primary can be windows x86 and standby can be Linux but the Database version should be >11g and patch 10 - Oracle 11g onward, Patch 13104881 is required. Check Document: Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID 413484.1) for the details. 
  • Compatible parameter must be the same on physical and standby databases. 
What are the use cases for Dataguard (excluding migrations)?
  • Disaster Recovery , which is the main job of Dataguard 
  • Creating a sync environment and Refreshing Clone Test and Development Databases (refresh by one time rman restore , then using Dataguard Snapshot standby) 
  • Creating a sync environment and Refreshing TEST or Clone environments in legacy environments from Exadata environments, which does not support HCC(Hybrid Columnar Compression) 
  • Creating Reporting Databases from primary and refreshing it. 
  • Creating standby databases and synching them to be able to offload backups of Primary databases to Standby databases. 
  • Creating standby databases and synching them and using Active Dataguard option to offload the readonly workload to the standby databases. 
Why do we use Dataguard for database migrations? 
  • It is supported by Oracle 
  • It is minimizes planned downtimes 
  • When there is no storage replications (Actually, this is the reason why dataguard basede migrations are rarely used) 
  • It has the ability to make heterogeneous migrations. ( --source can be Windows and target can be Linux--) 
  • It gives the ability to easily fail back. 
  • In physical standby based approach(which is the standby db type used mostly), the migration is done physically, so less in-db work,thus effort. 
  • It has the ability to open the database without breaking the synchronization .(transport continues, apply waits -- Dataguard Snapshot Standby) 
  • It has the ability to cascade the redo shipping & apply services 
  • Automatic role transitions(actually not required in migrations) and Centrealized,simple managegement (DataGuard Broker) -- note that, Dataguard broker is not used with the cascaded standbys. 
What are the use cases for Dataguard based migrations?
  • Migrating Large Oracle Databases residing on commodity disks. 
  • Upgrading from an HP Oracle Database Machine running Oracle Database 11g Release 1, to a SUN Oracle Exadata Database Machine running Oracle Database 11g Release 2. 
  • Migrating a single instance Oracle Database to a new RAC environment. 
  • Migrating Oracle databases to Oracle Database Appliance Systems. 
  • Data Center moves (create standby and then switchover) 
  • Os upgrades 
  • Migrating RAC database from one hardware to another 
....

What are Standby Types?
  • Physical Standby: Physical identical copy of primary database (block-for-block basis). It is kept synchronized by applying the redo data received by primary database. (Attention Redo data, not archivelog) 
  • Logical Standby: Logically same with the primary database. It is syncronized using SQL apply method. The sql statements and data is converted when the redo generated at the primary database and then those SQL transactions are applied on the logical standby 
When to use a physical standby? 
  • When simplicity and reliability is required. 
  • When there is a very high redo generated in the source environment. 
  • When there is a requirement to have highest level of protection against corruption . 
  • When a standby database is required to be opened read only while it is sychronizing with the primary. (Active Data Guard) 
  • When there is a requirement to offload fast incremental backups to standby (requires Active Dataguard) 
  • When a snapsot standby database (read-write) is required. 
  • When there is a need to perform rolling database upgrades using transient logical standby database. 
When to use a logical standby? 
  • A need for using standby database in reporting. Altough the data maintained by the standby database cannot be modified, new tables, schemas, indexes and MWs can be created on standby databases. 
  • A need for a rolling database upgrade from Oracle 10g. Note that: physical standby-transient logical standby database is used in rolling upgrades from 11g. 
What are ARCH, LGWR ASYNC and LGWR SYNC Dataguard?
  • ARCH: After the online redolog is archived on the local (after a log switch), the redo from the local archived redolog files are transferred to the standby. On the standby server RFS writes redo data to an archived redolog file from the standby redo log file. Lastly, MRP(physical stdby-redo apply) or LSP(logical stdby-sql apply) apply the redo to the standby database. 
  • LGWR ASYNC: Rather then waiting a log switch and writing entire archived redolog at one, LGWR process uses standby redolog files at the standby database site and read the redo database generated in the primary databases and transmits the redo data to remote. If there are mulitple remote destinations, these transmit is done in parallel. In LGWR ASYNC method, LGWR works asyncronously and does not wait for the Network I/O to complete. 
  • LGWR SYNC: Rather then waiting a log switch and writing entire archived redolog at one, LGWR process uses standby redolog files at the standby database site and read the redo database generated in the primary databases and transmits the redo data to remote.In LGWR SYNC method, LGWR works syncronously and does all the Network I/O in conjuction with writing redo data to the local online redolog files. In each local online redolog write, LGWR also waits for the Network I/O to complete.Transactions are commited when the redo data is received in the standby destinations.LGWR triggers LNS process to do this network I/O. In the standby site, RFS process receives the redo data from the network and writes to the Standby Redolog files. 
Dataguard Data Protection Modes: 
  • Maximum Protection Mode : LGWR SYNC AFFIRM -- Primary database shuts down itself(Dataguard shuts it down) in case of a failure encountered writing its redo stream to one of remote standby redologs in all the standby databases.. This protection mode requires standby redolog files(sized as the same as primary) available in standby . Having 2 standby databases is recommended in this protection mode, as if 1 standby database fails, production will continue to work. 
  • Maximum Availability Mode : LGWR SYNC AFFIRM -- Primary does not shut down itself in case of a remote write failure , but the primary operates in Maximum Performance mode till the error is fixes and then resumes operating in maxium Availability mode. This protection mode requires standby redolog files(sized as the same as primary) available in standby . 
  • Maximum Performance Mode: Any failure in standby does not stop the primary from running. LGWR ASYNC or ARCH. This protection mode does not require standby redolog files available in standby, but having standby redolog files are recommended. This protection method has the minimal impact on primary performance and also note that, when the network is fast, the data protection provided with this method may reach the same level as the maximum availability mode. 
Note that: AFFIRM keyword specified in Max Availability and Protection modes is used to tell the redo transport destination, "acknowledges received redo data after writing it to the standby redo log".

NOAFFIRM is used to tell "acknowledges received redo data after before it to the standby redo log".

So, for SYNC -> AFFIRM is the default, for ASYNC -> NOAFFIRM is the default.

Real Time Apply and Delayed Apply: 
  • Real time Apply is enabled for making apply services to apply the received redo to the standby database without waiting the standby redolog file to be filled and archived. In physical standby databases, It is enabled using: "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;" 
  • Delayed Apply:In delayed apply, the redo data received is placed on standby redolog files of the standby database and once the standby redolog files are filled, they are archived and the ARCH process on standby apply these redo data from the archived log files. It is enabled using: "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE" or "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 --in minutes". 
What are Standby Options? 
  • Snapshot Standby Database: Using snapshot standby database feature, Standby Database can be opened read/write. A Standby database continues to receive redo from primary, but does not apply those redo unless the standby database will be converted back to the physical standby. All the updates that are done while the standby database is a snapshot standby database, are discarded automatically, when the standby database is converted to physical standby database. 
  • Cascaded Standby Database: A physical standby database can be configured to forward redo to a remote physical or logical standby database. 
Cascaded standby databases example: Primary Database > Physical Standby Database with cascaded destination (also called cascading Standby Database) > Physical Standby Database. A standby configuration can be cascaded up to 30 standby databases, as of RDBMS version 11.2. For Ex: PROD -> standby -> cascaded standby -> cascaded standby -> .... so on.

For the details:
https://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#SBYDB5122
6.3 Cascaded Redo Transport Destinations
Note:
To use the Oracle Data Guard cascading redo transport destination feature described in this section
6.3.1 Configuring a Cascaded Destination https://docs.oracle.com/cd/A97630_01/server.920/a96653/cascade_appx.htm
Note that, although this documentation is for 9.2, the concepts still apply to 11.2. 
  • Active Dataguard: Using Active Dataguard, Standby database can be opened read-only, while the standby is continuing to apply redo that received from primary. 
  • Transient Logical Standby: Used for converting an existing physical standby to a logical standby database, which can then be converted back to physical standby. Read, http://www.oracle.com/au/products/database/maa-wp-11g-transientlogicalrollingu-1-131927.pdf for the deails and the restrictions. This Transient Logical Standby is a recommended method for performing rolling database upgrades and here is the list of actions performed in such operations; 
    • Create a guranteed restore point on primary. 
    • install upgraded ORACLE_HOME on primary and standby nodes. 
    • Convert physical standby to Logical Standby 
    • Perform the upgrade on Logical Standby 
    • Switch over (make old logical standby -> primary, make old primary -> logical standby ) 
    • Flashback the logical standby (old primary) 
    • Mount the logical standby(old primary) under the new Oracle Home 
    • Convert logical standby(old primary) to physical standby( this may take time, as standby will be syncronized with the primary(new primary)) 
    • Switch the roles once again. 
    • Increase the compatible settings. 
What are the use cases for DataGuard in EXADATA? 
  • Migrations "from" and "to" Exadata. 
  • Offloading read-only workload. 
  • Database rolling upgrades/Standby First patches:Apply patches first to the physical standby.
  • Switch over the targeted database after validations. Fallback- switch back in case necessary. Oracle patch sets and major release upgrades do not apply.Exadata Database Bundle Patch,Patch Set Update (PSU),Critical Patch Update (CPU) and Interim (“one-off”) patches apply.Oracle patches applied to the grid home,Operating system patches and firmware,Storage patches and Network patches also apply. 
  • High Availability (HA) (Local Standby) 
  • Disaster Recovery (DR) (Remote Standby) 
To be continued...

1 comment :

  1. Dear erman,

    Thanks for posting such a wonderful blog.

    Could you plz help in understanding below statement.

    When there is no storage replications (Actually, this is the reason why dataguard basede migrations are rarely used)

    Thank you

    ReplyDelete

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.