FAQ
Good morning,

Background: Windows 2003 / Oracle 10g with auto undo (8K block size)

We have only one datafile (20 GB) in the undo tablespace.
When I ran RMAN backup of this datafile, it created a 20GB backupset file.

I query dba_undo_extents and it shows less than 80 MB used.

SQL> select sum(blocks) from dba_undo_extents;
SUM(BLOCKS)

9824

Any explanation? How do I keep the backup of undo small?

Thanks,
Roger Xu

Search Discussions

  • Guillermo Alan Bort at Dec 15, 2009 at 4:50 pm
    Short answer... don't take a backup of the undo tablespace... it's not
    required for database recovery.

    Alan.-
    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu wrote:

    Good morning,

    Background: Windows 2003 / Oracle 10g with auto undo (8K block size)

    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB backupset file.

    I query dba_undo_extents and it shows less than 80 MB used.

    SQL> select sum(blocks) from dba_undo_extents;
    SUM(BLOCKS)
    -----------
    9824

    Any explanation? How do I keep the backup of undo small?

    Thanks,
    Roger Xu

    --
    http://www.freelists.org/webpage/oracle-l
  • MacGregor, Ian A. at Dec 15, 2009 at 5:07 pm
    The undo tablespace is required for database recovery

    Sent from my iPhone

    On Dec 15, 2009, at 8:51 AM, "Guillermo Alan Bort"
    wrote:
    Short answer... don't take a backup of the undo tablespace... it's
    not required for database recovery.



    Alan.-


    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu wrote:
    Good morning,

    Background: Windows 2003 / Oracle 10g with auto undo (8K block size)

    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB backupset
    file.

    I query dba_undo_extents and it shows less than 80 MB used.

    SQL> select sum(blocks) from dba_undo_extents;
    SUM(BLOCKS)
    -----------
    9824

    Any explanation? How do I keep the backup of undo small?

    Thanks,
    Roger Xu

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Fontana at Dec 15, 2009 at 5:15 pm
    Oracle uses an algorithm to age out used blocks in the undo. Even though they are not in use at any given point in time,
    they must be cleaned out by an Oracle process and I know of nothing that can be done directly to resize or release them.

    Ian is right that they are REQUIRED for database recovery and should be backed up regularly. Using compression
    in your backup script if you're using RMAN should result in significant savings IF the undo is marked as unused
    based upon the algoritm. I've seen 80-90% compression rates for undo datafiles. Your mileage may vary...

    Michael Fontana

    Sr. Technical Consultant

    Enkitec M: 214.912.3709

    enkitec

    oracle_certified_partner
  • Roger Xu at Dec 15, 2009 at 5:17 pm
    This thread is nothing to do with the reason why we need to backup undo. I
    just want to backup the undo segments which are required for recovery but
    not the entire undo datafile. (I know 11g skips commited before image but I
    am at 10g.) Thanks.

    On Tue, Dec 15, 2009 at 11:07 AM, MacGregor, Ian A.
    wrote:
    The undo tablespace is required for database recovery

    Sent from my iPhone

    On Dec 15, 2009, at 8:51 AM, "Guillermo Alan Bort"
    wrote:

    Short answer... don't take a backup of the undo tablespace... it's not
    required for database recovery.



    Alan.-

    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu wrote:

    Good morning,

    Background: Windows 2003 / Oracle 10g with auto undo (8K block size)

    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB backupset file.


    I query dba_undo_extents and it shows less than 80 MB used.

    SQL> select sum(blocks) from dba_undo_extents;
    SUM(BLOCKS)
    -----------
    9824

    Any explanation? How do I keep the backup of undo small?

    Thanks,
    Roger Xu

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremiah Wilton at Dec 15, 2009 at 6:34 pm
    Roger,

    As Mark P. pointed out, RMAN in 10g does not back up regions of
    datafiles that have never been used. However, it seems like at some
    point the whole tablespace filled with undo segments, then some
    segments shrunk or went away. I think RMAN will back up all blocks
    that have ever been part of any segment.

    If you want to make the backup small again, in line with the current
    allocation of undo, then switch to a new undo tablespace and drop the
    old one. This will work until someone does a giant transaction again!

    Or you could take the other advice someone gave and never back up undo
    tablespaces. Maybe skip SYSTEM too! (***JUST KIDDING***).

    Regards,

    Jeremiah Wilton
    Blue Gecko, Inc.
    http://www.bluegecko.net
    On Dec 15, 2009, at 9:17 AM, Roger Xu wrote:

    This thread is nothing to do with the reason why we need to backup
    undo. I just want to backup the undo segments which are required for
    recovery but not the entire undo datafile. (I know 11g skips
    commited before image but I am at 10g.)

    On Tue, Dec 15, 2009 at 11:07 AM, MacGregor, Ian A. > wrote:
    The undo tablespace is required for database recovery
    On Dec 15, 2009, at 8:51 AM, "Guillermo Alan Bort" > wrote:
    Short answer... don't take a backup of the undo tablespace... it's
    not required for database recovery.

    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu
    wrote:
    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB
    backupset file.
    I query dba_undo_extents and it shows less than 80 MB used.
    ...
    Any explanation? ...
    --
    http://www.freelists.org/webpage/oracle-l
  • Roger Xu at Dec 15, 2009 at 7:35 pm
    Thank you all. By the way, here is an example from Tom:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582
    On Tue, Dec 15, 2009 at 12:34 PM, Jeremiah Wilton wrote:

    Roger,

    As Mark P. pointed out, RMAN in 10g does not back up regions of datafiles
    that have never been used. However, it seems like at some point the whole
    tablespace filled with undo segments, then some segments shrunk or went
    away. I think RMAN will back up all blocks that have ever been part of any
    segment.

    If you want to make the backup small again, in line with the current
    allocation of undo, then switch to a new undo tablespace and drop the old
    one. This will work until someone does a giant transaction again!

    Or you could take the other advice someone gave and never back up undo
    tablespaces. Maybe skip SYSTEM too! (***JUST KIDDING***).

    Regards,

    Jeremiah Wilton
    Blue Gecko, Inc.
    http://www.bluegecko.net


    On Dec 15, 2009, at 9:17 AM, Roger Xu wrote:

    This thread is nothing to do with the reason why we need to backup undo.
    I just want to backup the undo segments which are required for recovery but
    not the entire undo datafile. (I know 11g skips commited before image but I
    am at 10g.)

    On Tue, Dec 15, 2009 at 11:07 AM, MacGregor, Ian A. <
    ian_at_slac.stanford.edu> wrote:
    The undo tablespace is required for database recovery

    On Dec 15, 2009, at 8:51 AM, "Guillermo Alan Bort" <
    cicciuxdba_at_gmail.com> wrote:

    Short answer... don't take a backup of the undo tablespace... it's not
    required for database recovery.

    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu <
    wellmetus_at_gmail.com> wrote:
    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB backupset
    file.
    I query dba_undo_extents and it shows less than 80 MB used.
    ...
    Any explanation? ...
    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark at Dec 15, 2009 at 5:22 pm
    The undo tablespace is definitely required for full recovery of the database. First your restore then you roll forward then you rollback at the end to create a consistent image.

    What kind of rman backup is being used? Backup pieces or copy, full or incremental, etc ... I expect at some point the full undo tablespace was used hence the full tablespace data files get backed up.

    From: oracle-l-bounce_at_freelists.org On Behalf Of MacGregor, Ian A.
    Sent: Tuesday, December 15, 2009 12:08 PM
    To: cicciuxdba_at_gmail.com
    Cc: wellmetus_at_gmail.com; oracle-l@freelists.org
    Subject: Re: backup UNDO Tablespace (dba_undo_extents)

    The undo tablespace is required for database recovery

    Sent from my iPhone

    On Dec 15, 2009, at 8:51 AM, "Guillermo Alan Bort" > wrote:

    Short answer... don't take a backup of the undo tablespace... it's not required for database recovery.

    Alan.-

    On Tue, Dec 15, 2009 at 1:20 PM, Roger Xu <wellmetus_at_gmail.com> wrote:
    Good morning,

    Background: Windows 2003 / Oracle 10g with auto undo (8K block size)

    We have only one datafile (20 GB) in the undo tablespace.
    When I ran RMAN backup of this datafile, it created a 20GB backupset file.

    I query dba_undo_extents and it shows less than 80 MB used.

    SQL> select sum(blocks) from dba_undo_extents;
    SUM(BLOCKS)

    9824

    Any explanation? How do I keep the backup of undo small?

    Thanks,
    Roger Xu

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 15, '09 at 4:20p
activeDec 15, '09 at 7:35p
posts8
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase