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]
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:
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
Thanks for mentioning STARTUP FORCE RESTRICT.
Post a Comment