Oracle Database Architecture Flashcards

An Oracle database server consists of a database and at least one instance. What is the fundamental difference between an Oracle database instance and an Oracle database?

Answer: The instance is a set of memory structures (SGA and PGA) and background processes, while the database consists of the physical data files, control files, and redo log files on disk.

An Oracle Database instance is comprised of the System Global Area (SGA) memory structures and the background processes that manage the database. The database itself consists of the physical files stored on disk, which include data files, control files, and online redo log files. The instance exists in memory to manage and provide access to the data stored in the database files.

A database administrator is reviewing the mandatory background processes of a healthy Oracle 19c database instance. Which background process is responsible for writing modified data blocks from the database buffer cache to the data files on disk?

Answer: DBWn (Database Writer)

The Database Writer (DBWn) process is responsible for writing dirty (modified) buffers from the database buffer cache in the SGA to the physical data files on disk. LGWR writes redo information, CKPT updates file headers and signals DBWn, and SMON performs instance recovery and other system monitoring tasks.

Which of the following is considered a logical storage structure in an Oracle Database?

Answer: Tablespace

Oracle database structures are divided into logical and physical categories. A tablespace is a logical container for database objects like tables and indexes. Data files and control files are physical files visible at the operating system level. An operating system block is the smallest unit of I/O for the OS, whereas an Oracle data block is a logical structure.

A user connects to an Oracle database and executes a complex query that requires sorting a large amount of data. In which memory structure would the memory for this sort operation typically be allocated?

Answer: Program Global Area (PGA)

The Program Global Area (PGA) is a private memory region for each server and background process. It contains session-specific information and work areas for memory-intensive operations like sorting and hash joins. The other options are components of the System Global Area (SGA), which is shared among all processes.

A DBA needs to identify the names and locations of all data files and redo log files associated with the database. Which physical component of the Oracle Database architecture contains this critical structural information?

Answer: Control File

The control file is a small binary file that records the physical structure of the database. It contains essential metadata, including the database name, the names and locations of data files and online redo log files, the timestamp of database creation, and checkpoint information.

Which component of the System Global Area (SGA) is used to store the most recently used SQL and PL/SQL statements, as well as data dictionary information?

Answer: Shared Pool

The Shared Pool is a key component of the SGA that caches various types of program data. It includes the Library Cache, which stores parsed SQL and PL/SQL code, and the Data Dictionary Cache, which holds information about database objects. Caching this information improves performance by reducing the need to re-parse statements and access the disk for dictionary data.