Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) (OCP) (OCP) — Questions 1–75

The ALTER PLUGGABLE DATABASE ... SAVE STATE command saves the current open mode of the PDB so that it is automatically restored when the CDB instance restarts. This is the correct method to ensure a PDB opens automatically in a specific mode. The command stores the state in the CDB's data dictionary and applies it during instance startup.

In NOARCHIVELOG mode, Oracle does not archive redo logs, so there is no continuous record of changes to roll forward or back to an arbitrary point. You can only restore the most recent full backup, losing all changes made after it. This makes point-in-time recovery impossible, which is the fundamental limitation of running without archiving.

Applying the latest quarterly Release Update patches known vulnerabilities in the Oracle binaries, closing exploits that attackers actively target. Staying current on security patches is a foundational hardening action for production instances, satisfying the stem's requirement for recommended security actions.

The recovery catalog schema stores user-created RMAN scripts, letting you persist and reuse stored scripts across sessions. This satisfies the statement that the catalog can hold backup scripts, a capability absent from control-file-only metadata storage.

DB time aggregates the elapsed time all foreground sessions spend actively working inside the database, including CPU and non-idle wait time. A high value therefore signals that sessions are collectively spending excessive time in the database, indicating heavy workload or contention rather than merely long connection durations.

SWITCH updates the RMAN repository and control file so a datafile copy is treated as the current datafile, satisfying the need to redirect the database to a replacement file without a full restore. It changes metadata pointers only, leaving the physical copy untouched.

preupgrade.jar performs the mandatory pre-upgrade checks for 19c to 23ai, analysing the database and generating a fixup script plus a preupgrade log. It satisfies the stem's requirement to verify upgrade readiness, unlike DBUA, which performs the actual upgrade rather than the readiness assessment.

When a PDB is in MOUNTED state its data files are associated but the PDB is not open for user traffic, so applications receive initialization errors. Issuing ALTER PLUGGABLE DATABASE SALESPDB OPEN from the root transitions the PDB to READ WRITE and immediately allows sessions to connect. This is the precise, minimal command for the reported symptom.

AutoUpgrade uses a simple text-based configuration file, typically named config.cfg, where parameters are specified as key=value pairs and grouped under section headers such as [GENERAL] and [JOBS]. This format is human-readable, easy to edit, and is the documented requirement for all AutoUpgrade operations, including upgrades to Oracle Database 23ai.

Optimiser statistics describe table, column and index data distribution, feeding cardinality estimates that drive cost calculations. They satisfy the stem because inaccurate or stale statistics cause the optimiser to select a plan based on wrong row-count assumptions.

Memoptimized Write requires heap-organized tables, as it bypasses standard buffer cache processing to write directly to the Memoptimized Rowstore. An index-organized table cannot use this feature because its rows must be maintained within a B-tree structure, which is incompatible with the direct-path ingestion mechanism the stem requires.

AI Vector Search exposes vector columns and similarity operations through ordinary SQL — VECTOR data types, vector distance functions and standard DML. Developers query embeddings alongside relational data using familiar statements, avoiding a separate API or query language.

REPORT SCHEMA lists tablespaces, datafiles and their sizes, producing the database structure with associated files. It satisfies the stem's requirement to display both structure and datafiles, unlike REPORT OBSOLETE or LIST commands, which target backups or individual file details rather than the full schema layout.

Enabling SQL trace for the session captures detailed execution statistics for all SQL statements executed in that session, including the problematic one. The trace file contains the execution plan, wait events, and row source statistics. This is a classic method for in-depth diagnosis of a single statement. It can be enabled at the session or instance level, but session-level is preferred for targeted analysis.

CREATE CATALOG initialises the recovery catalog schema and its internal packages inside the nominated schema. Until this runs, the catalog contains no repository structures, so REGISTER DATABASE cannot store target metadata. It is the mandatory first step before any target registration.

Dedicated local users exist only within a single pluggable database, not in the root container, satisfying the requirement for characteristics unique to PDBs. Unlike common users, who are created in the root and shared across all containers, local users are scoped strictly to their own PDB and cannot authenticate elsewhere in the multitenant architecture.

PDB$SEED is a read-only template containing the system and sysaux datafiles that Oracle copies when you issue CREATE PLUGGABLE DATABASE. It supplies the starting file structure for every new PDB, matching the stem's template purpose.

