Re: Oldest Archive Log needed

On Dec 28, 5:09 pm, zipRobe..._at_gmail.com wrote:
> I have an oracle 10g database in archivelog mode. It runs for a few
> days generating archive log files and I do a hot backup, a few more
> days go by and I do another backup.
>
> Assuming the last two backups are good and I only need the last one
> how do I determine the oldest archive log to keep so I can delete the
> old ones not needed for the most recent backup.
>
> Thanks.

this ought to be close:
select max(sequence#)
from v$archived_log
where first_time<(
select min(time)
  from v$backup)
);

there's a couple of assumptions:

1. no resetlogs operation has occurred.
2. the log history in the controlfile was set high enough that the entries for the archived redo logs of interest still exist
3. tested on 10.2.0.3 standard edition on w2k3 svr, 32 bit.

Why don't you write out this info to file as part of the hot backup set?
(insert rman comments here about rewriting functionality).

hth.

-bdbafh Received on Fri Dec 28 2007 - 16:33:29 CST