1 / 88

Flashback Techniques for Oracle Database 11g and The Next Generation

Carl Dudley University of Wolverhampton, UK UKOUG Oracle ACE Director carl.dudley@wlv.ac.uk. Flashback Techniques for Oracle Database 11g and The Next Generation. Introduction. Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002

jubal
Télécharger la présentation

Flashback Techniques for Oracle Database 11g and The Next Generation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Carl Dudley University of Wolverhampton, UK UKOUG Oracle ACE Director carl.dudley@wlv.ac.uk Flashback Techniques for Oracle Database 11g and The Next Generation

  2. 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

  3. 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

  4. 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 * FROMemployees AS OF TIMESTAMP TRUNC(SYSDATE);

  5. 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

  6. Flashback Query Limitations • Ability to see data in a table as at a point in time • Perform rollback past committed transactions • 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

  7. 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

  8. 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 SAL ST XID ----- ---- ------------------ ----------------- 7766 8000 01-MAR-13 05.32.38 06001F0014170000 7766 7120 01-MAR-13 05.28.45 04002C00151B0000 7766 6300 01-MAR-13 05.26.14 0200290017370000 • No need for audit tables? • Does not show any inserts and deletes due to online shrink operations

  9. 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 ENAME SAL ----- --------- ----- 7950 BROWN 3000 8888 GREEN 4000 1111 WHITE 1000

  10. 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 ENAME SAL ----- -------- ----- 8888 BROWN 6000 1111 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 ENAME SAL ----- -------- ----- 8866 BROWN 6650 1111 WHITE 1000

  11. 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

  12. 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

  13. 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'; 15.51 secs SELECT xid, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('070017004A090000'); 0.04 secs

  14. 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

  15. 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) • 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

  16. Flashback Transaction Requirements • Database must be in ARCHIVELOG mode and COMPATIBLE >= 11.1.0.0 • Requires additional redo logging • Minimal supplemental logging • Logging of primary key values for any row that is changed Must start work using archived logs – hence at least one log has to be archived since start of operation • Force an archive : • Flashback makes use of LogMiner • User requires privileges • SELECT ANY TRANSACTION • EXECUTE on dbms_flashback ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER SYSTEM ARCHIVE LOG CURRENT;

  17. 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

  18. Flashback Transaction Controls • Can dictate cascading effects on dependent transactions via the enumerated type options • 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"

  19. Collecting Information • Situation after committing the results of the CASCADE option SELECT * FROM dba_flashback_txn_report; COMPENSATING_XID : 0E000700F2020000 COMPENSATING_TXN_NAME : _SYS_COMP_TXN_8388645_TIM_1235938019 COMMIT_TIME : 01-MAR-13 XID_REPORT : <?xml version="1.0" encoding="ISO-8859-1"?> <COMP_XID_REPORT XID="0E000700F20200 USERNAME : SYS SELECT * FROM dba_flashback_txn_state; COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE USERNAME ---------------- ---------------- ---------------- ------------ -------- 0E000700F2020000 140011008D010000 CASCADE SYS 0E000700F2020000 0D001A0089020000 140011008D010000 CASCADE SYS

  20. Backing out Transactions Example DECLARE v_xids sys.XID_ARRAY := sys.xid_array(); BEGIN v_xids.extend; v_xids(1) := HEXTORAW('05001800BE000000'); sys.DBMS_FLASHBACK.TRANSACTION_BACKOUT(numtxns => 1, xids => v_xids, options => 4,scnhint => 0); END; / The bad transaction id • 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

  21. v$flashback_txn_mods EXEC scott.pt('SELECT * FROM v$flashback_txn_mods') COMPENSATING_XID : 080008009A210000 COMPENSATING_TXN_NAME : _SYS_COMP_TXN_1576008_TIM_1312991793 XID : 060010002F210000 TXN_NAME : PARENT_XID : 060010002F210000 INTERESTING : 1 ORIGINAL : 1 BACKOUT_SEQ : 2 UNDO_SQL : 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' UNDO_SQL_SQN : 1 UNDO_SQL_SUB_SQN : 1 BACKOUT_SQL_ID : 2 OPERATION : UPDATE BACKEDOUT : 1 CONFLICT_MOD : 0 MODS_PER_LCR : 1 ----------------- COMPENSATING_XID : 080008009A210000 COMPENSATING_TXN_NAME : _SYS_COMP_TXN_1576008_TIM_1312991793 XID : 060010002F210000 TXN_NAME : PARENT_XID : 060010002F210000 INTERESTING : 1 : :

  22. v$flashback_txn_graph EXEC scott.pt('SELECT * FROM v$flashback_txn_graph') COMPENSATING_XID : 080008009A210000 COMPENSATING_TXN_NAME : _SYS_COMP_TXN_1576008_TIM_1312991793 XID : 060010002F210000 TXN_NAME : PARENT_XID : 060010002F210000 INTERESTING : 1 ORIGINAL : 1 BACKOUT_SEQ : 2 NUM_PREDS : 0 NUM_SUCCS : 1 DEP_XID : 08001E0099210000 DEP_TXN_NAME : TXN_CONF_SQL_ID : 1 DEP_TXN_CONF_SQL_ID : 3 CONFLICT_TYPE : WRITE AFTER WRITE ----------------- COMPENSATING_XID : 080008009A210000 COMPENSATING_TXN_NAME : _SYS_COMP_TXN_1576008_TIM_1312991793 XID : 08001E0099210000 TXN_NAME : PARENT_XID : 08001E0099210000 INTERESTING : 1 ORIGINAL : 0 BACKOUT_SEQ : 1 NUM_PREDS : 1 NUM_SUCCS : 0 DEP_XID : 0000000000000000 DEP_TXN_NAME : TXN_CONF_SQL_ID : 0 DEP_TXN_CONF_SQL_ID : 0 CONFLICT_TYPE :

  23. 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

  24. 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

  25. Using Flashback Table • 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 • 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

  26. 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 Type -------------- ------------ SCHEMA VARCHAR2(32) OBJECT_NAME VARCHAR2(32) OBJECT# NUMBER RID ROWID ACTION CHAR(1)

  27. 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 • Create a table (ef) containing 14336 rows (143 rows per block) • Enable row movement ALTER TABLE ef ENABLE ROW MOVEMENT; • Find current timestamp SELECT systimestamp FROM DUAL; • 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>');

  28. 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 ACTION ------------------ ------ AAANAIAAEAAAAJMAAA D AAANAIAAEAAAAJMAAB D AAANAIAAEAAAAJMAAC D AAANAIAAEAAAAJMAAA I AAANAIAAEAAAAJMAAB I AAANAIAAEAAAAJMAAC 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 • ChangesROWIDs 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

  29. 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

  30. 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

  31. Flashback Drop and the Recycle Bin • Dropping a tableplaces 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 autoextensiblefiles • 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

  32. 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

  33. Purging Objects in the Recycle Bin • Object(s) can be purged from the recycle bin purges specified version of a dropped table PURGE TABLE “BIN$xyWe0+q+SniItJ0pn/u54A==$0”; PURGE TABLE emp; 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;

  34. 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;

  35. 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 • 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 ALTER SESSION SET recyclebin = OFF SCOPE = SPFILE;

  36. 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

  37. 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 ARCHIVELOGmode FLASHBACK DATABASE TO TIMESTAMP ... FLASHBACK DATABASE TO BEFORE TIMESTAMP ... FLASHBACK DATABASE TO BEFORE SCN ... FLASHBACK DATABASE TO SCN ... • Needs SYSDBA privilege

  38. Flashback Logging Database • Recommended to store both flashback and archived logs in a common recovery area • NOLOGGING operations are recorded in the flashback logs RVWR LGWR Archived logs Flashback logs RVWR = Recovery Writer

  39. SGA Selective block logging All changes RVWR LGWR Flashback Buffer (default size ~ 16M) Buffer cache Redo log buffer Before images (blocks) logged periodically Flashback logs Redo logs Flashback Structures • Flashback buffer size appears to be based on 2*LOG_BUFFER

  40. Setting up Flashback Database • 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;

  41. 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 Flashback log 21 Flashback log 22 Flashback log 23 55617 67234 50614 62983 Database SCN69633 Archive stream 50617 53297

  42. 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

  43. 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)

  44. 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

  45. Flashback Generation (continued) • Same number of transactions (updates), but approximately one row in each block updated • 2175 different blocks affected 2 flashback logs generated, each of 8mb (approx) Elapsed: 9.95 secs BEGIN FOR i IN 1..2175 LOOP UPDATE emp SET sal = sal+1 WHERE seqid = i*152; COMMIT; END LOOP; END; / • Repeat runs do not generate flashback logs  5.93 secs Without flashback  5.01 secs

  46. 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

  47. 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 END_TIME DB_DATA REDO_DATA FL_DATA EST_FL_SIZE ---------- ---------- ---------- ---------- ---------- ----------- 15th 15:59 15th 16:48 20234240 9678336 12361728 0 15th 14:59 15th 15:59 19652608 7720960 10272768 398499840 15th 13:59 15th 14:59 21643264 8264704 12541952 447406080 15th 12:59 15th 13:59 20897792 7571968 12435456 516833280 15th 11:52 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

  48. ~ ~ 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

  49. Repeating Flashbacks • Flashback must be performed in a mount state and requires an : • Deletes flashback logs • What if you are unsure that your flashback is to the correct point in time • Open the database in READ ONLY mode to observe the data 2. Shutdown • Mount the database • 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 • ALTER DATABASE OPEN RESETLOGS; • ALTER DATABASE OPEN READ ONLY;

  50. 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 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? CREATE RESTORE POINT before_upgrade [GUARANTEE FLASHBACK DATABASE];

More Related