The reasons why I always avoid to shutdown abort

It is a common practice to always shutdown abort the database before restarting and shutting in down immediate. This is because sometimes SHUTDOWN IMMEDIATE takes ages. For instance due to a huge transaction to be rollback.

I do not like it. At all.

First, chance exists that you won’t be able to start the database anymore. I have not heard or meet anyone who had this issue since Oracle7, but I still believe it.

Second, shutdown abort is very useful if something goes seriously wrong. But if something goes wrong, you may want to find out what it is.

Third, you may hit more bugs than if you do close normal. And you may get less help from support if this is due to an abusive shutdown abort. YMMV

Ok, small demo to preach to the converted
disclaimer: this demo is not innocent, do not try this on your database

SQL> create flashback archive fa tablespace ts retention 1 day;

Flashback archive created.

SQL> create table t(x number primary key);

Table created.

SQL> alter table t flashback archive fa;

Table altered.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DBA_SEGMENTS where tablespace_name='TS';

no rows selected

I have created a flashback archive table, and the committed transaction is not written down to the flashback tablespace yet.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2227072 bytes
Variable Size             234882176 bytes
Database Buffers          171966464 bytes
Redo Buffers                8470528 bytes
Database mounted.
Database opened.


SQL> sho parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDO1
SQL> create undo tablespace undo2 datafile '/u02/oradata/@/undo2_01.dbf' size 10m reuse;

Tablespace created.

SQL> alter system set undo_tablespace=undo2;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2227072 bytes
Variable Size             234882176 bytes
Database Buffers          171966464 bytes
Redo Buffers                8470528 bytes
Database mounted.
Database opened.

I have switched undo tablespace. So far so good. But remember the flashback archive did not write to the flashback tablespace before shutdown abort.


SQL> drop tablespace undo1 including contents and datafiles;
drop tablespace undo1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3544069484$' found, terminate dropping tablespace

You see… I cannot drop my old undo tablespace.

Q.E.D.

15 thoughts on “The reasons why I always avoid to shutdown abort”

  1. You say since Oracle 734, but I remember working on a 806 or maybe a 817 database and had a db that wouldn’t come up after an ABORT. And I only ran that AFTER immediate ‘failed’

    I’ve always thought of that as a ‘last resort’ option. Maybe I am suffering from old wives’ tales?

  2. The problem with abort has always been that it exposes you unnecessarily to any redo stream problem. Now, for years many people claim (one way or another) that the redo stream is inviolate, if you don’t trust Oracle recovery you should go work on Access, etc. The general claim is that redo and commit are atomic, either something is done or not, and recovery will always sort it out.

    Well, I’m more paranoid than that, partly because I’ve seen standby go bonkers in several versions, and what is standby than continuous recovery (granted, once things get out into a network there are additional corruption vectors), and partly because a quick look at bugs in recent versions still finds recovery bugs (Bug 9406607 in my 2-minute search looks tasty, though not completely deadly). A less common but still seen issue is hardware errors (sometimes silly configuration that allows hardware to lie to Oracle, sometimes real problems like earlier SSD’s aging and randomly flipping bits).

    I’ve certainly seen plain old crashing make problems in 8 and 8i. One of my favorites was a Sun box in a secure military center with the db set to autostart. Electrician comes in and starts working on the UPS, booting the Sun repeatedly as it tries to come up. All you need is the right paperwork…

    Add a few new features, and look what you demo in a dozen lines. Respect shutdown au-thor-i-tah.

  3. yes, but what’s the alternative when your database is frozen?
    when running the shutdown abort and no feedback from database ….

    The only way is to kill the PMON, at OS level…

  4. Thanks Laurent.

    It wasn’t frozen due to ORA-00020 maximum number of processes exceeded.

    The problem was on a RAC database and the running jobs were causing some deadlocks at OS level (due to intercluster communication), they were accessing same objects.

    Shutting down with srvctl didn’t help…

    Cheers.

  5. By frozen I mean: any select couldn’t be run …
    In alert log, errors related to locking resources.

  6. I would proceed in this order

    1) sqlplus / as sysdba : shutdown immediate
    2) sqlplus / as sysdba : shutdown abort
    3) sqlplus -prelim / as sysdba : shutdown abort
    4) open a SR
    5) kill SMON
    6) get a coffee
    6) kill -9 PMON

  7. why do you prefer killing SMON first instead of killing PMON first?

    sqlplus -prelim / as sysdba – this is used for dumping system state and hunganalyze info.
    Did anyone shutdown a database using -prelim option?

    sqlplus /nolog

    SQL*Plus: Release 10.2.0.3.0 – Production on Fri Aug 19 21:04:30 2011

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    SQL> set _prelim on
    SQL> conn / as sysdba
    Prelim connection established
    SQL> select name from v$database;
    select name from v$database
    *
    ERROR at line 1:
    ORA-01012: not logged on

    SQL>

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>