Home / Index Page

How to Shutdown Oracle Database

Home Page/Index

Oracle SHUTDOWN

Summary: in this tutorial, you will learn how to use the Oracle SHUTDOWN statement to shut down the Oracle Database.

TL;DR

Use the SHUTDOWN IMMEDIATE command to shut down the Oracle Database gracefully:
SHUTDOWN IMMEDIATE

Introduction to the Oracle SHUTDOWN statement

To shut down a currently running Oracle Database instance, you use the SHUTDOWN command as follows:
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Let’s examine each option of the SHUTDOWN command.

SHUTDOWN NORMAL

The SHUTDOWN NORMAL option waits for the current users to disconnect from the database before shutting down the database. The database instance will not accept any further database connection.  The SHUTDOWN NORMAL does not require an instance recovery on the next database startup.
The NORMAL is the default option if you don’t explicitly specify any option. Therefore SHUTDOWN and SHUTDOWN NORMAL commands have the same effect.
The SHUTDOWN or SHUTDOWN NORMAL is not really practical because you practically cannot wait for all users to come back to their desks and disconnect from the database.

SHUTDOWN TRANSACTIONAL

The SHUTDOWN TRANSACTIONAL waits for all uncommitted transactions to complete before shutting down the database instance. This saves the work for all users without requesting them to log off.
The database instance also does not accept any new transaction after a SHUTDOWN TRANSACTIONAL . When completing all transactions, the database instance disconnects all the currently connected users from the database and shuts down.
The SHUTDOWN TRANSACTIONAL does not require any instance recovery procedure on the next database startup.
The optional LOCAL mode waits for only local transactions to complete, not all the transactions. Then it shuts down local instance. This option is useful in some cases e.g., a scheduled outage maintenance.

SHUTDOWN ABORT

The SHUTDOWN ABORT is not recommended and only used on some occasions. The SHUTDOWN ABORT has a similar effect as you unplug the power of the server. The database will be in an inconsistent state. Therefore, you should never use the SHUTDOWN ABORT command before backing up the database. If you try to do so, you may not be able to recover the backup.
It is recommended to use the SHUTDOWN ABORT only when you want to shut down the database instantaneously. For example, if you know a power shutdown is going to happen in a minute or you experience some problems when starting up a database instance.
The SHUTDOWN ABORT proceeds with the fastest possible shutdown of the database. However, it requires instance recovery on the next database startup.

SHUTDOWN IMMEDIATE

The SHUTDOWN IMMEDIATE is the most common and practical way to shut down the Oracle database.
The SHUTDOWN IMMEDIATE does not wait for the current users to disconnect from the database or current transactions to complete.
During the SHUTDOWN IMMEDIATE, all the connected sessions are disconnected immediately, all uncommitted transactions are rolled back, and the database completely shuts down.
After issuing the SHUTDOWN IMMEDIATE statement, the database will not accept any new connection. The statement will also close and dismount the database.
Unlike the SHUTDOWN ABORT option, the SHUTDOWN IMMEDIATE option does not require an instance recovery on the next database startup.
The following table illustrates the differences between the shutdown modes:


Shutdown ModesAITN
Allow new connectionNoNoNoNo
Wait until all current sessions endNoNoNoYes
Wait until all current transactions endNoNoYesYes
Force a checkpoint and close filesNoYesYesYes
Shutdown Modes:
  • A = ABORT
  • I = IMMEDIATE
  • T = TRANSACTIONAL
  • N = NORMAL

Notes

To issue the SHUTDOWN statement, you must connect to the database as SYSDBA, SYSOPER, SYSBACKUP, or SYSDG. If the current database is a pluggable database, the SHUTDOWN statement will close the pluggable database only. The consolidated instance will continue to run. On the other hand, if the current database is a CDB, the SHUTDOWN statement will close the CDB instance.

Oracle SHUTDOWN statement example

