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
1.3 Gnu Public License added; pctincr 0 in rebuild index added
1.4 Added pointer to newest version on Geocities home page,
Fixed pointer to 'orapub' web site
Added nightly checklist and volumetrics
Support Information (customize for your site):
Oracle Support: CSI: <#>
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.
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/
· At the prompt, use the Unix ‘tail’ command to see the alert_
· 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
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
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
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. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.
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.
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
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
GROUP BY tablespace_name
-- To check free, pct_free, and allocated space within a tablespace
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') || '%'
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
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
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
-- 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)
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
-- To find out any object reaching
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace
-- Lastly, add another datafile to the tablespace if needed.
SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk
-- mk_volfact.sql (only run this once to set it up; do not run it nightly!)
-- -- Table UTL_VOL_FACTS
CREATE TABLE utl_vol_facts
-- Public Synonym
CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts
-- Grants for UTL_VOL_FACTS
GRANT SELECT ON utl_vol_facts TO public
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
insert into utl_vol_facts
, NVL ( num_rows, 0) as num_rows
, trunc ( last_analyzed ) as meas_dt
from all_tables -- or just user_tables
where owner in ('&OWNER') -- or a comma-separated list of owners
-- To find tables that don't match the tablespace default for NEXT extent.
-- The implicit rule here is that every table in a given tablespace should
-- use the exact same value for NEXT, which should also be the tablespace's
-- default value for NEXT.
-- This tells us what the setting for NEXT is for these objects today.
SELECT segment_name, segment_type, ds.next_extent as Actual_Next
, dt.tablespace_name, dt.next_extent as Default_Next
FROM dba_tablespaces dt, dba_segments ds
WHERE dt.tablespace_name = ds.tablespace_name
AND dt.next_extent !=ds.next_extent
AND ds.owner = UPPER ( '&OWNER' )
ORDER BY tablespace_name, segment_type, segment_name
-- To check existing extents
-- This tells us how many of each object's extents differ in size from
-- the tablespace's default size. If this report shows a
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84353/，如需转载，请注明出处，否则将追究法律责任。