Link to home
Start Free TrialLog in
Avatar of David VanZandt
David VanZandtFlag for United States of America

asked on

Creating a are-you-sure condition prior to shutdown: what do you think of...

Oracle standalone instance, say 11gR2 on Linux, DBA mistakenly issues a SHUTDOWN in a production session rather than development as intended. It's been suggested to (1) be extra careful next time and (2) don't do it a second time :)

Would it be worth pursuing to add a BEFORE SHUTDOWN trigger that checks IF business day AND IF business hours, THEN RAISE EXCEPTION to check for some intervening control -- such as a global variable value, or file presence. IOW prevent an unscheduled outage during business hours unless there is an override.

It appears that the BEFORE DATABASE trigger syntax could accommodate either a PL/SQL procedure or an OS shell. But what kind of trace would be required to see where control would go after the SHUTDOWN is prevented?
Avatar of Sean Stuber
Sean Stuber

I don't know how that could work.  
In order to stop a shutdown you'd have to raise an exception; but exceptions in shutdown triggers are ignored (the error is written to a trace file and alert log, but doesn't actually "raise".)

It's simple enough to test.
I just created the following shutdown trigger on my 11gR2 db on linux and it shuts down just fine with no indication that an exception ever happened (except messages in trace and alert log)

CREATE OR REPLACE TRIGGER trg_before_shutdown
    -- try raising a built in exception
    -- try raising your own exception
    raise_application_error(-20001, 'No!!! You can''t do that!');

Open in new window

Also, I don't think triggers fire at all if the DBA issues a "shutdown abort"
Avatar of David VanZandt


Thank you, Sean. It's rare, fortunately, for us to employ the abort clause. Your comment leads me to wonder if the trigger could suspend the shutdown temporarily. For instance, the impact to production users might be negated if the shutdown could be held off until 17:00. I've also thought about experimenting with an OS trap, if we required shutdowns to go through a bash shell and SQL script.
yes, a pause could work.  As long as the trigger is continuing to execute without error it should be fine.

So, a trigger that calls   dbms_lock.sleep should work just fine to cause the shutdown to pause.
Avatar of johnsone
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi johnsone, I hope to test this in another day. But to your comment about it being useful -- it may very well change an unscheduled production outage into an after-hours one. I'm down with that.
The negative is needing to perform a scheduled/emergency shutdown during business hours and forgetting the trigger exists.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the negative is ameliorated by having some condition that allows the trigger to be bypassed, or, of course, simply disabling it.
>>the negative is ameliorated by

Agreed as long as it is documented in such a way that it is remembered years from now.  My thought is that after 6 months or a year, it will long be forgotten about and someone will need to perform an emergency shutdown and it hangs and people start to panic.
Perhaps an appropriately named function or environment variable, e.g. DEFFERED_SHUTDOWN_TRIGGER_ENABLED=T. A cron or scheduler task could force it to true at the start of the business day. Whatever.
A shutdown abort will always stop the trigger from running.

But, to the point about it being useful.  If you do something by accident on the incorrect database.  There is no message to say that you did something.  No message to indicate which database you are shutting down.  So, how is it helpful?

What you really need to do is a policy change.  Create a script that does the shutdown.  Have that script ask for a confirmation.  It can show the name of the database you are about to shut down and make you confirm it.  As long as you always use the script, it is effective.  In an emergency situation you don't use the script or write it with an override.  We had policies like this and they worked very well.
Yes,  I've been answering the question "as asked" merely for academic curiosity.  

BUT... since I've already explained, and others have corroborated, you can't make this bullet proof.

So - I agree with making a script to do the shutdowns and a policy that it is required.
This will allow you to include logging, perhaps with an extended prompt for the DBA to explain why it it's being shutdown, maybe with free form text or from a menu of "approved" options.

Since "shutdown abort" is already a workaround that can't easily be prevented the goal shouldn't be to try to backdoor the policy.  Make the policy front and center.   Use OS and db auditing to capture the users logging in to do the shutdowns and apply appropriate sanctions for violations.
I am not trying to say the trigger wouldn't work.  I guess what I am trying to say is that no information is presented to the user for them to have that "aha" moment where they realize they are shutting down the wrong database.  Personally, I believe that makes it useless.  A script has more control over presenting that information.  Especially where you put in a 10 line banner on the script for production databases, something that they cannot miss, but that banner cannot be present on the non-production machines.  Otherwise, it becomes noise that people ignore and just hit OK.

Also, just a word of warning.  While cancelling a shutdown will seemingly return the database to normal operations, I have had cases a few times where it said it did and cancelled the shutdown, but it really didn't.  While you may believe that the trigger is the first thing that happens, it really isn't.  Watch the alert log, there are messages that hit the log before the sleep, so something things are done before the trigger fires.  Also, once it gets past the sleep you can still cancel it and you might end up in some unknown state where you would have to shutdown and restart anyway.
Thank you for the counsel.