Transcript Introduction and Overview

Flashback Techniques for Oracle
Database 11g and The Next Generation
Carl Dudley
University of Wolverhampton, UK
UKOUG
Oracle ACE Director
[email protected]
Introduction
Working with Oracle since 1986
Oracle DBA - OCP Oracle7, 8, 9, 10
Oracle DBA of the Year – 2002
Oracle ACE Director
Regular Presenter at Oracle Conferences
Consultant and Trainer
Technical Editor for a number of Oracle texts
UK Oracle User Group Director
Member of IOUC
Day job – University of Wolverhampton, UK
2
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
3
Flashback Queries - SQL Level Flashback
 Principal uses of flashback query :
— Repair bad data
— Collect and review recent data values over time
 To observe flashback using SQL
— Requires FLASHBACK privilege on the table
SELECT * FROM department
AS OF TIMESTAMP TO_TIMESTAMP('03-MAR-2013 09:30:00');
– To observe new records added today
CREATE TABLE changes_today AS
SELECT * FROM employees
MINUS
SELECT * FROM employees AS OF TIMESTAMP TRUNC(SYSDATE);
4
Flashback Queries - Session Level Flashback
 To observe data from several queries at a point in time using PL/SQL
— Requires execute privilege on dbms_flashback
• Known as session level flashback
dbms_flashback.enable_at_time('22-NOV-2012 11:00:00AM');
SELECT ... FROM ... ;
SELECT ... FROM ... ; -- No DML or DDL allowed
SELECT ... FROM ... ;
dbms_flashback.disable;
— If only a date is specified, time element defaults to 12 midnight
 Useful when using 3rd party apps and you cannot touch the code
— Simply put the session back in time
5
Flashback Query Limitations
 Flashback is enabled at nearest SCN to the specified time rounded down to
a 3 second interval
— SCNs are mapped to times only once every 3 seconds
— Need to know SCNs to flashback more accurately
 Alterations to tables (and indexes) since flashback time will cause errors
— Avoid flashing back to a time close to a DDL statement
• System could happen to choose an SCN earlier than the DDL statement
 Correspondence of SCNs to timestamps are shown in rows generated at five
minute intervals in smon_scn_time
— But the RAW column, tim_scn_map, holds up to 100 mappings within its raw data
• This allows efficient fine-grained 3 second mapping
6
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
7
Flashback Row History
 Shows row version data plus :
— Start and end times of the version (SCNs and timestamps)
— Transaction ID for every version of the row during the specified period
SELECT empno,sal,versions_starttime st,versions_xid XID
FROM empf VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('01-MAR-13 05.24.02')
AND TO_TIMESTAMP('01-MAR-13 05.35.02');
WHERE empno = 7766;
— Returns the salary for each transaction affecting the row, as follows:
EMPNO
----7766
7766
7766
SAL
---8000
7120
6300
ST
-----------------01-MAR-13 05.32.38
01-MAR-13 05.28.45
01-MAR-13 05.26.14
XID
----------------06001F0014170000
04002C00151B0000
0200290017370000
— No need for audit tables?
— Does not show any inserts and deletes due to online shrink operations
8
Flashback Transaction History - Scenario
 Build a table with some data
CONNECT scott/tiger
CREATE TABLE empf(empno NUMBER PRIMARY KEY
,ename VARCHAR2(16)
,sal NUMBER);
Sleep for a short time
INSERT INTO empf VALUES(7950, 'BROWN', 3000);
INSERT INTO empf VALUES(8888, 'GREEN', 4000);
INSERT INTO empf VALUES(1111, 'WHITE', 1000);
COMMIT;
 Contents of table empf
EMPNO
----7950
8888
1111
ENAME
SAL
--------- ----BROWN
3000
GREEN
4000
WHITE
1000
9
Flashback Transaction History – Scenario (continued)
 Bad transaction correctly deletes a row, incorrectly updates the other
DELETE FROM empf WHERE empno = 7950;
UPDATE empf SET sal = sal + 3000 WHERE empno = 8888;
COMMIT;
EMPNO
----8888
1111
ENAME
SAL
-------- ----BROWN
6000
WHITE
1000
 New transaction updates a remaining row with new values
UPDATE empf SET sal = sal + 400 WHERE empno = 8888;
UPDATE empf SET sal = sal + 250 WHERE empno = 8888;
COMMIT;
EMPNO
----8888
1111
ENAME
SAL
-------- ----BROWN
6650
WHITE
1000
10
Finding the Errant Transaction
 The DBA decides that there has been an error and interrogates the versions of
the rows (row history) for transaction information
SELECT versions_xid XID
,versions_startscn START_SCN
,versions_endscn END_SCN
,versions_operation OPERATION
,ename
,sal
,empno
FROM empf VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
XID
START_SCN
END_SCN O ENAME
SAL EMPNO
---------------- --------- --------- - ------ ----- -----05002500BE000000
428380
U BROWN
6650 8888
05001800BE000000
428372
428380 U BROWN
6000 8888
05001800BE000000
428372
D GREEN
4000 7950
04001600BE000000
428365
I WHITE
1000 1111
04001600BE000000
428365
428372 I BROWN
3000 8888
04001600BE000000
428365
428372 I GREEN
4000 7950
— Top row is the final transaction, second row is the error
11
Auditing the Transaction
 Obtain the UNDO of the original SQL for the entire bad transaction
— Requires SELECT ANY TRANSACTION system privilege
SELECT xid, undo_sql
FROM flashback_transaction_query
WHERE xid = '05001800BE000000';
XID
UNDO_SQL
---------------- ----------------------------------------05001800BE000000 update "SCOTT"."EMPF" set "SAL" = '3000'
where ROWID = 'AAAMUDAAEAAAAIXAAB';
05001800BE000000 insert into
"SCOTT"."EMPF"("EMPNO","ENAME","SAL")
values ('7950','GREEN','4000');
 Decide how to use this information to repair the data
12
Performance of flashback_transaction_query
 Searches on flashback_transaction_query can take some time
— The xid column is indexed but it is RAW(8)
• To use the index, specify HEXTORAW(‘<your_transaction_id>’) in the
WHERE clause
 Consider flashback_transaction_query with approximately 40,000 rows
SELECT xid, undo_sql
FROM flashback_transaction_query
WHERE xid = '070017004A090000';
SELECT xid, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('070017004A090000');
15.51 secs
0.04 secs
13
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
14
Flashback Transaction (Transaction Backout)
 Rolls back a transaction and all or some of its dependent transactions
— Gives fine control over how and which transactions are backed out
 Dependent transactions are those that :
a. Have written to the same data after the target transaction
• Called Write After Write (WAW)
b. Re-insert a primary key value that was deleted by the target transaction
 Uses undo, redo and supplementing logging information
 Creates compensating transactions that can be executed
— Controlled by user with commit, rollback statements
15
Flashback Transaction Requirements
 Database must be in ARCHIVELOG mode and COMPATIBLE >= 11.1.0.0
 Requires additional redo logging
1. Minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
2. Logging of primary key values for any row that is changed
ALTER DATABASE
ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Must start work using archived logs – hence at least one log has to be archived
since start of operation
— Force an archive :
ALTER SYSTEM ARCHIVE LOG CURRENT;
•
Flashback makes use of LogMiner
 User requires privileges
