Re: ideas?

"mammoth.usenet-access.com" wrote:
>
> I'm a little rusty with my scripting, can someone give me a push in the
> right direction. (I need to implement on an ver 8.1.5 NT4.0 server). What
> I need to do is fairly simple IE remove log files after I know they have
> been applied in this manual standby server.
> Basically;
>
> select sequence# from v$log_history
>
> delete any 'arcSEQ.001' from the d:\oracle\archcopy directory
>
> where SEQ is the sequence found from the v$log_history query.
>
> We don't have any programming tools to work with here other than DOS/SHELL
> and SQL/PL/SQL

Interesting question - it's a bit more complicated that it sounds. I think the following might be helpful. We had a similar requirement to test a given archive log file on the standby and know whether or not we could delete it (i.e. whether or not it has been applied, not just received, on the standby). Pass it the thread# and sequence# (which you can get by running strings on the archivelog file in question if you're paranoid like me):

select decode(sum("TOTAL"), 0, 'KEEP', 1, 'DELETE', 2, 'KEEP') from
(
select count(*) "TOTAL"
from v$log_history
where thread# = :THREAD
and sequence# = :SEQUENCE
UNION ALL
select count(*) "TOTAL"
from v$log_history
where thread# = :THREAD
and sequence# = :SEQUENCE
and next_change# > (select min(change#) from v$recover_file) )
/

Notes (if you care):
The main query sums up the results of the 2 subqueries. Only three values (0,1,2) are possible. 0 and 2 = Keep, 1 = Delete. The first subquery just checks to see if the logfile exists in v$log_history. If it doesn't, we want to keep it (definitely hasn't been applied). If it does, we may or may not want to keep it. The second query checks to see not only if it exists in v$log_history, but also whether or not it contains an SCN needed to recover the most out of date datafile. If it does, we want to keep it, if it doesn't, we can delete it. Finally the subquery "select min(change#) from v$recover_file" will return a "0" if you've added a datafile to the primary but haven't created it on the standby. In that case you many or may not want to add a where clause like "where change# > 0" to this particular subquery (I chose not to since I wanted to go the more conservative route to start).

I wrote this for 8.1.7.3.0 but haven't tested it in production yet, so use at your own risk. Questions/comments/critiques welcome.

Regards,
Sean Received on Thu May 30 2002 - 22:09:37 CDT