Oracle Database Architecture Interview Questions – Complete DBA Guide

Introduction

Preparing for an Oracle DBA interview?
Interviewers often focus on database architecture fundamentals and real production scenarios rather than just textbook definitions.

This comprehensive guide covers Oracle Database Architecture interview questions that truly matter — how Oracle actually works under the hood. You’ll learn about instance and memory structures (SGA, PGA), background processes, startup and shutdown sequences, automatic recovery, storage architecture, performance monitoring views, and practical troubleshooting.

Everything is explained in simple, interview-oriented language with real-world examples. Understand not just what happens inside Oracle, but why it happens and how to handle production situations — exactly what hiring managers expect from experienced DBAs.

0. Fundamentals First

What is Data?

Data = Raw facts without context.

Examples:

• 25
• John
• 2024-12-21

What is Information?

Information = Data with context or meaning.

Example:

• John is 25 years old and joined on 2024-12-21.

What is a Database?

Database = An organized collection of structured data stored electronically.

Key benefits:

• Eliminates data redundancy
• Ensures data integrity
• Enables concurrent access
• Provides security and backup

Important:
A database stores both data and metadata (data about data).

What is DBMS (Database Management System)?

DBMS = Software used to create, manage, and access databases.

Key points:

• Acts as an interface between users/applications and the database
• Controls data storage, retrieval, and security

Examples:

• Oracle
• MySQL
• PostgreSQL
• SQL Server

What is RDBMS (Relational DBMS)?

RDBMS = A DBMS based on the relational model where data is stored in tables (rows and columns).

Key features:

• Uses SQL for data manipulation
• Enforces ACID properties (Atomicity, Consistency, Isolation, Durability)
• Oracle is an RDBMS

Interview Questions

Q1: What is the difference between data and database?

Answer:
Data is raw facts. A database is an organized collection of related data stored systematically with relationships, constraints, and metadata for efficient retrieval and management.

Q2: Why use a database instead of flat files?

Answer:
Databases provide:

• Data integrity through constraints
• Concurrent access control
• ACID transactions
• Efficient querying using indexes
• Security and access control
• Backup and recovery mechanisms
• Reduced data redundancy

Q3: What are ACID properties?

Answer:

• Atomicity: Transaction is all-or-nothing
• Consistency: Database moves from one valid state to another
• Isolation: Concurrent transactions do not interfere
• Durability: Committed data survives system failures

1. Instance vs Database

What is a Database?

Database = Physical files stored on disk.

Includes:

• Datafiles
• Control files
• Redo log files

Nature:

• Permanent
• Exists even when the instance is down

What is an Instance?

Instance = Memory structures + background processes that access the database.

Includes:

• Memory: SGA, PGA
• Background processes (DBWn, LGWR, SMON, PMON, etc.)

Nature:

• Temporary
• Exists in memory (RAM)

Key Point

• One database can be accessed by multiple instances (RAC).
• In a non-RAC environment, the relationship is typically 1 instance : 1 database.

Analogy

• Database = Your house (physical structure)
• Instance = People living in the house (active users/processes)

Interview Questions

Q1: What is the difference between an instance and a database?

Answer:
An instance consists of memory and background processes and exists in RAM.
A database consists of physical files stored on disk and is permanent.
The instance must be started to access the database. In RAC, multiple instances can access a single database.

Q2: Can you have a database without an instance?

Answer:
No. Without an instance, the database files cannot be accessed.
The database exists on disk, but it is unusable without instance memory and processes.

Q3: Can you have an instance without a database?

Answer:
Yes, temporarily.
When you issue STARTUP NOMOUNT, the instance is started but the database is not mounted.
This state is used for database creation or control file recreation.

Q4: What happens if the instance crashes?

Answer:
The database files remain intact on disk.
When the instance is restarted, SMON performs automatic instance recovery using redo logs to bring the database to a consistent state.

2. Memory Architecture

A. SGA (System Global Area) – Shared Memory

Purpose:
Shared memory allocated at instance startup. Used by all users and background processes.

1. Database Buffer Cache

Purpose:

• Stores copies of data blocks read from datafiles
• Oracle always checks buffer cache first before disk I/O

Key points:

• Size parameter: DB_CACHE_SIZE or auto-managed via SGA_TARGET
• Uses LRU (Least Recently Used) algorithm for eviction

Types of buffers:

• Dirty buffers: Modified but not yet written to disk
• Clean buffers: Not modified or already written to disk
• Pinned buffers: Currently being accessed

DBA concern:
Too small buffer cache ⇒ increased physical reads ⇒ slow queries

2. Shared Pool

Stores parsed SQL, PL/SQL code, and metadata.

a) Library Cache

• Stores parsed SQL, PL/SQL code, execution plans
• Avoids re-parsing identical SQL

Parsing types:

• Hard parse: New SQL, creates execution plan (CPU intensive)
• Soft parse: Reuses existing plan (fast)

Components:

• SQL Area
• PL/SQL Area
• Java Pool Cache

b) Data Dictionary Cache (Row Cache)

• Stores metadata about:
  • Tables
  • Users
  • Privileges
  • Tablespaces
• Cached from SYS schema objects

c) Result Cache (19c)

• Stores query results for reuse
• Improves performance for repetitive queries
• Size controlled by SHARED_POOL_SIZE

DBA concern:
Insufficient size ⇒ hard parses spike ⇒ CPU saturation

3. Redo Log Buffer

• Small circular buffer for redo entries (change vectors)
• Written to redo log files by LGWR

Key points:

• Size parameter: LOG_BUFFER (10–16 MB usually sufficient)
• Rarely needs tuning in modern systems

DBA concern:
Too small ⇒ LGWR cannot keep up ⇒ "log file sync" waits

4. Large Pool (Optional but Recommended)

Used for:

• RMAN backup and restore
• Parallel execution message buffers
• Shared server (MTS) session memory

Parameter: LARGE_POOL_SIZE
Best practice: At least 128MB if RMAN is used

5. Java Pool

• Used for Java stored procedures and SQLJ
• Parameter: JAVA_POOL_SIZE
• Not required if Java is not used in the database

6. Streams Pool

• Used by Oracle Streams and GoldenGate
• Parameter: STREAMS_POOL_SIZE

7. Fixed SGA

• Internal housekeeping structures
• Stores instance state information
• Size cannot be controlled by DBA

Interview Questions (SGA)

Q1: What is SGA and its main components?

Answer:
SGA is shared memory allocated at instance startup.
Main components include:

• Database Buffer Cache
• Shared Pool
• Redo Log Buffer
• Optional pools like Large Pool and Java Pool

Q2: What is the difference between hard parse and soft parse?

Answer:

• Hard parse: New SQL, execution plan created, CPU intensive
• Soft parse: Existing plan reused, very fast
• Use bind variables to reduce hard parses

Q3: How to reduce hard parses?

Answer:

1. Use bind variables
2. Increase SHARED_POOL_SIZE
3. Set CURSOR_SHARING=FORCE (last resort)
4. Check V$SQL for similar SQL with different literals

Q4: What is stored in Database Buffer Cache?

Answer:
Copies of data blocks (table and index blocks).
Modified blocks become dirty until written to disk by DBWn.

Q5: What happens when buffer cache is full?

Answer:
LRU evicts clean buffers.
If no clean buffers are available, DBWn writes dirty buffers to disk, causing "free buffer waits".

B. PGA (Program Global Area) – Private Memory

Purpose:
Private memory for each server process. Not shared.

What is stored in PGA?

• Session memory (logon info, session state)
• Private SQL area (bind info, runtime data)
• Sort area (ORDER BY, GROUP BY)
• Hash area (hash joins)
• Bitmap merge area

PGA Parameters

• PGA_AGGREGATE_TARGET – Total PGA for all sessions
• PGA_AGGREGATE_LIMIT – Hard limit (19c)
• WORKAREA_SIZE_POLICY=AUTO (recommended)

How Oracle allocates PGA

• Estimates memory per operation
• Distributes PGA among active sessions
• If insufficient, spills to TEMP tablespace

DBA concern:
Small PGA ⇒ disk-based sorts ⇒ very slow performance

Monitoring views:

• V$PGASTAT
• V$PROCESS
• V$SQL_WORKAREA

Interview Questions (PGA)

Q1: Difference between SGA and PGA?

Answer:

• SGA: Shared, allocated at startup
• PGA: Private, per process, dynamic

Q2: What happens if PGA is insufficient?

Answer:
Sort and hash operations spill to TEMP tablespace, causing "direct path read/write temp" waits.

Q3: How to identify PGA undersizing?

SELECT name, value
FROM v$pgastat
WHERE name IN ('over allocation count','global memory bound');

If over allocation count > 0, increase PGA.

Q4: What is PGA_AGGREGATE_LIMIT (19c)?

Answer:
Hard limit on total PGA usage. Oracle terminates sessions using excessive PGA to protect the instance.

C. Memory Management Modes (19c)

1. Automatic Shared Memory Management (ASMM) – Recommended

SGA_TARGET = 10G
SGA_MAX_SIZE = 12G
PGA_AGGREGATE_TARGET = 3G

How it works:

• Oracle auto-tunes SGA components
• Minimums can be set (DB_CACHE_SIZE, SHARED_POOL_SIZE)
• Restart required only to increase SGA_MAX_SIZE

Best for: Production systems

2. Automatic Memory Management (AMM)

MEMORY_TARGET = 13G
MEMORY_MAX_TARGET = 16G

Limitations:

• Not supported with HugePages on Linux
• Overhead on large SGA systems

Best for: Small DBs, test/dev

3. Manual Memory Management

SGA_TARGET = 0
DB_CACHE_SIZE = 6G
SHARED_POOL_SIZE = 2G
LARGE_POOL_SIZE = 256M
LOG_BUFFER = 16M
PGA_AGGREGATE_TARGET = 3G

When to use: Rare, legacy, very specific tuning

Critical Rules (Very Important)

❌ Never mix AMM and ASMM

MEMORY_TARGET = 10G
SGA_TARGET = 8G   -- WRONG

✅ Valid configurations

ASMM (Recommended):

SGA_TARGET = 10G
SGA_MAX_SIZE = 12G
PGA_AGGREGATE_TARGET = 3G
MEMORY_TARGET = 0

AMM:

MEMORY_TARGET = 13G
MEMORY_MAX_TARGET = 16G

Parameter Change – Downtime Summary

Interview Questions (Memory)

Q1: Can I set both MEMORY_TARGET and SGA_TARGET?

Answer:
No. They are mutually exclusive. If MEMORY_TARGET is set, SGA_TARGET is ignored.

Q2: Which is better – AMM or ASMM?

Answer:
ASMM is recommended for 19c production systems.

Q3: Can I increase SGA_TARGET without downtime?

Answer:
Yes, up to SGA_MAX_SIZE. Beyond that, restart is required.

Q4: How to check memory usage?

SELECT * FROM v$sga;
SELECT * FROM v$pgastat;
SHOW PARAMETER sga
SHOW PARAMETER pga
SHOW PARAMETER memory

Q5: How to switch from AMM to ASMM?

ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=10G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size=12G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=3G SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

3. Process Architecture

A. User Process

Definition:
Started when a user connects to the database.

Key points:

• Started when user connects (SQL*Plus, application, JDBC)
• Runs on client machine, not on database server
• NOT part of Oracle instance
• Sends SQL requests to server process
• Receives results back from server process

B. Server Process

Definition:
Runs on the database server and handles requests from the user process.

Responsibilities:

• Parses and executes SQL
• Reads data blocks into buffer cache
• Returns results to user process
• Manages private memory (PGA)

Connection Modes

1. Dedicated Server (Default)

Characteristics:

