Database Management Systems

The control file is a small binary file that contains critical metadata about the database structure, including the names, locations, and status of all datafiles. Oracle reads the control file at startup to locate and open all datafiles. Redo logs contain transaction changes, parameter files have initialization settings, and password files store authentication data.

In Oracle databases, a latch is a low-level, highly efficient serialization mechanism used to protect shared data structures in the System Global Area (SGA) from simultaneous access by multiple processes.

Shared Pool

The nested loops join

The ALTER DATABASE BACKUP CONTROL FILE TO 'filename' command creates a binary copy of the control file at the specified location. This is different from the TO TRACE option which generates a SQL script. The binary backup preserves all control file contents in their native format.

When an Oracle background process encounters an exception or error, it writes diagnostic information to trace files in the user_dump_dest or background_dump_dest directory. These trace files contain error details, stack traces, and context information useful for troubleshooting database issues.

Making a complete backup immediately after opening the database ensures that all changes made during recovery (including any uncommitted transactions that were rolled back) are preserved. This provides a recoverable baseline. Options B and C only address individual files, and option D doesn't create any backup.

Oracle Enterprise Manager is the comprehensive monitoring and management tool for Oracle databases, providing performance dashboards, alerts, and diagnostic capabilities. Recovery Manager (RMAN) handles backups, Oracle Universal Installer manages software installation, and Database Configuration Assistant creates databases - none focus on performance examination.

LGWR writes redo entries to online redo log files at two key times: when a transaction commits (to ensure durability) and before DBWn writes dirty buffers to data files (to maintain recovery capability). Rollbacks do not trigger LGWR writes, and the buffer writes at 1/3 full, not 90%.

The correct RUN block allocates a DISK channel and creates image copies with LEVEL 0 specification, making them usable for incremental backups. Option A includes CHECK LOGICAL (explicitly NOT wanted), and options C, D lack the LEVEL 0 clause needed for incremental backup compatibility. The TYPE should be DISK, not sbt_tape.

A partitioned index spreads a large index across multiple tablespaces, reducing contention during index lookups and improving manageability by allowing operations on individual partitions. Bitmap indexes are for low-cardinality data, unique indexes enforce uniqueness, reverse key indexes reduce contention in sequence-based PKs, and single-column describes structure not distribution.

After adding an object to a replication group, you must generate replication support to create the necessary triggers, procedures, and packages that track changes and propagate them to other master sites. Snapshot logs are for materialized views, and the deferred transaction queue is automatically populated - manual entry is not required.

The sqlnet.ora file resides in the NETWORK/ADMIN directory under ORACLE_HOME (ORACLE_HOME/network/admin on Unix, %ORACLE_HOME%\NETWORK\ADMIN on Windows). This directory contains all SQL*Net configuration files including tnsnames.ora and listener.ora. BIN is for executables, RDBMS/ADMIN is for database scripts.

With automatic segment-space management (ASSM), free space is tracked using bitmaps stored in the segment header itself (within the extent), not in the data dictionary or control file. This eliminates contention on dictionary tables and improves space management efficiency. Undo tablespaces store rollback data, not free space maps.

When defining multiple LOG_ARCHIVE_DEST_n destinations, at least one must be a local disk destination (mandatory for the primary database). The maximum is 10 or 31 depending on Oracle version, not specifically 10. Destinations can be modified at both system and session level. Options include local disk and remote standby databases, but not arbitrary remote databases without Data Guard.