Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: not including temp neither undo tablespace in full backup

Re: not including temp neither undo tablespace in full backup

From: Riyaj Shamsudeen <>
Date: Thu, 13 Oct 2005 07:57:33 -0500
Message-ID: <>

 ('binary' encoding is not supported, stored as-is)

    undo tablespace backup is not an option , it is mandatory.     Scenario I described earlier, is just one of many possible scenarios, NOT the only scenario..

    Here is another scenario for you:

        Let us consider the database is in archivelog mode.
            Cold backup on Sunday (Incorrectly undo data files have not 
been backed up)
            Tuesday evening, point-in-time recovery as of 4PM needed.
            Restore the cold backup, make the undo files offline, 
recover database until 4PM.
            At this point, you can not open the database. Since undo 
tablespaces were not restored, no redo will be applied to undo tablespaces.
             That means, there may be many transactions outstanding in 
the database [Oh, yes, even if you comment undo segments, oracle knows about the segments]. Instance recovery can not access the undo segments that were active at 4PM and so instance recovery can NOT complete. Instance will either crash right after the open/ or will not allow you to open (version specific, controlled by few parameters).

           Database is in corrupted state!!! Backup is useless.

    In almost, every scenario, instance recovery can NOT complete without undo segments.

    If you don't want to take undo tablespace backup, then, following are the rules:

  1. Only recovery option is to restore cold backup. No point-in-time recovery possible.
  2. Shutdown before the cold backup is clean. Shutdown immediate will NOT work. immediate mode kills the session and kills the transaction, leaving uncommitted transactions. Transactional mode will work, as the shutdown waits for all the transaction to complete.
  3. Even then, I would shutdown transactional, take all the undo segments offline, undo tablespace offline, shutdown transactional and then do cold backup. [ Yes, I am slightly paranoid, when comes to backup ].

    Risk is so huge, just backup undo tablespace also.


Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (ver 7.0 - 9i)
Allocation & Assortment planning systems JCPenney

Juan Carlos Reyes Pacheco wrote:

>Riyaj, First I need to clarify I'm not advicing it, this is only one idea I had.
>Well, I t hink I'll do the test tomorroy, but what do you think
>about this idea,
>shutdown immediate
>shutdown immediate;
>I think this will solve (in theory) the situation you says or I'm wrong.

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

Received on Thu Oct 13 2005 - 08:01:55 CDT

Original text of this message