Backend interview prep

Start

Oracle Architecture Interview Questions

Master Oracle Architecture with interview-focused questions covering Oracle Instance, SGA, PGA, Background Processes, DBWR, LGWR, SMON, PMON, CKPT, ARCn, Listener, Startup, Shutdown, and enterprise production architecture.

Introduction

Oracle Architecture is one of the most important interview topics for

  • Oracle DBA
  • Java Developers
  • Database Engineers
  • Solution Architects

Oracle's architecture is designed for

  • High Performance
  • High Availability
  • Scalability
  • Fault Tolerance
  • Enterprise Workloads

Understanding Oracle Architecture helps explain how Oracle processes SQL statements, manages memory, writes data to disk, performs recovery, and handles concurrent users.

Oracle Architecture Overview

flowchart LR

Application --> Listener --> OracleInstance["Oracle Instance"]

OracleInstance["Oracle Instance"] --> SGA

OracleInstance["Oracle Instance"] --> PGA

OracleInstance["Oracle Instance"] --> BackgroundProcesses["Background Processes"]

BackgroundProcesses["Background Processes"] --> OracleDatabase["Oracle Database"]

OracleDatabase["Oracle Database"] --> ControlFiles["Control Files"]

OracleDatabase["Oracle Database"] --> DataFiles["Data Files"]

OracleDatabase["Oracle Database"] --> RedoLogFiles["Redo Log Files"]

1. What is Oracle Architecture?

Answer

Oracle Architecture consists of two major components

  • Oracle Instance
  • Oracle Database

The Instance manages access to the Database.

Oracle Architecture

Oracle System

├── Oracle Instance
│     ├── SGA
│     ├── PGA
│     └── Background Processes
│
└── Oracle Database
      ├── Data Files
      ├── Control Files
      └── Redo Log Files

2. What is an Oracle Instance?

Oracle Instance is the running environment.

It consists of

  • SGA
  • Background Processes

The instance exists only while Oracle is running.

3. What is an Oracle Database?

Oracle Database consists of physical files stored on disk.

These include

  • Data Files
  • Control Files
  • Redo Log Files

The database stores all application data.

Database vs Instance

Oracle Instance Oracle Database
Memory Physical Files
Temporary Permanent
SGA + Processes Data Files
Runs SQL Stores Data

4. What is SGA?

SGA

System Global Area

is shared memory used by all Oracle sessions.

Contains

  • Buffer Cache
  • Shared Pool
  • Redo Log Buffer
  • Large Pool
  • Java Pool
  • Streams Pool

SGA Structure

flowchart TD

SGA --> BufferCache

SGA --> SharedPool

SGA --> RedoBuffer

SGA --> LargePool

SGA --> JavaPool

5. What is Buffer Cache?

Buffer Cache stores

database blocks

in memory.

Benefits

  • Faster Reads
  • Reduced Disk IO

Frequently accessed blocks remain cached.

Buffer Cache Flow

Disk

↓

Buffer Cache

↓

SQL Execution

6. What is Shared Pool?

Shared Pool stores

  • SQL Statements
  • Execution Plans
  • Data Dictionary Cache

Benefits

  • SQL Reuse
  • Lower Parsing Cost

7. What is Library Cache?

Library Cache is part of Shared Pool.

Stores

  • Parsed SQL
  • PL/SQL
  • Execution Plans

Reduces repeated parsing.

8. What is Data Dictionary Cache?

Caches metadata such as

  • Tables
  • Columns
  • Users
  • Privileges
  • Indexes

Avoids repeated disk access.

9. What is Redo Log Buffer?

Stores redo information

before

LGWR writes it

to Redo Log Files.

Ensures transaction durability.

Redo Flow

flowchart LR

Transaction --> RedoBufferLgwrRedologfilesredo["Redo Buffer --> LGWR --> RedoLogFiles["Redo Log Files"]"]

10. What is Large Pool?

Large Pool is optional memory used for

  • RMAN Backups
  • Shared Server
  • Parallel Execution

Prevents pressure on Shared Pool.

11. What is Java Pool?

Stores memory used by Oracle JVM.

Used for Java stored procedures.

12. What is Streams Pool?

Supports Oracle Streams and GoldenGate-related memory requirements.

13. What is PGA?

PGA

Program Global Area

is private memory allocated to each server process.

Contains

  • Sort Area
  • Hash Area
  • Session Memory

Unlike SGA, PGA is not shared.

SGA vs PGA

SGA PGA
Shared Private
One Per Instance One Per Process
Shared SQL Session Memory

14. What are Oracle Background Processes?

Oracle automatically starts background processes to manage

  • Writing Data
  • Recovery
  • Checkpoints
  • Logging
  • Cleanup

Important processes include

  • DBWR
  • LGWR
  • SMON
  • PMON
  • CKPT
  • ARCn

Background Processes

flowchart TD

OracleInstance["Oracle Instance"] --> DBWR

OracleInstance["Oracle Instance"] --> LGWR

OracleInstance["Oracle Instance"] --> SMON

OracleInstance["Oracle Instance"] --> PMON

OracleInstance["Oracle Instance"] --> CKPT

OracleInstance["Oracle Instance"] --> ARCn

15. What is DBWR?

DBWR

Database Writer

writes dirty buffers

from Buffer Cache

to Data Files.

It does not write every transaction immediately.

DBWR Flow

Buffer Cache

↓

DBWR

↓

Data Files

16. What is LGWR?

