The SHUTDOWN NORMAL option waits for the present users to disconnect from the database before shutting down the database. The database instance won’t accept any longer database connection. The SHUTDOWN NORMAL doesn’t require an instance recovery on subsequent database startup.

The NORMAL is that the default if you don’t explicitly specify any option. Therefore SHUTDOWN and SHUTDOWN NORMAL commands have an equivalent effect.

The SHUTDOWN or SHUTDOWN NORMAL isn’t really practical because you practically cannot await all users to return back to their desks and disconnect from the database.

SHUTDOWN TRANSACTIONAL

The SHUTDOWN TRANSACTIONAL waits for all uncommitted transactions to finish before shutting down the database instance. this protects the work for all users without requesting them to sign off .

The database instance also doesn’t 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 doesn’t require any instance recovery procedure on subsequent database startup.

The optional LOCAL mode waits for less than local transactions to finish, not all the transactions. Then it shuts down local instance. this feature is beneficial in some cases e.g., a scheduled outage maintenance.

SHUTDOWN ABORT

The SHUTDOWN ABORT isn’t recommended and only used on some occasions. The SHUTDOWN ABORT features a similar effect as you unplug the facility of the server. The database is going to be in an inconsistent state. Therefore, you ought to never use the SHUTDOWN ABORT command before backing up the database. If you are trying to try to so, you’ll not be ready to recover the backup.

It is recommended to use the SHUTDOWN ABORT only you would like to shut down the database instantaneously. for instance, if you recognize an influence shutdown goes to happen during a minute otherwise you experience some problems when beginning a database instance.

The SHUTDOWN ABORT proceeds with the fastest possible shutdown of the database. However, it requires instance recovery on subsequent database start-up.

SHUTDOWN IMMEDIATE

The SHUTDOWN IMMEDIATE is that the commonest and practical thanks to shut down the Oracle database.

The SHUTDOWN IMMEDIATE doesn’t await the present users to disconnect from the database or current transactions to finish.

During the SHUTDOWN IMMEDIATE, all the connected sessions are disconnected immediately, all uncommitted transactions are rolled back, and therefore the database completely shuts down.

After issuing the SHUTDOWN IMMEDIATE statement, the database won’t accept any new connection. The statement also will close and dismount the database.

Unlike the SHUTDOWN ABORT option, the SHUTDOWN IMMEDIATE option doesn’t require an instance recovery on subsequent database start-up.

The following table illustrates the differences between the shutdown modes:

Shutdown Modes A I T N

Allow new connection No No No No

Wait until all current sessions end No No No Yes

Wait until all current transactions end No No Yes Yes

Force a checkpoint and shut files No Yes Yes Yes

Shutdown Modes:

A = ABORT
I = IMMEDIATE
T = TRANSACTIONAL
N = NORMAL

Notes

To issue the SHUTDOWN statement, you want to hook up with the database as SYSDBA, SYSOPER, SYSBACKUP, or SYSDG. If the present database may be a pluggable database, the SHUTDOWN statement will close the pluggable database only. The consolidated instance will still run. On the opposite hand, if the present database may be 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:

Third, check the present status of the Oracle instance:

SQL> select instance_name, status from v$instance;

Here is that the output:

INSTANCE_NAME STATUS

—————- ————

orcl OPEN

Fourth, issue the SHUTDOWN IMMEDIATE command:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down . How to shut down the Oracle Database

In this blog, you’ve got learned the way 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 employ the SHUTDOWN command as follows:

SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]

Let’s examine each option of the SHUTDOWN command.

SHUTDOWN NORMAL

The NORMAL is that the default if you don’t explicitly specify any option. Therefore, SHUTDOWN and SHUTDOWN NORMAL commands have an equivalent effect.

The SHUTDOWN or SHUTDOWN NORMAL isn’t really practical because you practically cannot await all users to return back to their desks and disconnect from the database.

SHUTDOWN TRANSACTIONAL

The SHUTDOWN TRANSACTIONAL waits for all uncommitted transactions to finish before shutting down the database instance. this protects the work for all users without requesting them to sign off.

The database instance also doesn’t 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 doesn’t require any instance recovery procedure on subsequent database start-up.

SHUTDOWN ABORT

The SHUTDOWN ABORT proceeds with the fastest possible shutdown of the database. However, it requires instance recovery on subsequent database start-up.

SHUTDOWN IMMEDIATE

The SHUTDOWN IMMEDIATE is that the commonest and practical thanks to shut down the Oracle database.

The SHUTDOWN IMMEDIATE doesn’t await the present users to disconnect from the database or current transactions to finish.

During the SHUTDOWN IMMEDIATE, all the connected sessions are disconnected immediately, all uncommitted transactions are rolled back, and therefore the database completely shuts down.

After issuing the SHUTDOWN IMMEDIATE statement, the database won’t accept any new connection. The statement also will close and dismount the database.

Unlike the SHUTDOWN ABORT option, the SHUTDOWN IMMEDIATE option doesn’t require an instance recovery on subsequent database start-up.

The following table illustrates the differences between the shutdown modes:

Shutdown Modes A I T N

Allow new connection No No No No

Wait until all current sessions end No No No Yes

Wait until all current transactions end No No Yes Yes

Force a checkpoint and shut files No Yes Yes Yes

Shutdown Modes:

A = ABORT
I = IMMEDIATE
T = TRANSACTIONAL
N = NORMAL

Notes

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:

Third, check the present status of the Oracle instance:

SQL> select instance_name, status from v$instance;

Here is that the output:

INSTANCE_NAME STATUS

—————- ————

orcl OPEN

Fourth, issue the SHUTDOWN IMMEDIATE command:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

So, this brings us to the end of blog. This Tecklearn ‘How to shut down the Oracle Database’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

412