Oracle Database Architecture Flashcards

A user connects to an Oracle database and executes a large, complex SQL query that requires significant sorting and hashing operations. Where is the memory for these user-specific operations primarily allocated?

Answer: Program Global Area (PGA)

The Program Global Area (PGA) is a private memory region that contains data and control information for a server process. It is allocated when a server process starts and is used for session-specific tasks like sorting, hashing, and storing bind variable values. The other options are components of the System Global Area (SGA), which is shared among all user processes.

Which Oracle background process is responsible for writing modified data blocks (dirty buffers) from the Database Buffer Cache to the physical data files on disk?

Answer: Database Writer (DBWn)

The Database Writer (DBWn) process is the component that writes the contents of dirty buffers from the Database Buffer Cache to the data files. This is a crucial background task that makes room in the cache for new blocks. LGWR writes to redo logs, SMON performs recovery, and CKPT updates file headers with checkpoint information but signals DBWn to perform the actual writing.

A database administrator is performing a recovery operation and needs to locate all the data files and redo log files. Which physical database structure contains this critical metadata, including the database name and the timestamp of its creation?

Answer: Control Files

Control files are small binary files that record the physical structure of the database. They contain the database name, the names and locations of data files and redo log files, and the database creation timestamp. They are essential for mounting and opening the database.

Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from largest to smallest?

Answer: Tablespace > Segment > Extent > Oracle Block

The logical hierarchy in Oracle is organized as follows: A database is divided into one or more Tablespaces. Each Tablespace contains Segments (e.g., a table or an index). Each Segment is made up of one or more Extents. Each Extent is a collection of contiguous Oracle Blocks, which are the smallest unit of I/O.

Following an unexpected server shutdown, an Oracle database instance is restarted. The database automatically begins a process of rolling forward committed changes from the redo logs and rolling back uncommitted transactions. Which background process is primarily responsible for performing this crash recovery?

Answer: System Monitor (SMON)

The System Monitor (SMON) process is responsible for instance recovery, also known as crash recovery. Upon startup after a failure, SMON applies redo log entries to the data files for committed transactions and rolls back uncommitted transactions. PMON cleans up failed user processes, and ARCn handles the archiving of redo logs.

When a user issues a `COMMIT` statement for their transaction, which of the following actions is synchronously performed by the Log Writer (LGWR) process to guarantee the durability of the transaction?

Answer: It writes the transaction's redo entries from the Redo Log Buffer to the online redo log files.

A `COMMIT` is not considered complete until the redo information for the transaction is successfully written to the online redo log files on disk. The Log Writer (LGWR) process handles this critical, synchronous write operation from the in-memory Redo Log Buffer. This 'write-ahead logging' ensures that the changes can be recovered even if the data blocks haven't been written to the data files yet.