OCP Practice Test 17 — 15 Questions

Question 1

You are the lead DBA for a busy online transaction processing (OLTP) database. Users report that a critical order-entry application has become slow. You run a query against V$SYSTEM_EVENT and notice that the 'log file sync' wait event has the highest total time waited. Which action should you take to address this issue?

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

Accepted Answer

Tune the redo log file I/O by moving them to faster storage.. The 'log file sync' wait event indicates that user sessions are waiting for the log writer (LGWR) to write redo entries to disk during commit operations. This wait is predominantly caused by slow I/O on the redo log files. To resolve this, the DBA should focus on improving the performance of the storage where the redo logs reside, such as by moving them to faster disks or SSDs. Increasing the redo log buffer size or adjusting DBWn processes does not directly address the I/O bottleneck that LGWR faces.

Answer

Increase the size of the redo log buffer.

Answer

Reduce the number of commits performed by the application.

Answer

Increase the number of database writer (DBWn) processes.

Question 2

What is the purpose of the 'Wallet' in Oracle security?

Accepted Answer

To store encryption keys and credentials. In Oracle security, a wallet is a password-protected container that stores encryption keys, credentials, and certificates. It is used by TDE to store the master encryption key and by clients to store credentials for secure connections. The wallet protects these secrets with a password or auto-login mechanism.

Answer

To backup the database

Answer

To manage database users

Answer

To improve query performance

Question 3

What happens to a traditional audit record when you migrate to Unified Auditing in 23ai?

Accepted Answer

Stay in separate tables. Traditional audit records are not automatically migrated; you should clear them or archive them before switching to Unified Auditing.

Answer

Automatically converted

Answer

Deleted automatically

Answer

Merged into the Unified Trail

Question 4

Which view should you query to verify the encryption status of tablespaces in 23ai?

Accepted Answer

V$ENCRYPTED_TABLESPACES. V$ENCRYPTED_TABLESPACES shows the encryption status of all tablespaces in the database.

Answer

DBA_ENCRYPTED_COLUMNS

Answer

DBA_ENCRYPTION_KEYS

Answer

DBA_TABLESPACES

Answer

V$WALLET

Question 5

You are implementing Transparent Data Encryption (TDE) in Oracle Database 23ai. You have already configured the keystore and created the master encryption key. Now you need to encrypt several existing tablespaces and columns. Which two actions must you perform to ensure that the data is encrypted and accessible? (Choose two.)

Accepted Answer

Use the ALTER TABLE ... MODIFY (column ENCRYPT) command to encrypt existing columns.. To encrypt existing data with TDE, you must explicitly encrypt tablespaces and columns. The ALTER TABLESPACE ... ENCRYPTION ONLINE ENCRYPT command encrypts existing tablespaces, while ALTER TABLE ... MODIFY (column ENCRYPT) encrypts existing columns. These actions ensure that the data is encrypted using the TDE master key. Other commands like setting the encryption key or opening the database do not perform the encryption of existing objects; they are part of key management or database startup.

Answer

Use the ALTER DATABASE OPEN command with the ENCRYPTION option to enable encryption for the entire database.

Answer

Use the ALTER SYSTEM SET ENCRYPTION KEY command to generate a new master key.

Answer

Use the DBMS_TDE package to encrypt all schemas in the database.

Question 6

In a multitenant environment, what is the best approach to backup only a specific PDB?

Accepted Answer

BACKUP PLUGGABLE DATABASE pdb_name. The correct RMAN syntax to back up a single pluggable database from the root container is BACKUP PLUGGABLE DATABASE pdb_name. This command must be executed while connected to the root (CDB$ROOT) and targets only the specified PDB's datafiles, control file, and archived logs as applicable. It is the documented, supported way to isolate a PDB backup in a multitenant architecture.

Answer

BACKUP CDB PDB pdb_name

Answer

BACKUP DATABASE PDBNAME

Answer

BACKUP PDB ALL

Question 7

