Oracle DBA Checklist
Oracle DBA Checklist
Oracle DBA Checklist
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>.
Index
I.
DAILY PROCEDURES
A.
B.
C.
D.
E.
F.
G.
H.
WEEKLY PROCEDURES
4
4
4
4
4
4
6
6
8
8
9
9
10
10
10
10
MONTHLY PROCEDURES
11
11
11
11
11
11
11
V. APPENDIX
A. DAILY PROCEDURES
B. NIGHTLY PROCEDURES
C. WEEKLY PROCEDURES
VI.
REFERENCES
12
12
15
17
22
I. Daily Procedures
A. 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.
B. 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/<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>.
C. Verify DBSNMP is running
1. Log on to each managed machine to check for the 'dbsnmp' process.
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".)
D. Verify success of database backup
E. Verify success of database archiving to tape
F. Verify enough resources for acceptable performance
1. Verify free space in tablespaces.
For each instance, verify that enough free space exists in each tablespace to
handle the days 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.
a) Go to each instance, run free.sql to check free mb in tablespaces.
Compare to the minimum free MB for that tablespace. Note any low-space
conditions and correct.
V. Appendix
A. Daily Procedures
1. Free.sql
--- free.sql
--- To verify free space in tablespaces
-- 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
2. Space.sql
--- 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
3. analyze5pct.sql
--- 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 ;
/
4. nr_extents.sql
--- nr_extents.sql
--- To find out any object reaching <threshold>
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
--- 11/30/98
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
5. spacebound.sql
--- spacebound.sql
--- 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 <foo> coalesce;).
-- Lastly, add another datafile to the tablespace if needed.
--- 11/30/98
10
11
B. Nightly Procedures
1. mk_volfact.sql
--- 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
(
table_name
VARCHAR2(30),
num_rows
NUMBER,
meas_dt
DATE
)
TABLESPACE platab
STORAGE (
INITIAL 128k
NEXT
128k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS unlimited
)
/
-- 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
/
2. analyze_comp.sql
--- analyze_comp.sql
-BEGIN
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
END ;
/
12
3. pop_vol.sql
--- pop_vol.sql
-insert into utl_vol_facts
select table_name
, 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
/
commit
/
13
C. Weekly Procedures
1. nextext.sql
--- nextext.sql
--- 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.
--- 11/30/98
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
2. existext.sql
--- existext.sql
--- 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 lot of different
-- sized extents, your free space is likely to become fragmented. If so,
-- this tablespace is a candidate for reorganizing.
--- 12/15/98
SELECT segment_name, segment_type
, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = '&OWNER'
GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent
14
3. No_pk.sql
--- no_pk.sql
--- To find tables without PK constraint
--- 11/2/98
SELECT table_name
FROM all_tables
WHERE owner = '&OWNER'
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = '&&OWNER'
AND constraint_type = 'P'
4. disPK.sql
--- disPK.sql
--- To find out which primary keys are disabled
--- 11/30/98
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = '&OWNER' AND status = 'DISABLED AND constraint_type = 'P'
5. nonuPK.sql
--- nonuPK.sql
--- To find tables with nonunique PK indexes. Requires that PK names
-- follow a naming convention. An alternative query follows that
-- does not have this requirement, but runs more slowly.
--- 11/2/98
SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name like '&PKNAME%'
15
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns@&my_db_link -- second environment
WHERE owner = '&OWNER2'
order by table_name, column_name
8. obj_coord.sql
--- obj_coord.sql
--- To find out any difference in objects between two instances
--- 12/08/98
SELECT object_name, object_type
FROM user_objects
MINUS
SELECT object_name, object_type
FROM user_objects@&my_db_link
________________________________________________________________
VI. References
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
17