Transcript Slide 1

Time Travel Back To The Future
With Oracle 11g Total Recall
Gavin Soorma
Senior Oracle DBA,
Bankwest
AUSOUG National Conference Series 2009
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
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
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
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
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
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
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
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
Flashback Data Archive Uses
•
Change Tracking
•
Information Life Cycle Management
•
Auditing
•
Generating Reports
•
Compliance
•
Recovering from human error
AUSOUG National Conference Series 2009
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
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
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
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
2 TABLESPACE his_data_1
3
QUOTA 200M
4 RETENTION 30 DAY;
fba2
Flashback archive created.
AUSOUG National Conference Series 2009
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>
2
3
4
5
6
CREATE TABLE
EMPSAL_HIS
(EMPNO number,
ENAME VARCHAR2(10),
SAL NUMBER,
FLASHBACK ARCHIVE;
Table created.
SQL> ALTER TABLE mysales FLASHBACK ARCHIVE fba2;
Table altered.
AUSOUG National Conference Series 2009
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
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
The proof is in the EXPLAIN PLAN
-----------------------------------------------------------------------------------------------------------------------| Id
|
| Operation
| Name
| Rows
| Bytes |TempSpc| Cost (%CPU)| Time
| Pstart| Pstop
-----------------------------------------------------------------------------------------------------------------------|
|
|
5 |
PARTITION RANGE SINGLE|
TABLE ACCESS FULL
|
1 |
39 |
|
2
(0)| 00:00:01 |
KEY |
1
| SYS_FBA_HIST_77429 |
1 |
39 |
|
2
(0)| 00:00:01 |
KEY |
1
|
|
|
| 89384 |
174M|
|*
|
6 |
|*
|
7 |
|*
|
8 |
|*
|
9 |
TABLE ACCESS FULL
| SYS_FBA_TCRV_77429 |
|* 10 |
|
TABLE ACCESS FULL
| MYSALES
FILTER
|
HASH JOIN RIGHT OUTER |
|
|
|
|
| 36333
(1)| 00:07:17 |
|
1 |
2028 |
|
2
(0)| 00:00:01 |
|
| 89384 |
1309K|
| 36331
(1)| 00:07:16 |
|
-----------------------------------------------------------------------------------------------------------------------AUSOUG National Conference Series 2009
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
Flashback Data Archive Data Dictionary Views
SQL> desc DBA_FLASHBACK_ARCHIVE_TS
Name
----------------------------------------FLASHBACK_ARCHIVE_NAME
FLASHBACK_ARCHIVE#
TABLESPACE_NAME
QUOTA_IN_MB
SQL> desc DBA_FLASHBACK_ARCHIVE
Name
----------------------------------------OWNER_NAME
FLASHBACK_ARCHIVE_NAME
FLASHBACK_ARCHIVE#
RETENTION_IN_DAYS
CREATE_TIME
LAST_PURGE_TIME
STATUS
SQL> desc DBA_FLASHBACK_ARCHIVE_TABLES
Name
----------------------------------------TABLE_NAME
OWNER_NAME
FLASHBACK_ARCHIVE_NAME
ARCHIVE_TABLE_NAME
STATUS
Null?
-------NOT NULL
NOT NULL
NOT NULL
Type
---------------------------VARCHAR2(255)
NUMBER
VARCHAR2(30)
VARCHAR2(40)
Null?
Type
-------- ---------------------------VARCHAR2(30)
NOT NULL VARCHAR2(255)
NOT NULL NUMBER
NOT NULL NUMBER
TIMESTAMP(9)
TIMESTAMP(9)
VARCHAR2(7)
Null?
-------NOT NULL
NOT NULL
NOT NULL
Type
---------------------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(255)
VARCHAR2(53)
VARCHAR2(8)
AUSOUG National Conference Series 2009
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
-------------------FBA1
FBA2
CREATED
RETENTION_IN_DAYS STATUS
----------- ----------------- ------02-nov-2009
7 DEFAULT
02-nov-2009
30
SQL> SELECT TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
2 FROM DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME
----------EMPSAL_HIS
MYSALES
FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
-------------------- -------------------FBA1
SYS_FBA_HIST_77419
FBA2
SYS_FBA_HIST_77429
STATUS
-----------ENABLED
ENABLED
AUSOUG National Conference Series 2009
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
-----------------------------DEPT
SYS_FBA_DDL_COLMAP_73201
SYS_FBA_TCRV_73201
SYS_FBA_HIST_73201
TABLESPACE_NAME
-----------------------------USERS
HIS_DATA_1
HIS_DATA_1
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
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
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
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
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
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
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
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
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
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
Thanks for attending!!
http://gavinsoorma.wordpress.com
Tel: 0417713124
[email protected]
QUESTIONS
ANSWERS
AUSOUG National Conference Series 2009