📊 Module 1: Oracle Database Fundamentals

Architecture, Instance Management, Memory Structures & Processes

3
Main Layers
5+
Key Processes
SGA/PGA
Memory Areas
100%
Coverage

🏗️ Oracle Database Architecture

Three-Tier Architecture Overview

Oracle Database follows a three-layer architecture that separates concerns and enables scalability:

Layer Components Function
Client/Presentation Layer SQL*Plus, SQL Developer, Applications User interface & SQL submission
Application/Logic Layer Listener, Connection Manager, Dispatcher Connection management, request processing
Database Server Layer Oracle Instance, Database Files Data storage, transactions, recovery

Database vs Instance

💾 Memory Structures (SGA - System Global Area)

SGA Components

The System Global Area is shared memory allocated when database instance starts:

Component Size Purpose
DB Buffer Cache 25-50% of SGA Caches data blocks from disk, reduces I/O
Redo Log Buffer 4-10MB (small) Records all database changes for recovery
Shared Pool 20-40% of SGA Library cache (SQL), Data dictionary cache
Large Pool Optional Memory for I/O, batch operations, sessions
Java Pool Optional Java code execution in database
💡 Key Insight: Buffer Cache efficiency directly impacts performance. High cache hit ratio (95%+) means less disk I/O.

Query Execution & Buffer Cache Flow

-- User query execution flow:
SELECT * FROM employees WHERE salary > 5000;

-- Oracle checks: Is data in Buffer Cache?
-- YES → Return from cache (1-2ms)
-- NO  → Read from disk (5-20ms) → Store in cache

-- Check buffer cache hit ratio:
SELECT 
    name, 
    value 
FROM v$sysstat 
WHERE name IN (
    'consistent gets',
    'db block gets',
    'physical reads'
);

⚙️ Process Structures

Background Processes

These processes run automatically when database instance starts:

Process Full Name Responsibility
PMON Process Monitor Cleans up failed processes, recovers resources
SMON System Monitor Instance recovery, merges free extents
DBW0 Database Writer Writes dirty blocks from buffer cache to disk
LGWR Log Writer Writes redo log buffer to redo log files
CKPT Checkpoint Synchronizes database files with SGA
ARCH Archiver Archives filled redo log files (optional)

Viewing Active Processes

-- List all active background processes:
SELECT pid, pname, spid 
FROM v$process 
WHERE pname IS NOT NULL 
ORDER BY pname;

-- Output example:
-- PID PNAME SPID
-- 2   PMON  15234
-- 3   SMON  15235
-- 4   DBW0  15236
-- 5   LGWR  15237

📁 Database Physical Files

Core Database Files

File Type Quantity Purpose Recovery Needed
Data Files Multiple (1-1000+) Stores actual table data, indexes YES - Critical
Redo Log Files 2-3 (minimum) Records all changes for recovery YES - Critical
Control Files 2-3 (multiplexed) Database configuration, checkpoint info YES - Critical
Temp Files 1+ (optional) Temporary storage for sorts, hash joins NO - Recreated
Undo Files 1+ (in undo tablespace) Stores undo data for rollback YES - Important

Checking Physical File Locations

-- View all data files:
SELECT file#, name, bytes/1024/1024 AS size_mb 
FROM v$datafile;

-- View redo log files:
SELECT group#, member, status 
FROM v$logfile 
ORDER BY group#;

-- View control files:
SELECT name 
FROM v$controlfile;

🔄 Database Initialization & Parameters

Initialization Parameters (init.ora / spfile)

Control database startup behavior and resource allocation:

Parameter Type Example Value Impact
db_name String ORCL Database identifier
db_files Integer 200 Max data files allowed
sga_max_size Size 4G Max SGA size
pga_aggregate_target Size 2G Total PGA for all sessions
processes Integer 150 Max concurrent processes
open_cursors Integer 300 Max open cursors per session

Modifying Parameters Dynamically

-- Change parameter in session (temporary):
ALTER SESSION SET db_recovery_file_dest_size = 50G;

-- Change parameter system-wide (permanent in spfile):
ALTER SYSTEM SET sga_max_size = 5G SCOPE = BOTH;

-- View current parameter values:
SHOW PARAMETERS sga_max_size;

-- View all parameters:
SELECT name, value 
FROM v$parameter 
WHERE name LIKE '%sga%';
⚠️ Warning: Changing parameters with SCOPE=BOTH requires database restart. Use SCOPE=MEMORY for runtime changes only.

🚀 Instance Startup & Shutdown Sequence

Database Startup Phases

Phase Command Actions
NOMOUNT STARTUP NOMOUNT Read init.ora, start processes, allocate SGA
MOUNT ALTER DATABASE MOUNT Read control file, identify data/redo files
OPEN ALTER DATABASE OPEN Open data/redo/temp files, ready for users

Startup & Shutdown Commands

-- Normal startup (all 3 phases):
STARTUP;

-- Startup with recovery:
STARTUP MOUNT;
RECOVER DATABASE;

-- Restrict access (admin only):
STARTUP RESTRICT;

-- Graceful shutdown (wait for sessions):
SHUTDOWN NORMAL;

-- Immediate shutdown (no wait):
SHUTDOWN IMMEDIATE;

-- Abort shutdown (emergency, may need recovery):
SHUTDOWN ABORT;

-- Check current database status:
SELECT status, open_cursors 
FROM v$instance;

✅ Best Practices

Architecture Best Practices

Performance Monitoring Essentials

-- Monitor active sessions:
SELECT sid, serial#, username, status, event 
FROM v$session 
WHERE status = 'ACTIVE';

-- Check database load:
SELECT value 
FROM v$sysstat 
WHERE name = 'CPU used by this session';

-- Monitor tablespace usage:
SELECT tablespace_name, 
        sum(bytes)/1024/1024 total_mb,
        sum(DECODE(free, null, 0, free))/1024/1024 free_mb
FROM dba_data_files 
GROUP BY tablespace_name;
✓ Learning Checklist - Module 1