Re: TSPITR or PITR

I agree that we really can't differentiate between options A, B and D -- they are all Incomplete Database Recoveries. However, option C IS different. Since we are much more likely to know the "wall clock time" as to when the table was dropped than the SCN, B makes more sense than A. B also makes more sense than D because B is quite explicit. [My reading of the OCP Exam Guides is that you may well be presented with more than 1 correct answer but you must select the "best" or "most appropriate"
answer].

If we take C :
You would have recovered that one table to the point-in-time before it was dropped.
However, other tables, still in your database, are as-of the *current* point-in-time.
Now, if the recovered table was not an important table OR you did not have multi-table transactions [who doesn't ?] you could very well carry on. But if you've already seen a number of transactions proceeding in the database after the table was dropped ... meaning that this table is "inconsistent" with the
rest of the database ....
Oh! But, of course, multi-table transactions wouldn't have proceeded after the table was dropped. So, as long as some silly developer or "analyst" hasn't updated
data directly in other tables, no other table is inconsistent with this one, right ?

Another point-of-view is that TSPITR has more restrictions than complete database recovery. Therefore, where possible, complete database recovery is the preferred option. Wwhat if the "dropped table" has varray columns, nested
tables, snapshots etc --- then TSPITR can't be used. Given the limited information
in the question C is not the "best answer".

I have been doing point-in-time recoveries and where necessary have done single table / tablespace recoveries [again, like you, using the old methods without the advantage of looking up the sys.tspitr% views but not for important and production databases].
Whether it is an OCP question [where you must choose the "best answer" out of more than one possible answers] or real-life, TSPITR should not be the first
option unless you have complete information about the table and how you are going to recover the table into your production database or you *cannot* do a full database recovery [e.g. damagement or other constraints don't allow you the time].

There will be situations where you can or must do TSPITR. There will be situations where you would prefer to do a Full [albeit Incomplete] Database Recovery.

Hemant

At 07:09 AM 29-06-03 -0800, you wrote:
Silly me! With a bunch of years of production DBA experience encountering problems exactly like this one (except it was someone else dropping the important table) as well as problems far more complicated, I can't decide what answer they are seeking here! What's more, I would have chosen the wrong answer...
Forgive me, but how exactly are these test makers differentiating between the phrases "change-based recovery" and "point-in-time recovery"? Or "cancel-based recovery" and "point-in-time recovery"? My understanding is that both change-based and cancel-based recovery are point-in-time recoveries. That is, recoveries that were halted prior to the current point-in-time, also known as "incomplete recoveries". Since the only point-in-time recovery method that is missing from the list is "time-based recovery", I have to assume the "point-in-time recovery" and "time-based recovery" are one and the same, perhaps? Just semantics, I guess, but in a multiple-choice test, misunderstanding the semantics is the difference between right and wrong. Should be an essay question anyway...

---

The response of "tablespace point-in-time recovery" has been my choice each
time these situations have occurred, in real life.  I haven't necessarily
used the mechanism that Oracle produced in Oracle8.0, mostly because the
times I encountered the situation were prior to Oracle8.0.  But the idea is
that you restore a "clone" database (consisting of all tablespaces
containing rollback segments and the datafiles containing the table in
question) and recover that new "clone" database forward to the point-in-time
just prior to the DROP TABLE.  Then export the table data from the "clone"
and import into the production database.
Therefore, my response on the test ("tablespace point-in-time recovery"),
coming from successful experience in production environments, would have
been marked incorrect on this test.  C'est la vie (or more appropriately
"C'est la certification")...


on 6/29/03 4:34 AM, Hemant K Chitale at [EMAIL PROTECTED] wrote:



>

> No, TSPITR should not be the preferred method.  Why not ? Because it

> doesn't guarantee that you have achieved consistency of data across 

objects.


> You must still export the "related objects" and bring them in.

>

> Suppose you have a transactions which updates tables in three different

> tablespaces.  A TSPITR for one tablespace would have one table "older"

> than the other two.

> Similarly, indexes in a seperate tablespace are inconsistent with the data

> and must be recreated.

>

> TSPITR is to be used only when you cannot do a full recovery AND you

> can gaurantee that you can recover data consistency.

>

> Hemant

>

> At 11:09 PM 28-06-03 -0800, you wrote:

>> Hello list I came across the following question in the TMH exam guide

>> for 1z0-032:

>>

>> Chris, a DBA, while performing maintenance tasks accidentally drops a

>> very important table. What is the best method available for Chris to

>> recover this table if he is aware of the time when the table was

>> dropped?

>>

>> A . Change-based recovery

>>

>> B*. Point-in-time recovery

>>

>> C . Tablespace point-in-time recovery

>>

>> D . Cancel-based recovery

>>

>> Answer : Point-in-time recovery

>>

>> Wouldn't TSPITR be the best method available in general ?

>>

>>

>>

>>

>> --

>> Please see the official ORACLE-L FAQ: http://www.orafaq.net

>> --

>> Author: <[EMAIL PROTECTED]

>>   INET: [EMAIL PROTECTED]

>>

>> 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: [EMAIL PROTECTED] (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).

>

> Hemant K Chitale

> Oracle 9i Database Administrator Certified Professional

> My personal web site is :  http://hkchital.tripod.com

>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
  INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).