Database Management Systems

During recovery, Oracle only needs to apply redo records with SCNs higher than the checkpoint SCN because all changes up to and including the checkpoint SCN are already written to data files. This is the fundamental purpose of checkpoints - they reduce recovery time by establishing a point of consistency. Option A is incomplete - a checkpoint defines the highest SCN that's been written to disk. Option B is incorrect - redo entries at or below the checkpoint are already on disk. Option D confuses LOG_CHECKPOINT_INTERVAL with time-based intervals.

When a user session terminates abnormally (power loss), Oracle automatically rolls back the uncommitted transaction when the database next starts and the rollback segments are accessed. PMON (Process Monitor) handles cleanup of failed processes. The DBA doesn't need to manually restore tables (option B), clean up partial updates (option C), or use LogMiner (option D) - Oracle's transaction integrity guarantees automatic rollback.

The statement ALTER DATABASE BACKUP CONTROLFILE TO TRACE generates a trace file in the user dump destination containing SQL commands to recreate the control file. This is useful for recovery when you've lost the control file but have the database files. The trace file contains CREATE CONTROLFILE syntax (option D), not the entire database creation (option C). It's text-based (not binary, option B) and contains SQL statements (not just a copy, option A).

In RMAN, a channel represents a stream of data to a specific device type (like disk or tape) and corresponds to one server session. Channels are allocated to perform backup and recovery operations. Option A is incorrect - multiple target databases can share one recovery catalog. Option B is incorrect - RMAN scripts are stored in the recovery catalog, not the control file. Option D incorrectly describes the RMAN client; it's the channel that establishes server sessions.

The DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES command removes the tablespace contents and deletes all associated data files from the operating system. This works regardless of whether files are Oracle Managed Files or manually managed. Options B and C incorrectly state that OMF is required. Option D incorrectly suggests that OMF-named files are excluded - they are deleted along with all other files.

When a data file is lost in NOARCHIVELOG mode, you can only restore to the point of the last backup. If you need to restore to a different disk location, use SET NEWNAME in RMAN to tell the control file the new location, then RESTORE and RECOVER. Options A and B incorrectly suggest OS commands for restore - RMAN handles this. Option C is incorrect - RMAN doesn't automatically relocate files based on utilization; you must specify the location.

RMAN's CONFIGURE ... CLEAR command resets the configuration to its default value. For retention policy, the default configuration is REDUNDANCY 1. It does not remove retention policy entirely (which would be NONE) nor does it cause DELETE OBSOLETE to fail.

When you start the listener with LSNRCTL> START without specifying a listener name, it starts the default listener named LISTENER. The output shows the listener starting but doesn't explicitly name it - you need to know the default. TNSLSNR (option A) is the listener executable name, not the listener instance name. LOCAL_LISTENER and DEFAULT_LSRN (options C and D) are not valid default names.

When restoring a control file in NOARCHIVELOG mode using RMAN, the database must be in NOMOUNT state. The NOMOUNT state allows RMAN to access the control file without opening the database fully. You cannot restore control files in OPEN, IDLE, or MOUNT states because the database needs to read the control file during startup.

The System Monitor (SMON) process performs instance recovery at database startup when the database is reopened after an abnormal shutdown or failure. PMON cleans up failed user processes, and RECO resolves in-doubt distributed transactions.

In a locally managed tablespace with UNIFORM SIZE specified, the extent size is set uniformly. The UNIFORM SIZE 1M clause means each extent allocated in this tablespace will be exactly 1 MB. This is different from segments (which are logical objects like tables or indexes), Oracle blocks (the smallest I/O unit), or operating system blocks (file system level).

An index can be rebuilt online using the ALTER INDEX ... REBUILD ONLINE statement. This allows concurrent DML operations on the underlying table during the rebuild, minimizing downtime and fragmentation. Other options incorrectly claim index unavailability or require drops.

When UNDO_MANAGEMENT is AUTO but no UNDO tablespace is specified in CREATE DATABASE, Oracle automatically creates an undo tablespace named SYS_UNDOTBS. This is default behavior to ensure automatic undo management works properly. Oracle does not create undo segments in the SYSTEM tablespace, nor does database creation fail.

V$UNDOSTAT provides statistics on undo tablespace usage including undo blocks written per second. This dynamic performance view contains one row for each 10-minute interval and shows various undo metrics including transaction activity and space consumption. V$TRANSACTION shows active transactions, V$ROLLSTAT shows rollback segment statistics, and DBA_UNDO_EXTENTS shows undo extent information but not the per-second write rate.

In a user-managed backup strategy, the database administrator uses operating system commands (like copy, cp, or tar) to copy the physical database files to a backup location after placing the tablespace in backup mode. SQL commands only manage database state.