• One server process per user connection
• Server process dedicated to a single user

Pros:

• Simple and predictable
• Better for long-running operations

Cons:

• More memory consumption (each process has its own PGA)

Best for:

• OLTP systems
• Batch jobs
• Less than 500 concurrent users

2. Shared Server (MTS – Multi-Threaded Server)

Characteristics:

• Multiple users share a pool of server processes
• Uses dispatcher processes to route requests

Pros:

• Supports more users with less memory usage

Cons:

• Not suitable for long-running queries
• Not suitable for batch jobs

Best for:

• Many concurrent short transactions (>1000 users)

Configuration:

ALTER SYSTEM SET shared_servers=10 SCOPE=BOTH;
ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=5)' SCOPE=BOTH;

Interview Questions

Q1: What’s the difference between user process and server process?

Answer:
User process runs on the client machine and sends SQL requests.
Server process runs on the database server, executes SQL, reads data into buffer cache, and returns results.
In dedicated server mode, it is 1:1. In shared server mode, multiple users share server processes.

Q2: When should you use shared server?

Answer:
When you have many (>1000) concurrent users with short transactions.
Each dedicated server process uses ~4–5MB memory.
With 5000 users, that is ~25GB memory just for server processes.
Shared server significantly reduces this memory usage.

Q3: Can I mix dedicated and shared server?

Answer:
Yes. You can configure shared servers, and users can still force a dedicated connection.

-- Force dedicated server in tnsnames.ora
(SERVER=DEDICATED)

C. Background Processes

Background processes are automatically started when the instance starts.
They perform housekeeping and internal maintenance tasks.

Mandatory Background Processes

(Instance will not start without these)

1. SMON (System Monitor)

Responsibilities

• Instance recovery at startup after a crash
  • Rolls forward redo logs (cache recovery)
  • Opens database for use
  • Rollback is done later by SMON in background
• Cleans up temporary segments in TEMP tablespace
• Coalesces free space in dictionary-managed tablespaces (old method)
• Recovers dead transactions skipped during instance recovery

Recovery Process

Crash → Startup → SMON reads redo logs
→ Rolls forward (apply changes)
→ Database opens
→ SMON rolls back uncommitted transactions in background

If SMON dies:
Instance crashes immediately.

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'SMON';
SELECT * FROM v$recovery_progress;  -- During recovery

2. PMON (Process Monitor)

Responsibilities

• Monitors user server processes
• Cleans up after failed user processes:
  • Releases locks held by dead sessions
  • Releases database resources
  • Rolls back uncommitted transactions
  • Frees PGA memory
• Registers instance with listener (dynamic registration)
• Restarts dead dispatcher processes (shared server mode)

Example Scenario

User application crashes → Connection lost
→ PMON detects failure
→ Rolls back user's transaction
→ Releases locks
→ Other users can proceed

If PMON dies:
Instance crashes.

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'PMON';

-- Check dead processes cleaned by PMON
SELECT COUNT(*) FROM v$process WHERE paddr = '00';

3. DBWn (Database Writer) – Multiple Processes

Naming

DBW0, DBW1, …, DBW9, DBWa, DBWb, …, DBWj
(Up to 100 DBWn processes)

Responsibilities

• Writes dirty buffers from the database buffer cache to datafiles
• Writes in batch for efficiency (not one block at a time)

When DBWn Writes (Triggers)

1. Checkpoint occurs (CKPT signals DBWn)
2. No free buffers available (server process needs space)
3. Timeout (every 3 seconds)
4. Tablespace goes offline or becomes read-only
5. Table is dropped or truncated
6. Before redo log switch (to maintain write-ahead logging)

Write-Ahead Logging (WAL) Protocol

• Redo must be written to disk before data blocks
• LGWR always writes before DBWn
• Ensures database recoverability

Performance Tuning

• Increase number of DBWn processes on busy systems:

ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE;
-- Requires restart

• Increasing DBWn can help if you see “free buffer waits”

Monitoring

SELECT * FROM v$bgprocess WHERE name LIKE 'DBW%';

-- Check write activity
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%dbwr%' OR name LIKE '%buffer%';

Performance Issue

• If DBWn is slow, users experience “free buffer waits”
• This means clean buffers are not available in the buffer cache

4. LGWR (Log Writer)

Critical Process:
If LGWR cannot write, the database hangs.

Responsibilities

• Writes redo log buffer to online redo log files
• Writes sequentially (very fast)
• Ensures data durability

When LGWR Writes (Triggers)

1. User commits
   (Synchronous — COMMIT does not return until LGWR writes)
2. Redo log buffer is 1/3 full (proactive)
3. Before DBWn writes (Write-Ahead Logging protocol)
4. Every 3 seconds (timeout)
5. More than 1MB of redo is generated

Critical for Commits

COMMIT;
-- Waits here until LGWR writes redo to disk
-- This wait is called "log file sync"

Performance Issues

• Slow disk I/O for redo logs ⇒ users experience “log file sync” waits

Solutions:

• Put redo logs on the fastest storage (SSD, RAID 10)
• Never place redo logs on NFS or slow SAN
• Separate redo logs from datafiles (different disks)
• Use ASM or raw devices for redo logs

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'LGWR';

-- Check LGWR-related wait events
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE 'log file%';

If LGWR Cannot Write

• Archive destination full → Database hangs
• Redo log disk full → Database hangs
• Redo log corruption → Instance crashes

5. CKPT (Checkpoint Process)

Responsibilities

• Signals DBWn to write dirty buffers to disk
• Updates control file with checkpoint information
• Updates datafile headers with checkpoint SCN

What is a Checkpoint?

• Point in time when all dirty buffers are written to disk
• Database is in a consistent state
• Checkpoint SCN is recorded

Types of Checkpoints

1. Complete checkpoint
   • All dirty buffers written
   • Occurs during SHUTDOWN NORMAL / SHUTDOWN IMMEDIATE
2. Incremental checkpoint
   • Continuous process
   • Controlled by DBWn during normal operation
3. Partial checkpoint
   • Only specific tablespace or datafile
   • Example: ALTER TABLESPACE OFFLINE

When Checkpoints Occur

• Redo log switch
• Forced checkpoint:

ALTER SYSTEM CHECKPOINT;

• Database shutdown (except ABORT)
• Tablespace offline or read-only

Checkpoint Tuning

-- Control checkpoint frequency (in seconds)
FAST_START_MTTR_TARGET = 300  -- Target 5-minute recovery time
-- Oracle auto-tunes checkpoint frequency to meet this target

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'CKPT';
SELECT checkpoint_change# FROM v$database;
SELECT checkpoint_change#, checkpoint_time FROM v$datafile_header;

Interview Questions (Checkpoint, DBWn, LGWR, SMON, PMON)

Q1: What happens during a checkpoint?

Answer:
CKPT signals DBWn to write all dirty buffers to datafiles.
Once complete, CKPT updates the control file and datafile headers with the checkpoint SCN (System Change Number).
This ensures database consistency and reduces recovery time.

Q2: What is the sequence: LGWR or DBWn writes first?

Answer:
LGWR always writes first (Write-Ahead Logging).
Redo must be written to disk before the data block.
This ensures that if a crash happens after DBWn writes, the database can still recover using redo logs.

Q3: User says “commit is slow”. What do you check?

Answer:
Check the “log file sync” wait event in V$SESSION_EVENT or AWR.
This usually indicates LGWR is slow writing redo logs.

Investigate:

• Redo log disk I/O performance
• Network latency (if redo is on remote storage)
• Whether archive destination is full
• V$SYSTEM_EVENT for log file–related waits

Q4: Difference between SMON and PMON?

Answer:

• SMON: System-level recovery
  • Cleans up instance after crash
  • Coalesces free space
  • Cleans temporary segments
• PMON: Process-level cleanup
  • Cleans up after failed user sessions
  • Releases locks
  • Registers instance with listener

Q5: Can I increase the number of DBWn processes without downtime?

Answer:
No. DB_WRITER_PROCESSES is a static parameter.
It must be set in the SPFILE and requires an instance restart.

Q6: What if all DBWn processes are busy and buffer cache has no free space?

Answer:
Server processes wait on the “free buffer waits” event.
They cannot read new blocks into the buffer cache, causing severe performance impact.

Solution:

• Increase DBWn processes
• Improve disk write performance

Q7: Why does LGWR write every 3 seconds even with no commits?

Answer:
To prevent the redo log buffer from filling up and to ensure redo is persisted quickly.
Even without commits, DML operations generate redo that must be protected.

Optional but Important Processes

6. ARCn (Archiver)

(Multiple processes: ARC0–ARC9, up to ARCa–ARCz)

Purpose

• Copies filled online redo logs to the archive location

Active only if:
Database is in ARCHIVELOG mode

Why Is ARCn Needed?

• Point-in-time recovery (restore backup + apply archived logs)
• Data Guard standby synchronization
• Compliance and audit requirements

How ARCn Works

Online redo log fills
→ LGWR switches to next redo log
→ ARCn copies filled log to archive destination
→ Old redo log becomes available for reuse

Configuration

Enable ARCHIVELOG Mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Set Archive Destinations

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/archive1/';
ALTER SYSTEM SET log_archive_dest_2='LOCATION=/archive2/';  -- Multiplex
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc';

Configure Multiple ARCn Processes

ALTER SYSTEM SET log_archive_max_processes=4;

Critical Issue

Archive destination full
→ ARCn cannot write
→ LGWR cannot switch logs
→ Database HANGS
→ No transactions possible

DBA Daily Checks

-- Check archive destination status
SELECT dest_id, status, destination, error
FROM v$archive_dest;

-- Check archive gap (Data Guard)
SELECT * FROM v$archive_gap;

-- Check failed archives
SELECT * FROM v$archive_dest_failure;

Monitoring

SELECT * FROM v$bgprocess WHERE name LIKE 'ARC%';
SELECT COUNT(*) FROM v$archived_log WHERE completion_time > SYSDATE - 1;

Parameter – Downtime Requirement

• LOG_ARCHIVE_MAX_PROCESSES
  • Dynamic parameter
  • No restart required

7. RECO (Recoverer)

Purpose

• Resolves failures in distributed transactions

Active only if:
Database links are used for distributed transactions

Scenario

-- Transaction spans two databases via database link
BEGIN
  UPDATE local_table SET ...;
  UPDATE remote_table@dblink SET ...;
  COMMIT;  -- Two-phase commit
END;

-- If the remote database crashes after PREPARE but before COMMIT,
-- RECO automatically resolves the transaction when the remote database comes back up

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'RECO';

-- In-doubt distributed transactions
SELECT * FROM dba_2pc_pending;

8. MMON (Manageability Monitor)

Purpose

• Performance monitoring and statistics collection

Responsibilities

• Collects AWR (Automatic Workload Repository) snapshots
  (Default interval: every 60 minutes)
• Writes statistics to SYSAUX tablespace
• Issues alerts for threshold violations
• Maintains optimizer statistics
• Generates automatic space management alerts

Critical for DBAs

• Without MMON, no AWR reports (no performance analysis)
• dbms_workload_repository packages rely on MMON

Configuration

Set AWR Snapshot Interval (Minutes)

EXEC dbms_workload_repository.modify_snapshot_settings(interval => 30);

Set AWR Retention (Days)

EXEC dbms_workload_repository.modify_snapshot_settings(retention => 14);

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'MMON';

SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

9. MMNL (Manageability Monitor Lite)

Purpose

• Writes Active Session History (ASH) data to memory

How It Differs from MMON

• MMNL:
  • Writes ASH samples every second (high frequency)
• MMON:
  • Writes AWR snapshots every hour (low frequency)

ASH Data Storage

