OCP Practice Test 16 — 15 Questions

Question 1

Which THREE pieces of information are needed to restore a database to a specific point-in-time using RMAN?

Accepted Answer

The DBID of the database.. Option C is correct because RMAN must identify which database to restore, and the DBID is required when using a control file backup or when the target database is not mounted, since RMAN needs it to locate the correct backup pieces and catalog entries. Option D is correct because a valid control file backup (or a recovery catalog) is needed to supply the metadata about backups, datafiles, and archived redo logs required to perform the point-in-time restore and recovery. Option E is correct because point-in-time recovery requires an explicit target, specified as a timestamp (SET UNTIL TIME) or an SCN (SET UNTIL SCN), to determine how far to apply redo and where to stop recovery. Option A is not required because RMAN authentication uses OS or password authentication for the session, but the operating system password itself is not part of the restore specification. Option B is not required because the current SPFILE is not needed to perform a point-in-time restore; RMAN can start the instance with a dummy parameter file or the existing SPFILE, and the SPFILE is not one of the three essential pieces of information for the restore operation.

Answer

The operating system password.

Answer

The current SPFILE.

Question 2

Which THREE are true regarding the use of 'FLASHBACK DATABASE'?

Accepted Answer

It can be performed on a closed database.. It requires FRA, archivelog mode, and flashback logs.

Answer

It deletes all archivelogs.

Answer

It requires the database to be in open state.

Question 3

You are the DBA for an Oracle Database 23ai instance. Users report that a particular application module is slow. You query V$ACTIVE_SESSION_HISTORY and see a high number of samples for the wait event 'log file sync'. You want to determine which sessions and SQL statements are contributing most to this wait. Which of the following would you use to find the top sessions and SQL_IDs associated with 'log file sync'?

Accepted Answer

Query V$ACTIVE_SESSION_HISTORY filtering on the event and group by session_id and sql_id.. V$ACTIVE_SESSION_HISTORY samples active sessions and records the wait event, session ID, and SQL ID for each sample. By filtering on the 'log file sync' event and aggregating by session_id and sql_id, you can identify the top contributors to this wait. This is the standard method for drilling down from a system-wide wait event to the specific sessions and SQL statements causing it.

Answer

Query V$SQL and order by ELAPSED_TIME descending.

Answer

Query V$SESSION_EVENT and order by TIME_WAITED descending.

Answer

Query V$SYSSTAT and look at the 'user commits' statistic.

Question 4

You are configuring RMAN backups for an Oracle 23ai database. You want to ensure that RMAN automatically deletes archived redo logs from the fast recovery area after they have been backed up to tape and are no longer needed for recovery. Which command should you execute?

Accepted Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';. The ARCHIVELOG DELETION POLICY determines when RMAN can delete archived redo logs from the FRA. Setting it to BACKED UP 1 TIMES TO 'SBT_TAPE' ensures that logs are only deleted after they are backed up to tape. This prevents premature deletion and frees FRA space automatically. The other options either set retention for backups, perform a one-time deletion, or optimize backups without addressing log deletion.

Answer

CONFIGURE BACKUP OPTIMIZATION ON;

Answer

BACKUP ARCHIVELOG ALL DELETE INPUT;

Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

Question 5

You are performing a cross-platform data migration using RMAN CONVERT. The destination platform has different endianness. Which file type must be converted before it can be transported?

Accepted Answer

Datafiles. When performing cross-platform transport with different endianness, datafiles must be converted using RMAN CONVERT because their internal block structure is byte-order dependent. Redo logs, control files, and parameter files are not transported across platforms in this manner; they are either recreated or are platform-independent text/binary structures that do not require endian conversion for the transport workflow.

Answer

Redo log files

Answer

Parameter files

Answer

Control files

Question 6

Which TWO components must be secured to maintain database integrity?

Accepted Answer

Data files. Data files (B) must be secured because they contain the actual database records; unauthorized modification, deletion, or corruption of these files directly destroys the accuracy and consistency of the data, which is the essence of database integrity. Backup files (C) must also be secured because they are the recovery source if data files are damaged or compromised; if backups are tampered with or corrupted, restoring the database would reintroduce bad data, so protecting them preserves integrity across recovery operations. The user manual (A) is documentation only and does not store or affect database records. System logs (D) are useful for auditing and troubleshooting but are not the database content itself, so securing them alone does not maintain data integrity. The client browser (E) is an endpoint interface, not a database component, and securing it does not protect the stored data.

Answer

User manual

Answer

System logs

Answer

Client browser

Question 7

What is the purpose of the 'Database Vault' option?

Accepted Answer

To prevent DBA access to sensitive data. Database Vault restricts access to application data by privileged users like SYS.

Answer

To improve query performance

Answer

To encrypt backups

Answer

To manage user passwords

Question 8

You are implementing AI Vector Search in Oracle Database 23ai. Which data type is mandatory for storing vector embeddings?

Accepted Answer

VECTOR. Oracle Database 23ai introduces the VECTOR data type specifically for storing vector embeddings, and it is mandatory for AI Vector Search because it defines the dimensions and numeric format needed for distance calculations. Other data types such as BLOB or CLOB can store binary or character data but do not carry the vector semantics, dimensions, or format required by vector indexes and functions.

Answer

BLOB

Answer

CLOB

Answer

JSON

Question 9

