OCP Practice Test 15 — 15 Questions

Question 1

What is the recommended tool to apply a bundle patch to a 23ai environment?

Accepted Answer

OPatch. OPatch is the standard utility for applying patches to Oracle software.

Answer

Enterprise Manager

Answer

RMAN

Answer

AutoUpgrade

Question 2

Which TWO actions occur during a RMAN 'BACKUP AS COPY' operation?

Accepted Answer

The output file is an exact image copy of the datafile.. Option B is correct because RMAN 'BACKUP AS COPY' produces an image copy, which is a byte-for-byte duplicate of the datafile, including its block structure and headers, rather than a proprietary backup piece. Option C is correct because an image copy is a usable datafile in its own right; it can be brought online or referenced directly (for example, via SWITCH DATAFILE ... TO COPY) without first performing an RMAN RESTORE, since no conversion from backup format is needed. Option A is incorrect because compression into a single backup piece describes a backup set, not an image copy, which is a one-to-one file copy. Option D is incorrect because the RMAN proprietary backup format applies to backup sets, whereas an image copy is an exact OS-level copy of the file. Option E is incorrect because encryption is not automatic; it must be explicitly enabled via configuration or the ENCRYPTION clause.

Answer

Datafiles are compressed into a single backup piece.

Answer

The backup is stored in the RMAN proprietary backup format.

Answer

The backup is automatically encrypted.

Question 3

To improve performance, you suspect an index is missing. Which advisor should you invoke to get recommendations for new indexes?

Accepted Answer

SQL Access Advisor. The SQL Access Advisor provides recommendations on schema design, including indexes and materialized views.

Answer

SQL Tuning Advisor

Answer

Memory Advisor

Answer

ADDM

Question 4

You are tuning a query that is performing poorly due to high 'buffer busy waits'. What is the likely cause?

Accepted Answer

Multiple sessions accessing the same blocks. Buffer busy waits occur when multiple sessions try to modify the same block at the same time.

Answer

PGA memory exhausted

Answer

Library cache contention

Answer

Excessive disk I/O

Question 5

A DBA is using Oracle Database 23ai and wants to implement a schema-level privilege analysis to monitor privileges used by applications. Which two statements are true about this feature? (Choose two.)

Accepted Answer

It can be scoped to a specific schema or the entire database.. Schema-level privilege analysis can capture privileges used by users or roles, including role-granted privileges, and can be scoped to a specific schema or the entire database. It does not require ARCHIVELOG mode, does not automatically revoke privileges, and does not require an additional license.

Answer

It requires the database to be in ARCHIVELOG mode.

Answer

It requires a separate license in addition to the Enterprise Edition license.

Answer

It automatically revokes unused privileges after the analysis period.

Question 6

When configuring parallel DML, what is the effect of setting 'FORCE' on a statement?

Accepted Answer

It forces parallel execution. The FORCE keyword in a parallel DML statement (e.g., ALTER SESSION FORCE PARALLEL DML) overrides session- or object-level restrictions and forces the statement to execute in parallel even if the optimizer would otherwise choose serial execution. It is an explicit directive to the SQL engine to use parallel DML.

Answer

It disables parallel DML

Answer

It runs sequentially

Answer

It causes an error

Question 7

You are the lead DBA for a CDB called PRODCDB that contains 40 PDBs. An application team reports that connections to the PDB named SALESPDB intermittently fail with ORA-01033: ORACLE initialization or shutdown in progress, even though the CDB instance has been up for days. You query V$PDBS and notice SALESPDB shows a con_id of 5 and an open_mode of MOUNTED. Which action most directly restores application connectivity to SALESPDB?

Accepted Answer

Execute ALTER PLUGGABLE DATABASE SALESPDB OPEN; from the root container.. A PDB showing MOUNTED in V$PDBS is known to the CDB but not open for user connections, which produces initialization errors for the application. Opening the specific PDB from the root container changes its open mode to READ WRITE without disturbing the other 39 PDBs. Restarting the instance, re-registering services, or saving state do not transition the container out of MOUNTED.

Answer

Run ALTER SYSTEM REGISTER on the SALESPDB service to re-register it with the listener.

Answer

Restart the CDB instance with STARTUP FORCE to re-open all containers automatically.

Answer

Set the PDB's saved state to OPEN by running ALTER PLUGGABLE DATABASE SALESPDB SAVE STATE.

Question 8

You want to use Memoptimized Write to speed up data ingestion. Which type of table should you use?

Accepted Answer

