Transcript Slide 1
1 <Insert Picture Here> Automate a Secure Historical Data Store with Oracle Total Recall Venky Radhakrishnan Database Developer Kevin Jernigan Senior Director Product Management Oracle OpenWorld Latin America 2010 December 7–9, 2010 3 Oracle OpenWorld Beijing 2010 December 13–16, 2010 4 Oracle Products Available Online Oracle Store Buy Oracle license and support online today at oracle.com/store 5 The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 6 Never Lose Important Business Data Total Recall = Total History You Track History for a Few Key Fact Tables Why Not Track History for all Business Data Orders, Shipments Employees Products Assets You can answer this: “Which employees were on leave 3 years ago?” “What options did product YY have in 2001?” “What is the average age of laptops retired last year?” “How much of product XX was sold 3 years ago?” When you need to know… How will you know? Recreating history after the fact is expensive or impossible Can you answer these? How long would it take? The ROI on “knowing who did what” can be huge “What changes did disgruntled employee Sam make in his last week?” 7 History is Complicated Orders, Shipments Employees History Kept Products Assets Partial or No History • Keeping history is complicated • Extensive application logic is needed to track history • Must be maintained with every application change • Viewing history is complicated • Querying and reporting history data is hard, as schemas evolve • Viewing data across multiple history tables is even harder • The result is history is only tracked for a few key tables • Often raw fact data is tracked but context is not • e.g. Sales history is tracked, but not options, quota rules, or territories 8 Tracking Changes Before Total Recall • Application logic – Problem: Direct DB updates bypass history capture • Database triggers – Problem: Performance impact • Log mining – Problem: Complex & error prone 9 Total Recall - Turn it on and You’re Done Total Recall = Total History Transparent History Tracking Employees Products Total Recall Archive Assets Turn on archiving for the tables for which history is not normally tracked Total Recall automatically and transparently records changes over time Easily Query Data as of any point in time Not just single rows or tables, but complex joins 10 Total Recall Functionality • Automate tracking of historical database changes – – – – Turn it on at the table level Specify the retention period All subsequent changes transparently archived and tamper-proof Records older than retention period are automatically removed • Use Flashback technologies to retrieve history – SELECT … AS OF TIMESTAMP… SELECT … VERSIONS BETWEEN TIMESTAMP and TIMESTAMP… • Modify schema – it keeps on working updates Base Table history Flashback Data Archive queries Flashback Request 11 How Total Recall Works DML • Create new tablespace to hold flashback archive • Create flashback data archive, assign to tablespace, specify retention period • Alter base table(s) to enable archiving, assign to flashback archive DML Original data in buffer cache employees undo Undo data Background (fda) process fda1 – History captured async by background process(es) at self-tuned intervals (5 min default) – History data stored compressed and partitioned, SecureFiles de-duplicated – History data automatically purged per retention policy fda slaves (as needed) help ... • Partitions automatically created based on time, volume • Queries skip unrelated partitions 12 Total Recall Setup: 3 Easy Steps FLASHBACK ARCHIVE ADMINISTER GRANT FLASHBACK ARCHIVE ADMINISTER TO Bill; DML Requires new system privilege Step 1: Create tablespace (tbs2) to hold flashback data archive Step 2: Create flashback data archive CREATE FLASHBACK ARCHIVE DEFAULT fda1 TABLESPACE tbs2 RETENTION 5 YEAR; employees fda1 Requires new object privilege FLASHBACK ARCHIVE GRANT FLASHBACK ARCHIVE ON fda1 TO Susan; history Step 3: Enable archiving on desired tables ALTER TABLE employees FLASHBACK ARCHIVE fda1; 13 Total Recall Usage: Flashback Flashback Query (SELECT AS OF) “Retrieve the employee table as it looked 90 days ago” SELECT * FROM employees AS OF (SYSDATE – 90); Flashback Version Query (SELECT VERSIONS BETWEEN start AND end) “Retrieve Jim’s 2008 compensation history” History Base Table fda1 employees Active Undo Segments Undo data SELECT salary, bonus FROM employee VERSIONS BETWEEN ‘2008-01-01’ and ‘2008-12-31’ WHERE name = ‘Jim’; Flashback Version Query (Pull from current and historical sources) “Get Jerry’s 401K balance since 1/1/2008” SELECT 401_bal FROM employees VERSIONS BETWEEN ‘31-DEC-2007‘ and MAXVALUE WHERE name = ‘Jerry’; 14 What’s New in Oracle Database 11g Release 2 • Schema evolution support – Alter base table – history table automatically adjusts – Complex DDL changes (e.g. table split) accommodated • Flashback query support across DDL changes • Performance enhancements – Parallel DML, slaves during archiving – Auto-tuning for heavy loads – Bulk archiving of small transactions 15 Transparent Schema Evolution New in 11g Release 2 • Schema evolution – Additional DDL support (Add Column always supported) • Drop, Rename, Modify Column • Drop, Truncate Partition • Rename, Truncate Table Add Column Add Column Drop Column Flashback Version Query time – Flashback queries work across DDL changes • Output is presented accordingly – All other DDL NOT automatically supported (see next slide) 16 Full Schema Evolution New in 11g Release 2 • Disassociate/Associate procedures – DBMS_FLASHBACK_ARCHIVE (PL/SQL package) – Disables Total Recall on specified tables, allowing more complex DDL (upgrades, split tables, etc.) – Enforces schema integrity after association • Base table and history table schemas must be the same – Requires FLASHBACK ARCHIVE ADMINISTER privilege History Table disassociate associate Base Table History Table 17 New in Oracle Database 11.2.0.2 • Performance improvements – Faster archiving • Support for Flashback Table 18 5% average increase in response time with Total Recall A Silent Partner Minimal Overhead ( 54% average increase using Triggers ) Total Recall vs Triggers 70 56.9% % Increase in Response Time 60 54.0% 53.2% 52.8% 50 40 30 20 10 8.9% 4.5% 2.5% 6.0% 0 100k/1k 100k/10k 1M/1k 1M/10k # of Row s / Com m it Interval Total Recall Triggers 19 Customer Example Healthways • Clinical application – OLTP app – Up to 6 concurrent batch jobs that could make more than 100K changes per job • Design goal: easy to maintain history/auditing system – HIPAA requirements – Fix mistakes – Debug application • Database – 2TB, growing by 200GB/month • Growth driven by business, conversion from old system – 5 node RAC w/ 11g Release 2, OLTP and OLAP services – Total Recall enabled on 1,026 tables – 170GB in FDA tables – Retention set to 10 years – Largest table has 26 million rows, with 42 million rows in history 20 Total Recall: A Tool for Many Uses • ILM (Information Lifecycle Management) – Guarantee immutable history of financial data – Automatic retention policy enforcement • Historical reporting – Combine with DW and BI tools – E.g Analyze product changes over time • Error Recovery – Creates a longer flashback archive – E.g Restore records erroneously removed or updated 21 Total Recall: A Tool for Many Uses Combine Total Recall with Audit Vault for: • Data forensics – E.g. Find and revert changes made by disgruntled employee • Employee Fraud detection – E.g Find assets that were deleted but never sold 22 Total Recall - Turn it on and You’re Done Total Recall = Total History Foolproof and Secure History Data Application Transparency Optimal Storage Footprint Easy to Set Up Transparent History Tracking Employees Products Assets Total Recall Archive 23 24 25