Oracle Database Architecture

Study computing architecture with memory, storage, and CPU, and see why memory's speed matters for processing. Learn how Oracle database architecture maps these components to drive efficient data work.

Explore how an Oracle database uses storage files and an instance with the SGA and background processes to process data, and how clients connect to the instance to access data.

Oracle database comprises data files, control files, and read log files; the instance uses the SGA with shared pool and buffer cache, plus background processes like log writer and checkpoint.

Explore the Oracle instance memory, including the SGA and its pools. Detail library cache, dictionary cache, buffer cache, log buffer, and large pool, Java pool, and Streams pool.

Oracle database uses data files, control files, and redo log files. Data files hold user data; undo and temp files enable rollback and sorts. Password and parameter files configure startup.

Learn how the Oracle listener listens for connection requests on port 1521, connects to the appropriate database instance, and spawns a server process to handle the session.

Discover how a select statement is parsed, hashed, and validated (syntax, semantics, privileges) in shared pool and dictionary cache, then optimized into a plan and fetched via the buffer cache.

Describe how a DML update is processed by an Oracle server, moving blocks to buffer cache, writing redo and undo, acquiring locks, and committing to redo log and data file.

Data definition language statements modify metadata by creating, altering, or dropping objects, and updating the dictionary cache in the system tablespace, performing an implicit commit as internal dml.

Understand how Oracle instance recovery works, including buffer cache, log buffer, undo, and read consistency. Explain roll forward and rollback for committed and uncommitted changes during startup after a crash.

Explain how redo log files are organized into groups with multiple members and how log buffer contents are written, switched, and archived for recovery and performance.

Explain how concurrent transactions generate interlaced log buffer entries in a multi-user Oracle database. Show how the log writer writes redo logs, and how archive logs support recovery.

Oracle database startup follows four phases: not started to normal state, mounted after reading the P file or SP file and control file, then open with data and log files.

Explore how an Oracle database shuts down, covering normal, transactional, immediate, and abort shutdowns, memory-to-file syncing, checkpointing, and startup recovery implications.

Oracle real application clusters (RAC) enable multiple instances on separate hosts to access a single database with shared storage, delivering high availability and scalable performance through clusterware and grid infrastructure.

Describe crash recovery in Oracle RAC: when an instance crashes, surviving instances identify changed blocks via redo threads, recover them, and restore full availability with no downtime.

Explore how applications connect to the database through a listener, creating server processes per user, and how connection pools and shared servers optimize resources in app servers and thick clients.

Learn how shared server configurations optimize resources by using dispatchers to manage connections and shared server processes to handle requests, with request and response queues coordinating user interactions.

Explore how a connection pool improves database performance by reusing a fixed set of connections, avoiding login and log off costs, and tuning min, max, and timeout settings.

Learn how using a connection pool versus creating new connections per operation affects Oracle database performance, with a practical demo comparing bad and good scripts.

Explore global temporary tables in Oracle, including temp undo, on commit delete rows and preserve rows behavior, and session-specific data, with a live demo showing two sessions.

Explore the physical and logical structures of an Oracle database, from data and control files to read log files and tablespaces, segments, extents, and blocks.

Explore the five types of Oracle tablespaces—system, undo, sox, temporary, and user—learning their roles, from code and data dictionary to undo history, performance data, and application data.

Understand read consistency and data concurrency in Oracle, enabling multiple readers to access the same row while a writer performs DML and locks the row.

Explore Oracle's multi-version read consistency, using undo to provide statement-level consistency for selects, ensuring data reflects the state as of the query start, supported by the undo tablespace.

System change number converts each executed statement into a monotonically increasing value, enabling commit processing, read consistency, and guiding instance recovery and rollback of uncommitted changes.

Understand read consistency in Oracle databases across transaction levels: read committed, serializable, and read-only, and use flashback query for time-specific data views.

Data blocks are the smallest IO unit, stored as Oracle blocks in data files. Standard block size sets the default cache; non-default block sizes in tablespaces require caches.

Examine the oracle data block structure, including the header, block size options, and transaction slots that govern concurrent dml, any trans and max trans, row directory, and free space management.

Learn how an Oracle row is stored in data blocks, with variable length and possible multi-piece storage, including headers, column data, and long and lob data.

Learn space management in Oracle databases, including dictionary versus locally managed table spaces, extents, high water marks, and manual or automatic segment space management, free list, and pct free.

Learn how the Oracle rowid acts as a row address, stored in indexes for fast access, and how its four components—data object ID, file, block, and row—ensure unique row location.

A user has a password and a profile, and a schema is the collection of objects the user creates, such as tables, indexes, and materialized views.

Learn how Oracle user profiles enforce password and resource limits, including failed login attempts, password lifetime, and cpu per session, connect time, and idle time.