Automate a Secure Historical Data Store with Oracle Total Recall

2. Automate a Secure Historical Data Store with Oracle Total Recall Venky Radhakrishnan Kevin Jernigan Database Developer Senior Director Product Management

3. Oracle OpenWorldLatin America 2010 December 7–9, 2010

4. Oracle OpenWorldBeijing 2010 December 13–16, 2010

5. Oracle Products Available Online Oracle Store Buy Oracle license and support online today atoracle.com/store

6. 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.

7. You Track History for a Few Key Fact Tables Why Not Track History for all Business Data Orders, Shipments Employees Products Assets “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?” You can answer this: “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 The ROI on “knowing who did what” can be huge “What changes did disgruntled employee Sam make in his last week?” Never Lose Important Business DataTotal Recall = Total History Can you answer these? How long would it take?

8. History is Complicated Orders, Shipments Employees Products Assets History Kept 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

9. 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

10. Total Recall - Turn it on and You’re DoneTotal Recall = Total History Turn on archiving for the tables for which history is not normally tracked Total Recall automatically and transparently records changes over time Transparent History Tracking Employees Products Assets Easily Query Data as of any point in time Not just single rows or tables, but complex joins Total Recall Archive

11. 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 history queries Flashback Request Base Table Flashback Data Archive

12. How Total Recall Works 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 . . . • 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 Originaldata in buffer cache Undodata undo history fda slaves (as needed) Background (fda) process help history • Partitions automatically created based on time, volume • Queries skip unrelated partitions employees fda1

13. Total Recall Setup: 3 Easy Steps Step 1: Create tablespace (tbs2) to hold flashback data archive Step 2: Create flashback data archive Requires new system privilege FLASHBACK ARCHIVE ADMINISTER fda1 CREATE FLASHBACK ARCHIVE DEFAULT fda1 TABLESPACE tbs2 RETENTION 5 YEAR; Step 3: Enable archiving on desired tables GRANT FLASHBACK ARCHIVE ADMINISTER TO Bill; employees history Requires new object privilege FLASHBACK ARCHIVE ALTER TABLE employeesFLASHBACK ARCHIVE fda1; GRANT FLASHBACK ARCHIVE ON fda1 TO Susan;

14.  Flashback Query (SELECT AS OF)“Retrieve the employee table as it looked 90 days ago” SELECT * FROM employees AS OF (SYSDATE – 90);  Active Undo Segments Flashback Version Query (SELECT VERSIONS BETWEEN start AND end)“Retrieve Jim’s 2008 compensation history” History Base Table fda1 employees Undodata 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’; Total Recall Usage: Flashback

15. 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 TIME

16. Transparent Schema EvolutionNew in 11g Release 2 Schema evolution Additional DDL support (Add Column always supported) Drop, Rename, Modify Column Drop, Truncate Partition Rename, Truncate Table Flashback queries work across DDL changes Output is presented accordingly All other DDL NOT automatically supported (see next slide) Drop Column Add Column Add Column Flashback Version Query       time

17. Full Schema EvolutionNew 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 Base Table History Table HistoryTable   Alter schema disassociate Alter schema associate  

18. New in Oracle Database 11.2.0.2 Performance improvements Faster archiving Support for Flashback Table

19. A Silent PartnerMinimal Overhead 5% average increase in response time with Total Recall ( 54% average increase using Triggers )

20. Customer ExampleHealthways 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

21. 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

22. 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

23. Total Recall - Turn it on and You’re DoneTotal 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