oracle add tag
leigh riffel (imported from SE)
Shutting down a database before doing an upgrade or a patch can be done several ways.

    shutdown immediate;

or 

    shutdown abort;
    startup restrict;
    shutdown immediate;

or

    shutdown abort;
    startup restrict;
    shutdown;

or 

    alter system checkpoint;
    shutdown abort;
    startup restrict;
    shutdown immediate;
    
Of course there are other options as well.  Which should be preferred and why?
Top Answer
Jack Douglas (imported from SE)
The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.

There are 3 SQL*Plus `shutdown` commands that achieve this in theory, all of which immediately prevent *new* sessions connecting to the instance:

 0. [`shutdown normal`](http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#ADMIN11157) or just `shutdown`: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the only `shutdown` mode that did not cancel running transactions.
 0. [`shutdown transactional`](http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#ADMIN11159): disconnects sessions once currently running transactions complete, preventing new transactions from beginning.
 0. [`shutdown immediate`](http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#ADMIN11158): disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but *the shutdown may not be* as any interrupted transactions may take time to roll back.

The fourth mode of `shutdown` is [`shutdown abort`](http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#ADMIN11160). This is like pulling the power cord - the instance stops *now* without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide [says](http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89041):

> This mode is intended for emergency situations, such as when no other form of shutdown is successful.

All the examples you give [perform a checkpoint](http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89041) as part of the `shutdown [normal]` or `shutdown immediate` so explicit checkpointing is presumably to [reduce the time required for recovery](http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89043).

general advice:

 * Do not use `shutdown normal`.
 * Use `shutdown transactional` *for attended shutdown only*, when you want to minimise cancelled transactions (attended only because this kind of shutdown [is not guaranteed to shut the database down](http://docs.oracle.com/cd/E11882_01/server.112/e25494/start003.htm#ADMIN11161) at all if timeouts are breached).
 * Use `shutdown immediate` for unattended shutdown or when you do not care about currently running transactions.
 * Do not use `shutdown abort` (plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to [minimise downtime](http://www.speakeasy.org/~jwilton/oracle/shutdown-abort-bad.html) this mode may be appropriate.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.