Heap-organized table. Memoptimized Write is a feature introduced in Oracle Database 18c that accelerates high-volume INSERT operations by buffering inserts in the SGA and writing them directly to the database using a fast path, bypassing much of the standard buffer cache and redo overhead. It is only supported on heap-organized tables, since these tables have a simple rowid-based physical structure that the fast-path insert mechanism can target. Index-organized tables, external tables, and global temporary tables do not support the Memoptimized Write clause.

Answer

External table

Answer

Global temporary table

Answer

Index-organized table

Question 9

Which utility is used to perform a 'fixup' during the AutoUpgrade process?

Accepted Answer

preupgrade_fixups.sql. The AutoUpgrade utility performs pre-upgrade and post-upgrade fixups by running SQL scripts; the preupgrade_fixups.sql script is generated by the 'java -jar autoupgrade.jar -mode analyze' step and is executed during the 'fixup' phase to address issues that would block the upgrade. It is the specific artifact used to apply corrective actions before the actual upgrade proceeds.

Answer

srvctl

Answer

utlu122s.sql

Answer

catupgrd.sql

Question 10

What is the purpose of the 'Seed' PDB (PDB$SEED)?

Accepted Answer

To act as a template for new PDBs. PDB$SEED is the template for creating new PDBs.

Answer

To store undo data

Answer

To hold CDB diagnostic logs

Answer

To store temporary data

Question 11

Your company runs a CDB named HQCDB with several PDBs. A junior DBA executed ALTER PLUGGABLE DATABASE HRPDB CLOSE IMMEDIATE; a few minutes later, users of HRPDB report that connections fail with ORA-01033. You query V$PDBS and observe that HRPDB shows OPEN_MODE of MOUNTED and RESTRICTED of NO. You need HRPDB fully available for read/write traffic with no restrictions. Which command should you run from the CDB root?

Accepted Answer

ALTER PLUGGABLE DATABASE HRPDB OPEN READ WRITE;. When a PDB is closed with CLOSE IMMEDIATE, it moves to MOUNTED state and its services stop accepting normal connections, causing ORA-01033. The correct recovery is to open it read/write without the RESTRICTED clause so that all authorized users can connect. The restricted variant would still block ordinary users, the FORCE clause does not exist for this statement, and read-only mode would fail for transactional workloads that require DML.

Answer

ALTER PLUGGABLE DATABASE HRPDB OPEN FORCE;

Answer

ALTER PLUGGABLE DATABASE HRPDB OPEN READ WRITE RESTRICTED;

Answer

ALTER PLUGGABLE DATABASE HRPDB OPEN READ ONLY;

Question 12

You are administering an Oracle 23ai database in ARCHIVELOG mode. A data block in a frequently updated table is logically corrupted. You want to repair only that block while the database remains open and users continue to access other parts of the table. Which RMAN command should you use?

Accepted Answer

RECOVER DATAFILE 5 BLOCK 1234;. Block-level recovery with RECOVER DATAFILE ... BLOCK ... is designed to repair individual corrupt blocks online. It restores the block from a backup and applies archived redo to make it consistent, all while the database is open. The other commands either recover too much, revert the whole database, or only diagnose the problem. This makes block-level recovery the precise solution for the scenario.

Answer

FLASHBACK DATABASE TO BEFORE CORRUPTION;

Answer

VALIDATE DATAFILE 5;

Answer

RECOVER DATAFILE 5;

Question 13

Which RMAN command should you use to verify that your backup pieces are not corrupted?

Accepted Answer

VALIDATE BACKUPSET. VALIDATE BACKUPSET checks the integrity of the backup pieces.

Answer

LIST BACKUP

Answer

CROSSCHECK BACKUP

Answer

RESTORE DATABASE VALIDATE

Question 14

You are using RMAN to backup a database. What does the 'COMPRESS' clause in the backup command do?

Accepted Answer

It reduces the backup size.. The 'COMPRESS' clause reduces the size of the backup pieces by applying compression, saving storage space.

Answer

It validates the backup.

Answer

It encrypts the backup.

Answer

It speeds up the backup.

Question 15

What is the main requirement for an existing table to be converted into a Blockchain table?

Accepted Answer

Tables cannot be converted to Blockchain. Existing tables cannot be converted into Blockchain tables. Blockchain tables must be created as such from the start using the CREATE BLOCKCHAIN TABLE statement. There is no ALTER TABLE command to convert a regular table into a blockchain table.

Answer

The table must have an index

Answer

The table must be partitioned

Answer

The table must be empty