Oracle Database Architecture - Complete Guide

Oracle Database Architecture - Complete Guide

Understanding Oracle database architecture is crucial for effective database administration, performance tuning, and troubleshooting. This comprehensive guide covers both traditional and modern Oracle architectures, including the revolutionary multitenant container database (CDB) and pluggable database (PDB) features.

Oracle Multitenant Architecture (12c+)

Container Database (CDB) Overview

Oracle’s multitenant architecture, introduced in Oracle 12c, represents a fundamental shift in database design. This architecture allows multiple pluggable databases (PDBs) to share a single container database (CDB), providing significant benefits for consolidation, management, and cloud deployments.

Container Database Structure

-- Check if database is a CDB
SELECT name, cdb FROM v$database;

-- View CDB information
SELECT con_id, name, open_mode, restricted, open_time
FROM v$containers
ORDER BY con_id;

-- Check current container
SHOW CON_NAME;
SHOW CON_ID;

CDB Components:

1. Root Container (CDB$ROOT) - Contains Oracle metadata and common users
2. Seed PDB (PDB$SEED) - Template for creating new PDBs
3. User PDBs - Individual pluggable databases containing application data
4. System Container - Overall CDB management context

Pluggable Database (PDB) Architecture

-- List all PDBs in the CDB
SELECT con_id, pdb_id, pdb_name, status,
       creation_scn, open_mode, restricted
FROM cdb_pdbs
ORDER BY pdb_id;

-- Connect to specific PDB
ALTER SESSION SET CONTAINER = hr_pdb;

-- Open/Close PDB operations
ALTER PLUGGABLE DATABASE hr_pdb OPEN;
ALTER PLUGGABLE DATABASE hr_pdb CLOSE;
ALTER PLUGGABLE DATABASE ALL OPEN;

-- PDB resource management
SELECT pdb_name, shares, utilization_limit,
       parallel_server_limit
FROM cdb_pdb_history;

Multitenant Benefits

Consolidation Advantages

-- Resource utilization across PDBs
SELECT p.con_id, p.pdb_name,
       s.statistic#, s.name, s.value
FROM v$sysstat s
JOIN v$containers p ON s.con_id = p.con_id
WHERE s.name IN ('CPU used by this session',
                 'physical reads',
                 'physical writes')
ORDER BY p.con_id, s.statistic#;

Key Benefits:

1. Resource Consolidation - Multiple databases share memory and processes
2. Simplified Management - Single CDB manages multiple PDBs
3. Rapid Provisioning - Clone PDBs in minutes
4. Cost Reduction - Fewer Oracle licenses required
5. Cloud Ready - Perfect for cloud and container deployments

PDB Management Operations

-- Create new PDB from seed
CREATE PLUGGABLE DATABASE sales_pdb
ADMIN USER sales_admin IDENTIFIED BY password
FILE_NAME_CONVERT = ('/pdbseed/', '/sales_pdb/');

-- Clone existing PDB
CREATE PLUGGABLE DATABASE test_hr_pdb FROM hr_pdb;

-- Unplug/Plug operations for migration
ALTER PLUGGABLE DATABASE sales_pdb UNPLUG INTO '/tmp/sales_pdb.xml';
CREATE PLUGGABLE DATABASE sales_pdb USING '/tmp/sales_pdb.xml'
COPY;

-- Resource limits
ALTER PLUGGABLE DATABASE hr_pdb SET CONTAINER_DATA = ALL;

PDB Resource Management

Resource Allocation

-- Set PDB resource plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CDB_PLAN';

-- PDB resource allocation
SELECT plan_name, pdb_name, shares, utilization_limit,
       parallel_server_limit
FROM cdb_pdb_resource_plans;

-- Memory usage by PDB
SELECT con_id, pool, name, bytes/1024/1024 as mb
FROM v$sgainfo
WHERE con_id > 2
ORDER BY con_id, bytes DESC;

