1 / 51

Data Guard SQL Apply Back to the Future!

Session id: 40144. Data Guard SQL Apply Back to the Future!. Larry M. Carpenter Senior Principal Consultant Server Technologies Oracle Corporation. A Quick Review. What’s in the Oracle Data Guard ‘Stack’? A High Level Overview Redo Apply SQL Apply How does SQL Apply do it today?

gabi
Download Presentation

Data Guard SQL Apply Back to the Future!

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. Session id: 40144 Data Guard SQL ApplyBack to the Future! Larry M. CarpenterSenior Principal Consultant Server Technologies Oracle Corporation

  2. A Quick Review • What’s in the Oracle Data Guard ‘Stack’? • A High Level Overview • Redo Apply • SQL Apply • How does SQL Apply do it today? • A Couple of Customer Case Studies • How they use Oracle Data Guard Today. • What Business Problems they solved. • What can we do to make it better?

  3. At the Highest Level • Data Guardcomprises of two parts • REDO APPLY • Maintains a physical, block for block copy of the Production (also called Primary) database. • SQL APPLY • Maintains a logical, transaction for transaction copy of the Production database.

  4. REDO Apply Architecture Physical Standby Database Asynchronous/ Synchronous Redo Shipping Primary Database MRP Redo Apply Network Backup • Maintains a ‘Physical’ block for block copy of the Primary Database

  5. SQL Apply Architecture Primary Database Network Logical Standby Database ContinuouslyOpen for Reports Asynchronous/ Synchronous Redo Shipping SQLApply Transform Redo to SQL Additional Indexes and Materialized Views • Maintains a ‘Logical’ transactional copy of the Primary Database

  6. SQL Apply Engine Architecture MiningGroup ApplyingGroup LSP PX PX PX PX SQL Redo RFS Log Transport Services Remote Archived Logs Logical Standby Database

  7. Customer Case Studies • How are customers using SQL Apply today? • A look at two European Banks and their setup • What Problems were they able to solve? • What are we doing to make life easier and better? • For the Business • For the User • For the DBA!

  8. Bank #1 –Web Site Internet Users FireWall • Users access only the standby outside the firewall. • Web Developers access only the Production DB. • Physical Standby provides extra Disaster Recovery Logical Standby Logical Standby FireWall Web Developers Physical Standby Production Database

  9. Bank #1 – Online Banking Internet Users • Users access the Production DB outside the firewall. • Query Users access only the Logical Standby • Physical Standby outside firewall provides Disaster Recovery Production Database Physical Standby FireWall Reporting and Query Users Logical Standby

  10. Bank #2 – Branch Banking Branch Users • Branch Users access the Production DB. • Query Users access only the Logical Standby • Physical Standby Disaster Recovery • Production Database and Logical standby on the same server Production Database Physical Standby Reporting and Query Users Logical Standby

  11. Oracle Database 10gSolving Those Problems! • Zero Downtime Logical Standby Database Creation • Faster, Easier, Bulletproof • Support for more Data Types • Wider range of applications and functionality supported by SQL Apply • Real Time Apply • Integration with Flash Back Database • Improved Ease of Use • Rolling Database Upgrades – Going Forward

  12. Zero Downtime Instantiation • Logical standby databases easy to create • Using an On-line backup! • No shut down and no quiesce of the Primary • No shutdown = no downtime of production system • No quiesce = no wait on quiesce • And no dependence on Resource Manager

  13. Zero Down Time Instantiation 1 3 On-Line Backup 2 Primary Database Restore Physical/Logical Standby Database Create and Copy Logical Standby Control File 4 Transport Service

  14. Zero Down Time Instantiation 5 6 Recovery Activation Logical Standby Database! 7 Change DBNAME and DBID 8 Start SQL Apply Services

  15. Support for more Data Index Organized Tables More datatypes Primary database Logical standby database

  16. Data Types • New Support for: • LONG • LONG_RAW • Multi-byte CLOB • NCLOB • BINARY_FLOAT (New in Oracle Database 10g) • BINARY_DOUBLE (New in Oracle Database 10g)

  17. Index Only Tables • IOT Support in a Logical standby database • No LOB columns in the IOT • No IOT with Overflow

  18. Real Time Apply • Redo data is applied to the standby database as it is received from the primary database. • Apply services on the standby do not wait until the current standby redo log file is archived. • Invoked through SQL: • For SQL Apply: • ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; • For Redo Apply: • ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

  19. Real Time Apply An up-to-date Logical or Physical Standby Database Transactions Oracle Net LGWR RFS LSP MRP Standby Redo Logs Online Redo Logs Primary Database Real Time Apply! ARCH ARCH Archived Redo Logs Archived Redo Logs

  20. Existing Site Recovery Tradeoffs Reporting on delayed data Primary Database Standby Database Redo Shipment Delayed Apply • Log apply may be delayed to protect from user errors but: • Switchover/Failover gets delayed • Reports run on old data • After failing over to standby, production DB must be rebuilt

  21. Flashback Database • A new strategy for point in time recovery • Eliminate the need to restore a whole database backup • Integrated seamlessly with RMAN • Think of it as a continuous backup • Restores just changed blocks • It’s fast - recover in minutes, not hours • It’s easy - single command restore RMAN> FLASHBACK DATABASE TIMESTAMP to_timestamp ('2003-08-15 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

  22. Real Time Apply Primary Database Standby Database No Delay! Enhanced DR with Flashback Database Real TimeReporting Redo Shipment Flashback Log Flashback Log • Flashback DB removes the need to delay application of logs • Flashback DB removes the need to reinstantiate primary after failover • Real-time apply enables real-time reporting on standby Primary: No reinstantiation after failover!

  23. Ease of Use • New and Improved Data Guard Manager! • Monitoring SQL Apply • Unsupported Storage Attributes • Applied Logs and Apply Progress • Managing the Logical Standby • Bypassing the Guard • Skipping Table Redo • Skipping Failed (and subsequently fixed) Transactions

  24. Enterprise Manager New Features • Streamlined browser-based interface that enables complete standby database lifecycle management • Focus on: • Ease of use • Management based on best practices • Pre-built integration with other HA features

  25. Viewing Unsupported Objects SQL> SELECT DISTINCT table_name, attributes 2> FROM dba_logstdby_unsupported 3> WHERE owner = 'HR'; TABLE_NAME ATTRIBUTES ------------ --------------------- COUNTRIES Index Organized Table DEPARTMENTS Table Compression EMPLOYEES Table Compression JOBS Table Compression JOB_HISTORY Table Compression LOCATIONS Table Compression REGIONS Table Compression 7 rows selected.

  26. Checking for Applied Logs SQL> SELECT thread#, sequence#, applied FROM 2> dba_logstdby_log order by sequence#; THREAD# SEQUENCE# APPLIED ---------- ---------- ------- 1 48 YES 1 49 CURRENT 1 50 CURRENT 1 51 CURRENT 1 52 CURRENT 1 53 CURRENT 6 rows selected.

  27. Viewing the Progress of SQL Apply SQL> SELECT applied_scn, applied_thread#, newest_scn, 2> newest_thread# 3> FROM dba_logstdby_progress; APPLIED_SCN APPLIED_THREAD# NEWEST_SCN NEWEST_THREAD# ----------- --------------- ---------- -------------- 961263 1 961263 1

  28. New Guard Bypass Syntax SQL> ALTER SESSION DISABLE GUARD; SQL> ALTER SESSION ENABLE GUARD;

  29. Using Wildcards to Skip Tables DBMS_LOGSTDBY.SKIP(stmt, schema_name, table_name, proc_name, use_like, esc); • In Oracle9i Database all names are wildcard matched. • TEST_T% for table_name skips all tables starting with TEST and with anything in the place of the ‘_’. • No way to indicate that you only wanted table TEST_TABLE and not TEST1TABLE. • Set use_like to False to use the escape character. • Set escto a \ for example to allow better matching • Now TEST\_T% will only skip TEST_TABLE.

  30. Automatically Skipping the Last Failed Transaction Logical Standby Database Redo from primary Restart SQL Apply Skip Failed transaction ALTER DATABASE START STANDBY APPLY SKIP FAILED TRANSACTION;

  31. Remember Those Banks? • Logical Standby Creation simple, fast and online! • Logical standbys no longer have to lag an archive log behind the Primary! • More Data types = More Applications! • Better View of the Logical Standby Status! • Easier to Manage!

  32. Rolling Database Upgrades • In Oracle Database 10g, SQL Apply provides the starting point for performing rolling upgrades of the Oracle RDBMS software and database with minimal interruption of service. • By utilizing a Logical standby database customers can upgrade one database while running on the original production database and then run in a mixed version environment before returning to the original, but upgraded, configuration!

  33. Those Banks (Again)? Production Database Version X Logical Standby Version X Node One Node Two • Initial Data Guard Setup with all databases at Version X • Applications running on Node One, the Production Database. • Reporting and Query Applications running on the Logical standby

  34. Upgrade the Logical Standby Production Database Version X Logical Standby Version X+1 Node One Node Two • Stop Redo shipping to the Logical Standby on Node Two. • Archive logs stack up on Node One. • Upgrade Logical Standby • Test General reporting and queries on the upgraded logical standby • Applications still running on Node One, the Production Database.

  35. Run In Mixed Environment Production Database Version X Logical Standby Version X+1 Node One Node Two • Re-enable Redo Shipping to the Logical Standby on Node Two • Stacked Archive logs automatically sent to resynchronize the Logical Standby (Bring it up to date) • Applications still running on Node One, the Production Database. • Reporting and Query Applications running on upgraded Logical standby

  36. Switch Over Applications Logical Standby Version X Production Database Version X+1 Node One Node Two • Switch over databases from Node One to Node Two. • Original Production Database is now a Logical Standby. • Redo Shipping to Node One is stopped. • Archive logs stack up on Node Two. • Applications running on Node Two, the Upgraded Production Database.

  37. Upgrade Node One Logical Standby Version X+1 Production Database Version X+1 Node One Node Two • Upgrade the Logical Standby database on Node One. • Applications still running on Node Two, the Upgraded Production Database.

  38. Re-Enable Redo Shipping Logical Standby Version X+1 Production Database Version X+1 Node One Node Two • Re-enable Redo Shipping to the Logical Standby on Node One • Stacked Archive logs automatically sent to resynchronize the Logical Standby (Bring it up to date) • Applications still running on Node Two, the Production Database. • Reporting and Query Applications running on upgraded Logical standby

  39. Switch Back to Original Setup Production Database Version X+1 Logical Standby Version X+1 Node One Node Two • Initial Data Guard Setup with all databases at Version X+1 • Applications running on Node One, the Production Database. • Reporting and Query Applications running on the Logical standby

  40. SQL Apply and Oracle Database 10g • Improving upon a great foundation! • Creating Logical Standby databases is a snap! • Querying up-to-date data with Real-Time Apply! • Supporting more and more Applications! • Moving forward to Rolling Upgrades!

  41. Resources • Maximum Availability Architecture white papers: http://otn.oracle.com/deploy/availability/htdocs/maa.html • New SQL Apply Best Practices Paper now available! • HA Portal on OTN: http://otn.oracle.com/deploy/availability • Data Guard home page on OTN: http://otn.oracle.com/deploy/availability/htdocs/odg_overview.html • Oracle Consulting Services: http://otn.oracle.com/consulting

  42. 11:00 AM How Oracle Database 10g Revolutionizes Availability and Enables the Grid 3:30 PM Oracle Recovery Manager (RMAN) 10g: Reloaded 5:00 PM Proven Techniques for Maximizing Availability 8:30 AM Oracle Database 10g - RMAN and ATA Storage in Action 11:00 AM Oracle Data Guard: Maximum Data Protection at Minimum Cost 1:00 PM Oracle Database 10g Time Navigation: Human-Error Correction 4:30 PM Data Guard SQL Apply: Back to the Future Next StepsHigh AvailabilitySessions from Oracle Tuesday in Moscone Room 304 Wednesday in Moscone Room 304 For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/

  43. Database HA Demos All Four Days In The Oracle Demo Campground Real Application Clusters Data Guard Database Backup & Recovery Flashback Recovery LogMiner, Online Redefinition, and Cross Platform Transportable Tablespaces Next StepsHigh AvailabilitySessions from Oracle Thursday 8:30 AM in Moscone Room 304 Oracle Database 10g Data Warehouse Backup and Recovery: Automatic, Simple, Reliable 8:30 AM in Moscone Room 104 Building RAC Clusters over InfiniBand For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/

  44. Reminder – please complete the OracleWorld online session surveyThank you.

  45. Q & Q U E S T I O N S A N S W E R S A

More Related