Re: trigger script after archive log switch

Philip Brown wrote:
>
> >OK, so you seem to be unable to tolerate the possibility that an
> >optional log_archive_dest_n parameter to an NFS mount might fail and
> >cause a gap in the logs that are copied by Oracle, requiring manual
> >intervention. So why not set your log_archive_dest_n parameter to
> >mandatory? In other words, how serious are you that you must have the
> >latest archive copied remotely?
>
> Utter seriousness, up to, but NOT including, stopping the production
> database from running

In other words, not very serious (bear with me, and I hope you'll see why I say this).

> > Yes, remote archiving might fail, NFS
> >might fail, etc., all of which *could* lead to a halted production
> >database. But what happens when your homegrown script fails? Your
> >production database may not halt, but you'll still get a gap.
>
> And when connectivity is restored to the remote site, the gap will
> *automatically* be filled in by the intelligent script.

Clearly loss of connectivity is not the only scenario in which your script will fail. Even so, what happens when connectivity is restored, but one of the archives that hasn't yet made it to the remote host has already been moved/deleted from the local host? All it would take is for your remote host's filesystem to be full or connectivity to be down for a long enough period of time that your local archive destination begins to fill. So you move the files from the local host off to tape and delete them (after all, the availability of the database is the primary concern). Where does that leave your "intelligent" script? Trying to copy a file that no longer exists?

> I'm a professional script writer (and device driver writer, for that
> matter). It's my job to handle odd quirky failure modes well.
> I can write a bulletproof script. I just want it triggered in optimal
> fashion.

But why do you need your script triggered at all? You've already stated above that you can tolerate significant data divergence from the primary. What does it matter if that divergence consists of one log or three? Your script can't possibly guarantee anything either way, so why setup this false requirement that Oracle must notify you?

> >How important is this remote destination *really*? If it's important, set
> >it mandatory and monitor your alert.log and v$archive_dest for archiving
> >errors so that you can fix problems before the instance halts. If it's
> >not that important, set it to optional and monitor your
> >alert.log/v$archive_dest for errors to determine when manual
> >intervention is necessary.
>
> As I said above, the remote archive is very important, but still secondary
> to continued uninterrupted functioning of the production database.
> So it fits neither the mandatory criteria, nor the optional criteria.

Fine - so why do you still believe you need Oracle to trigger your script?  

> With all the knobs and tweaks in oracle, I cant believe that there is no
> way for oracle to notify you when it has completed an archive.
> Astounding.

I still don't see your need for it. Let's pretend you have your golden egg: Oracle somehow proactively can call your script after it has archived a log file. What would you do with this information? Kick off the copy automatically? So what? Why is this any different than polling at some reasonable frequency? Your script, as you've already noted, can't possibly guarantee the copy completes in a timely fashion, so what does it matter if the copy occurs 5 minutes after the file was archived, or 5 seconds? What happends when Oracle, during high activity, generates archives more quickly than you can copy them across the network? What happens when a glitch causes your script to miss a message from Oracle? What happens when the remote archive location is full? The answer is the same in all cases: a gap between what has been archived locally and what has been copied remotely. Sure, your script may try to pick back up where things left off once normal activity resumes, and it may or may not succeed. But there's no need to have Oracle proactively notify your script, because even if Oracle could this capability wouldn't guarantee anything.

Furthermore, Oracle does NOT guarantee that an online redo log is archived after a log switch. Oracle *only* guarantees that it will not overwrite an online redo log until it has been archived to a mininum set of destinations. There's a big difference. Even if Oracle could notify your script that it had archived a particular online redo log, you would have NO guarantee that there weren't many other non-current, yet still unarchived, online redo logs which contain vital information - even more data divergence that your script has zero control over.

This is all a very long (and probably tedious) way of saying that I suspect you have set false pretenses for solving your problem. But without more insight into what you believe the problem to be, I'm left taking shots in the dark.

Regards,
Sean Received on Wed Jun 26 2002 - 23:32:58 CDT