Understanding oracle9 i automatic undo management and flashback query
This presentation is the property of its rightful owner.
Sponsored Links
1 / 46

Understanding Oracle9 i Automatic Undo Management and Flashback Query PowerPoint PPT Presentation


  • 186 Views
  • Uploaded on
  • Presentation posted in: General

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.

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


    Understanding oracle9 i automatic undo management and flashback query1

    Understanding Oracle9i Automatic Undo Management andFlashback Query

    Demonstration

    Q & A

    [email protected]


  • Login