LGWR

Log Writer

writes Redo Buffer

to Redo Log Files.

LGWR writes

  • Commit
  • Every few seconds
  • Buffer Full

This ensures durability.

LGWR Flow

Redo Buffer

↓

LGWR

↓

Redo Log Files

17. What is SMON?

SMON

System Monitor

Responsibilities

  • Instance Recovery
  • Cleanup Temporary Segments
  • Space Recovery

18. What is PMON?

PMON

Process Monitor

Responsibilities

  • Cleans failed sessions
  • Releases locks
  • Frees resources
  • Restarts failed server processes (where applicable)

19. What is CKPT?

Checkpoint Process

Updates

  • Data File Headers
  • Control Files

during checkpoints.

It signals DBWR to write dirty buffers.

20. What is ARCn?

ARCn

Archiver Process

Copies filled Redo Log Files

to Archive Logs.

Required for

  • Backup
  • Recovery
  • Data Guard

ARCn Flow

Redo Log Files

↓

ARCn

↓

Archive Logs

21. What is Oracle Listener?

Oracle Listener accepts

incoming client connections.

Acts as the communication layer

between

Applications

and

Oracle Instance.

Listener Architecture

flowchart LR

Application --> Listener --> OracleInstance["Oracle Instance"]

22. What happens during Oracle Startup?

Startup stages

STARTUP NOMOUNT

↓

STARTUP MOUNT

↓

OPEN DATABASE

Startup Stages

Stage Description
NOMOUNT Starts Instance
MOUNT Reads Control Files
OPEN Opens Data Files

23. What happens during Shutdown?

Shutdown options

  • NORMAL
  • TRANSACTIONAL
  • IMMEDIATE
  • ABORT

Recommended

SHUTDOWN IMMEDIATE

24. What is Checkpoint?

Checkpoint synchronizes

Buffer Cache

with

Data Files.

Benefits

  • Faster Recovery
  • Consistent Database State

25. What are Redo Log Files?

Redo Log Files store

every database change.

Used for

  • Crash Recovery
  • Media Recovery
  • Data Guard
  • Replication

26. What are Control Files?

Control Files store

  • Database Name
  • Data File Locations
  • Redo Log Information
  • Checkpoint Information

Database cannot start without them.

27. Banking Example

Money Transfer

↓

Redo Generated

↓

LGWR Writes Redo

↓

DBWR Writes Data

↓

Transaction Committed

28. E-Commerce Example

Customer places order

↓

Buffer Cache updated

↓

Redo generated

↓

Commit

↓

DBWR writes later

29. Production Example

Database crash

↓

SMON performs Instance Recovery

↓

Redo Logs replayed

↓

Database becomes consistent

30. Common Oracle Architecture Interview Questions

  • Difference between Instance and Database
  • SGA vs PGA
  • DBWR vs LGWR
  • PMON vs SMON
  • CKPT purpose
  • ARCn purpose
  • Listener role
  • Startup stages
  • Shutdown modes
  • Buffer Cache vs Shared Pool

Oracle Memory Architecture

flowchart LR

SGA --> BufferCache

SGA --> SharedPool

SGA --> RedoBuffer

PGA --> SessionMemory

PGA --> SortArea

Enterprise Best Practices

  • Allocate adequate SGA based on workload.
  • Size PGA appropriately for sorting and hashing.
  • Monitor Buffer Cache hit ratio.
  • Keep Shared Pool sized to avoid excessive hard parsing.
  • Enable ARCHIVELOG mode in production.
  • Monitor background process health.
  • Place Redo Logs on fast storage.
  • Regularly review AWR and ASH reports.
  • Test startup and recovery procedures.
  • Monitor listener availability.

Quick Revision

Topic Key Point
Oracle Instance Memory + Processes
Oracle Database Physical Files
SGA Shared Memory
PGA Private Process Memory
Buffer Cache Data Blocks
Shared Pool SQL & Metadata Cache
Redo Buffer Transaction Changes
DBWR Writes Data Files
LGWR Writes Redo Logs
SMON Instance Recovery
PMON Process Cleanup
CKPT Checkpoint Manager
ARCn Archive Logs
Listener Client Connectivity

Interview Tips

Interviewers frequently ask

  • What is Oracle Architecture?
  • Database vs Instance?
  • Explain SGA.
  • SGA vs PGA.
  • DBWR vs LGWR.
  • PMON vs SMON.
  • Explain CKPT.
  • Explain ARCn.
  • Oracle Startup Stages.
  • Explain Buffer Cache.

A strong interview explanation is:

"Oracle Architecture consists of the Oracle Instance and the Oracle Database. The Instance contains the SGA and background processes such as DBWR, LGWR, PMON, and SMON. The Database contains physical files like Data Files, Control Files, and Redo Log Files. When a transaction is committed, LGWR writes redo information immediately, while DBWR writes modified data blocks to disk later. This separation ensures high performance and reliable crash recovery."

Summary

Oracle Architecture is built around the interaction between the Oracle Instance and the Oracle Database. The Instance manages memory (SGA and PGA) and background processes such as DBWR, LGWR, SMON, PMON, CKPT, and ARCn, while the Database stores data in physical files such as Data Files, Control Files, and Redo Log Files.

Understanding Oracle memory structures, background processes, startup and shutdown sequences, and recovery mechanisms is essential for Oracle Developers, Database Engineers, DBAs, and Solution Architects working with enterprise Oracle environments.