• Last 1 hour of ASH data is kept in memory
  • View: V$ACTIVE_SESSION_HISTORY
• Older ASH data is written to disk by MMON
  • View: DBA_HIST_ACTIVE_SESS_HISTORY

Why MMNL Is Important

• Enables real-time performance troubleshooting
• Captures what sessions are doing every second
• Essential for diagnosing short-lived / transient performance issues

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'MMNL';

Query ASH Data

Current ASH data (last 1 hour, in memory):

SELECT *
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24;

Historical ASH data (stored on disk):

SELECT *
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 1;

10. MMAN (Memory Manager)

Purpose

• Handles automatic memory management

Responsibilities

• Dynamically resizes SGA components
  (when using ASMM or AMM)
• Coordinates memory transfers between SGA components
• Ensures memory allocation stays within:
  • SGA_TARGET (ASMM)
  • MEMORY_TARGET (AMM)

Example

11:00 AM – Heavy queries
→ MMAN increases buffer cache
→ Shrinks shared pool

02:00 PM – Heavy parsing
→ MMAN increases shared pool
→ Shrinks buffer cache

Active Only If

• ASMM is used (SGA_TARGET set), or
• AMM is used (MEMORY_TARGET set)

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'MMAN';

View Memory Resize Operations

SELECT component,
       current_size/1024/1024 MB,
       oper_type,
       oper_mode,
       start_time,
       end_time
FROM v$sga_resize_ops
ORDER BY start_time DESC;

11. LREG (Listener Registration) – 19c

Purpose

• Registers the database instance with the listener dynamically

In Older Versions

• This responsibility was handled by PMON

Why a Separate Process in 19c?

• Offloads work from PMON
• Improves performance for connection handling
• Handles service registration more efficiently

How LREG Works

Instance starts
→ LREG contacts listener
→ Registers services and load information
→ Listener knows which instances are available

Configuration

Local Listener

ALTER SYSTEM SET local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))';

Remote Listener (SCAN in RAC)

ALTER SYSTEM SET remote_listener='scan-name:1521';

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'LREG';

Check Registered Services

SELECT name, pdb, network_name
FROM v$services;

12. DBRM (Database Resource Manager)

Purpose

• Enforces resource plans
  (CPU, I/O limits per user or application)

Active only if:
Resource Manager is enabled

Use Case

Limit reporting users to 30% CPU.

BEGIN
  dbms_resource_manager.create_plan(
    plan    => 'DAYTIME_PLAN',
    comment => 'Daytime resource allocation'
  );

  dbms_resource_manager.create_plan_directive(
    plan              => 'DAYTIME_PLAN',
    group_or_subplan  => 'REPORTING_GROUP',
    cpu_p1            => 30  -- Max 30% CPU
  );
END;

Activate Resource Plan

ALTER SYSTEM SET resource_manager_plan='DAYTIME_PLAN';

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'DBRM';
SELECT * FROM v$rsrc_plan;
SELECT * FROM v$rsrc_consumer_group;

13. Jnnn (Job Queue Processes)

(J000, J001, …)

Purpose

• Executes scheduled jobs created using:
  • DBMS_SCHEDULER
  • DBMS_JOB

Configuration

Set Maximum Job Processes

ALTER SYSTEM SET job_queue_processes=10;

Monitoring

SELECT * FROM v$bgprocess WHERE name LIKE 'J0%';

SELECT job_name, state, running_instance
FROM dba_scheduler_running_jobs;

14. Pnnn (Parallel Execution Servers)

(P000, P001, …)

Purpose

• Executes parallel queries and parallel DML operations

Active when:
Queries use parallel execution

Example

-- Parallel query
SELECT /*+ PARALLEL(8) */ *
FROM large_table;
-- Uses 8 parallel processes

Configuration

ALTER SYSTEM SET parallel_min_servers=5;   -- Keep 5 idle
ALTER SYSTEM SET parallel_max_servers=20;  -- Maximum allowed

Monitoring

SELECT * FROM v$bgprocess WHERE name LIKE 'P0%';
SELECT * FROM v$px_process;
SELECT degree FROM v$sql WHERE sql_id = 'xxxxx';

15. Snnn (Shared Server Processes)

(S000, S001, …)

Active only if:
Shared server mode is enabled

16. Dnnn (Dispatcher Processes)

(D000, D001, …)

Active only if:
Shared server mode is enabled

Purpose

• Routes user requests to available shared server processes

17. Wnnn (Space Management Coordinator Workers)

Purpose

• Performs automatic space management tasks

Tasks

• Segment shrink operations
• Space reclamation
• Statistics gathering for segments

18. VKTM (Virtual Keeper of Time)

Purpose

• Provides high-resolution time service for the instance

Why a Separate Process?

• System calls for time are expensive
• VKTM makes one call and shares time with all processes
• Improves overall performance

Monitoring

SELECT * FROM v$bgprocess WHERE name = 'VKTM';

19. DIAG (Diagnosability Daemon)

Purpose

• Monitors instance health and captures diagnostic data

Responsibilities

• Detects hangs and deadlocks
• Dumps diagnostic information
• Coordinates ADR (Automatic Diagnostic Repository)

20. GEN0 (General Task Execution Process)

Purpose

• Performs general maintenance tasks

Introduced in:
Oracle 12c and later

Process Architecture – Summary Table

Interview Questions (Process Architecture)

Q1: Which background processes are mandatory?

Answer:
SMON, PMON, DBWn, LGWR, CKPT.
Without any of these processes, the instance cannot start or will crash immediately.

Q2: My database is hanging. No users can connect or run queries. What should I check?

Answer:

1. Check if archive destination is full (ARCHIVELOG mode):

df -h /archive

2. Check the alert log for errors:

tail -100 alert_<SID>.log

3. Check if LGWR is waiting:

SELECT event
FROM v$session
WHERE program LIKE '%LGWR%';

4. Check for “log file sync” waits:

SELECT event, total_waits
FROM v$system_event
WHERE event LIKE 'log%';

Q3: What is the relationship between CKPT and DBWn?

Answer:
CKPT is the coordinator and DBWn is the worker.
CKPT signals DBWn to write dirty buffers.
After DBWn completes, CKPT updates the control file and datafile headers with the checkpoint SCN.

Q4: Can I manually trigger a checkpoint?

Answer:
Yes.

ALTER SYSTEM CHECKPOINT;  -- Forces a complete checkpoint

Use this before taking a tablespace offline or before a backup.

Q5: I see 8 DBWn processes but set db_writer_processes=4. Why?

Answer:
Oracle may start more DBWn processes than configured if it detects heavy write activity.
This is part of automatic tuning.

SELECT * FROM v$bgprocess WHERE name LIKE 'DBW%';

Q6: What is the sequence of events during COMMIT?

Answer:

User issues COMMIT
→ Server process writes redo to redo log buffer
→ Server process signals LGWR
→ LGWR writes redo to disk
→ LGWR signals server process
→ COMMIT returns to user
→ SCN incremented
→ Locks released

DBWn writes dirty buffers later (asynchronously).

Q7: How do you check if MMON is running and collecting AWR?

Answer:

Check MMON process:

SELECT * FROM v$bgprocess WHERE name = 'MMON';

Check recent AWR snapshots:

SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1
ORDER BY snap_id DESC;

If no recent snapshots exist:

SELECT * FROM dba_hist_wr_control;

Q8: Difference between MMON and MMNL?

Answer:

• MMON:
  • Collects AWR snapshots every 60 minutes (configurable)
  • Writes data to SYSAUX tablespace
• MMNL:
  • Samples active sessions every second
  • Keeps ASH data in memory (V$ACTIVE_SESSION_HISTORY)

Q9: Can I disable ARCn processes?

Answer:
You cannot disable the ARCn processes directly, but you can disable ARCHIVELOG mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

ARCn processes remain present but stay idle.
This is not recommended for production databases.

Q10: I increased job_queue_processes but still see only 5 job processes. Why?

Answer:
Oracle starts job processes on demand, not all at once.
As scheduled jobs execute, Oracle spawns additional Jnnn processes up to the configured limit.
Idle job processes are not created unnecessarily.

Q11: What is PMON’s role in RAC?

Answer:
In RAC, when an instance crashes, PMON on surviving instances:

• Recovers locks held by the failed instance
• Performs instance recovery
• Re-masters resources

Q12: How to identify which process is consuming the most CPU?

Answer:

On the database side:

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       p.spid,
       s.status
FROM v$session s,
     v$process p
WHERE s.paddr = p.addr
ORDER BY p.spid;

On the OS side (Linux):

top -p <SPID>   -- Use SPID obtained from the query above

4. Storage Architecture

A. Physical Storage Structure

1. Datafiles (.dbf)

Purpose

• Store actual database objects:
  • Tables
  • Indexes
  • LOBs

Characteristics

• Binary format
• Belong to exactly ONE tablespace
• Can be resized (manual or auto-extend)
• Can be moved or renamed while the database is running (with restrictions)

Best Practices

• Use ASM or LVM for easier management
• Separate datafiles across different disks for better I/O distribution
• Size datafiles appropriately to avoid frequent auto-extends
• Monitor space usage using:
  • DBA_DATA_FILES
  • V$DATAFILE

Operations

Add Datafile to Tablespace

ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/ORCL/users02.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

Resize Datafile

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 5G;

Enable Autoextend

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Take Datafile Offline

(Database must be in ARCHIVELOG mode)

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' OFFLINE;

Bring Datafile Online

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' ONLINE;

Move / Rename Datafile

ALTER TABLESPACE users OFFLINE;

-- At OS level
mv users01.dbf /new_location/users01.dbf

ALTER TABLESPACE users RENAME DATAFILE
'/old_location/users01.dbf' TO '/new_location/users01.dbf';

ALTER TABLESPACE users ONLINE;

Monitoring

Check Datafile Size and Usage

SELECT file_name,
       tablespace_name,
       bytes/1024/1024 MB,
       autoextensible,
       maxbytes/1024/1024 MAX_MB
FROM dba_data_files
ORDER BY tablespace_name;

Check Datafile I/O

SELECT name,
       phyrds,
       phywrts,
       readtim,
       writetim
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#;

2. Control Files

Purpose

• Database metadata repository

Contains

• Database name and creation timestamp
• Datafile names and locations
• Redo log file names and locations
• Tablespace information
• Current log sequence number
• Checkpoint information (SCN)
• RMAN backup metadata
• Archive log history

Critical Importance

• Database cannot open without a valid control file
• Loss of all control files = major disaster

Best Practices

• Always multiplex control files
  • Minimum 3 copies on different disks/controllers
• Never place all control files on the same disk
• Always include control files in backup strategy

Multiplexing Control Files

Check Current Control Files

SELECT name FROM v$controlfile;

Add Control File Locations

(Requires instance restart)

ALTER SYSTEM SET control_files=
'/u01/oradata/ORCL/control01.ctl',
'/u02/oradata/ORCL/control02.ctl',
'/u03/oradata/ORCL/control03.ctl'
SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

-- At OS level
cp control01.ctl control03.ctl

STARTUP;

Recovery from Control File Loss

If One Copy Is Still Available

SHUTDOWN ABORT;

-- At OS level
cp good_control.ctl missing_control.ctl

STARTUP;

If All Control Files Are Lost

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Monitoring

SELECT * FROM v$controlfile;

SELECT name, value
FROM v$parameter
WHERE name = 'control_files';

Control File Record Sections

SELECT type,
       record_size,
       records_total,
       records_used
FROM v$controlfile_record_section;

3. Online Redo Log Files

Purpose

• Records all changes made to the database:
  • DML
  • DDL
  • COMMIT operations

Critical For

