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

1
Time Travel Back To The FutureWith Oracle 11g
Total Recall
Gavin Soorma Senior Oracle
DBA, Bankwest
2
Historical Data Retention Why?

• Laws and regulations mandate maintenance of
  customer data for long retention periods - SOX,
  HIPAA and BASELII
• 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

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

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

5
Life before Total Recall

• SQLgt select prod_id from mysales
• 2 as of timestamp to_timestamp('19-OCT-2009
  112200','DD-MON-YYYY HH24MISS')
• 3 where rownum lt10
• ERROR at line 1
• ORA-01466 unable to read data - table definition
  has changed
• SQLgt select from MGMT_METRICS_1HOUR
• 2 as of timestamp
• 3 to_timestamp('10-OCT-2009
  000000','DD-MON-YYYY HH24MISS')
• select from MGMT_METRICS_1HOUR
• ERROR at line 1
• ORA-08180 no snapshot found based on specified
  time
• SQLgt select from MGMT_METRICS_1HOUR

6
Pre 11g Set these parameters properly!

• Key parameters which influence undo data
  retention and flashback log retention
• UNDO_RETENTION (seconds)
• SQLgt ALTER SYSTEM SET UNDO_RETENTION 2400
  gtgtgtgt 40 minutes
• DB_RECOVERY_FILE_DEST_SIZE
• DB_RECOVERY_FILE_DEST (Note in RAC must be on
  shared storage)

7
Flashback technology over the years
Flashback Query
Flashback Version Query
Flashback Table
Flashback Database
Flashback Data Archive Flashback Transaction Backo
ut
8
What is Total Recall

• Yes its an Arnold Schwarzenegger
• blockbuster (1990)
• Its 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

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

10
Flashback Data Archive Uses

• Change Tracking
• Information Life Cycle Management
• Auditing
• Generating Reports
• Compliance
• Recovering from human error

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

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

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.

14
Creating a Flashback Data Archive

• SQLgt CREATE TABLESPACE his_data_1
• 2 DATAFILE 'data' SIZE 500M
• Tablespace created.
• SQLgt CREATE FLASHBACK ARCHIVE DEFAULT fba1
• 2 TABLESPACE his_data_1
• 3 RETENTION 7 DAY
• Flashback archive created.
• SQLgt CREATE FLASHBACK ARCHIVE fba2
• 2 TABLESPACE his_data_1
• 3 QUOTA 200M
• 4 RETENTION 30 DAY
• Flashback archive created.

15
Creating a Flashback Data Archive

• SQLgt GRANT FLASHBACK ARCHIVE ON fba1 TO scott
• Grant succeeded.
• SQLgt GRANT FLASHBACK ARCHIVE ON fba2 TO scott
• Grant succeeded.
• SQLgt CREATE TABLE
• 2 EMPSAL_HIS
• 3 (EMPNO number,
• 4 ENAME VARCHAR2(10),
• 5 SAL NUMBER,
• 6 FLASHBACK ARCHIVE
• Table created.
• SQLgt ALTER TABLE mysales FLASHBACK ARCHIVE fba2

16
Lets Test Total Recall

• SQLgt conn sh/sh
• Connected.
• SQLgt select to_char(sysdate,'DD-MON-YYYY
  HH24MISS') from dual
• TO_CHAR(SYSDATE,'DD-
• --------------------
• 12-NOV-2009 141407
• SQLgt select distinct prod_id from mysales
• PROD_ID
• ----------
• 444
• SQLgt update mysales set prod_id555
• 1787686 rows updated.

17
Lets Test Total Recall

• Thu Nov 12 142142 2009
• FBDA started with pid40, OS id4389
• Thu Nov 12 142554 2009
• FBDA started with pid23, OS id4758
• SQLgt create undo tablespace undotbs2 datafile
  'data' size 100M
• Tablespace created.
• SQLgt alter system set undo_tablespace'UNDOTBS2'
• System altered.
• SQLgt drop tablespace undotbs1 including contents
  and datafiles
• Tablespace dropped.

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)
  000001 KEY 1
• 6 TABLE ACCESS FULL
  SYS_FBA_HIST_77429 1 39
  2 (0) 000001 KEY 1
• 7 FILTER
  
  

19
Using FBDA to recover from human error

• SQLgt show parameter undo_retention
• NAME TYPE
  VALUE
• ------------------------------------ -----------
  ------------------------------
• undo_retention integer
  60
• SQLgt DELETE scott.dept
• 4 rows deleted.
• SQLgt INSERT INTO scott.dept
• 2 SELECT FROM scott.dept
• 3 AS OF TIMESTAMP
• 4 TO_TIMESTAMP ('02-NOV-2009
  200000','DD-MON-YYYY HH24MISS')
• 4 rows created.

20
Flashback Data Archive Data Dictionary Views

• SQLgt 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)
• SQLgt 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)

21
Flashback Data Archive Data Dictionary Views

• SQLgt 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
• SQLgt 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

22
Flashback Data Archive Internals

• SQLgt select object_id from dba_objects where
  object_nameDEPT'
• OBJECT_ID
• ----------
• 73201
• SQLgt 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
• SQLgt desc SYS_FBA_HIST_73201
• Name Null?
  Type
• -----------------------------------------
  -------- ----------------------------

23
FBDA History Tables

• SQLgt INSERT INTO MYSALES
• 2 SELECT FROM SALES
• 918843 rows created.
• SQLgt COMMIT
• Commit complete.
• SQLgt SELECT COUNT() FROM SYS_FBA_HIST_77429
• COUNT()
• ----------
• 0
• SQLgt UPDATE MYSALES
• 2 SET PROD_ID1 WHERE ROWNUM lt 10001
• 10000 rows updated.

24
History Tables are Partitioned and Compressed

• SQLgt 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
• SQLgt SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION
  from USER_TAB_PARTITIONS
• TABLE_NAME PARTITION_NAME
  COMPRESS
• ------------------------------ -------------------
  ----------- --------
• SYS_FBA_HIST_73201 HIGH_PART
  ENABLED
• SQLgt select TABLE_NAME ,PARTITIONING_TYPE,PARTITIO
  N_COUNT from user_part_tables
• TABLE_NAME PARTITION
  PARTITION_COUNT

25
MODIFY a Flashback Archive

• SQLgt ALTER FLASHBACK ARCHIVE fba1 MODIFY
  TABLESPACE his_data_1 QUOTA 250M
• Flashback archive altered.
• SQLgt ALTER FLASHBACK ARCHIVE fba1 MODIFY
  RETENTION 1 DAY
• Flashback archive altered.
• SQLgt DROP FLASHBACK ARCHIVE fba1
• Flashback archive dropped.
• SQLgt 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

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
• SQLgt ALTER FLASHBACK ARCHIVE fda1 PURGE ALL
• Purge all historical data older than one day from
  Flashback Data Archive fda1
• SQLgt ALTER FLASHBACK ARCHIVE fda1
• PURGE BEFORE TIMESTAMP (SYSTIMESTAMP -
  INTERVAL '1' DAY)
• Purge all historical data older than SCN 528967
  from Flashback Data Archive fda1
• SQLgt ALTER FLASHBACK ARCHIVE fda1 PURGE BEFORE
  SCN 528967

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

28
Operations on tracked tables- 11g R1

• SQLgt truncate table ht_emp
• truncate table ht_emp
• ERROR at line 1
• ORA-55610 Invalid DDL statement on
  history-tracked table
• SQLgt 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
• SQLgt drop table ht_emp
• drop table ht_emp
• ERROR at line 1
• ORA-55610 Invalid DDL statement on
  history-tracked table
• SQLgt DELETE scott.sys_fba_hist_73201

29
Operations on tracked tables- 11g R2

• SQLgt ALTER TABLE emp MODIFY job VARCHAR2(20)
• Table altered.
• SQLgt TRUNCATE TABLE emp
• Table truncated.
• SQLgt ALTER TABLE emp DROP COLUMN sal
• Table altered.
• SQLgt DROP TABLE emp
• DROP TABLE emp
• ERROR at line 1
• ORA-55610 Invalid DDL statement on
  history-tracked table

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.

SQLgt delete from mysales where rownum
lt50001 delete from mysales where rownum lt50001
ERROR at line 1 ORA-55617 Flashback
Archive "FDA1" runs out of space and tracking on
"MYSALES" is suspended SQLgt alter flashback
archive fda1 2 modify tablespace his_data_1
quota 500M Flashback archive altered. SQLgt
delete from sh.mysales where rownum
lt50001 50000 rows deleted.
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

32
Thanks for attending!!
http//gavinsoorma.wordpress.com Tel
0417713124 gavin.soorma_at_bankwest.com.au
A