314 questions total · 5pages · All types, answers revealed
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?
This procedure checks if a non-CDB is compatible with the target CDB.
Why this answer
DBMS_PDB.CHECK_PLUG_COMPATIBILITY is the standard procedure for pre-migration validation.
You are upgrading from 19c to 23ai and need to identify incompatible parameters before the upgrade. Which tool should you use?
This tool identifies issues before the upgrade process starts.
Why this answer
The Pre-Upgrade Information Tool (preupgrade.jar) is the standard method for identifying parameter incompatibilities.
Which THREE items are required for the Automatic SQL Plan Management to effectively stabilize plans?
The core component for plan storage.
Why this answer
SPM requires SQL baselines, a plan history, and an evolution process.
Which TWO features help protect database availability during an upgrade?
Provides a standby instance.
Why this answer
Data Guard and Flashback Database are key for availability and recovery.
You want to limit the amount of memory a specific PDB can consume in a CDB. Which parameter should you configure?
PDB_SGA_TARGET allows limiting memory per PDB.
Why this answer
The SGA_TARGET at the PDB level is managed via the PDB_SGA_TARGET parameter.
Which THREE pieces of information can be found in a SQL Tuning Advisor report?
Advisor analyzes access paths.
Why this answer
The report includes findings, recommendations, and execution statistics.
Which TWO tasks are required to successfully unplug a PDB?
Unplugging creates the XML manifest.
Why this answer
You must close the PDB and then execute the UNPLUG command.
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?
The catalog is not subject to the circular reuse constraints of the control file.
Why this answer
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.
What is the purpose of the 'Wallet' in Oracle security?
This is the definition of a wallet.
Why this answer
A wallet stores sensitive credentials like passwords and encryption keys.
In the context of 23ai, what does 'True Cache' mean for application developers?
The database handles the routing.
Why this answer
Developers can offload read operations to True Cache without changing their application logic.
What happens to a traditional audit record when you migrate to Unified Auditing in 23ai?
They remain in their original locations.
Why this answer
Traditional audit records are not automatically migrated; you should clear them or archive them before switching to Unified Auditing.
When migrating to 23ai, which method allows for the least amount of downtime?
This allows for efficient bulk data movement with minimal downtime.
Why this answer
Transportable Tablespaces combined with Data Guard or GoldenGate typically offers the lowest downtime.
Which THREE of the following are benefits of the JSON Duality feature?
Easier data handling.
Why this answer
Duality simplifies coding, maintains consistency, and supports native SQL access.
Which TWO parameters are critical for PDB resource management?
Limits CPU usage per PDB.
Why this answer
PDB_SGA_TARGET and CPU_COUNT (or PDB_CPU_COUNT) control PDB resources.
Which THREE things can prevent an AutoUpgrade process from starting?
Indicates another process is running.
Why this answer
Insufficient space, incorrect permissions, or already running processes are common blockers.
Which component is required to implement Transparent Data Encryption (TDE) in 23ai?
The wallet acts as the keystore for the master key.
Why this answer
A keystore (wallet) is required to hold the master encryption key for TDE.
Which THREE actions occur when you execute 'ALTER PLUGGABLE DATABASE PDB1 OPEN'?
Default open mode is READ WRITE.
Why this answer
The PDB state changes, triggers run, and services are started.
Which view should you query to verify the encryption status of tablespaces in 23ai?
This is the correct view for encryption status.
Why this answer
V$ENCRYPTED_TABLESPACES shows the encryption status of all tablespaces in the database.
In a multitenant environment, what is the best approach to backup only a specific PDB?
This is the correct command for PDB-level backups.
Why this answer
You can use the 'BACKUP PLUGGABLE DATABASE' command to target a specific PDB.
Which directory contains the Alert log for the CDB?
This is the standard ADR path.
Why this answer
The Automatic Diagnostic Repository (ADR) stores all CDB diagnostic information.
How does 23ai handle vector distance calculations at scale?
The SQL engine is optimized for this.
Why this answer
23ai optimizes these calculations in the SQL engine.
You are investigating an RMAN backup failure. The log indicates that RMAN is unable to access the recovery area. Which view should you query to check the current space usage of the Fast Recovery Area?
This view is the standard for checking FRA utilization.
Why this answer
V$RECOVERY_FILE_DEST provides the directory, space limit, and space used for the FRA.
Your database is in ARCHIVELOG mode. You accidentally dropped a critical tablespace and need to perform a Tablespace Point-in-Time Recovery (TSPITR). What is the primary requirement for the auxiliary instance during this process?
The auxiliary instance is used to restore and recover the datafiles to the point in time.
Why this answer
TSPITR requires an auxiliary instance to recover the tablespace to the desired point in time before importing it back to the target database.
You are implementing Unified Auditing in an Oracle 23ai database. You need to capture all actions performed by a specific user 'APP_USER', regardless of the object accessed. Which SQL statement is correct?
The 'ACTIONS ALL BY username' syntax is correct for auditing every action performed by that user.
Why this answer
CREATE AUDIT POLICY policy_name ACTIONS ALL BY APP_USER is the correct syntax for auditing all actions by a specific user.
Which TWO security features can be applied specifically to a PDB?
Can be set per PDB.
Why this answer
Encryption and Auditing can be PDB-specific.
When configuring parallel DML, what is the effect of setting 'FORCE' on a statement?
This overrides the optimizer.
Why this answer
The FORCE hint forces the statement to run in parallel regardless of the cost optimizer's decision.
You are configuring a new CDB and want to ensure that PDBs use only a specific subset of the CDB's total SGA memory while allowing individual PDBs to scale up when demand is high. Which parameter should you configure to enforce this?
This parameter limits the maximum amount of SGA memory that a specific PDB can consume.
Why this answer
The SGA_TARGET parameter at the CDB level defines the total SGA, while PDB_SGA_MIN_SIZE and PDB_SGA_MAX_SIZE parameters allow for fine-grained control and resource capping within the multitenant environment.
You have a high volume of parse time in your database. What could be the cause?
This causes excessive hard parses for each new literal value.
Why this answer
Hard parsing occurs when SQL is not shared, often due to literals instead of bind variables.
What happens to existing sessions when you issue 'ALTER PLUGGABLE DATABASE PDB1 CLOSE'?
IMMEDIATE forces the closing of sessions.
Why this answer
By default, the command fails if sessions exist unless 'IMMEDIATE' is used.
You are using SQL Plan Management. What happens when a new execution plan is discovered for a SQL statement that already has a baseline?
New plans are kept as unaccepted until verified.
Why this answer
The new plan is marked as 'accepted' only if it is proven to be better than the existing plan; otherwise, it is stored as 'unaccepted'.
Which THREE parameters can be set at the PDB level?
Allowed as a PDB-specific setting.
Why this answer
SGA_TARGET, UNDO_TABLESPACE, and DB_FILE_NAME_CONVERT can be PDB-specific.
You have a standby database and want to use RMAN to offload backups to the standby. Which configuration is required to ensure the primary database recognizes the backups performed on the standby?
This setting allows RMAN to perform autobackups of the control file from the standby site.
Why this answer
To use a standby for backups, you must connect RMAN to the recovery catalog and the standby database, and ensure the configuration includes 'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY'.
Which TWO tools can be used to perform PDB point-in-time recovery?
Standard tool for PITR.
Why this answer
RMAN and SQL*Plus are used to manage PDB PITR operations.
You have a vector index and notice search performance is slow. Which index type should you use to optimize similarity search?
HNSW is optimized for vector search operations.
Why this answer
HNSW (Hierarchical Navigable Small World) index is the standard for efficient vector similarity search in 23ai.
A PDB is in 'READ WRITE' mode. You need to perform a maintenance task that requires the PDB to be in 'RESTRICTED' mode. Which command achieves this?
The correct sequence is to close the PDB first, then reopen it with the RESTRICTED privilege.
Why this answer
To move a PDB into restricted mode, you must use ALTER PLUGGABLE DATABASE ... CLOSE followed by OPEN RESTRICTED.
You want to capture a specific set of SQL statements and their execution plans to baseline them. Which package allows you to capture plans from the cursor cache?
This package manages SQL Plan Baselines.
Why this answer
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is the standard way to load plans into the baseline.
You are migrating a legacy application to use Wide Tables in 23ai. What is the maximum number of columns supported by a Wide Table?
4096 is the new limit for Wide Tables.
Why this answer
Oracle 23ai supports up to 4096 columns in a table.
What is the purpose of the 'ASH' report?
ASH is designed for recent, high-frequency snapshots.
Why this answer
ASH reports analyze transient problems by looking at recent active session history.
You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?
If the task is disabled, the system will not automatically evolve plans.
Why this answer
The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.
You need to ensure that the PDB name is unique in your network. Which parameter should you check?
Must be unique for each database in a Data Guard configuration.
Why this answer
DB_UNIQUE_NAME must be unique across the CDB infrastructure.
When using the VECTOR data type, what is the maximum number of dimensions supported?
2048 is the supported maximum.
Why this answer
Oracle 23ai supports up to 2048 dimensions for the vector data type.
What is the default behavior when shutting down a CDB instance?
PDBs are closed automatically during CDB shutdown.
Why this answer
The SHUTDOWN command closes all PDBs before the CDB.
What happens if a block is marked as corrupt during an RMAN backup?
This is the standard behavior to track corruption.
Why this answer
RMAN writes the corruption information to V$DATABASE_BLOCK_CORRUPTION but continues the backup unless the number of errors exceeds MAXCORRUPT.
Which TWO features of 23ai help improve performance of high-concurrency workloads?
Reduces contention.
Why this answer
Lock-Free Reservations and Memoptimized Write are specifically for high-concurrency improvements.
Which command is used to display the current container you are connected to?
Displays the current container context.
Why this answer
SHOW CON_NAME shows the current container name in SQL*Plus.
What is the primary purpose of 'Data Masking' in the Oracle Data Masking and Subsetting Pack?
This is the core purpose.
Why this answer
Data masking permanently obscures sensitive data in a non-production environment.
Which THREE steps are involved in upgrading a PDB?
Verify no issues persist.
Why this answer
Run the upgrade script, compile invalid objects, and check for errors.
How can you verify if a PDB is encrypted?
This view shows the wallet and encryption status.
Why this answer
Querying DBA_PDB_KE_STATE or checking V$ENCRYPTION_WALLET is the standard method.
You have a query that is being incorrectly optimized because the statistics are not representative of the actual data distribution. What is the best method to handle this without changing the global statistics?
Preferences allow granular control over how stats are gathered for a specific object.
Why this answer
Table-specific statistics collection with specific parameters or using preference settings can help.
Which feature allows you to capture SQL statements from the cursor cache and load them into a SQL Tuning Set?
This procedure extracts SQL from the library cache.
Why this answer
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET is the procedure used to pull queries from memory.
You need to capture PDB performance data. Which feature allows you to capture an AWR snapshot for a specific PDB?
This procedure supports a container ID parameter.
Why this answer
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT is used for AWR snapshots at the container level.
What does the 'Elapsed Time' in a SQL statement represent?
Elapsed time includes both CPU and wait time.
Why this answer
It is the total wall-clock time spent executing the statement from start to finish.
Which advisor should you run to determine if your SGA and PGA memory settings are optimal?
Memory Advisor provides tuning suggestions for SGA/PGA.
Why this answer
Memory Advisor identifies if memory pools are undersized or oversized.
You are monitoring parallel DML performance. Which view provides the best insight into current parallel server activity?
Specifically for parallel execution.
Why this answer
V$PX_SESSION provides real-time information on parallel sessions.
Which THREE features are enabled in 23ai to simplify database management?
Simplifies AI workflows.
Why this answer
JSON Duality, Lock-Free Reservations, and Vector Search are key 23ai management/performance features.
Which TWO files are modified to configure a secured listener?
Configures the listener endpoint.
Why this answer
listener.ora and sqlnet.ora are both typically involved in network security configuration.
When running parallel DML in 23ai, what happens to the data if the transaction fails?
Standard transactional consistency applies.
Why this answer
Parallel DML operations are fully atomic and follow standard rollback procedures.
You are performing an RMAN duplicate database operation. What is the benefit of using the 'FROM ACTIVE DATABASE' clause?
It avoids the backup-restore cycle by copying live data.
Why this answer
It duplicates the database directly from the source without requiring a backup set to be restored first.
An ADDM report suggests increasing the library cache size. What is the most appropriate action?
Increasing SGA_TARGET allows the automatic shared memory manager to allocate more to the shared pool.
Why this answer
Oracle 23ai manages SGA memory automatically; adjusting the SGA_TARGET or SGA_MAX_SIZE is the primary way to influence this.
You have a standby database and want to offload backups to it. What is the recommended way to register the standby's backups in the recovery catalog?
Backups performed on the standby are registered by connecting the standby instance to the catalog.
Why this answer
The standby database should be connected to the recovery catalog to register its own backups.
You want to perform a backup that excludes tablespaces that are read-only. Which command should you use?
This clause specifically ignores read-only tablespaces.
Why this answer
RMAN automatically skips read-only tablespaces in incremental backups, or you can use the 'SKIP READONLY' clause.
Which THREE features are specific to the Multitenant architecture and distinguish a CDB from a non-CDB?
PDBs share the instance processes of the CDB.
Why this answer
Multitenant architecture relies on shared background processes, centralized data dictionary views (CDB_ views), and the ability to plug and unplug databases.
You want to backup the control file automatically whenever a change is made to the database structure. Which command do you use?
This is the correct command.
Why this answer
CONFIGURE CONTROLFILE AUTOBACKUP ON enables automatic control file backups.
A production database experiences a sudden performance degradation. You want to analyze the last 10 minutes of activity to pinpoint blocked sessions. Which approach is best?
V$ACTIVE_SESSION_HISTORY captures session activity in memory for recent performance analysis.
Why this answer
ASH data is stored in memory and is the primary source for recent, short-term performance diagnostics.
What is the default state of a PDB after creating it using the CREATE PLUGGABLE DATABASE command?
The default state is MOUNTED.
Why this answer
By default, a new PDB is created in MOUNTED state.
Which THREE steps are involved in an RMAN-based cross-platform migration of a data file using CONVERT?
The file must be moved to the target server.
Why this answer
The process requires the file to be backed up, converted, and moved to the destination.
Which TWO actions are required when performing a 'Unplug and Plug' operation of a PDB between two different CDBs to ensure data integrity?
The XML file is required to describe the PDB's datafiles for the subsequent plug operation.
Why this answer
To safely move a PDB, the database must be in a consistent state (closed) and the metadata XML file must be generated to describe the PDB structures.
Which THREE of the following are components of the Oracle performance tuning lifecycle?
Third step is implementation.
Why this answer
Tuning is an iterative process involving identification, analysis, and implementation.
Which THREE conditions must be met to perform a successful RMAN cross-platform transportable tablespace migration?
Conversion is required if moving between platforms with different endianness.
Why this answer
The databases must be at compatible levels, endian formats must be checked, and the tablespace must be read-only.
You need to capture a specific set of SQL statements that are causing high CPU usage for later tuning. Which tool allows you to group these statements and transport them across different database environments?
SQL Tuning Sets provide the functionality to capture and export groups of SQL statements for tuning.
Why this answer
SQL Tuning Sets are specifically designed to capture, store, and transport SQL statements along with their execution context and statistics.
When using parallel DML, which system parameter limits the degree of parallelism?
This controls the degree.
Why this answer
PARALLEL_DEGREE_LIMIT restricts the degree of parallelism.
How does Memoptimized Write affect data integrity?
The database ensures data integrity.
Why this answer
Memoptimized Write is fully compliant with ACID properties.
Which TWO parameters are essential for configuring the environment for Wide Tables?
Needs to be large enough for the wide rows.
Why this answer
Wide tables rely on specific database settings to handle the large number of columns.
Which tool can be used to monitor the progress of an AutoUpgrade process?
This tool tracks the upgrade status.
Why this answer
The AutoUpgrade tool provides a job monitor output in the log file directory.
Which TWO views are essential for monitoring PDB-specific memory usage?
Tracks memory per PDB.
Why this answer
V$PDB_MEMORY and V$RESOURCE_LIMIT allow tracking PDB memory consumption.
Practice OCP by domain
Target a specific domain to shore up weak areas.