Performance Monitoring

-- PDB performance metrics
SELECT p.pdb_name, m.metric_name, m.value, m.metric_unit
FROM v$sysmetric m
JOIN v$containers p ON m.con_id = p.con_id
WHERE m.metric_name IN ('CPU Usage Per Sec',
                        'Physical Reads Per Sec',
                        'Physical Writes Per Sec',
                        'User Transaction Per Sec')
ORDER BY p.pdb_name, m.metric_name;

Traditional Single-Tenant Architecture

Non-CDB Architecture (Pre-12c Style)

For databases not using multitenant architecture or Oracle versions prior to 12c:

-- Check database architecture
SELECT name, cdb, version_time FROM v$database;

-- For non-CDB databases
SELECT instance_name, host_name, version, startup_time
FROM v$instance;

Instance and Database Architecture

Oracle Instance

An Oracle instance consists of memory structures and background processes that manage database operations.

Memory Structures

System Global Area (SGA)

-- View SGA components
SELECT
    component,
    current_size/1024/1024 as current_mb,
    min_size/1024/1024 as min_mb,
    max_size/1024/1024 as max_mb
FROM v$sga_dynamic_components
ORDER BY current_size DESC;

-- SGA summary
SELECT * FROM v$sga;

SGA Components:

1. Database Buffer Cache

   • Stores data blocks read from datafiles
   • Managed by LRU algorithm
   • Configured with db_cache_size

2. Shared Pool

   • Library cache (SQL and PL/SQL code)
   • Data dictionary cache
   • Configured with shared_pool_size

3. Redo Log Buffer

   • Stores redo entries before writing to redo logs
   • Configured with log_buffer

4. Large Pool

   • Used for large allocations (RMAN, parallel operations)
   • Configured with large_pool_size

5. Java Pool

   • Memory for Java operations
   • Configured with java_pool_size

Program Global Area (PGA)

-- View PGA statistics
SELECT
    name,
    value/1024/1024 as value_mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'total PGA allocated',
    'total PGA used'
);

-- PGA usage by session
SELECT
    s.sid,
    s.username,
    ROUND(p.pga_used_mem/1024/1024, 2) as pga_used_mb,
    ROUND(p.pga_alloc_mem/1024/1024, 2) as pga_alloc_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
ORDER BY p.pga_alloc_mem DESC;

Background Processes

Core Background Processes:

1. System Monitor (SMON)

   • Instance recovery
   • Cleanup of temporary segments
   • Coalescing free space

2. Process Monitor (PMON)

   • Process cleanup and recovery
   • Listener registration
   • Resource cleanup

3. Database Writer (DBWn)

   • Writes dirty buffers to datafiles
   • Multiple writers possible (DBW0, DBW1, …)

4. Log Writer (LGWR)

   • Writes redo log buffer to redo log files
   • Triggers on commit, buffer full, or timeout

5. Checkpoint (CKPT)

   • Updates control files and datafile headers
   • Manages checkpoint processing

6. Archiver (ARCn)

   • Archives filled redo log files
   • Multiple archivers for performance

-- View background processes
SELECT
    name,
    description
FROM v$bgprocess
WHERE paddr != '00'
ORDER BY name;

-- Check process status
SELECT
    program,
    COUNT(*) as process_count
FROM v$session
WHERE type = 'BACKGROUND'
GROUP BY program
ORDER BY program;

Database Physical Structure

Control Files

Control files contain critical database metadata:

• Database name and identifier
• Datafile and redo log file locations
• Database creation timestamp
• Current log sequence number

-- View control file information
SELECT * FROM v$controlfile;

-- Control file record sections
SELECT
    type,
    records_total,
    records_used,
    record_size
FROM v$controlfile_record_section
ORDER BY type;

-- Create control file trace
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Data Files

Data files store the actual database data:

