DBA > Interview Resource

Oracle DBA Checklist

Part:   1  2  3  4 

(Continued from previous part...)

Oracle DBA Checklist


II. Nightly Procedures

Most production databases (and many development and test databases) will benefit from having certain nightly batch processes run.

A. Collect volumetric data
This example collects table row counts. This can easily be extended to other objects such as indexes, and other data such as average row sizes.
1. Analyze Schemas and Collect Data.
The idea here is to use the more time consuming and more accurate ANALYZE COMPUTE command and save the results, which show up in the data dictionary, to a more permanent store.
a) If you havent' yet, create the volumetrics table with mk_volfact.sql
b) To gather nightly sizing information, run analyze_comp.sql.
c) To collect the resulting statistics, run pop_vol.sql
d) Examine the data at your leisure, probably weekly or monthly.
I use MS Excel and an ODBC connection to examine and graph data growth.


III. Weekly Procedures


A. Look for objects that break rules
For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed.

1. Every object in a given tablespace should have the exact same size for NEXT_EXTENT, which should match the tablespace default for NEXT_EXTENT. As of 12/14/98, default NEXT_EXTENT for DATAHI is 1 gig (1048576 bytes), DATALO is 500 mb (524288 bytes), and INDEXES is 256 mb (262144 bytes).
a) To check settings for NEXT_EXTENT, run nextext.sql.
b) To check existing extents, run existext.sql

2. All tables should have unique primary keys.
a) To check missing PK, run no_pk.sql.
b) To check disabled PK, run disPK.sql.
c) All primary key indexes should be unique. Run nonuPK.sql to check.

3. All indexes should use INDEXES tablespace. Run mkrebuild_idx.sql.

4. Schemas should look identical between environments, especially test and production.
a) To check data type consistency, run datatype.sql.
b) To check other object consistency, run obj_coord.sql.
c) Better yet, use a tool like Quest Software's Schema Manager.


B. Look for security policy violations

C. Look in SQL*Net logs for errors, issues
1. Client side logs
2. Server side logs

D. Archive all Alert Logs to history

E. Visit home pages of key vendors

1. Oracle Corporation
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.oramag.com

2. Quest Software
http://www.quests.com
3. Sun Microsystems
http://www.sun.com

(Continued on next part...)

Part:   1  2  3  4