— SELECT ANY TRANSACTION
— EXECUTE on dbms_flashback
16
dbms_flashback.transaction_backout
 Can back out a set of transactions
— Analyzes dependencies
— Performs the DML to backout the transactions
— Produces dictionary information
• dba_flashback_txn_state
– Shows whether a transaction is active or backed out
• dba_flashback_txn_report
– Detailed report of backed out transactions
 Example syntax :
dbms_flashback.transaction_backout (
numberofxids
NUMBER
xids
XID_ARRAY
options
NUMBER DEFAULT NOCASCADE
timehint
TIMESTAMP DEFAULT MINTIME)
— options can take values 1,2,3,4
17
Flashback Transaction Controls
 Can dictate cascading effects on dependent transactions via the enumerated
type options
Backout mode (options)
Number value
Result
NOCASCADE
1
Error generated if dependencies exist
NOCASCADE_FORCE
2
The specified transactions are entirely backed
out without handling any dependencies
NOCONFLICT_ONLY
3
Only changes having no dependencies are
backed out
CASCADE
4
All changes made by the transaction(s) and all
its dependents are completely backed out
 The routine performs the necessary DML and holds locks on the data items
— Does not commit – left as a decision for the user
 Can be traced using
EVENTS = "55500 TRACE NAME CONTEXT FOREVER, LEVEL 2"
18
Collecting Information
 Situation after committing the results of the CASCADE option
SELECT * FROM dba_flashback_txn_report;
COMPENSATING_XID
COMPENSATING_TXN_NAME
COMMIT_TIME
XID_REPORT
USERNAME
:
:
:
:
0E000700F2020000
_SYS_COMP_TXN_8388645_TIM_1235938019
01-MAR-13
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="0E000700F20200
: SYS
SELECT * FROM dba_flashback_txn_state;
COMPENSATING_XID
---------------0E000700F2020000
0E000700F2020000
XID
DEPENDENT_XID
---------------- ---------------140011008D010000
0D001A0089020000 140011008D010000
BACKOUT_MODE
-----------CASCADE
CASCADE
USERNAME
-------SYS
SYS
19
Backing out Transactions Example
DECLARE
v_xids sys.XID_ARRAY := sys.xid_array();
BEGIN
The bad transaction id
v_xids.extend;
v_xids(1) := HEXTORAW('05001800BE000000');
sys.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns => 1,
xids => v_xids, options => 4,scnhint => 0);
END;
/
 Populates the transaction array (v_xids) with just one transaction id
— Array is passed to the transaction_backout procedure
 options parameter set to 4 (CASCADE), causes backout of any dependent
transactions (WAW)
 The scnhint is an alternative to timehint
— Default is related to retention period in undo tablespace
20
v$flashback_txn_mods
EXEC scott.pt('SELECT * FROM v$flashback_txn_mods')
COMPENSATING_XID
COMPENSATING_TXN_NAME
XID
TXN_NAME
PARENT_XID
INTERESTING
ORIGINAL
BACKOUT_SEQ
UNDO_SQL
:
:
:
:
:
:
:
:
:
UNDO_SQL_SQN
UNDO_SQL_SUB_SQN
BACKOUT_SQL_ID
OPERATION
BACKEDOUT
CONFLICT_MOD
MODS_PER_LCR
----------------COMPENSATING_XID
COMPENSATING_TXN_NAME
XID
TXN_NAME
PARENT_XID
INTERESTING
:
:
:
:
:
:
:
:
:
:
:
:
:
:
080008009A210000
_SYS_COMP_TXN_1576008_TIM_1312991793
060010002F210000
060010002F210000
1
1
2
update "FRED"."STOCK" set "NAME" = 'IBM', "CTIME" =
TO_TIMESTAMP('10-AUG-11 16.48.40.781000') where "ST_ID"
= '2' and "NAME" = 'HP' and "PRICE" = '11' and "CTIME"
= TO_TIMESTAMP('10-AUG-1116.49.10.859000') and ROWID
= 'AAAUe2AAEAAAbVEAAB'
1
1
2
UPDATE
1
0
1
080008009A210000
_SYS_COMP_TXN_1576008_TIM_1312991793
060010002F210000
060010002F210000
1
:
21
v$flashback_txn_graph
EXEC scott.pt('SELECT * FROM v$flashback_txn_graph')
COMPENSATING_XID
COMPENSATING_TXN_NAME
XID
TXN_NAME
PARENT_XID
INTERESTING
ORIGINAL
BACKOUT_SEQ
NUM_PREDS
NUM_SUCCS
DEP_XID
DEP_TXN_NAME
TXN_CONF_SQL_ID
DEP_TXN_CONF_SQL_ID
CONFLICT_TYPE
----------------COMPENSATING_XID
COMPENSATING_TXN_NAME
XID
TXN_NAME
PARENT_XID
INTERESTING
ORIGINAL
BACKOUT_SEQ
NUM_PREDS
NUM_SUCCS
DEP_XID
DEP_TXN_NAME
TXN_CONF_SQL_ID
DEP_TXN_CONF_SQL_ID
CONFLICT_TYPE
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
080008009A210000
_SYS_COMP_TXN_1576008_TIM_1312991793
060010002F210000
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
080008009A210000
_SYS_COMP_TXN_1576008_TIM_1312991793
08001E0099210000
060010002F210000
1
1
2
0
1
08001E0099210000
1
3
WRITE AFTER WRITE
08001E0099210000
1
0
1
1
0
0000000000000000
0
0
22
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
23
Flashback Table Functionality
 Enables fast recovery of a table to a previous point in time (SCN or timestamp)
— Table has an exclusive DML lock while it is being restored
 Requirements for use
— FLASHBACK ANY TABLE system privilege or FLASHBACK object privilege on table
— SELECT, INSERT, UPDATE, DELETE privileges on the table
— Table must have row movement enabled (ROWIDs are not preserved)
 Automatically restores all dependent objects
— Indexes dropped since flashback point will be synchronized with flashback table
— Indexes created after the flashback point will be dropped
— Statistics are not flashed back
 Works only if constraints are not violated
— Referential integrity constraints are maintained across all tables
• A violation causes a rollback of the flashback statement
 Data in the original table is not lost after a flashback
— You can later revert to the original state
24
Using Flashback Table
1. Use flashback row and transaction history to find position for flashback
— Record current SCN at time of flashback if a reversal of the operation is required
• Found in v$database.current_scn or via
dbms_flashback.get_system_change_number
2. Perform the flashback table operation
FLASHBACK TABLE t1 TO SCN 12345;
FLASHBACK TABLE t1
TO TIMESTAMP TO_TIMESTAMP('2013-03-01 12:05:00')
ENABLE TRIGGERS;
— Triggers are not enabled by default during a flashback operation
25
Global Temporary Table
 On executing FLASHBACK TABLE, a global temporary table called sys_temp_fbt
