Oracle DB Startup/Shutdown Modes: Modes, Files, Commands & Recovery Impact

TAKEAWAYS:

• Oracle databases start in NOMOUNT → MOUNT → OPEN modes, each requiring specific files and commands (STARTUP, ALTER DATABASE MOUNT, ALTER DATABASE OPEN).
• Shutdown modes (NORMAL, TRANSACTIONAL, IMMEDIATE, ABORT) determine how Oracle closes connections and writes redo logs, directly impacting recovery speed and data integrity.
• The control file (stored in CONTROL_FILES parameter) is mandatory in MOUNT and OPEN modes and must be multiplexed for disaster recovery.
• RESTRICTED mode allows DBAs to perform maintenance without user access, while FORCE mode bypasses all checks for emergency recovery.
• Backup and recovery operations (e.g., RMAN) must align with the current mode—restores require MOUNT, while backups can run in OPEN mode if archivelog is enabled.
• Real-world tie: NEPSE’s stock database uses SHUTDOWN IMMEDIATE nightly to apply security patches without disrupting trading the next day.

1. Oracle Database Startup Modes: The Boot Sequence

Oracle databases do not start instantly like a web server. Instead, they progress through three distinct modes before users can access data. Each mode requires specific files and validates critical components. Visualize the sequence as a checkpoint system:

stateDiagram-v2
    [*] --> NOMOUNT: STARTUP NOMOUNT
    NOMOUNT --> MOUNT: ALTER DATABASE MOUNT
    MOUNT --> OPEN: ALTER DATABASE OPEN
    OPEN --> [*]
    MOUNT --> RESTRICTED: ALTER DATABASE OPEN RESTRICTED
    MOUNT --> FORCE: STARTUP FORCE

Key Files Required at Each Mode

How to Start a Database in Each Mode

Use these SQL*Plus commands (tested on Oracle 19c/21c):

-- Start in NOMOUNT (instance only, no files checked)
STARTUP NOMOUNT;

-- Mount the database (validates control file and redo logs)
ALTER DATABASE MOUNT;

-- Open for normal operations
ALTER DATABASE OPEN;

-- Open in RESTRICTED mode (DBA-only access)
ALTER DATABASE OPEN RESTRICTED;

-- Force-start (bypasses corruption checks)
STARTUP FORCE;

Worked Example: Daraz’s Order Processing Database Daraz’s order-processing database (running Oracle) must restart daily to apply security patches. The DBA uses:

-- Step 1: Check current mode (OPEN)
SQL> SELECT status FROM v$instance;
STATUS
--------
OPEN

-- Step 2: Shutdown normally (waits for transactions to complete)
SQL> SHUTDOWN IMMEDIATE;

-- Step 3: Start in RESTRICTED mode to apply patches
SQL> STARTUP RESTRICTED;
SQL> ALTER DATABASE OPEN; -- Now open for users

Why RESTRICTED?

• Prevents users from submitting orders during the 5-minute patch window.
• Ensures no transactions are lost (unlike ABORT, which risks data corruption).

2. Oracle Database Shutdown Modes: Graceful vs. Emergency

Shutting down a database incorrectly can corrupt redo logs or leave transactions incomplete. Oracle offers four shutdown modes, each balancing speed and safety:

sequenceDiagram
    participant DBA
    participant Oracle
    DBA->>Oracle: SHUTDOWN IMMEDIATE
    Oracle-->>DBA: Terminates sessions
    Oracle->>Oracle: Rolls back uncommitted txns
    Oracle-->>DBA: Database closed
    note right of Oracle: Redo logs may need recovery
    DBA->>Oracle: STARTUP MOUNT
    Oracle->>Oracle: Validates control file
    Oracle-->>DBA: Database mounted
    DBA->>Oracle: ALTER DATABASE OPEN
    Oracle-->>DBA: Database open (with recovery)

