OCP Multiple Select — 20 Questions

Question 1

Which THREE factors influence the duration of a database upgrade?

Accepted Answer

Number of database objects. The duration of a database upgrade is directly driven by the volume of work the upgrade engine must process, so the number of database objects (A) matters because each object (tables, indexes, constraints, procedures) must be parsed, converted, and recompiled, and more objects mean more metadata operations. System resources (B), specifically CPU and I/O throughput, determine how fast those operations execute, since an upgrade is largely CPU-bound for compilation and I/O-bound for data and log writes, so faster resources shorten the elapsed time. Database size (E) is also a primary factor because larger data volumes require more time for data conversion, index rebuilds, and log generation during the upgrade. The color of the server (C) is irrelevant since physical appearance has no bearing on performance, and the number of users (D) is not a direct factor in upgrade duration because the upgrade is typically performed with the database in a restricted or single-user mode, so concurrent user count does not affect how long the upgrade itself takes.

Answer

The color of the server

Answer

Number of users

Question 2

Which THREE operations are automatically performed by RMAN when executing the 'RECOVER TABLE' command?

Accepted Answer

Exporting the recovered table to the target database using Data Pump. RECOVER TABLE is an RMAN command that performs a table-level point-in-time recovery by leveraging an auxiliary instance and a temporary auxiliary destination. Option B is correct because RMAN restores the tablespace(s) containing the target table to an auxiliary location (the auxiliary destination) rather than to the production datafiles. Option D is correct because RMAN automatically creates an auxiliary instance (spawned from the target database) to perform the recovery work. Option A is correct because, after the table is recovered in the auxiliary instance, RMAN uses Data Pump to export the table and then imports it into the target database. Option C is incorrect because RECOVER TABLE does not delete backups older than the restore point; backup retention is governed by the configured retention policy, not by this command. Option E is incorrect because RMAN never automatically converts the database to NOARCHIVELOG mode; ARCHIVELOG mode is required for this operation and is not changed by RECOVER TABLE.

Answer

Automatic deletion of all backups older than the restore point

Answer

Automatic conversion of the database to NOARCHIVELOG mode

Question 3

Which THREE types of information are contained in the PDB XML metadata file?

Accepted Answer

