Backup & Recovery
10 Questions
Study Guide: This page shows the correct answers and explanations for all questions in this topic.
Review these to understand the key concepts.
Question:
Which three statements are true about dynamic performanc e views?
Correct Answer: A,B,E
- A. Read consistency is not guaranteed.
- B. V$FIXED_TABLE can be queried to display the names of all dynamic performance views.
- E. They can be queried only when the database is open.
Explanation
A .True. V$ views reflect real-time memory data, not consistent snapshots. B .True. V$FIXED_TABLE lists all V$ views. C .True. Data comes from memory structures and data dictionary metadata. D .False. Owned by SYS, not SYSTEM. E .False. Some V$ views are accessible in MOUNT state.
Question:
In which two ways would you disable timing information c ollected for wait events and much of the performance monitoring capability of the database?
Correct Answer: A,D
- A. By setting the TIMED_STATISTICS system parameter to FALSE.
- D. By setting the STATISTICS_LEVEL parameter to BASIC.
Explanation
A .True. TIMED_STATISTICS = FALSE disables timing data collection. B .False. No such procedure exists. C .False. No TIMED_STATISTICS_LEVEL parameter exists. D .True. STATISTICS_LEVEL = BASIC disables most performance monitoring. E .False. No such procedure exists.
Question:
Which advisor is used to detect missing or stale object statistics?
Correct Answer: B
- B. SQL Statistics Advisor
Explanation
B .True. The SQL Statistics Advisor (new in 23ai) identifies stale or missing statistics. Others focus on tuning, performance analysis, or access paths.
Question:
Which two statements are true about trace files produced by the Oracle Database server?
Correct Answer: B,C
- B. They can be written by server processes to a file system.
- C. They can be written by background processes to a file system.
Explanation
A .False. Not all trace files indicate errors; some log normal activity. B .True. Server processes write traces (e.g., ora_s000_123.trc) to the file system. C .True. Background processes (e.g., ora_lgwr_123.trc) also write traces. D .False. Names include process type and PID, not just DB name and sequence. E .False. Traces go to DIAGNOSTIC_DEST, not necessarily FRA unless configured there.
Question:
Which two SQL Plan Management Advisor tasks are part of Automatic Maintenance Tasks?
Correct Answer: D,E
- D. The Automatic SQL Plan Management Evolve Advisor task, which evolves plans that have recently been added as the SQL plan baseline for statements.
- E. The Automatic SQL Tuning Advisor tasks, which would exam ine the performance of high-load SQL statements and make recommendations for those state ments.
Explanation
Automatic Maintenance Tasks (AMTs) in 23ai optimize database performance. Let’s explore: A .False. SQL Access Advisor exists but isn’t an AMT; it’s manual or invoked separately. B .False. SQL Performance Analyzer assesses change impacts but isn’t part of AMTs. C .False. No “Automatic Index Advisor” exists as an AMT; Auto Index is a separate feature. D .True. The SPM Evolve Advisor task (part of ORA$AUTOTASK) automatically evolves SQL plan baselines, testing and accepting new plans. Mechanics:Runs in maintenance windows, managed by DBMS_SPM. E .True. SQL Tuning Advisor (STA) runs automatically via AMTs, tuning high-load SQL. Mechanics:Identifies candidates from AWR and suggests index es, profiles, etc.
Question:
Which three tasks are part of the predefined Automated Maintenance Tasks?
Correct Answer: B,D,F
- B. Automatic Optimizer Statistics Collection.
- D. Automatic SQL Plan Management advisor tasks.
- F. Automatic segment and segment statistics advisor tasks.
Explanation
A .False. Backups are via RMAN, not AMTs. B .True. Stats collection is an AMT. C .False. Error logging isn’t an AMT. D .True. SPM tasks are included. E .False. Notifications aren’t AMTs. F .True. Segment Advisor is an AMT.
Question:
Which two AWR-based tools listed below are part of Oracle Database self-tuning components?
Correct Answer: A,B
- A. Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.
- B. ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrade s noticeably.
Explanation
The Automatic Workload Repository (AWR) is a cornerstone of Oracle’s self-tuning capabilities, collecting and storing performance statistics for analysis. Let’s dissect each option: A . Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic. True. AWR automatically captures statistics (e.g., wait events, SQL stats) from the System Global Area (SGA) via the MMON (Manageability Monitor) process, storing them in the AWR repository (in SYSAUX). This is part of the Automatic Database Diagnostic Mo nitor (ADDM) framework, though “Automatic Database Diagnostic” likely refers to this broader me chanism. Mechanics:Snapshots are taken hourly by default (configurable via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS), persisting data like DBA_HIST_SYSSTAT for self-tuning analysis. Practical Use:Enables historical performance tracking, fe eding tools like ADDM and SQL Tuning Advisor. Edge Case:If STATISTICS_LEVEL=BASIC, AWR collection is disabled, haltingself-tuning. Historical Note:Introduced in 10g, enhanced in 23ai for finer granularity. B . ADDM, a server-based expert that reviews database perform ance statistics captured by Snapshots to identify potential problems before system performance degrade s noticeably. True. The Automatic Database Diagnostic Monitor (ADDM) analyzes AWR snapshots to proactively detect issues (e.g., high CPU usage, I/O bottlenecks) and recom mend fixes. It runs automatically after each snapshot in maintenance windows. Mechanics:ADDM uses DBA_ADVISOR_FINDINGS to log issues, leveraging AWR data like DBA_HIST_SQLSTAT. Example: It might suggest adding an i ndex for a slow query. Practical Use:Prevents performance degradation in production systems by catching trends early. Edge Case:Limited by snapshot frequency; real-time issues may need manual intervention. C . Automatic Diagnostic Collector used to capture and store database errors and hung analysis. False. No such tool exists as an “Automatic Diagnostic Collector” in AWR context. This likely confuses the Incident Packaging Service (IPS) or ADR (Automatic Diagn ostic Repository), which handles errors but isn’t AWR -based or self-tuning. Why Incorrect:ADR collects trace files and logs, not AW R statistics, and isn’t part of self-tuning. D . Automatic population of performance views (V$ views) from sta tistical data stored in AWR repository and using Automatic Database Diagnostic. False. V$ views (e.g., V$SESSION) are real-time memory structur es in the SGA, not populated from AWR, which is historical (e.g., DBA_HIST_*). AWR doesn’t back-feed V$ views; the reverse occurs via snapshots. Why Incorrect:Misunderstands the data flow; AWR is a sink, not a source for V$ views. E. Automatic Application Tracing used to collect High-Load SQL statements and statistics. False. No “Automatic Application Tracing” exists as an AWR tool. SQL tracing (e.g., DBMS_MONITOR) is manual, and high-load SQL is captured by AWR indirectl y via V$SQL snapshots, not a distinct tracing tool. Why Incorrect:Conflates manual tracing with AWR’s passive collection.
Question:
Which three statements are true about using SQL*Plus?
Correct Answer: A,E,F
- A. It can run scripts passed to it by a shell script.
- E. It can run scripts entered at the SQL prompt.
- F. It has its own commands that are separate from any SQL statements.
Explanation
A .True. sqlplus @script.sql works from shell scripts. B .False. RMAN uses its own client, not SQL*Plus. C .False. Bundled with Oracle DB software. D .False. Command-line only; no GUI in 23ai. E .True. @script runs scripts interactively. F .True. Commands like SHOW PARAMETER are unique.
Question:
What are Oracle Database Metrics?
Correct Answer: A,C
- A. Oracle Database Metrics monitor performance using thresholds to generate alerts.
- C. Oracle Database Metrics are a set of measured statistics per unit of time (per second), transaction, or sessions that are used to evaluate performance.
Explanation
A .True. Metrics (e.g., V$SYSMETRIC) use thresholds for alerts. B .False. Metrics aren’t tied to email notifications directly. C .True. Metrics measure rates (e.g., IOPS, transactions/s ec). D .False. EMCC uses metrics, but they’re DB -level, not EM -specific. E .False. Metrics exist in the DB, not just EMCC.
Question:
What are the three components of Oracle Database Automatic Maintenance Tasks?
Correct Answer: B,C,F
- B. Oracle Database Resource Manager, which enables you to manage and configure system resources used by the Automatic Maintenance Tasks.
- C. The maintenance windows managed by Oracle Database Scheduler, which are predefined time intervals permitting scheduled tasks.
- F. A set of tasks that are started automatically at regular intervals to perform maintenance operations on the database.
Explanation
A .False. Diagnostic collection is ADR, not AMT. B .True. Resource Manager allocates resources to AMTs. C .True. Scheduler defines maintenance windows. D .False. Packaging is IPS/ADR, not AMT. E .False. Alert log is separate from AMTs. F .True. Tasks like stats collection are AMTs.