50 Oracle Database Administration II Practice Questions: Question Bank 2025

You need to perform a complete recovery of your database after losing all datafiles due to disk failure. The control files and online redo logs are intact. Which RMAN command sequence should you use?

Which view would you query to determine the current status and progress of a long-running RMAN backup operation?

A DBA needs to configure automatic memory management for an Oracle database. The total available memory is 16 GB, and the DBA wants Oracle to automatically manage both SGA and PGA. Which parameter configuration is correct?

Which privilege is required for a user to enable Unified Auditing policies in an Oracle database?

Your database has multiple tablespaces and you need to perform an incomplete recovery to a specific point in time. After restoring from backup, which RMAN command should you use to recover the database to 2 hours ago?

A database is experiencing performance issues. After examining AWR reports, you notice that the 'db file scattered read' wait event is consuming significant time. What does this indicate and what should you investigate?

You need to implement Database Vault to separate duties in your organization. A user needs to be able to manage Database Vault policies but should not be able to access application data. Which Database Vault role should be granted?

You are managing a tablespace with automatic segment space management (ASSM). Several large batch delete operations have been performed, and you want to reclaim the unused space below the high water mark and make it available to the operating system. Which approach will accomplish this?

Your production database uses ARCHIVELOG mode with a Fast Recovery Area (FRA). The FRA is 80% full and backups are being deleted automatically. You need to implement a backup strategy that retains backups for 30 days while optimizing FRA space usage. Which RMAN configuration is most appropriate?

A critical application table has been accidentally dropped and purged from the recycle bin 4 hours ago. The database is in ARCHIVELOG mode with RMAN backups taken nightly at midnight. It is currently 2 PM. The DBA needs to recover only this table without affecting other database objects or causing downtime. Which recovery method should be used?

You need to configure unified auditing in your Oracle Database. Which statement is true about enabling unified auditing?

A DBA notices that the Automatic Workload Repository (AWR) snapshots are being purged too quickly, and historical performance data is not available for analysis. What should the DBA do to retain AWR snapshots for 60 days?

During a media recovery operation, you discover that an archived redo log file required for complete recovery is missing and cannot be restored. What is the best approach to recover the database?

You want to prevent a specific user from consuming excessive CPU resources. Which Resource Manager component should you configure to limit the CPU usage for this user's sessions?

A tablespace containing critical application data has been accidentally dropped. The database is in ARCHIVELOG mode with flashback database enabled. What is the most efficient recovery method?

Which statement accurately describes the behavior of the UNDO_RETENTION parameter in an Oracle Database with Automatic Undo Management enabled?

You are implementing fine-grained auditing (FGA) to monitor SELECT operations on the SALARY column of the EMPLOYEES table. Which package and procedure should you use to create this audit policy?

During SQL performance tuning, you identify that a SQL statement is not using an available index. You want to provide a hint to the optimizer without modifying the application code. What should you use?

You need to move a large table to a different tablespace with minimal downtime. The table is actively being accessed by users. Which method should you use?

You configured a mandatory unified audit policy that audits all connections to the database. After several months, the unified audit trail is consuming significant space. What is the recommended approach to manage this growth?

You need to configure transparent data encryption (TDE) for a tablespace in your Oracle database. Which prerequisite must be completed before you can encrypt the tablespace?

A database administrator notices that the SYSAUX tablespace is growing rapidly. Which query would help identify the top space consumers in the SYSAUX tablespace?

You want to enable automatic segment space management advisor to provide recommendations for poorly performing SQL statements. Which step must be performed first?

Your database experiences a media failure affecting datafile 5, which belongs to the USERS tablespace. The database is in ARCHIVELOG mode and you have valid RMAN backups. You want to recover the datafile while keeping the database open for other users. What is the correct recovery approach?

You are configuring unified auditing in your Oracle database. After enabling unified auditing and creating audit policies, you notice that audit records are not being generated for certain user activities. What is the most likely cause?

An application is experiencing poor performance due to high logical I/O on a particular table. SQL Monitor shows that full table scans are occurring frequently despite an index existing on the columns used in the WHERE clause. What should you investigate first?

You need to perform a point-in-time recovery (PITR) of a pluggable database (PDB) named SALESPDB to recover from a logical corruption that occurred at 10:00 AM today. The container database (CDB) and other PDBs must remain operational. What is the correct approach?