is created in the user schema
— Rows are inserted using INSERT APPEND
• Rows are removed when session is terminated but the table is not dropped
— The sys_temp_fbt tracks ROWIDs of affected rows
Name
-------------SCHEMA
OBJECT_NAME
OBJECT#
RID
ACTION
Type
-----------VARCHAR2(32)
VARCHAR2(32)
NUMBER
ROWID
CHAR(1)
26
Use of Temporary Table - Scenario
 Update of one row generates 2 entries for each row in same block
 Deletes and inserts generate one entry for each row that is changed
— 2 entries for other rows in the same blocks
 Scenario
1 Create a table (ef) containing 14336 rows (143 rows per block)
2 Enable row movement
ALTER TABLE ef ENABLE ROW MOVEMENT;
3 Find current timestamp
SELECT systimestamp FROM DUAL;
4 Update a single row
UPDATE ef SET deptno = 99 WHERE ROWNUM = 1;
5 Perform a flashback
FLASHBACK TABLE ef
TO TIMESTAMP TO_TIMESTAMP('<time_stamp>');
27
Use of Temporary Table – Scenario (continued)
 Update of one row causes 286 entries (2*143) in the temp table
SELECT rid,action
FROM sys_temp_fbt;
RID
-----------------AAANAIAAEAAAAJMAAA
AAANAIAAEAAAAJMAAB
AAANAIAAEAAAAJMAAC
AAANAIAAEAAAAJMAAA
AAANAIAAEAAAAJMAAB
AAANAIAAEAAAAJMAAC
ACTION
-----D
D
D
I
I
I
SELECT ROWID,deptno
FROM ef;
ROWID
EMPNO
------------------ ----AAANAIAAEAAAAJMAAE 7369
AAANAIAAEAAAAJMAAF 7900
AAANAIAAEAAAAJMAAG 7654
Example scenario
shows only a
subset of rows in
affected block
— Changes ROWIDs of all rows in the updated blocks in the ef table
UPDATE ef SET deptno = 99 WHERE ROWNUM < 145;
Generates 572 (2*286) rows in the temporary table
UPDATE ef SET deptno = 99 WHERE empno = 7369;
(7369 is present every 14 rows)
Generates 28672 rows in the temporary table
28
Flashback Table Restrictions
 Cannot flashback a table :
— Past a DDL operation
— Owned by sys
— When in the middle of a transaction
— If undo information is not available
 Flashback operation cannot be rolled back
— But another flashback command can be issued to move forward in time
29
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
30
Flashback Drop and the Recycle Bin
 Dropping a table places the table in the recycle bin
— Makes it possible to recover a table at a later time using
 Does not protect against an erroneous TRUNCATE
 Different to flashback table as it does not use rollback data
 Dropped objects continue to count against user quotas until purged
 Oracle will purge dropped tables in preference to extending autoextensible files
— Dependent objects (indexes) are purged before tables
— Purging is performed on a first-in, first-out basis
 Dropping a tablespace, or a user does not place any objects in the bin
— Purges the bin of any objects belonging to that tablespace or user
31
Renaming Tables in the Recycle Bin
 The renaming avoids name clashes across different users
— Prevents clashes if tables are dropped, rebuilt with same name and dropped again
— Example name - BIN$xyWe0+q+SniItJ0pn/u54A==$0
 Indexes, constraints and triggers of dropped tables are also held in recycle bin
 Some classes of dependent objects are not protected
— Bitmap join indexes
— Materialized view logs
— Referential integrity constraints
 Data can be queried in 'binned' tables as follows
SELECT * FROM “BIN$xyWe0+q+SniItJ0pn/u54A==$0” [AS OF ...];
— Cannot perform DML/DDL on recycle bin objects
32
Purging Objects in the Recycle Bin
 Object(s) can be purged from the recycle bin
PURGE TABLE “BIN$xyWe0+q+SniItJ0pn/u54A==$0”;
PURGE TABLE emp;
purges specified version
of a dropped table
purges earliest dropped version of emp
PURGE RECYCLEBIN;
PURGE TABLESPACE user1;
PURGE TABLESPACE user1 USER fred;
PURGE INDEX "BIN$FTX34MN88J7==$0”;
 Users with SYSDBA privilege, can purge the entire recycle bin
PURGE DBA_RECYCLEBIN;
33
Restoring Objects in the Recycle Bin
 Flashback drop will restore the most recent version of a table from the recycle bin
FLASHBACK TABLE emp TO BEFORE DROP;
 A specific version can also be restored
— If same table is dropped more than once
• Each dropped version is given a different ‘bin’ name
FLASHBACK TABLE “BIN$xyWe0+q+SniItJ0pn/u54A==$ TO BEFORE DROP;
 Table reverts to its original name in both cases
— Dependent objects are recovered (except referential constraints)
— Indexes and constraints keep their 'binned' name
 Use RENAME to avoid name clashes with new objects of the same name
FLASHBACK TABLE emp TO BEFORE DROP RENAME TO employees;
34
Managing the Recycle Bin
 Dropped objects in the recyclebin continue to count against user quota
 Objects in the recycle bin can prevent the shrinking of datafiles
 Recyclebin feature can be turned off
ALTER SESSION SET recyclebin = OFF SCOPE = SPFILE;
— Any object present in the recyclebin will remain when the parameter is set to OFF
— Can be ON | OFF DEFERRED
• Affects all new sessions and is effectively dynamic
— Parameter can be set at session level
 After performing flashback to before drop :
— Rebuild foreign key constraints
— Rename or drop the indexes
— Rename or drop the triggers
— Recompile triggers
35
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
36
Flashback Database
 Fast (point in time) recovery from ‘recent’ logical errors
— Effectively a rollback of the database
— An alternative to normal recovery mechanisms
 Database must be in ARCHIVELOG mode
FLASHBACK DATABASE TO TIMESTAMP ...
FLASHBACK DATABASE TO BEFORE TIMESTAMP ...
FLASHBACK DATABASE TO BEFORE SCN ...
FLASHBACK DATABASE TO SCN ...
— Needs SYSDBA privilege
37
Flashback Logging
Database
LGWR
Archived logs
RVWR
Flashback logs
 Recommended to store both flashback and archived logs in a common
recovery area
— NOLOGGING operations are recorded in the flashback logs
RVWR = Recovery Writer
38
Flashback Structures
SGA
RVWR
Selective
block
logging
Flashback
Buffer
(default size ~ 16M)
All changes
LGWR
Buffer cache
Redo log
buffer
Before images (blocks)
logged periodically
Flashback logs
Redo logs
 Flashback buffer size appears to be based on 2*LOG_BUFFER
39
Setting up Flashback Database
1. Configure the recovery area
DB_RECOVERY_FILE_DEST_SIZE
— Default size is 2GB
DB_FLASHBACK_RETENTION_TARGET (default 1440 minutes)
— Oracle will try to keep enough flashback information to rollback through 1440
minutes
2. Find the current SCN of the database in case you need to perform a
subsequent ‘flashback’ to the current state
3. ALTER DATABASE FLASHBACK ON;
SELECT current_scn FROM v$database;
40
Flashback Mechanism
 Flashback information ‘jumps’ the database back in time
 Archive log data is then applied to perform a point in time recovery
 Example : FLASHBACK DATABASE TO SCN 53297