• Database recovery after crash
• Data Guard replication
• GoldenGate replication
• Point-in-time recovery

Structure

• Redo Log Groups
  • Logical unit
  • Minimum 2 groups required
• Redo Log Members
  • Physical files within a group
  • Should always be multiplexed
• Circular Usage
  • LGWR continuously switches between groups

Example Structure

Group 1

• /u01/redo/redo01a.log (Member 1)
• /u02/redo/redo01b.log (Member 2 – mirror)

Group 2

• /u01/redo/redo02a.log (Member 1)
• /u02/redo/redo02b.log (Member 2 – mirror)

Group 3

• /u01/redo/redo03a.log (Member 1)
• /u02/redo/redo03b.log (Member 2 – mirror)

How It Works

LGWR writes to Group 1
→ Group 1 fills
→ Log switch
→ LGWR switches to Group 2
→ ARCn archives Group 1
→ Group 2 fills
→ Log switch
→ LGWR switches to Group 3
→ Group 3 fills
→ LGWR switches back to Group 1 (overwritten)

Sizing Guidelines

• Ideal log switch every 15–30 minutes
• Too frequent:
  • Performance impact
  • Checkpoint storms
• Too infrequent:
  • Longer crash recovery time

Best Practices

• Minimum 3 redo log groups
• Minimum 2 members per group (multiplexing)
• Place members on different disks/controllers
• Use fast storage (SSD recommended)
• Size redo logs based on redo generation rate

Operations

Check Redo Log Configuration

SELECT group#, thread#, sequence#, bytes/1024/1024 MB,
       members, status, archived
FROM v$log;

SELECT group#, member, status
FROM v$logfile;

Add Redo Log Group

ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/redo/redo04a.log', '/u02/redo/redo04b.log')
SIZE 512M;

Add Member to Existing Group

ALTER DATABASE ADD LOGFILE MEMBER
'/u03/redo/redo01c.log' TO GROUP 1;

Drop Redo Log Group

(Must be inactive and archived)

ALTER DATABASE DROP LOGFILE GROUP 4;

Drop Redo Log Member

(At least one member per group must remain)

ALTER DATABASE DROP LOGFILE MEMBER '/u03/redo/redo01c.log';

Resize Redo Logs

(Redo logs cannot be resized directly — must be recreated)

1. Add new groups with larger size

ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/redo/redo04a.log', '/u02/redo/redo04b.log') SIZE 1G;

2. Force log switches

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

3. Drop old smaller groups (after archive)

ALTER DATABASE DROP LOGFILE GROUP 1;

4. Recreate dropped group with new size

ALTER DATABASE ADD LOGFILE GROUP 1
('/u01/redo/redo01a.log', '/u02/redo/redo01b.log') SIZE 1G;

Force Log Switch

ALTER SYSTEM SWITCH LOGFILE;

Clear Corrupted Redo Log

⚠️ Use with extreme caution

ALTER DATABASE CLEAR LOGFILE GROUP 2;

-- If unarchived
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Monitoring

Log Switches per Hour

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Redo Generation Rate (MB)

SELECT inst_id,
       ROUND(SUM(value)/1024/1024, 2) AS redo_mb
FROM gv$sysstat
WHERE name = 'redo size'
GROUP BY inst_id;

Current Redo Log Status

SELECT group#, status, archived
FROM v$log;

Redo-Related Wait Events

SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE '%log%'
ORDER BY time_waited DESC;

Common Issues

• “Cannot allocate new log”
  • All groups active
  • Solution: Increase redo log size or add more groups
• “Checkpoint not complete”
  • Log switches too fast
  • DBWn cannot keep up
  • Solution: Increase redo log size
• “Log file sync” waits
  • LGWR is slow
  • Check redo log disk I/O

4. Archive Redo Log Files

Purpose

• Copies of online redo logs used for recovery

Active only when:
Database is in ARCHIVELOG mode

Why Use ARCHIVELOG Mode

• Point-in-time recovery
  (restore backup + apply archived redo logs)
• Online backups (hot backups while database is running)
• Data Guard standby synchronization
• Zero data loss (with proper backup strategy)

Disadvantages

• Requires additional disk space
• Database can hang if archive destination becomes full
• Slightly more overhead compared to NOARCHIVELOG mode

Configuration

Check Current Log Mode

SELECT log_mode FROM v$database;

Enable ARCHIVELOG Mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Configure Archive Destinations

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/archive1/ MANDATORY' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='LOCATION=/archive2/ OPTIONAL'  SCOPE=BOTH;

Archive Log Format

(Must include %t thread, %s sequence, %r resetlogs)

ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=SPFILE;

Configure Multiple Archiver Processes

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH;

Disable ARCHIVELOG Mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

Backup and Cleanup

Backup Archive Logs Using RMAN

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

• Backs up archived redo logs
• Deletes them after successful backup

Delete Archives Older Than 7 Days

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

Manual Deletion (Warning)

• Never delete archived redo logs manually
• Always confirm they are backed up using RMAN

Monitoring

Check Archive Destinations

SELECT dest_id, status, destination, error
FROM v$archive_dest;

Check Archive Log Generation

SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS log_date,
       COUNT(*)
FROM v$archived_log
WHERE first_time > SYSDATE - 7
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')
ORDER BY 1;

Check Archive Destination Space

SELECT * FROM v$recovery_file_dest;

Check Archive Gaps (Data Guard)

SELECT * FROM v$archive_gap;

5. Parameter File

There are two types of parameter files used by Oracle:

a) PFILE (Parameter File) – Text

Characteristics

• Location: $ORACLE_HOME/dbs/init<SID>.ora (Linux/Unix)
• Format: Plain text, human-readable
• Modifications: Edited manually using a text editor
• Changes: Not persistent unless saved in the file
• Use case:
  • Initial database creation
  • Startup troubleshooting

Example PFILE

db_name=ORCL
sga_target=10G
pga_aggregate_target=3G
control_files=/u01/control01.ctl,/u02/control02.ctl

b) SPFILE (Server Parameter File) – Binary ✅ Recommended

Characteristics

• Location: $ORACLE_HOME/dbs/spfile<SID>.ora (Linux/Unix)
• Format: Binary (not directly editable)
• Modifications: Performed using ALTER SYSTEM
• Changes: Can be persistent or temporary
• Use case:
  • Production databases (standard practice)

Scope Options

Change Only in Memory

(Lost after restart)

ALTER SYSTEM SET sga_target=12G SCOPE=MEMORY;

Change Only in SPFILE

(Takes effect after restart)

ALTER SYSTEM SET sga_target=12G SCOPE=SPFILE;

Change in Both Memory and SPFILE

(Immediate + persistent)

ALTER SYSTEM SET sga_target=12G SCOPE=BOTH;

Operations

Check Which Parameter File Is Being Used

SELECT value FROM v$parameter WHERE name = 'spfile';

• If the value is NULL, the database is using a PFILE

Create SPFILE from PFILE

CREATE SPFILE FROM PFILE;
CREATE SPFILE='/u01/spfileORCL.ora' FROM PFILE='/tmp/initORCL.ora';

Create PFILE from SPFILE

(For editing or backup)

CREATE PFILE FROM SPFILE;
CREATE PFILE='/tmp/initORCL.ora' FROM SPFILE;

Start Database with a Specific Parameter File

STARTUP PFILE='/tmp/initORCL.ora';

Best Practices

• Always use SPFILE in production
• Keep a backup copy of PFILE:

CREATE PFILE='/backup/init.ora' FROM SPFILE;

• Document all parameter changes

6. Password File

Purpose

• Stores passwords for users with SYSDBA and SYSOPER privileges

Why Password File Is Needed

• Enables remote SYSDBA connections
• Allows database startup and shutdown remotely

Location

• $ORACLE_HOME/dbs/orapw<SID> (Linux/Unix)

Operations

Create Password File

(OS-level command)

orapwd file=$ORACLE_HOME/dbs/orapwORCL password=SysPassword123 entries=10

Grant SYSDBA Privilege

GRANT SYSDBA TO scott;

Check Users with SYSDBA Privilege

SELECT * FROM v$pwfile_users;

Remote SYSDBA Connection Using Password File

sqlplus sys/password@orcl as sysdba

7. Alert Log

Purpose

• Chronological log of database messages and errors

Contains

• Startup and shutdown events
• All errors (ORA- errors)
• Log switches
• Checkpoints
• Archive operations
• Internal errors
• Administrative operations

Location (Oracle 19c)

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/alert_<SID>.log

Also Available in XML Format

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/alert/log.xml

DBA Daily Tasks

Monitor Alert Log Continuously

tail -f $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Check for Errors Today

grep -i "ora-" alert_orcl.log | grep "$(date +%Y-%m-%d)"

Search for a Specific Error

grep -i "ora-00600" alert_orcl.log

Check Last 100 Lines

tail -100 alert_orcl.log

Query Alert Log from SQL

Query Recent Alert Log Entries (11g+)

SELECT originating_timestamp,
       message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1
ORDER BY originating_timestamp;

Check for ORA Errors

SELECT message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-%'
AND originating_timestamp > SYSDATE - 1;

8. Trace Files

Purpose

• Provides detailed diagnostic information for troubleshooting

Types of Trace Files

• User trace files
  • Generated by user sessions
• Background process trace files
  • Generated by processes such as SMON, PMON, etc.

Location

• Same directory as the alert log
• Typically:

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

Generate Trace File

Enable Trace for Current Session

ALTER SESSION SET sql_trace=TRUE;
ALTER SESSION SET tracefile_identifier='MY_TRACE';

Enable Trace Using Events (10046)

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Find Your Trace File

Using Diagnostic Views

SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';

Manually Construct Trace File Name

SELECT value || '/' || instance_name || '_ora_' || spid || '_MY_TRACE.trc'
FROM v$parameter, v$instance, v$process
WHERE name = 'user_dump_dest'
AND addr = (
  SELECT paddr
  FROM v$session
  WHERE sid = (
    SELECT sid FROM v$mystat WHERE rownum = 1
  )
);

Disable Trace

ALTER SESSION SET sql_trace=FALSE;

Format Trace File (Make It Readable)

tkprof tracefile.trc output.txt

Interview Questions (Storage Architecture)

Q1: What’s the difference between a control file and a parameter file?

Answer:

• Control file:
  • Contains database structure metadata
  • Includes datafile locations, redo log information, and checkpoint SCN
  • Binary file
  • Updated automatically by Oracle
• Parameter file:
  • Contains instance configuration settings (memory, processes, etc.)
  • SPFILE is binary, PFILE is text
  • Updated and managed by the DBA

Q2: Database won’t open – ORA-00205: error in identifying control file. What should you do?

Answer:
This error indicates the control file is missing or corrupted.

Steps to resolve:

1. Check if the control file exists at the location specified in the control_files parameter
2. If one copy exists, copy it to the missing location and restart the database
3. If all copies are lost, restore from backup:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Q3: What is the minimum number of redo log groups?

Answer:

• Minimum 2 redo log groups are required
• Best practice is 3 or more groups to avoid “cannot allocate new log” errors
• Each group should have 2 or more members (multiplexed)

Q4: What’s the difference between online redo logs and archive logs?

Answer:

• Online redo logs:
  • Fixed number of groups
  • Reused in a circular manner by LGWR
  • Always exist
• Archive logs:
  • Copies of filled online redo logs
  • Generated only in ARCHIVELOG mode
  • Continuously grow and require cleanup

Q5: Log switches are happening every 5 minutes. Is this a problem?

Answer:
Yes. The ideal log switch interval is 15–30 minutes.

Too frequent log switches cause:

• Checkpoint storms (performance impact)
• Excessive archive log generation
• ARCn bottlenecks

