Database Management Systems

When restoring a control file in NOARCHIVELOG mode using RMAN, the database must be in NOMOUNT state. NOMOUNT allows instance initialization and control file access without opening the data files. OPEN state would require a valid control file already, MOUNT needs the control file to be accessible, and IDLE is not a valid Oracle state. The NOMOUNT state enables control file restoration before mounting.

Rebuilding an index online (REBUILD ONLINE) allows users to perform DML operations on the underlying table during the rebuild process, ensuring zero downtime. Dropping and recreating the index or rebuilding it offline restricts user access, which violates the requirement of continuous 24/7 availability. Freelists do not automatically resolve index fragmentation.

When UNDO_MANAGEMENT is set to AUTO and you omit the UNDO tablespace clause in CREATE DATABASE, Oracle automatically creates a default undo tablespace named SYS_UNDOTBS. This is the expected behavior in automatic undo management mode. Options A and D are incorrect because the database creation succeeds and Oracle does create an undo tablespace automatically. Option B is wrong because undo segments are not placed in the SYSTEM tablespace when using automatic undo management.

Specifying tablespace-mode export (tablespace=SAMPLE) exports all tables located in that tablespace (EMPLOYEES), their indexes regardless of tablespace (IPK_EMP), and any table partitions located in that tablespace (SALES partition P1).

The DROP TABLESPACE command drops the tablespace. In modern Oracle versions, adding the 'INCLUDING CONTENTS AND DATAFILES' clause deletes the datafiles, but the basic command to initiate this is DROP TABLESPACE.

The DROP TABLESPACE statement with INCLUDING CONTENTS AND DATAFILES removes the tablespace, all its contents (segments/data), and the associated data files from the operating system. This is the complete cleanup option. Option B and C incorrectly suggest Oracle-Managed Files (OMF) is required, but this clause works for both OMF and non-OMF data files. Option D is wrong because the AND DATAFILES clause removes all data files regardless of naming convention.

When a client machine loses power during a transaction, Oracle's PMON (Process Monitor) background process automatically detects the interrupted session and rolls back the uncommitted changes. This ensures database consistency and atomicity. Option B is incorrect because you don't need to restore - the transaction was never committed. Option C is unnecessary as PMON handles cleanup. Option D is overly complex when automatic rollback occurs.

When a data file is lost and you need to restore it to a different location, use RMAN's SET NEWNAME command to tell the control file the new location, then RESTORE and RECOVER. This is necessary because the original disk (DISK 3) is unavailable. Options A and B are incorrect because you don't use operating system commands to move data files in RMAN recovery. Option C is wrong because RMAN doesn't automatically select a new disk based on utilization - you must explicitly specify it with SET NEWNAME.

For an incomplete database recovery, all restored data files must be from backups taken prior to the target point-in-time to allow the recovery process to roll forward to the desired state.

The LOG_ARCHIVE_START parameter controls automatic archiving at instance startup. Setting it to TRUE in the initialization parameter file enables automatic archiving when the instance starts. Option B is incorrect because automatic archiving is not the default - you must explicitly enable it. Option C sets the number of archiver processes but doesn't enable automatic archiving. Option D is incorrect because LOG_ARCHIVE_START is an initialization parameter, not set dynamically with ALTER DATABASE.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE generates a trace file in the user_dump_dest directory containing SQL commands to recreate the control file. This is useful for recovery or documentation purposes. Option A is incorrect because it's not a direct text copy of the control file - it's SQL to recreate it. Option B is wrong because it's not a binary copy. Option C is incorrect because it recreates the control file, not the entire database.

An RMAN channel represents a stream of data to a device type and corresponds to one server session on the target database instance. Other options are incorrect because target databases do not require individual catalogs, and scripts cannot be stored in the control file.

For a consistent closed database backup using OS commands, use NORMAL, IMMEDIATE, or TRANSACTIONAL shutdown options. These ensure all transactions are complete and the database is cleanly closed. NORMAL waits for all users to disconnect. IMMEDIATE rolls back active transactions and disconnects users. TRANSACTIONAL waits for active transactions to finish. ABORT is unsafe for backup as it doesn't cleanly close the database - it's equivalent to pulling the plug.

To export tables owned by another user, you must have the CREATE SESSION privilege to connect to the database and the EXP_FULL_DATABASE role to export objects belonging to other schemas.

The LOG_ARCHIVE_DEST_n parameters use SYNC/ASYNC for network transmission mode (synchronous or asynchronous), LGWR/ARCH to specify which primary process sends redo (LGWR for real-time, ARCH for archiver), and AFFIRM/NOAFFIRM to control whether disk writes are synchronous (AFFIRM) or asynchronous (NOAFFIRM). These three attributes directly control data availability and protection modes in Data Guard. Option D mentions PROTECTED/UNPROTECTED which are high-level availability modes, not individual parameters.