Flashback
log 20
50614
55617
Flashback
log 21
62983
Flashback
log 22
Flashback
log 23
67234
Database
SCN 69633
50617
53297
Archive stream
41
Fast Recovery Area Storage
 FRA can hold flashback logs, archive logs, backupsets and datafile copies
— Alert is raised when Recovery Area is 85% full
— Obsolete backups or flashback logs are deleted when 100% full
 Recovery Manager (RMAN) automatically deletes flashback logs in preference to
other files needed for backup purposes
— Invades DB_FLASHBACK_RETENTION_TARGET
— Automatically removes ‘corresponding’ flashback logs when archivelogs are purged
— Recommended not to use FRA in a non-RMAN environment
 Inspect v$recovery_area_usage
FILE_TYPE
PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------- ------------------ ------------------------- --------------CONTROLFILE
0
0
0
ONLINELOG
2
0
22
ARCHIVELOG
4.05
2.01
31
BACKUPPIECE
3.94
3.86
8
IMAGECOPY
15.64
10.43
66
FLASHBACKLOG
.08
0
1
42
Flashback Generation
 Oracle places flashback markers in the flashback stream
 An individual block is logged only once in between the markers
— Independent of how many changes or transactions affect the block
— Logged once every 30 minutes?
 Scenario
— emp table of following structure containing 330688 rows
• Table stored in 8K blocks each containing around 152 rows
• Rows stored in ascending seqid order
SEQID
NUMBER(6)
PRIMARY KEY
EMPNO
NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
43
Flashback Generation (continued)
 Change the same row/block 2175 times
— All updates localised on one block
BEGIN
FOR i IN 1..2175 LOOP
UPDATE emp SET sal = sal+1 WHERE seqid = 40000;
COMMIT;
END LOOP;
END;
/
No flashback logs generated
Elapsed: 00.98 secs
Without flashback  00.35 secs
44
Flashback Generation (continued)
 Same number of transactions (updates), but approximately one row in each block
updated
— 2175 different blocks affected
BEGIN
FOR i IN 1..2175 LOOP
UPDATE emp SET sal = sal+1 WHERE seqid = i*152;
COMMIT;
END LOOP;
END;
/
2 flashback logs generated, each of 8mb (approx)
Elapsed: 9.95 secs
Repeat runs do not generate flashback logs  5.93 secs
Without flashback  5.01 secs
45
Useful Views
 Use v$flashback_database_log to show
— Available recovery window
— Actual size of flashback data
— An estimate of amount of flashback needed to support retention target
 v$recovery_file_dest
— Shows total space usage in recovery area
SELECT name
,space_limit
,space_used
,reclaimable_space reclaim
,number_of_files files
FROM v$recovery_file_dest;
NAME
SPACE_LIMIT SPACE_USED RECLAIM FILES
----------------------------- ----------- ---------- ------- ----D:\oracle\flash_recovery_area 2147483648 364353536
0
86
46
Useful Views (continued)
 v$flashback_database_stat
— Shows write activity at hourly intervals
SELECT TO_CHAR(begin_time,'ddth hh24:mi') start_time
,TO_CHAR(end_time,'ddth hh24:mi') end_time
,db_data
,redo_data
,flashback_data fl_data
,estimated_flashback_size est_fl_size
FROM v$flashback_database_stat;
START_TIME
---------15th 15:59
15th 14:59
15th 13:59
15th 12:59
15th 11:52
END_TIME
DB_DATA REDO_DATA
FL_DATA EST_FL_SIZE
---------- ---------- ---------- ---------- ----------15th 16:48
20234240
9678336
12361728
0
15th 15:59
19652608
7720960
10272768
398499840
15th 14:59
21643264
8264704
12541952
447406080
15th 13:59
20897792
7571968
12435456
516833280
15th 12:59
46702592
32384000
33333248
712679424
— estimated_flashback_size is the value found in
v$flashback_database_log at the end of the time interval
47
Flashback Database Features
 Cannot recover from media failure such as loss of a datafile
 Cannot flashback past a shrink datafile
— But can handle datafile automatic expansion
 Can also be used in a Data Guard environment
— Used with snapshot standby databases
 Flashback data requires a lot of space
— On Windows, logs are ~ 8MB on a ‘quiet’ system with names like
O1_MF_0B87CPH6_.FLB
— Any change within a block means the whole block is logged
 Volume of flashback log generation is ~
~ volume of redo log generation
— If DB_FLASHBACK_RETENTION_TARGET is 24 hours, and 20 GB of redo is
generated in a day, then allow 20 GB to 30 GB disk space for the flashback logs
48
Repeating Flashbacks
 Flashback must be performed in a mount state and requires an :
ALTER DATABASE OPEN RESETLOGS;
— Deletes flashback logs
 What if you are unsure that your flashback is to the correct point in time
1 Open the database in READ ONLY mode to observe the data
ALTER DATABASE OPEN READ ONLY;
2. Shutdown
3 Mount the database
4 Flashback to a different point
• The new point in time can be in advance or behind the first flashback
• To move forward a conventional recovery must now be performed
– This allows flashing back to the original ‘current’ state
49
Restore Points
 Named markers for FLASHBACK DATABASE
— Avoids need for SCNs or timestamps
 Position before potentially risky operations that could compromise the database
 Can be normal or guaranteed
CREATE RESTORE POINT before_upgrade
[GUARANTEE FLASHBACK DATABASE];
 Normal restores require FLASHBACK mode
 Guaranteed restores can be used when database not in FLASHBACK mode
— Could lead to less logging—changed blocks logged once only
• BUT flashback logs are still produced and forcibly retained
• Database hangs if FRA not big enough to support guaranteed restore point
— Less performance impact and strain on recovery area?
50
Using Restore Points
FLASHBACK DATABASE TO RESTORE POINT before_major_change;
FLASHBACK TABLE TO RESTORE POINT before_major_change;
RECOVER DATABASE TO RESTORE POINT before_major_change;
 Removing restore points
DROP RESTORE POINT before_major_change;
— Normal restore points age out of control file
• Control file keeps maximum of 2048
• Guaranteed restore points are never removed
 Cannot take database out of archivelog mode if a guaranteed restore point is
present
— Database will not start if guaranteed restore point is active and no space in FRA
• Check flashback_on in v$database for a value of ‘RESTORE POINT ONLY’
51
Using Restore Points
(continued)
 FRA must be configured
 Restore points can be created in an open state (11gR2)
— Require archivelog mode
 If guaranteed restore points defined, instance crashes when RVWR gets I/O errors
 Observe in v$restore_point
SELECT name, scn, time, database_incarnation#,
guarantee_flashback_database, storage_size
FROM v$restore_point;
 For normal restore points, storage_size is zero
 For guaranteed restore points, storage_size is the space for logs required to
guarantee FLASHBACK DATABASE to the restore point
52
Restore Point Options
 With flashback database disabled, can restore only to a guaranteed restore point
