Oracle Database Administration II Practice Exam 2025: Latest Questions

You need to perform a complete database recovery after a media failure that destroyed all datafiles. You have RMAN backups and archived redo logs available. Which sequence of RMAN commands should you use?

A DBA notices that a particular SQL statement is consuming excessive CPU resources. Which Oracle feature should be used to automatically prevent this SQL statement from consuming more than a specified amount of CPU time?

You want to enable fine-grained auditing to track all SELECT statements on the EMPLOYEES table where the SALARY column value exceeds 100000. Which Oracle feature should you use?

After performing routine maintenance, you need to reorganize a large table to reclaim wasted space and improve performance. The table must remain available for DML operations during this process. Which method should you use?

Your database is in ARCHIVELOG mode and you need to perform a point-in-time recovery (PITR) to restore the database to its state at 2 PM yesterday. A user accidentally dropped an important table at 3 PM. What is the consequence of this recovery?

You are analyzing performance issues and notice high wait events for 'db file scattered read'. Which type of operation is most likely causing this wait event, and what tuning approach should you consider?

Your organization requires that all users connecting to the database must be authenticated using their operating system credentials, and no passwords should be stored in the database. Which authentication method should you implement?

You need to move a large 500 GB tablespace from one storage system to another with minimal downtime. The database is in ARCHIVELOG mode and must remain open and available during the migration. Which approach provides the least downtime?

Your RMAN backup strategy includes incremental backups. After taking a level 0 incremental backup on Sunday, you take level 1 incremental backups daily. On Friday, the database crashes and all datafiles are lost. During recovery, RMAN reports that block 12450 in datafile 5 has corruption that existed before the Friday backup. What RMAN feature can you use to restore this specific block without restoring the entire datafile?

A critical production query has suddenly started performing poorly. The execution plan shows that the optimizer is choosing a full table scan instead of an index that was previously used. The table statistics were updated yesterday. You need to force the optimizer to use the previous execution plan without modifying the SQL code. What is the best approach?

A DBA needs to configure Real-Time SQL Monitoring to automatically capture execution plans for all SQL statements that run longer than 10 seconds. Which parameter should be set to accomplish this?

Your database experienced a media failure and you need to restore a tablespace. The database is running in ARCHIVELOG mode. Which statement about performing a tablespace point-in-time recovery (TSPITR) is correct?

A security audit requires you to track all SELECT statements executed against the EMPLOYEES table by users with the HR_MANAGER role. Which auditing approach provides the most efficient solution with minimal performance overhead?

Your production database is experiencing performance degradation. After investigation, you discover that several critical SQL statements are using suboptimal execution plans. You need to quickly force Oracle to use better execution plans without modifying application code. What is the most appropriate solution?

A DBA needs to perform an incomplete recovery of the database to a specific SCN due to a logical corruption. The database is in ARCHIVELOG mode. After restoring all datafiles from backup and mounting the database, which RMAN command sequence should be used?

You need to implement Transparent Data Encryption (TDE) for columns containing sensitive data in an existing production database. The database does not currently use TDE. What is the correct sequence of steps to enable TDE column encryption?

Your database is experiencing high wait events for 'db file sequential read'. After analysis, you identify that several large tables are experiencing excessive I/O due to full table scans. The tables are frequently queried but rarely updated. What combination of solutions would best address this performance issue?

A database administrator needs to implement an automatic segment space management strategy for a tablespace containing tables that experience frequent inserts and deletes. Some segments have grown excessively due to deleted data. What is the most effective approach to reclaim space and maintain optimal space usage?

Your organization requires that all privileged user activities be audited, including SYS operations, with the audit records stored securely outside the database. The audit data must be tamper-proof and queryable. Which configuration best meets these requirements?

A production database experienced a failure and you need to perform recovery. You discover that archived log sequence 5234 is missing and cannot be restored. The most recent backup was taken 3 days ago at SCN 4500000. The missing archive log covers SCN range 4750000-4780000. Current SCN is 5100000. What is the best recovery approach to minimize data loss?

You need to configure auditing to track all failed login attempts to your Oracle database. Which auditing statement should you execute?

A DBA notices that a tablespace is experiencing high fragmentation with many small free extents scattered throughout. The tablespace uses Automatic Segment Space Management (ASSM) and has AUTOEXTEND enabled. Which approach is most appropriate to defragment the tablespace?

Your database is configured with a flash recovery area. During a routine check, you discover that the flash recovery area is 95% full and backups are failing. You have verified that the retention policy is set correctly. What is the most likely cause and recommended solution?

You are troubleshooting poor performance of a critical SQL statement. An execution plan shows that a full table scan is being performed on a large table despite an appropriate index existing on the WHERE clause column. The statistics are current. Which diagnostic step should you perform FIRST to identify why the index is not being used?

Your production database is running in ARCHIVELOG mode with a recovery window retention policy of 14 days. You need to perform a tablespace point-in-time recovery (TSPITR) for the USERS tablespace to a point 10 days ago due to a logical corruption. The tablespace contains tables with foreign key relationships to tables in the SALES tablespace. What is a critical requirement for successfully performing TSPITR in this scenario?