flowchart TD
    A["SHUTDOWN"] --> B["NORMAL"]
    A --> C["TRANSACTIONAL"]
    A --> D["IMMEDIATE"]
    A --> E["ABORT"]
    B -->|"Waits for"| C
    C -->|"Waits for"| D
    D -->|"No wait"| E
    E -->|"Risk of corruption"| F["Recovery needed"]

Worked Example: Ncell’s Billing System Failover

Ncell’s billing database (Oracle 12c) must failover to a standby site during a power outage. The DBA uses:

-- Primary site loses power → ABORT shutdown (no time for graceful shutdown)
SQL> SHUTDOWN ABORT; -- Crashes immediately

-- On standby site (already in MOUNT mode):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; -- Syncs redo logs
SQL> ALTER DATABASE OPEN READ ONLY; -- Now serving read-only queries

Why ABORT?

• Power outage leaves no time for IMMEDIATE or NORMAL.
• Standby database uses RECOVER MANAGED to apply redo logs from the primary’s archivelogs.

3. Special Startup Modes: RESTRICTED and FORCE

RESTRICTED Mode: Maintenance Without Disruption

• Command: ALTER DATABASE OPEN RESTRICTED
• Behavior:
  • Database opens, but only users with RESTRICTED SESSION privilege can connect.
  • Blocks regular users (e.g., CREATE USER, DROP TABLE).
• Real-World Use:
  • Khalti’s payment gateway: DBAs run ALTER SYSTEM FLUSH SHARED_POOL to clear memory leaks during peak hours without affecting transactions.

FORCE Mode: Emergency Recovery

• Command: STARTUP FORCE
• Behavior:
  • Bypasses all checks (control file, redo logs, datafile corruption).
  • Danger: May start a corrupted database.
• When to Use:
  • After SHUTDOWN ABORT due to hardware failure.
  • Before running RECOVER DATABASE (manual or RMAN).
• Example:

  -- After a disk crash, force-start to access metadata
  SQL> STARTUP FORCE;
  SQL> RECOVER DATABASE; -- Then recover using backups
  

4. Startup/Shutdown and Backup/Recovery: Critical Interactions

Backup and recovery operations depend on the current mode:

Worked Example: eSewa’s Daily Backup Policy eSewa’s Oracle database (handling 1M+ transactions/day) uses:

1. Nightly backup in OPEN mode (level 0 full backup + incremental):

   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   

2. Weekly restore test in MOUNT mode:

   STARTUP MOUNT;
   RMAN> RESTORE DATABASE;
   RECOVER DATABASE;
   

3. Emergency recovery after crash:

   STARTUP FORCE; -- Bypass corruption
   RECOVER DATABASE USING BACKUP CONTROLFILE;
   

Why OPEN for backups?

• Archivelog mode ensures no data loss.
• PLUS ARCHIVELOG backs up redo logs for point-in-time recovery.

5. Common Pitfalls and DBA Best Practices

Mistake 1: Skipping MOUNT Mode for Recovery

• Problem: Running RECOVER DATABASE in OPEN mode fails with:

  ORA-01102: file ... needs media recovery
  

• Fix: Always ALTER DATABASE MOUNT before recovery.

Mistake 2: Using ABORT Without Backup

• Problem: SHUTDOWN ABORT + STARTUP FORCE may start a corrupted database.
• Fix: Take a control file autobackup before critical operations:

  RMAN> BACKUP CURRENT CONTROLFILE FOR RECOVERY OF AUTOBACKUP;
  

Mistake 3: Ignoring RESTRICTED Mode for Patches

• Problem: Applying patches in OPEN mode risks:
  • User sessions locking tables.
  • Partial patch application (e.g., missing ALTER SYSTEM commands).
• Fix: Use STARTUP RESTRICTED + ALTER SYSTEM commands.

In the Real World