— If enabled, can restore to any point in time (RESTORE or otherwise)
Guaranteed restore point
(Only this version will be logged
when not in flashback mode)
block 34
block 34
block 34
~ ~ ~
~ ~~ ~~ ~
~ ~~~~~~~ ~
~ ~~~~~~~ ~
~ ~~ ~~ ~
~ ~ ~
~ ~ ~
~ ~~ ~~ ~
~ ~~~~~~~ ~
~ ~~~~~~~ ~
~ ~~ ~~ ~
~ ~ ~
~ ~ ~
~ ~~ ~~ ~
~ ~~~~~~~ ~
~ ~~~~~~~ ~
~ ~~ ~~ ~
~ ~ ~
flashback log1
flashback log2
Restore point 1
Current version
Restore point 2
flashback log3
53
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
54
Flashback Data Archive
 Transparently tracks historical changes to data in selected tables
— Secure - no possibility to modify historical data
 Retained according to a time specification
— Automatically purged based on retention policy
• Independent of system level undo
 Minimize performance impact of capturing historical data
— Faster than routines based on triggers?
 Different mechanism and characteristics to Warehouse Builder facility
Now part of Enterprise and Standard Edition
— No licence needed except when compression is used (EE only)
55
Setting up Flashback Archives
 FlashBack Data Archive (FBDA) process takes read consistent data from buffer
cache and/or undo tablespace into the archive
 Archives are designed to hold data for a long time (e.g. 1,3,5 years)
 One archive can have many tablespaces
— Can be seen as sum of quotas on the allocated tablespaces
— Tablespaces can be added and removed
 Archives managed by account with FLASHBACK ARCHIVE ADMINISTER privilege
 Archive users need FLASHBACK ARCHIVE object privilege on the flashback
archive object to allow history tracking for specific tables
— Documented in sys_fba_users
56
Flashback Data Archive Mechanism
EMPNO
ENAME
SAL
7369
ADAMS
2000
7788
COX
2500
7900
MILLS
800
7902
WALL
1500 9999
7934
GOLD
3500
UNDO
tablespace
Original (undo) data
in buffer cache
DML operations
Flashback data archives
stored in tablespaces
tail
1500
Active data
head
FBDA
Inactive data
1 yr retention
2 yr retention
5 yr retention
57
Creating a Flashback Data Archive
CREATE TABLESPACE tbs_fba1
DATAFILE 'c:\oracle\oradata\orcl\tbs_fba1_f1.dbf' SIZE 10G
SEGMENT SPACE MANAGEMENT AUTO;
 Create the flashback data archive in ASSM (mandatory) tablespace(s)
CREATE FLASHBACK ARCHIVE fba1 TABLESPACE tbs_fba1
QUOTA 5G RETENTION 1 MONTH;
 Allow a user to track changes to his tables in the fba1 archive
GRANT FLASHBACK ARCHIVE ON fba1 TO fred;
58
Creating a Flashback Data Archive
 Enable history tracking for a table in the fba1 archive
CONN fred/fred
CREATE TABLE emparch (empno
NUMBER(4)
,ename
VARCHAR2(12)
,sal
NUMBER(7,2)
,comm
NUMBER(7,2))
FLASHBACK ARCHIVE fba1;
or
ALTER TABLE emp FLASHBACK ARCHIVE fba1;
 View the historical data
SELECT empno, ename, sal
FROM emp AS OF TIMESTAMP TO_TIMESTAMP
('2013-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT empno, ename, sal
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '20' DAY);
59
FDA Dictionary Information
SELECT * FROM dba_flashback_archive;
FLASHBACK_
FLASHBACK_ RETENTION_ CREATE_TIME
LAST_PURGE_TIME
STATUS
ARCHIVE_NAME ARCHIVE#
IN_DAYS
------------ ---------- ---------- ---------------------------- ---------------------------- ------FBA1
1
30 13-MAR-13 14.24.28.000000000 12-APR-13 14.24.28.000000000
SELECT * FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ TABLESPACE_NAME QUOTA_IN_MB
ARCHIVE#
---------------------- ---------- --------------- ----------FBA1
1 TBS_FBA1
5000
SELECT * FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
---------- ---------- ---------------------- -----------------EMPARCH
SYS
FBA1
SYS_FBA_HIST_69882
Does not show in dba_tables until
actually used by fbda
60
Population of Flashback Archive Table
 No evidence of FDA data after inserting fourteen rows and committing
— Inserts are not captured
 After updating all 14 rows, committing and waiting for 5 minutes
— The history table is built, and rows appear in the history table
SELECT * FROM sys_fba_hist_69882;
RID
STARTSCN ENDSCN XID
O EMPNO ENAME
SAL COMM
------------------ -------- ------- ---------------- - ----- ------ ---- ---AAAR0nAABAAAVxiAAA 5432321 5432342 02001D00C3110000 I 7369 SMITH
800
AAAR0nAABAAAVxiAAB 5432321 5432342 02001D00C3110000 I 7499 ALLEN 1600 300
AAAR0nAABAAAVxiAAC 5432321 5432342 02001D00C3110000 I 7521 WARD
1250 500
AAAR0nAABAAAVxiAAD 5432321 5432342 02001D00C3110000 I 7566 JONES 2975
AAAR0nAABAAAVxiAAE 5432321 5432342 02001D00C3110000 I 7654 MARTIN 1250 1400
AAAR0nAABAAAVxiAAF 5432321 5432342 02001D00C3110000 I 7698 BLAKE 2850
AAAR0nAABAAAVxiAAG 5432321 5432342 02001D00C3110000 I 7782 CLARK 2450
AAAR0nAABAAAVxiAAH 5432321 5432342 02001D00C3110000 I 7788 SCOTT 3000
AAAR0nAABAAAVxiAAI 5432321 5432342 02001D00C3110000 I 7839 KING
5000
AAAR0nAABAAAVxiAAJ 5432321 5432342 02001D00C3110000 I 7844 TURNER 1500
0
AAAR0nAABAAAVxiAAK 5432321 5432342 02001D00C3110000 I 7876 ADAMS 1100
AAAR0nAABAAAVxiAAL 5432321 5432342 02001D00C3110000 I 7900 JAMES
950
AAAR0nAABAAAVxiAAM 5432321 5432342 02001D00C3110000 I 7902 FORD
3000
AAAR0nAABAAAVxiAAN 5432321 5432342 02001D00C3110000 I 7934 MILLER 1300
ROWIDs of rows in base table
NULL if operation is
direct path (e.g CTAS)
61
Dictionary Details
 Some of the flashback objects are temporary tables
