How to Shutdown Oracle Database

Back to: Oracle DBA Tutorials

How to Shutdown Oracle Database

In this article, I am going to discuss How to Shutdown Oracle Database. Until now we have learned about Starting the Oracle Database. Now we will learn about shutting down the Oracle Database. To shut down any database we need to first connect as SYSDBA or SYSOPER. Generally, shutting down of the database is performed by the database administrator (SYS) user having the highest privileges user.

When do we need to Shutdown Oracle Database?

There are specific reasons to shut down the database.

1. Change a Static Parameter.
2. Patch the database server
3. Perform Maintenance or other administrative tasks.

Let’s take an example if we need to update the database from 12c to 19c we need to first shut down the database and then perform the upgradation.

Modes of Shutdown of the Oracle Database:

1. Shutdown Normal
2. Shutdown Transactional
3. Shutdown Immediate
4. Shutdown Abort

Below is a generic table of the shutdown methods which determines the actions of each shutdown method.

Shutdown Normal:

When the database is shut down normal. The database doesn’t allow any new connections from the users. The database waits until the current sessions run on the database end. The database waits until the current transactions are completed in the database. It also forces a checkpoint and ends the file. This method is very safe as well as very hard to complete because some sessions running in the database may take hours or maybe a full day for the user sessions to complete and then shuts down the database.

Shutdown Transactional:

The second mode is shutdown Transactional. When we hit shutdown Transactional the database doesn’t allow new users to connect to the database. The database doesn’t wait for the current sessions to complete. But the database will wait for the current transactions to end. This method will also force the checkpoint and closes the files. This is also a safe method to shut down the database. But sometimes it might be tricky because some transactions may take a lot of time to complete and the database needs to wait until all the transactions to complete.

Shutdown Immediate:

This is the most commonly used shutdown method in the database industry. This method does not allow new connections, does not wait for the current sessions to complete, and does not allow the current transactions to complete. It forces the checkpoint and then closes the files.

These three shutdown methods are very safe shutdown methods. These methods will lead to a consistent database. But now the shutdown abort method leads to an inconsistent database.

Shutdown Abort:

This method doesn’t allow new connections, doesn’t allow the sessions to complete, doesn’t allow the transactions to complete and doesn’t force the checkpoint, and doesn’t close the file. We may lose the data by using this method because it doesn’t force the checkpoint and save the file. Try to avoid using this method.

Note: If you enter only shut down in the database without any database, this takes the shutdown Normal.

Differences between the Consistent and Inconsistent Database:

The consistent database includes shutdown methods like Shutdown normal, shutdown transactional, and shutdown immediate. During these shutdown methods, the uncommitted data will be saved to the database but for shutdown immediate, the uncommitted changes will be rolled back. Database Buffer cache will be written to datafiles. All the resources are released. There is no need for instance recovery because everything is saved. The inconsistent database includes methods like shutdown abort, instance failure, and startup force. The startup force is equal to shutdown abort which will kill all the background processes. Due to this, the modified buffers (dirty buffers) cannot be written to the data files. Uncommitted changes are not rolled back. During the startup of the database, the Online redo log files are used to re-apply the changes and undo segments are used to roll back the uncommitted data. All the resources are released.

Previously we have discussed the background process. There is a background process called DBWn. This is a database writer. This writes the data buffers from the database buffer cache to the data files which are present in the physical location. When we hit commit in the database the changes that we made don’t directly store in the datafiles. When any transactions happen in the database the transactions happen in the memory i.e in the database buffer cache. For example, we have used an update statement for some rows in the database. In the below image you can see the blocks in brown color. The brown color boxes indicate the modified database. The modified data is referred to as a dirty buffer. So, the updated rows will be stored inside the brown color blocks i.e. dirty buffers.

The blocks with brown color in the above database buffer cache are the dirty buffers.

When does the database writer write to datafiles?

We have five main reasons behind the DBWn writing the data to data files.