-- View datafile information
SELECT
    file_name,
    tablespace_name,
    ROUND(bytes/1024/1024, 2) as size_mb,
    ROUND(maxbytes/1024/1024, 2) as max_size_mb,
    autoextensible,
    status
FROM dba_data_files
ORDER BY tablespace_name, file_name;

-- Check datafile I/O
SELECT
    df.file_name,
    fs.phyrds as physical_reads,
    fs.phywrts as physical_writes,
    fs.phyblkrd as blocks_read,
    fs.phyblkwrt as blocks_written
FROM v$filestat fs
JOIN dba_data_files df ON fs.file# = df.file_id
ORDER BY fs.phyrds + fs.phywrts DESC;

Redo Log Files

Redo logs record all database changes:

-- View redo log information
SELECT
    group#,
    thread#,
    sequence#,
    ROUND(bytes/1024/1024, 2) as size_mb,
    members,
    status,
    archived,
    first_change#
FROM v$log
ORDER BY group#;

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

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

Archive Log Files

Archive logs are copies of filled redo logs:

-- View archive log destinations
SELECT
    dest_id,
    destination,
    status,
    binding
FROM v$archive_dest
WHERE status != 'INACTIVE';

-- Archive log usage
SELECT * FROM v$recovery_file_dest;

-- Recent archive logs
SELECT
    name,
    sequence#,
    first_change#,
    next_change#,
    completion_time
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY completion_time DESC;

Logical Storage Structure

Tablespaces

Tablespaces are logical storage units:

-- Tablespace information
SELECT
    tablespace_name,
    status,
    contents,
    logging,
    extent_management,
    allocation_type,
    segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

-- Tablespace usage
SELECT
    tablespace_name,
    ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
    ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
    ROUND(used_percent, 2) AS used_percent
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Segments

Segments are sets of extents allocated for database objects:

-- Large segments
SELECT
    owner,
    segment_name,
    segment_type,
    tablespace_name,
    ROUND(bytes/1024/1024, 2) as size_mb,
    blocks,
    extents
FROM dba_segments
WHERE bytes > 100*1024*1024  -- > 100MB
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Segment growth analysis
SELECT
    owner,
    object_name,
    object_type,
    SUM(space_used_delta) as space_growth_mb
FROM dba_hist_seg_stat
WHERE begin_interval_time > SYSDATE - 30
GROUP BY owner, object_name, object_type
ORDER BY space_growth_mb DESC
FETCH FIRST 20 ROWS ONLY;

Extents and Blocks

-- Extent information for a table
SELECT
    extent_id,
    file_id,
    block_id,
    blocks,
    ROUND(bytes/1024, 2) as size_kb
FROM dba_extents
WHERE owner = 'HR'
    AND segment_name = 'EMPLOYEES'
ORDER BY extent_id;

-- Block size information
SELECT
    tablespace_name,
    block_size
FROM dba_tablespaces
ORDER BY tablespace_name;

Oracle Processes Architecture

Server Processes

Dedicated Server

Each user connection has its own server process:

-- View dedicated server connections
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.machine,
    p.spid as server_process_id
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.type = 'USER'
    AND s.username IS NOT NULL
ORDER BY s.sid;

Shared Server

Multiple user sessions share a pool of server processes:

-- Shared server configuration
SHOW PARAMETER shared_server;

-- Shared server statistics
SELECT
    name,
    value
FROM v$sysstat
WHERE name LIKE '%shared server%';

-- Dispatcher information
SELECT
    name,
    network,
    status,
    accept,
    messages,
    bytes
FROM v$dispatcher;

User Processes

User processes run on client machines and connect to server processes:

-- User session information
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.osuser,
    s.machine,
    s.program,
    s.logon_time,
    s.last_call_et
FROM v$session s
WHERE s.type = 'USER'
    AND s.username IS NOT NULL
ORDER BY s.logon_time;

Oracle Network Architecture

Listener