SELECT owner, tablespace_name, table_name, temporary
FROM dba_tables WHERE table_name LIKE '%FBA%';
OWNER
----FRED
FRED
FRED
FRED
FRED
FRED
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
TABLESPACE_NAME
--------------TBS_FBA2
TBS_FBA2
TBS_FBA2
TBS_FBA2
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
TABLE_NAME
-----------------------SYS_FBA_DDL_COLMAP_69878
SYS_FBA_TCRV_69878
SYS_FBA_DDL_COLMAP_69882
SYS_FBA_TCRV_69882
SYS_FBA_HIST_69882
SYS_FBA_HIST_69878
SYS_FBA_TSFA
SYS_FBA_FA
SYS_FBA_BARRIERSCN
SYS_FBA_TRACKEDTABLES
SYS_FBA_PARTITIONS
SYS_FBA_USERS
SYS_FBA_DL
SYS_FBA_COLS
SYS_FBA_CONTEXT
SYS_FBA_CONTEXT_AUD
SYS_FBA_CONTEXT_LIST
SYS_FBA_APP
SYS_FBA_APP_TABLES
SYS_MFBA_NHIST_69882
SYS_MFBA_NCHANGE
SYS_MFBA_NROW
SYS_MFBA_TRACKED_TXN
SYS_MFBA_STAGE_RID
SYS_MFBA_NTCRV
SYS_MFBA_NHIST_69878
TEMPORARY
--------N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
Y
Y
Y
Y
Y
Y
Y
No value for
tablespace_name
because they are
partitioned
62
Partitioning of Flashback Data Archive Tables
SELECT * FROM dba_part_tables WHERE table_name = 'SYS_FBA_HIST_69882'
OWNER
TABLE_NAME
PARTITIONING_TYPE
SUBPARTITIONING_TYPE
PARTITION_COUNT
DEF_SUBPARTITION_COUNT
PARTITIONING_KEY_COUNT
SUBPARTITIONING_KEY_COUNT
STATUS
DEF_TABLESPACE_NAME
DEF_PCT_FREE
DEF_PCT_USED
DEF_INI_TRANS
DEF_MAX_TRANS
DEF_INITIAL_EXTENT
DEF_NEXT_EXTENT
DEF_MIN_EXTENTS
DEF_MAX_EXTENTS
DEF_MAX_SIZE
DEF_PCT_INCREASE
DEF_FREELISTS
DEF_FREELIST_GROUPS
DEF_LOGGING
DEF_COMPRESSION
DEF_COMPRESS_FOR
DEF_BUFFER_POOL
DEF_FLASH_CACHE
DEF_CELL_FLASH_CACHE
REF_PTN_CONSTRAINT_NAME
INTERVAL
IS_NESTED
DEF_SEGMENT_CREATION
DEF_INDEXING
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
SYS
SYS_FBA_HIST_69882
RANGE
NONE
1
0
1
0
VALID
TBS_FBA1
Size could well be governed by
10
_flashback_archiver_partition_size
1
255
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
NONE
ENABLED
ADVANCED
DEFAULT
DEFAULT
DEFAULT
NO
NONE
ON
Partitions may be
compressed – only for OLTP
63
The First Two Partitions of the Flashback Archive Table
SELECT table_name, partition_name, high_value, high_value_length
,initial_extent, compression, compress_for
FROM dba_tab_partitions
WHERE table_name = 'SYS_FBA_HIST_69882'
TABLE_NAME
PARTITION_NAME
HIGH_VALUE
HIGH_VALUE_LENGTH
INITIAL_EXTENT
COMPRESSION
COMPRESS_FOR
:
:
:
:
:
:
:
SYS_FBA_HIST_69882
PART_8290898
SCN value used as partition key
8290898
7
8388608
Note first extent is 8M and is not deferred
ENABLED
ADVANCED
TABLE_NAME
PARTITION_NAME
HIGH_VALUE
HIGH_VALUE_LENGTH
INITIAL_EXTENT
COMPRESSION
COMPRESS_FOR
:
:
:
:
:
:
:
SYS_FBA_HIST_69882
HIGH_PART
MAXVALUE
8
8388608
ENABLED
ADVANCED
64
Flashback Archive Query Trace
SELECT COUNT(*) FROM emparch
AS OF TIMESTAMP(systimestamp – INTERVAL '2' DAY);
COUNT(*)
-------100000
----------------------------------------------------------------| Id | Operation
| Name
| Rows |
----------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
|
1 | SORT AGGREGATE
|
|
1 |
|
2 |
VIEW
| could index columns
|
998 |
|
3 |
UNION-ALL
|
|
|
in this table
|* 4 |
FILTER
|
|
|
|
5 |
PARTITION RANGE SINGLE|
|
1 |
|* 6 |
TABLE ACCESS FULL
| SYS_FBA_HIST_69882 |
1 |
|* 7 |
FILTER
|
|
|
|* 8 |
HASH JOIN OUTER
|
|
997 |
|* 9 |
TABLE ACCESS FULL
| EMP_FLASH
| 1511 |
| 10 |
VIEW
|
|
204K|
|* 11 |
TABLE ACCESS FULL
| SYS_FBA_TCRV_69882 |
204K|
----------------------------------------------------------------This table is automatically indexed
65
Flashback Archive Query Performance
 emparch has 1,000,000 rows
 sys_fba_hist_69882 has 329,387 rows
 empno has high selectivity (10 rows with empno value of 50000)
SELECT COUNT(*) FROM emparch
AS OF TIMESTAMP(SYSTIMESTAMP – INTERVAL '60' DAY)
WHERE empno = 50000;
Elapsed time : 7.06secs
CREATE INDEX emparch$empno ON sys_fba_hist_69882(empno);
Elapsed time : 4.89secs
66
Column Mapping and DDL Restrictions
 From 11g Release 2 you can perform DDL on the source table
— Add, drop, rename, modify a column
— Drop or truncate a partition
— Rename, truncate table (but not drop)
— Add, drop, rename, modify a constraint
ALTER TABLE emparch DROP COLUMN job;
ALTER TABLE emparch RENAME COLUMN salary TO sal;
SELECT * FROM sys_fba_ddl_colmap_69882;
STARTSCN ENDSCN
XID
OPERATION COLUMN_NAME
TYPE
HISTORICAL_
COLUMN_NAME
-------- -------- --------- --------- -------------- ------------ ---------5424699
EMPNO
NUMBER(4)
EMPNO
5424699
NAME
VARCHAR2(12) ENAME
5424699 22415904
SAL
NUMBER(7,2) SALARY
5424699
COMM
NUMBER(7,2) COMM
5474905 20775346
D_20775346_JOB VARCHAR2(15) JOB
67
Tips for Using Flashback Data Archive
 COMMIT or ROLLBACK before querying past data
 Use System Change Number (SCN) where precision is needed
— Timestamps have a three-second granularity
— Obtain SCN with DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
 Disabling archiving for a specific table requires the FLASHBACK ARCHIVE
ADMINISTER privilege – ‘normal’ users are not allowed to disable the archiving
— Drops the history table
ALTER TABLE scott.emp NO FLASHBACK ARCHIVE;
 Tables that are being archived cannot be dropped
— Archiving must first be disabled
 Tablespaces that contain archive-enabled tables cannot be dropped
 Total recall data is not exported
— Lost when flashback data archiving is turned off
• Save into another table
68
Flashback Data Archiving versus Oracle11g Auditing
 Auditing needs the audit_trail parameter to be set to DB or DB,EXTENDED
 Auditing uses autonomous transactions, which has some performance overhead
— FDA written in background by FBDA process – gives less impact on performance
 Audit trails are handled manually
— Flashback Data Archives can be automatically purged
ALTER FLASHBACK ARCHIVE fba1 MODIFY RETENTION 6 MONTH;
— Or handled manually
ALTER FLASHBACK ARCHIVE fba1
PURGE BEFORE TIMESTAMP(SYSTIMESTAMP – INTERVAL '1' DAY);
69
Flashback Data Archiving versus Database Triggers
 Database triggers can populate an audit table