Running `preupgrade_fixups.sql` executes the fixup stage of AutoUpgrade, automatically resolving issues the preupgrade checks flagged, such as invalid parameters or missing privileges. It satisfies the stem's requirement for the utility performing the fixup, distinct from the check script that only reports problems.

V$SESSION_WAIT exposes the current wait event, wait time and wait parameters for each active session, letting you pinpoint which sessions are stalled and on what resource right now. This directly satisfies the requirement to monitor current session performance rather than historical trends.

True Cache uses a coherency mechanism to invalidate cached blocks when the primary database modifies them. If the cache is not properly registered or the coherency messages are not received, stale data can be returned. Checking the registration and coherency status ensures that the cache receives invalidation messages and serves only current committed data.

This command transitions HRPDB from MOUNTED to READ WRITE with no restrictions, which is exactly the state needed. It is issued from the CDB root, requires no instance restart, and immediately makes the PDB's services accept ordinary user connections. This is the correct and minimal action to recover from the junior DBA's CLOSE IMMEDIATE.

Accepting a SQL Tuning Advisor recommendation applies the suggested SQL Profile, storing it in the SQL profile repository so the optimizer uses the adjusted estimates for that statement. It does not rewrite the SQL text or create indexes; it implements the profile itself.

The dictionary must be upgraded to match the engine version.

MMNL performs background tasks, including AWR snapshotting.

Flashback logs reside in the Fast Recovery Area, the configured disk location Oracle uses for recovery-related files. This placement satisfies the storage constraint, since the FRA is where the database writes flashback data used by Flashback Database operations.

Block-level recovery allows RMAN to restore and recover a single corrupt block from a backup while the database is online. It requires the datafile number and block number, which you can obtain from V$DATABASE_BLOCK_CORRUPTION. This operation is transparent to users and does not require taking the datafile offline, making it ideal for the scenario.

DBMS_BLOCKCHAIN_TABLE is the Oracle-supplied PL/SQL package that verifies blockchain table rows, recomputing and comparing row hashes to detect tampering. It provides the verification mechanism the question asks about, unlike packages that merely create or administer such tables.

DBMS_STATS.GATHER_TABLE_STATS is the standard procedure to collect fresh statistics for a table. It gathers table, column, and index statistics, which the optimizer uses to generate execution plans. In this scenario, the stale results are likely due to outdated statistics after adding a column, so refreshing them with this procedure is the correct action. It ensures the optimizer has current data distribution information.

UNPLUG writes the PDB's metadata into its own XML manifest and closes it, letting you move the data files and plug them into a different CDB via CREATE PLUGGABLE DATABASE. This satisfies the no-Data-Pump constraint by relocating files directly.

VALIDATE BACKUPSET scans each backup piece and reports logical corruption without restoring data, satisfying the requirement to verify integrity. Unlike RESTORE VALIDATE, which reads datafiles, it checks the backup set itself, confirming blocks are readable and consistent. This directly answers the need to confirm backup pieces are uncorrupted.

COMPRESS applies binary compression to backup pieces, shrinking the output written to disk or tape. It satisfies the stem's focus on backup size by reducing the number of bytes stored, though it consumes extra CPU during the operation.

Blockchain tables must be created as blockchain tables from the outset; Oracle provides no conversion path from an existing standard table. Migrating requires creating a new blockchain table and loading the data into it, so conversion is impossible.

Realms create protected zones around schemas and objects, so even SYS must pass realm authorisation before touching that data. Enabling and configuring realms therefore enforces the separation of duty the scenario demands, blocking highly privileged accounts from sensitive application tables.

An XML file holds the PDB metadata generated during unplugging, capturing the datafiles, tablespaces and options needed to plug the PDB into another CDB. This satisfies the requirement to preserve structural metadata independently of the datafiles themselves.

Oracle 23ai introduces the VECTOR data type and specialized vector indexes, enabling efficient storage and similarity searches on vector embeddings. This is purpose-built for AI and machine learning workloads, providing native support for distance calculations and approximate nearest neighbor searches, which are essential for a recommendation engine.

It manages server process overhead efficiently.

This policy instructs RMAN to delete archived redo logs only after two successful SBT backups, matching the stem's twice-to-tape requirement. The BACKED UP n TIMES TO DEVICE TYPE clause is the precise mechanism controlling automatic archivelog deletion.