The listener process manages client connections:

# Check listener status
lsnrctl status

# View listener configuration
cat $ORACLE_HOME/network/admin/listener.ora

# Monitor listener activity
lsnrctl services

-- Database registration with listener
SELECT
    instance_name,
    status,
    database_status
FROM v$instance;

-- Service information
SELECT
    name,
    network_name,
    enabled
FROM v$services;

TNS (Transparent Network Substrate)

TNS provides location transparency for database connections:

# Test TNS connectivity
tnsping <service_name>

# View TNS configuration
cat $ORACLE_HOME/network/admin/tnsnames.ora

Memory Management

Automatic Memory Management (AMM)

-- Check AMM settings
SHOW PARAMETER memory_target;
SHOW PARAMETER memory_max_target;
SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;

-- Memory advisor recommendations
SELECT
    component,
    current_size/1024/1024 as current_mb,
    oper_count,
    last_oper_type,
    last_oper_mode,
    last_oper_time
FROM v$memory_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

Automatic Shared Memory Management (ASMM)

-- View ASMM components
SELECT
    component,
    oper_count,
    last_oper_type,
    last_oper_mode,
    TO_CHAR(last_oper_time, 'DD-MON-YY HH24:MI:SS') as last_operation
FROM v$sga_dynamic_free_memory;

-- SGA resize operations
SELECT
    component,
    oper_type,
    oper_mode,
    initial_size/1024/1024 as initial_mb,
    target_size/1024/1024 as target_mb,
    final_size/1024/1024 as final_mb,
    start_time,
    end_time
FROM v$sga_resize_ops
ORDER BY start_time DESC;

Transaction Processing

Transaction Components

-- Active transactions
SELECT
    s.sid,
    s.serial#,
    s.username,
    t.start_time,
    t.used_ublk as undo_blocks,
    t.used_urec as undo_records,
    ROUND((SYSDATE - t.start_date) * 24 * 60, 2) as minutes_active
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr
ORDER BY t.start_date;

-- Undo statistics
SELECT
    begin_time,
    end_time,
    undotsn,
    undoblks,
    txncount,
    maxquerylen,
    maxquerysqlid
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 24 ROWS ONLY;

ACID Properties Implementation

1. Atomicity: Undo segments ensure all-or-nothing transactions
2. Consistency: Constraints and triggers maintain data integrity
3. Isolation: Lock mechanisms and read consistency
4. Durability: Redo logs ensure committed changes survive system failures

High Availability Architecture

Oracle Real Application Clusters (RAC)

Oracle RAC provides high availability and scalability by allowing multiple instances to access a single database simultaneously.

-- RAC information (if applicable)
SELECT
    inst_id,
    instance_name,
    host_name,
    status,
    startup_time,
    database_status
FROM gv$instance
ORDER BY inst_id;

-- Cluster database parameter
SHOW PARAMETER cluster_database;

-- RAC services
SELECT name, network_name, failover_method, failover_type
FROM gv$services
WHERE name NOT LIKE 'SYS%';

-- Interconnect traffic
SELECT name, value FROM gv$sysstat
WHERE name LIKE '%global cache%'
   OR name LIKE '%gc %';

RAC with Multitenant

-- RAC instances and PDB status
SELECT i.inst_id, i.instance_name, p.pdb_name, p.open_mode
FROM gv$instance i
CROSS JOIN cdb_pdbs p
ORDER BY i.inst_id, p.pdb_name;

-- PDB services across RAC nodes
SELECT inst_id, service_name, pdb_name, enabled
FROM gv$pdb_services
ORDER BY service_name, inst_id;

Data Guard

Oracle Data Guard provides disaster recovery and high availability through standby databases.

-- Data Guard configuration
SELECT
    database_role,
    protection_mode,
    protection_level,
    open_mode,
    log_mode,
    force_logging
FROM v$database;

