ORACLE STARTUP AND SHUTDOWN SEQUENCE
1. Introduction
Starting and shutting down an Oracle database are fundamental DBA activities. The startup sequence moves through NOMOUNT, MOUNT and OPEN. Shutdown can be performed using NORMAL, TRANSACTIONAL, IMMEDIATE or ABORT modes.
2. Oracle Startup Sequence
STARTUP | v Read PFILE / SPFILE | v Allocate SGA | v Start Background Processes | v NOMOUNT | v Open Control Files | v MOUNT | v Open Datafiles and Online Redo Logs | v OPEN
3. STARTUP NOMOUNT
STARTUP NOMOUNT;
Oracle reads the initialization parameter file, allocates the SGA and starts the required background processes. The instance is started, but the database is not mounted.
- Instance → Started
- SGA → Allocated
- Background processes → Started
- Control files → Not opened
- Datafiles → Not opened
SELECT status FROM v$instance; -- STARTED
4. STARTUP MOUNT
STARTUP MOUNT; -- or, from NOMOUNT: ALTER DATABASE MOUNT;
Oracle opens the control files and mounts the database. Datafiles are not opened for normal user access.
- Instance → Started
- Control files → Open
- Datafiles → Not open
- Database → Mounted
SELECT status FROM v$instance; -- MOUNTED
Common DBA uses: RMAN recovery, media recovery, ARCHIVELOG configuration and certain Data Guard/control-file operations.
5. STARTUP OPEN
ALTER DATABASE OPEN; -- or simply: STARTUP;
Oracle opens the database files and online redo logs and performs the required consistency checks. If the previous shutdown was abnormal, instance recovery may be performed.
- Instance → Running
- Control files → Open
- Datafiles → Open
- Database → Available for normal users
SELECT status FROM v$instance; -- OPEN SELECT name, open_mode FROM v$database;
6. Startup Status — Easy Memory
NOMOUNT → Instance MOUNT → Control Files OPEN → Database
7. STARTUP Variations
STARTUP NOMOUNT;— Starts only the instance.STARTUP MOUNT;— Starts the instance and mounts the database.STARTUP;— Starts the instance, mounts and opens the database.STARTUP RESTRICT;— Opens the database in restricted mode.STARTUP FORCE;— Forces a shutdown/startup sequence when normal startup is not possible.STARTUP PFILE='/tmp/initORCL.ora';— Starts using a specified PFILE.
8. Oracle Shutdown Sequence
SHUTDOWN | v Stop New Connections | v End / Disconnect Sessions | v Close Database | v Unmount Database | v Shutdown Instance | v Instance OFF
9. SHUTDOWN NORMAL
SHUTDOWN NORMAL;
Oracle prevents new connections and waits for users to disconnect normally. It can take a long time if a session remains connected.
10. SHUTDOWN TRANSACTIONAL
SHUTDOWN TRANSACTIONAL;
Oracle prevents new connections and allows active transactions to complete before shutting down.
- No new connections
- Active transactions can complete
- Controlled shutdown
11. SHUTDOWN IMMEDIATE
SHUTDOWN IMMEDIATE;
This is the commonly used shutdown mode for planned DBA maintenance. Oracle prevents new connections, terminates active sessions, rolls back uncommitted work, closes the database, unmounts it and stops the instance.
SHUTDOWN IMMEDIATE | v Stop New Connections | v Terminate Sessions | v Rollback Uncommitted Work | v Close Database | v Unmount Database | v Shutdown Instance
12. SHUTDOWN ABORT
SHUTDOWN ABORT;
This is the most forceful shutdown. The instance is terminated without normal shutdown cleanup. On the next startup, Oracle performs instance recovery if required.
SHUTDOWN ABORT | v Instance Terminates | v STARTUP | v Instance Recovery (if required) | v Database OPEN
13. Shutdown Modes — Comparison
| Mode | New Connections | Active Work | Typical Use |
|---|---|---|---|
| NORMAL | No | Waits for users | Rare / controlled shutdown |
| TRANSACTIONAL | No | Transactions complete | Controlled shutdown |
| IMMEDIATE | No | Sessions terminated; uncommitted work rolled back | Normal maintenance |
| ABORT | No | Immediately terminated | Emergency / unresponsive instance |
14. Practical Startup Example
sqlplus / as sysdba STARTUP; SELECT status FROM v$instance; SELECT name, open_mode FROM v$database;
15. Manual Startup Example
STARTUP NOMOUNT; ALTER DATABASE MOUNT; ALTER DATABASE OPEN;
16. Practical Shutdown Example
sqlplus / as sysdba SHUTDOWN IMMEDIATE;
17. Important DBA Commands
-- Instance status SELECT status FROM v$instance; -- Database status SELECT name, open_mode FROM v$database; -- Instance name SHOW PARAMETER INSTANCE_NAME; -- Database name SHOW PARAMETER DB_NAME; -- Control file locations SHOW PARAMETER CONTROL_FILES;
18. Interview Questions
- What are the three stages of startup?
NOMOUNT → MOUNT → OPEN. - What happens in NOMOUNT?
Oracle reads PFILE/SPFILE, allocates memory and starts background processes. - What happens in MOUNT?
Oracle opens the control files and mounts the database. - What happens in OPEN?
Oracle opens the database for normal user access and performs required checks/recovery. - Which shutdown mode is commonly used for planned maintenance?
SHUTDOWN IMMEDIATE. - What happens to uncommitted work during SHUTDOWN IMMEDIATE?
It is rolled back. - What is SHUTDOWN ABORT?
An immediate instance termination; recovery may be required at the next startup. - Which process performs instance recovery?
SMON (System Monitor).
19. Quick DBA Cheat Sheet
STARTUP | +-- NOMOUNT → Instance Started | +-- MOUNT → Control Files Open | +-- OPEN → Database Available SHUTDOWN | +-- NORMAL → Wait for users | +-- TRANSACTIONAL → Wait for transactions | +-- IMMEDIATE → Terminate sessions + rollback | +-- ABORT → Immediate termination
Conclusion
Remember: NOMOUNT means the instance is started, MOUNT means the control files are open and the database is mounted, and OPEN means the database is available for normal users. For planned maintenance, SHUTDOWN IMMEDIATE is generally the preferred controlled shutdown method.