Understanding Oracle9i Automatic Undo Management and Flashback Query

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 ltautomanualgt
• UNDO_TABLESPACE ltts_namegt
• UNDO_RETENTION ltseconds900gt
• UNDO_SUPPRESS_ERRORS ltfalsetruegt

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_tablespaceltUndoT
  Sgt)
• 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 ltNew Undo TS
  Namegt

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)
• VUNDOSTAT
• 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
VUNDOSTAT

• 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
VUNDOSTAT

• 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
VUNDOSTAT

• BEGIN_TIME END_TIME UNDOBLKS
  TXNCOUNT
• ----------------- ----------------- ----------
  ----------
• 11/08/04 023223 11/08/04 025223 0
  0
• 11/08/04 022223 11/08/04 023223 0
  206
• 11/08/04 021223 11/08/04 022223 0
  203
• 11/08/04 020223 11/08/04 021223 0
  200
• 11/08/04 015223 11/08/04 020223 0
  195
• 11/08/04 003223 11/08/04 015223 0
  0
• 11/08/04 002223 11/08/04 003223 1
  170
• 11/08/04 000223 11/08/04 002223 0
  0
• 11/07/04 235223 11/08/04 000223 1
  160
• 11/07/04 181223 11/07/04 235223 0
  0
• 11/07/04 180223 11/07/04 181223 0
  54
• 11/07/04 175223 11/07/04 180223 0
  49
• 11/07/04 155223 11/07/04 175223 0
  0
• Not a 10 minute interval if TXNCOUNT 0
• TXNCOUNT column is cumulative

BEGIN_TIME END_TIME UNDOBLKS
TXNCOUNT ----------------- -----------------
---------- ---------- 11/08/04 023223 11/08/04
025223 0 0 11/08/04 022223
11/08/04 023223 0 206 11/08/04
021223 11/08/04 022223 0
203 11/08/04 020223 11/08/04 021223
0 200 11/08/04 015223 11/08/04 020223
0 195 11/08/04 003223 11/08/04
015223 0 0 11/08/04 002223
11/08/04 003223 1 170 11/08/04
000223 11/08/04 002223 0
0 11/07/04 235223 11/08/04 000223 1
160 11/07/04 181223 11/07/04 235223
0 0 11/07/04 180223 11/07/04
181223 0 54 11/07/04 175223
11/07/04 180223 0 49 11/07/04
155223 11/07/04 175223 0
0
25
VUNDOSTAT

• B_TIME E_TIME UNDOBLKS TXNCOUNT EXPSTEALCNT
  EXPBLKRELCNT EXPBLKREUCNT
• ----------- ------------ ----------------
  ---------------- -------------------
  ------------------------- -----------------------
  --
• 130027 131027 31636
  138 27
  11776
  0
• 125027 130027 18606
  35 5
  15864
  0
• 124027 125027 18571
  17 3
  15864
  0
• 123027 124027 18570
  12 1
  5112
  0
• 122027 123027 18569
  3 0
  0
  0
• 121027 122027 9313
  2 0
  0
  0
• 120027 121027 9269
  3 0
  0
  0
• EXPSTEALCNT gt 0
• gt Dynamic Extent Transfer

• B_TIME E_TIME UNDOBLKS TXNCOUNT EXPSTEALCNT
  EXPBLKRELCNT EXPBLKREUCNT
• ----------- ------------ ----------------
  ---------------- -------------------
  ------------------------- -----------------------
  --
• 130027 131027 31636
  138 27
  11776
  0
• 125027 130027 18606
  35 5
  15864
  0
• 124027 125027 18571
  17 3
  15864
  0
• 123027 124027 18570
  12 1
  5112
  0
• 122027 123027 18569
  3 0
  0
  0
• 121027 122027 9313
  2 0
  0
  0
• 120027 121027 9269
  3 0
  0
  0
• EXPSTEALCNT gt 0
• Dynamic Extent Transfer

26
UNDO Tablespace Sizing

• Monitor VUNDOSTAT
• 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 VUNDOSTAT

• REM Run as SYS
• CREATE OR REPLACE VIEW vw_undostat
• AS
• SELECT
• FROM vundostat
• 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
  HH24MISS') "Begin Time",
• to_char(max(end_time),'MM/DD/YYYY
  HH24MISS') "End Time",
• (max(end_time)-min(begin_time))246060
  "Seconds",
• sum(undoblks) "UndoBlks",
• ceil(sum(undoblks)/((max(end_time)-min(begin
  _time))246060)) "UDBPS",
• (max(txncount) - min(txncount)) "Xactions",
• max(maxquerylen) "MaxQryLen"
• FROM
• vw_undostat
• Begin Time End Time
  Seconds UndoBlks UDBPS Xactions MaxQryLen
• ------------------------- -----------------------
  -- ---------- ------------ -----------
  ---------- ---------------
• 11/07/2004 201815 11/08/2004 213625 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_tim
  e))246060))
• max(maxquerylen) "UndoTSBlocks"
• FROM
• vundostat
• /
• 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 vparameter
• WHERE name 'undo_retention'),
• (SELECT (sum (undoblks) /
• sum ( ((end_time - begin_time)
  86400))) as UDBPS
• FROM vundostat),
• (SELECT value AS OVERHEAD FROM vparameter
• WHERE name 'db_block_size')
• /
• Retention Bytes
• ----------------- --------------
• 3600 88515698.5

31
(No Transcript)
32
(No Transcript)
33
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

34
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

35
FBQ How it Works?

• Relies on Oracles read consistency model
• Undo information in undo segments is used to
  construct past data
• Sufficient Undo information MUST be available for
  FBQ to work

36
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

37
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

38
DBMS_FLASHBACK

• Procedures
• ENABLE_AT_TIME
• exec dbms_flashback.enable_at_time
  (past_date)
• exec dbms_flashback.enable_at_time
  (to_timestamp
• ('10-MAR-2002114700','DD-MON-YYYYHH24
  MISS'))
• ENABLE_AT_SYSTEM_CHANGE_NUMBER
• exec dbms_flashback.enable_at_system_change_n
  umber
• (23488)
• DISABLE
• exec dbms_flashback.disable

39
DBMS_FLASHBACK

• Function
• GET_SYSTEM_CHANGE_NUMBER
• SQLgt SELECT dbms_flashback.get_system_change_numbe
  r
• 2 FROM dual
• GET_SYSTEM_CHANGE_NUMBER
• ------------------------------------------------
• 
  5.98E12
• SQLgt set numwidth 18
• SQLgt /
• GET_SYSTEM_CHANGE_NUMBER
• ------------------------------------------------
• 
  5976736332383

40
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 lttablegt
• AS OF ltSCNTIMESTAMPgt ltexpressiongt
• where .

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

42
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

43
FBQ With Export

• Export options
• FLASHBACK_SCN
• Export Data as of SCN
• exp tablesemployees flashback_scn 3853558
  fileemp.dmp
• FLASHBACK_TIME
• Export Data as of TimeStamp
• exp tablestest flashback_time2004-10-14
  133000
• filetest.dmp

44
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

45
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

46
Understanding Oracle9i Automatic Undo
Management andFlashback Query

• Demonstration
• Q A
• kirtikumar_deshpande_at_yahoo.com