Oracle Database Administration Quiz

The alert log file (alert.log or alert_.log) is the primary source for Oracle instance status information. It contains database startup/shutdown messages, error messages, checkpoint information, and critical events. The init.ora file contains initialization parameters, tnsnames.ora holds network connection details, and redo log files store transaction records - none provide instance status.

The System Monitor (SMON) background process is responsible for performing instance recovery when a crashed database is reopened. It applies redo log entries (roll forward) and discards uncommitted changes (roll back). PMON cleans up failed user processes, RECO resolves distributed transactions, and CKPT updates file headers during checkpoints.

In a locally managed tablespace with UNIFORM SIZE specification, all extents are allocated at the uniform size specified (1 MB in this case). The extent is the basic unit of allocation - not segments, Oracle blocks, or OS blocks. Each extent will be exactly 1 MB regardless of the segment size or Oracle block size. This simplifies space management and reduces fragmentation.

A Primary Key constraint enforces both uniqueness and non-null requirements simultaneously. It uniquely identifies each row (no duplicates) and implicitly prevents NULL values. A Unique constraint allows one NULL value, Not Null doesn't prevent duplicates, Check validates expressions, and Foreign Key references other tables. Only Primary Key combines both rules.

A checkpoint records the highest SCN written to data files. During recovery, only redo records with SCNs higher than the checkpoint SCN need to be applied - changes below that point are already persisted. This dramatically reduces recovery time. Option A is wrong because checkpoint doesn't define SCN, B is wrong because redo above checkpoint is NOT written to data files yet, and D refers to a different parameter.

The V$UNDOSTAT dynamic performance view contains statistics for monitoring the effects of undo space management, including the number of undo blocks consumed (UNDOBLKS) in 10-minute intervals. This data allows administrators to calculate the rate of undo blocks written per second. Other views do not provide this specific time-series undo block data.

The Log Writer (LGWR) background process reads redo entries from the redo log buffer in memory and writes them to online redo log files on disk. This is critical for durability - committed transactions must be written to redo logs before acknowledgment. ARCn archives redo logs, DBWn writes dirty buffers, CKPT updates file headers, and PMON/SMON handle process and transaction recovery.

In user-managed backups, the database administrator manually copies the database files using operating system copy utilities (such as cp or copy) after putting the tablespaces into backup mode. SQL or SQL*Plus commands are used to manage the database state, but they do not perform the actual file copying.

The CONFIGURE ... CLEAR command in RMAN resets the specified configuration parameter back to its default value. For the retention policy, the default setting is REDUNDANCY 1. It does not remove the retention policy entirely, nor does it cause the DELETE OBSOLETE command to fail.

Running the START command in the LSNRCTL utility without specifying a listener name starts the default listener, which is named LISTENER. TNSLSNR is the executable program name, not the listener name, while LOCAL_LISTENER is an initialization parameter, and DEFAULT_LSRN is not a standard default name.