Oracle Database Administration II Advanced Practice Exam: Hard Questions 2025

During a recovery operation, you discover that archive log sequence 1250 is missing, but sequences 1249 and 1251-1253 are available. The database was performing heavy DML operations during this period. You have a Level 0 backup from 3 days ago and incremental backups. Which statement is TRUE about your recovery options?

Your production database experiences performance degradation. AWR analysis shows high waits for 'enq: TX - row lock contention' and 'cursor: pin S wait on X'. The top SQL statements show high parse counts with very similar SQL text differing only in literal values. Which combination of actions would MOST effectively address these issues?

You are configuring Unified Auditing in your database. After enabling unified auditing and creating audit policies, you notice that some audit records are not being captured for SYS operations performed via SQL*Plus with SYSDBA privilege. The audit policy includes 'AUDIT POLICY comprehensive_policy BY SYS'. What is the MOST likely cause?

During a tablespace point-in-time recovery (TSPITR) operation for the USERS tablespace, the operation fails with ORA-01516 'nonexistent log file, data file, or temporary file'. Investigation reveals that a table in USERS tablespace has a BLOB column stored in the BLOB_DATA tablespace. What is the correct approach to successfully complete this recovery?

A critical query against a 500GB partitioned table suddenly shows severe performance degradation. Execution plans show a full table scan instead of the previous partition pruning with index access. Statistics were recently gathered. Upon investigation, you find that the predicate uses TO_DATE(date_column) in the WHERE clause, while the partition key is the DATE column itself. What is the BEST solution to restore partition pruning without application changes?

You are implementing Data Redaction for credit card numbers in a production database. The requirement is to show only the last 4 digits to application users while showing full numbers to the FINANCE role. After implementing the policy with DBMS_REDACT.FULL for all users, members of FINANCE role still see redacted data. What is the issue?

After performing an RMAN restore and recovery, you attempt to open the database and receive 'ORA-01113: file 7 needs media recovery' despite having run RECOVER DATABASE. The alert log shows that datafile 7 checkpoint SCN is higher than the database checkpoint SCN. What scenario would cause this, and what is the resolution?

Your database uses Automatic Segment Space Management (ASSM) and locally managed tablespaces. Users report intermittent performance issues. AWR shows high 'gc buffer busy acquire' and 'gc current block busy' waits in your 4-node RAC environment. Investigation reveals these waits are concentrated on specific segments with high concurrent inserts. What is the MOST effective solution?

During a security audit, you need to implement separation of duties where the security team can create and manage audit policies, but cannot view the actual audit records, while the compliance team can view audit records but cannot modify policies. The DBA team should not have access to audit data. How should you configure this using Unified Auditing?

You are managing a database with multiple large tables that are rarely updated but frequently queried. Segment space has not been reclaimed after a major data purge. You want to reclaim space and improve query performance. The tables have referential integrity constraints and are accessed 24/7. What is the BEST approach?