RMAN must be connected to the target with the correct DBID so it can locate that database's backups and archived logs in the recovery catalog or control file. Without the DBID, point-in-time restore cannot identify the right backup set.

V$ACTIVE_SESSION_HISTORY contains sampled session activity, including the wait event, session ID, and SQL ID. By filtering for 'log file sync' and grouping by session_id and sql_id, you can identify which sessions and SQL statements are experiencing the most waits. This is the correct approach for top-down analysis.

Flashback database is performed while the database is mounted.

This command configures the archived log deletion policy so that RMAN can delete archived logs from the FRA once they have been backed up at least once to the specified device type. It ensures that logs are not deleted until they are safely stored on tape, freeing FRA space while maintaining recoverability. This directly meets the requirement.

V$FILESTAT accumulates physical read counts and read time (in centiseconds) per data file. Dividing the read time by the number of reads gives the average read latency per file. If this value is high (e.g., >20 ms), it indicates slow I/O. This directly confirms whether the storage is the bottleneck for the observed single-block reads.

True Cache holds frequently accessed read-only data close to the application tier, reducing round trips to the primary database. This caching of frequently accessed data satisfies the 3-tier scenario by offloading repetitive reads from the origin database and lowering latency.

Datafiles store blocks in the source platform's endian format, so RMAN CONVERT must translate their byte order before the destination can open them. The differing endianness named in the stem is precisely what makes conversion mandatory for these files.

True Cache is an in-memory, read-only cache that can be deployed on separate servers. It automatically caches frequently accessed data and serves consistent reads to applications, thereby offloading read traffic from the primary database. This allows real-time analytics without impacting OLTP performance.

The DBMS_BLOCKCHAIN_TABLE package provides procedures to manage and verify blockchain tables. VERIFY_ROWS checks the hash chain and row hashes to detect tampering. This is the correct way to verify the integrity of rows in a blockchain table.

Data files store the actual table and index pages, so unauthorised modification directly corrupts persisted rows and breaks integrity. Securing them with restrictive filesystem permissions prevents tampering outside the database engine, satisfying the requirement to protect stored data rather than merely access paths or credentials.

SQL Plan Management captures and stores known-good execution plans, then ensures the optimiser uses them instead of regressing to suboptimal plans. This directly addresses plan instability, the stated cause, by preventing performance fluctuations rather than merely reacting to symptoms after they appear.

USING BACKUP CONTROLFILE tells RMAN that the control file itself is being restored from backup, so it must extract the datafile and redo information from that backup rather than the current control file. It is mandatory for this recovery path.

Setting this to TYPICAL or ALL enables AWR snapshots.

Database Vault enforces separation of duties by placing sensitive schemas and data behind realms and command rules, so DBAs holding powerful privileges cannot query protected objects without explicit authorisation, directly blocking DBA access to sensitive data.

RMAN persistent configuration settings, set with CONFIGURE, are stored in the control file and optionally the recovery catalog, and they apply to every RMAN session automatically. This is RMAN's native way to persist backup retention and device policy without retyping commands, matching the requirement to store the policy across sessions.

This command creates an auto-login keystore, which allows the database to open the keystore automatically without requiring a password. This is ideal for ensuring TDE-encrypted data remains accessible after a restart, such as following an upgrade. The auto-login keystore is a separate file that stores the master key encrypted with a system-generated password.

The VECTOR data type is mandatory for storing vector embeddings in Oracle Database 23ai; it holds the dimensional float array and supports the distance functions and indexes used by AI Vector Search. No other type satisfies the stem's storage requirement.

In Oracle 23ai, creating an audit policy with CREATE AUDIT POLICY only defines it. To enforce it, you must enable it with the AUDIT POLICY statement. This action activates the policy and starts writing audit records to the Unified Audit Trail for the specified actions, thereby meeting the requirement.

Blockchain tables enforce row retention immutably; while rows remain within their retention period, the drop operation is rejected. Oracle returns an error rather than silently deleting or bypassing retention, so the command fails, preserving the tamper-resistant guarantee the table was created to provide.

A SQL profile supplies auxiliary statistics and hints that steer the optimizer toward a better plan without changing the SQL text or schema objects. SQL Tuning Advisor recommends this when plan regressions or misestimates persist, directly improving performance.

OPatchAuto orchestrates patch application across Oracle homes and Grid Infrastructure, including RAC and standalone deployments. It satisfies the stem by being a supported patching method for Oracle 23ai, automating the underlying OPatch and datapatch steps in a single invocation.

