chris ruel chris ruel@pti net n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chris Ruel chris.ruel@pti.net PowerPoint Presentation
Download Presentation
Chris Ruel chris.ruel@pti.net

Loading in 2 Seconds...

play fullscreen
1 / 14

Chris Ruel chris.ruel@pti.net - PowerPoint PPT Presentation


  • 143 Views
  • Uploaded on

Chris Ruel chris.ruel@pti.net. Oracle "Total Recall": Not the Awesome 80s Movie You’re Thinking Of Or… Oracle 11g: Flashback Data Archive. Oracle “Total Recall”. * Image used without permission. Flashback Data Archive. Track all changes to a record during its lifetime

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Chris Ruel chris.ruel@pti.net' - auryon


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
chris ruel chris ruel@pti net
Chris Ruel

chris.ruel@pti.net

Oracle "Total Recall": Not the Awesome 80s Movie You’re Thinking OfOr…Oracle 11g: Flashback Data Archive

oracle total recall
Oracle “Total Recall”

* Image used without permission

flashback data archive
Flashback Data Archive
  • Track all changes to a record during its lifetime
  • No need to code complex triggers and history tables
  • No need to write complex application logic to retrieve historical data
  • Completely transparent, secure and efficient
  • AKA: “Oracle Total Recall”
traditional archiving
Traditional Archiving
  • Developers and DBAs use
    • Database Triggers
    • application code
  • Database triggers can record before values and move them to “History” table
  • Application must have built-in modules to query different tables to view historical data
  • Storage can be overwhelming
    • Compression is an afterthought
traditional archiving1
Traditional Archiving

Traditional TRIGGER based archiving:

EMPLOYEES

EMPLOYEES_HIST

Trigger in place on EMPLOYEES for UPDATES, DELETE, etc. to MOVE/COPY rows.

  • Hundreds/thousands of tables duplicated
  • Hundreds/thousands of triggers to manage
  • Massive Shared Pool – performance disadvantage
  • Massive effort for maintenance on objects, code, and space
  • Retention Maintenance – usually does not happen
  • Need completely separate SQL code to access history
  • Easy to tamper with
flashback data archive1
Flashback Data Archive
  • Data stored in compressed form
  • Increased storage
    • You specify retention
  • Other resources conserved
    • CPU, UNDO, Developer
  • Operations that would invalidate history are disallowed
    • DROP
    • TRUNCATE
  • No possibility to modify historical data
    • Very safe from tampering
  • Takes UNDO out of the picture for longer, separately managed retention periods
flashback data archive2
Flashback Data Archive
  • FBDA is an online operation
  • View data as it existed in the past, right now
  • Granular down to the table
  • Ability to go to different points in time for different rows
  • In contrast to Flashback Database:
    • Physically takes the entire database back in time
    • Offline operation
    • Can only be done at database level, for one time period (Consistent Database View)
    • More for recovery than tracking history
undo flashback
UNDO Flashback
  • Oracle 9i introduced “Flashback Query”
  • Based on UNDO tablespace size and UNDO_RETENTION settings, DBAs could determine how far back undo data was stored
  • Requires massive amount of UNDO storage to go back for lengthy time periods
  • ALL data changes are saved for the longest period – no customization
  • Configuration is not an exact science for space consumption
  • No guarantee (until 10g)
    • RETENTION GUARANTEE
    • Could result in Database Hanging Issues instead of ORA-01555
flashback data archive3
Flashback Data Archive
  • Group objects according to retention periods
  • Create different tablespaces to hold FBDA data with different retention periods
    • One for 1 year retention
    • One for 2 year retention
    • One for 5 year retention
    • etc…
  • Indexes are not maintained for FBDA data, but you can create appropriate ones yourself
  • Data is automatically purged from FBDA day after retention expires
  • Set QUOTAS on Flashback Archives for growth
    • If quota is met, new transactions will be blocked!
    • Keep an eye on space usage. Check Alert Log.
flashback data archive example
Flashback Data Archive Example
  • CREATE TABLESPACE fbda_1yr DATAFILE ‘+DATA1’;
  • CREATE FLASHBACK ARCHIVE FBDA1

TABLESPACE fbda_1yr QUOTA 10G RETENTION 1 YEAR;

  • ALTER TABLE emp FLASHBACK ARCHIVE FBDA1;

…six months later…

  • SELECT *

FROM emp

AS OF TIMESTAMP SYSDATE – 180;

  • ALTER TABLE emp NO FLASHBACK ARCHIVE;
flashback data archive views
Flashback Data Archive Views
  • DBA_FLASHBACK_ARCHIVE
  • DBA_FLASHBACK_ARCHIVE_TABLES
  • DBA_FLASHBACK_ARCHIVE_TS
since you re licensing advanced compression
Since You’re Licensing Advanced Compression…
  • OLTP Table Compression
  • File Compression and De-duplication
  • Advanced RMAN Compression – 2.5x faster than “regular” backup compression
  • DataPump Compression
  • Network Compression for DataGuard and RAC
  • Net result can be enhanced performance across memory, disk, and network
how compression works in 11g
How Compression Works in 11g
  • Rows are inserted uncompressed
  • Block reaches PCTFREE threshold
  • Triggers compression
    • Compression always occurs while block in memory
  • More inserts uncompressed
  • Block Reaches PCTFREE threshold again
  • Triggers compression
  • …and so on

Advanced Compression FAQ:

http://www.oracle.com/technetwork/database/options/compression/faq-092157.html

the end
The End

Chris Ruel

chris.ruel@pti.net