OCP Practice Test 20 — 15 Questions

Question 1

You manage an Oracle 23ai database backed up nightly with RMAN to a fast recovery area. Management asks you to configure RMAN so that a backup of the database, archived logs, and control file runs every night automatically and redundant backups are removed. Which RMAN feature is designed to persist this backup policy across all sessions without manually typing the commands each night?

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

Accepted Answer

Persistent RMAN configuration settings. RMAN persistent configuration settings, established with the CONFIGURE command, are recorded in the target control file and optionally the recovery catalog. They are automatically applied in every RMAN session, so retention policy, backup destination, and device type persist without manual re-entry. This makes CONFIGURE the correct mechanism for a durable nightly backup policy.

Answer

CRON job invoking a shell script with RMAN commands

Answer

A recovery catalog schema

Answer

Server-managed automatic backups via Enterprise Manager

Question 2

A telecommunications company is migrating a critical billing application to Oracle Database 23ai. The application performs complex analytical queries on a large partitioned table. The DBA wants to ensure that SQL execution plans remain stable after the migration and that any plan changes are automatically validated to prevent performance regressions. Which 23ai feature should be configured?

Accepted Answer

Real-Time SQL Plan Management. Real-Time SQL Plan Management automatically manages execution plans, ensuring that new plans are only used if they perform better. This provides plan stability and prevents regressions, which is crucial for critical applications after a migration. Other advisors offer tuning advice but do not enforce plan stability.

Answer

Automatic Indexing

Answer

SQL Tuning Advisor

Answer

SQL Access Advisor

Question 3

You are upgrading a 19c multitenant container database (CDB) to Oracle Database 23ai using AutoUpgrade. The source CDB contains three pluggable databases (PDBs). After running the upgrade, you need to verify that the upgrade was successful and that no manual intervention is required. Which two actions should you perform? (Choose two.)

Accepted Answer

Check the AutoUpgrade log files for any errors or warnings.. After an AutoUpgrade, you should check the log files for errors and query PDB_PLUG_IN_VIOLATIONS to detect any issues requiring manual intervention. These actions confirm the upgrade succeeded and highlight any remaining problems. Recompiling objects, checking plug compatibility, or running catuppst.sql are either redundant or incorrect for post-upgrade verification.

Answer

Manually run the catuppst.sql script in each PDB.

Answer

Execute the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function.

Answer

Run the utlrp.sql script to recompile invalid objects.

Question 4

You are managing a CDB named CDB1 with several PDBs. You need to create a new PDB named DEVPDB by cloning an existing PDB named PRODPDB. You want to minimize the impact on PRODPDB and ensure the clone is consistent. Which two statements are true regarding the CREATE PLUGGABLE DATABASE ... FROM PRODPDB command? (Choose two.)

Accepted Answer

The source PDB PRODPDB must be in READ ONLY mode during the cloning operation.. Cloning a PDB locally with CREATE PLUGGABLE DATABASE ... FROM requires the source PDB to be in READ ONLY mode to ensure consistency. The FILE_NAME_CONVERT clause allows relocation of the clone's datafiles. These two statements are true. The clone does not inherit open mode, does not require unplugging, and the source must not be merely mounted.

Answer

The clone DEVPDB will automatically inherit the open mode of the source PDB PRODPDB.

Answer

The clone operation requires the source PDB to be in MOUNTED state.

Answer

The source PDB PRODPDB must be unplugged before it can be cloned.

Question 5

You are using RMAN to back up your Oracle 23ai database. You want to ensure that RMAN automatically backs up the control file and server parameter file (SPFILE) whenever a backup is taken. Which RMAN configuration command should you use?

Accepted Answer

CONFIGURE CONTROLFILE AUTOBACKUP ON;. To automatically back up the control file and SPFILE with every backup, you must enable CONTROLFILE AUTOBACKUP. The other options either specify a format, perform a manual backup, or configure a snapshot control file, none of which provide automatic backup functionality.

Answer

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/snapcf.ctl';

Answer

BACKUP CURRENT CONTROLFILE;

