Re: Duplicate v9i database to P-I-T

Finally got an answer as to whether they did a DELETE or TRUNCATE. Neither. They just dropped the table thinking it was a temp table, but didn't verify first.

On Tue, Mar 21, 2023 at 1:35 PM Jeremiah Cetlin Wilton < jcwilton93_at_earlham.edu> wrote:

> Check out select... as of...; and ...versions between... also flashback
> transaction query. This stuff is automatically on in any 9i database using
> automatic undo.
>
> If I were to bet, I'd say your best bet is logminer as long as they didn't
> truncate.
>
> If they truncated, then you need to figure out some way to use that backup
> and roll forward.
>
> And here's some relevant anapestic tetrameter to go with this half-baked
> answer.
>
>
> https://web.archive.org/web/20120502011355/http://www.bluegecko.net/oracle/too-many-flashbacks/
>
> Best of luck
>
> Jeremiah
>
>
>
> On Tue, Mar 21, 2023 at 12:10 PM Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
>> Yes, log miner was suggested. I'm trying to find out when the row
>> removal occurred and if they used DELETE or TRUNCATE. I haven't used log
>> miner for several years, but I recall it saving us when I did use it.
>>
>> Sandy
>>
>> On Tue, Mar 21, 2023 at 1:02 PM Jeremiah Wilton <jcwilton93_at_earlham.edu>
>> wrote:
>>
>>> Well that’s not Flashback Query, but it was available in 9i too. Easy
>>> mistake , since Oracle called five or six different features “Flashback.” Anyway
>>> it’s probably too late. Has anyone suggested logminer?
>>>
>>> Jeremiah
>>>
>>> On Mar 21, 2023, at 11:44 AM, Michael Pecoraro <mikejp_at_buffalo.edu>
>>> wrote:
>>>
>>> 
>>> Oracle9i Supplied PL/SQL Packages and Types Reference
>>> Release 2 (9.2)
>>> DBMS_FLASHBACK
>>> https://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_flashb.htm#1656
>>>
>>>
>>> *Michael J Pecoraro*
>>> Database Administrator | Enterprise Infrastructure Services
>>> University at Buffalo Information Technology (UBIT)
>>> 334 Computing Center | Buffalo, NY 14260
>>> 716-645-7765
>>> ------------------------------
>>> *From:* Sandra Becker <sbecker6925_at_gmail.com>
>>> *Sent:* Tuesday, March 21, 2023 2:35 PM
>>> *To:* Michael Pecoraro <mikejp_at_buffalo.edu>
>>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* Re: Duplicate v9i database to P-I-T
>>>
>>> Doesn't look like flashback was available in 9i. I see it with our 10g
>>> databases. I'll see if logminer might work. I'm not sure how they deleted
>>> the rows.
>>>
>>> Thanks for the suggestions.
>>>
>>> Sandy
>>>
>>> On Tue, Mar 21, 2023 at 12:08 PM Michael Pecoraro <mikejp_at_buffalo.edu>
>>> wrote:
>>>
>>> It likely has been too long at this point, but I believe flashback
>>> existed in 9i. You may be able to select the data using flashback query
>>> and insert it into a new table for analysis.
>>>
>>> Mike
>>>
>>>
>>> ---
>>>
>>> *Michael J Pecoraro*
>>>
>>> Database Administrator | Enterprise Infrastructure Services
>>> University at Buffalo Information Technology (UBIT)
>>>
>>> ------------------------------
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>> on behalf of Sandra Becker <sbecker6925_at_gmail.com>
>>> *Sent:* Tuesday, March 21, 2023 1:20 PM
>>> *To:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* Duplicate v9i database to P-I-T
>>>
>>> OS: Solaris 5.8
>>> Oracle: 9.2.0.8 ( I knew this would bite us one day, but I have no
>>> control over it.)
>>>
>>> Problem: Someone purged all the data from a single table, now they want
>>> it recovered to a P-I-T
>>> Backups are still on disk, but have aged out. When I list backup of
>>> datafile 1, it shows the oldest backup is one day after the one I need.
>>> Unfortunately, you can't catalog backups in 9i. We also no longer have
>>> Oracle support because the company thought it was a waste of money. Is
>>> there anything I can do to duplicate a new database from the backups I need?
>>>
>>> Also unfortunate that the production database and the clone are on the
>>> same server. Again, I had no choice in the matter. Am I totally screwed
>>> in recovering that table?
>>>
>>> --
>>> Sandy B.
>>>
>>>
>>>
>>> --
>>> Sandy B.
>>>
>>> CAUTION: This email originated from outside of the organization. Do not
>>> click links or open attachments unless you recognize the sender and know
>>> the content is safe.
>>>
>>>
>>
>> --
>> Sandy B.
>>
>>
>> CAUTION: This email originated from outside of the organization. Do not
>> click links or open attachments unless you recognize the sender and know
>> the content is safe.
>>
>>

-- 
Sandy B.

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