— Implies a performance overhead
CREATE TABLE emp_trig_aud
— The audit table must be managed
(empno
NUMBER(8),
ename
VARCHAR2(12),
sal
NUMBER(7,2),
comm
NUMBER(7,2),
rid
VARCHAR2(20),
curdate DATE,
username VARCHAR2(20));
CREATE OR REPLACE TRIGGER e_f_trig
AFTER UPDATE ON emp_trig
FOR EACH ROW
BEGIN
INSERT INTO emp_trig_aud
VALUES(:OLD.empno,:OLD.ename,:OLD.sal,:OLD.comm
,:OLD.rowid,sysdate,user);
END;
70
FDA versus Database Triggers Results
 Three tables with same data (114688 rows)
1. emp_none – no auditing
2. emp_fda - FDA
3. emp_trig – Trigger
EMPNO
----7654
7698
:
ENAME SAL COMM
----- ---- ---aaaa 1250 1400
aaaa 2850
:
:
:
 Update all records and then commit on all three tables
UPDATE <<table_name>> SET ename = 'bbbb';
COMMIT;
Table
emp_none
emp_fda
emp_trig
Update
2.45s
3.81s
9.70s
Commit
0.00s
0.18s
0.00s
71
12c New features : User-context tracking
 User context and other metadata can now be tracked - easier to identify a user
— Better alternative to trigger-based auditing
EXEC dbms_flashback_archive.set_context_level(level=> 'ALL');
— level can be ALL, TYPICAL, NONE
• Value can be seen in sys_fba_context_list
SELECT * FROM SYS.SYS_FBA_CONTEXT_LIST;
NAMESPACE
-----------------------------USERENV
USERENV
:
USERENV
USERENV
FBA_CONTEXT
PARAMETER
-----------------------ACTION
AUTHENTICATED_IDENTITY
:
SESSIONID
TERMINAL
ALL
72
New View – sys_fba_context_aud
EXEC PT ('SELECT * FROM sys.sys_fba_context_aud')
XID
: 1E00120059030000
ACTION
:
AUTHENTICATED_IDENTITY
: fred
CLIENT_IDENTIFIER
:
CLIENT_INFO
:
CURRENT_EDITION_NAME
: ORA$BASE
CURRENT_SCHEMA
: SYS
It appears that both ALL
CURRENT_USER
: SYS
generate this information
DATABASE_ROLE
: PRIMARY
DB_NAME
: orcl
GLOBAL_UID
:
HOST
: WORKGROUP\LT1
IDENTIFICATION_TYPE
: LOCAL
INSTANCE_NAME
: orcl
IP_ADDRESS
:
MODULE
: SQL*Plus
OS_USER
: LT1\Administrator
SERVER_HOST
: ltree1
SERVICE_NAME
: SYS$USERS
SESSION_EDITION_NAME
: ORA$BASE
SESSION_USER
: FRED
SESSION_USERID
: 120
SESSIONID
: 1509620
TERMINAL
: LT1
SPARE
:
----------------------------------------------------:
:
and TYPICAL
73
Correlating Context Information with History
SELECT sfba.authenticated_identity
,sfba.host
,sfba.module
,hist.empno
,hist.ename
FROM sys.sys_fba_context_aud sfba
,fred.sys_fba_hist_175827 hist
WHERE hist.xid = sfba.xid;
SESSION_USER
-----------FRED
FRED
FRED
HOST
------------WORKGROUP\LT1
WORKGROUP\LT1
WORKGROUP\LT1
MODULE
EMPNO ENAME
-------- ----- -----SQL*Plus 7934 MILLER
SQL*Plus 7902 FORD
SQL*Plus 7900 JAMES
 Context for a given transaction can also be obtained from :
dbms_flashback_archive.get_sys_context(v_xid,'USERENV','SESSION_USER');
74
More New Features
 Archive tables can now be selectively compressed
— Requires a licence for the Advanced Compression Option
— The compression is COMPRESS FOR OLTP (FOR ALL OPERATIONS)
— Default is not to compress (NO OPTIMIZE) allowing FDA for SE
CREATE FLASHBACK ARCHIVE FBA_OPT TABLESPACE FBA_OPT
RETENTION 12 MONTH OPTIMIZE DATA;
CREATE TABLE EMP_OPT AS SELECT * FROM EMP;
ALTER TABLE EMP_OPT FLASHBACK ARCHIVE FBA_OPT;
 Application support
— Sets of tables can be registered for archiving in an ‘Application’
75
Importing User-Generated Archive Data
 Extend mappings to the past (01-JAN-1988) to enable import of history
EXEC DBMS_FLASHBACK_ARCHIVE.extend_mappings();
 Create an empty temp_history table for previously user managed archive table
dbms_flashback_archive.create_temp_history_table('fred','emp')
 Populate temp table from audit table (must commit)
INSERT INTO temp_history SELECT * FROM emp_aud;
COMMIT;
Name
Type
STARTSCN
ENDSCN
XID
OPERATION
EMPNO
ENAME
SAL
COMM
NUMBER
NUMBER
RAW(8)
VARCHAR2(1)
NUMBER(4)
VARCHAR2(12)
NUMBER(7,2)
NUMBER(7,2)
 Import temp table data into FDA History table
----------- -----------RID
VARCHAR2(4000)
SELECT tname FROM tab;
dbms_flashback_archive.import_history('fred','emp')
TNAME
SELECT
tname FROM tab;
------------------------TNAMEEMP
------------------------EMP SYS_FBA_DDL_COLMAP_406248
SYS_FBA_HIST_406248
SYS_FBA_DDL_COLMAP_406248
SYS_FBA_TCRV_406248
SYS_FBA_HIST_406248
TEMP_HISTORY
SYS_FBA_TCRV_406248
76
Importing User-Generated Archive Data
 emp table audit data had been generated into emp_aud by this trigger
— emp_aud has same structure as history table
CREATE OR REPLACE TRIGGER emparch_trg
AFTER UPDATE OR DELETE ON fred.emparch
FOR EACH ROW
DECLARE
opn VARCHAR2(1);
BEGIN
IF UPDATING THEN opn := 'U';
ELSE opn := 'D';
END IF;
INSERT INTO fred.emparch_aud
(rid, startscn, endscn, xid, operation, empno, ename, sal, comm )
VALUES (:OLD.ROWID, NULL, NULL, NULL, opn, :OLD.empno,
:OLD.ename, :OLD.sal, :OLD.comm);
END;
/
77
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
78
Valid Time Support – Temporal Validity
 Allows tables to have one or more valid time dimensions
— Data is visible (‘valid’) depending on its time-based validity
— Determined by start and end dates or time stamps of a valid time period
 Examples include :
— Hiring and finishing dates of an employee
— Valid period for an insurance policy
— Date of change of address for a customer or client
 Temporal Validity is typically actioned with :
1. AS OF and VERSIONS BETWEEN
2. DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time
 Can be set to show all table data (the default) or valid data as of a specified time
— Flash forward to see future database state??
 Useful with Information Lifecycle Management (ILM)
79
Valid Time Dimensions
 Two date-time columns result from CREATE TABLE or ALTER TABLE
— Can be added explicitly or created automatically from PERIOD specification
 One NUMBER column is also created with same name as specified PERIOD