-- Standby database status
SELECT
    process,
    status,
    client_process,
    client_pid,
    sequence#,
    block#
FROM v$managed_standby
ORDER BY process;

-- Archive log apply status
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
WHERE dest_id = 1
ORDER BY sequence# DESC;

Data Guard with PDBs

-- PDB synchronization status
SELECT pdb_name, synchronization_status,
       apply_lag, transport_lag
FROM cdb_pdb_sync_status;

-- Standby PDB information
SELECT pdb_id, pdb_name, status, open_mode
FROM v$pdbs
ORDER BY pdb_id;

Cloud and Container Integration

Oracle Cloud Integration

Autonomous Database Architecture

-- Autonomous Database features
SELECT parameter, value, description
FROM v$parameter
WHERE parameter LIKE 'autonomous%'
   OR parameter LIKE 'auto_%';

-- Service names in Autonomous
SELECT name, network_name
FROM v$services
WHERE name LIKE '%_HIGH'
   OR name LIKE '%_MEDIUM'
   OR name LIKE '%_LOW';

Oracle Database on Kubernetes

• Container-ready PDBs - Each PDB runs as separate service
• Dynamic scaling - PDBs can be moved between nodes
• Resource isolation - Container limits mapped to PDB resource plans
• Persistent storage - Shared storage for CDB files

Modern Deployment Patterns

Database as a Service (DBaaS)

-- Cloud service monitoring
SELECT service_name, creation_date,
       edition, version, shape
FROM cloud_service_instances;

-- Resource utilization in cloud
SELECT instance_name, cpu_count, memory_target,
       sga_target, pga_aggregate_target
FROM v$instance, v$parameter
WHERE name IN ('cpu_count', 'memory_target');

Microservices Architecture

• One PDB per microservice - Perfect isolation
• Service-specific schemas - Clear boundaries
• Independent scaling - Scale PDBs independently
• Simplified deployments - Deploy PDB changes separately

Performance Architecture

Optimizer

-- Optimizer parameters
SELECT
    name,
    value,
    description
FROM v$parameter
WHERE name LIKE '%optimizer%'
ORDER BY name;

-- SQL plan management
SELECT
    sql_handle,
    plan_name,
    enabled,
    accepted,
    fixed
FROM dba_sql_plan_baselines
ORDER BY created DESC;

Execution Plans

-- View execution plans
SELECT
    sql_id,
    plan_hash_value,
    executions,
    ROUND(elapsed_time/1000000, 2) as elapsed_sec,
    ROUND(cpu_time/1000000, 2) as cpu_sec,
    buffer_gets,
    disk_reads
FROM v$sql
WHERE executions > 100
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Migration Strategies

Upgrading to Multitenant Architecture

Converting Non-CDB to PDB

-- Check compatibility
EXEC DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  pdb_descr_file => '/tmp/ncdb.xml',
  pdb_name => 'SALES_PDB');

-- Convert non-CDB to PDB
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

-- Generate PDB description
EXEC DBMS_PDB.DESCRIBE(
  pdb_descr_file => '/tmp/sales_ncdb.xml');

-- Create CDB and plug in PDB
CREATE PLUGGABLE DATABASE sales_pdb
USING '/tmp/sales_ncdb.xml'
COPY;

Migration Best Practices

-- Pre-migration validation
SELECT * FROM PDB_PLUG_IN_VIOLATIONS
WHERE pdb_name = 'SALES_PDB'
  AND status != 'RESOLVED';

-- Post-migration cleanup
ALTER PLUGGABLE DATABASE sales_pdb OPEN;
ALTER SESSION SET CONTAINER = sales_pdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Consolidation Planning

Resource Assessment

-- Current resource usage analysis
SELECT instance_name,
       ROUND(SUM(bytes)/1024/1024/1024,2) gb_allocated,
       COUNT(*) datafiles
FROM v$datafile df, v$instance
GROUP BY instance_name;

