Helpful Oracle hints I've found and saved

Shutting Down Your Oracle Database

As a new Oracle Admin, it can be pretty nerve-racking to shutdown a production instance of your database.

ESPECIALLY if your client is highly dependent on the database being up.

So, here are a few tips I've found/learnt regarding shutting down your database.

The SHUTDOWN commands

The following commands are used to shutdown your database:

  1. shutdown normal (or just "shutdown" because this is the default shutdown method)
  2. shutdown transactional
  3. shutdown immediate
  4. shutdown abort

SHUTDOWN NORMAL

This method of shutting down your database is supposedly the best/cleanest method, but I haven't personally seen it work.

The reason is, it waits for EVERYTHING to finish up on the database, and if your application is constantly busy, this will hardly ever happen.

If you do a "shutdown normal", Oracle waits for:

  • all users to be logged off (good luck with those idle sessions)
  • all updates to be completed
  • all queries to be completed (any long reports you really want to wait for??)
  • anything else that is busy on the DB

Here's a nice quote from the Oracle guy with the photos (a.k.a.: Oracle shutdown By Burleson Consulting) 😛

A normal shutdown of an Oracle database is actually rarely used. This is because the normal shutdown waits for everyone to complete their work and then logoff in an orderly fashion. When a normal shutdown occurs, the database is closed in a normal manner, and all changes made in the database are flushed to the database datafiles .  This is known as a "clean shutdown."

Most of the time this is not practical… there always seems to be someone who has left for vacation and who forgot to log out, or there are times that Oracle processes become "zombied" (this is where Oracle thinks someone is connected to the database but they really are not). In these cases, the database will never come down.

It will simply wait forever until you manually kill those sessions. Because of this, we often recommend the shutdown immediate or shutdown abort commands, which we will discuss in the next sections. Here is an example of the use of the normal shutdown command.

So, I've actually stopped using this method.

SHUTDOWN IMMEDIATE

This is the method I use now.

From the 9i DBA guide:

Use immediate database shutdown only in the following situations:

  • To initiate an automated and unattended backup
  • When a power shutdown is going to occur soon
  • When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE option:

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
  • Oracle does not wait for users currently connected to the database to disconnect. Oracle implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

So, it doesn't seem like there's any real harm in doing that, AND it means you get to go about doing what you need to a LOT sooner.

SHUTDOWN TRANSACTIONAL

This method waits for active transactions to complete while not allowing any new ones to start.

From the 9i DBA docs:

Transactional database shutdown proceeds with the following conditions:

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • After all transactions have completed, any client still connected to the instance is disconnected.
  • At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

SHUTDOWN ABORT

A "shutdown abort" is probably the "last resort" method of shutting down your Oracle Database, even though there's no real/apparent harm in doing it.

I just don't like the sound of it because it's pretty harsh and seems to just plain old CRASH your database:

You can shut down a database instantaneously by aborting the database's instance. If possible, perform this type of shutdown only in the following situations:

  • The database or one of its applications is functioning irregularly and none of the other types of shutdown works.
  • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
  • You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT option:

SHUTDOWN ABORT

An aborted database shutdown proceeds with the following conditions:

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • Current client SQL statements being processed by Oracle are immediately terminated.
  • Uncommitted transactions are not rolled back.
  • Oracle does not wait for users currently connected to the database to disconnect. Oracle implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

Well, now that I've memorised all that, I'm off to carry on with my database export and backup…

Leave a Reply

Powered by WordPress | Designed by Elegant Themes

Pin It on Pinterest

Shares
Share This