CREATE TABLE emp_vt (
empno NUMBER(6) NOT NULL
,ename VARCHAR2(20),
PERIOD FOR emp_vt_time);
SELECT column_name
,data_type
user_tab_columns does
,column_id AS col_id
,segment_column_id AS seg_col_id
not show hidden columns
,internal_column_id AS int_col_id
,hidden_column
,virtual_column
FROM user_tab_cols WHERE table_name = 'EMP_VT';
COLUMN_NAME
----------------EMP_VT_TIME_START
EMP_VT_TIME_END
EMP_VT_TIME
EMPNO
ENAME
DATA_TYPE
COL_ID SEG_COL_ID INT_COL_ID HID VIR
--------------------------- ------ ---------- ---------- --- --TIMESTAMP(6) WITH TIME ZONE
1
1 YES NO
TIMESTAMP(6) WITH TIME ZONE
2
2 YES NO
NUMBER
3 YES YES
NUMBER
1
3
4 NO NO
VARCHAR2
2
4
5 NO NO
80
Using Valid Times – SQL level control
INSERT INTO emp_vt(empno,ename,emp_vt_time_start,emp_vt_time_end)
VALUES (1023
,'COX'
,'01-APR-2013 12.00.01 PM GMT'
Valid from 1st to 5th April
,'05-APR-2013 12.00.01 PM GMT');
INSERT INTO emp_vt(empno,ename,emp_vt_time_start,emp_vt_time_end)
VALUES (1024
,'ADAMS'
,'06-APR-2013 12.00.01 PM GMT'
,'05-APR-2014 12.00.01 PM GMT');
SELECT * FROM emp_vt;
EMPNO
----1023
1024
ENAME
--------------COX
ADAMS
SELECT * FROM emp_vt AS OF PERIOD FOR
emp_vt_time '03-APR-2013 12.00.00 PM';
EMPNO ENAME
----- -------------------1023 COX
SELECT * FROM emp_vt
WHERE emp_vt_time_start > '03-APR-2013 12.00.00 PM';
EMPNO ENAME
----- --------------------1024 ADAMS
81
Displaying Valid Times
 Must explicitly select hidden columns
— Cannot be combined with ‘*’
SELECT empno
,ename
,emp_vt_time_start
,emp_vt_time_end
,emp_vt_time
FROM emp_vt;
EMPNO
----1023
1024
ENAME
----COX
ADAMS
EMP_VT_TIME_START
-------------------------------01-APR-13 12.00.01.000000 PM GMT
06-APR-13 12.00.01.000000 PM GMT
EMP_VT_TIME_END
EMP_VT_TIME
-------------------------------- ----------05-APR-13 12.00.01.000000 PM GMT
7339307
05-APR-14 12.00.01.000000 PM GMT
7339307
— Additional periods each require three extra columns
 Visibility of rows can also be controlled with ARCHIVAL VISIBILITY
— Uses another hidden column ORA_ARCHIVE_STATE
82
Session Level Control of Visibility
 Visibility only of data valid at a point in time
Row must be valid for all PERIODs in use
EXEC—DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF','01-JAN-02
01.01.01.000000')
EXEC
dbms_flashback_archive.enable_at_valid_time
UPDATE
emp_vt2
SET vt_a_start = '01-JAN-02 01.01.01.000000 AM' WHERE empno = 1;
('ASOF', '01-APR-13 12.00.01 PM')
ORA-00904: "VT_A_START": invalid identifier
EXEC
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL')
 Visibility
only of currently valid data
UPDATE
emp_vt2
SET vt_a_start = '01-JAN-02 01.01.01.000000 AM' WHERE empno = 1;
EXEC
dbms_flashback_archive.enable_at_valid_time('CURRENT')
1 row updated.
 Full data visibility (default )
EXEC dbms_flashback_archive.enable_at_valid_time('ALL')
Must be UPPERCASE
83
Constraints for Valid Tiimes
OWNER
CONSTRAINT_NAME
CONSTRAINT_TYPE
TABLE_NAME
SEARCH_CONDITION
SEARCH_CONDITION_VC
R_OWNER
R_CONSTRAINT_NAME
DELETE_RULE
STATUS
DEFERRABLE
DEFERRED
VALIDATED
GENERATED
BAD
RELY
LAST_CHANGE
INDEX_OWNER
INDEX_NAME
INVALID
VIEW_RELATED
ORIGIN_CON_ID
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
FRED
EMP_VT_TIMEA67E83
C
EMP_VT
EMP_VT_TIME_START < EMP_VT_TIME_END
EMP_VT_TIME_START < EMP_VT_TIME_END
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
USER NAME
16-sep-2013 23:18:34
A null value appears to
get past the constraint
0
84
Filters based on Valid Times
 Two valid time periods placed on table data
— Plan shows combined constraint
filter(("T"."VT_A_START" IS NULL OR
SYS_EXTRACT_UTC("T"."VT_A_START")<=SYS_EXTRACT_UTC(TIMESTAMP'
2010-01-01 00:00:00.000000000')) AND ("T"."VT_A_END" IS NULL OR
SYS_EXTRACT_UTC("T"."VT_A_END")>SYS_EXTRACT_UTC(TIMESTAMP' 2010-01-01
00:00:00.000000000')) AND ("T"."VT_B_START" IS NULL OR
SYS_EXTRACT_UTC("T"."VT_B_START")<=SYS_EXTRACT_UTC(TIMESTAMP'
2010-01-01 00:00:00.000000000')) AND ("T"."VT_B_END" IS NULL OR
SYS_EXTRACT_UTC("T"."VT_B_END")>SYS_EXTRACT_UTC(TIMESTAMP' 2010-01-01
00:00:00.000000000')))
85
Table With User-generated Start and End Columns
CREATE TABLE
(empno
,ename
,stime
,etime
emp_mine
NUMBER
VARCHAR2(12)
DATE
DATE);
SELECT * FROM
EMPNO
----11
12
ENAME
----COX
ALLEN
EMP_MINE;
STIME
--------20-JAN-13
20-JAN-13
ETIME
--------21-FEB-13
21-FEB-13
ALTER TABLE emp_mine ADD(PERIOD FOR pd(stime,etime));
SELECT column_name,data_type,column_id AS col_id
,segment_column_id AS seg_col_id
,internal_column_id AS int_col_id,hidden_column,virtual_column
FROM user_tab_cols WHERE table_name = 'EMP_MINE';
COLUMN_NAME
--------------PD
ETIME
STIME
ENAME
EMPNO
DATA_TYPE
COL_ID SEG_COL_ID INT_COL_ID HID VIR
-------------------- ---------- ---------- ---------- --- --NUMBER
5 YES YES
DATE
4
4
4 NO NO
DATE
3
3
3 NO NO
VARCHAR2
2
2
2 NO NO
NUMBER
1
1
1 NO NO
86
Flashback Techniques for Oracle Database 11g
and The Next Generation
Flashback Queries
Flashback Row and Transaction History
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Flashback Data Archive
Valid Time Support
87
Flashback Techniques for Oracle
Database 11g and The Next Generation
Carl Dudley
University of Wolverhampton, UK
UKOUG
Oracle ACE Director
[email protected]