Diagnostic Tools and Automated Maintenance
This domain tests where diagnostic output lives and how Oracle runs maintenance jobs for you: the Automatic Diagnostic Repository, trace and incident files, health checks, and the automated maintenance task framework.
Automatic Diagnostic Repository
Since 11g, diagnostics live under DIAGNOSTIC_DEST, typically ORACLE_BASE/diag/rdbms/<db_name>//, with alert, trace, incident, cdump, and hm subdirectories. The alert log appears as text and as log.xml. The adrci tool provides SHOW HOMES, SHOW ALERT, SHOW TRACEFILE, IPS BUNDLE, and PURGE commands. Background processes write trace files; SQL tracing is enabled with DBMS_SESSION or DBMS_MONITOR and formatted with TKPROF. Health Monitor checks run through DBMS_HM and are recorded in V$HM_RUN. Incidents can be packaged into a bundle for Oracle Support.
Automated maintenance tasks
Three clients run inside maintenance windows: Automatic Optimizer Statistics Collection, Segment Advisor, and Automatic SQL Tuning Advisor. The default window covers weeknights from 22:00 to 02:00 and all weekend. DBA_AUTOTASK_CLIENT and DBA_AUTOTASK_WINDOW show configuration; DBMS_AUTO_TASK_ADMIN enables or disables a client. Automated jobs run in the ORA$AUTOTASK resource consumer group.
Worked example
Optimizer statistics have not been refreshed for a week and the automatic job appears not to run. Reasoning: first check DBA_AUTOTASK_CLIENT to confirm AUTO_OPTIMIZER_STATS is enabled, then DBA_SCHEDULER_WINDOWS to confirm the maintenance window actually opened, then the run history for the client's job. If the client was disabled with DBMS_AUTO_TASK_ADMIN, re-enable it and, if necessary, run the stats job manually.
Traps that cost marks
- Looking for the alert log in the old bdump directory; ADR replaced that layout.
- Deleting trace files manually instead of using the ADR purge policy.
- Assuming that disabling one client stops all maintenance jobs; each client is enabled separately.