1 / 32

Time Travel Back To The Future With Oracle 11g Total Recall

Time Travel Back To The Future With Oracle 11g Total Recall. Gavin Soorma Senior Oracle DBA, Bankwest. Historical Data Retention – Why?. Laws and regulations mandate maintenance of customer data for long retention periods - SOX, HIPAA and BASEL–II

braith
Download Presentation

Time Travel Back To The Future With Oracle 11g Total Recall

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. Time Travel Back To The FutureWith Oracle 11g Total Recall Gavin Soorma Senior Oracle DBA, Bankwest AUSOUG National Conference Series 2009

  2. Historical Data Retention – Why? • Laws and regulations mandate maintenance of customer data for long retention periods - SOX, HIPAA and BASEL–II • Non compliance can attract fines, loss of investor and customer confidence, business reputation • Historical data has immense business value • Historical data can be used to extract and analyze market trends and customer behaviour on which business decisions can be made. • For example - Passenger traffic information based on point of sale, month of travel, destination, class of travel AUSOUG National Conference Series 2009

  3. Data Retention Requirements • Historical data should be completely secure – access only to authorized personnel • Should be tamper proof – protected from any updates • Should be easily accessible without requiring application or interface changes • Storage footprint should be minimised considering the volume of historical data • Should be easily manageable AUSOUG National Conference Series 2009

  4. Life before Total Recall • Prior to 11g, historical data management was at the application level – added complexity to applications for data tracking • Use of triggers incurred a performance overhead • Third party solutions were costly and required additional customisations to tailor for specific application • How far back you can flashback to is dependant on undo data or available flashback logs . • The Undo tablespace was meant for providing transactional consistency, not archival of data • Cannot collect undo data for a single or limited set of tables AUSOUG National Conference Series 2009

  5. Life before Total Recall SQL> select prod_id from mysales 2 as of timestamp to_timestamp('19-OCT-2009 11:22:00','DD-MON-YYYY HH24:MI:SS') 3 where rownum <10; * ERROR at line 1: ORA-01466: unable to read data - table definition has changed SQL> select * from MGMT_METRICS_1HOUR 2 as of timestamp 3 to_timestamp('10-OCT-2009 00:00:00','DD-MON-YYYY HH24:MI:SS'); select * from MGMT_METRICS_1HOUR * ERROR at line 1: ORA-08180: no snapshot found based on specified time SQL> select * from MGMT_METRICS_1HOUR 2 as of timestamp 3 to_timestamp('10-OCT-2009 00:00:00','DD-MON-YYYY HH24:MI:SS'); select * from MGMT_METRICS_1HOUR ERROR: ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small AUSOUG National Conference Series 2009

  6. Pre 11g – Set these parameters properly! • Key parameters which influence undo data retention and flashback log retention • UNDO_RETENTION (seconds) SQL> ALTER SYSTEM SET UNDO_RETENTION = 2400; >>>> 40 minutes • DB_RECOVERY_FILE_DEST_SIZE • DB_RECOVERY_FILE_DEST (Note – in RAC must be on shared storage) AUSOUG National Conference Series 2009

  7. Flashback technology over the years Flashback Query Flashback Version Query Flashback Table Flashback Database Flashback Data Archive Flashback Transaction Backout AUSOUG National Conference Series 2009

  8. What is Total Recall • Yes – its an Arnold Schwarzenegger blockbuster (1990) • It’s also a separate licensed option in Oracle 11g Enterprise Edition • Leverages Flashback technology which has been around since Oracle 9i • Flashback Data Archive is the underlying technology behind Total Recall • Removes the limitation prevalent until Oracle 11g related to dependence on undo data which is recycled based on undo and flashback related database parameters AUSOUG National Conference Series 2009

  9. Total Recall Features • Easy to configure – apply to all tables, one or a group of tables with simple “enable archive” command • Secure – complete protection from accidental or malicious updates and deletes • Efficiency of performance and storage – capture process is asynchronous background process and data in history tables is partitioned as well as compressed automatically • Easy to access historical data using standard SQL “AS OF” constructs • Automated data management – historical data is automatically purged without any human intervention • Retention policies customised to suit business needs AUSOUG National Conference Series 2009

  10. Flashback Data Archive Uses • Change Tracking • Information Life Cycle Management • Auditing • Generating Reports • Compliance • Recovering from human error AUSOUG National Conference Series 2009

  11. Flashback Data Archive – behind the scene • Tablespace • - Flashback Data Archive • - FBDA History Tables • Primary source for historical data is the • Undo data • Background process fbda captures data • asynchronously: • Every 5 minutes (default) • More frequent intervals based on activity • Undo on tracked tables not recycled until • history is archived AUSOUG National Conference Series 2009

  12. Flashback Archive – Getting Started • System Privilege - FLASHBACK ARCHIVE ADMINISTER to create and administer a flashback data archive • Connect explicitly as SYSDBA • Object Privilege - FLASHBACK ARCHIVE privilege on the specific flashback data archive to enable historical data tracking • Quota on the tablespace where the flashback data archive has been created AUSOUG National Conference Series 2009

  13. Creating a Flashback Data Archive • Create a new tablespace or use existing tablespace – tablespace needs to be ASSM • Specify the FBDA as the default (optional) • Assign a quota for the FBDA (optional) • Assign a retention period for the FBDA • Retention period integer denoting days,months or years • Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off. AUSOUG National Conference Series 2009

  14. Creating a Flashback Data Archive SQL> CREATE TABLESPACE his_data_1 2 DATAFILE '+data' SIZE 500M; Tablespace created. SQL> CREATE FLASHBACK ARCHIVE DEFAULT fba1 2 TABLESPACE his_data_1 3 RETENTION 7 DAY; Flashback archive created. SQL> CREATE FLASHBACK ARCHIVE fba2 2 TABLESPACE his_data_1 3 QUOTA 200M 4 RETENTION 30 DAY; Flashback archive created. AUSOUG National Conference Series 2009

  15. Creating a Flashback Data Archive SQL> GRANT FLASHBACK ARCHIVE ON fba1 TO scott; Grant succeeded. SQL> GRANT FLASHBACK ARCHIVE ON fba2 TO scott; Grant succeeded. SQL> CREATE TABLE 2 EMPSAL_HIS 3 (EMPNO number, 4 ENAME VARCHAR2(10), 5 SAL NUMBER, 6 FLASHBACK ARCHIVE; Table created. SQL> ALTER TABLE mysales FLASHBACK ARCHIVE fba2; Table altered. AUSOUG National Conference Series 2009

  16. Let’s Test Total Recall SQL> conn sh/sh Connected. SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'DD- -------------------- 12-NOV-2009 14:14:07 SQL> select distinct prod_id from mysales; PROD_ID ---------- 444 SQL> update mysales set prod_id=555; 1787686 rows updated. SQL> commit; Commit complete. AUSOUG National Conference Series 2009

  17. Let’s Test Total Recall Thu Nov 12 14:21:42 2009 FBDA started with pid=40, OS id=4389 Thu Nov 12 14:25:54 2009 FBDA started with pid=23, OS id=4758 SQL> create undo tablespace undotbs2 datafile '+data' size 100M; Tablespace created. SQL> alter system set undo_tablespace='UNDOTBS2'; System altered. SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. SQL> select distinct prod_id from mysales 2 as of timestamp 3 to_timestamp('12-NOV-2009 14:00:00','DD-MON-YYYY HH24:MI:SS'); PROD_ID ---------- 444 AUSOUG National Conference Series 2009

  18. The proof is in the EXPLAIN PLAN ------------------------------------------------------------------------------------------------------------------------ - | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ - | | 5 | PARTITION RANGE SINGLE| | 1 | 39 | | 2 (0)| 00:00:01 | KEY | 1 | |* 6 | TABLE ACCESS FULL | SYS_FBA_HIST_77429 | 1 | 39 | | 2 (0)| 00:00:01 | KEY | 1 | |* 7 | FILTER | | | | | | | | | |* 8 | HASH JOIN RIGHT OUTER | | 89384 | 174M| | 36333 (1)| 00:07:17 | | | |* 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_77429 | 1 | 2028 | | 2 (0)| 00:00:01 | | | |* 10 | TABLE ACCESS FULL | MYSALES | 89384 | 1309K| | 36331 (1)| 00:07:16 | | | ------------------------------------------------------------------------------------------------------------------------ - AUSOUG National Conference Series 2009

  19. Using FBDA to recover from human error SQL> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 60 SQL> DELETE scott.dept; 4 rows deleted. SQL> INSERT INTO scott.dept 2 SELECT * FROM scott.dept 3 AS OF TIMESTAMP 4 TO_TIMESTAMP ('02-NOV-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'); 4 rows created. AUSOUG National Conference Series 2009

  20. Flashback Data Archive Data Dictionary Views SQL> desc DBA_FLASHBACK_ARCHIVE_TS Name Null? Type ----------------------------------------- -------- ---------------------------- FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) FLASHBACK_ARCHIVE# NOT NULL NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) QUOTA_IN_MB VARCHAR2(40) SQL> desc DBA_FLASHBACK_ARCHIVE Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER_NAME VARCHAR2(30) FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) FLASHBACK_ARCHIVE# NOT NULL NUMBER RETENTION_IN_DAYS NOT NULL NUMBER CREATE_TIME TIMESTAMP(9) LAST_PURGE_TIME TIMESTAMP(9) STATUS VARCHAR2(7) SQL> desc DBA_FLASHBACK_ARCHIVE_TABLES Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(30) OWNER_NAME NOT NULL VARCHAR2(30) FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) ARCHIVE_TABLE_NAME VARCHAR2(53) STATUS VARCHAR2(8) AUSOUG National Conference Series 2009

  21. Flashback Data Archive Data Dictionary Views SQL> SELECT FLASHBACK_ARCHIVE_NAME,TABLESPACE_NAME,QUOTA_IN_MB 2 FROM DBA_FLASHBACK_ARCHIVE_TS; FLASHBACK_ TABLESPACE_NAME QUOTA_IN_MB ---------- ------------------------------ ---------------------------------------- FBA1 HIS_DATA_1 FBA2 HIS_DATA_1 200 SQL> SELECT FLASHBACK_ARCHIVE_NAME, to_char(CREATE_TIME,'dd-mon-yyyy') Created, 2 RETENTION_IN_DAYS,STATUS FROM DBA_FLASHBACK_ARCHIVE; FLASHBACK_ARCHIVE_NA CREATED RETENTION_IN_DAYS STATUS -------------------- ----------- ----------------- ------- FBA1 02-nov-2009 7 DEFAULT FBA2 02-nov-2009 30 SQL> SELECT TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS 2 FROM DBA_FLASHBACK_ARCHIVE_TABLES; TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS ----------- -------------------- -------------------- ------------ EMPSAL_HIS FBA1 SYS_FBA_HIST_77419 ENABLED MYSALES FBA2 SYS_FBA_HIST_77429 ENABLED AUSOUG National Conference Series 2009

  22. Flashback Data Archive Internals SQL> select object_id from dba_objects where object_name=‘DEPT'; OBJECT_ID ---------- 73201 SQL> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ DEPT USERS SYS_FBA_DDL_COLMAP_73201 HIS_DATA_1 SYS_FBA_TCRV_73201 HIS_DATA_1 SYS_FBA_HIST_73201 SQL> desc SYS_FBA_HIST_73201 Name Null? Type ----------------------------------------- -------- ---------------------------- RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OPERATION VARCHAR2(1) DEPTNO NUMBER(4) DNAME VARCHAR2(10) LOC VARCHAR2(20) AUSOUG National Conference Series 2009

  23. FBDA History Tables SQL> INSERT INTO MYSALES 2 SELECT * FROM SALES; 918843 rows created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM SYS_FBA_HIST_77429; COUNT(*) ---------- 0 SQL> UPDATE MYSALES 2 SET PROD_ID=1 WHERE ROWNUM < 10001; 10000 rows updated. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM SYS_FBA_HIST_77429; COUNT(*) ---------- 10000 AUSOUG National Conference Series 2009

  24. History Tables are Partitioned and Compressed SQL> SELECT TABLE_NAME FROM USER_TABLES 2 WHERE TABLE_NAME LIKE '%FBA%'; TABLE_NAME ------------------------------ SYS_FBA_DDL_COLMAP_73201 SYS_FBA_TCRV_73201 SYS_FBA_HIST_73201 SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS; TABLE_NAME PARTITION_NAME COMPRESS ------------------------------ ------------------------------ -------- SYS_FBA_HIST_73201 HIGH_PART ENABLED SQL> select TABLE_NAME ,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables; TABLE_NAME PARTITION PARTITION_COUNT ------------------------------ --------- --------------- SYS_FBA_HIST_78721 RANGE 1 AUSOUG National Conference Series 2009

  25. MODIFY a Flashback Archive SQL> ALTER FLASHBACK ARCHIVE fba1 MODIFY TABLESPACE his_data_1 QUOTA 250M; Flashback archive altered. SQL> ALTER FLASHBACK ARCHIVE fba1 MODIFY RETENTION 1 DAY; Flashback archive altered. SQL> DROP FLASHBACK ARCHIVE fba1; Flashback archive dropped. SQL> alter table dept_copy no flashback archive; alter table dept_copy no flashback archive * ERROR at line 1: ORA-55620: No privilege to use Flashback Archive AUSOUG National Conference Series 2009

  26. Purging a Flashback Archive • Automatic purging happens one day after retention expiry • Manual purging can also be performed by a user with FLASHBACK ADMINISTER privilege • Purge all historical data from Flashback Data Archive fda1: SQL> ALTER FLASHBACK ARCHIVE fda1 PURGE ALL; • Purge all historical data older than one day from Flashback Data Archive fda1: SQL> ALTER FLASHBACK ARCHIVE fda1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY); • Purge all historical data older than SCN 528967 from Flashback Data Archive fda1: SQL> ALTER FLASHBACK ARCHIVE fda1 PURGE BEFORE SCN 528967; AUSOUG National Conference Series 2009

  27. Operations permitted on FBDA Tables • In11g Release 2, we can alter the structure of a tracked table, modify columns, truncate the table. • In 11g Release 1 we could not do any of the above • In 11g release 1 and 2, we cannot drop a tracked table • We cannot delete a history table even as SYSDBA • We cannot update a history table even as SYSDBA AUSOUG National Conference Series 2009

  28. Operations on tracked tables- 11g R1 SQL> truncate table ht_emp; truncate table ht_emp * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table SQL> ALTER TABLE ht_emp MODIFY emp_manager VARCHAR2(10); ALTER TABLE ht_emp MODIFY emp_manager VARCHAR2(10) * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table SQL> drop table ht_emp; drop table ht_emp * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table SQL> DELETE scott.sys_fba_hist_73201; DELETE SYS_FBA_HIST_73201 * ERROR at line 1: ORA-00942: table or view does not exist SQL> DROP TABLE scott.sys_fba_hist_73201; DROP TABLE scott.sys_fba_hist_73201 * ERROR at line 1: ORA-00942: table or view does not exist AUSOUG National Conference Series 2009

  29. Operations on tracked tables- 11g R2 SQL> ALTER TABLE emp MODIFY job VARCHAR2(20); Table altered. SQL> TRUNCATE TABLE emp; Table truncated. SQL> ALTER TABLE emp DROP COLUMN sal; Table altered. SQL> DROP TABLE emp; DROP TABLE emp * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table SQL> DELETE scott.sys_fba_hist_73201; DELETE SYS_FBA_HIST_73201 * ERROR at line 1: ORA-00942: table or view does not exist AUSOUG National Conference Series 2009

  30. Flashback Archive Space Quota • If Flashback Archive space is exhausted because quota has been exceeded, then DML statements on tracked tables will fail. • Database will issue out-of-space alerts when space in FBDA exceeds 90% of specified quota • Keep disk space allocated to tablespace in mind when specifying retention periods. SQL> delete from mysales where rownum <50001; delete from mysales where rownum <50001 * ERROR at line 1: ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on "MYSALES" is suspended SQL> alter flashback archive fda1 2 modify tablespace his_data_1 quota 500M; Flashback archive altered. SQL> delete from sh.mysales where rownum <50001; 50000 rows deleted. AUSOUG National Conference Series 2009

  31. Final Thoughts …. • Flashback technology introduced in Oracle 9i and has been continually enhanced • Prior to 11g, reliance on undo data and flashback logs for historical data • Background process asynchronously writes undo data to disk and does not recycle undo data until archived • Maintenance of historical data is a mandatory legal requirement as well as required for the business value it provides • Long term customised retention of historical data out of the box with no requirement for any application level modifications • Secure and ease of administration – history tables are protected from any kind of modification even by administrators and automated purging of data without any administrator intervention AUSOUG National Conference Series 2009

  32. Thanks for attending!! Q & Q U E S T I O N S A N S W E R S http://gavinsoorma.wordpress.com Tel: 0417713124 gavin.soorma@bankwest.com.au A AUSOUG National Conference Series 2009

More Related