Flashback Techniques for Oracle 11 g and Oracle

Flashback Techniques for Oracle 11 g and Oracle 12 c Carl Dudley Tradba Ltd Oracle ACE Director carl. dudley@wlv. ac. uk

Introduction Working with Oracle since 1986 Oracle DBA - OCP Oracle 7, 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 Official Member of IOUC Day job – University of Wolverhampton, UK 2

Flashback Techniques for Oracle Database 11 g 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-2013 11: 00 AM'); 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 3 rd 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 11 g 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 SAL ---8000 7120 6300 ST ---------01 -MAR-13 05. 32. 38 01 -MAR-13 05. 28. 45 01 -MAR-13 05. 26. 14 XID --------06001 F 0014170000 04002 C 00151 B 0000 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 VARCHAR 2(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 --------- - -----05002500 BE 000000 428380 U BROWN 6650 8888 05001800 BE 000000 428372 428380 U BROWN 6000 8888 05001800 BE 000000 428372 D GREEN 4000 7950 04001600 BE 000000 428365 I WHITE 1000 1111 04001600 BE 000000 428365 428372 I BROWN 3000 8888 04001600 BE 000000 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 = '05001800 BE 000000'; XID UNDO_SQL ----------------------------05001800 BE 000000 update "SCOTT". "EMPF" set "SAL" = '3000' where ROWID = 'AAAMUDAAEAAAAIXAAB'; 05001800 BE 000000 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 = '070017004 A 090000'; SELECT xid, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('070017004 A 090000'); 15. 51 secs 0. 04 secs 13

Flashback Techniques for Oracle Database 11 g 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 Log. Miner Ø 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 : : 0 E 000700 F 2020000 _SYS_COMP_TXN_8388645_TIM_1235938019 01 -MAR-13 <? xml version="1. 0" encoding="ISO-8859 -1"? > <COMP_XID_REPORT XID="0 E 000700 F 20200 : SYS SELECT * FROM dba_flashback_txn_state; COMPENSATING_XID --------0 E 000700 F 2020000 XID DEPENDENT_XID --------140011008 D 010000 0 D 001 A 0089020000 140011008 D 010000 BACKOUT_MODE ------CASCADE USERNAME -------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('05001800 BE 000000'); 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 : : : : : 08009 A 210000 _SYS_COMP_TXN_1576008_TIM_1312991793 060010002 F 210000 1 1 2 update "FRED". "STOCK" set "NAME" = 'IBM', "CTIME" = TO_TIMESTAMP('10 -AUG-13 16. 48. 40. 781000') where "ST_ID" = '2' and "NAME" = 'HP' and "PRICE" = '11' and "CTIME" = TO_TIMESTAMP('10 -AUG-13 16. 49. 10. 859000') and ROWID = 'AAAUe 2 AAEAAAb. VEAAB' 1 1 2 UPDATE 1 080008009 A 210000 _SYS_COMP_TXN_1576008_TIM_1312991793 060010002 F 210000 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 : : : : 08009 A 210000 _SYS_COMP_TXN_1576008_TIM_1312991793 060010002 F 210000 : : : : 08009 A 210000 _SYS_COMP_TXN_1576008_TIM_1312991793 08001 E 0099210000 060010002 F 210000 1 1 2 0 1 08001 E 0099210000 1 3 WRITE AFTER WRITE 08001 E 0099210000 1 1 0 00000000 0 0 22

