Oracle Database Administration/Maintenance

This lesson introduces Oracle database maintenance.

Objectives and Skills
Objectives and skills for the database maintenance portion of Oracle Database Administration I certification include:
 * Database Maintenance
 * Use and manage optimizer statistics
 * Use and manage Automatic Workload Repository (AWR)
 * Use advisory framework
 * Manage Alerts and Thresholds

Readings

 * 1) Oracle: Database 2-Day DBA Monitoring and Tuning the Database
 * 2) Oracle: Database 2-Day + Performance Tuning Guide Oracle Database Performance Method
 * 3) Oracle: Database 2-Day + Perforamce Tuning Guide Automatic Database Performance Monitoring

Multimedia

 * 1) YouTube: Oracle Performance Tuning - Oracle Enterprise Manager - AWR

Tutorial

 * 1) Complete the tutorial Oracle: Monitoring and Tuning the Database.

Optimizer Statistics

 * 1) Use the following query to verify that automatic optimizer statistics collection is enabled:
 * 2) Use the following queries to determine when statistics were last updated for tables, indexes, and columns in the HR schema:
 * 3) Gather statistics for the HR.REGIONS table using the following query:
 * 4) Verify that statistics were updated using the following query:
 * 5) Gather statistics for all objects in the HR schema using the following query:
 * 6) Verify that statistics were updated using the following queries:
 * 7) If you want to manually update statistics for all objects in the database, you can use the following query, but note that it may take a long time to run and will impact database performance while it runs:

Automatic Workload Repository (AWR)

 * 1) Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Snapshots to select a snapshot. Review the corresponding snapshot report.
 * 2) Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Edit to view options for snapshot collection and retention. If desired, modify snapshot settings.

Advisory Framework

 * 1) Use Enterprise Manager Database Control / Advisor Central to view available advisors and tasks. View the current status for each advisor.
 * 2) Use Enterprise Manager Database Control / Server / Automated Maintenance Tasks to view the task schedule. Configure changes if desired.

Alerts and Thresholds

 * 1) Use Enterprise Manager Database Control / Metric and Policy Settings to view current metric thresholds and policies.
 * 2) Use Enterprise Manager / Setup to view and configure notification methods.