You are the lead DBA for a 23ai production database. Company policy requires that all administrative actions performed by DBAs be recorded in the Unified Audit Trail. You have already created an audit policy named DBA_ACTIONS that captures the relevant actions, but you need to ensure that it is enforced. Which action must you perform?

Accepted Answer

Enable the audit policy using the AUDIT POLICY DBA_ACTIONS statement.. The correct action is to enable the audit policy with the AUDIT POLICY statement. Creating a policy in Unified Auditing only defines it; it remains inactive until explicitly enabled. This step ensures that the specified administrative actions are captured in the Unified Audit Trail, satisfying the policy requirement.

Answer

Set the AUDIT_TRAIL initialization parameter to DB, EXTENDED.

Answer

Restart the database instance to activate the policy.

Answer

Grant the AUDIT_ADMIN role to the DBAs.

Question 10

You are implementing Transparent Data Encryption (TDE) in a 23ai database. You have configured the wallet and set the encryption key. You need to ensure that the data in the USERS tablespace is encrypted. Which statement correctly describes the process to encrypt the USERS tablespace?

Accepted Answer

You can encrypt an existing tablespace online using the ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT; command.. In Oracle Database 23ai, you can encrypt an existing tablespace without moving data by using the ALTER TABLESPACE ... ENCRYPTION ONLINE command. This command encrypts all data files in the tablespace while the tablespace remains online and accessible. The process requires the TDE wallet to be open and the tablespace to be in read-write mode. This method is efficient and minimizes downtime. The other options describe either unnecessary manual steps, nonexistent packages, or parameters that only affect new tablespaces.

Answer

You must use the DBMS_TDE package to encrypt the USERS tablespace, specifying the tablespace name and encryption algorithm.

Answer

You must create a new encrypted tablespace and move all data from USERS to the new tablespace, then drop USERS.

Answer

You must set the parameter ENCRYPT_NEW_TABLESPACES to TRUE, which will automatically encrypt all existing tablespaces upon restart.

Question 11

Which TWO components are essential for the operation of the Automatic Database Diagnostic Monitor (ADDM)?

Accepted Answer

AWR snapshots. ADDM relies on AWR snapshots (option C) because it analyzes the performance data captured in the Automatic Workload Repository between two snapshots to identify root causes and quantify their impact. It also requires an ADDM Analysis Task (option D), which is the actual diagnostic run that processes those snapshots and produces findings and recommendations. Without AWR snapshots there is no historical performance data to diagnose, and without an ADDM Analysis Task the diagnostic engine has no execution context. SQL Plan Baselines (A) are a SQL plan management feature for plan stability, not a prerequisite for ADDM. Data Pump files (B) are used for export/import operations and are unrelated to ADDM diagnostics. Listener control (E) manages network listener services and has no role in ADDM's internal analysis.

Answer

SQL Plan Baselines

Answer

Data Pump files

Answer

Listener control

Question 12

You need to migrate a non-CDB to a PDB in an existing CDB. Which package should you use to check for compatibility issues before starting the migration?

Accepted Answer

DBMS_PDB.CHECK_PLUG_COMPATIBILITY. DBMS_PDB.CHECK_PLUG_COMPATIBILITY is the standard procedure for pre-migration validation.

Answer

DBMS_PDB_MIGRATE

Answer

DBMS_METADATA.CHECK

Answer

DBMS_CDB_UPGRADE

Question 13

You are upgrading from 19c to 23ai and need to identify incompatible parameters before the upgrade. Which tool should you use?

Accepted Answer

Pre-Upgrade Information Tool. The Pre-Upgrade Information Tool (preupgrade.jar) is the standard method for identifying parameter incompatibilities.

Answer

Cloud Pre-Migration Advisor Tool

Answer

Oracle Fleet Patching and Provisioning

Answer

DBUA

Question 14

Which TWO tasks are required to successfully unplug a PDB?

Accepted Answer

Generate an XML metadata file. To unplug a PDB, you must first close the PDB with ALTER PLUGGABLE DATABASE ... CLOSE, because a PDB must be in MOUNTED (or closed) state before its data files can be described and detached — this is why option E is correct. You must then generate an XML metadata file (typically via ALTER PLUGGABLE DATABASE ... UNPLUG INTO '<file>.xml'), which captures the PDB's datafile paths and metadata needed to plug it into another CDB — this is why option D is correct. Option A (convert to non-CDB) is a separate, unrelated operation and is not part of unplugging. Option B (drop the PDB) deletes the PDB and its datafiles, which is the opposite of preserving it for transport. Option C (shut down the CDB) is unnecessary and would disrupt all other PDBs; the CDB instance remains open during an unplug.

Answer

Convert to non-CDB

Answer

Drop the PDB

Answer

Shut down the CDB

Question 15

You are configuring RMAN to store backup metadata in a Recovery Catalog. What is the benefit of using a catalog over the target database control file for long-term backup retention?

Accepted Answer

It supports storing backup metadata for longer than the control_file_record_keep_time initialization parameter.. A recovery catalog provides a persistent repository that is independent of the target database control file, allowing for retention periods longer than the control_file_record_keep_time.

Answer

It allows RMAN to perform backups while the database is in NOARCHIVELOG mode.

Answer

It automatically encrypts all RMAN backup sets.

Answer

It is required for using the RECOVERY WINDOW retention policy.