Solution:
Increase redo log size.

Q6: How do you check if the database is in ARCHIVELOG mode?

Answer:

SELECT log_mode FROM v$database;

Or:

ARCHIVE LOG LIST;

Q7: Can I delete archive logs manually?

Answer:
NO. Never delete archive logs manually.
Always use RMAN, which ensures the logs are backed up first.

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

RMAN guarantees that archived logs are safely backed up before deletion.

Q8: What’s the difference between PFILE and SPFILE?

Answer:

• PFILE:
  • Text file
  • Edited manually
  • Changes are not persistent after restart
• SPFILE:
  • Binary file
  • Edited using ALTER SYSTEM
  • Changes can be persistent (SCOPE=SPFILE or SCOPE=BOTH)
  • Always recommended for production environments

Q9: Database is hung and alert log shows “checkpoint not complete”. What does it mean?

Answer:
Redo logs are switching too fast.
DBWn has not finished writing dirty buffers from the previous checkpoint before LGWR needs to reuse the redo log.

Solutions:

• Increase redo log size
• Add more redo log groups
• Tune DBWn (increase DBWn processes)
• Check for slow datafile I/O

Q10: How do you increase redo log size without downtime?

Answer:
You cannot resize existing redo logs.
However, you can add new larger groups and drop the old ones while the database remains online.

-- 1. Add new groups with larger size
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/redo/redo04.log') SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/redo/redo05.log') SIZE 1G;

-- 2. Force log switches to make old groups inactive
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- 3. Check status (wait until INACTIVE and ARCHIVED)
SELECT group#, status, archived FROM v$log;

-- 4. Drop old small groups
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;

-- 5. Recreate with new size
ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/redo/redo01.log') SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/redo/redo02.log') SIZE 1G;

The database stays online throughout this process.

Q11: What happens if I lose one member of a redo log group?

Answer:
Nothing happens immediately.
LGWR continues writing to the remaining member(s).

Actions to take:

1. Check the alert log for the error
2. Recreate the missing redo log member
3. Or drop the damaged member if other members exist

Q12: What happens if I lose all members of the current redo log group?

Answer:
The database crashes immediately.
If redo cannot be written, the instance fails.

Recovery steps:

STARTUP MOUNT;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
ALTER DATABASE OPEN;

⚠️ Data loss is possible for transactions in that redo log.
A full backup is required immediately after recovery.

Q13: Why should control files be multiplexed, but I see only one path sometimes?

Answer:
You are likely checking different views.

SELECT name FROM v$controlfile;

• Shows all multiplexed control file locations

SELECT value FROM v$parameter WHERE name = 'control_files';

• Shows the parameter value containing all configured paths

Q14: Can I start the database without SPFILE or PFILE?

Answer:
No. Oracle searches for parameter files in the following order:

1. spfile<SID>.ora (default location)
2. spfile.ora (default location)
3. init<SID>.ora (default location)

If none are found, Oracle returns:

ORA-01078: failure in processing system parameters

Q15: What’s the difference between alert log and trace files?

Answer:

• Alert log:
  • High-level events
  • Errors
  • Startup and shutdown messages
  • Administrative operations
• Trace files:
  • Detailed diagnostic information
  • Specific session or background process details
  • SQL execution and wait events

B. Logical Storage Structure

Hierarchy

Database → Tablespace → Segment → Extent → Data Block

DATABASE (ORCL)
    ├── SYSTEM Tablespace
    │       ├── Segment: SYS.TAB$
    │       │       ├── Extent 1 (128 blocks)
    │       │       └── Extent 2 (128 blocks)
    │       └── Segment: SYS.OBJ$
    ├── SYSAUX Tablespace
    ├── USERS Tablespace
    │       └── Segment: SCOTT.EMP (table)
    │               ├── Extent 1 (8 blocks)
    │               └── Extent 2 (8 blocks)
    └── TEMP Tablespace

1. Data Block

Definition

Smallest unit of I/O in Oracle.

Characteristics

• Set at database creation (DB_BLOCK_SIZE)
• Typical sizes: 2KB, 4KB, 8KB (most common), 16KB, 32KB
• Cannot be changed after database creation (must recreate database)
• OS block size should match or be smaller than DB block size

Block Structure

┌─────────────────────────┐
│ Block Header            │ ← Metadata (address, type, SCN)
├─────────────────────────┤
│ Table Directory         │ ← Tables in this block
├─────────────────────────┤
│ Row Directory           │ ← Row addresses
├─────────────────────────┤
│ Free Space              │ ← Available space
├─────────────────────────┤
│ Row Data                │ ← Actual data
└─────────────────────────┘

• Block overhead: ~100–200 bytes per block

Choosing Block Size

• 8KB (default): Good for most OLTP applications
• 16KB / 32KB: Better for data warehouse workloads (large rows, full table scans)
• 4KB: Suitable for very small rows and high concurrency environments

Check Block Size

SELECT value 
FROM v$parameter 
WHERE name = 'db_block_size';

SELECT tablespace_name, block_size 
FROM dba_tablespaces;

2. Extent

Definition

Logical unit of contiguous data blocks.

Characteristics

• Set of consecutive data blocks
• Initial extent allocated when object is created
• Additional extents allocated as the object grows
• Managed automatically by Oracle in modern versions

Extent Allocation

-- Example: Table creation with extent parameters
CREATE TABLE large_table (...)
TABLESPACE users
STORAGE (
  INITIAL 1M        -- Initial extent size
  NEXT 1M           -- Next extent size
  MINEXTENTS 1      -- Minimum extents at creation
  MAXEXTENTS 100    -- Maximum extents (UNLIMITED in modern Oracle)
  PCTINCREASE 0     -- Percentage increase for each extent
);

In Modern Oracle (ASSM – Automatic Segment Space Management)

• Oracle manages extents automatically
• No need to specify the STORAGE clause
• Uses bitmaps to track block usage within segments

Check Extents

-- Extents for a specific object
SELECT segment_name, extent_id, bytes/1024/1024 MB, blocks
FROM dba_extents
WHERE owner = 'SCOTT' 
  AND segment_name = 'EMP'
ORDER BY extent_id;

-- Count extents per object
SELECT segment_name, COUNT(*) extents
FROM dba_extents
WHERE owner = 'SCOTT'
GROUP BY segment_name;

3. Segment

Definition

A segment is a set of extents that stores data for a specific database object.

Types of Segments

a) Data Segment

• Stores table data
• One segment per table (non-partitioned tables)
• One segment per partition (partitioned tables)

b) Index Segment

• Stores index data
• One segment per index

c) Temporary Segment

• Created in the TEMP tablespace
• Used for:
  • Sorting
  • Hash joins
  • Global temporary tables
• Automatically dropped after the operation completes

d) Undo Segment

• Stores before-image of data (old values)
• Used for:
  • Transaction rollback
  • Read consistency
  • Flashback operations
• System-managed in the UNDO tablespace (modern Oracle)

e) LOB Segment

• Stores large objects such as CLOB and BLOB
• Stored separately from the table segment

Segment Space Management

1. Manual Segment Space Management (MSSM) — Old Method

CREATE TABLESPACE users
DATAFILE '/u01/users01.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT MANUAL;  -- Old way

Characteristics:

• Uses freelists to track free blocks
• Requires more DBA intervention
• Not recommended for modern databases

2. Automatic Segment Space Management (ASSM) — Modern (Default)

CREATE TABLESPACE users
DATAFILE '/u01/users01.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO;  -- Default in 10g+

Characteristics:

• Uses bitmaps to track block status
• Better concurrency
• Self-tuning
• Always use this in Oracle 19c

Check Segments

-- All segments for a user
SELECT segment_name, segment_type, tablespace_name, 
       bytes/1024/1024 MB, blocks, extents
FROM dba_segments
WHERE owner = 'SCOTT'
ORDER BY bytes DESC;

-- Largest segments in the database
SELECT owner, segment_name, segment_type, bytes/1024/1024/1024 GB
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Segment growth over time (AWR)
SELECT * 
FROM dba_hist_seg_stat 
WHERE obj# = ...;

4. Tablespace

Definition

A tablespace is a logical container for segments and maps to one or more physical datafiles.

Purpose

• Organize database objects logically
• Separate user data from system data
• Allocate different storage for different applications
• Simplify backup and recovery (backup by tablespace)
• Control I/O by placing tablespaces on different disks

Types of Tablespaces

a) SYSTEM Tablespace ⚠️ Critical

• Created automatically during database creation
• Contains data dictionary (SYS schema objects)
• Never store user data here
• Cannot be dropped or taken offline (except during recovery)
• Must always remain online

b) SYSAUX Tablespace ⚠️ Critical

• Auxiliary tablespace for SYSTEM
• Contains:
  • AWR data (performance statistics)
  • MMON / MMNL data
  • Automatic Maintenance tasks
  • Oracle Text, Spatial, Multimedia metadata
  • STATSPACK, DBMS_SCHEDULER metadata
• Can be taken offline temporarily (not recommended)

c) UNDO Tablespace ⚠️ Critical

• Stores undo segments (before-image data)
• Required for:
  • Transaction rollback
  • Read consistency (multi-version concurrency)
  • Flashback queries
  • Flashback database
• System-managed (do not manually create undo segments)
• Only one active UNDO tablespace at a time

d) TEMP Tablespace

• Stores temporary segments used for:
  • Sorting (ORDER BY, GROUP BY, DISTINCT)
  • Hash joins
  • Index creation
  • Temporary tables
• Contents are automatically dropped after operations
• Never backed up (recreated if lost)
• Supports Temporary Tablespace Groups

e) User Tablespaces

• Store application data (tables, indexes)
• Examples:
  • USERS
  • DATA
  • INDEX
  • LOB tablespaces
• Multiple tablespaces can be created for better organization

Tablespace States

1. ONLINE

• Available for use
• Default state

ALTER TABLESPACE users ONLINE;

2. OFFLINE

• Not available for use, but metadata remains intact

Types:

• NORMAL – Clean offline (checkpoint performed)
• TEMPORARY – Quick offline (no checkpoint, requires recovery)
• IMMEDIATE – Forced offline (no checkpoint, requires recovery)

ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users OFFLINE TEMPORARY;
ALTER TABLESPACE users OFFLINE IMMEDIATE;

3. READ ONLY

• Data can be queried but not modified
• Useful for static or archive data
• Can be skipped during backups if unchanged for long periods

ALTER TABLESPACE archive_data READ ONLY;
ALTER TABLESPACE archive_data READ WRITE;

Creating Tablespaces

Basic Tablespace

CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

Tablespace with Specific Features

CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL          -- Default
SEGMENT SPACE MANAGEMENT AUTO    -- ASSM (Default)
LOGGING;                         -- Default

TEMP Tablespace

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 2G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

UNDO Tablespace

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oradata/ORCL/undotbs02.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

Bigfile Tablespace

CREATE BIGFILE TABLESPACE big_data
DATAFILE '/u01/oradata/ORCL/big_data01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 32T; -- Can go up to 32TB

Bigfile vs Smallfile Tablespaces

Tablespace Operations

Add Datafile to Tablespace

ALTER TABLESPACE users 
ADD DATAFILE '/u01/oradata/ORCL/users02.dbf' SIZE 1G;

Resize Datafile

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 5G;

Drop Tablespace (Including Datafiles)

DROP TABLESPACE old_data INCLUDING CONTENTS AND DATAFILES;

Rename Tablespace (12c+)

ALTER TABLESPACE old_name RENAME TO new_name;

Set Default Tablespace for User

ALTER USER scott DEFAULT TABLESPACE app_data;

Set Default Temporary Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Coalesce Free Space

