Oracle Database Administration/Maintenance

This lesson introduces Oracle database maintenance.

Objectives and Skills edit

Objectives and skills for the database maintenance portion of Oracle Database Administration I certification include:^[1]

Database Maintenance
- Use and manage optimizer statistics
- Use and manage Automatic Workload Repository (AWR)
- Use advisory framework
- Manage Alerts and Thresholds

Use the following query to verify that automatic optimizer statistics collection is enabled:
SELECT * FROM DBA_AUTOTASK_CLIENT;
Use the following queries to determine when statistics were last updated for tables, indexes, and columns in the HR schema:
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR';
SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
Gather statistics for the HR.REGIONS table using the following query:
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'REGIONS');
Verify that statistics were updated using the following query:
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
Gather statistics for all objects in the HR schema using the following query:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
Verify that statistics were updated using the following queries:
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR';
SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
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:
EXEC DBMS_STATS.GATHER_DATABASE_STATS();

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

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

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