Your production database has automatic segment space management (ASSM) enabled. You notice that several large tables have significant wasted space due to deleted rows. The tables are actively used with mixed DML operations. What is the most appropriate action to reclaim space while minimizing impact on users?

You are implementing a backup strategy using RMAN and need to configure block change tracking to improve incremental backup performance. After enabling block change tracking, you notice that the change tracking file is growing larger than expected. What factors contribute to the size of the block change tracking file?

You are troubleshooting a performance issue where SQL statements are experiencing unusually high parse times. Upon investigation, you discover high contention on the shared pool latch. Multiple queries show version_count values greater than 100 in V$SQLAREA. What is the most likely root cause and solution?

You need to analyze why a particular SQL statement is performing poorly in production. The statement was executed several hours ago and you want to view the execution plan that was actually used. Which method should you use?

Your database is experiencing performance issues. You want to capture SQL statements that consume more than 10 seconds of elapsed time. Which approach should you implement?

You want to enable unified auditing in your Oracle database that currently uses traditional auditing. What is the correct sequence of steps?

A tablespace is running out of space and you need to add more storage. The tablespace uses OMF (Oracle Managed Files) and automatic extension is enabled but the datafiles have reached their MAXSIZE. What should you do?

You are performing a database recovery and need to recover a specific tablespace to a point in time different from the rest of the database. Which recovery method allows this?

Your database uses Automatic Segment Space Management (ASSM). A table has experienced numerous updates and deletes, and now queries are slower despite having appropriate indexes. What maintenance task would most likely improve performance?

You need to create a mandatory unified audit policy that captures all actions by users with DBA privileges, including both successful and failed attempts. Which statement correctly creates this policy?

During a disaster recovery drill, you discover that your RMAN backup pieces on disk have been corrupted. You have a backup catalog database, but the control file of the target database is also lost. What is the correct sequence to recover?

You are troubleshooting a performance issue where multiple sessions are waiting on 'enq: TX - row lock contention'. Upon investigation, you find that one session has been idle in transaction for several hours, holding locks. Which combination of actions should you take?

Your production database has Real-Time Query enabled on the physical standby. Users report that queries on the standby return data that is 30 minutes behind the primary, despite network connectivity being normal. What is the most likely cause and solution?

A database administrator needs to configure the Fast Recovery Area (FRA) for a production database. Which two types of files can be automatically managed by the FRA? (Choose the best answer that identifies the primary automated files)

You are troubleshooting a performance issue where users report slow query responses during peak hours. After examining AWR reports, you notice high values for 'db file scattered read' wait events. What is the most likely cause of this issue?

An organization wants to implement fine-grained auditing to track access to sensitive salary data in the EMPLOYEES table, but only when the SALARY column exceeds $100,000. Which Oracle feature should be used?

During a database maintenance window, you need to shrink a tablespace to reclaim unused space. You attempt to shrink the tablespace but receive an error. What is the most likely reason for this failure?

A critical production database experienced a media failure affecting a data file in the USERS tablespace at 2:00 PM. The database is running in ARCHIVELOG mode with daily full backups at midnight. What is the correct sequence of RMAN commands to recover this data file while keeping the database available for other users?

You notice that the Automatic Workload Repository (AWR) snapshots are consuming excessive space in the SYSAUX tablespace. Currently, snapshots are taken every 30 minutes and retained for 60 days. What is the best approach to reduce space consumption while maintaining useful performance data?

Your organization requires that all privileged user activities be audited in a tamper-proof manner with centralized management. Which Oracle Database auditing architecture should you implement?

You are managing space in a tablespace that uses Automatic Segment Space Management (ASSM). Several large batch delete operations have been performed, leaving tables with significant unused space below the high water mark. Which statement is true about reclaiming this space?

A database is running in NOARCHIVELOG mode and experiences a disk failure affecting a data file at 10:00 AM. A full database backup was taken at 2:00 AM the same day. Several critical transactions were processed between 2:00 AM and 10:00 AM. What is the recovery outcome?

You are analyzing SQL performance and notice that a particular query shows a very high number of 'latch: cache buffers chains' wait events. The query accesses a frequently updated table with high concurrency. What is the most effective solution to reduce these wait events?