(For dictionary-managed tablespaces only; not needed for locally-managed)

ALTER TABLESPACE users COALESCE;

Monitoring Tablespaces

Tablespace Size and Max Capacity

SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024/1024, 2) total_gb,
       ROUND(SUM(CASE WHEN autoextensible = 'YES' 
                     THEN maxbytes 
                     ELSE bytes END)/1024/1024/1024, 2) max_gb
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) total_gb,
       ROUND(SUM(CASE WHEN autoextensible = 'YES' 
                     THEN maxbytes 
                     ELSE bytes END)/1024/1024/1024, 2) max_gb
FROM dba_temp_files
GROUP BY tablespace_name;

Tablespace Free Space

SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024, 2) free_mb
FROM dba_free_space
GROUP BY tablespace_name;

Tablespace Usage Percentage

SELECT a.tablespace_name,
       ROUND(((a.total - NVL(b.free, 0)) / a.total) * 100, 2) used_pct
FROM (SELECT tablespace_name, SUM(bytes) total
      FROM dba_data_files 
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) free
      FROM dba_free_space 
      GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

Check Tablespace Attributes

SELECT tablespace_name, block_size, status, contents,
       logging, extent_management, allocation_type,
       segment_space_management, bigfile
FROM dba_tablespaces;

TEMP Tablespace Usage

SELECT tablespace_name, 
       tablespace_size/1024/1024 size_mb,
       allocated_space/1024/1024 allocated_mb,
       free_space/1024/1024 free_mb
FROM dba_temp_free_space;

Identify Sessions Using TEMP

SELECT s.username, s.sid, s.serial#, s.sql_id,
       t.tablespace, t.blocks, 
       t.blocks * 8192 / 1024 / 1024 mb_used
FROM v$tempseg_usage t, v$session s
WHERE t.session_addr = s.saddr
ORDER BY t.blocks DESC;

INTERVIEW QUESTIONS

Q1: What’s the difference between extent and segment?

A:

• Extent: Contiguous set of data blocks (storage allocation unit)
• Segment: Set of extents that stores one database object (table, index)
  • One segment can consist of multiple extents

Q2: Can you change DB_BLOCK_SIZE after database creation?

A:
No. DB_BLOCK_SIZE is fixed at database creation and cannot be changed.
You must recreate the database to change it.

Note: You can create tablespaces with non-standard block sizes.

Q3: What’s ASSM vs MSSM?

A:

• MSSM (Manual Segment Space Management)
  • Uses freelists to track free blocks
  • Requires tuning (FREELISTS, FREELIST GROUPS)
  • Old method
• ASSM (Automatic Segment Space Management)
  • Uses bitmaps to track block usage
  • Self-tuning and better concurrency
  • Default from 10g onward
  • Always use ASSM in modern databases

Q4: Can SYSTEM tablespace be taken offline?

A:
No (except during incomplete recovery).
SYSTEM contains the data dictionary and must always remain online for the database to function.

Q5: What’s stored in SYSAUX tablespace?

A:

• AWR data
• MMON statistics
• Automatic Maintenance tasks
• Oracle Text / Spatial / Multimedia metadata
• STATSPACK data
• DBMS_SCHEDULER metadata

SYSAUX is auxiliary to SYSTEM.

Q6: Difference between UNDO and TEMP tablespace?

A:

• UNDO
  • Stores before-image data
  • Used for rollback, read consistency, flashback
  • Data persists until transaction commits or rolls back
• TEMP
  • Stores temporary segments for sorting and joins
  • Automatically dropped after the operation

Q7: Can you have multiple UNDO tablespaces?

A:
Yes. Multiple UNDO tablespaces can exist, but only one is active per instance.

ALTER SYSTEM SET undo_tablespace=undotbs2;

Useful during maintenance or in RAC (different instances use different UNDO tablespaces).

Q8: What happens if TEMP tablespace fills up?

A:
Queries fail with:

ORA-01652: unable to extend temp segment

Solutions:

• Add more tempfiles
• Resize existing tempfiles
• Identify and tune SQL using excessive TEMP
• Clean up temporary segments (usually automatic)

Q9: When to use READ ONLY tablespace?

A:
For static or archive data, such as:

• Historical data (closed fiscal years)
• Reference data

Benefits:

• Can skip in incremental backups
• Prevents accidental modifications
• Can be placed on read-only storage

Q10: What’s a Bigfile tablespace?

A:
A tablespace with one datafile that can grow up to 32TB (with 8KB blocks).

Comparison:

• Smallfile tablespace:
  • Up to 1022 datafiles
  • Each datafile up to 32GB
• Bigfile tablespace:
  • Single datafile
  • Simplifies management
  • Creates a single point of failure

Q11: Can you drop SYSTEM or SYSAUX tablespace?

A:
No. Both SYSTEM and SYSAUX tablespaces are mandatory.
They are created during database creation and must always exist.

Q12: How to find which tablespace is filling up?

A:

SELECT tablespace_name,
       ROUND(used_percent, 2) used_pct
FROM dba_tablespace_usage_metrics
WHERE used_percent > 80
ORDER BY used_percent DESC;

Q13: Difference between DROP TABLESPACE and DROP TABLESPACE INCLUDING CONTENTS?

A:

• DROP TABLESPACE
  • Fails if the tablespace contains any objects
  • Only drops an empty tablespace
• DROP TABLESPACE INCLUDING CONTENTS
  • Drops the tablespace and all objects inside it
• DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
  • Drops the tablespace
  • Deletes all objects
  • Deletes physical datafiles from the OS

Q14: What’s a Temporary Tablespace Group?

A:
A Temporary Tablespace Group is a group of TEMP tablespaces assigned to users.
Oracle automatically distributes TEMP usage across the group for better performance.

CREATE TEMPORARY TABLESPACE temp2 
TEMPFILE '...' SIZE 1G 
TABLESPACE GROUP temp_group;

ALTER USER scott TEMPORARY TABLESPACE temp_group;

Q15: How to move a table from one tablespace to another?

A:

Method 1: ALTER TABLE MOVE

ALTER TABLE scott.emp MOVE TABLESPACE new_tbs;

Notes:

• Locks the table
• Invalidates indexes

-- Rebuild indexes after move
ALTER INDEX scott.emp_pk REBUILD ONLINE;

Method 2: DBMS_REDEFINITION

• Online method
• No table lock
• Allows concurrent access
• More complex than ALTER TABLE MOVE

5. STARTUP / SHUTDOWN SEQUENCE

Database Startup Stages

SHUTDOWN → NOMOUNT → MOUNT → OPEN

Stage 1: NOMOUNT

Command

STARTUP NOMOUNT;

What happens

1. Reads parameter file (SPFILE or PFILE)
2. Allocates SGA memory
3. Starts background processes
   (SMON, PMON, DBWn, LGWR, CKPT, etc.)
4. Does NOT open control files or datafiles

Instance State

• Instance is started
• No database access

When to use

• Database creation CREATE DATABASE;
• Control file recreation CREATE CONTROLFILE;
• Certain recovery scenarios

Check status

SELECT status FROM v$instance;
-- Returns: STARTED

SELECT name FROM v$database;
-- Error: database not mounted

Stage 2: MOUNT

Command

STARTUP MOUNT;
-- OR (if already NOMOUNT)
ALTER DATABASE MOUNT;

What happens

1. Opens and reads control file(s)
2. Identifies datafile and redo log locations
3. Does NOT open datafiles or redo logs

Database State

• Database is mounted
• Not accessible to users

When to use

• Database recovery
• Enable/disable ARCHIVELOG mode
• Renaming datafiles
• Certain backup operations

Operations allowed in MOUNT stage

Enable ARCHIVELOG

ALTER DATABASE ARCHIVELOG;

Rename datafile

ALTER DATABASE RENAME FILE
'/old/path/users01.dbf'
TO
'/new/path/users01.dbf';

Recover database

RECOVER DATABASE;

Create standby control file

ALTER DATABASE CREATE STANDBY CONTROLFILE
AS '/backup/standby.ctl';

Check status

SELECT status FROM v$instance;
-- Returns: MOUNTED

SELECT name, open_mode FROM v$database;
-- name: ORCL
-- open_mode: MOUNTED

Stage 3: OPEN

Command

STARTUP;           -- Goes through all stages
STARTUP OPEN;

-- OR (if already MOUNTED)
ALTER DATABASE OPEN;

What happens

1. Opens all online datafiles
2. Opens redo log files
3. SMON performs instance recovery if required:
   • Roll forward (apply redo)
   • Roll back uncommitted transactions
4. Database becomes available for normal operations

Database State

• Database is open
• Users can connect and access data

Open Modes

a) READ WRITE (Default)

ALTER DATABASE OPEN;

• Full access
• DML and DDL allowed

b) READ ONLY

ALTER DATABASE OPEN READ ONLY;

• Only SELECT allowed
• No DML / DDL

Used for:

• Reporting databases
• Testing
• Safety checks before changes

c) UPGRADE / MIGRATE

STARTUP UPGRADE;

• Used during database upgrades
• Limited operations allowed

Check status

SELECT status FROM v$instance;
-- Returns: OPEN

SELECT name, open_mode FROM v$database;
-- name: ORCL
-- open_mode: READ WRITE

Database Shutdown Options

NORMAL → TRANSACTIONAL → IMMEDIATE → ABORT
(Cleanest)                         (Fastest, requires recovery)

1. SHUTDOWN NORMAL

(Cleanest, Slowest)

Command

SHUTDOWN NORMAL;

What happens

1. Waits for all connected users to disconnect
   (No new connections allowed)
2. Waits for all transactions to complete
3. Performs checkpoint
   (Writes all dirty buffers to disk)
4. Closes datafiles and redo logs
5. Dismounts database
6. Shuts down instance

Characteristics

• No data loss
• No instance recovery required on next startup
• Can take very long if users don’t disconnect
• Rarely used in production

Use when

• Planned shutdown with advance notice to all users

2. SHUTDOWN TRANSACTIONAL

Command

SHUTDOWN TRANSACTIONAL;

What happens

1. Prevents new transactions and connections
2. Waits for active transactions to complete
   (Commit or rollback)
3. Disconnects users after transactions finish
4. Performs checkpoint
5. Closes datafiles and redo logs
6. Shuts down instance

Characteristics

• No data loss
• No instance recovery required
• Faster than NORMAL
  (Does not wait for idle sessions)
• Allows users to finish current work

Use when

• Planned shutdown where users need time to complete transactions

3. SHUTDOWN IMMEDIATE

✅ Recommended for most cases

Command

SHUTDOWN IMMEDIATE;

What happens

1. Rolls back uncommitted transactions
2. Disconnects all users immediately
3. Performs checkpoint
4. Closes datafiles and redo logs
5. Shuts down instance

Characteristics

• No data loss
  (Uncommitted work is rolled back)
• No instance recovery required on next startup
• Fast (seconds to minutes)
• Most commonly used in production

Use when

• Standard planned shutdown

4. SHUTDOWN ABORT

⚠️ Emergency only

Command

SHUTDOWN ABORT;

What happens

1. Immediately terminates the instance
2. No checkpoint performed
3. No rollback of transactions
4. Disconnects users instantly
5. Equivalent to pulling the power plug

Characteristics

• Fastest (instant)
• Instance recovery required on next startup
• Dirty buffers are not written to disk
• Use only when:
  • Database is hung
  • Emergency situations
  • Other shutdown methods fail

Next startup

STARTUP;

• SMON automatically performs instance recovery
• Rolls forward using redo logs
• Rolls back uncommitted transactions

Use when

• Database is hung
• Emergency situations
• All other shutdown methods are not working

Shutdown Comparison Table

