Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Stop an Oracle 12c Database

Just as there is an order of events to starting an Oracle 12c database instance, there is also an order for how a database instance is stopped. Ideally, this is what happens during a database shutdown:

  • New connections to the database are denied.

  • Existing transactions are either committed or rolled back with proper updates to online redo log files.

  • User sessions are terminated.

  • Database file headers are updated and files are closed.

  • SGA is shut down.

  • Background processes are terminated.

It is preferable for all the steps to occur naturally during shutdown, which ensures that

  • All transactions are neatly committed or rolled back.

  • Online redo log files are properly updated.

  • All files are closed properly without corruption.

If the preceding steps don’t occur during shutdown because of a server or database instance crash or SHUTDOWN ABORT, the cleanup operations must occur during startup in a phase called instance recovery.

During instance recovery, Oracle won’t open a database instance until it’s satisfied that all transactions are accounted for and all data files are opened. If it can’t complete these tasks, error messages appear and the DBA must address them. Instance recovery is successful most of the time, but it may take several minutes to process the cleanup.

Shutdown types in Oracle 12c

When a database needs to be shut down, several methods exist to do so with varying effects on current users and their transactions.

SHUTDOWN [NORMAL]

  • New connections to the database are denied.

  • Existing transactions continue normally until either they roll back or commit.

  • Users log out normally on their own.

  • After the last user logs out, database file headers are updated and files are closed.

  • SGA is shut down.

  • Background processes are terminated.

  • Specified by the SHUTDOWN or SHUTDOWN NORMAL command.

SHUTDOWN TRANSACTIONAL

  • New connections to the database are denied.

  • Existing transactions continue normally until they either roll back or commit.

  • After an existing transaction is completed, user sessions are terminated.

  • Database file headers are updated and files are closed.

  • SGA is shut down.

  • Background processes are terminated.

  • Specified by the SHUTDOWN TRANSACTIONAL command.

SHUTDOWN IMMEDIATE

  • New connections to the database are denied.

  • Existing transactions are rolled back.

  • User sessions are terminated.

  • Database file headers are updated, and files are closed.

  • SGA is shut down.

  • Background processes are terminated.

  • Specified by the SHUTDOWN IMMEDIATE command.

SHUTDOWN ABORT

  • New connections to the database are denied.

  • Existing transactions are not rolled back.

  • User sessions are terminated.

  • SGA is shut down.

  • Background processes are terminated.

  • Specified by the SHUTDOWN ABORT command.

  • Instance recovery is required on startup.

Shutdown decisions in Oracle 12c

When do you use each shutdown type?

  • Generally, SHUTDOWN IMMEDIATE is what you want because it cleanly commits or rolls back existing transactions, terminates user sessions when they are complete, and then closes the database in a clean manner.

  • Don’t use SHUTDOWN NORMAL very often because even one user still logged in (after he’s left for the day) can hang the shutdown.

  • SHUTDOWN TRANSACTIONAL doesn’t buy you much because it forces you to wait on users to finish their transactions. If you want to wait, you can just enter SHUTDOWN NORMAL. However, if you want to force them off the database instance, you use SHUTDOWN IMMEDIATE. There are times SHUTDOWN TRANSACTIONAL is useful, but it’s not as common as you might think. This method is most commonly used in clustered environments.

    Here’s how a typical SHUTDOWN IMMEDIATE executes. Keep in mind that you must be logged in as SYSDBA to run the shutdown command.

    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit
  • Use SHUTDOWN ABORT only when you have to. It essentially crashes the database and expects instance recovery to pick up the pieces. You may have to do that if the system is hung, but it shouldn’t be your first choice (unless you want to do real database recovery sometime).

    If you can issue commands on the database instance, issue an ALTER SYSTEM SWITCH LOGFILE to force a checkpoint to close file headers and flush the online redo logs before issuing the SHUTDOWN ABORT. Forcing a check point allows for an easier instance recovery during the next startup.

Before you issue any commands changing the running state of the instance, make sure you’re connecting to the correct instance. On Linux, if the instance is not started yet, type the following at the command line:

$ echo $ORACLE_SID

If the instance is already running and you want to shut it down or restrict it, type this:

$ sqlplus / as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev12c
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.