1. Ncell’s Core Billing System

   • Idea Used: SHUTDOWN TRANSACTIONAL + STARTUP RESTRICTED
   • How: During monthly billing cycles, Ncell’s Oracle database runs:

     SHUTDOWN TRANSACTIONAL; -- Waits for current calls to complete
     STARTUP RESTRICTED;    -- Applies tax updates
     ALTER DATABASE OPEN;   -- Resumes service
     

   • Impact: Avoids billing errors while minimizing downtime.

2. Daraz’s Order Processing Database

   • Idea Used: Control file multiplexing + STARTUP MOUNT
   • How: Daraz maintains 3 multiplexed control files across servers. If the primary fails:

     -- On backup server:
     STARTUP NOMOUNT PFILE='/config/init.ora';
     ALTER DATABASE MOUNT USING '/backup/control02.ctl';
     RECOVER MANAGED STANDBY DATABASE;
     

   • Impact: Zero data loss during server failures.

3. NEPSE’s Stock Trading Database

   • Idea Used: Scheduled SHUTDOWN IMMEDIATE + STARTUP NORMAL
   • How: NEPSE shuts down its Oracle database nightly to:
     • Apply security patches (e.g., SQL injection fixes).
     • Rebuild indexes without affecting next-day trading.
   • Impact: Prevents fraud while ensuring uptime.

Exam Tip

1. Memorize the 3 Startup Modes and Their Files

   • NOMOUNT: No files checked (only init.ora).
   • MOUNT: Control file + redo logs validated.
   • OPEN: Datafiles opened for I/O.
   • Exam trick: Questions often ask, “Which mode is required to restore a control file?” → NOMOUNT.

2. Shutdown Modes: Speed vs. Safety

   • NORMAL/TRANSACTIONAL: Safe but slow (waits for users/transactions).
   • IMMEDIATE/ABORT: Fast but risky (use only for emergencies).
   • Exam trick: “When would you use SHUTDOWN ABORT?” → Hardware failure (never for software updates).

3. Backup/Recovery Mode Dependencies

   • Backup: Can run in OPEN (with archivelog) or MOUNT (for cold backups).
   • Restore: Requires MOUNT mode.
   • Recovery: Requires MOUNT mode (RECOVER DATABASE).
   • Exam trick: “Why does RMAN fail in OPEN mode?” → Because it needs to mount the database to access metadata.

4. RESTRICTED and FORCE Modes

   • RESTRICTED: DBA-only access (e.g., “How would you apply a patch without affecting users?”).
   • FORCE: Emergency bypass (e.g., “The database crashed. What’s the first command?” → STARTUP FORCE).

5. Real-World Scenarios

   • Always tie answers to Nepali companies (e.g., “How would Daraz handle a database crash?”).
   • Use commands in your answers (examiners reward SQL*Plus syntax).

Final Checklist for Full Marks ✅ Define NOMOUNT/MOUNT/OPEN and their file requirements. ✅ Compare NORMAL/IMMEDIATE/ABORT shutdowns with examples. ✅ Explain RESTRICTED and FORCE modes with use cases. ✅ Link startup/shutdown to backup/recovery (e.g., RMAN needs MOUNT). ✅ Include one Nepali company example (Ncell, Daraz, NEPSE). ✅ Use SQL commands in your explanations.

In the real world

• NEPSE’s stock database uses SHUTDOWN IMMEDIATE nightly to apply security patches without disrupting trading the next day. The database enters MOUNT mode, patches are applied in RESTRICTED mode, then reopened—ensuring no transactions are lost during the 5-minute window.
• Pathao’s payment system employs SHUTDOWN TRANSACTIONAL during glitches to disconnect users immediately while waiting for in-progress transactions to commit, minimizing financial risks.
• Ncell’s billing system relies on SHUTDOWN ABORT during power outages, then recovers via a standby database using ALTER DATABASE RECOVER MANAGED STANDBY DATABASE to apply redo logs from the primary site.