Oracle Database Administration

The alert log (often historically referred to or associated with alert.log or an alert file) contains critical status and error information for the Oracle instance. The init.ora is for parameters, tnsnames.ora is for Net configuration, and redologs store change vectors.

The UNIFORM SIZE 1M clause sets the extent size to 1 MB uniformly across the tablespace. Each extent allocated will be exactly 1 MB, not the segment, block, or OS block. SEGMENT is the collection of extents. Oracle block is the smallest unit of I/O (typically 8KB). OS block is managed by the operating system.

V$UNDOSTAT provides undo statistics including undo blocks written, which can be used to calculate blocks per second. It tracks undo usage over time intervals. V$TRANSACTION shows current transactions but not historical rates. V$ROLLSTAT shows rollback segment statistics but not per-second rates. DBA_UNDO_EXTENTS shows extent allocation, not write rates. DBA_ROLLBACK_SEGS shows rollback segment configuration, not activity.

To restore the control file using RMAN, the target database must be in a NOMOUNT state because the control file itself is required to transition the database to the MOUNT state. OPEN and MOUNT require the control file to already be loaded.

SMON (System Monitor) performs instance recovery after a crash, including rolling forward uncommitted changes and rolling back uncommitted transactions. PMON (Process Monitor) cleans up failed processes. RECO handles distributed transaction recovery. ARCn archives redo logs. CKPT (Checkpoint) updates file headers to checkpoint position.

Adding a new master site to an Advanced Replication group without quiescing allows continuous DML operations on existing masters during the process. This is a key availability feature. Option A is incorrect - existing replication groups don't matter. Option B oversimplifies - it requires multiple setup steps. Option C is incorrect - master definition site consistency is required.

MERGE updates rows in T1 that match the join predicate from T2, and inserts rows from T2 that don't match. It's an 'upsert' operation. Option A incorrectly says 'only if it doesn't exist' - MERGE does both update and insert. Option B incorrectly describes a natural join. Option C incorrectly describes a Cartesian product.

In the Listener Control (LSNRCTL) utility environment, the SET modifier is used to dynamically modify listener parameters, such as passwords, log files, or trace levels. ALTER, CHANGE, and UPDATE are not valid LSNRCTL commands.

LGWR (Log Writer) reads redo entries from the redo log buffer and writes them to online redo log files. ARCn copies filled redo logs to archive destinations. DBWn writes dirty buffers to data files. CKPT updates file headers with checkpoint info. PMON cleans up failed processes. SMON does instance recovery and space management.

LIST EXPIRED shows files marked EXPIRED after CROSSCHECK - meaning the backup record exists but the file is missing. DELETE EXPIRED would remove these records. DELETE OBSOLETE removes backups older than retention policy. CHANGE UNCATALOG removes records from the repository entirely.

The DROP TABLESPACE command is used to remove a tablespace from the database. To automatically delete the associated data files, you must include the INCLUDING CONTENTS AND DATAFILES clause. Option A mentions a non-existent DROP DATAFILE command, while options C and D describe prerequisites for automatic file deletion in Oracle Managed Files environments.

A Primary Key constraint enforces uniqueness (no duplicate values in the specified column) and prohibits null values (acting as a NOT NULL constraint). Unique constraints allow nulls, and Check or Not Null constraints do not enforce uniqueness alone.

Oracle allows online index rebuilds using the ALTER INDEX ... REBUILD ONLINE command. This operation rebuilds the fragmented index while maintaining DML operations on the underlying table. Dropping and recreating the index (option A) would work but causes more downtime. Option E is incorrect because users can continue working during an online rebuild. The fragmentation type (option B) doesn't determine whether you can rebuild online.

If UNDO_MANAGEMENT is set to AUTO and the UNDO tablespace clause is omitted in the CREATE DATABASE statement, Oracle automatically creates a default undo tablespace named SYS_UNDOTBS. It does not fail, create no tablespace, or put undo in SYSTEM.

The correct SQL query to view the text of the procedure named SALARY_CALC, created by John, would be:

A. SELECT text FROM user_source WHERE name = 'SALARY_CALC';

This query selects the text column from the user_source table where the name is equal to 'SALARY_CALC'. This will retrieve the text of the procedure.

The other options are incorrect because:

B. SELECT * FROM user_source WHERE name='SALARY_CALC';
This query selects all columns from the user_source table where the name is equal to 'SALARY_CALC'. It will retrieve all details of the procedure, not just the text.

C. SELECT * FROM user_objects WHERE object_name='SALARY_CALC';
This query selects all columns from the user_objects table where the object_name is equal to 'SALARY_CALC'. This will retrieve details about the object, but not the text of the procedure.

D. SELECT * FROM user_procedures WHERE object_name='SALARY_CALC';
This query selects all columns from the user_procedures table where the object_name is equal to 'SALARY_CALC'. This will retrieve details about the procedure, but not the text of the procedure.

E. SELECT text FROM user_source WHERE name='SALARY_CALC' and owner='JOHN';
This query selects the text column from the user_source table where the name is equal to 'SALARY_CALC' and the owner is equal to 'JOHN'. This query assumes that John is the owner of the procedure. If John is indeed the owner, this query will work. However, if John is not the owner, it will not retrieve the text of the procedure.

So, the correct answer is option A.

When you export by tablespace using exp with tablespace=SAMPLE, Oracle exports all database segments stored in that tablespace. This includes tables, indexes, and partitions. The SAMPLE tablespace contains the EMPLOYEES table and its primary key index IPK_EMP (stored separately in INDX but associated with EMPLOYEES). Partition P1 of SALES is also in SAMPLE, so the entire SALES table definition is exported (though only P1's data is in SAMPLE).

In user-managed backups, the database administrator uses operating system commands (like cp, copy, or dd) to copy the physical database files, rather than using RMAN commands or SQL statements.