1. Dirty buffers threshold: The first reason is when there is no space for the new dirty buffers in the database buffer cache the database writer writes the data to data files. We call it the threshold value of the dirty buffer.
2. When we need free buffers for some operations: Let’s take an example, a user queried for 10,000 rows so we don’t have space in the memory so the database writer writes the dirty buffer to the datafiles and then frees the memory in the database buffer cache which will be helpful to query for 10,000 rows.
3. Shutdown (Normal, Transactional, Immediate): The third reason during the shutdown process. When a database administrator shut down the database using shutdown normally, shutdown transactional, or shutdown immediately the dirty buffers will be written to the datafiles using the DBWn. This will not cause any data loss. But if the database administrator uses shutdown abort, then there will be data loss because the database writer’s background process will be terminated immediately because the shutdown abort will kill all the background processes and the dirty buffer present in the database buffer cache will not be saved to the datafiles.
4. Changing the Status for Tablespace: The fourth reason is changing the status of the tablespace. If we change the status of the tablespace to read only then the database writer writes the dirty buffer to the datafiles.
5. During a Checkpoint: The fifth reason is during the checkpoint. We will discuss this checkpoint in further topics.

Practical Exercise:

Until now we have discussed shutting down the database in the theoretical part. Now let’s see the practical implementation of the shutdown methods.

Step 1: Connect to Oracle Database.

Log in to the Linux machine as an oracle user. After logging into the oracle user. Open two terminals and connect to the database as “sysdba” for both terminal sessions.
Command: sqlplus / as sysdba

After connecting to the database using the command “show con_name” which displays the container database name.

Step 2: Oracle Database Shutdown Normal

Let’s create a table in the database in terminal -2. Creating a table in the root database is not a good practice but we are in the learning stage so, we can create but shouldn’t be done in the production. We are creating the table using the below queries. So, we are creating a table “student” with row name “roll_no”. After creating the table and row we are inserting a value 1 into the row.

So, now the table is created, we haven’t committed the data yet. Now go to the first terminal and try to issue the command Shutdown Normal and press enter.

So, after issuing the command “shutdown Normal” the database is not shutting down because the second session where the table is created needs to be committed and then closed from the database. So, we will enter the command “Commit”. After executing the command “Commit” the transaction is completed. After this transaction, he may perform any other transactions like rollback (or) else create another table. So, in order to avoid further delay the user needs to exit the session.

After exiting from the database, the Shutdown Normal will work and the database will be closed. So, from this exercise, we can understand that the database will wait until the transaction is completed and the session is exited from the database, and then shut down the database.

Step 3: Oracle Database Shutdown Transactional

After shutting the database in Normal mode start the database again using “startup”. Open two terminals. Go to Terminal -1 and start the database using “Startup”. Once the database is completely opened. Then go to terminal – 2 and connect to the database using “sqlplus / as sysdba”. After connecting to the database insert another row to the tables that we have created previously. All the commands are practiced and shown below.

After inserting a new row into the database. We haven’t committed the data yet. So, go to terminal – 1 and then issue the command “Shutdown Transactional”.

Even after issuing the command “Shutdown Transactional” the database didn’t shut down because the insert transaction that we have done in terminal-2 didn’t complete yet. In order to complete the transaction, we have to use the command “Commit”. After issuing the command the Shutdown Transactional will work and shutdown the database. This method will not wait until the session exits. You can see below the database is closed and shutdown competed.

Step 4: Oracle Database Shutdown Immediate

After completing the shutdown Transactional. Start the database again using the command “Startup” in Terminal – 1.

After the database is started insert another record in the table that we have created previously in terminal -2. So, in terminal -1 the database is started. In Terminal -2 another row has been inserted into the table student.

After inserting another row, we will issue the command “Shutdown Immediate”. After Issuing this command oracle will not wait for the user transaction and user session to complete. The database will shut down instantly. So, the row that we have inserted into the table student will be rolled back.

The “Shutdown Immediate” has been initiated. So, the database will shut down. In order to check whether the database has been shut down or not go to terminal – 2 and run any query. For example, we will run a query “select name from v$database”. Let’s see what would be the output.

So, we got an output saying the connection lost contact. This indicates that the database is down. Let’s start the database again and check whether the new record that we have created is present inside the database or not.

We have started the database. Let’s run a query to check the rows present inside the table student.

Query: Select * from student;

We’ve got only two rows. There is no third row because we haven’t committed the data and shut down the database through “shutdown immediate”. So, any uncommitted data will not be saved during the shutdown immediate process.

Shutdown Abort is another process. We don’t recommend using this process because it will affect the performance of the database.

In the next article, I am going to discuss How to Connect to Oracle Database. Here, in this article, I try to explain How to Shutdown Oracle Database and I hope you enjoy this Shutdown of the Oracle Database article.