First, launch SQL*Plus:
> sqlplus
Second, log in to the Oracle database using the SYS user:
Enter user-name: sys as sysdba Enter password: <sys_password>
Third, check the current status of the Oracle instance:
SQL> select instance_name, status from v$instance;
Here is the output:
INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN
Fourth, issue the SHUTDOWN IMMEDIATE command:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
In this tutorial, you have learned how to use the Oracle SHUTDOWN statement to shut down the Oracle Database.

How to Startup Oracle Database

Home Page/Index 

How to Start Oracle Database

Summary: in this tutorial, you will learn how to use the Oracle STARTUP command to start an Oracle Database instance.
To start up a database instance, you use the STARTUP command:
STARTUP

When the Oracle Database starts an instance, it goes through the following stages: NOMOUNT, MOUNT, and OPEN.



Oracle Startup - database stages
The STARTUP command allows you to control the stage of the database instance.

1) NOMOUNT stage

In the NOMOUNT stage, Oracle carries the following steps:
  • First, search for a server parameter file in the default location. You can override the default behavior by using the SPFILE or PFILE parameters in the STARTUP command.
  • Next, read the parameter file to get the values of the initialization parameters.
  • Then, allocate the system global area (SGA) based on the initialization parameter settings.
  • After that, start the Oracle background processes such as SMON, PMON, and LGWR.
  • Finally, open the alert log and trace files and record all explicit parameters to the alert log in the valid parameter syntax.
At the NOMOUNT stage, Oracle does not associate the database with the instance.

2) MOUNT stage

In the MOUNT stage, Oracle associates a database with an instance. In other words, the instance mounts the database.
The instance carries the following steps to mount a database:
  • First, get the name of the database control files specified in the CONTROL_FILE initialization parameter.
  • Second, open the control files.
  • Third, find the name of data files and the online redo log files.
When a database is mounted, the database is only available to database administrators, not all users.

3) OPEN stage

In the OPEN stage, Oracle performs the following actions:
  • First, open the online data files in tablespaces other than the undo tablespaces.
  • Then, select an undo tablespace. The instance uses default undo tablespace if an undo tablespace is specified in the UNDO_TABLESPACE initialization parameter. Otherwise, it will select the first available undo tablespace.
  • Finally, open the online redo log files.
When Oracle opens a mounted database, the database is available for normal operations.
The following picture illustrates the Oracle database startup process:



Oracle Startup Stages

Oracle STARTUP command

The basic syntax of the STARTUP command is as follows:
STARTUP;
It is equivalent to start the database instance in the OPEN stage:
STARTUP OPEN;
If you want to start the database instance in a specific stage, you can use the following syntax:
STARTUP stage;
For example, to start up a database instance in the NOMOUNT stage, you use the following command:
STARTUP NOMOUNT;
To bring the database to the next stage, you use the ALTER DATABASE statement. For example, this statement brings the database from the NOMOUNT to the MOUNT stage:
ALTER DATABASE MOUNT;

Oracle STARTUP command example

First, launch the SQL*Plus program and log in to the Oracle Database as the SYS user.
Second, issue the SHUTDOWN IMMEDIATE command to shut down the database:
shutdown immediate;
Here is the output:
Database closed. Database dismounted. ORACLE instance shut down.
Third, start the database instance at the OPEN stage:
startup
Here is the output:



Oracle Startup
Fourth, shut down the instance again:
shutdown immediate;
Fifth, start the database instance at the MOUNT state:
startup mount;
The output is:
ORACLE instance started. Total System Global Area 2550136832 bytes Fixed Size 3835304 bytes Variable Size 738200152 bytes Database Buffers 1795162112 bytes Redo Buffers 12939264 bytes
Sixth, check the current status of the database instance by querying the v$instance view:
SELECT instance_name, status FROM v$instance;
Output:
INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED
Seventh, bring the database to the OPEN stage by using the ALTER DATABASE command:
ALTER DATABASE OPEN;
Output:
Database altered.
Finally, check the status of the database by executing the following statement:
SELECT instance_name, status FROM v$instance;
Now, the database is open and available for normal operations.
INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN
In this tutorial, you have learned how to start a database instance using the Oracle STARTUP command.

Popular Posts