OCP Practice Test 5 — 25 Questions

Question 1

What is the primary purpose of the Flashback Database feature?

Accepted Answer

To revert the entire database to a point in time. Flashback Database allows you to return the entire database to a previous point in time without restoring from a backup.

Answer

To restore individual corrupted datafiles

Answer

To recover dropped tables

Answer

To undo a specific transaction

Question 2

You are analyzing a performance issue in your Oracle 23ai database. You suspect that a specific SQL statement is performing poorly due to a suboptimal execution plan. You want to capture the actual execution plan and runtime statistics for this statement while it is executing. Which tool should you use to achieve this with minimal overhead?

Accepted Answer

Use the DBMS_XPLAN.DISPLAY_CURSOR function with the 'ALLSTATS LAST' format.. To capture the actual execution plan and runtime statistics for a specific SQL statement with minimal overhead, the DBMS_XPLAN.DISPLAY_CURSOR function with the 'ALLSTATS LAST' format is the most efficient method. It retrieves the plan and statistics from the library cache for the last execution of the cursor, leveraging data that the database already collects. This avoids the overhead of enabling SQL trace or re-executing the statement with EXPLAIN PLAN, and it provides the actual rows and other runtime metrics needed for tuning.

Answer

Enable SQL trace for the session and use TKPROF.

Answer

EXPLAIN PLAN FOR statement;

Answer

Query the V$SQL_PLAN_STATISTICS_ALL view for the SQL ID.

Question 3

Which THREE of the following are components of the Oracle performance tuning lifecycle?

Accepted Answer

Implementing a solution. Tuning is an iterative process involving identification, analysis, and implementation.

Answer

Deleting all users

Answer

Reinstalling the database

Question 4

An Oracle Database 23ai administrator supports an application that frequently runs ad hoc queries against a large JSON collection stored in an Oracle JSON column. Developers complain that every query must be rewritten to match the document structure, and they want the relational and JSON views of the same data to stay in sync without duplicating storage. The DBA enables JSON Relational Duality Views. Which statement correctly describes how the application benefits from this feature?

Accepted Answer

The duality view exposes relational tables as JSON documents that can be queried and updated with standard JSON and SQL, without duplicating data.. JSON Relational Duality Views let applications interact with relational data as JSON documents while the database continues to store the data relationally. The view is a definition, not a copy, so there is no duplicated storage and no synchronization job to manage. Reads and writes through the view are translated into operations on the base tables, keeping relational and JSON access consistent for ad hoc query workloads.

Answer

The duality view stores a second physical copy of the JSON documents, which the database keeps synchronized automatically.

Answer

The duality view can only be queried, not updated, so developers must use stored procedures for any data changes.

Answer

The duality view requires the base tables to be converted to a document-only storage model before it can be used.

Question 5

You are administering a CDB with several PDBs. You need to ensure that a specific PDB named HRPDB is automatically started and opened in READ WRITE mode whenever the CDB instance starts. Which action should you take?

Accepted Answer

Set the PDB's open mode in the CDB's startup configuration by executing ALTER PLUGGABLE DATABASE HRPDB SAVE STATE.. The ALTER PLUGGABLE DATABASE ... SAVE STATE command records the current open mode of a PDB in the CDB's data dictionary. When the CDB instance restarts, the PDB is automatically opened in the saved mode. This is the standard and recommended way to ensure a PDB opens automatically in READ WRITE mode.

Answer

Modify the PDB's initialization parameter file to include OPEN_MODE=READ WRITE.

Answer

Set the PDB's state to OPEN in the CDB's spfile using ALTER SYSTEM SET PDB_OPEN_MODE='READ WRITE'.

Answer

Create a database trigger that fires on startup and opens the PDB.

Question 6

You need to migrate a non-CDB database to a PDB in a 23ai CDB. Which utility is the recommended tool to assess compatibility before migration?

Accepted Answer

DBMS_PDB.CHECK_PLUG_COMPATIBILITY. DBMS_PDB.CHECK_PLUG_COMPATIBILITY is the standard PL/SQL procedure to identify potential issues before plugging a non-CDB into a CDB.

Answer

RMAN CONVERT

Answer

DBUA

Answer

SQL Developer

Answer

Data Pump

Question 7

Your team maintains a large Oracle Database 23ai data warehouse. A new reporting application requires the ability to store and query JSON documents alongside relational data without duplicating the data or writing complex transformation logic. The application will issue both standard SQL queries and Oracle Database API for MongoDB (Oracle API for MongoDB) commands against the same underlying data. Which 23ai feature should you implement to satisfy this requirement?

Accepted Answer

