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?
LVL 23
DavidSenior Oracle Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
    BEFORE SHUTDOWN
    ON DATABASE
BEGIN
    -- try raising a built in exception
    RAISE VALUE_ERROR;
    -- try raising your own exception
    raise_application_error(-20001, 'No!!! You can''t do that!');
END;

Open in new window



Also, I don't think triggers fire at all if the DBA issues a "shutdown abort"
0
DavidSenior Oracle Database AdministratorAuthor Commented:
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.
0
sdstuberCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
I have a database that I can play the up and down game with, so I did a little testing.

As everyone noted already, raising an error does nothing.

However, it does appear that the sleep does work.

If you create this trigger:
CREATE OR REPLACE TRIGGER trg_before_shutdown
    BEFORE SHUTDOWN
    ON DATABASE
BEGIN
    DBMS_LOCK.SLEEP(60);
END;
/

Open in new window

It will pause during the shutdown.  And if you <Cntrl>-C during the sleep it will interrupt the shutdown and return to normal database operation (at least it seems to in my 12.1.0.2 database).

However, you get no indication that it is waiting to do anything (sorry, DBMS_OUTPUT doesn't display here either).  All you really get is a pause in the shutdown, so I don't think it is really useful.

And, as noted, SHUTDOWN ABORT will cause the trigger to not run and it will shutdown anyway.
0
DavidSenior Oracle Database AdministratorAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
The negative is needing to perform a scheduled/emergency shutdown during business hours and forgetting the trigger exists.
0
sdstuberCommented:
if you want to go the sleep route you'll probably want something like one of these...



CREATE OR REPLACE TRIGGER trg_before_shutdown
    BEFORE SHUTDOWN
    ON DATABASE
BEGIN
    -- sleep 1 minute at a time until 5pm
    WHILE SYSDATE < TRUNC(SYSDATE) + 17 / 24 -- 5pm
    LOOP
        DBMS_LOCK.sleep(60);
    END LOOP;
END;
/

Open in new window


CREATE OR REPLACE TRIGGER trg_before_shutdown
    BEFORE SHUTDOWN
    ON DATABASE
BEGIN
    -- sleep until 5pm
    IF SYSDATE < TRUNC(SYSDATE) + 17 / 24
    THEN
        DBMS_LOCK.sleep(86400 * ((TRUNC(SYSDATE) + 17 / 24) - SYSDATE));
    END IF;
END;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
the negative is ameliorated by having some condition that allows the trigger to be bypassed, or, of course, simply disabling it.
0
slightwv (䄆 Netminder) Commented:
>>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.
0
DavidSenior Oracle Database AdministratorAuthor Commented:
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.
0
johnsoneSenior Oracle DBACommented:
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.
0
sdstuberCommented:
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.
0
johnsoneSenior Oracle DBACommented:
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.
0
DavidSenior Oracle Database AdministratorAuthor Commented:
Thank you for the counsel.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.