Flashback Techniques for Oracle Database 11 g 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 t 1 TO SCN 12345; FLASHBACK TABLE t 1 TO TIMESTAMP TO_TIMESTAMP('2013 -11 -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 ------VARCHAR 2(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 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 11 g 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$xy. We 0+q+Sni. It. J 0 pn/u 54 A==$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$xy. We 0+q+Sni. It. J 0 pn/u 54 A==$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$xy. We 0+q+Sni. It. J 0 pn/u 54 A==$0”; PURGE TABLE emp; purges specified version of a dropped table purges earliest dropped version of emp PURGE RECYCLEBIN; PURGE TABLESPACE user 1 USER fred; PURGE INDEX "BIN$FTX 34 MN 88 J 7==$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$xy. We 0+q+Sni. It. J 0 pn/u 54 A==$ 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 [DEFERRED] | 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 11 g 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 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 ~ 16 M) 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 2 GB 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 8 K blocks each containing around 152 rows • Rows stored in ascending seqid order SEQID NUMBER(6) PRIMARY KEY EMPNO NUMBER(4) ENAME VARCHAR 2(10) JOB VARCHAR 2(9) MGR NUMBER(4) HIREDATE 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 8 mb (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 FROM v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED RECLAIM FILES --------------- -------D: oracleflash_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 hh 24: mi') start_time , TO_CHAR(end_time, 'ddth hh 24: mi') end_time , db_data , redo_data , flashback_data fl_data , estimated_flashback_size est_fl_size FROM v$flashback_database_stat; START_TIME -----15 th 15: 59 15 th 14: 59 15 th 13: 59 15 th 12: 59 15 th 11: 52 END_TIME DB_DATA REDO_DATA FL_DATA EST_FL_SIZE ---------- -----15 th 16: 48 20234240 9678336 12361728 0 15 th 15: 59 19652608 7720960 10272768 398499840 15 th 14: 59 21643264 8264704 12541952 447406080 15 th 13: 59 20897792 7571968 12435456 516833280 15 th 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 ~ 8 MB on a ‘quiet’ system with names like O 1_MF_0 B 87 CPH 6_. 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 (11 g. R 2) — 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 ~ ~ ~~ ~~ ~ ~ ~~~~~~~ ~ ~ ~~ ~~ ~ ~ flashback log 1 block 34 ~ ~ ~~ ~~ ~ ~ ~~~~~~~ ~ ~ ~~ ~~ ~ ~ flashback log 2 block 34 Restore point 1 ~ ~ ~~ ~~ ~ ~ ~~~~~~~ ~ ~ ~~ ~~ ~ ~ Current version Restore point 2 flashback log 3 53

Flashback Techniques for Oracle Database 11 g 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 Ø Flash. Back 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 57

Creating a Flashback Data Archive CREATE TABLESPACE tbs_fba 1 DATAFILE 'c: oracleoradataorcltbs_fba 1_f 1. dbf' SIZE 10 G SEGMENT SPACE MANAGEMENT AUTO; Ø Create the flashback data archive in ASSM (mandatory) tablespace(s) CREATE FLASHBACK ARCHIVE fba 1 TABLESPACE tbs_fba 1 QUOTA 5 G RETENTION 1 MONTH; Ø Allow a user to track changes to his tables in the fba 1 archive GRANT FLASHBACK ARCHIVE ON fba 1 TO fred; 58

