Oracle DBA checklist

Daily procedures

1. Verify all instances are up: run daily reports or via "Oracle Enterprise Manager's 'probe' event"
2. Look for any new alert log entries: Check ORA-errors in $ORACLE_BASE//bdump/alert_.log
3. Verify success of database backup
4. Verify success of database archiving to tape
5. Verify enough resource for acceptable performance:
   a. Verify free space in tablespaces (free.sql, space.sql)
   b. Verify rollback segment: should be ONLINE
   c. Identify bad growth projections:
   I. analyze5pct.sql
   II. nr_extents.sql
   III. Query current table sizing
   IV. Query current index sizing
   V. Query growth trends
   d. Identify space-bound objects (spacebound.sql)
   e. Review contention for CPU, RAM, network and disk resources.
6. Copy archived logs to standby database and roll forward: use automated scripts, verify daily

Nightly procedures

1. Collect volumetric data: mk_volfact.sql, analyze_comp.sql, pop_vol.sql

Weekly procedures

1. Look for objects that break rules
   a. Check NEXT_EXTENT: nextext.sql, check existing extents: existext.sql
   b. Check missing PK: no_pk.sql, check disabled PK: disPK.sql, check PK indexes uniqueness: nonuPK.sql
   c. All indexes should use INDEXES tablespace: mkrebuild_idx.sql
   d. Check schema objects consistency between test and production: datatype.sql, obj_coord.sql, Quest Schema Manager.
2. Look for security policy violations
3. Look in SQL*Net logs for errors, issues
   a. Check client side logs
   b. Check server side logs
4. Archive all alert logs to history

Montly procedures

1. Look for harmful growth rates
2. Review tuning opportunities: cache hit ratio, latch contention etc.
3. Look for I/O contention
4. Review fragmentation: row chaining etc.
5. Project performance into the future
6. Perform tuning and maintenance