Tanubhrt.This.Side

A database in working is actually a set of operating system files that contain the user data and the metadata. Every instance of the running Oracle database is associated with a set of background processes or threads and a shared memory. Now, an instance can open at most one database at a time but a database can be opened by many instances. For example, you can query a database one at a time but then there are millions of users who can hit the database at a particular time.

What actually is a Database Shutdown?

Database shutdown simply means that you close the database and terminate the instance running at the particular moment. When a user initiates a shutdown until and unless it is complete the control is not returned back to the session. In case you try to connect to the database while it is performing a shutdown, Oracle throws ORA-01090.

Note: To do a shutdown, you need to connect to the database as SYSDBA or SYSOPER.

There are different modes that can bring down the database –

1. Shutdown immediate
2. Shutdown transactional
3. Shutdown abort
4. Shutdown normal

SHUTDOWN IMMEDIATE

When you do a shutdown immediate, the database terminates any executing SQL and terminates all the users. All the active transactions get terminated and rollback takes place. Although you always have the option of doing it your way, it is preferred that you do a shut down with immediate clause only in the below conditions –

1. In case, there is an automated an unattended backup required.
2. When a power shutdown is going to occur.
3. When the database or any application is not working properly and the users cannot log off or you don’t have the means to ask them to do so.

The shutdown command with the immediate clause proceeds with the following conditions –

1. Once you have done an immediate shutdown, no new connections or transactions are allowed.
2. The uncommitted transaction is rolled back.
3. Oracle database does not wait for the users to disconnect. The decision is made and the database disconnects all the active users and closes the ongoing transactions.

SHUTDOWN TRANSACTIONAL

When you do a shutdown with the transactional option, the database does not allow the users to make any new transaction. However, unlike the immediate shutdown, it waits for all the ongoing transactions to complete before it shuts down.

The shutdown command with transactional clause proceeds with the following conditions –

1. Neither a new connection nor a new transaction can be started once the statement is issued.
2. Once all the transactions are complete, the database disconnects any active user present.

The best part about the transactional shutdown is that it allows all the ongoing transactions to complete and also let some users remain active for a certain time.

SHUTDOWN ABORT

This is the most haphazard way of shutting down your database. You can instantaneously shutdown the database using the shutdown abort option. This is the quickest way of closing the database and when this happens you do not need to perform an instance recovery option once the shutdown is done.

This option should be chosen in any of the following cases –

1. The database or any of the applications is not working fine and there is no other option left for you.
2. You need to shutdown the database immediately. For instance, there is a power shutdown about to happen in a minute. In that case, it is better to go for this.
3. There are some issues when starting the database instance.

Aborted database proceeds with the following operations –

1. Now new connections are allowed, nor any new transactions can be performed.
2. Current SQL operations being performed by the client are immediately terminated.
3. Uncommitted transactions are not rolled back.
4. Any active user is disconnected.

SHUTDOWN NORMAL

Normal is the default clause used with the shutdown and is optional. Normal shutdown proceeds with the following conditions –

1. Now new connections are allowed after the statement has been executed. However, the active users can perform any new transactions if they prefer to.
2. The database waits for all the users to disconnect before it finally disconnects the database.

Note: The shutdown modes that wait for the users to disconnect like the normal mode, have a limit till which it can wait after which the database performs the abort operation and throws the error –ORA-01013. In case the database throws this error it simply means that the database is in an unpredictable state and even if the shutdown fails, you need to resubmit the job or try using SHUTDOWN ABORT.

The Tech World