-- Memory requirements estimation
SELECT
  'SGA_TARGET' parameter,
  ROUND(SUM(value)/1024/1024/1024,2) gb_required
FROM v$parameter
WHERE name = 'sga_target'
UNION ALL
SELECT
  'PGA_TARGET',
  ROUND(SUM(value)/1024/1024/1024,2)
FROM v$parameter
WHERE name = 'pga_aggregate_target';

Best Practices

Multitenant Architecture Design

1. CDB Planning

   • Size CDB appropriately for all planned PDBs
   • Plan for common services and schemas
   • Configure resource management at CDB level
   • Implement proper backup strategy for all containers

2. PDB Design

   • Use consistent naming conventions
   • Plan resource allocation per PDB
   • Implement PDB-specific monitoring
   • Design for easy cloning and migration

3. Security Architecture

   • Implement common users vs local users strategy
   • Use lockdown profiles for PDB restrictions
   • Configure proper privilege separation
   • Enable unified auditing across containers

Traditional Architecture Design

1. Instance Configuration

   • Size SGA appropriately for workload
   • Configure multiple redo log groups with multiple members
   • Separate data, index, and temp tablespaces
   • Use ASM for storage management when possible

2. Performance Optimization

   • Monitor wait events regularly
   • Tune SQL statements and execution plans
   • Manage statistics effectively
   • Configure memory parameters based on workload
   • Use partitioning for large tables

3. High Availability

   • Implement RAC for scalability and availability
   • Configure Data Guard for disaster recovery
   • Plan for rolling upgrades and maintenance
   • Test failover procedures regularly

Cloud and Modern Deployments

1. Container Integration

   • Design PDBs for container deployment
   • Implement proper persistent storage
   • Plan for dynamic scaling
   • Use service discovery mechanisms

2. Microservices Alignment

   • One PDB per microservice domain
   • Implement proper inter-service communication
   • Plan for independent deployments
   • Monitor resource usage per service

3. DevOps Integration

   • Automate PDB provisioning
   • Implement infrastructure as code
   • Use CI/CD for database changes
   • Monitor across all environments

Architecture Decision Framework

Choosing Architecture Type

Multitenant vs Traditional

Use Multitenant (CDB/PDB) when:
✓ Multiple applications need isolation
✓ Development/test environment proliferation
✓ Cloud or container deployment planned
✓ Need rapid provisioning/cloning
✓ Consolidation is a priority
✓ Oracle 12c+ is available

Use Traditional when:
✓ Single application environment
✓ Maximum performance is critical
✓ Simple administration preferred
✓ Older Oracle versions required
✓ Existing architecture works well

RAC vs Single Instance

Use RAC when:
✓ High availability is critical
✓ Scalability beyond single server needed
✓ Rolling maintenance windows required
✓ Load balancing across nodes needed
✓ Budget allows for additional complexity

Use Single Instance when:
✓ Cost optimization is priority
✓ Simpler administration preferred
✓ Single server can handle workload
✓ High availability via Data Guard sufficient

Future Architecture Trends

Oracle Autonomous Database

• Self-driving - Automatic performance tuning
• Self-securing - Automated security features
• Self-repairing - Automatic failure recovery
• Built on multitenant - Leverages CDB/PDB architecture

Kubernetes and Containers

• Oracle Database Operator - Kubernetes native deployment
• PDB as microservice - Perfect alignment with container philosophy
• Dynamic scaling - Automatic resource adjustment
• Service mesh integration - Advanced networking and security

Machine Learning Integration

• Automatic indexing - ML-driven index recommendations
• Automatic SQL tuning - AI-powered optimization
• Predictive scaling - ML-based capacity planning
• Anomaly detection - AI-powered monitoring

This comprehensive architecture guide provides the foundation for understanding Oracle’s evolution from traditional single-tenant databases to modern multitenant, cloud-ready architectures that support contemporary application development patterns.