Re: trigger script after archive log switch

Philip Brown wrote:
>
> On Tue, 25 Jun 2002 15:32:59 -0600, smckeownNO_at_BACKSIESearthlink.net wrote:
> >Hi Philip-
> >
> >You seem to have 2 criteria that are at odds with each other: 1) you
> >want to guarantee that an archive is copied to an alternate location the
> >instant it is created but 2) you don't want to let Oracle do this for
> >you (using multiple archive_log_dest params). You can't have both. If
> >you want Oracle to guarantee that an archive is copied to an alternate
> >location the instant it is created, you need to let Oracle do this
> >through it's own built-in mechanisms.
>
> I dont want to have Oracle guarantee that an archive is copied. I just want
> Oracle to tell me when the archive is created. I can take care of the
> guarantees on copy myself :-)

The only way I can conceive to do this would be to put a trigger on the system table(s) which make up the v_$archived_log view. This, however, is not an option - I'm not even sure Oracle will let you, but I am sure it's a Really Bad Idea (tm).

> > If you don't want to use Oracle's
> >built-in mechanisms which guarantee this copy,
>
> It was my understanding that in no way does oracle guarantee the remote copy.
> It TRIES to make the copy, and if it doesnt make the copy, it marks the
> archive destination as failed, and no longer tries to write to it for
> future archive logs. (until you manually go in and bump it)
> Unless you mark it as mandatory, in which case, oracle wont run if the
> remote location is unavailable at that point.

Correct, Oracle does not guarantee the copy per se. But it can guarantee that the online redo log in question is not reused until the remote copy has succeeded (e.g. log_archive_dest_n set to mandatory). [Also, as an aside, if you set the REOPEN parameter, Oracle will retry for future archives, just not the failed archive.]  

> Neither choice is acceptible to me, therefore, i want to be able to handle
> the copy/updates myself.

And here's the rub - you say these choices are unacceptable without providing your reasons. That makes it hard for those wishing to give advice to do so. You may have perfectly valid reasons, but if we don't know what they are, it's hard not to assume you simply haven't explored the standard options to their fullest. Should we give you the benefit of the doubt and take you at your word that your reasons are valid without further explanation? Probably. But after seeing/repsonding to enough posters with invalid justifications, it's hard to trust implicitly.  

> > you will have to poll
> >(either the database itself via v$archived_log or the OS via ls, etc.)
> >on some interval.
>
> Is it kosher to put a trigger on v$archived_log for this purpose, then?

No. The fact that you're even suggesting this adds to my concern that you haven't fully explored the options built into Oracle.  

> > If you're suggesting that your interval would be on
> >the order of 1 second, you're implying that you need a *guaranteed* copy
> >the instant the archive is created.
>
> No, not that stringent. I want my script to be called reasonably quickly
> after the redo log has been archived.
> After kicking off the script, oracle will continue running reguardless of
> what the script has to do, and the copy will take place at the system level,
> in the background.

So, why not just use cron or dbms_job to poll every X minutes, where X is the typical duration between log switches on your system? If not X, then X/2? X/4? In other words something reasonable, not every second.  

> >It might help matters if you could explain your multiple reasons for not
> >wanting to use multiple archive destinations w/in Oracle (I assume this
> >has something to do with security concerns),
>
> Actually, more disaster recovers reasons. But security is also an issue.

Please, for the 2nd time now (third time actually if you include Sybrand's original response), could you tell us all the big secret. What are your "disaster recovery" reasons for NOT wanting to use multiple archive destinations within Oracle? I'm legitimately trying to help here.  

Thanks,
Sean Received on Tue Jun 25 2002 - 18:14:42 CDT