Answer

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';

Question 6

You are managing an Oracle Database 23ai environment with Transparent Data Encryption (TDE) enabled. A security audit requires that you rotate the master encryption key for the database. You have already taken a backup of the keystore. Which command should you use to rotate the master key?

Accepted Answer

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'rotation' WITH BACKUP;. Rotating the master encryption key in 23ai requires the ADMINISTER KEY MANAGEMENT SET KEY statement. The WITH BACKUP clause ensures a backup of the keystore is taken, and the USING TAG clause optionally tags the new key. Other options are invalid because they either use incorrect syntax or refer to non-existent procedures.

Answer

ALTER DATABASE ROTATE ENCRYPTION KEY;

Answer

EXEC DBMS_CRYPTO.ROTATE_KEY;

Answer

ALTER SYSTEM SET ENCRYPTION KEY ROTATE;

Question 7

You are administering an Oracle Database 23ai environment with Unified Auditing enabled. A new security policy requires that all failed logon attempts be audited. Which action should you take to meet this requirement?

Accepted Answer

Create a unified audit policy that audits failed logons and enable it.. To audit failed logon attempts in a Unified Auditing environment, you create and enable a unified audit policy that captures CREATE SESSION with the WHENEVER NOT SUCCESSFUL clause. Legacy parameters like AUDIT_TRAIL and AUDIT_SYS_OPERATIONS do not apply to Unified Auditing and would not fulfill the requirement.

Answer

Set the AUDIT_TRAIL parameter to DB, EXTENDED.

Answer

Configure the database to use the OS audit trail and set audit_syslog_level.

Answer

Enable the AUDIT_SYS_OPERATIONS parameter.

Question 8

Your Oracle 23ai database is in NOARCHIVELOG mode. A user reports that a critical table was accidentally modified, and you want to restore the database to a point before the change. What is the primary limitation you face?

Accepted Answer

Point-in-time recovery is not possible because archived redo logs are not generated in NOARCHIVELOG mode.. NOARCHIVELOG mode means redo logs are overwritten rather than archived, so there is no way to replay changes to reach an arbitrary past point. Recovery is limited to restoring the last consistent backup, losing subsequent transactions. This is why production databases requiring point-in-time recovery must run in ARCHIVELOG mode.

Answer

The fast recovery area cannot be used in NOARCHIVELOG mode.

Answer

You cannot take any backups while the database is in NOARCHIVELOG mode.

Answer

RMAN cannot be used to back up a NOARCHIVELOG database.

Question 9

Your Oracle 23ai database is in ARCHIVELOG mode. A user accidentally dropped a table in the SALES schema. You want to recover the table using Flashback Drop. However, the recycle bin for the tablespace was purged. What is the most efficient way to recover the table using RMAN?

Accepted Answer

Perform a table-level point-in-time recovery using RECOVER TABLE.. RECOVER TABLE is the RMAN feature specifically designed to recover a table from a backup to a point in time, even if Flashback Drop is not possible. It uses an auxiliary instance to restore and recover only the necessary data files, then extracts the table. This minimizes impact on the production database and is the most efficient method.

Answer

Use Data Pump to import the table from a recent export dump.

Answer

Use Flashback Table to recover the table to before the drop.

Answer

Restore the entire database to a point in time before the drop, then export the table.

Question 10

You are administering a CDB with multiple PDBs. You need to ensure that a specific PDB named FINPDB automatically starts up when the CDB instance is started. Which action should you take?

Accepted Answer

Execute ALTER PLUGGABLE DATABASE FINPDB SAVE STATE;. To make a PDB automatically open on CDB startup, you use ALTER PLUGGABLE DATABASE ... SAVE STATE. This command persists the PDB's open mode in the CDB's metadata. When the CDB restarts, the PDB is opened to the saved state. Other methods like triggers or parameter files are not standard and can be unreliable.

Answer

Modify the PDB's initialization parameter file to include a startup command.

Answer

Create a database trigger that executes ALTER PLUGGABLE DATABASE FINPDB OPEN on startup.

