Comments below.
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
<teefamcg_at_my-deja.com> wrote in message news:8t7lhs$44e$1_at_nnrp1.deja.com...
> Hello, I have an 8.1.6.2 Oracle database running on an Unix platform. So
as
> not to impact the production environment or other development
environments,
> developers will use this database to develop and test a purge procedure.
> Once the delete code is run, they will want to recover the database back
to
> its status before the delete. I have a cold backup, which we can use by
> shutting down the database, removing the dbf files, copying over the
> compressed backup files and uncompressing. After about an hour and a
half,
> they can restart the database. This process is unsatisfactory for two
> reasons: 1. It takes a fair amount of time. 2. If the developer wants to
> create specific, new data to purge, they would have to input the data,
create
> a new cold backup (over an hour), before testing the code again.
>
> I would like somehow to use the archive logs. With a small test, I was
able
> to recover the database fairly quickly using 'recover database until time
> ....' and specifying a time after I new data was created and before the
> delete happened. But the database was completely recovered, including the
> delete.
All that means is that you got the time wrong. You replayed the delete
actions. Since you have 8.1.6, use the log miner facility to determine the
exact time of the delete. Recover to one second before that, and you'll be
guaranteed to get the data back.
>So my questions are: 1. Can I use the archive logs for this
> purpose, and how can I recover the data without the delete?
Recover until time is an incomplete recovery, and requires you to restore
ALL datafiles, roll them forward with archives until the time just prior to
the delete being issued, and then opening the database with a resetlogs.
>2. Once I 'alter
> database open resetlogs', do I need to keep the archivelogs from before
the
> database was shutdown?
Technically, after every resetlogs, you should close the database down, and
perform a closed, complete backup -because you have a new incarnation of the
database, and all prior backups and archives are useless. Hence, you can
indeed delete prior backups and archives. However, it is possible to do a
recovery through resetlogs if you have the patience of Job and know exactly
what you are doing, and if you feel the need to save on time by not doing a
new backup after opening with a resetlogs, you would need the prior backups
and archives... however, I wouldn't recommend it.
>Can they be deleted, and the next time I recover the
> database will I only need the archive logs since this most recent restart?
>
See above. Other things being equal, and assuming you have a complete
backup of some sort from *after* the resetlogs, the answer is yes.
> Your words of wisdom are welcome. I do not want to use the Export/Import
> functionality because a full Export/Import would take more time than using
> the cold backup files, and the incremental will not handle well that the
> tables already exist. Thanks, Jamil
>
Frankly, I think you are going down a deep dark hole. A development
database is not the place for archives.
I recommend you have a look at transportable tablespaces. The theory is
that you export the metadata describing your tablespaces which takes a
matter of minutes, and need never be done again (the dump files stick around
unchanged for ever). You then simply have pristine copies of the datafiles
with the relevant data in the wings. You run import on the database, to
plug in the relevant tablespaces -we are talking minutes here, because you
are simply plugging in the entire datafiles into the data dictionary. It
takes minutes, tops. Let the developers do their worst... when they're
ready, simply drop all tablespaces including contents cascade constraints,
and then plug in the pristine copies once again.
Regards
HJR
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 26 2000 - 07:02:33 CDT
