1 / 29

Oracle Database 10g Time Navigation for Human Error Correction

Learn about Oracle Database 10g's Flashback features for speedy and precise error correction to reduce downtime caused by human errors. 8 Relevant

kaustin
Download Presentation

Oracle Database 10g Time Navigation for Human Error Correction

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: 40109 Oracle Database 10g Time Navigation: Human-Error Correction Tammy BednarSr. Product Manager Oracle Corporation Magnus Lubeck DBA/Systems Analyst CERN, IT-DB Group

  2. Time Navigation and HumanError Correction Correction Correction Correction noitcerroC Correction

  3. Human Errors Other Downtime Human Error • Estimated to be the biggest single cause of downtime • Need to quickly determine what happened and fix it • Localized damage • Needs surgical detection and repair • Example – removed wrong person named ‘Smith’ • Widespread damage • Requires drastic action to avoid long downtime • Example – batch job deletes this month’s orders • Analysis and correction using traditional recovery is slow and complex • Restore database to point in time and extract data • Oracle Database 10g is a breakthrough release for human error correction

  4. Flashback Time Navigation • Flashback Query • Query all data at point in time • Flashback Versions Query • See all versions of a row between two times • See transactions that changed the row • Flashback Transaction Query • See all changes made by a transaction Select * from Emp AS OF ‘2:00 P.M.’ where … Tx 3 Select * from Emp VERSIONS BETWEEN ‘2:00 PM’ and ‘3:00 PM’ where … Tx 2 Tx 1 Select * from DBA_TRANSACTION_QUERY where xid = ‘000200030000002D’;

  5. Flashback Error Correction Database • Recovery at all levels • Database Level • Flashback Database restores the whole database to time • Uses Flashback Logs • Table Level • Flashback Table restores rows in a set of tables to time • Uses UNDO in database • Flashback Drop restores a dropped table or a index • Recycle bin for DROPs • Row Level • Flashback Query restores rows to time Customer Order

  6. 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 • Replay log to restore DB to time • It’s fast - recover in minutes, not hours • It’s easy - single command restore Flashback Database to ‘2:05 PM’ Disk Write New Block Version Old Block Version “Rewind” button for the Database Data Files FlashbackLog

  7. Flashback Drop Emp • Quickly recover dropped objects Provides self-service recovery • Eliminate the need for TSPITR • Virtual Recycle Bin • Objects remain in the recycle bin until you permanently drop them with the PURGE command or recover them with the Flashback Table command. • Objects will remain in the recycle bin until there is no room in the tablespace for new rows or updates to existing rows or until the tablespace needs to be extended • Objects are purged in the order they were dropped. Drop table emp; Recycle bin Flashback Table emp before drop; Emp Mistake was made

  8. Flashback Table • Recover a table or tables to a specific point in time without restoring a backup • Provides a way for users to easily and quickly recover from accidental modifications without DBA involvement • In-place and online recovery of a table to a point in time in the past • Eliminate traditional restores and clone instances to recover a table or tables to a specific point in time • Data in the tables and all associated objects (indexes, constraints, triggers, etc.) are restored

  9. Flashback Versions Query • Provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. • Retrieve all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past • Use the transaction ID to perform transaction mining using LogMiner or Flashback Transaction Query to obtain additional information about the transaction.

  10. Flashback Transaction Query • Provides a way for you to view changes made to the database at the transaction level • When used in conjunction with Flashback Versions Query, it allows you to easily recover from user or application errors. • Benefits • Increase online diagnosability of problems in your database • Perform analysis and audits of transactions • Fast recovery at the transaction level

  11. Database Mission Determine what transaction removed Order ID 2453 Recover the corruption fast!

  12. Order ID 2453 & line item data

  13. The Order ID data is removed from the database

  14. Using the ‘VERSIONS BETWEEN’ syntax, you can find the operations and transactions executed for Order 2453

  15. After manually executing the SQL statements, Order 2453 & its line items have been inserted back into the database!

  16. Flashback Transaction Query Provides the SQL statements to ‘undo’ the offending operation

  17. CERN • Magnus Lubeck, DBA/Systems analyst • CERN is the world biggest particle physics research center. In operation for 50 years! • In our physics research we collect enormous amounts of data, of which some is stored in ORDBMS • Oracle 10G beta testing and evaluation is an important activity for CERN to support the physics community by understanding and request new features. • 10 people in our Oracle beta test team, focusing on different areas 6 miles

  18. General purpose for RAC testing Oracle 10G testing The setup Five dual CPU servers (four connected to SAN) Gbit interconnect Plenty of disk A set up workstations to run applications Not a setup for Benchmarks, but for proof of concept Testbed setup SAN

  19. Testing Flashback Features • The Flashback features is a subset of the beta testing performed at CERN • The Human Error Correction testing, in order of appearance – the one hour approach • Install Sofware + DB instance • Setting up the recovery area • Flashback table • Flashback transaction query • Flashback database

  20. CERN – Testing FB features Preparations Testing Flashback Transaction Query Configure Flash Recovery Area Software Installation Flashback Table Flashback Database SCN Database Instances Timeline RMAN Repository

  21. Summary of CERN Flashback testing • Flashback Table/Drop • Easy to get back dropped tables • Nice to be able to query dropped tables • Flashback Transaction Query • Diagnosability • Accountability • Flashback database • Fast recovery • Flash Recovery Area • Organizes & manages backups • Future use of FB features

  22. Revolution in Recovery • Flashback Revolutionizes Recovery • Operates on just the changed data • Time to correct error equals time to make error • Minutes instead of hours • Flashback is Easy • Single command instead of complex procedure Correction Time = Error Time + f(DB_SIZE)

  23. Flashback for All Users • END USER • Flashback Query • Flashback Versions Query • DEVELOPER • Flashback Versions Query • Flashback Transaction Query • Flashback Table • DATABASE ADMIN • Flashback Database • Flashback Drop • SYSTEM ADMIN • Data Guard

  24. 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/

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

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

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

More Related