Answer

Set the PDB's state to OPEN in the CDB's startup triggers.

Question 11

You are upgrading a 19c database to Oracle Database 23ai. The database uses a password-protected software keystore for TDE. During the upgrade, you need to ensure that the keystore remains accessible. Which command should you use to open the keystore automatically after the upgrade, without manual intervention?

Accepted Answer

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/path/to/keystore' IDENTIFIED BY "password";. Creating an auto-login keystore allows the database to open the keystore automatically upon startup, eliminating manual password entry. This is essential after an upgrade to ensure TDE-encrypted data is available without intervention. The other commands either open the keystore manually, rekey the master encryption key, or create a new keystore, none of which provide the required automation.

Answer

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/path/to/keystore' IDENTIFIED BY "password";

Answer

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password";

Answer

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "password" WITH BACKUP;

Question 12

Your organization is migrating a critical 19c database to Oracle Database 23ai. The business requires that the cutover downtime be minimized, ideally under 10 minutes. You have a large database (20 TB) and a high transaction rate. Which migration method should you use to achieve the least downtime?

Accepted Answer

Use Oracle GoldenGate to replicate data to the 23ai database and perform a switchover.. Oracle GoldenGate enables logical replication with near-zero downtime by keeping the target synchronized while the source remains online. At cutover, only a brief application pause is needed for the target to catch up and switch over. Data Pump, the plug-in method, and transportable tablespaces all require substantial downtime for a 20 TB database with high transactions, making them unsuitable for the 10-minute goal.

Answer

Use the plug-in method with AutoUpgrade and a full backup/restore.

Answer

Use Data Pump full export/import with parallelism.

Answer

Use transportable tablespaces to move the data files to the 23ai database.

Question 13

You are a DBA for a CDB called CDB1. You need to monitor the performance of individual PDBs to identify which PDB is consuming the most resources. Which two views should you query to get PDB-level performance metrics and resource usage? (Choose two.)

Accepted Answer

V$RSRCPDBMETRIC_HISTORY. The correct views for monitoring PDB-level resource usage are V$RSRCPDBMETRIC for real-time metrics and V$RSRCPDBMETRIC_HISTORY for historical data. These views provide CPU, I/O, and other resource statistics per PDB. The other options are non-existent views and would not return any data.

Answer

V$PDB_METRIC

Answer

V$PDB_METRIC_SUMMARY

Answer

V$PDB_METRIC_HISTORY

Question 14

You are the lead DBA for an Oracle Database 23ai production system. Users report that a critical order-entry application becomes unresponsive for several seconds every few minutes. While monitoring, you notice a significant number of 'log file sync' wait events in V$SYSTEM_EVENT. Which action is most likely to reduce this contention?

Accepted Answer

Configure the redo log files on faster storage with lower latency.. The 'log file sync' wait event is directly related to the time a user session waits for the log writer to write redo to disk during a commit. The primary factor affecting this wait is the performance of the storage where the online redo logs reside. Moving the redo logs to faster storage with lower latency reduces the write time, thereby reducing the wait. Other options, such as increasing buffer size or adding log groups, do not address the commit path latency.

Answer

Increase the number of redo log groups.

Answer

Increase the size of the redo log buffer.

Answer

Enable asynchronous I/O for the redo log files.

Question 15

You are managing an Oracle Database 23ai instance where a specific SQL statement is consuming excessive CPU. You want to identify the top SQL statements by CPU usage in real time. Which dynamic performance view should you query?

Accepted Answer

V$SQLSTATS. To identify top SQL statements by CPU usage in real time, you should query a view that aggregates CPU time per SQL statement and is optimized for quick access. V$SQLSTATS is designed for this purpose, providing cumulative CPU_TIME and other statistics for SQL statements in the shared pool. It is more efficient than V$SQLAREA and more granular than V$SYSSTAT. V$SESSION does not aggregate CPU per SQL. Therefore, V$SQLSTATS is the correct choice.

Answer

V$SQLAREA

Answer

V$SYSSTAT

Answer

V$SESSION