Oracle
DBA Checklist
Version 1.4 Generic
Revised:
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, <http://www.geocities.com/tbcox23>.
Change Notes: 1.1:
Typo in 'existext.sql' identified by Steve
DeNunzio, fixed
1.2:
Typos fixed
1.3 Gnu Public License added;
pctincr 0 in rebuild index added
1.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):
Help
Desk: <phone>
Physical
DBA: <name> <phone>
Application
DBA: <name> <phone>
Oracle
Support: CSI: <#> <phone>
Acknowledgements:
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, <[email protected]>
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.,
Index
I. Daily
Procedures 3
A. Verify all instances are up 3
B. Look for any new alert log entries 3
C. Verify DBSNMP is running 3
D. Verify success of database backup 3
E. Verify success of database archiving to
tape 3
F. Verify enough resources for acceptable
performance 3
G. Copy Archived Logs to Standby Database and
Roll Forward 5
H. Read DBA manuals for one hour 5
II. Nightly Procedures 6
A. Collect volumetric data 6
III. Weekly Procedures 7
A. Look for objects that break rules 7
B. Look for security policy violations 7
C. Look in SQL*Net logs for errors, issues 7
D. Archive all Alert Logs to history 7
E. Visit home pages of key vendors 8
IV. Monthly Procedures 9
A. Look for Harmful Growth Rates 9
B. Review Tuning Opportunities 9
C. Look for I/O Contention 9
D. Review Fragmentation 9
E. Project Performance into the Future 9
F. Perform Tuning and Maintenance 9
V. Appendix 10
A. Daily Procedures 10
B. Nightly Procedures 12
C. Weekly Procedures 14
VI. References 17
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.
· Connect to each managed system.
· Use 'telnet' or comparable program.
· For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump. Make sure to look under each managed database's SID.
· At the prompt, use the Unix ‘tail’ command to see the alert_<SID>.log, or otherwise examine the most recent entries in the file.
· If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in <file>.
For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of <date>, the minimum free space for <repeat for each tablespace>: [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least <time to order, get, and install more disks> days’ data growth.
Compare to the minimum free MB for that
tablespace. Note any low-space
conditions and correct.
Compare to the minimum percent free for that
tablespace. Note any low-space
conditions and correct.
Status should be ONLINE, not OFFLINE or FULL, except
in some cases you may have a special rollback segment for large batch jobs
whose normal status is OFFLINE.
Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
Space-bound objects’ next_extents are bigger than
the largest extent that the tablespace can offer. Space-bound objects can harm database
operation. If we get such object, first
need to investigate the situation. Then
we can use ALTER TABLESPACE <tablespace> COALESCE. Or add another datafile.
If you have a Standby Database, copy the appropriate
Archived Logs to the expected location on the standby machine and apply those
logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.
The copying of logs, the applying of them, or both,
can in some cases be automated. If you
have automated them, then your daily task should be to confirm that this
happened correctly each day.
Nothing is more valuable in the long run than that
the DBA be as widely experienced, and as widely read, as possible.
Most production databases (and many development and
test databases) will benefit from having certain nightly batch processes run.
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.
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.
I use MS Excel and an ODBC connection to examine and graph data growth.
For each object-creation policy (naming convention,
storage parameters, etc.) have an automated check to verify that the policy is
being followed.
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.oramag.com
http://www.quests.com
http://www.sun.com
--
-- free.sql
--
-- Minimum amount of
free space
-- document your
thresholds:
--
<tablespace_name> = <amount> m
--
SELECT
tablespace_name, sum ( blocks ) as free_blk ,
trunc ( sum ( bytes ) / (1024*1024) ) as free_m
, max ( bytes ) / (1024) as
big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name
--
-- space.sql
--
-- To check free, pct_free, and allocated space within a
tablespace
--
-- 11/24/98
SELECT tablespace_name, largest_free_chunk
, nr_free_chunks,
sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM
dba_data_files
GROUP BY
tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP
BY tablespace_name )
WHERE tablespace_name = fs_ts_name
--
-- analyze5pct.sql
--
-- To analyze tables and indexes quickly, using a 5% sample
size
-- (do not use this script if you are performing the
overnight
-- collection of volumetric data)
--
-- 11/30/98
BEGIN
dbms_utility.analyze_schema ( '&OWNER',
'ESTIMATE', NULL, 5 ) ;
END ;
/
1. Loney, Kevin Oracle8 DBA Handbook
2.
Cook, David
Database Management from Crisis to Confidence
[http://www.orapub.com/]
3. Cox, Thomas B. The Database Administration Maturity Model