JSON Relational Duality Views. JSON Relational Duality Views let you define a JSON document view over relational tables, so the same data can be queried with SQL and manipulated as JSON documents via Oracle API for MongoDB. This eliminates data duplication and transformation code, exactly matching the need for dual access in the data warehouse.

Answer

Blockchain Tables

Answer

Wide Tables

Answer

Memoptimized Rowstore

Question 8

Which database feature is recommended for high-frequency inserts into a single table with minimal overhead?

Accepted Answer

Memoptimized Write. Memoptimized Write is an Oracle feature designed specifically for high-frequency, low-overhead inserts into a single table. It uses a memory buffer and a lightweight commit path to reduce redo and undo overhead, making it ideal for IoT, telemetry, and logging workloads where rows are appended rapidly. It is the recommended feature for this scenario in 23ai.

Answer

Materialized Views

Answer

Flashback

Answer

Partitioning

Question 9

You are tuning a data warehouse query that performs a large hash join. The query is spilling to disk, and you want to determine if increasing the PGA_AGGREGATE_TARGET would help. Which of the following dynamic performance views would you query to see the current PGA memory usage and the number of multi-pass executions?

Accepted Answer

V$PGASTAT. V$PGASTAT is the primary view for monitoring PGA memory allocation and work area execution efficiency. It includes statistics such as 'aggregate PGA target parameter', 'total PGA allocated', 'cache hit percentage', and counts of optimal, one-pass, and multi-pass executions. By checking the number of multi-pass executions, you can determine if increasing PGA_AGGREGATE_TARGET would reduce disk spills and improve performance.

Answer

V$PROCESS

Answer

V$SQL_WORKAREA

Answer

V$SESSTAT

Question 10

You are analyzing a performance degradation in a data warehouse. The AWR report shows a high number of 'cell single block physical read' waits on Exadata. You suspect that the storage index is not being used effectively for a particular query. Which action should you take to improve the effectiveness of the storage index for this query?

Accepted Answer

Ensure that the query predicates are not using functions on the columns.. The storage index relies on predicates that reference columns directly, without functions or expressions, to filter out storage regions. If a predicate uses a function on a column, the storage index cannot be applied, leading to unnecessary physical reads. Ensuring predicates are on bare columns allows the storage index to function optimally and reduce I/O.

Answer

Enable the 'cell_offload_processing' parameter.

Answer

Recreate the table with a larger block size.

Answer

Increase the size of the Exadata Smart Flash Cache.

Question 11

You need to back up only the archived redo log files that have not yet been backed up, and then delete the input files after the backup completes. Which RMAN command accomplishes this?

Accepted Answer

BACKUP ARCHIVELOG NOT BACKED UP DELETE INPUT;. The requirement is to back up only archived redo log files that have not yet been backed up. RMAN provides the NOT BACKED UP clause specifically for this purpose. Combining it with DELETE INPUT removes the input files after a successful backup. The other options either back up all archived logs or use a time-based filter, which does not guarantee that only unbacked-up logs are processed.

Answer

BACKUP ARCHIVELOG ALL DELETE INPUT;

Answer

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

Answer

BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' DELETE INPUT;

Question 12

You are diagnosing a performance issue in an Oracle Database 23ai instance. You suspect that the issue is related to the optimizer's cardinality estimates. You want to gather additional information to confirm your suspicion. Which two of the following would provide you with the optimizer's estimated cardinality and the actual cardinality for each step of an execution plan? (Choose two.)

Accepted Answer

Using the /*+ GATHER_PLAN_STATISTICS */ hint and then querying V$SQL_PLAN_STATISTICS_ALL.. To compare estimated and actual cardinality, you need a method that captures runtime execution statistics. The GATHER_PLAN_STATISTICS hint causes the optimizer to collect actual row counts during execution, which are then visible in V$SQL_PLAN_STATISTICS_ALL. Similarly, DBMS_XPLAN.DISPLAY_CURSOR with 'ALLSTATS LAST' shows the plan with both estimated (E-Rows) and actual (A-Rows) cardinalities for the last execution. Both approaches are valid and commonly used for this purpose.

Answer

Querying V$SQLAREA and looking at the OPTIMIZER_COST column.

Answer

Querying V$SQL_PLAN for the SQL_ID and examining the CARDINALITY column.

Answer

Using the EXPLAIN PLAN FOR command and then querying PLAN_TABLE for the CARDINALITY column.

Question 13

Your CDB is configured with a PDB named REPORTSPDB that must be opened automatically in READ ONLY mode every time the CDB instance starts, because it hosts static reporting data. Which two-step approach ensures this behavior across instance restarts? (Choose the best answer.)

Accepted Answer

