RE: Duplicate v9i database to P-I-T

You still have physical backups of the files?  

IF so, you may be able to use the startup rename resetlogs method from prior to RMAN and point in time recovery being an actual feature. You would need the files for system, sysaux, the files of the tablespace the table is in (you don’t need the index tablespace files if that is different) and undo, and at least one redo log containing the “end backup” from the backup prior to the commit of the delete (or the truncate or drop).  

You would have to shut down production (or use a different machine) AND be careful doing the startup rename. You’ll need to create online redologs to be used for this operation that are different from the files you will use to restart production after you unload the data you need in this table (either another database or a flatfile) from this rename. You can either adjust the control file or rename datafile for that. OR, you can copy the existing redologs somewhere, destroy them, and then put them back from the copies you made (probably with mv so you don’t have to actually copy them twice).  

Rust is flaking off my brain cells and coming out my ears at this point, but this routinely worked from 6.something through at least 10 if you have the files you need. IF you only have the abbreviated backup files tracked by RMAN for blocks that only contained blocks that were not empty, this won’t work.  

You might be able to copy the existing files, put the blocks from RMAN on top of the addresses where they belong in the copied files and patch the first block of the file to the serial address you need to match the archived redologs you need (if any). I can’t remember whether they compressed blocks in 9 for RMAN backups or just slenderized by only taking the used blocks.  

Possibly dbvisit could help. They’d have far less rust to deal with. When RMAN started working reasonably well, folks mostly stopped hiring consultants to do that work and I really only did performance stuff after that apart from reviewing whether someone’s planned operations for business continuation would work. dbvisit has software for versions where RMAN is not available and use physical replication, so they probably remember things I don’t.  

mwf  

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker Sent: Tuesday, March 21, 2023 1:21 PM
To: oracle-l
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.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 21 2023 - 19:57:48 CET