RE: Transportable Tablespaces as backup?

Kirti, Jonathan - Thanks for your responses. I was able to validate the process with a small test tablespace.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, December 18, 2002 8:34 PM To: Multiple recipients of list ORACLE-L

I did that once. And did not run into any problems. HP-UX 11.0 Oracle 8.1.7.4.
Worked rather well to my surprise. Never thought that TTS could be so useful!
For me, the time consuming part was altering the source TBS to read-only mode as the database was rather busy at the time. We also had two databases on the same server share one datafile! Both using it in read-only mode. TTS is a neat thing :)

• Kirti

-----Original Message-----
Sent: Wednesday, December 18, 2002 3:26 PM To: Multiple recipients of list ORACLE-L

Someone asked the same question at one
of my seminars recently, and I couldn't think of a good reason why - for THEIR circumstances - it would be a bad idea.

Of course you do have to drop the messed tablespace including contents - which can be quite time-consuming, especially with DMTs and/or tablespace quotas - but if the tablespaces are truly 100% independent of the rest of the database it sounds okay.

The only thing I would test (other than a check to see that it seems to work) is whether there is some strange overhead in latching that takes place anywhere because the tablespaces are 'known' to be alien "pluggable" tablespaces. I believe that the x$bh flag has a special bit set to show that the blocks didn't come from the original database - I don't know if this causes any extra codepath to be used. (It might actually shorten it, of course, as Oracle would know that any block in that tablespace couldn't possibly need any local work done for read consistency).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 18 December 2002 21:15

>Has anyone used transportable tablespaces as a special backup method?
>
>Periodically I get a request from our applications people to "backup
these
>tables before we run this year-end program so if something goes wrong
we can
>reset everything." And the attention-getter is "if we can't recover,
you
>won't get paid again."
> - Usually nothing goes wrong (thankfully).
> - We do full backups, obviously, but there are unpleasant
consequences
>for rolling back an enterprise-scale database. The system goes back
for
>everyone. Obviously we can do a TSPITR over on a test system, but I
don't
>like to rely on that alone.
> - Export works great for small to medium size tables.
> - Export of large tables is fine, but my experience says that
importing a
>really large table can take a LOOONG time, a frightening prospect
during an
>emergency.
> - It occurs to me that making the tablespace read-only and
performing a
>transportable "export" should work great. These large tables are in
their
>own tablespace. The application doesn't any RI in Oracle.
> - Recovery should be a matter of dropping the existing tablespace,
>copying the backup datafile off tape, running the import procedure,
and
>making the tablespace read-write. Much faster than the true import.
>
>Am I missing something? I plan to try this on a test system to make
sure I
>have the right syntax.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).