OCP Database Architecture & Administration 2 — Questions and Answers
Question 1: Which Oracle background process is responsible for writing dirty buffers from the buffer cache to datafiles?
- LGWR
- DBWR (Correct answer)
- CKPT
- SMON
Correct answer: DBWR
DBWR (Database Writer) writes modified (dirty) buffers from the buffer cache to the datafiles on disk.
Question 2: In Oracle's multi-tier memory architecture, which component stores the execution plans for recently parsed SQL statements?
- Database buffer cache
- Redo log buffer
- Library cache (Correct answer)
- Java pool
Correct answer: Library cache
The library cache, which is part of the shared pool in the SGA, stores parsed SQL statements and their execution plans.
Question 3: What is the purpose of the CKPT background process in Oracle?
- Writes redo entries to online redo log files
- Updates datafile headers and control files with checkpoint information (Correct answer)
- Performs crash recovery at instance startup
- Monitors and cleans up dead transactions
Correct answer: Updates datafile headers and control files with checkpoint information
CKPT updates the control file and datafile headers with checkpoint information, signaling that a checkpoint has occurred.
Question 4: Which Oracle data dictionary view shows the current size and usage of all tablespaces?
- DBA_SEGMENTS
- DBA_TABLESPACES (Correct answer)
- DBA_FREE_SPACE
- V$TABLESPACE
Correct answer: DBA_TABLESPACES
DBA_TABLESPACES provides information about all tablespaces including their status, block size, and extent management type.
Question 5: When a user issues a COMMIT, which Oracle process ensures the redo data is written to disk before the commit completes?
- DBWR
- ARCH
- LGWR (Correct answer)
- CKPT
Correct answer: LGWR
LGWR (Log Writer) writes redo log buffer contents to the online redo log files at commit time, ensuring durability.
Question 6: Which type of Oracle file stores the physical structure information of the database including the locations of datafiles and redo log files?
- Parameter file (SPFILE)
- Control file (Correct answer)
- Password file
- Alert log
Correct answer: Control file
The control file records the physical structure of the database, including names and locations of datafiles and redo log files.
Question 7: In Oracle, what is the effect of setting the AUTOEXTEND ON clause when creating a datafile?
- The datafile is automatically backed up when full
- The datafile grows automatically when space runs out (Correct answer)
- The tablespace switches to a new datafile when the current one is full
- The datafile is striped across multiple disks automatically
Correct answer: The datafile grows automatically when space runs out
AUTOEXTEND ON allows Oracle to automatically increase the datafile size when the tablespace runs out of free space, up to the specified MAXSIZE.
OCP Database Architecture & Administration 2
Which Oracle background process is responsible for writing dirty buffers from the buffer cache to datafiles?