Oracle Database Administration

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.

After CROSSCHECK BACKUP, use LIST EXPIRED to see which backup files are missing. CROSSCHECK marks files as EXPIRED if they cannot be found on disk. LIST EXPIRED shows these expired backups without deleting them. DELETE EXPIRED would remove the records, DELETE OBSOLETE removes backups no longer needed per retention policy, and CHANGE UNCATALOG removes files from the repository entirely.

In the Listener Control (LSNRCTL) utility, the SET modifier is used to dynamically change parameter values (such as LOG_STATUS, PASSWORD, or TRACE_LEVEL) during runtime. ALTER, CHANGE, and UPDATE are not valid LSNRCTL commands for setting parameters.

The Log Writer (LGWR) background process is responsible for writing the contents of the redo log buffer in the SGA to the active online redo log files. DBWn writes data blocks, CKPT updates headers/controlfiles, and ARCn copies filled logs.

Adding a master site to a replication group without quiescing allows administrators to dynamically expand replication environments. This procedure is designed to run transparently, ensuring that transactions and DML operations on replicated tables continue without interruption or locking out active users.

A checkpoint records the SCN up to which all changes have been written to data files. During recovery, only redo records with SCNs higher than the checkpoint SCN need to be applied. This is because all changes at or below the checkpoint are already safely stored in the data files. Checkpoints do not define the highest SCN, they don't guarantee redo entries at the SCN are written, and LOG_CHECKPOINT_INTERVAL specifies redo blocks, not time.

MERGE performs an upsert operation: for each row from the source (T2), it updates the target row if it exists in T1 based on the join condition, or inserts the row if it doesn't exist. MERGE is not a join type - it's a data manipulation command. It doesn't create natural joins or Cartesian products. The ON clause specifies the matching condition between source and target tables.

The DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement drops the tablespace from the data dictionary, deletes all contents, and deletes the associated operating system datafiles, regardless of whether OMF is used. Hence, option 554180 is correct and others are false restrictions.

In NOARCHIVELOG mode with a damaged SYSTEM data file, use SET NEWNAME in RMAN to redirect the restore to a new location, then RESTORE and RECOVER. SET NEWNAME updates the control file with the new data file location. OS-level restore commands alone won't work because RMAN needs to know the new location. RMAN doesn't automatically choose disk locations based on utilization - you must specify them.

CONFIGURE RETENTION POLICY CLEAR resets the retention policy to the default, which is REDUNDANCY 1 (keeps one copy of each backup). It doesn't remove retention policies or make backups never expire. The DELETE OBSOLETE command will continue to work normally after clearing. CLEAR restores the Oracle default, not a no-policy state.

If no specific listener name is provided to LSNRCTL, it starts the listener named LISTENER by default. TNSLSNR is the executable name, and LOCAL_LISTENER is an initialization parameter, not the default listener name started by default configuration.

Incomplete database recovery requires ALL data files to be from a consistent point in time - a backup taken prior to the target recovery time. If some files are from newer backups, the database won't be consistent at the recovery point. Oracle applies redo logs to bring all files forward to the same point-in-time, but they must all start from before that point.

To enable automatic archiving at instance startup in older Oracle releases (e.g. Oracle 9i and earlier), LOG_ARCHIVE_START must be set to TRUE in the initialization parameter file. Option 554209 is incorrect because ARCHIVELOG mode does not automatically start archiving unless configured. Other options do not control startup archiving behavior.

The statement writes a text file to the database's trace directory containing the SQL statement (CREATE CONTROLFILE) needed to recreate the control file. It does not produce a copy of the database schema, nor does it create a binary or exact text copy of the control file itself.

In Oracle RMAN, a channel represents a stream of data to a specified device (disk or tape) and corresponds to a single server session on the target database. Target databases share a recovery catalog, RMAN scripts are not stored in control files, and the RMAN executable compiles commands before execution.

When a user session terminates abnormally (power loss), Oracle's PMON (Process Monitor) background process automatically detects the dead session and rolls back the uncommitted transaction. The database remains consistent - no manual intervention is needed. Options B, C, and D suggest unnecessary manual actions that PMON already handles automatically.

A Primary Key constraint enforces both uniqueness (no duplicate values in a column or set of columns) and the not-null constraint (columns in a primary key cannot contain nulls). A Unique constraint allows nulls, Not Null does not enforce uniqueness, and Check and Foreign Key constraints enforce different rules.