You are the DBA for a CDB named CORPCDB that has several PDBs. You need to shut down the entire CDB instance cleanly, including all PDBs, as part of a planned maintenance window. Which two statements about the shutdown process are correct? (Choose two.)

Accepted Answer

A PDB can be shut down individually with ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE while the CDB instance remains running.. Shutting down the CDB from the root with SHUTDOWN IMMEDIATE closes every PDB and stops the instance, so no per-PDB action is needed. When only one container must be taken offline, ALTER PLUGGABLE DATABASE ... CLOSE IMMEDIATE closes that PDB while the CDB and other PDBs continue running. The remaining statements misdescribe transactional shutdown, abort requirements, or the fate of mounted PDBs.

Answer

You must connect to each PDB individually and issue SHUTDOWN ABORT before shutting down the CDB root.

Answer

SHUTDOWN TRANSACTIONAL from the root container waits for all PDB transactions to complete but leaves PDBs open for new connections until the end.

Answer

After SHUTDOWN IMMEDIATE from the root, any PDB that was in MOUNTED state remains mounted and does not need to be closed separately.

Question 8

You have a high volume of parse time in your database. What could be the cause?

Accepted Answer

Using literals instead of bind variables. Hard parsing occurs when SQL is not shared, often due to literals instead of bind variables.

Answer

High memory usage

Answer

Too many indexes

Answer

Missing statistics

Question 9

You are upgrading an Oracle Database 19c non-CDB to Oracle Database 23ai. The database uses a custom application that relies on the DBMS_LOB package and has a large amount of PL/SQL code. You have chosen the AutoUpgrade utility to perform the upgrade. During the upgrade, you encounter an error related to the DBMS_LOB package. Which action should you take to resolve the issue?

Accepted Answer

Check the AutoUpgrade log files for detailed error messages and apply the recommended fix, such as running the preupgrade script or applying a patch.. The AutoUpgrade utility provides comprehensive logging that pinpoints the cause of failures. When an error related to a built-in package like DBMS_LOB occurs, it often indicates a missing patch or an environment issue that the preupgrade script can identify. The correct action is to review the logs and follow the tool's recommendations, which may include applying a patch or running the fixup script. This ensures the upgrade proceeds smoothly without unnecessary rollbacks.

Answer

Recompile all invalid PL/SQL objects manually using utlrp.sql after the upgrade.

Answer

Restart the upgrade from the beginning after restoring the database from a backup.

Answer

Manually grant EXECUTE privilege on DBMS_LOB to the application schema and retry the upgrade.

Question 10

You are the lead DBA for a CDB called PRODCDB that contains 30 PDBs. A developer reports that the application connecting to the PDB named SALESPDB receives ORA-01033 (Oracle initialization or shutdown in progress) when the database is otherwise available. You connect to the CDB root and query V$PDBS and see that SALESPDB shows OPEN_MODE of MOUNTED while all other PDBs show READ WRITE. Which command should you execute to make SALESPDB available to the application with the least administrative effort?

Accepted Answer

ALTER PLUGGABLE DATABASE SALESPDB OPEN;. A PDB left in MOUNTED state has its datafiles associated with the instance but its dictionary and services are not available to user sessions, producing ORA-01033. Opening the PDB from the CDB root with ALTER PLUGGABLE DATABASE ... OPEN transitions it to READ WRITE and restores service without affecting other PDBs. The read-only variant would not satisfy a transactional application, no ALTER SYSTEM STARTUP syntax exists, and a full instance restart is unnecessary and disruptive.

Answer

ALTER PLUGGABLE DATABASE SALESPDB OPEN READ ONLY;

Answer

SHUTDOWN IMMEDIATE; STARTUP;

Answer

ALTER SYSTEM STARTUP PLUGGABLE DATABASE SALESPDB;

Question 11

You need to ensure that the PDB name is unique in your network. Which parameter should you check?

Accepted Answer

