Oracle DBA Checklist
Version 1.4 Generic
Authors:Thomas B. Cox, with Christine Choi
Purpose: This document gives details for performing daily, weekly, and monthly checks of the status of one or more Oracle databases. All SQL and PL/SQL code for the listed checks can be found in the appendix.
The latest version of this paper should always be available on the primary author's home page, .
Change Notes:1.1: Typo in 'existext.sql' identified by Steve DeNunzio, fixed 1.2: Typos fixed
3. Gnu Public License added; pctincr 0 in rebuild index added 4. Added pointer to newest version on Geocities home page, http://www.geocities.com/tbcox23 Fixed pointer to 'orapub' web site
Added nightly checklist and volumetrics
Support Information (customize for your site):
This paper was inspired by the work of David Cook (see References), and Version 1.0 was largely fleshed out by Christine Choi of Hewlett-Packard (Components Group), San Jose, California. I am grateful to both for their contributions to this document.
Please send your corrections, suggestions, and feedback to me at the address below, with your return address so I may credit your contribution. Thank you.
-Thomas B. Cox,
Copyright © 1999, 2000 Thomas B. Cox, All Rights Reserved.
This document is free; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This document is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
For a copy of the GNU General Public License write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. Index
A.Verify all instances are up3
B.Look for any new alert log entries3
C.Verify DBSNMP is running3
D.Verify success of database backup3
E.Verify success of database archiving to tape3
F.Verify enough resources for acceptable performance3
G.Copy Archived Logs to Standby Database and Roll Forward5 H.Read DBA manuals for one hour5
A.Collect volumetric data6
A.Look for objects that break rules7
B.Look for security policy violations7
C.Look in SQL*Net logs for errors, issues7
D.Archive all Alert Logs to history7
E.Visit home pages of key vendors8
A.Look for Harmful Growth Rates9
B.Review Tuning Opportunities9
C.Look for I/O Contention9
E.Project Performance into the Future9
F.Perform Tuning and Maintenance9
1 Verify all instances are up
Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this. Optional implementation: use Oracle Enterprise Manager's 'probe' event.
2 Look for any new alert log entries
• Connect to each managed system.
• Use 'telnet' or comparable program.
• For each managed instance, go to the background dump destination, usually $ORACLE_BASE//bdump. Make sure to look under each managed database's SID. • At the prompt, use the Unix ‘tail’ command to see the alert_.log, or otherwise examine the most recent entries in the file. • If any ORA-errors have appeared since the previous...