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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
