Oracle Database Administration II Intermediate Practice Exam: Medium Difficulty 2025

Your production database experienced a media failure at 10:00 AM. The most recent full backup was taken at midnight, and archived redo logs are available from that point. However, you discover that the archived log sequence from 8:00 AM to 9:00 AM is missing due to a storage issue. What is the most recent point-in-time to which you can recover the database?

You need to configure RMAN to automatically delete obsolete backups based on a retention policy. Your requirement is to keep backups that allow recovery to any point within the last 7 days. Which RMAN configuration command should you use?

During a performance analysis, you notice that a particular SQL statement is performing poorly. The Automatic Workload Repository (AWR) report shows high buffer gets but the execution plan is not available in the AWR data. What is the most likely reason and what should you do?

You are implementing SQL Plan Management (SPM) for a critical application. After creating a SQL plan baseline, you notice that the optimizer is not using the baseline even though the SQL text matches. What are two possible reasons for this behavior?

Your database has Automatic Segment Space Management (ASSM) enabled. A batch job is inserting millions of rows into a table, and you notice significant contention on the segment header. What is the best approach to reduce this contention?

You need to implement unified auditing to track all activities performed by users with the DBA role, including SQL statements they execute. The audit records should be written to the database audit trail. Which steps are required?

A developer reports that a query runs slowly despite an index being present on the filtered column. Upon investigation, you find that the query uses a function on the indexed column in the WHERE clause (WHERE UPPER(customer_name) = 'SMITH'). What is the most appropriate solution to improve performance while maintaining data integrity?

You are implementing transparent data encryption (TDE) for tablespace encryption. The database already has a software keystore configured. What is the correct sequence of steps to encrypt an existing tablespace that contains data?

Your database is experiencing high wait events for 'enq: TX - index contention'. Investigation reveals that multiple sessions are inserting into a table with a primary key on a sequence-generated column. What is the most effective solution to reduce this contention?

You need to perform an incomplete recovery of your database because a critical table was dropped at 2:00 PM yesterday. You have RMAN backups and all archived logs. After restoring the database and mounting it, what is the correct sequence of commands to recover to 1:55 PM yesterday?