Re: not including temp neither undo tablespace in full backup

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Wed, 12 Oct 2005 13:47:54 -0500
Message-ID: <434D5A5A.9040200@jcpenney.com>

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

>>Riyaj You need redo files, but not undo neither temp tablespaces.

This is a dangerous and very misleading statement to make. Hopefully, newbies will read the full thread before accepting your advice.

Just because one test case is successful, does not mean that the theory is correct. As Jonathan Lewis would say "It takes only one test case to disprove a theory". Without undo tablespace, there is a very high potential for data corruption and instance will shutdown during SMON's instance recovery. In fact, Oracle support would let you set couple of parameters to open the database with corruption, if you don't have undo tablespace backup. But right after that you must rebuild your database if you want further support.

Database can not rollback the outstanding transactions without rollback segments during instance recovery. Consistent view of the data can not be recreated either. Expect corruption all around the database in a very active database.

Not satisfied? Read Tom Kyte's thread thoroughly:

do not, repeat, do not take this as a way to get out of backing up UNDO, just consider UNDO as important as your own tables. Always back it up. ALWAYS.

http://asktom.oracle.com/pls/ask/f?p=4950:8:12294222471961948103::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5669213349582

Miniurl, in case, you can't access the above link:

http://tinyurl.com/97hq3

You want test case, try this:

Session #1:

Create a table with say million rows. Update say 25% of the table.
Don't commit.

Session #2:

   Shutdown immediate;
   Take a backup excluding undo tablespace.    Remove data files and undo files..
   Restore..Startup.. Check alert log (Instance should fail when smon tries to do rollback portion of the instance recovery)..If not, just try to do count(*) from the table created in session #1..

You will then agree that undo tablespace is absolutely needed to be backed up!! Do NOT, I repeat, do NOT try this in any database, that you can not afford to lose!!

Thanks

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

Juan Carlos Reyes Pacheco wrote:

>Riyaj You need redo files, but not undo neither temp tablespaces.
>A few times I lose them and recreated successfully.
>
>You will need undo, if you abort your database, otherwise you don't need.
>(obviously I'm not talking in rac environment, I don't have idea about that)
>
>This is not a theory I tested it agains my will previously.
>:)
>
>
>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 12 2005 - 13:50:10 CDT