1 / 30

11g Active Data Guard More than just DR ….

11g Active Data Guard More than just DR …. Gavin Soorma Senior Oracle DBA, Bankwest. DR Sites of today. Lots of $$$ spent on disaster recovery (DR) systems that sit idle waiting for a disaster to occur

wwinders
Télécharger la présentation

11g Active Data Guard More than just DR ….

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. 11g Active Data Guard More than just DR…. Gavin Soorma Senior Oracle DBA, Bankwest AUSOUG National Conference Series 2009

  2. DR Sites of today • Lots of $$$ spent on disaster recovery (DR) systems that sit idle waiting for a disaster to occur • Investment on hardware, state of the art data centres, software licenses, maintenance costs • DR sites very often have identical capacity as the production site – idle and unused capacity in most cases • Traditionally resource intensive activities like reporting and backups were not offloaded to the standby site • Standby databases using Data Guard are synchronized replicas of production databases – so why not use them? • Oracle 11g Active Data Guard changes that enabling us to optimize Return On Investment from the DR or Standby sites AUSOUG National Conference Series 2009

  3. Life before 11g Active Data Guard • Prior to Oracle 11g, read access to standby database required Redo Apply to be stopped. • Queries on read only standby databases could potentially return stale results as changes occurring on the primary database are not applied. • This backlog of unapplied redo data could potentially increase failover times • Traditional reporting sites based on MV's • MV's need administrative overhead for setup and maintenance. • MV refresh times can be lengthy for complex queries - while complete refresh happens no rows returned for queries AUSOUG National Conference Series 2009

  4. Data Guard Overview Data Guard Broker Transactions Oracle Net LGWR MRP RFS Standby Redo Logs Online Redo Logs Standby Database Primary Database ARCH ARCH Archived Redo Logs Archived Redo Logs AUSOUG National Conference Series 2009

  5. What is Active Data Guard? • Introduced in 11g Release 1 • Available in the database server Enterprise Edition as an additional license option • Real time replica of a production database which is open in read only mode while changes transmitted from the primary database are being simultaneously applied to it. • Applies to physical and not logical standby database • Can offload reporting, queries and fast incremental backups from the primary site to the standby site improving the performance and adding to the scalability of the primary site • Performs its primary Data Guard objective of preventing data loss and downtime due to data corruptions, database and site failures, human error, or natural disaster. AUSOUG National Conference Series 2009

  6. Active Data Guard Usage • Redirect read only portions of application to Standby site - online order and shipping application will have a read only component where the catalogue is searched by customer before the shopping cart activity commences • Read mostly applications can use redirection to Active DG site • Redirect complex adhoc queries like year end or month end reports to DR site • Redirect Business Intelligence tools like Discoverer to the standby site • Use optimized incremental backups with RMAN block change tracking in 11g now on standby sites to make backups up to 20 times faster • Extends basic Data guard functionality – switchovers/failovers • Data Guard is free - Active Data Guard is a licensed option AUSOUG National Conference Series 2009

  7. Active Data Guard – Not just for protection • Offload fast incremental backups to an Active Data Guard Standby • Block change tracking eliminates full scans • Incremental backups complete 20x faster (8.3 min vs 2.8 hrs) • In 11g, block change tracking file can be created on physical standby • Minimal overhead on standby database –less than 3% AUSOUG National Conference Series 2009

  8. 11g Active Data Guard Read Only Users/Reporting Applications Read/Write Users Auto block Protection Active Standby Database Primary Database Fast Incremental Backups AUSOUG National Conference Series 2009

  9. Increasing Read-Only Scalability Active Data Guard Reader Farm Primary Database (Two Node RAC Cluster) AUSOUG National Conference Series 2009

  10. Active Data Guard – Operations Permitted What can we do? • Issue SELECT statements • Issue complex queries such as grouping SET queries and WITH clause queries • Call stored procedures • Use database links to write to remote databases • Use stored procedures to call remote procedures via dblinks AUSOUG National Conference Series 2009

  11. Active Data Guard – Operations Disallowed What we cannot do …. • Any DML excluding SELECT • Any DDL • So no additional indexes allowed • AWR tool not supported for Active DG standby • Read note 454848.1 for installing and using Standby statspack AUSOUG National Conference Series 2009

  12. What operations can be performed? SQL> select getsal('KING') from dual; GETSAL('KING') -------------- 5000 SQL> select getsal_remote ('KING') from dual; -- getsal_remote is synonym for getsal@dblink GETSAL_REMOTE('KING') --------------------- 5000 SQL> create table mytab 2 (col_a number); create table mytab * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access SQL> delete scott.emp; delete scott.emp * ERROR at line 1: ORA-16000: database open for read-only access AUSOUG National Conference Series 2009

  13. What operations can be performed? SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY SQL> INSERT INTO dept@mylink --remote database 2 VALUES 3 (50,'I.T','HOUSTON'); 1 row created. SQL> commit; Commit complete. SQL> UPDATE dept@mylink -- remote database 2 SET LOC='DALLAS' 3 WHERE LOC='HOUSTON'; 1 row updated. AUSOUG National Conference Series 2009

  14. Enabling Active Data Guard Via SQL*PLUS ….. If the standby database is not running (note in 11g, STARTUP command will start standby in read-only mode) SQL> STARTUP SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; If standby database is running and managed recovery in operation SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN READ ONLY; SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; AUSOUG National Conference Series 2009

  15. Enabling Active Data Guard Via the Data Guard Broker … DGMGRL> startup DGMGRL> edit database ‘STDBY’ set state=‘APPLY_ON’ In case standby database is already in managed recovery mode Stop redo apply DGMGRL> edit database ‘STDBY’ set state=‘APPLY-OFF’ Using SQL*PLUS open the database in read only SQL> ALTER DATABASE OPEN READ ONLY; Restart redo apply DGMGRL> edit database ‘STDBY’ set state=‘APPLY-ON’ AUSOUG National Conference Series 2009

  16. Don’t do this at home (or in the office) SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from emp; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ 188 dd if=/dev/null of=/u02/oradata/apex/monitor_data01.dbf bs=8192 conv=notrunc seek=188 << EOF CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt EOF apex:/u01/oracle/scripts> ./corrupt.sh 0+0 records in 0+0 records out SQL> alter system flush buffer_cache; System altered. SQL> select * from monitor.emp; select * from monitor.emp * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 188) ORA-01110: data file 5: '/u02/oradata/apex/monitor_data01.dbf' AUSOUG National Conference Series 2009

  17. Active Data Guard Block Recovery • Automatically repair block corruptions online • Primary repaired from standby and vice-versa Corrupt block relative dba: 0x018005da (file 5, block 188) Fractured block found during user buffer read Data in bad block: type: 6 format: 2 rdba: 0x018005da last change scn: 0x0000.001b3ebf seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0x1b14 computed block checksum: 0xb2a8 Reading datafile '/u01/oracle/testdr01.dbf' for corruption at rdba: 0x018005da (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 AUSOUG National Conference Series 2009

  18. Guaranteed SLA’s for read-only users • Active Data Guard service level agreements (SLA) can be implemented using the session parameter, STANDBY_MAX_DATA_DELAY • New with Data Guard 11g Release 2 • Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when they can be queried on an active standby database • The active standby will return an ORA-3172 error code if the limit is exceeded. • Applications can handle this error and redirect queries to the primary database AUSOUG National Conference Series 2009

  19. Guaranteed SLA’s for read-only users SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0; ERROR: ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users SQL> conn sh/sh Connected. SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0; Session altered. ON PRIMARY SQL> update mysales set prod_id=2; ON STANDBY SQL> select distinct prod_id from mysales; ERROR at line 1: ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded SQL> / PROD_ID ---------- 1 >>>> QUERY REDIRECTED TO PRIMARY AUSOUG National Conference Series 2009

  20. Testing Active Data Guard SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY SQL> update mysales set prod_id=1; 918843 rows updated. SQL> commit; Commit complete. SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'DD- -------------------- 04-NOV-2009 10:26:12 SQL> select distinct prod_id from mysales; PROD_ID ---------- 1 AUSOUG National Conference Series 2009

  21. Testing Active Data Guard SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> conn sh/sh Connected. SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'DD- -------------------- 04-NOV-2009 10:26:31 SQL> select distinct prod_id from mysales; PROD_ID ---------- 1 AUSOUG National Conference Series 2009

  22. Monitoring Active Data Guard • Goal - How far behind does data lag on Standby relative to the primary database? • Focus areas Determine Transport lag Determine Apply lag Determine Query SCN or time – compare CURRENT_SCN on Primary and Standby databases • Guarantees a transactionally consistent view of data • Query SCN on Standby is equivalent to CURRENT SCN on primary • Redo Transport considerations – synchronous vs. asynchronous • View V$DATAGUARD_STATS to monitor “apply lag” as well as “transport lag” AUSOUG National Conference Series 2009

  23. Monitoring Active Data Guard Query V$DATAGUARD_STATS view to determine both transport lag as well as apply lag Query V$DATABASE on both primary as well as standby database to obtain CURRENT_SCN SQL> select value from v$dataguard_stats where name='apply lag'; VALUE ---------------------------------------------------------------- +00 00:00:01 SQL> select value from v$dataguard_stats where name='transport lag'; VALUE ---------------------------------------------------------------- +00 00:00:00 AUSOUG National Conference Series 2009

  24. 11g Snapshot Standby Active Dataguard • Read Only - redo from primary received and applied Snapshot Standby • Read Write - redo from primary received but not applied • Redo applied when snapshot standby database is converted back to physical standby Note: • Both Active Data Guard and Snapshot standby require a physical standby database in place as opposed to a logical standby database AUSOUG National Conference Series 2009

  25. Snapshot Standby for Test environments • Two steps vs. 15 in 10g Release 2 • Convert Physical Standby to Snapshot Standby and open for read/writes by testing applications • ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; • Discard testing writes and resync with primary by applying logs • ALTER DATABASE CONVERT TO PHYSICAL STANDBY; • Preserves zero data loss as well • But no real time query (Active Data Guard) or fast failover Physical Standby Apply Logs Open Database Back out Changes Snapshot Standby Perform Testing Continuous Redo Shipping while operating in Snapshot Standby mode AUSOUG National Conference Series 2009

  26. Snapshot Standby and Change Control • Combined with Real Application Testing to provide a simple way to test and maintain protection at the same time • Eliminate guesswork as performance test results are realistic and reliable since using a replica of production data • Understand the impact as well as extent of system resource consumption on production data during performance testing • Change assurance - Introduce changes with confidence • Snapshot standby provides simultaneous disaster recovery capabilities as well as QUALITY ASSURANCE capabilities AUSOUG National Conference Series 2009

  27. Combining with Real Application Testing Archive Logs Physical Standby Primary Database Snapshot Standby Workload Pre-process Workload Replay Workload Capture Analysis & Reporting AUSOUG National Conference Series 2009

  28. Snapshot Standby – some considerations • Can only be used with a physical standby database and not logical standby database • Needs flashback logging to be enabled - creates a implicit guaranteed restore point internally to which the snapshot standby is flashed back to convert back to physical standby • The name of this guaranteed restore points begins with ‘SNAPSHOT_STANDBY_REQUIRED_* • Need to consider how long the standby database operates in snapshot standby mode • First have to rewind the database to the guaranteed restore point using flashback logs - then changes generated on the primary when standby was in snapshot standby mode have to be applied by rolling forward and applying archive logs • Note – switchover or failover cannot be performed to a snapshot standby database AUSOUG National Conference Series 2009

  29. Final Thoughts …. • Organisations are not fully utilising the investment in hardware and resources on disaster recovery sites • Now in Oracle 11g, it is possible to increase the return on such investment by employing new features like Active Data Guard and Snapshot Standby database • Standby databases are not just for disaster recovery • Offload resource intensive operations like reporting and backups to standby sites • Use the standby database to perform testing and QA via the Snapshot Database technology • Need to weigh benefits vs. perceived additional licensing costs AUSOUG National Conference Series 2009

  30. Thanks for attending!! Q & Q U E S T I O N S A N S W E R S http://gavinsoorma.wordpress.com Tel: 0417713124 gavin.soorma@bankwest.com.au A AUSOUG National Conference Series 2009

More Related