INTERVIEW QUESTIONS

Q1: What are the stages of database startup?

A:
Three stages:

1. NOMOUNT
   • Instance started
   • Memory allocated
   • Background processes started
2. MOUNT
   • Control file opened
   • Datafile and redo log locations identified
3. OPEN
   • Datafiles and redo logs opened
   • Database accessible to users

STARTUP;  -- Goes through all stages automatically

Q2: When would you use STARTUP NOMOUNT?

A:

• Creating a new database CREATE DATABASE;
• Recreating control file
• Certain RMAN restore operations

Q3: What’s the difference between MOUNT and OPEN?

A:

• MOUNT
  • Control file is read
  • Oracle knows file locations
  • Files are not opened
  • Database not accessible to users
• OPEN
  • Datafiles and redo logs opened
  • SMON performs recovery if needed
  • Users can connect

Q4: Can you rename a datafile when database is OPEN?

A:
No. Tablespace must be offline or database must be in MOUNT stage.

Method 1: Offline tablespace

ALTER TABLESPACE users OFFLINE;
-- Move file at OS level
ALTER TABLESPACE users
RENAME DATAFILE '/old/path' TO '/new/path';
ALTER TABLESPACE users ONLINE;

Method 2: MOUNT stage

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RENAME FILE '/old/path' TO '/new/path';
ALTER DATABASE OPEN;

Q5: What’s the difference between SHUTDOWN IMMEDIATE and SHUTDOWN ABORT?

A:

• IMMEDIATE
  • Rolls back uncommitted transactions
  • Performs checkpoint
  • Clean shutdown
  • No recovery needed
• ABORT
  • Instant termination
  • No checkpoint
  • No rollback
  • Instance recovery required on next startup

Q6: Which SHUTDOWN option is recommended for planned maintenance?

A:
SHUTDOWN IMMEDIATE

• Fast
• Clean (no recovery needed)
• Properly rolls back uncommitted work
• Used in ~99% of planned shutdowns

Q7: Database is hung and won’t respond. What do you do?

A:

-- Try IMMEDIATE first
SHUTDOWN IMMEDIATE;

-- If no response after 5 minutes
SHUTDOWN ABORT;

-- Then startup
STARTUP;

• SMON performs automatic instance recovery

Q8: After SHUTDOWN ABORT, do I lose data?

A:
No data loss for committed transactions.

• Redo logs contain committed changes
• SMON applies redo during recovery
• Only uncommitted transactions are lost (expected behavior)

Q9: What happens during instance recovery?

A:
SMON automatically performs:

1. Roll Forward (Cache Recovery)
   • Applies redo logs
   • Brings datafiles to a consistent state
2. Roll Back (Transaction Recovery)
   • Uses UNDO
   • Rolls back uncommitted transactions

No DBA intervention required.

Q10: Can you open database in READ ONLY mode for upgrades?

A:
No. For upgrades use:

STARTUP UPGRADE;

• Special mode for running upgrade scripts

Q11: How to check why database took long to startup?

A:
Check alert log for instance recovery details:

grep -i "recovery" alert_orcl.log

Look for:

• Recovery start/end timestamps
• Number of redo blocks applied
• Media recovery details

Q12: What’s the command to change from NOMOUNT to MOUNT to OPEN?

A:

Method 1: Step by step

STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

Method 2: Direct

STARTUP;  -- Goes through all stages automatically

6. INSTANCE RECOVERY

(Automatic Crash Recovery)

What is Instance Recovery?

Scenario:

• Database crashes due to:
  • Power failure
  • SHUTDOWN ABORT
  • Background / server process killed

At the moment of crash:

• Dirty buffers exist in buffer cache (not written to disk)
• Some committed transactions may still be in redo log buffer
• Online redo logs contain:
  • Committed changes
  • Uncommitted changes
• Datafiles are inconsistent

Recovery is needed to:

1. Bring database to a consistent state
2. Ensure all committed transactions are persisted
3. Roll back uncommitted transactions

Two Phases of Instance Recovery

STARTUP after crash
        ↓
   SMON starts recovery
        ↓
Phase 1: ROLL FORWARD (Cache Recovery)
        ↓
Phase 2: ROLL BACK (Transaction Recovery)
        ↓
     Database OPEN

Phase 1: ROLL FORWARD

(Cache Recovery)

Purpose

• Apply all changes from redo logs to datafiles

What happens

1. SMON reads online redo logs starting from the last checkpoint SCN
2. Applies all changes (committed and uncommitted)
3. Brings datafiles to the state they were in at crash time
4. Recreates the buffer cache state

Why apply uncommitted transactions?

• Oracle cannot determine committed vs uncommitted transactions without reading redo
• Applying everything first is faster
• Uncommitted transactions are handled in rollback phase

Timeline

Last Checkpoint → Crash
       ↓            ↓
     SCN 100      SCN 150
       |------------|
        Redo logs applied

Example

-- Before crash:
Transaction A:
UPDATE emp SET sal=5000 WHERE id=1;
COMMIT;                -- SCN 110

Transaction B:
UPDATE emp SET sal=6000 WHERE id=2;
-- NOT committed       -- SCN 120

-- Database crashes at SCN 150

Roll forward phase result:

• SMON applies both changes from redo logs
  • emp(id=1): sal=5000
  • emp(id=2): sal=6000

Phase 2: ROLL BACK

(Transaction Recovery)

Purpose

• Undo all uncommitted transactions

What happens

1. SMON identifies uncommitted transactions using UNDO segments
2. Rolls back these transactions using UNDO data
3. Database reaches a consistent state
4. Database becomes available for users

Rollback timing

Rollback can occur:

• Before database OPEN (faster startup)
• After database OPEN (background rollback by SMON)

19c Improvement

• Fast-Start Recovery limits recovery time
• Improves startup performance after crash

Example (continued)

After roll forward:

emp(id=1): sal = 5000 ✓
emp(id=2): sal = 6000 ✗ (uncommitted)

Roll back phase:

• SMON uses UNDO to reverse Transaction B

emp(id=2): sal = original_value

Final state:

emp(id=1): sal = 5000 ✓  (Committed)
emp(id=2): sal = original_value ✓  (Rolled back)

Monitoring Instance Recovery

During recovery

Check recovery progress

SELECT * FROM v$recovery_progress;

Monitor alert log

tail -f alert_orcl.log

Sample Alert Log Entries

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 2500 KB redo, 145 data blocks need recovery
Started recovery at
 Thread 1: logseq 52, block 2, scn 987654
Recovery of Online Redo Log: Thread 1 Group 2 Seq 52 Reading mem 0
Completed redo application of 1.23MB
Completed crash recovery at
 Thread 1: logseq 52, block 2150, scn 987799
145 data blocks read, 145 data blocks written, 2500 KB redo read

Tuning Recovery Time

Problem

• Long recovery time = long downtime after crash

Solution

• FAST_START_MTTR_TARGET parameter

Example

-- Target recovery time: 5 minutes (300 seconds)
ALTER SYSTEM SET fast_start_mttr_target=300 SCOPE=BOTH;

How it works

• Oracle automatically tunes checkpoint frequency
• More frequent checkpoints ⇒ less redo to apply ⇒ faster recovery
• Trade-off:
  • More checkpoints
  • Slightly higher overhead during normal operation

Default

• 0 (disabled)
• Oracle uses internal calculation

Recommended

• 60–300 seconds for production databases

Check estimated recovery time

SELECT estimated_mttr FROM v$instance_recovery;

Parameters Affecting Recovery

Modern Oracle (19c) Best Practice

• Set FAST_START_MTTR_TARGET
• Let Oracle automatically manage all other recovery-related parameters

INTERVIEW QUESTIONS

Q1: What is instance recovery?

A:
Automatic recovery performed by SMON when the database starts after a crash.

Two phases:

1. Roll Forward
   • Applies redo logs
   • Brings datafiles to crash-time state
2. Roll Back
   • Undoes uncommitted transactions using UNDO

• No DBA intervention required

Q2: What’s the difference between roll forward and roll back?

A:

• Roll Forward
  • Applies changes from redo logs
  • Includes committed and uncommitted transactions
  • Brings datafiles to a consistent crash state
• Roll Back
  • Undoes uncommitted transactions
  • Uses UNDO segments

Q3: Why does Oracle apply uncommitted transactions during roll forward?

A:
Redo logs do not distinguish between committed and uncommitted changes at the physical level.

• Faster to apply all redo
• Uncommitted transactions are later rolled back using UNDO

Q4: Can users access database during roll back phase?

A:
Yes, with fast-start recovery.

• Oracle opens the database after critical roll forward
• Roll back continues in the background
• Users can access data not involved in uncommitted transactions

Q5: How to reduce instance recovery time?

A:
Set FAST_START_MTTR_TARGET to the desired recovery time (seconds).

ALTER SYSTEM SET fast_start_mttr_target=300;  -- 5 minutes

• Oracle increases checkpoint frequency to meet this target

Q6: What if both roll forward and roll back fail?

A:
The database will not open. Check the alert log.

Possible causes:

• Corrupted redo logs → Media recovery required
• Corrupted UNDO segments → May need incomplete recovery
• Missing datafiles → Restore from backup

Q7: Does instance recovery cause data loss?

A:
No.

• All committed transactions are preserved (stored in redo logs)
• Only uncommitted transactions are lost (expected behavior)

Q8: What’s the difference between instance recovery and media recovery?

A:

• Instance Recovery
  • Automatic
  • Handles crashes / SHUTDOWN ABORT
  • Uses online redo logs
• Media Recovery
  • DBA-initiated
  • Handles disk failures or file corruption
  • Uses backups and archived redo logs

Q9: Where can I see how long recovery took?

A:
Check the alert log.

Example:

Completed crash recovery at
 Thread 1: logseq 52, block 2150, scn 987799
145 data blocks read, 145 data blocks written, 2500 KB redo read

• Calculate time between:
  • Beginning crash recovery
  • Completed crash recovery

Q10: If I set FAST_START_MTTR_TARGET = 60, is recovery guaranteed in 60 seconds?

A:
No.

• It is a target, not a guarantee
• Oracle tunes checkpoints to try to meet it
• Actual recovery time depends on:
  • Redo volume
  • I/O performance
  • System load

7. KEY VIEWS FOR DBAs — Quick Reference

Instance and Memory

Instance Information

SELECT instance_name,
       host_name,
       version,
       status,
       startup_time,
       database_status
FROM v$instance;

SGA Components

Total SGA

SELECT name,
       value/1024/1024 mb
FROM v$sga;

Detailed SGA breakdown

SELECT pool,
       name,
       bytes/1024/1024 mb
FROM v$sgastat
WHERE bytes > 10485760      -- Show components > 10 MB
ORDER BY bytes DESC;

PGA Statistics

SELECT name,
       value/1024/1024 mb
FROM v$pgastat
WHERE name IN (
    'total PGA allocated',
    'total PGA inuse',
    'over allocation count'
);

Memory Parameters

SELECT name,
       value,
       isdefault,
       ismodified
FROM v$parameter
WHERE name LIKE '%target%'
   OR name LIKE '%sga%'
   OR name LIKE '%pga%';

Memory Resize Operations (Last 7 Days)

SELECT component,
       oper_type,
       oper_mode,
       initial_size/1024/1024 initial_mb,
       target_size/1024/1024  target_mb,
       final_size/1024/1024   final_mb,
       start_time,
       end_time
FROM v$sga_resize_ops
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;

Sessions and Processes

Active Sessions

SELECT sid,
       serial#,
       username,
       osuser,
       machine,
       program,
       status,
       sql_id,
       event,
       wait_time,
       seconds_in_wait
