Self-healing database job? Is it possible

[Maven4Champ]

All,

I have a procedure that is being called in a DBMS_JOB.SUBMIT procedure which refreshes materialized views in sequential order and then closes out. One of the drawbacks to my method is that I look for a failure and if one is encountered, it does NULL (nothing) and if no failures occur, it proceeds with each step in the procedure, refreshing one by one the materialized views and again checking for failures.

Is it possible that if when it gets to the end of the job and encounters one or more failures, it resets the failures (self-healing) to 0 so the job can attempt to run the next day as scheduled without automatically NULL'ing out?

Are there any drawbacks to that method as well or any better solutions to resolve this request? Ultimately, we want to be proactive in the notification of failures, etc. but with that put aside, we need the ability for the job to be smart enough to reset itself after each nightly run.

 

[SantaMufasa]

Are there any drawbacks to that method...?

Does that method introduce risk into your business or process that are greater than the risks of an alternative? That is the proper question (IMHO).

any better solutions to resolve this request?

If it works (i.e., resolves your business/process need), then it is already a good solution. To make it better, an alternative would need to accomplish the same result at less cost. I haven't sat down to create an alternative, so I'd have to wait until somebody creates an alternative to comment on "better".



I infer from your post ("it resets the failures (self-healing) to 0") that you are somehow preserving the result of your run to pass along to the next run. By what method are you preserving that outcome?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Your business logic does seem suspect here.
If you detect errors, but then do nothing about them, why bother to detect them in the first place?

If it doesn't matter about errors (because tomorrow's run will presumably try to fix it anyway) why detect the errors.

What is the business requirement here? Establish that clearly and then use it to decide the functional, and then implement that necessaary function.

Regards

T

Grinding away at things Oracular

 

[Maven4Champ]

The business requirement is that, for that particular day, if one refresh fails, they should all fail and since the DBMS_JOB itself will attempt to re-run after a failure until it is broken, the alternative is to check for "1" in the failure column of user_jobs view and when it hits 1 or exceeds 1, do nothing until the job is determined to be broken.

We also have a notification mechanism we are implementing to use UTL_SMTP to notify us when the job raises an exception or finishes OK with no errors. Just as well though, we need the job to be a little self-sufficient in that, it can reset itself after each run.

 

[sem]

You may create another job - a watchdog to monitor the state of this first one and reschedule it if necessary.

Regards, Dima

http://seminihin.narod.ru

 

[karluk]

Is there any reason you are not using a materialized view refresh group? That is the standard way of managing materialized views that need to be refreshed together.