Complete Guide to Starting and Stopping Oracle Database (Single Instance)

― Understand the Shutdown Modes in Depth ―

Starting and stopping Oracle Database correctly is a fundamental operation in day-to-day DBA tasks.
In this article, we will walk through:

How Oracle starts and stops in a single-instance configuration
The detailed differences between shutdown modes
Recommended practices with diagrams and practical examples

※ This guide focuses exclusively on single-instance environments. CDB/PDB, RAC, and Oracle Restart setups are excluded.

🔰 1. Prerequisites
1. [Diagram] Required Environment
🚀 2. Startup Process and Command
1. [Diagram] Oracle Startup Phases
2. ✅ Startup Command (Linux)
⛔ 3. Shutdown Modes Explained in Detail
📊 4. Shutdown Mode Comparison Table
🧠 5. How to Check the Current State
1. SQL to Check Instance Status
2. Linux Process Check
✅ 6. Summary

🔰 1. Prerequisites

Before performing startup or shutdown operations, ensure the following conditions are met.

[Diagram] Required Environment

+-----------------------------------------+
| Oracle installed on a Linux environment |
+-----------------------------------------+
              │
              ▼
+-----------------------------+
| Logged in as 'oracle' user  |
+-----------------------------+
              │
              ▼
+----------------------------------------------+
| ORACLE_HOME, ORACLE_SID, and PATH are set    |
+----------------------------------------------+
              │
              ▼
+-----------------------------+
| SYSDBA access via SQL*Plus   |
+-----------------------------+

🚀 2. Startup Process and Command

Oracle startup progresses through three internal stages. The STARTUP command executes all of them sequentially.

[Diagram] Oracle Startup Phases

STARTUP
  └─→ NOMOUNT  (Instance starts: SGA + background processes)
         └─→ MOUNT  (Control files are read)
               └─→ OPEN  (Data files and REDO logs opened; DB available)

✅ Startup Command (Linux)

# Set environment variables
$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
$ export ORACLE_SID=orcl
$ export PATH=$ORACLE_HOME/bin:$PATH

# Connect as SYSDBA
$ sqlplus / as sysdba

# Start the database
SQL> STARTUP

⛔ 3. Shutdown Modes Explained in Detail

Oracle provides four shutdown modes via the SHUTDOWN command. Each has different behaviors and implications.

Mode	Detailed Behavior
`NORMAL`	Waits until all connected users log out. No new connections allowed. Session ends must be user-initiated. Not recommended for operational use.
`IMMEDIATE`	Blocks new connections, aborts any running SQL, disconnects all sessions, and rolls back uncommitted transactions. Most commonly used and safest method.
`TRANSACTIONAL`	Rejects new connections. Waits for ongoing transactions to finish before disconnecting and shutting down. Rarely used; suitable for batch systems.
`ABORT`	Immediately terminates the instance without cleaning up memory or rolling back. Requires instance recovery on the next startup. Use only as a last resort.

[Diagram] Visualizing Shutdown Modes

┌────────────┐
│ NORMAL      │ → Passive wait (users must disconnect)
└────────────┘

┌──────────────┐
│ IMMEDIATE     │ → Terminate SQL, rollback, and disconnect
└──────────────┘

┌────────────────┐
│ TRANSACTIONAL   │ → Waits for transactions to finish
└────────────────┘

┌────────────┐
│ ABORT       │ → Immediate forced shutdown (unsafe)
└────────────┘

✅ Recommended Shutdown Command

$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE

This mode safely disconnects users and rolls back transactions without data corruption or recovery requirement.

❗ When Forced Shutdown is Unavoidable (ABORT)

If shutdown hangs or the system is unresponsive:

SQL> SHUTDOWN ABORT
SQL> STARTUP
SQL> SHUTDOWN IMMEDIATE

Always follow SHUTDOWN ABORT with a clean STARTUP and SHUTDOWN IMMEDIATE to return to a stable state.

📊 4. Shutdown Mode Comparison Table

Mode	New Connections	Interrupt SQL	Wait for TX	Shutdown Speed	Recovery Required	Recommended?
NORMAL	No	No	No	Waits for logout	No	× (Not ideal)
IMMEDIATE	No	Yes	No	Fast	No	◎ (Best choice)
TRANSACTIONAL	No	No	Yes	Medium	No	△ (Rare use)
ABORT	No	No	No	Instant	Yes (on startup)	× (Last resort)

🧠 5. How to Check the Current State

SQL to Check Instance Status

SQL> SELECT status FROM v$instance;

STATUS
-------
OPEN

Linux Process Check

$ ps -ef | grep pmon
oracle   12345     1  0 10:00 ? 00:00:00 ora_pmon_orcl

If ora_pmon_<SID> is running, your instance is up.

✅ 6. Summary

Topic	Summary
Startup command	`STARTUP`
Shutdown (recommended)	`SHUTDOWN IMMEDIATE`
Emergency shutdown	`SHUTDOWN ABORT` → `STARTUP` → `SHUTDOWN IMMEDIATE`
SYSDBA access required	Use `sqlplus / as sysdba`
Shutdown mode usage	Use `IMMEDIATE` unless special cases demand `TRANSACTIONAL` or `ABORT`

Correctly shutting down Oracle Database is critical to maintaining data consistency and system stability.
Understanding the difference between each shutdown mode will help you make informed decisions in real-world operations.

[reference]
Database Reference