314 questions total · 5pages · All types, answers revealed
Which component of the Oracle Database uses the Automatic Diagnostic Repository (ADR) to store critical errors and performance incidents?
ADR is the repository that stores diagnostic data.
Why this answer
ADR is the centralized location for all diagnostic data, including alert logs and incident dumps.
Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?
AWR reports contain 'Top SQL' sections categorized by various metrics.
Why this answer
The AWR report has a section specifically for Top SQL by Elapsed Time.
When performing a 'Refreshable PDB Switchover', what is the state of the source PDB?
The source becomes a refreshable PDB and stays in READ ONLY mode.
Why this answer
During a switchover, the source PDB is converted to a refreshable PDB.
You need to perform a Table Point-in-Time Recovery (TPITR) to restore a table to a state before a mistaken DROP TABLE operation. Which RMAN command performs this operation while minimizing impact to other tablespaces?
RECOVER TABLE is the correct RMAN command for performing TPITR on a single table.
Why this answer
The RECOVER TABLE command is specifically designed for table-level recovery, which creates an auxiliary instance automatically to extract the table data.
An administrator has been tasked with reducing the overhead of AWR snapshot collection. What is the most effective way to modify the snapshot interval?
This is the correct administrative interface for AWR configuration.
Why this answer
The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure is the official method to adjust snapshot frequency and retention policies.
Which THREE types of information are contained in the PDB XML metadata file?
Paths for the PDB files.
Why this answer
It contains file paths, GUIDs, and character set information.
In 23ai, when using the 'JSON relational duality' feature, how are changes to the JSON document propagated to the underlying tables?
This is the core value proposition of Duality Views.
Why this answer
Updates made via JSON Duality Views are transparently propagated to the underlying relational tables using the view's defined mapping.
How can you view the recommendations made by the Automatic Database Diagnostic Monitor (ADDM)?
This view contains the output findings for all advisors, including ADDM.
Why this answer
ADDM recommendations can be viewed in AWR or via the DBA_ADVISOR_FINDINGS view.
You want to enforce password complexity in 23ai using a profile. Which clause is used to reference a custom verification function?
This enables custom complexity rules.
Why this answer
The PASSWORD_VERIFY_FUNCTION clause in the CREATE PROFILE statement allows for custom verification logic.
What is the primary role of the 'Patching' phase in an upgrade?
The dictionary must be upgraded.
Why this answer
Ensuring the binaries and the data dictionary match the desired target version.
A database is configured with a Flash Recovery Area (FRA). You want to ensure that RMAN automatically deletes obsolete backups to reclaim space in the FRA. Which command should you configure?
Setting a retention policy allows RMAN to identify files as obsolete when they fall outside the policy, enabling the FRA to reclaim that space.
Why this answer
The CONFIGURE RETENTION POLICY TO RECOVERY WINDOW or REDUNDANCY command, combined with the automatic management of the FRA, allows RMAN to delete obsolete backups when space is required.
Can you use standard SQL DML on a Blockchain table?
Native SQL support is a key feature.
Why this answer
Yes, you can insert rows into a Blockchain table using standard INSERT statements.
What is the default location for RMAN backups if no FRA is configured?
Without FRA, it defaults to the current directory.
Why this answer
If no FRA is configured, RMAN defaults to the location defined by the OS or the current directory, but it is best practice to configure the FRA.
What is the recommended tool to apply a bundle patch to a 23ai environment?
OPatch is the correct tool.
Why this answer
OPatch is the standard utility for applying patches to Oracle software.
When configuring a secure listener in 23ai, which protocol is recommended for encrypted communication?
TCPS provides SSL/TLS encryption.
An administrator needs to identify the top wait events causing performance degradation in the database over the last hour. Which tool provides the most granular, session-level view of these wait events?
ASH captures active session state every second, providing the necessary granularity for real-time analysis.
Why this answer
ASH provides real-time, session-level performance data, making it the ideal tool for analyzing short-term, granular performance spikes.
What is the primary characteristic of a Blockchain table in 23ai?
Immutability is the core feature.
Why this answer
It is an insert-only, immutable table.
When using 'Snapshot Carousel', how many snapshots can a single PDB retain?
This parameter governs the snapshot limit.
Why this answer
The number is managed by the MAX_PDB_SNAPSHOTS parameter.
What is the recommended approach for updating data in a table using Lock-Free Reservations?
The database handles the magic.
Why this answer
The application performs the update, and Oracle handles the non-blocking logic.
Which THREE actions can you take to improve execution plan stability?
Provides hints for stable plans.
Why this answer
Stability is achieved through baselines, profiles, and optimizer plan stability features.
What is the purpose of the 'CDB_ROOT'?
The root is the central management container.
Why this answer
The CDB root manages system-wide objects and metadata.
When a PDB is in 'RESTRICTED' mode, which sessions are allowed?
This privilege is required to access restricted PDBs.
Why this answer
Only users with RESTRICTED SESSION privilege are allowed.
Which THREE states can a PDB reside in?
Base state after creation.
Why this answer
PDBs can be MOUNTED, READ WRITE, or READ ONLY.
Which THREE of the following are prerequisites for performing an RMAN backup to an Oracle Cloud Infrastructure (OCI) Object Storage bucket?
The config file provides the necessary path information for OCI storage.
Why this answer
You need the OCI SDK, a configuration file, and the correct library.
You are configuring a new CDB and want to ensure that PDBs use a common set of initialization parameters while allowing for specific overrides. Which parameter must be configured at the CDB level to allow local PDB parameter overrides?
PDB_SPFILE is essential for managing local PDB initialization parameters.
Why this answer
The PDB_SPFILE parameter in the CDB root allows managing individual PDB spfiles.
In True Cache, how is consistency maintained with the primary database?
True Cache relies on standard replication flows.
Why this answer
True Cache uses the database's internal replication mechanisms to stay consistent.
An application is experiencing high library cache lock contention. Which view is best suited to investigate this specific wait event?
This view provides real-time information on what a session is waiting for.
Why this answer
V$SESSION_WAIT shows details about wait events for active sessions.
What is the primary purpose of the Flashback Database feature?
Flashback Database provides a fast way to revert the database state.
Why this answer
Flashback Database allows you to return the entire database to a previous point in time without restoring from a backup.
When configuring Memoptimized Write, what is the role of the 'flush' operation?
This is the persistence step.
Why this answer
The flush operation moves data from the memory buffer to the physical data blocks.
For Memoptimized Write, where is the data initially stored before being written to the database?
Data is buffered in memory for speed.
Why this answer
Memoptimized Write uses a buffer in the SGA to speed up the ingestion process.
When configuring Parallel DML in 23ai, which parameter must be set to 'ALWAYS' to ensure parallel execution for DML statements without hints?
This parameter controls the behavior of parallel DML.
Why this answer
Setting ENABLE_PARALLEL_DML to ALWAYS ensures that all DML operations are executed in parallel if possible.
When using Blockchain Tables, what is the default behavior for rows that have reached their 'no drop' period?
The table protects rows until the retention period passes, but manual deletion is still restricted by the feature.
Why this answer
Blockchain tables prevent modification and deletion until the retention period expires.
When using SQL Tuning Advisor, what is a 'SQL Tuning Set' (STS)?
STS groups SQL, plans, and bind variables for tuning analysis.
Why this answer
An STS is a collection of SQL statements with their associated execution context.
Which RMAN command allows you to verify that all database files are present and accessible without performing a full backup?
CROSSCHECK checks the physical existence of backups.
Why this answer
CROSSCHECK verifies the status of files in the catalog against the physical files on disk.
Which view displays the current settings for the AWR retention period?
This view stores the AWR configuration parameters.
Why this answer
DBA_HIST_WR_CONTROL contains the snapshot settings including retention and interval.
Which view displays the PDBs that are currently open in the CDB?
V$PDBS lists all PDBs and their open state.
Why this answer
V$PDBS shows the status of all PDBs in the instance.
How do you disable a Unified Audit policy?
This is the correct syntax for disabling a policy.
Why this answer
The 'NOAUDIT POLICY' command is used to disable a policy.
You are using Block Change Tracking (BCT). Where is the BCT file stored?
By default, it is stored in the FRA.
Why this answer
The BCT file is managed by the database and typically stored in the FRA or as defined by the DB_BLOCK_CHANGETRACKING_FILE parameter.
You are configuring a PDB. What happens to the PDB if you drop the CDB?
PDBs cannot exist outside a CDB.
Why this answer
All PDBs are automatically removed when the CDB is dropped.
Which command allows you to change the state of a PDB to READ ONLY?
Correct syntax for read-only mode.
Why this answer
ALTER PLUGGABLE DATABASE [PDB_NAME] OPEN READ ONLY is the correct syntax.
You are tuning a complex SQL statement. You want to see the execution plan that the optimizer is currently choosing. Which dynamic performance view is most useful?
This view provides the execution plan for cached SQL.
Why this answer
V$SQL_PLAN shows the execution plan for statements currently in the library cache.
Which THREE of the following are components of AI Vector Search in 23ai?
Used for optimized retrieval.
Why this answer
AI Vector Search includes the VECTOR data type, vector indexing, and SQL operators for distance calculation.
Which TWO actions occur during a RMAN 'BACKUP AS COPY' operation?
An image copy is a direct, uncompressed copy of the file.
Why this answer
A backup copy is a direct image copy, not an RMAN backup set, and is stored as an exact bit-for-bit file.
To improve performance, you suspect an index is missing. Which advisor should you invoke to get recommendations for new indexes?
This advisor analyzes access paths and recommends indexes/materialized views.
Why this answer
The SQL Access Advisor provides recommendations on schema design, including indexes and materialized views.
Which TWO conditions are required for Block Change Tracking (BCT) to be effective?
It is not enabled by default.
Why this answer
BCT must be enabled and an incremental backup must be performed.
Which user is responsible for the management of the Unified Audit trail?
This is the dedicated role for audit administration.
Why this answer
The AUDIT_ADMIN role is required for managing unified audit policies and data.
You are migrating a non-CDB database to a PDB in a 23ai CDB using the remote cloning method. Which condition must be met for the non-CDB to be successfully plugged in?
A non-CDB must be opened READ ONLY to allow the CDB to perform the metadata copy.
Why this answer
When plugging in a non-CDB, it must be opened in READ ONLY mode to ensure consistency during the cloning process, and the non-CDB must be at a version compatible with the CDB.
You are performing a tablespace recovery. What is the first thing that must happen to the tablespace before recovery can start?
Tablespace recovery requires the tablespace to be offline.
Why this answer
The tablespace must be taken offline to perform recovery on its constituent datafiles.
When using Wide Tables, what is the impact on performance for queries that select only a small subset of columns?
The architecture is optimized for wide row access.
Why this answer
Wide Tables in 23ai are optimized to handle many columns without significant performance degradation compared to vertical splitting.
When migrating to 23ai, what is the impact of switching to the VECTOR data type for existing applications?
Integration requires code changes.
Why this answer
Applications need to be updated to use the new native vector support.
The Automatic SQL Plan Management (ASPM) feature is enabled. A new execution plan is identified for a high-load query that performs better during testing. How does the database verify this plan before evolving the baseline?
The database performs a trial execution to confirm performance improvement before evolving the plan baseline.
Why this answer
The database performs a verification task where it executes the new plan and compares it against the baseline performance to decide if it should be accepted.
Which RMAN configuration must be set to ensure that backups are automatically deleted when they are no longer needed?
This sets the policy for retention.
Why this answer
CONFIGURE RETENTION POLICY defines the criteria for what is obsolete.
Which TWO audit trails can be enabled simultaneously?
Can be enabled.
Why this answer
Unified and traditional audit trails can both be active, though it is not recommended.
Which of the following is true regarding Vector embeddings in 23ai?
This is how vector data is represented.
Why this answer
Vectors are typically generated by AI models and stored as arrays of floats.
Which TWO statements about Unified Auditing are correct?
It is the unified successor.
Why this answer
Unified auditing is enabled by default in some configurations and is the replacement for standard auditing.
Which privilege is required to perform a PDB unplug operation?
Required for container management operations.
Why this answer
SYSDBA or SYSOPER at the CDB root is required.
Which TWO methods can be used to tune SQL statements?
Manual intervention using hints.
Why this answer
Manual tuning with hints and automated tuning via advisors are common practices.
Which THREE of the following are valid components or features of the Automatic SQL Tuning process in Oracle 23ai?
SPM is integrated with tuning to ensure performance stability.
Why this answer
The Automatic SQL Tuning process involves the SQL Tuning Advisor, SQL Tuning Sets, and SQL Plan Baselines as part of its automated diagnostic and correction cycle.
You need to perform a Rolling Upgrade using Data Guard. What is the minimum required configuration?
A physical standby is the foundation for a rolling upgrade.
Why this answer
A physical standby database is required to perform a rolling upgrade using transient logical standby or switchover methods.
Which component allows True Cache to provide high performance for read-only workloads by caching data on the application tier?
True Cache is the feature designed for this purpose.
Why this answer
True Cache is a read-only cache that resides in the application tier to offload the primary database.
You are tuning a query that is performing poorly due to high 'buffer busy waits'. What is the likely cause?
This contention happens in the buffer cache.
Why this answer
Buffer busy waits occur when multiple sessions try to modify the same block at the same time.
Which configuration parameter determines the maximum size of a backup piece?
This is the correct RMAN command.
Why this answer
CONFIGURE MAXSETSIZE is the parameter used to cap the size of a backup set.
You are applying an Oracle Release Update (RU) in a Data Guard environment. Following the 'Rolling Upgrade' methodology, which instance should be patched first to ensure continuous availability?
The standby is patched first to allow for a switchover, minimizing downtime.
Why this answer
In a rolling upgrade, the standby instance is patched first, then a switchover is performed, and finally the former primary is patched.
Which RMAN command should you use to verify that all blocks in your database datafiles are free from physical corruption without actually performing a restore?
The BACKUP VALIDATE command checks the datafiles for physical and logical corruption.
Why this answer
The VALIDATE command checks for physical and logical block corruption in datafiles, control files, and backup sets.
What is the primary benefit of using a SQL Profile?
Profiles provide auxiliary information to the optimizer.
Why this answer
SQL Profiles improve the optimizer's choices by providing better selectivity and cardinality estimates.
Which THREE types of information are captured in an AWR snapshot?
Important for parsing/memory health.
Why this answer
AWR captures workload, system statistics, and session activity.
When migrating to 23ai, which file is used to specify the target home directory in AutoUpgrade?
The config file is where the target home is defined.
Why this answer
The AutoUpgrade .cfg file uses the 'target_home' parameter.
Which TWO commands allow you to check for block corruption in a database?
This is the standard RMAN way to check for physical corruption.
Why this answer
ANALYZE TABLE and RMAN validate are the primary methods.
What is the primary benefit of using a 'Proxy PDB'?
Proxy PDBs provide local access to remote PDB objects.
Why this answer
A Proxy PDB allows access to a PDB in a remote CDB.
Which dictionary view tracks the PDB creation time?
Contains PDB metadata including creation time.
Why this answer
CDB_PDBS contains the creation time of all PDBs in the CDB.
Which TWO of the following are benefits of using an RMAN recovery catalog?
This is a primary benefit.
Why this answer
Centralization and extended retention are key benefits.
What allows Memoptimized Write to perform so quickly?
This reduces IO latency.
Why this answer
It avoids writing to the redo log synchronously.
Which THREE features are required for using Flashback Database?
FRA stores the flashback logs.
Why this answer
Flashback Database requires the recovery area, flashback logs, and the database to be in ARCHIVELOG mode.
How do you check the status of all current RMAN backup jobs?
This view is the standard for monitoring job progress.
Why this answer
V$RMAN_STATUS provides the most comprehensive view of backup job history and status.
Which THREE operations are permitted on Blockchain tables?
Used for data integrity checks.
Why this answer
You can insert, select, and verify rows in a Blockchain table.
Practice OCP by domain
Target a specific domain to shore up weak areas.