1 / 52

Oracle10g: Behind the Scenes of Flashback Technologies

Oracle10g: Behind the Scenes of Flashback Technologies. DOUG Meeting July 19, 2007 Kirtikumar Deshpande. About Me. Senior Oracle DBA Idearc Media Phone Directories Publication. Agenda. Flashback Query - Background Oracle10g - What’s New Flashback Table Flashback Drop Table

shaina
Download Presentation

Oracle10g: Behind the Scenes of Flashback Technologies

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. Oracle10g: Behind the Scenes of Flashback Technologies DOUG Meeting July 19, 2007 Kirtikumar Deshpande DOUG_071907

  2. About Me • Senior Oracle DBA • Idearc Media • Phone Directories Publication DOUG_071907

  3. Agenda • Flashback Query - Background • Oracle10g - What’s New • Flashback Table • Flashback Drop Table • Flashback Version Query • Flashback Transaction Query • Flashback Database • Q & A DOUG_071907

  4. Flashback Query - Background • Oracle9i Release 1 • Undo Tablespace • Automatic Undo Segments • Undo Retention Time • Session level Flashback Query feature • Oracle9i Release 2 • Statement level Flashback Query feature DOUG_071907

  5. What’s New in Oracle10g • Guaranteed Undo Retention • Undo preserved for specified retention time • Auto Undo Retention Tuning • Undo Retention time adjusted automatically • V$UNDOSTAT view enhancements • New information to track long running queries DOUG_071907

  6. What’s New in Oracle10g • Pseudocolumns • ORA_ROWSCN • VERSIONS_STARTTIME • VERSIONS_STARTSCN • VERSIONS_ENDTIME • VERSIONS_ENDSCN • VERISONS_XID • VERSIONS_OPERATION DOUG_071907

  7. What’s New in Oracle10g • Functions • SCN_TO_TIMESTAMP • TIMESTAMP_TO_SCN • View • FLASHBACK_TRANSACTION_QUERY • Recycle Bin • To restore dropped table (not PURGED) DOUG_071907

  8. What’s New in Oracle10g • Flashback Drop • Restore dropped table • Flashback Table • Restore table to a point in time in the past DOUG_071907

  9. What’s New in Oracle10g • Flashback Versions Query • How data changed over a timeframe • Flashback Transaction Query • How data changed at transactions level DOUG_071907

  10. What’s New in Oracle10g • Flashback Database • Restores database to a point in time in the past • Does not use undo segments and recyclebin • Needs special configuration DOUG_071907

  11. GuaranteedUndo Retention • Guaranteed Retention makes sure that the committed undo is preserved for the specified retention time. • Transactions that need more undo space may terminate due to lack of undo space as Oracle will not overwrite undo information. DOUG_071907

  12. Guaranteed Undo Retention • UNDO Tablespace Option • create unto tablespace undo_tbs datafile ‘/u09/oradata/ked9_undo_01.dbf’ size 100M retention guarantee; • alter tablespace undo_tbs retention guarantee; • alter tablespace undo_tbs retention noguarantee; • RETENTIONNOGUARANTEE - Default DOUG_071907

  13. Auto Undo Retention Tuning • UNDO_RETENTION value may change • V$UNDOSTAT tracks undo retention value for sampled intervals • TUNED_UNDORETENTION DOUG_071907

  14. V$UNDOSTAT View • New Columns • MAXQUERYID • ACTIVEBLKS • UNEXPIREDBLKS • EXPIREDBLKS • TUNED_UNDORETENTION DOUG_071907

  15. V$UNDOSTAT Example BEGIN_TIME END_TIME MAXQUERYID MAXQL TUNEDRET ACTBLK UNXPBLK XPBLK ------------------------ ------------------------- ------------------- ----------- -------------- ---------- ----------- ---------- 22-FEB-06 23:54:49 23-FEB-06 00:04:49 0 1800 448 256 142416 22-FEB-06 23:44:49 22-FEB-06 23:54:49 0 1800 448 130176 12496 22-FEB-06 23:34:49 22-FEB-06 23:44:49 b1z64uk2y5p6v 5997 6358 448 130048 12496 22-FEB-06 23:24:49 22-FEB-06 23:34:49 b1z64uk2y5p6v 5392 5752 448 130048 12496 22-FEB-06 23:14:49 22-FEB-06 23:24:49 b1z64uk2y5p6v 4785 5145 448 130048 12496 22-FEB-06 23:04:49 22-FEB-06 23:14:49 b1z64uk2y5p6v 4178 4538 448 130048 12496 22-FEB-06 22:54:49 22-FEB-06 23:04:49 b1z64uk2y5p6v 3571 3932 448 129792 12496 22-FEB-06 22:44:49 22-FEB-06 22:54:49 b1z64uk2y5p6v 2965 3325 448 129664 12496 22-FEB-06 22:34:49 22-FEB-06 22:44:49 b1z64uk2y5p6v 2357 2720 448 129408 13392 22-FEB-06 22:24:49 22-FEB-06 22:34:49 b1z64uk2y5p6v 1752 2112 448 129408 13264 22-FEB-06 22:14:49 22-FEB-06 22:24:49 b1z64uk2y5p6v 1145 1800 448 129408 13136 22-FEB-06 22:04:49 22-FEB-06 22:14:49 b1z64uk2y5p6v 538 1800 448 127616 13264 22-FEB-06 21:54:49 22-FEB-06 22:04:49 5rv0j37z1ztgm 203 1800 448 256 15952 22-FEB-06 21:44:49 22-FEB-06 21:54:49 ac20brc1kdgdx 927 1800 448 256 15952 22-FEB-06 21:34:49 22-FEB-06 21:44:49 ac20brc1kdgdx 320 1800 448 128 16080 DOUG_071907

  16. Flashback Table • Recovers table data to point in time in the past • Uses undo segments to reconstruct past data • Undo information must be available in undo segments • Uses an internal global temporary table, SYS_TEMP_FBT, to stage data DOUG_071907

  17. Flashback Table • Enable row movement • alter table t1 enable row movement; • Flashback Table to SCN or Timestamp • flashback table t1 to <SCN>; • flashback table t1 to timestamp( <systimestamp – interval ‘5’ minute>); DOUG_071907

  18. Flashback Table • Example: SQL> select department_id, department_name from dept; DEPARTMENT_ID DEPARTMENT_NAME --------------------- ------------------------------ 10 Administration 20 Marketing 30 Purchasing 40 Human Resources DOUG_071907

  19. Flashback Table SQL> delete dept; 4 rows deleted. SQL> commit; Commit complete. SQL> flashback table dept to timestamp (systimestamp - interval ‘10' minute); Flashback complete. SQL> select department_id, department_name from dept; DEPARTMENT_ID DEPARTMENT_NAME --------------------- ------------------------------ 10 Administration 20 Marketing 30 Purchasing 40 Human Resources DOUG_071907

  20. Flashback Table • Restrictions and Limitations • Does not work for • Clustered tables • Tables in Materialized View • Advance Queuing Tables • Data Dictionary Tables • Table Partitions/Sub-Partitions • Nested Tables • Remote Tables DOUG_071907

  21. Flashback Table • Restrictions and Limitations • Referential Integrity Constraints may limit flashback operation • Any DDL operation on table will restrict flashback time (or SCN) to the DDL time • Bug: 3076151 - Unpublished • Internal GTT SYS_TEMP_FBT not dropped after flashback operation DOUG_071907

  22. Flashback Drop Table • Really an ‘undrop’ Table operation • Does not use Undo Segments • Uses ‘RECYCLEBIN’ to restore a dropped table flashback table t1 to before drop; flashback table t1 to before drop rename t2; DOUG_071907

  23. Flashback Drop Table • DROP TABLE with PURGE option, or purging RECYCLEBIN will NOT restore the table • Restored dependent objects (triggers, indexes, constraints) will get system generated names • Need to RENAME those explicitly DOUG_071907

  24. Flashback Drop Table • Recyclebin • Dropped objects do not release disk space • Views • USER_RECYCLEBIN • DBA_RECYCLEBIN • Public Synonym • RECYCLEBIN DOUG_071907

  25. Flashback Drop Table • Recyclebin • Enabled by Default • Can be Disabled • 10g R1 alter system set “_recyclebin” = false; • 10g R2 alter session set recyclebin = off; alter system set recyclebin = off; DOUG_071907

  26. Flashback Drop Table • Example: SQL> drop table fdt cascade constraints; Table dropped. SQL> select object_name, original_name, type from recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE ---------------------------------------------- --------------------- -------------- BIN$2NkxhR9EQYaOi6iEb5MyEw==$0 FDT_I1 INDEX BIN$eH23RoJUSg6P6x98+kvK/w==$0 FDT_PK INDEX BIN$3jL+fbUZTza4Z0HRlZMYvA==$0 FDT_T1 TRIGGER BIN$2EIiiDRWQ8u/P1c366iICg==$0 FDT TABLE 4 rows selected. DOUG_071907

  27. Flashback Drop Table SQL> flashback table fdt to before drop; Flashback complete. SQL> select index_name obj_name, ‘NDX' type from user_indexes where table_name ='FDT‘ 2 union 3 select trigger_name obj_name, 'TRIG' type from user_triggers where table_name = 'FDT' 4 union 5 select constraint_name obj_name, 'CNSTR- ' ||constraint_type 6 from user_constraints where table_name = 'FDT' 7 / OBJ_NAME TYPE ------------------------------------------------ --------------- BIN$2NkxhR9EQYaOi6iEb5MyEw==$0 NDX BIN$3jL+fbUZTza4Z0HRlZMYvA==$0 TRIG BIN$ZsANcV+8TfGmDzwH5wzIFg==$0 CNSTR- P BIN$eH23RoJUSg6P6x98+kvK/w==$0 NDX BIN$s10Y+0xMTriID+82Iqovxg==$0 CNSTR- C 5 rows selected. DOUG_071907

  28. Flashback Drop Table • Limitations (Does not Work For) • External Tables • Materialized Views • Tables in Dictionary Managed Tablespaces • Tables in SYSTEM Tablespace DOUG_071907

  29. Flashback Versions Query • View row data at various commit times (Versions) within a timeframe • Enhanced SQL syntax to retrieve rows • VERSIONS BETWEEN Clause • Timeframe can be Timestamp range or SCN range • VERSIONS BETWEEN TIMESTAMP • VERSIONS BETWEEN SCN DOUG_071907

  30. Flashback Versions Query • Timeframe must fall within Undo Retention time • Can be used to audit and analyze changes • Can assist in testing Application process • Only committed changes can be seen DOUG_071907

  31. Flashback Versions Query • Syntax: SELECT * FROM DEPARTMENTS VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP – INTERVAL ’30’ MINUTE) AND (SYSTIMESTAMP) WHERE DEPARTMENT_ID = 100; DOUG_071907

  32. Flashback Versions Query • Pseudo-columns with returned rows: • VERSIONS_STARTSCN • If NULL, then version existed prior to the lower bound of SCN range, else the starting SCN for version of row data shown • VERSIONS_ENDSCN • If NULL, then shown version is current row or row is deleted, else the SCN when row version expired DOUG_071907

  33. Flashback Versions Query • Pseudo-columns: • VERSIONS_STARTTIME • If NULL then version existed prior to the lower bound of timestamp range, else the starting timestamp for version of row data shown • VERSIONS_ENDTIME • If NULL then shown version is current row or row is deleted, else the timestamp when row version expired DOUG_071907

  34. Flashback Versions Query • Pseudo-columns: • VERSIONS_XID • Transaction ID that created the row version • VERSIONS_OPERATION • DML Operation that created the row version (I=Insert, D=Delete, U=Update) DOUG_071907

  35. Flashback Versions Query • Example SQL> select department_id, department_name, manager_id, 2 versions_starttime, versions_endtime, 3 versions_operation 4 from fv 5 versions between timestamp 6 (systimestamp - interval '5' minute) 7 and (systimestamp) 8 order by department_id, versions_starttime; DOUG_071907

  36. Flashback Versions Query Dept DeptName MGR_ID VERSIONS_STARTTIME VERSIONS_ENDTIME V ------ ---------------------- ---------- ----------------------------- ---------------------------- - 10 Administration 888 23-FEB-06 05.27.01 PM U 10 Administration 200 23-FEB-06 05.27.01 PM 20 Marketing 201 30 Purchasing 114 40 Human Resources 203 DOUG_071907

  37. Flashback Versions Query • Example SQL> select versions_xid xid, versions_startscn start_scn, 2 versions_endscn end_scn, versions_operation oper, 3 department_id, department_name, manager_id 4 from fv 5 versions between scn 6 minvalue 7 and maxvalue; DOUG_071907

  38. Flashback Version Query XID START_SCN END_SCN O Dept DeptName MANAGER_ID ------------------------ --------------- --------------- - ------- ------------------- ----------------- 08002400E9080000 5373412 U 30 Purchasing 777 06000000DB080000 5373406 U 10 Administration 888 5373406 10 Administration 200 20 Marketing 201 5373412 30 Purchasing 114 40 Human Resources 203 6 rows selected. DOUG_071907

  39. Flashback Transaction Query • View/Recover all data modifications at Transaction level • FLASHBACK_TRANSACTION_QUERY view provides Undo SQL among other information • SELECT ANY TRANSACTION privilege needed to access this view DOUG_071907

  40. Flashback Transaction Query • Can be used to audit and analyze changes • Can assist in testing Application process • Only committed changes can be seen • Supplements Flashback Versions Query DOUG_071907

  41. Flashback Transaction Query • FLASHBACK_TRANSACTION_QUERY View Name Null? Type ----------------------------- -------- -------------- XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000) DOUG_071907

  42. Flashback Transaction Query • Example: • List transactions and affected tables in ‘KIRTI’ schema after a certain date and time SQL> select xid, operation, start_scn, commit_scn 2 table_owner, table_name, logon_user, undo_sql 3 from flashback_transaction_query 4 where table_owner = 'KIRTI' 5 and start_timestamp >= 6 to_timestamp('02/03/06 17:27:00','MM/DD/YY HH24:MI:SS'); DOUG_071907

  43. Flashback Transaction Query XID OPERATION START_SCN COMMIT_SCN TABLE_OWNER TABLE_NAME ----------------------- --------------- -------------- ----------------- ------------------ -----------------LOGON_USER ---------------- UNDO_SQL ------------------------------------------------------------------------------------------ 06000000DB080000 UPDATE 5373227 5373406 KIRTI FV KIRTI update "KIRTI"."FV" set "MANAGER_ID" = '200' where ROWID = 'AAANtjAAEAAAAGkAAA'; 08002400E9080000 UPDATE 5373406 5373412 KIRTI FT KIRTI update "KIRTI"."FT" set "MANAGER_ID" = '204' where ROWID = 'AAANtkAAEAAAAGsAAC'; 08002400E9080000 UPDATE 5373406 5373412 KIRTI FV KIRTI update "KIRTI"."FV" set "MANAGER_ID" = '114' where ROWID = 'AAANtjAAEAAAAGkAAC'; DOUG_071907

  44. Flashback Database • Set entire database to point in time in the past • Similar to the database Point In Time Recovery • Special setup is required to enable this functionality – Does not use undo segments and Recyclebin DOUG_071907

  45. Flashback Database • Flashback Logs - New type of log files • FB Logs are written to by a new background process RVWR • FB Logs are kept in flashback area • FB Logs contain before images of Oracle data blocks and are not archived DOUG_071907

  46. Flashback Database • New initialization parameters • DB_RECOVERY_FILE_DEST • Default location of flash recovery area • DB_RECOVERY_FILE_DEST_SIZE • Hard limit for the disk space used by files in recovery area • DB_FLASHBACK_RETENTION_TARGET • Upper limit in minutes on how far database can be flashed back DOUG_071907

  47. Flashback Database • New Views • V$FLASH_RECOVERY_AREA_USAGE • Usage information about flash recovery area • V$FLASHBACK_DATABASE_LOG • Information about flashback data, used for sizing flash recovery area • V$FLASHBACK_DATABASE_STAT • Information about I/O overhead of logging flashback data, estimated flashback spaces based on workloads DOUG_071907

  48. Flashback Database • Set up database for Flashback mode • Define new initialization parameters • Mount the database • Enable Archive log mode, if required • Switch database to run in ‘flashback’ mode alter database flashback on; • Open database DOUG_071907

  49. Flashback Database • Database can be flashed back using: • SQL command SQL> flashback database to scn|timestamp; • RMAN command RMAN> flashback database to scn|time; • OEM Recovery Wizard • Database must be in ‘mount’ mode • Database must be opened with ‘resetlogs’ DOUG_071907

  50. Flashback Database • Oracle10g R2 • Restore points • Aliases to SCN that you can use in flashback database command create restore point batch_start_1; create restore point batch_mid_1; flashback database to restore point batch_mid_1; DOUG_071907

More Related