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.
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.
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.
SQL> shutdown normal;
[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?
SQL> shutdown transactional;
[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?
SQL> shutdown immediate;
[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.
SQL> shutdown abort;
[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.