RE: Duplicate v9i database to P-I-T

You can restore the controlfile from backup sets with: “restore controlfile from ‘<backup set file name>’;” If you don’t know which file contains the controlfile backup, you can just try each one until you get it. Then, according to MOS, the way you can catalog the backup pieces is to install 10g software and bring up the database in MOUNT. Then with 10g RMAN, you can catalog the backup pieces. The important thing is that the version of software has to support the compatible parameter of your 9i database (and don’t change it). Shut the database down, change your ORACLE_HOME back to 9i and you should be able to do the rest.  

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Sandra Becker Sent: Wednesday, March 22, 2023 8:13 AM
To: jcwilton93_at_earlham.edu
Cc: mikejp_at_buffalo.edu; oracle-l <oracle-l_at_freelists.org> Subject: 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 <mailto: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/ <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 <mailto: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 <mailto: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 <mailto: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 <mailto:sbecker6925_at_gmail.com> > Sent: Tuesday, March 21, 2023 2:35 PM
To: Michael Pecoraro <mikejp_at_buffalo.edu <mailto:mikejp_at_buffalo.edu> > Cc: oracle-l <oracle-l_at_freelists.org <mailto: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 <mailto: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 <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > on behalf of Sandra Becker <sbecker6925_at_gmail.com <mailto:sbecker6925_at_gmail.com> > Sent: Tuesday, March 21, 2023 1:20 PM
To: oracle-l <oracle-l_at_freelists.org <mailto: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 Received on Wed Mar 22 2023 - 18:05:10 CET