Creating a Flashback Data Archive Ø Enable history tracking for a table in the fba 1 archive CONN fred/fred CREATE TABLE emparch (empno NUMBER(4) , ename VARCHAR 2(12) , sal NUMBER(7, 2) , comm NUMBER(7, 2)) FLASHBACK ARCHIVE fba 1; or ALTER TABLE emp FLASHBACK ARCHIVE fba 1; Ø View the historical data SELECT empno, ename, sal FROM emp AS OF TIMESTAMP TO_TIMESTAMP ('2013 -11 -01 00: 00', 'YYYY-MM-DD HH 24: 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_ RETENTION_ CREATE_TIME LAST_PURGE_TIME STATUS ARCHIVE_NAME ARCHIVE# IN_DAYS ----------------------------------FBA 1 1 30 13 -OCT-13 14. 28. 00000 12 -NOV-13 14. 28. 00000 SELECT * FROM dba_flashback_archive_ts; FLASHBACK_ARCHIVE_NAME FLASHBACK_ TABLESPACE_NAME QUOTA_IN_MB ARCHIVE# ----------------FBA 1 1 TBS_FBA 1 5000 SELECT * FROM dba_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME ----------- ---------EMPARCH SYS FBA 1 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 --------- ------------ ---AAAR 0 n. AABAAAVxi. AAA 5432321 5432342 02001 D 00 C 3110000 I 7369 SMITH 800 AAAR 0 n. AABAAAVxi. AAB 5432321 5432342 02001 D 00 C 3110000 I 7499 ALLEN 1600 300 AAAR 0 n. AABAAAVxi. AAC 5432321 5432342 02001 D 00 C 3110000 I 7521 WARD 1250 500 AAAR 0 n. AABAAAVxi. AAD 5432321 5432342 02001 D 00 C 3110000 I 7566 JONES 2975 AAAR 0 n. AABAAAVxi. AAE 5432321 5432342 02001 D 00 C 3110000 I 7654 MARTIN 1250 1400 AAAR 0 n. AABAAAVxi. AAF 5432321 5432342 02001 D 00 C 3110000 I 7698 BLAKE 2850 AAAR 0 n. AABAAAVxi. AAG 5432321 5432342 02001 D 00 C 3110000 I 7782 CLARK 2450 AAAR 0 n. AABAAAVxi. AAH 5432321 5432342 02001 D 00 C 3110000 I 7788 SCOTT 3000 AAAR 0 n. AABAAAVxi. AAI 5432321 5432342 02001 D 00 C 3110000 I 7839 KING 5000 AAAR 0 n. AABAAAVxi. AAJ 5432321 5432342 02001 D 00 C 3110000 I 7844 TURNER 1500 0 AAAR 0 n. AABAAAVxi. AAK 5432321 5432342 02001 D 00 C 3110000 I 7876 ADAMS 1100 AAAR 0 n. AABAAAVxi. AAL 5432321 5432342 02001 D 00 C 3110000 I 7900 JAMES 950 AAAR 0 n. AABAAAVxi. AAM 5432321 5432342 02001 D 00 C 3110000 I 7902 FORD 3000 AAAR 0 n. AABAAAVxi. AAN 5432321 5432342 02001 D 00 C 3110000 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 SYS SYS SYS SYS SYS TABLESPACE_NAME -------TBS_FBA 2 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_69878 SYS_FBA_TSFA SYS_FBA_BARRIERSCN SYS_FBA_TRACKEDTABLES SYS_FBA_PARTITIONS SYS_FBA_USERS SYS_FBA_DL SYS_FBA_COLS SYS_FBA_CONTEXT_AUD SYS_FBA_CONTEXT_LIST 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 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_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_FBA_HIST_69882 RANGE NONE 1 0 VALID TBS_FBA 1 Size could well be governed by 10 _flashback_archiver_partition_size 1 255 DEFAULT DEFAULT NONE ENABLED ADVANCED 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_length , initial_extent, compression, compress_for FROM dba_tab_partitions WHERE table_name = 'SYS_FBA_HIST_69882' TABLE_NAME PARTITION_NAME 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 8 M and is not deferred ENABLED ADVANCED TABLE_NAME PARTITION_NAME 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 | 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 | | 204 K| |* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_69882 | 204 K| --------------------------------This table is automatically indexed 65

Flashback Archive Query Performance Ø emparch has 1, 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. 06 secs CREATE INDEX emparch$empno ON sys_fba_hist_69882(empno); Elapsed time : 4. 89 secs 66

Column Mapping and DDL Restrictions Ø From 11 g 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 HISTORICAL_ XID OPERATION COLUMN_NAME E --------- -------5424699 EMPNO 5424699 NAME 5424699 22415904 SAL 5424699 COMM 5474905 20775346 D_20775346_JOB TYPE COLUMN_NAM ------NUMBER(4) VARCHAR 2(12) NUMBER(7, 2) VARCHAR 2(15) -----EMPNO ENAME SALARY COMM 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 Ø 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

Stopping Archiving Ø Disabling archiving for a specific table requires the FLASHBACK ARCHIVE ADMINISTER privilege – ‘normal’ users are not allowed to disable the archiving ALTER TABLE scott. emp NO FLASHBACK ARCHIVE; — Drops the history table Ø Archiving can be temporarily disabled using dbms_flashback_archive — Keeps the history table — Allows schema modifications 1. EXEC dbms_flashback_archive. dissociate_fba(’scott’, ’emp’) 2. Make structural changes to table being archived 3. Make corresponding structural changes to the history table 4. EXEC dbms_flashback_archive. reassociate_fba(’scott’, ’emp’) 69

Flashback Data Archiving versus Oracle 11 g 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 — BUT Unified Auditing on 12 c uses a buffering mechanism Ø Audit trails are handled manually — Flashback Data Archives can be automatically purged ALTER FLASHBACK ARCHIVE fba 1 MODIFY RETENTION 6 MONTH; — Or handled manually ALTER FLASHBACK ARCHIVE fba 1 PURGE BEFORE TIMESTAMP(SYSTIMESTAMP – INTERVAL '1' DAY); 70

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 VARCHAR 2(12), sal NUMBER(7, 2), comm NUMBER(7, 2), rid VARCHAR 2(20), curdate DATE, username VARCHAR 2(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; 71

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. 45 s 3. 81 s 9. 70 s Commit 0. 00 s 0. 18 s 0. 00 s 72

12 c 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_fba_context_list; NAMESPACE ---------------USERENV : USERENV FBA_CONTEXT PARAMETER ------------ACTION AUTHENTICATED_IDENTITY : SESSIONID TERMINAL ALL 73

New View – sys_fba_context_aud EXEC PT ('SELECT * FROM sys_fba_context_aud') XID : 1 E 00120059030000 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 : WORKGROUPLT 1 IDENTIFICATION_TYPE : LOCAL INSTANCE_NAME : orcl IP_ADDRESS : MODULE : SQL*Plus OS_USER : LT 1Administrator SERVER_HOST : ltree 1 SERVICE_NAME : SYS$USERS SESSION_EDITION_NAME : ORA$BASE SESSION_USER : FRED SESSION_USERID : 120 SESSIONID : 1509620 TERMINAL : LT 1 SPARE : --------------------------: : and TYPICAL 74

Correlating Context Information with History SELECT sfba. authenticated_identity , sfba. host , sfba. module , hist. empno , hist. ename FROM sys_fba_context_aud sfba , fred. sys_fba_hist_175827 hist WHERE hist. xid = sfba. xid; SESSION_USER ------FRED HOST ------WORKGROUPLT 1 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'); 75

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’ —See dbms_flashback_archive documentation 76

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 RAW(8) VARCHAR 2(1) NUMBER(4) VARCHAR 2(12) NUMBER(7, 2) Ø Import temp table data into FDA History table -----------RID VARCHAR 2(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 77

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 VARCHAR 2(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, opn, : OLD. empno, : OLD. ename, : OLD. sal, : OLD. comm); END; / 78

Flashback Techniques for Oracle Database 11 g 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 79

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) 80

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 VARCHAR 2(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 NUMBER 1 3 4 NO NO VARCHAR 2 2 4 5 NO NO 81

Using Valid Times – SQL level control INSERT INTO emp_vt(empno, ename, emp_vt_time_start, emp_vt_time_end) 82

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 Ø No real support for primary keys — What if employees have numerous valid periods — Cannot have more than one row for each employee 83

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. 000000') EXEC dbms_flashback_archive. enable_at_valid_time UPDATE emp_vt 2 SET vt_a_start = '01 -JAN-02 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_vt 2 SET vt_a_start = '01 -JAN-02 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 84

Constraints for Valid Tiimes OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME 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_TIMEA 67 E 83 C 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 85

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. 00000')) AND ("T". "VT_A_END" IS NULL OR SYS_EXTRACT_UTC("T". "VT_A_END")>SYS_EXTRACT_UTC(TIMESTAMP' 2010 -01 -01 00: 00. 00000')) AND ("T". "VT_B_START" IS NULL OR SYS_EXTRACT_UTC("T". "VT_B_START")<=SYS_EXTRACT_UTC(TIMESTAMP' 2010 -01 -01 00: 00. 00000')) AND ("T". "VT_B_END" IS NULL OR SYS_EXTRACT_UTC("T". "VT_B_END")>SYS_EXTRACT_UTC(TIMESTAMP' 2010 -01 -01 00: 00. 00000'))) 86

Table With User-generated Start and End Columns CREATE TABLE (empno , ename , stime , etime emp_mine NUMBER VARCHAR 2(12) DATE); SELECT * FROM EMPNO ----11 12 ENAME ----COX ALLEN emp_mine; STIME ----20 -JAN-13 ETIME ----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 DATE 4 4 4 NO NO DATE 3 3 3 NO NO VARCHAR 2 2 NO NO NUMBER 1 1 1 NO NO 87

Flashback Techniques for Oracle Database 11 g and Oracle Database 12 c Flashback Queries Flashback Row and Transaction History Flashback Transaction Backout Flashback Table Flashback Drop Flashback Database Flashback Data Archive Valid Time Support 88

Flashback Techniques for Oracle 11 g and Oracle 12 c Carl Dudley Tradba Ltd Oracle ACE Director carl. dudley@wlv. ac. uk