A. Stop Oracle Database
After knowing how to start an Oracle database, we may begin to learn something about stopping an Oracle database. For stopping a RAC database, you may take a look.
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.
1. SHUTDOWN NORMAL
SQL> shutdown 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. SHUTDOWN TRANSACTIONAL
SQL> shutdown 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. SHUTDOWN IMMEDIATE
SQL> shutdown immediate;
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. SHUTDOWN ABORT
SQL> shutdown 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.
Sometimes, you may not know the condition of the database, there're some ways to know what state of the instance currently is.
Pluggable Database
To close a pluggable database (PDB) locally, you can do this:
SQL> alter pluggable database CRMPDB close;
Pluggable database altered.
For closing PDB in a RAC system, you should specify instances clause.
SQL> alter pluggable database CRMPDB close instances=all;
Pluggable database altered.
B. Shutdown Script
Oracle provide a set of utilities to start and stop the database according to the settings in /etc/oratab, which I have talked about the topic in How to Start Oracle Database :: D. Startup Script pretty much.
To stop databases only, you can just issue:
[oracle@test ~]$ dbshut
To stop databases as well as the listener, you can issue:
[oracle@test ~]$ dbshut $ORACLE_HOME
How convenient!
For now, we have learned stopping and starting Oracle databases. Normally, we use them to restart a database in two steps. Is there a way to restart a database in only one step? Think about it.