Oracle Database Administration I Advanced Practice Exam: Hard Questions 2025

A database uses an SPFILE and was restarted after a host crash. The instance fails to mount with an error indicating that a control file cannot be identified. You discover that a parameter change was made recently with: ALTER SYSTEM SET control_files='/u01/c1.ctl','/u02/c2.ctl' SCOPE=MEMORY; The files on disk are still in the old locations and no SPFILE change was recorded. What is the most appropriate recovery action to bring the instance up and make the change persistent?

Your database is in ARCHIVELOG mode and uses a server parameter file. After adding a new redo log group, you want to ensure that on the next planned restart the instance does NOT perform a lengthy instance recovery due to an unusually high number of dirty buffers. You can afford a brief performance impact now but want to minimize recovery time. Which action best addresses this requirement?

A single-instance database occasionally hangs during peak load. The alert log shows many sessions waiting on 'library cache lock' and 'library cache pin'. Investigation reveals frequent invalidations due to DDL executed by a deployment process. You want to reduce disruption while maintaining the ability to deploy changes. Which approach is most appropriate from an instance/SGA management perspective?

A newly created service using a listener alias fails with ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. The listener is running and a 'tnsping' to the host/port succeeds. The database is open. The connect descriptor uses SERVICE_NAME=SALES. What is the most likely cause and best fix?

You must enforce that a specific application schema can create tables only in its dedicated tablespace and cannot consume space in the default USERS tablespace even if it issues CREATE TABLE without a TABLESPACE clause. The schema must still be able to create indexes. Which configuration best meets the requirement with least privilege and strongest enforcement?

A security audit finds that developers can indirectly obtain elevated privileges by enabling roles inside PL/SQL definer-rights procedures. The requirement is: prevent role-enabled privilege escalation from within definer-rights code while still allowing necessary object access. Which statement best reflects Oracle behavior and the correct mitigation?

A tablespace is created with locally managed extents and autoallocate. After a large purge, free space exists but new segment allocations are failing with ORA-01652 (unable to extend). Datafiles show plenty of total free space, but not enough contiguous free space in the specific datafile where the segment is trying to extend. What is the most appropriate administrative action to resolve this with minimal risk?

You are investigating high 'enq: TX - allocate ITL entry' waits on a hot table during batch updates. The table is in a tablespace with ASSM enabled. The application cannot be changed. Which storage-level change is the best targeted fix to reduce ITL allocation contention?

An OLTP database experiences intermittent ORA-01555 (snapshot too old) during long-running reports that run concurrently with heavy DML. UNDO tablespace is adequately sized, and undo retention is set, but the report still fails. The business requires the reports to succeed without significantly impacting OLTP response time. Which approach is most appropriate?

You are using RMAN with a recovery catalog. A datafile is lost, and you must recover it as quickly as possible. The latest backups exist, but some archived logs are missing from disk due to an OS cleanup. The database is in ARCHIVELOG mode. Which RMAN strategy best addresses restoring the missing archived logs needed for recovery while minimizing manual steps?