OCP Practice Exam 3 — 20 Questions

Question 1

You need to ensure that the PDB$SEED template is updated with the latest patch level from the CDB. How should you proceed?

Accepted Answer

Apply the patch to the CDB and let the system handle synchronization. The PDB$SEED is read-only. To update it, you must apply the patch to the CDB home, and the seed will be automatically synchronized during the next CDB startup or via the execution of the datapatch utility.

Answer

Run 'ALTER PLUGGABLE DATABASE PDB$SEED SYNC'

Answer

Drop and recreate PDB$SEED using the CREATE PLUGGABLE DATABASE command

Answer

Open PDB$SEED in READ WRITE mode and apply patches

Question 2

You want to clear all configured RMAN settings back to their default values. Which command do you use?

Accepted Answer

CONFIGURE [parameter] CLEAR. CONFIGURE [parameter] CLEAR resets a specific setting to its default.

Answer

DELETE CONFIGURATION

Answer

RESET ALL

Answer

CONFIGURE ALL CLEAR

Question 3

You need to ensure that RMAN automatically deletes archived redo logs after they have been backed up twice to tape. Which configuration command should you use?

Accepted Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE SBT;. The CONFIGURE ARCHIVELOG BACKUP COPIED command with the TO DEVICE TYPE and num_copies parameters defines the retention policy for archived logs.

Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Answer

SET ARCHIVELOG DELETION POLICY TO 2 COPIES;

Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Question 4

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. The RECOVER TABLE command creates an auxiliary instance, performs a partial restore and recovery of the tablespace containing the table, and then exports the table metadata to the target database.

Answer

Automatic deletion of all backups older than the restore point

Answer

Automatic conversion of the database to NOARCHIVELOG mode

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. Datafiles in a platform with different endianness must be converted using the CONVERT command to be readable by the target instance.

Answer

Redo log files

Answer

Parameter files

Answer

Control files

Question 6

You want to backup the control file automatically whenever a change is made to the database structure. Which command do you use?

Accepted Answer

CONFIGURE CONTROLFILE AUTOBACKUP ON. CONFIGURE CONTROLFILE AUTOBACKUP ON enables automatic control file backups.

Answer

BACKUP CONTROLFILE ON

Answer

ALTER SYSTEM SET CONTROLFILE_AUTOBACKUP=TRUE

Answer

SET CONTROLFILE AUTOBACKUP ON

Question 7

What is the default location for RMAN backups if no FRA is configured?

Accepted Answer

The current working directory of the RMAN session.. 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.

Answer

The system tablespace.

Answer

The database home directory.

Answer

The redo log path.

Question 8

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?

Accepted Answer

The auxiliary instance must have enough disk space to hold the tablespace being recovered.. TSPITR requires an auxiliary instance to recover the tablespace to the desired point in time before importing it back to the target database.

Answer

The auxiliary instance must have the same DBID as the target database.

Answer

The auxiliary instance must be configured with a separate ORACLE_HOME.

Answer

The auxiliary instance must be started in NOMOUNT mode manually by the DBA.

Question 9

You are configuring a RMAN backup strategy for a multitenant container database (CDB) and want to ensure that a backup of the root container also includes all PDBs. Which configuration command achieves this automatically?

Accepted Answer

BACKUP DATABASE. The 'CONFIGURE PDB BACKUP OPTIMIZATION ON' command is not for this, but 'CONFIGURE BACKUP OPTIMIZATION ON' is standard. Specifically, 'BACKUP DATABASE' in a CDB context handles all PDBs unless excluded.

Answer

CONFIGURE INCLUDE PDB BACKUP ON

Answer

CONFIGURE CDB BACKUP ALL ON

Answer

BACKUP CONTAINER ALL

Question 10

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?

Accepted Answer

DBMS_SPM. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is the standard way to load plans into the baseline.

Answer

DBMS_SCHEDULER

Answer

DBMS_SQLTUNE

Answer

DBMS_STATS

Question 11

In a multitenant environment, what is the best approach to backup only a specific PDB?

Accepted Answer

BACKUP PLUGGABLE DATABASE pdb_name. You can use the 'BACKUP PLUGGABLE DATABASE' command to target a specific PDB.

Answer

BACKUP CDB PDB pdb_name

Answer

BACKUP DATABASE PDBNAME

Answer

BACKUP PDB ALL

Question 12

You need to determine why a particular session is experiencing high 'db file sequential read' waits. Which view provides the most detailed information about the specific blocks being read?

Accepted Answer

V$SESSION_WAIT. V$SESSION_WAIT includes P1, P2, and P3 columns which often identify the file and block being accessed during the wait.

Answer

V$BH

Answer

V$SQL

Answer

V$FILESTAT

Answer

V$SQL_PLAN

Question 13

A critical application query is experiencing performance issues due to stale optimizer statistics. You want to use the Optimizer Statistics Advisor to identify the root cause. Which action must you perform to trigger the advisor?

Accepted Answer

Call DBMS_STATS.REPORT_STATS_ADVISOR_TASK to generate a report on statistics-related issues.. The Optimizer Statistics Advisor can be invoked using the DBMS_STATS.REPORT_STATS_ADVISOR_TASK function to analyze and report on statistics health.

Answer

Run the AWR report and look for 'Stats' in the top wait events.

Answer

Enable the SQL Tuning Advisor for the specific SQL_ID.

Answer

Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

Question 14

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?

Accepted Answer

SQL Tuning Sets. SQL Tuning Sets are specifically designed to capture, store, and transport SQL statements along with their execution context and statistics.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 15

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?

Accepted Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;. 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.

Answer

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '...';

Answer

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Question 16

When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?

Accepted Answer

The total time spent by sessions in the database. DB Time represents the total time spent by foreground processes in the database, indicating overall load.

Answer

The time spent on disk I/O only

Answer

The time spent on instance background processes

Answer

The time the database was open

Question 17

You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?

Accepted Answer

The SPM Evolve Task is disabled. The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.

Answer

SQL Plan Baselines are full

Answer

Statistics are gathered manually

Answer

The database is in NOARCHIVELOG mode

Question 18

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?

Accepted Answer

PDB_SGA_MAX_SIZE. 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.

Answer

DB_CACHE_SIZE

Answer

SGA_MIN_SIZE

Answer

MEMORY_TARGET

Question 19

You have a query that is performing poorly due to a stale execution plan. You want to lock the current optimal plan to prevent the optimizer from changing it during subsequent runs. What should you do?

Accepted Answer

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. SQL Plan Baselines allow you to store and fix execution plans for specific SQL statements.

Answer

Run DBMS_STATS.LOCK_TABLE_STATS

Answer

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Answer

Use SQL Tuning Advisor to accept a new profile

Question 20

Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?

Accepted Answer

AWR Report. The AWR report has a section specifically for Top SQL by Elapsed Time.

Answer

Alert Log

Answer

Listener Log

Answer

ADDM Report