1 / 46

Understanding Oracle9 i Automatic Undo Management and Flashback Query

Understanding Oracle9 i Automatic Undo Management and Flashback Query. Kirtikumar Deshpande CLTOUG July 14, 2005. About Me. Senior Oracle DBA Verizon Information Services Phone Directories Publication. Agenda. Automatic Undo Management Flashback Query Feature Demonstration Q & A.

tracey
Download Presentation

Understanding Oracle9 i Automatic Undo Management and Flashback Query

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. Understanding Oracle9i Automatic Undo Managementand Flashback Query Kirtikumar Deshpande CLTOUG July 14, 2005

  2. About Me • Senior Oracle DBA • Verizon Information Services • Phone Directories Publication

  3. Agenda • Automatic Undo Management • Flashback Query Feature • Demonstration • Q & A

  4. New Terminology • Undo Segment, not Rollback Segment • Automatic Undo Management (AUM) • System Managed Undo (SMU) • Manual Undo Management (MUM) • Rollback Undo (RBU)

  5. AUTOMATIC v/s MANUAL UNDO • Oracle9i database can operate in: • Automatic Undo Management Mode (AUM) • Default if database is created using DBCA • Possible only when COMPATIBLE is 9.0.0 and higher • Manual Undo Management Mode (MUM) • Possible when COMPATIBLE is 9.x, or 8.x • Use it when you are not ready for AUM or you are upgrading a lower release database • Changing UNDO mode requires instance startup

  6. AUTOMATIC v/s MANUAL UNDO • Manual Undo Management Mode: • Same as using Rollback Segments in Oracle8i and below • DBA must name, create and manage RBS • Oracle does NOT encourage using this mode

  7. AUTOMATIC v/s MANUAL UNDO • Automatic Undo Management Mode: • Oracle to name, create, manage Undo Segments • Oracle to control sizing, number of undo segments • Requires a new type of tablespace: UNDO • New init.ora parameters

  8. UNDO Tablespace - Creation • Option in CREATE DATABASE command • CREATE UNDO TABLESPACE command • LMT with SYSTEM policy for space allocation • One Active UNDO tablespace per instance • Each RAC instance has its own UNDO tablespace

  9. UNDO Tablespace - Creation CREATE database KED9 controlfile reuse datafile '/u01/oradata/KED9/system_01.dbf' size 250M undo tablespace undo_tbs datafile '/u02/oradata/KED9/undo_tbs_01.dbf' size 500M logfile group 1 ('/u10/oradata/KED9/redo_g1m1.log') size 25M, . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ; CREATE undo tablespace undo_tbs datafile '/u02/oradata/KED9/undo_tbs_01.dbf' size 500M; CREATE database KED9 controlfile reuse datafile '/u01/oradata/KED9/system_01.dbf' size 250M undo tablespace undo_tbs datafile '/u02/oradata/KED9/undo_tbs_01.dbf' size 500M logfile group 1 ('/u10/oradata/KED9/redo_g1m1.log') size 25M, . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ; CREATE undo tablespace undo_tbs datafile '/u02/oradata/KED9/undo_tbs_01.dbf' size 500M;

  10. UNDO Tablespace • No permanent objects allowed (ORA-30022) • You can change data file size, add data files • You can change data file properties • You can alter tablespace for on-line backups • You cannot offline an active UNDO tablespace • You cannot change extent sizes

  11. Automatic Undo Segments • Name of AUS • System Generated • _SYSSMUn$ (n is the undo segment number, usn) • Number of AUS • Initially depends on SESSIONS parameter • Minimum required AUS are brought online at startup • More AUS are brought online, or created, as needed, provided undo space is available • One Transaction per AUS is the desired goal • Two extents per AUS to start with (minextents 2)

  12. Automatic Undo Segments • Dynamic Extents Transfer • Reusing expired (or unexpired) undo extents from other undo segments • Shrinking Undo Segments • Every 12 hours SMON shrinks idled undo segments • Foreground processes signal SMON to shrink undo segments when more undo space is needed • Controlling Use of Undo • Use UNDO_POOL directive in Resource Manager • UNDO quota works similar to tablespace quotas

  13. Initialization Parameters • COMPATIBLE = 9.0.0 (to use AUM) • UNDO_MANAGEMENT = <auto|manual> • UNDO_TABLESPACE = <ts_name> • UNDO_RETENTION = <seconds|900> • UNDO_SUPPRESS_ERRORS = <false|true>

  14. UNDO_MANAGEMENT • Auto: • Oracle deals with undo segments • Manual: • DBA deals with rollback segments • Not dynamic, instance restart needed when changed

  15. UNDO_TABLESPACE • To use at instance startup (undo_tablespace=<UndoTS>) • If specified Undo TS is not available, any other available Undo TS is used. If none present, SYSTEM TS is used with a warning in alert.log: ***Warning - Executing transaction without active Undo Tablespace

  16. UNDO_TABLESPACE • When creating new database, if Undo TS is specified but no ‘undo tablespace’ in ‘CREATE DATABASE’ command, the database creation fails. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-30045: No undo tablespace name specified • Undo TS Can be changed dynamically (not advisable) alter system set undo_tablespace = <New Undo TS Name>;

  17. UNDO_RETENTION • Duration to preserve undo information after commits • In seconds, defaults to 900 (15 minutes) • Max value is (2³² - 1) seconds • Dynamic at System level • Affects Undo tablespace sizing decision • Not 100% guaranteed UNDO_RETENTION and adequately sized Undo Tablespace can minimize occurrence of ORA-1555 error

  18. UNDO_SUPPRESS_ERRORS • FALSE (default): • Reports as error any manual management operation related to automatic undo segments • TRUE: • Reports success for all such operations without actually carrying them out • Dynamic at System and Session level

  19. New UNDO Views • DBA_UNDO_EXTENTS • Lists the commit times for each extent in the undo tablespace (from Oracle9i Database Reference, Release 1) • Describes the extents comprising the segments in all undo tablespaces in the database (from Oracle9i Database Reference, Release 2) • V$UNDOSTAT • Statistics for monitoring and tuning Undo space

  20. DBA_UNDO_EXTENTS OWNER CHAR(3) SEGMENT_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NOT NULL NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER COMMIT_JTIME NUMBER COMMIT_WTIME VARCHAR2(20) STATUS VARCHAR2(9)

  21. DBA_UNDO_EXTENTS • COMMIT_JTIME • Julian date form • COMMIT_WTIME • Formatted Wall Clock time • STATUS column to show extent as • ACTIVE • UNEXPIRED • EXPIRED • STATUS may show EXPIRED when you expected it to be UNEXPIRED • Commit times will return NULLS in 9i R2

  22. V$UNDOSTAT BEGIN_TIME DATE -- Sample start date/time END_TIME DATE -- Sample end date/time UNDOTSN NUMBER -- Last Active Undo TS Number UNDOBLKS NUMBER -- Undo blocks used TXNCOUNT NUMBER -- Number of Transactions in sample MAXQUERYLEN NUMBER -- MAX Query Length MAXCONCURRENCY NUMBER -- Max Concurrency UNXPSTEALCNT NUMBER -- Attempts to steal un-expired blocks UNXPBLKRELCNT NUMBER -- Un-expired blocks released UNXPBLKREUCNT NUMBER -- Un-expired blocks reused EXPSTEALCNT NUMBER -- Attempts to steal expired blocks EXPBLKRELCNT NUMBER -- Expired blocks released EXPBLKREUCNT NUMBER -- Expired blocks reused SSOLDERRCNT NUMBER -- Snapshot Old Error Count NOSPACEERRCNT NUMBER -- No Space Left Error Count BEGIN_TIME DATE -- Sample start date/time END_TIME DATE -- Sample end date/time UNDOTSN NUMBER -- Last Active Undo TS Number UNDOBLKS NUMBER -- Undo blocks used TXNCOUNT NUMBER -- Number of Transactions in sample MAXQUERYLEN NUMBER -- MAX Query Length MAXCONCURRENCY NUMBER -- Max Concurrency UNXPSTEALCNT NUMBER -- Attempts to steal un-expired blocks UNXPBLKRELCNT NUMBER -- Un-expired blocks released UNXPBLKREUCNT NUMBER -- Un-expired blocks reused EXPSTEALCNT NUMBER -- Attempts to steal expired blocks EXPBLKRELCNT NUMBER -- Expired blocks released EXPBLKREUCNT NUMBER -- Expired blocks reused SSOLDERRCNT NUMBER -- Snapshot Old Error Count NOSPACEERRCNT NUMBER -- No Space Left Error Count

  23. V$UNDOSTAT • Available in both SMU and RBU mode. (From Oracle9i Database Reference Release 1) • Returns null values if using MUM(RBU) mode. (From Oracle9i Database Reference Release 2) • Returns one useless row in 9i R1, if using MUM (RBU) mode. • Returns a cumulative number in ‘txncount’ column in 9i R2. (Bug # 2506744, 3130916) • Reports information in 10 minute intervals • Only when there is a transaction within this interval

  24. V$UNDOSTAT BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT ----------------- ----------------- ---------- ---------- 11/08/04 02:32:23 11/08/04 02:52:23 0 0 11/08/04 02:22:23 11/08/04 02:32:23 0 206 11/08/04 02:12:23 11/08/04 02:22:23 0 203 11/08/04 02:02:23 11/08/04 02:12:23 0 200 11/08/04 01:52:23 11/08/04 02:02:23 0 195 11/08/04 00:32:23 11/08/04 01:52:23 0 0 11/08/04 00:22:23 11/08/04 00:32:23 1 170 11/08/04 00:02:23 11/08/04 00:22:23 0 0 11/07/04 23:52:23 11/08/04 00:02:23 1 160 11/07/04 18:12:23 11/07/04 23:52:23 0 0 11/07/04 18:02:23 11/07/04 18:12:23 0 54 11/07/04 17:52:23 11/07/04 18:02:23 0 49 11/07/04 15:52:23 11/07/04 17:52:23 0 0 BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT ----------------- ----------------- ---------- ---------- 11/08/04 02:32:23 11/08/04 02:52:23 0 0 11/08/04 02:22:23 11/08/04 02:32:23 0 206 11/08/04 02:12:23 11/08/04 02:22:23 0 203 11/08/04 02:02:23 11/08/04 02:12:23 0 200 11/08/04 01:52:23 11/08/04 02:02:23 0 195 11/08/04 00:32:23 11/08/04 01:52:23 0 0 11/08/04 00:22:23 11/08/04 00:32:23 1 170 11/08/04 00:02:23 11/08/04 00:22:23 0 0 11/07/04 23:52:23 11/08/04 00:02:23 1 160 11/07/04 18:12:23 11/07/04 23:52:23 0 0 11/07/04 18:02:23 11/07/04 18:12:23 0 54 11/07/04 17:52:23 11/07/04 18:02:23 0 49 11/07/04 15:52:23 11/07/04 17:52:23 0 0 Not a 10 minute interval if TXNCOUNT = 0 TXNCOUNT column is cumulative

  25. V$UNDOSTAT B_TIME E_TIME UNDOBLKS TXNCOUNT EXPSTEALCNT EXPBLKRELCNT EXPBLKREUCNT ----------- ------------ ---------------- ---------------- ------------------- ------------------------- ------------------------- 13:00:27 13:10:27 31636 138 27 11776 0 12:50:27 13:00:27 18606 35 5 15864 0 12:40:27 12:50:27 18571 17 3 15864 0 12:30:27 12:40:27 18570 12 1 5112 0 12:20:27 12:30:27 18569 3 0 0 0 12:10:27 12:20:27 9313 2 0 0 0 12:00:27 12:10:27 9269 3 0 0 0 EXPSTEALCNT > 0 • Dynamic Extent Transfer B_TIME E_TIME UNDOBLKS TXNCOUNT EXPSTEALCNT EXPBLKRELCNT EXPBLKREUCNT ----------- ------------ ---------------- ---------------- ------------------- ------------------------- ------------------------- 13:00:27 13:10:27 31636 138 27 11776 0 12:50:27 13:00:27 18606 35 5 15864 0 12:40:27 12:50:27 18571 17 3 15864 0 12:30:27 12:40:27 18570 12 1 5112 0 12:20:27 12:30:27 18569 3 0 0 0 12:10:27 12:20:27 9313 2 0 0 0 12:00:27 12:10:27 9269 3 0 0 0 EXPSTEALCNT > 0 => Dynamic Extent Transfer

  26. UNDO Tablespace Sizing • Monitor V$UNDOSTAT • Number of Transactions • Number of Undo Blocks consumed • Maximum Query length • Formula: Undo Space in Bytes = (UR * UDBPS * DB_Block Size) + Overhead UR = Undo Retention Time in Seconds UDBPS = Undo Blocks used Per Second Overhead = One DB block for metadata

  27. Custom View for V$UNDOSTAT REM – Run as SYS CREATE OR REPLACE VIEW vw_undostat AS SELECT * FROM v$undostat WHERE txncount != 0; CREATE PUBLIC SYNONYM vw_undostat FOR vw_undostat;

  28. UNDO Tablespace Sizing - 1 SELECT to_char(min(begin_time),'MM/DD/YYYY HH24:MI:SS') "Begin Time", to_char(max(end_time),'MM/DD/YYYY HH24:MI:SS') "End Time", (max(end_time)-min(begin_time))*24*60*60 "Seconds", sum(undoblks) "UndoBlks", ceil(sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60)) "UDBPS", (max(txncount) - min(txncount)) "Xactions", max(maxquerylen) "MaxQryLen" FROM vw_undostat; Begin Time End Time Seconds UndoBlks UDBPS Xactions MaxQryLen ------------------------- ------------------------- ---------- ------------ ----------- ---------- --------------- 11/07/2004 20:18:15 11/08/2004 21:36:25 91090 693712 8 44393 1973 NOTE: From Oracle9i Release 2 (9.2.0.4) database on AIX 5.2

  29. UNDO Tablespace Sizing - 2 -- Undo TS Sizing based on Average Undo generation -- and Max Query Length SELECT max(maxquerylen) "MaxQryLen", ceil(sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60)) * max(maxquerylen) "UndoTSBlocks" FROM v$undostat / MaxQryLen UndoTSBlocks ------------------ ------------------ 7289 29156

  30. UNDO Tablespace Sizing - 3 -- Undo TS sizing for Current Load and Current Undo SELECT rd AS “Retention”, (rd * (udbps * overhead) + overhead) as "Bytes" FROM (SELECT value AS RD FROM v$parameter WHERE name = 'undo_retention'), (SELECT (sum (undoblks) / sum ( ((end_time - begin_time) * 86400))) as UDBPS FROM v$undostat), (SELECT value AS OVERHEAD FROM v$parameter WHERE name = 'db_block_size') / Retention Bytes ----------------- -------------- 3600 88515698.5

  31. What is Flashback Query? • Mechanism to view data as it existed at a point in time in the past • Past data can be viewed as of a timestamp or System Change Number (SCN) • Using Automatic Undo Management is strongly recommended

  32. What can Flashback Query do? • Recover from accidental data modification • Extract data as of past time (export) • Compare current data with data in the past • Track data changes

  33. FBQ: How it Works? • Relies on Oracle’s read consistency model • Undo information in undo segments is used to construct past data • Sufficient Undo information MUST be available for FBQ to work

  34. FBQ: How it Works? • SMON maintains an internal table to map timestamp to SCN updating it every 5 minutes to record current timestamp and SCN • The internal table (sys.smon_scn_time) can hold data for up to 5 days (of instance uptime) and is persistent across startups • SCN is used to reconstruct past data from Undo segments

  35. FBQ: How it Works? • Oracle9i Release 1: • FBQ must be enabled at Session level • Oracle9i Release 2: • Privileges and enhanced SQL syntax can be used • New package: DBMS_FLASHBACK • As SYS, grant execute privilege to user

  36. DBMS_FLASHBACK • Procedures: • ENABLE_AT_TIME exec dbms_flashback.enable_at_time (past_date); exec dbms_flashback.enable_at_time (to_timestamp ('10-MAR-2002:11:47:00','DD-MON-YYYY:HH24:MI:SS')); • ENABLE_AT_SYSTEM_CHANGE_NUMBER exec dbms_flashback.enable_at_system_change_number (23488); • DISABLE exec dbms_flashback.disable;

  37. DBMS_FLASHBACK • Function: • GET_SYSTEM_CHANGE_NUMBER SQL> SELECT dbms_flashback.get_system_change_number 2 FROM dual; GET_SYSTEM_CHANGE_NUMBER ------------------------------------------------ 5.98E+12 SQL> set numwidth 18 SQL> / GET_SYSTEM_CHANGE_NUMBER ------------------------------------------------ 5976736332383

  38. FBQ : Oracle9i Release 2 • DBMS_FLASHBACK Package is still available • No need to enable FB at session level • SQL syntax has a Flashback Clause: select * from <table> AS OF <SCN|TIMESTAMP> <expression> where ……….

  39. FBQ : Oracle9i Release 2 select * from emp as of scn 23478 where emp_id = 100; select * from emp as of timestamp sysdate – 1/24; select * from emp minus select * from emp as of timestamp trunc(sysdate);

  40. FBQ : Oracle9i Release 2 • Object Privilege grant FLASHBACK on a_table to you; • System Privilege grant FLASHBACK ANY TABLE to me; (except data dictionary tables) NOTE: DBA role has the system privilege

  41. FBQ: With Export • Export options • FLASHBACK_SCN • Export Data as of SCN exp tables=employees flashback_scn = 3853558 file=emp.dmp • FLASHBACK_TIME • Export Data as of TimeStamp exp tables=test flashback_time=‘”2004-10-14 13:30:00”’ file=test.dmp

  42. FBQ: Limitations • SYS cannot use DBMS_FLASHBACK procedures • Specifying FB time can only find flashback data to the nearest 5 minute interval • You cannot flashback more than 5 days of instance uptime

  43. FBQ: Limitations • Flashback not possible beyond the time of DDL operation that changed, or altered, the table • Not possible for remote table accessed via DB link

  44. Understanding Oracle9i Automatic Undo Management andFlashback Query Demonstration Q & A kirtikumar_deshpande@yahoo.com

More Related