10.28.2012

How do I decide between Shutdown NORMAL|IMMEDIATE|ABORT

Well, as an Oracle DBA, we have been taught and learnt the various database shutdown modes, NORMAL|IMMEDIATE|TRANSACTIONAL|ABORT that would be used as situation demands.

When the NORMAL|IMMEDIATE mode doesn't work, as a last resort, we use the 'SHUTDOWN ABORT' to terminate an active instance as quickly as possible, leaving a database inconsistent mode. Of course, the subsequent database startup requires an instance recovery and the recovery will be performed by the SMON b/g process automatically. Having said that, sometimes, this mode would have a huge risk of data corruption, specifically in pre 8.1.6 version.

Beyond a doubt, the 'SHUTDOWN ABORT' is the fastest mode of a database shutdown. Nevertheless,  we sometime afraid of using this mode due to the above mentioned facts. In order make a decision between a clean shutdown and shutdown about, one can do the following exercise:

Determine the of rollback is required (in bytes) for a clean database shutdown:

      select sum(used_ublk) * 
         <block size of the undo/rollback segment tablespace> 
         from v$transaction;

If the amount of rollback required for a clean shutdown is very little, then go ahead with 'SHUTDOWN IMMEDIATE'.  In case if the amount of rollback required for a clean shutdown is huge and time consuming, use the 'SHUTDOWN ABORT' command, preferably if you are on >8.1.6 version.

Bring up the database in RESTRICT MODE subsequently to verify the rollback progress:

        select sum(distinct(ktuxesiz)) 
        from x$ktuxe where ktuxecfl = 'DEAD';

Upon rollback completion, shutdown the database cleanly, using the 'SHUTDOWN IMMEDIATE'.


Reference:
What Is The Fastest Way To Cleanly Shutdown An Oracle Database? [ID 386408.1]

2 comments:

Mark J. Bobak said...

I think it's worth mentioning that if you want to do:SHUTDOWN ABORT/STARTUP RESTRICT/SHUTDOWN IMMEDIATE, it's a bit quicker to do STARTUP FORCE RESTRICT/SHUTDOWN IMMEDIATE.

"STARTUP FORCE RESTRICT" is shorthand for "SHUTDOWN ABORT/STARTUP RESTRICT".

-Mark

The Human Fly said...

Thanks for mentioning STARTUP FORCE RESTRICT.