DB_UNIQUE_NAME. DB_UNIQUE_NAME must be unique across the CDB infrastructure.

Answer

INSTANCE_NAME

Answer

DB_NAME

Answer

PDB_NAME

Question 12

You need to perform an incomplete recovery of your Oracle 23ai database to a specific SCN. The database is in ARCHIVELOG mode. Which RMAN command sequence correctly performs this recovery?

Accepted Answer

STARTUP MOUNT; RESTORE DATABASE; RECOVER DATABASE UNTIL SCN 123456; ALTER DATABASE OPEN RESETLOGS;. Incomplete recovery to a specific SCN requires the database to be mounted, data files restored from backup, recovery up to the target SCN, and then opened with RESETLOGS. The RESETLOGS operation is mandatory after any incomplete recovery to reset the online redo logs and create a new database incarnation. Omitting RESTORE or using NOMOUNT would fail, and opening without RESETLOGS is not permitted.

Answer

STARTUP NOMOUNT; RESTORE DATABASE; RECOVER DATABASE UNTIL SCN 123456; ALTER DATABASE OPEN RESETLOGS;

Answer

STARTUP MOUNT; RECOVER DATABASE UNTIL SCN 123456; ALTER DATABASE OPEN;

Answer

STARTUP MOUNT; RESTORE DATABASE; RECOVER DATABASE UNTIL SCN 123456; ALTER DATABASE OPEN;

Question 13

Which TWO features of 23ai help improve performance of high-concurrency workloads?

Accepted Answer

Lock-Free Reservations. Lock-Free Reservations (B) is correct because it lets concurrent transactions reserve a column value (e.g., inventory quantity) without holding a row lock, eliminating lock contention and enabling high-concurrency OLTP throughput. Memoptimized Write (E) is correct because it uses the Memoptimized Rowstore fast ingest path to buffer inserts in memory and write them to disk asynchronously, dramatically improving insert performance under many concurrent sessions. Wide tables (A) merely allow up to 1,000 columns and do not by themselves improve concurrency. Blockchain tables (C) add tamper-evident row chaining for auditing, which adds overhead rather than boosting concurrency. JSON Duality (D) provides dual relational/JSON access to the same data for developer flexibility, not a high-concurrency performance feature.

Answer

Wide tables

Answer

Blockchain tables

Answer

JSON Duality

Question 14

What happens if a block is marked as corrupt during an RMAN backup?

Accepted Answer

The information is recorded in the V$DATABASE_BLOCK_CORRUPTION view.. RMAN writes the corruption information to V$DATABASE_BLOCK_CORRUPTION but continues the backup unless the number of errors exceeds MAXCORRUPT.

Answer

The backup terminates immediately.

Answer

The backup is marked as 'failed' in the recovery catalog.

Answer

The corrupt blocks are repaired automatically.

Question 15

Your CDB named FINCDB has several PDBs. A developer reports that they cannot create a local user in the PDB named REPORTSPDB because the PDB is in RESTRICTED mode. You connect to the root as SYSDBA and want to allow normal user connections to REPORTSPDB while keeping other PDBs unchanged. Which command should you execute?

Accepted Answer

ALTER PLUGGABLE DATABASE REPORTSPDB OPEN READ WRITE;. RESTRICTED mode on a PDB permits only users holding the RESTRICTED SESSION privilege to connect. To restore normal access, the PDB must be reopened in READ WRITE mode without the RESTRICTED clause. Executing ALTER PLUGGABLE DATABASE REPORTSPDB OPEN READ WRITE from the root applies the change only to REPORTSPDB, so the developer can connect and create a local user while other PDBs in the CDB remain unaffected.

Answer

ALTER PLUGGABLE DATABASE REPORTSPDB CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE REPORTSPDB OPEN;

Answer

ALTER PLUGGABLE DATABASE REPORTSPDB OPEN READ WRITE RESTRICTED;

Answer

ALTER SYSTEM ENABLE RESTRICTED SESSION;