FROM v$session
WHERE username IS NOT NULL
  AND status = 'ACTIVE'
ORDER BY seconds_in_wait DESC;

Long-Running Sessions

SELECT s.sid,
       s.serial#,
       s.username,
       s.sql_id,
       s.last_call_et/3600 hours_running,
       sq.sql_text
FROM v$session s,
     v$sql sq
WHERE s.sql_id = sq.sql_id
  AND s.status = 'ACTIVE'
  AND s.last_call_et > 3600    -- Running > 1 hour
ORDER BY s.last_call_et DESC;

Blocking Sessions

SELECT blocking_session,
       sid,
       serial#,
       wait_class,
       seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

Kill Session

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Process Information

SELECT pid,
       spid,
       program,
       pga_used_mem/1024/1024   pga_mb,
       pga_alloc_mem/1024/1024  pga_alloc_mb
FROM v$process
ORDER BY pga_used_mem DESC;

Session Count by Program

SELECT program,
       COUNT(*) session_count
FROM v$session
GROUP BY program
ORDER BY COUNT(*) DESC;

SQL Performance

Top SQL by Elapsed Time (Last 1 Hour)

SELECT sql_id,
       elapsed_time/1000000 elapsed_sec,
       executions,
       elapsed_time/executions/1000000 avg_elapsed_sec,
       buffer_gets,
       disk_reads,
       SUBSTR(sql_text, 1, 100) sql_text
FROM v$sqlarea
WHERE last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Top SQL by CPU Time

SELECT sql_id,
       cpu_time/1000000 cpu_sec,
       executions,
       buffer_gets,
       SUBSTR(sql_text, 1, 100) sql_text
FROM v$sqlarea
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

Top SQL by Physical Reads

SELECT sql_id,
       disk_reads,
       executions,
       disk_reads/executions avg_reads,
       SUBSTR(sql_text, 1, 100) sql_text
FROM v$sqlarea
WHERE executions > 0
ORDER BY disk_reads DESC
FETCH FIRST 20 ROWS ONLY;

Full SQL Text

SELECT sql_fulltext
FROM v$sqlarea
WHERE sql_id = 'xxxxxxxxx';

Current Execution Plan

SELECT *
FROM TABLE(
    dbms_xplan.display_cursor(
        'sql_id',
        NULL,
        'ALLSTATS LAST'
    )
);

Wait Events

Top Wait Events (System-Wide)

SELECT event,
       total_waits,
       time_waited/100 time_waited_sec,
       average_wait*10 avg_wait_ms,
       wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 20 ROWS ONLY;

Current Session Waits

SELECT sid,
       serial#,
       username,
       event,
       wait_time,
       seconds_in_wait,
       state
FROM v$session
WHERE wait_class != 'Idle'
  AND username IS NOT NULL
ORDER BY seconds_in_wait DESC;

Wait Event Histogram

SELECT event,
       wait_time_milli,
       wait_count
FROM v$event_histogram
WHERE event = 'log file sync'
ORDER BY wait_time_milli;

Storage

(Datafiles, Tablespaces)

Datafile Information

SELECT file_name,
       tablespace_name,
       bytes/1024/1024/1024 size_gb,
       autoextensible,
       maxbytes/1024/1024/1024 max_gb,
       status
FROM dba_data_files
ORDER BY bytes DESC;

Tablespace Usage

SELECT tablespace_name,
       ROUND(used_percent, 2) used_pct,
       ROUND(used_space * 8192 / 1024 / 1024 / 1024, 2) used_gb,
       ROUND(tablespace_size * 8192 / 1024 / 1024 / 1024, 2) total_gb
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Free Space by Tablespace

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) free_gb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY SUM(bytes);

TEMP Space Usage

SELECT tablespace_name,
       ROUND(tablespace_size/1024/1024, 2) size_mb,
       ROUND(allocated_space/1024/1024, 2) allocated_mb,
       ROUND(free_space/1024/1024, 2) free_mb
FROM dba_temp_free_space;

Who Is Using TEMP

SELECT s.username,
       s.sid,
       s.serial#,
       s.sql_id,
       t.tablespace,
       t.blocks * 8192 / 1024 / 1024 mb_used
FROM v$tempseg_usage t,
     v$session s
WHERE t.session_addr = s.saddr
ORDER BY mb_used DESC;

Top 20 Largest Segments

SELECT owner,
       segment_name,
       segment_type,
       ROUND(bytes/1024/1024/1024, 2) size_gb,
       tablespace_name
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

Redo and Archive Logs

Online Redo Log Configuration

SELECT group#,
       thread#,
       sequence#,
       bytes/1024/1024 size_mb,
       members,
       status,
       archived
FROM v$log;

Redo Log Members

SELECT group#,
       member,
       status,
       type
FROM v$logfile;

Log Switches per Hour (Last 24 Hours)

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
       COUNT(*) switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Redo Generation Rate (MB/Hour)

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
       COUNT(*) *
       (SELECT bytes/1024/1024 FROM v$log WHERE ROWNUM = 1) redo_mb
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Archive Log Destinations

SELECT dest_id,
       status,
       destination,
       error
FROM v$archive_dest
WHERE status != 'INACTIVE';

Archive Log Generation (Last 7 Days)

SELECT TO_CHAR(first_time, 'YYYY-MM-DD') day,
       COUNT(*) archives,
       ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) size_gb
FROM v$archived_log
WHERE first_time > SYSDATE - 7
  AND dest_id = 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')
ORDER BY day;

Check for Archive Gaps (Data Guard)

SELECT * FROM v$archive_gap;

Background Processes

All Background Processes

SELECT paddr,
       name,
       description,
       error_count
FROM v$bgprocess
WHERE paddr != '00'
ORDER BY name;

Check Specific Process Status

SELECT *
FROM v$bgprocess
WHERE name IN ('PMON', 'SMON', 'LGWR', 'DBW0', 'CKPT');

Process Resource Usage

SELECT p.program,
       p.spid,
       p.pga_used_mem/1024/1024 pga_mb,
       s.sid,
       s.status,
       s.event
FROM v$process p,
     v$session s
WHERE p.addr = s.paddr(+)
ORDER BY p.pga_used_mem DESC;

Database Configuration

Database Information

SELECT dbid,
       name,
       db_unique_name,
       platform_name,
       log_mode,
       open_mode,
       database_role,
       flashback_on,
       force_logging
FROM v$database;

All Parameters

SELECT name,
       value,
       isdefault,
       ismodified,
       description
FROM v$parameter
ORDER BY name;

Modified Parameters (Non-Default)

SELECT name,
       value,
       ismodified
FROM v$parameter
WHERE ismodified != 'FALSE'
ORDER BY name;

Control Files

SELECT name,
       status
FROM v$controlfile;

Features in Use

SELECT name,
       currently_used,
       first_usage_date,
       last_usage_date
FROM dba_feature_usage_statistics
WHERE currently_used = 'TRUE'
ORDER BY last_usage_date DESC;

Performance Monitoring

AWR Snapshot Information

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

Generate AWR Report

@?/rdbms/admin/awrrpt.sql

ASH Report

@?/rdbms/admin/ashrpt.sql

Database Time Model

SELECT stat_name,
       ROUND(value/1000000, 2) time_sec
FROM v$sys_time_model
ORDER BY value DESC;

System Statistics

SELECT name,
       value
FROM v$sysstat
WHERE name IN (
    'user commits',
    'user rollbacks',
    'physical reads',
    'physical writes',
    'db block gets',
    'consistent gets',
    'redo size',
    'parse count (total)',
    'parse count (hard)',
    'execute count'
)
ORDER BY name;

Hit Ratios

SELECT
  'Buffer Cache Hit Ratio' metric,
  ROUND(
    (1 - (phy.value / (db.value + con.value))) * 100,
    2
  ) pct
FROM v$sysstat phy,
     v$sysstat db,
     v$sysstat con
WHERE phy.name = 'physical reads'
  AND db.name  = 'db block gets'
  AND con.name = 'consistent gets'

UNION ALL

SELECT
  'Library Cache Hit Ratio',
  ROUND((SUM(pinhits) / SUM(pins)) * 100, 2)
FROM v$librarycache;

Alert Log Queries

Recent Alerts (Last 24 Hours)

SELECT originating_timestamp,
       message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1
ORDER BY originating_timestamp DESC;

Errors in Alert Log

SELECT originating_timestamp,
       message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-%'
  AND originating_timestamp > SYSDATE - 7
ORDER BY originating_timestamp DESC;

Specific Error Search

SELECT originating_timestamp,
       message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00600%'
ORDER BY originating_timestamp DESC;

8. QUICK TROUBLESHOOTING GUIDE

Problem: Database Performance Slow

1. Check Wait Events

SELECT event,
       total_waits,
       time_waited/100 sec
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

2. Check Top SQL

SELECT sql_id,
       elapsed_time/1000000 elapsed_sec,
       executions
FROM v$sqlarea
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

3. Check Blocking Sessions

SELECT blocking_session,
       sid,
       seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

4. Check Resource Usage

SELECT *
FROM v$resource_limit
WHERE current_utilization > 0;

Problem: Database Hanging

1. Check Alert Log

tail -100 alert_orcl.log

2. Check Archive Destination Status

SELECT dest_id,
       status,
       error
FROM v$archive_dest;

3. Check for Locks

SELECT * FROM dba_blockers;
SELECT * FROM dba_waiters;

4. Check LGWR Status

SELECT event
FROM v$session
WHERE program LIKE '%LGWR%';

Last Resort

SHUTDOWN ABORT;
STARTUP;

Problem: Tablespace Full

1. Check Usage

SELECT tablespace_name,
       used_percent
FROM dba_tablespace_usage_metrics
WHERE used_percent > 85;

2. Check Autoextend Status

SELECT file_name,
       autoextensible,
       maxbytes
FROM dba_data_files
WHERE tablespace_name = 'USERS';

3. Add Datafile or Resize

Add datafile

ALTER TABLESPACE users
ADD DATAFILE '/u01/users02.dbf'
SIZE 1G AUTOEXTEND ON;

Resize existing datafile

ALTER DATABASE DATAFILE '/u01/users01.dbf' RESIZE 10G;

Enable autoextend

ALTER DATABASE DATAFILE '/u01/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Problem: High CPU Usage

1. Find CPU-Intensive SQL

SELECT sql_id,
       cpu_time/1000000 cpu_sec,
       executions
FROM v$sqlarea
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

2. Check Parsing Issues

SELECT name,
       value
FROM v$sysstat
WHERE name IN (
    'parse count (total)',
    'parse count (hard)',
    'execute count'
);

Check for literal SQL (no bind variables)

SELECT sql_id,
       sql_text,
       executions
FROM v$sqlarea
WHERE executions = 1
FETCH FIRST 20 ROWS ONLY;

Problem: High Memory Usage

1. Check PGA Usage

SELECT * FROM v$pgastat;

Sessions using most PGA

SELECT s.sid,
       s.username,
       p.pga_used_mem/1024/1024 pga_mb
FROM v$session s,
     v$process p
WHERE s.paddr = p.addr
ORDER BY p.pga_used_mem DESC
FETCH FIRST 20 ROWS ONLY;

2. Check SGA Usage

SELECT *
FROM v$sgastat
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

3. Check for Memory Leaks

SELECT pool,
       name,
       bytes/1024/1024 mb
FROM v$sgastat
WHERE name LIKE '%free%'
ORDER BY bytes;

Best of luck with your Oracle DBA interview!
I hope this guide has helped strengthen your preparation and confidence.

Feel free to revisit any section whenever needed, and remember—understanding the why behind Oracle’s architecture is what truly sets great DBAs apart.

You’ve got this! 🚀