Startup and Shutdown

How to Stop Oracle Database

Stop Oracle Database

After knowing how to start an Oracle database, we may begin to learn something about stopping an Oracle database.

Stopping a database is a less complex job than starting a database. This is because all kinds of shutdown shall go to the idle state eventually, no matter what state currently is.

Shutdown to IDLE
Shutdown to IDLE

That is to say, you can never switch state from OPEN to MOUNT or MOUNT to NOMOUNT. The final destination is always IDLE. Sometimes, which is also called shutdown, stopped, unstarted, ceased or closed.

Shutdown to States
Shutdown to States

In some rare incidents, the instance may decide to shutdown itself in case of further damage to the whole database. We should be aware of that.

In Oracle, there’re four options of shutdown to stop a database.

1. NORMAL

For Single-instance

SQL> shutdown normal;

For RAC

[oracle@primary01 ~]$ srvctl stop database -d <db_unique_name> -o normal

This is the gentlest shutdown, it will wait for users to disconnect from the database and allows new connections to come in. No rush, no hurry, take your time. This would drive DBA crazy. To the bright side, at least next startup will not require any instance recovery.

Normally, DBA will notify all users before planned shutdowns in practice, so I don’t see the necessity of using SHUTDOWN NORMAL, do you?

2. TRANSACTIONAL

For Single-instance

SQL> shutdown transactional;

For RAC

[oracle@primary01 ~]$ srvctl stop database -d <db_unique_name> -o transactional

Transactions are allowable to complete naturally, either explicit or implicit endings can terminate the transactions, but no more new transactions. Sessions with no transactions will be disconnected.

But the problem is, some transactions may take a long long time to complete, would you like to wait for a potentially infinite time?

3. IMMEDIATE

For Single-instance

SQL> shutdown immediate;

For RAC

[oracle@primary01 ~]$ srvctl stop database -d <db_unique_name> -o immediate

If your default stop option is IMMEDIATE, then you can omit the option.

[oracle@primary01 ~]$ srvctl stop database -d <db_unique_name>

Statements in progress are allowed to complete, uncommitted transaction will be rolled back, no matter how many blocks have been changed. All clients will be disconnected. This is the most often used shutdown mode for DBA to operate some maintenance tasks.

Normally, the database will be closed in several minutes, if not, SHUTDOWN IMMEDIATE may hang in the middle way to close.

4. ABORT

For Single-instance

SQL> shutdown abort;

For RAC

[oracle@primary01 ~]$ srvctl stop database -d <db_unique_name> -o abort

It closes the database without doing too much before ceasing the instance. By this mode, it will terminate all activities right away and left the database in an inconsistent state. The side effect is that next startup will need an instance recovery, it may take a serious time to complete.

Even though it left the database in an inconsistent state, SHUTDOWN ABORT is theoretically safe, while server blackouts or system failures may be not.

Now the database is idle, if you are a remote client, then you need to know how to connect an idle, nomount or restricted database remotely to do following maintenance jobs.

Leave a Reply

Your email address will not be published. Required fields are marked *