Datafile locations. The PDB XML metadata file (the XML file generated alongside a PDB, e.g., by the PDB metadata API or DBMS_PDB.DESCRIBE) records structural and configuration details of the pluggable database, not its business data. Option B is correct because the file lists the datafile locations (the paths of the PDB's datafiles, such as the SYSTEM, SYSAUX, and user tablespaces) so the PDB can be plugged in or cloned on another CDB. Option C is correct because the PDB GUID (the globally unique identifier assigned to the PDB) is stored in the metadata so the CDB can uniquely identify and track the plugged-in database. Option E is correct because the database character set (and national character set) of the PDB is captured in the XML, which is essential for compatibility checks when plugging the PDB into a new CDB. Option A is not part of the XML metadata — user credentials are stored in the data dictionary (e.g., SYS.USER$), not in the PDB description file. Option D is also incorrect because the XML contains only metadata about the PDB's structure and configuration, never the actual table rows, which reside in the datafiles.

Answer

User passwords

Answer

Full table data

Question 4

Which THREE types of information are captured in an AWR snapshot?

Accepted Answer

Library cache statistics. AWR captures workload, system statistics, and session activity.

Answer

Tablespace creation scripts

Answer

Database user passwords

Question 5

Which TWO methods can be used to tune SQL statements?

Accepted Answer

Manual addition of optimizer hints. Option C (Manual addition of optimizer hints) is correct because optimizer hints are directives embedded in SQL statements (e.g., /*+ INDEX(...) */ or /*+ FULL(...) */) that influence the query optimizer's execution plan, directly tuning statement performance. Option E (SQL Tuning Advisor) is correct because it is an Oracle tool that analyzes a SQL statement, its execution plan, and statistics, then produces tuning recommendations such as SQL profiles, indexes, or restructured SQL. The other options do not tune SQL statements: increasing backups (A) is a backup/recovery activity, dropping the database (B) is a destructive DDL operation, and changing user roles (D) is a security/privilege change, none of which affect query execution plans or SQL performance.

Answer

Increasing the number of backups

Answer

Dropping the database

Answer

Changing user roles

Question 6

Which THREE configuration steps are required to implement True Cache?

Accepted Answer

Update the connection pool. To implement True Cache, you must update the connection pool (A) so the application can route read-only queries to the True Cache instance via the appropriate connection descriptor, which is essential for directing traffic to the cache. You must also configure the True Cache instance (B) itself, which involves creating it with the True Cache-specific initialization parameters and associating it with the primary database so it can automatically receive and cache data. Additionally, you must enable on the application side (C), meaning the application must be configured to use the True Cache connection and set to read-only mode where appropriate, allowing it to leverage the cached data. Manual data refresh scripts (D) are not required because True Cache automatically refreshes data from the primary database, and disabling Data Guard (E) is not required since True Cache can coexist with Data Guard and does not depend on disabling it.

Answer

Manual data refresh scripts

Answer

Disable Data Guard

Question 7

Which TWO types of reports are most useful for diagnosing long-term database performance trends?

Accepted Answer

AWR Report. The AWR Report (B) is correct because Automatic Workload Repository snapshots persist performance data over long retention periods, enabling comparison of load, wait events, and top SQL across days or weeks to reveal long-term trends. The ADDM Report (E) is correct because Automatic Database Diagnostic Monitor analyzes AWR snapshots and produces findings and recommendations that track recurring performance issues over time, making it well suited for trend diagnosis. V$SESSION_WAIT (A) is a dynamic view showing only current or very recent wait activity, so it lacks historical depth. ASH Report (C) samples active sessions at fine granularity but typically covers short windows, not long-term trends. SQL Trace with tkprof (D) captures a single session's statement-level execution details for point-in-time tuning, not multi-period trend analysis.

Answer

V$SESSION_WAIT view

Answer

ASH Report

Answer

SQL Trace (tkprof)

Question 8

Which TWO methods are used to verify the success of an upgrade?

Accepted Answer

Reviewing log files. Checking the log files and querying the dictionary version are standard verification methods.

Answer

Reinstalling the OS

Answer

Running a test report

Answer

Checking user login

Question 9

Which TWO views can be used to monitor current session performance?

Accepted Answer

V$SESSION_WAIT. V$SESSION and V$SESSION_WAIT are standard tools for real-time monitoring.

Answer

V$CONTROLFILE

Answer

DBA_TABLES

Answer

DBA_USERS

Question 10

Which THREE operations are permitted on Blockchain tables?

Accepted Answer

VERIFY. Blockchain tables in Oracle Database are append-only, tamper-evident tables, so the permitted operations are INSERT (C), SELECT (E), and VERIFY (A). INSERT (C) is allowed because new rows can only be added to the end of the chain, which is how the table grows while preserving its hash-linked row history. SELECT (E) is allowed because reading and querying rows is unrestricted and does not alter the chain. VERIFY (A) is allowed because it is the special operation that checks the table's hashes and signatures to confirm no rows have been tampered with. DELETE (B) and UPDATE (D) are not permitted, since blockchain tables are append-only and rows cannot be modified or removed after insertion.

Answer

DELETE

Answer

UPDATE

Question 11

Which TWO of these are common sources of performance degradation?

Accepted Answer

High resource contention. Suboptimal SQL and system-wide resource contention are the most common issues.

Answer

Renaming a table

Answer

Changing the database name

Answer

Adding a new user

Question 12

Which THREE tasks are performed after an upgrade is finished?

Accepted Answer

Recompile invalid objects. Post-upgrade, you must run the timezone script, recompile invalid objects, and gather dictionary statistics.

Answer

Create new users

Answer

Deinstall old version

Question 13

Which THREE things can prevent an AutoUpgrade process from starting?

Accepted Answer

Existing lock files. Insufficient space, incorrect permissions, or already running processes are common blockers.

Answer

Expired passwords

Answer

Too many users

Question 14

When using the SQL Tuning Advisor to analyze a SQL statement, which THREE types of recommendations can it provide to improve performance?

Accepted Answer

Creating a SQL profile. The SQL Tuning Advisor can suggest creating new indexes, refreshing optimizer statistics, and creating SQL profiles to alter the execution plan.

Answer

Restructuring the SQL query syntax

Answer

Renaming database tables

Question 15

Which THREE components are shared between a CDB and its PDBs?

Accepted Answer

Background processes. In a multitenant architecture, the CDB and all its PDBs share a single set of background processes (C), such as DBWn, LGWR, CKPT, SMON, and PMON, because there is only one Oracle instance serving the entire container database. They also share the redo log files (D), since all changes across the CDB and its PDBs are recorded in the same online redo logs to support a single instance-level recovery stream. The SGA (E) is likewise shared, as one instance means one memory area containing the buffer cache, shared pool, and other components used by all PDBs. Undo tablespace (A) is not shared in the same way, because each PDB can have its own local undo tablespace (or use the CDB's shared undo depending on configuration), so it is not inherently a shared component. Datafiles (B) are not shared either, since each PDB has its own dedicated datafiles, while the CDB has its own separate datafiles for the root and PDB$SEED.

Answer

Undo tablespace

Answer

Datafiles

Question 16

Which THREE actions are part of the AutoUpgrade process?

Accepted Answer

Post-upgrade cleanup. AutoUpgrade is Oracle's utility for upgrading Oracle databases, and its workflow consists of distinct phases. Option E, pre-check analysis, is correct because AutoUpgrade runs a preupgrade analysis (via the 'analyze' mode, which invokes the preupgrade checks) to detect issues such as invalid objects, deprecated parameters, and missing prerequisites before any changes are made. Option B, upgrading the database, is correct because the core phase of AutoUpgrade is the 'deploy' mode, which performs the actual database upgrade (including running the upgrade scripts and converting the data dictionary) to the target release. Option A, post-upgrade cleanup, is correct because AutoUpgrade includes a post-upgrade phase (the 'deploy' mode also handles post-upgrade actions, and there is a separate 'finalize' step) that removes obsolete components, recompiles invalid objects, and completes time zone and other cleanup tasks. Option C, applying security patches, is not part of AutoUpgrade, since patching is handled by tools such as OPatch or the patch utilities, not by the upgrade process itself. Option D, rebuilding the OS, is not part of AutoUpgrade, as it operates entirely at the database level and does not touch the operating system.

Answer

Applying security patches

Answer

Rebuilding the OS

Question 17

Which THREE are characteristics of Flashback Logs?

Accepted Answer

They are stored in the Fast Recovery Area (FRA).. Option A is correct because Flashback Database logs are written to the Fast Recovery Area (FRA), configured via DB_RECOVERY_FILE_DEST, which is the required storage location for flashback logs. Option C is correct because flashback logs operate in a circular fashion: as space in the FRA is consumed, older flashback logs are automatically reused or overwritten, so the flashback retention window is bounded by available FRA space. Option E is correct because flashback logs are the core mechanism of Flashback Database, allowing the database to be reverted to a prior point in time (for example, via FLASHBACK DATABASE TO TIMESTAMP or TO SCN) without restoring backups. Option B is not correct because flashback logs are managed automatically by the database and are not backed up with RMAN; RMAN backs up datafiles, archived redo logs, and control files, not flashback logs. Option D is not correct because flashback logs store before-images of changed data blocks, not redo information; redo information is contained in the online and archived redo logs.

Answer

They are backed up using RMAN.

Answer

They contain redo information.

Question 18

Which THREE characteristics are unique to PDBs?

Accepted Answer

Dedicated local users. In a multitenant architecture, each pluggable database (PDB) can have its own dedicated local users (Option A), meaning user accounts created inside a PDB are scoped to that PDB and are not visible in other PDBs or the root, which is a defining characteristic of PDBs. Each PDB also owns its own independent datafiles (Option C), stored in its own tablespace set, so its physical storage is isolated from other PDBs even though they share the same instance. Additionally, each PDB is exposed through its own individual services (Option E), allowing clients to connect directly to a specific PDB by service name rather than to the container database as a whole. By contrast, shared redo logs (Option B) and shared background processes (Option D) are not unique to PDBs; in a multitenant container database, all PDBs share the same redo logs and the same set of background processes of the single instance, so these are characteristics of the CDB rather than distinguishing features of individual PDBs.

Answer

Shared redo logs

Answer

Shared background processes

Question 19

Which THREE factors influence the optimizer's choice of an execution plan?

Accepted Answer

Optimizer statistics. The optimizer considers statistics, system parameters, and schema objects.

Answer

The current time of day

Answer

User password strength

Question 20

Which TWO of the following statements about True Cache are correct?

Accepted Answer

It resides in the application tier. Option D is correct because Oracle True Cache is deployed as a separate, lightweight instance that runs in the application tier, close to the application servers, so it can serve queries with minimal network latency. Option E is correct because True Cache is fundamentally a read-only, in-memory cache that serves consistent read queries and does not accept DML; write operations continue to go to the primary database. Option A is incorrect because True Cache does not replace Data Guard — Data Guard remains Oracle's disaster-recovery and data-protection solution, and True Cache is complementary to it. Option B is incorrect because True Cache does not require a dedicated database; it is an additional cache instance associated with the primary database, not a separate standalone database deployment. Option C is incorrect because True Cache does not support DML operations; it only serves read-only queries, with all DML directed to the primary database.

Answer

It replaces Data Guard

Answer

It requires a dedicated database

Answer

It supports DML operations