Issuing this command opens every pluggable database whose current open mode is mounted, transitioning each to read-write, provided the CDB itself is open. It satisfies the requirement to bring all PDBs online simultaneously without naming them individually, respecting each PDB's saved state and any instance-restriction settings.

Oracle Database 23ai supports online tablespace encryption. You can encrypt an existing tablespace without taking it offline by using the ALTER TABLESPACE ... ENCRYPTION ONLINE clause. This command encrypts all data files in the tablespace and updates the data dictionary. It requires the tablespace to be in a read-write state and the wallet to be open. This is the most efficient method to encrypt an existing tablespace with minimal downtime.

The RESERVABLE clause is used in the column definition to enable lock-free reservations. For example, a column defined as NUMBER RESERVABLE allows transactions to reserve amounts without locking the row. This clause is specific to Oracle Database 23ai and is required to activate the feature. Without it, the column behaves as a standard numeric column with row-level locking.

The no-drop clause sets a retention period during which the blockchain table cannot be dropped, enforcing immutability. This directly satisfies the requirement of preventing table deletion, protecting the tamper-resistant ledger from accidental or malicious removal until the specified retention window expires.

ADDM relies on AWR snapshots as its data source: each analysis run compares two snapshots to quantify instance-wide activity and identify bottlenecks. Without a snapshot pair, ADDM has no baseline or interval to diagnose, so AWR snapshot generation is essential to its operation.

DBMS_PDB.CHECK_PLUG_COMPATIBILITY validates a non-CDB's data dictionary against the target CDB before migration, reporting violations that would block plugging. It satisfies the stem's requirement to detect compatibility issues prior to starting, unlike DBMS_PDB.DESCRIBE, which only generates the XML manifest without performing any compatibility checks.

The Pre-Upgrade Information Tool directly satisfies the requirement to identify incompatible parameters before upgrading. It analyses the source database and reports deprecated and obsolete initialisation parameters, along with other pre-upgrade fix-ups, producing a detailed report that must be reviewed and resolved prior to running the upgrade itself.

V$RSRCPDBMETRIC_HISTORY provides historical resource usage metrics for PDBs, similar to V$RSRCPDBMETRIC but with a time dimension. It allows you to analyze trends and past performance. This view is crucial for historical analysis of PDB resource consumption. It is a valid view in Oracle Database 23ai and complements the real-time view for comprehensive monitoring.

SQL Plan Baselines store accepted, known-good execution plans that the optimiser is forced to reproduce. They satisfy the stabilisation requirement by preventing plan regression, since only verified plans are permitted for the captured SQL statement.

Generating an XML metadata file captures the PDB's structure and data definitions before unplugging, satisfying the requirement to preserve the pluggable database's metadata for later re-plugging into another container. Without this export, the unplugged PDB's datafiles would lack the manifest needed to register them elsewhere.

A Recovery Catalog resides in a separate schema, so its retention is independent of the target's control_file_record_keep_time parameter, which governs only control file records. This satisfies the long-term retention constraint: metadata persists indefinitely even when control file records are overwritten and aged out.

The Wallet stores encryption keys, credentials and certificates, and it is the mechanism that protects them. It supplies the master keys used for Transparent Data Encryption and other Oracle security features, keeping sensitive material separate from the data it protects.

The 'log file sync' wait event occurs when a user session must wait for the log writer (LGWR) to write redo information to disk upon a COMMIT. The primary cause of high 'log file sync' waits is slow I/O on the redo log files. Moving the redo logs to faster storage, such as solid-state drives (SSDs), or ensuring they are on dedicated, low-latency disks directly reduces the time LGWR takes to complete the write, thereby reducing the wait time for user sessions.

True Cache serves as a read-only, automatically managed cache in front of the primary database. Applications connect through the same connection string and issue identical queries; routing to cached data happens beneath the driver, so no code changes are required — the transparency developers rely on.

Traditional audit records remain in their existing SYS.AUD$ tables after enabling Unified Auditing; they are not merged into the unified audit trail. New unified records go to AUDSYS, so legacy data stays in separate tables.

JSON Duality lets developers store and query JSON documents using familiar APIs while the database presents the same data relationally, so application code needs no separate mapping layer or dual-write logic, directly reducing development effort.

Indicates another process is running.