understanding oracle9 i automatic undo management and flashback query
Download
Skip this Video
Download Presentation
Understanding Oracle9 i Automatic Undo Management and Flashback Query

Loading in 2 Seconds...

play fullscreen
1 / 46

Understanding Oracle9i Automatic Undo Management and Flashback Query - PowerPoint PPT Presentation


  • 223 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Understanding Oracle9i Automatic Undo Management and Flashback Query' - tracey


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
understanding oracle9 i automatic undo management and flashback query
Understanding Oracle9i Automatic Undo Managementand Flashback Query

Kirtikumar Deshpande

CLTOUG

July 14, 2005

about me
About Me
  • Senior Oracle DBA
    • Verizon Information Services
      • Phone Directories Publication
agenda
Agenda
  • Automatic Undo Management
  • Flashback Query Feature
  • Demonstration
  • Q & A
new terminology
New Terminology
  • Undo Segment, not Rollback Segment
  • Automatic Undo Management (AUM)
    • System Managed Undo (SMU)
  • Manual Undo Management (MUM)
    • Rollback Undo (RBU)
automatic v s manual undo
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
automatic v s manual undo1
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
automatic v s manual undo2
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
undo tablespace creation
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
undo tablespace creation1
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;

undo tablespace
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
automatic undo segments
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)
automatic undo segments1
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
initialization parameters
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>
undo management
UNDO_MANAGEMENT
  • Auto:
    • Oracle deals with undo segments
  • Manual:
    • DBA deals with rollback segments
  • Not dynamic, instance restart needed when changed
undo tablespace1
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

undo tablespace2
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>;

undo retention
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

undo suppress errors
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
new undo views
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
dba undo extents
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)

dba undo extents1
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
v undostat
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

v undostat1
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
v undostat2
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

v undostat3
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

undo tablespace sizing
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

custom view for v undostat
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;

undo tablespace sizing 1
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

undo tablespace sizing 2
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

undo tablespace sizing 3
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

what is flashback query
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
what can flashback query do
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
fbq how it works
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
fbq how it works1
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
fbq how it works2
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
dbms flashback
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;

dbms flashback1
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

fbq oracle9 i release 2
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 ……….

fbq oracle9 i release 21
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);

fbq oracle9 i release 22
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

fbq with export
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

fbq limitations
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
fbq limitations1
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
ad