Open REPORTSPDB in READ ONLY mode, then run ALTER PLUGGABLE DATABASE REPORTSPDB SAVE STATE.. To have a PDB reopen in a specific mode after instance restart, open it in that mode and then save its state. ALTER PLUGGABLE DATABASE ... SAVE STATE records the open mode and makes the CDB restore it automatically at startup. Startup triggers and nonexistent parameters or direct view updates are not the supported approach.

Answer

Set the PDB's OPEN_MODE column in PDB_SAVED_STATE by running an UPDATE statement.

Answer

Modify the CDB's SPFILE parameter PDB_OPEN_MODE to READ ONLY for REPORTSPDB.

Answer

Create a database startup trigger in the root that executes ALTER PLUGGABLE DATABASE REPORTSPDB OPEN READ ONLY.

Question 14

You are configuring RMAN to use a media manager for tape backups. Which component must be installed and configured on the database server to enable RMAN to communicate with the media manager?

Accepted Answer

Media Management Library (MML) provided by the media manager vendor. To use RMAN with a media manager for tape backups, the media manager vendor must provide a Media Management Library (MML) that implements the Oracle SBT (System Backup to Tape) API. This library is installed on the database server and referenced by Oracle. Without it, RMAN cannot send commands to the tape subsystem. Other components like OSB, Data Guard broker, or a recovery catalog are not required for this specific integration.

Answer

Oracle Secure Backup (OSB) client

Answer

Oracle Recovery Manager (RMAN) catalog database

Answer

Oracle Data Guard broker

Question 15

What is the primary role of True Cache in a 3-tier architecture?

Accepted Answer

Caching frequently accessed data. It acts as a caching layer between the application and the database server.

Answer

Database backup

Answer

Load balancing

Answer

User authentication

Question 16

You are preparing to upgrade a 19c database to 23ai using the AutoUpgrade utility. You have created the configuration file and run the upgrade in analyze mode. The analysis completes successfully. What is the next step you should perform according to best practices?

Accepted Answer

Review the analysis report generated by AutoUpgrade to identify any issues that need to be addressed before upgrading.. The AutoUpgrade utility's analyze mode produces a comprehensive report of the database's readiness for upgrade. This report includes checks for deprecated parameters, invalid objects, time zone versions, and other prerequisites. The correct next step is to review this report carefully to identify any issues that could hinder the upgrade. Based on the findings, you can then take corrective actions, such as running fixup mode for automatic fixes or manually resolving problems. Only after ensuring the database is ready should you proceed to deploy mode to perform the actual upgrade.

Answer

Run the upgrade in analyze mode again with a different configuration to compare results.

Answer

Run the upgrade in deploy mode to perform the actual upgrade.

Answer

Run the upgrade in fixup mode to have AutoUpgrade automatically resolve any issues identified during analysis.

Question 17

If you are in ARCHIVELOG mode, what is the primary benefit of having a multiplexed control file?

Accepted Answer

Redundancy against disk failure.. In ARCHIVELOG mode, a multiplexed control file provides redundancy against disk failure by maintaining multiple copies of the control file on different disks. If one copy becomes corrupted or a disk fails, the database can use another copy to continue operations, ensuring high availability and preventing downtime.

Answer

Faster backup performance.

Answer

Ability to perform point-in-time recovery.

Answer

Automatic database tuning.

Question 18

In Oracle 23ai, you want to use Database Vault to prevent highly privileged users (like SYS) from accessing sensitive application data. Which component must be enabled and configured to achieve this separation of duty?

Accepted Answer

Database Vault Realms. Database Vault Realms are the specific Database Vault component designed to create a protective boundary around application schemas and data, blocking even highly privileged users like SYS or SYSDBA from accessing them unless explicitly authorized. A realm defines the objects to protect and the authorized users/roles that may access them, thereby enforcing separation of duty between DBAs and application data owners. This is the core mechanism in Oracle 23ai Database Vault for preventing privileged user access to sensitive application data.

Answer

Virtual Private Database (VPD)

Answer

Data Redaction

Answer

Label Security

Question 19

Which command is used to delete backups that are no longer needed based on the current retention policy?

Accepted Answer

DELETE OBSOLETE. DELETE OBSOLETE is the command to purge backups exceeding the retention policy.

Answer

DROP BACKUP

Answer

PURGE BACKUPS

Answer

DELETE BACKUP

Question 20

You are restoring a database and need to use a backup control file. Which clause is mandatory in the recover command?

Accepted Answer

USING BACKUP CONTROLFILE. The 'USING BACKUP CONTROLFILE' clause is required when you are not using the current control file.

Answer

WITH EXTERNAL CONTROLFILE

Answer

RECOVER CONTROLFILE

Answer

USING OLD CONTROLFILE