OCP Practice Test 12 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

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 4

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 5

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

Question 6

Which RMAN command displays the database structure and associated datafiles?

Accepted Answer

REPORT SCHEMA. REPORT SCHEMA shows the layout of the database and its datafiles.

Answer

DESCRIBE DATABASE

Answer

LIST SCHEMA

Answer

SHOW SCHEMA

Question 7

Which THREE items are required for the Automatic SQL Plan Management to effectively stabilize plans?

Accepted Answer

SQL Plan Baselines. SPM requires SQL baselines, a plan history, and an evolution process.

Answer

An external monitoring server

Answer

A manual index rebuild

Question 8

Which TWO are common audit types in Oracle?

Accepted Answer

Unified Auditing. Unified Auditing (C) is correct because it is Oracle's modern audit framework, introduced in Oracle 12c, that consolidates all audit records into a single unified audit trail stored in the AUDSYS schema and managed via CREATE AUDIT POLICY and AUDIT statements. Standard Auditing (D) is correct because it is the traditional Oracle auditing mechanism (also called traditional auditing), configured with parameters such as AUDIT_TRAIL and the AUDIT statement, and it remains a recognized audit type alongside Unified Auditing. Tablespace Auditing (A) is not a valid Oracle audit type; tablespaces are storage structures, not an auditing category. Instance Auditing (B) is not a standard Oracle audit type; auditing is configured at the database level, not as a separate 'instance' audit mode. Flashback Auditing (E) does not exist in Oracle; Flashback refers to technologies such as Flashback Query, Flashback Drop, and Flashback Database, which are unrelated to auditing.

Answer

Tablespace Auditing

Answer

Instance Auditing

Answer

Flashback Auditing

Question 9

When configuring a secure listener in 23ai, which protocol is recommended for encrypted communication?

Accepted Answer

TCPS. TCPS (TCP with SSL/TLS) is the recommended protocol for a secure listener in Oracle 23ai because it encrypts all communication between clients and the database using SSL/TLS certificates. Configuring the listener with TCPS ensures that authentication and data transfer are protected against eavesdropping and man-in-the-middle attacks. While TCP is the default for non-secure connections, TCPS is specifically designed for encrypted communication and is required for features like Oracle Data Guard and many compliance standards.

Answer

TCP

Answer

IPC

Answer

HTTP

Question 10

When upgrading a database, what is a 'Flashback Database' used for?

Accepted Answer

Quick recovery after a failed upgrade. Flashback Database allows you to quickly rewind an Oracle database to a prior point in time without restoring from backup. During an upgrade, if the upgrade fails or introduces regressions, you can flash back to the guaranteed restore point created before the upgrade, returning the database to its pre-upgrade state in minutes. This is far faster than a full restore and recovery.

Answer

User authentication

Answer

Data migration

Answer

Performance tuning

Question 11

You are reviewing security best practices for 23ai. Which THREE of the following represent recommended actions to harden a production instance?

Accepted Answer

Apply the latest quarterly Release Update (RU). Option A is correct because applying the latest quarterly Release Update (RU) ensures the instance receives Oracle's most recent security patches and bug fixes, which is a foundational hardening step for any production 23ai database. Option B is correct because using Oracle Key Vault for TDE master key management centralizes and isolates encryption keys from the database server, providing stronger key custody and separation of duties than storing keys in a local wallet. Option C is correct because enforcing strong password profiles (via Oracle profiles with parameters like FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, and PASSWORD_VERIFY_FUNCTION) reduces the risk of brute-force and credential-guessing attacks against database accounts. Option D is not recommended because running in NoArchiveLog mode disables archiving and thus eliminates point-in-time recovery, increasing data-loss risk rather than hardening the instance. Option E is not recommended because disabling all default schemas immediately can break core database functionality and built-in features; default accounts should instead be locked or expired selectively after verifying they are unused.

Answer

Run database in NoArchiveLog mode for security

Answer

Disable all default schemas immediately

Question 12

Which THREE of the following are valid methods to patch an Oracle 23ai database?

Accepted Answer

OPatchAuto. OPatchAuto (A) is a valid patching method because it orchestrates the application of patches across an Oracle Grid Infrastructure/RAC environment, automatically stopping and restarting the relevant components and running OPatch and the appropriate utilities for each home. Fleet Patching and Provisioning (B), formerly Rapid Home Provisioning, is valid because it centrally manages and applies patches to Oracle homes and databases across a fleet, including rolling patching of RAC and Data Guard configurations. OPatch (C) is valid because it is Oracle's core patch utility used to apply interim patches and patch sets to an Oracle home, invoked as the underlying engine for many patching operations. Data Pump Patching (D) is not a real patching method—Data Pump (expdp/impdp) is a data movement utility, not a patch mechanism. SQL*Plus Patch (E) is also not valid, since SQL*Plus is merely a client/command-line interface for running SQL and PL/SQL and does not apply patches to an Oracle home.

Answer

Data Pump Patching

Answer

SQL*Plus Patch

Question 13

Which TWO actions are required when performing a 'Unplug and Plug' operation of a PDB between two different CDBs to ensure data integrity?

Accepted Answer

An XML metadata file must be created during the unplug process. Option B is correct because the unplug operation generates an XML metadata file (the PDB description file) that captures the PDB's datafiles, tablespaces, and metadata, which is essential for plugging the PDB into the target CDB and preserving data integrity. Option C is correct because the PDB must be closed (or placed in MOUNT state) before unplugging; you cannot unplug an open PDB, and closing it ensures a consistent state and proper generation of the XML manifest. Option A is wrong because upgrade mode is only used for running upgrade scripts, not for unplugging. Option D is wrong because the source CDB does not need to be dropped; only the PDB is unplugged. Option E is wrong because restricted mode is unrelated to unplug/plug operations.

Answer

The PDB must be opened in upgrade mode before unplugging

Answer

The source CDB must be dropped after the operation

Answer

The CDB must be in restricted mode

Question 14

Which command is used to display the current container you are connected to?

Accepted Answer

SHOW CON_NAME. SHOW CON_NAME shows the current container name in SQL*Plus.

Answer

SHOW CDB_NAME

Answer

SHOW CURRENT_PDB

Answer

SELECT CONTAINER FROM DUAL

Question 15

You have a standby database and want to offload backups to it. What is the recommended way to register the standby's backups in the recovery catalog?

Accepted Answer

Connect RMAN to the standby and the catalog.. The standby database should be connected to the recovery catalog to register its own backups.

Answer

Run the register command on the primary.

Answer

Export the primary catalog metadata to the standby.

Answer

Use the import command in RMAN.