Ken - 14 miles, you dawg! I'm jealous. Here I am humping 30 miles across the
major metro area. Seriously, learn as much as you can from the departing
person, because even if you have access to that DBA in the future, it is
amazing how fast they can forget details.
Robert - I think you have excellent points about learning everything about
their backup and recovery procedures. That reminds me that a good question
would be to ask about their test recovery and the procedure they used. :-)
Ken - Here is an audit form I've prepared to investigate various aspects of
a database. It may be help you ask more questions. If you use it and any
improvements occur to you, be sure to pass them along.
Oracle Database Audit
Server: __________ Instance Name: ______________ Date:
_____________
Test / Production
Backup / Recovery Audit
Archive: Y/N show parameter log_archive_start
Control file placement: number ___ separate devices Y/N select * from
v$controlfile;
Date of last backup controlfile to trace: show parameter user_dump_dest
Log file: size ____ number _____ groups _____ separate devices Y/N select *
from v$logfile, v$log;
Backup: schedule ____________ RMAN Y/N RMAN validation commands
Exports: schedule ____________ location:________ Date of last export,
________ errors in log? Y/N
Temp tablespace: select username, temporary_tablespace from dba_users; Are
any system? Y/N
Default tablespace: select username, default_tablespace from dba_users; Are
any system? Y/N
Usernames owning tables: __________________ select distinct owner from
dba_tables;
LogMiner: show parameter utl_file_dir ___________________
Security Audit
DBA privilege select grantee from dba_role_privs where
granted_role = 'DBA';
Default passwords
List of schemas, responsible person, # of processes (activity)
select username, count(*) from v$session group by username;
Performance Audit
When are the critical performance times for this database?
_______________________________
Attach STATSPACK report from a peak time.
TIMED_STATISTICS = true/false show parameter timed_statistics
Table statistics are stored: ________________ for future diagnosis in case
CBO chooses new plans.
Shared Pool
What is the block size? __________ show parameter db_block_size;
What is the shared pool size?____________ show parameter shared_pool_size;
What is the library-cache hit ratio? __________ goal 99%+ (from STATSPACK
report, first page)
What is the dictionary hit ratio? ___________ goal 99%+ (from STATSPACK,
Dictionary Cache Stats)
What is the JAVA_POOL_SIZE? _________
What is the LARGE_POOL_SIZE? _________
Buffer Cache
What is the BHR? __________ (from STATSPACK report, first page)
What is db_block_buffers? __________ (from STATSPACK report, first page)
Keep pool: show parameter buffer_pool_keep ________
Recycle pool: show parameter buffer_pool_recycle _______
What are the hit ratios for all buffer pools? V$BUFFER_POOL_STATISTICS.
Statistics for increasing buffers - 8i V$RECENT_BUCKET, 9i V$DB_CACHE_ADVICE
Which tables and indexes are assigned to KEEP, RECYCLE?
select owner, table_name, buffer_pool from dba_tables; dba_indexes;
What are the sum of blocks of the objects assigned to the KEEP pool? As a %
of KEEP pool size?
select sum(blocks) from dba_tables where buffer_pool = 'KEEP';
Number of LRU_LATCHES? _______ show parameter db_block_lru_latches;
Redo Log Buffer
What is the log buffer size? ___________ show parameter log_buffer;
At what time interval are log switches occuring? ________ goal: 20min.
Look for "log buffer space%" in v$session_wait
In v$sysstat, look for "redo buffer allocation retries", "redo log space
requests"
Are there waits for the redo allocation latch?
File I/O
Are all temporary tablespaces correctly defined?
select tablespace_name, file_name, autoextensible from dba_temp_files;
select tablespace_name, maxextents from dba_tablespaces order by
tablespace_name;
List objects in SYSTEM tablespace that are not owned by SYS:
select segment_name, segment_type, owner from dba_segments where owner <>
'SYS' and tablespace_name = 'SYSTEM';
Do DATA tablespaces contain only tables?
select segment_name, segment_type, owner, tablespace_name from dba_segments
where tablespace_name like '%DATA%' and segment_type <> 'TABLE'
Do INDEX tablespaces contain only indexes?
select segment_name, segment_type, owner, tablespace_name from dba_segments
where tablespace_name like '%INDEX%' and segment_type <> 'INDEX'
Do ROLLBACK tablespaces contain only rollback segments?
select segment_name, segment_type, owner, tablespace_name from dba_segments
where tablespace_name like '%RBS%' and segment_type <> 'ROLLBACK';
I/O conflicts - priorities
1. Are redo logs on separate devices from any other tablespaces? select
member from v$logfile;
2. Are rollback tablespace datafiles on separate devices from any other
tablespaces?
3. Are DATA and INDEX tablespace datafiles on separate devices from SYSTEM
tablespaces?
4. Are DATA and INDEX tablespace datafile separate
Chained Rows - ANALYZE all tables,
select owner, table_name, num_rows, chain_cnt from dba_tables where chain_>
num_rows*0.01
Sorts: In-memory Sort Ratio from STATSPACK ______
Sort Area Size ________ show parameter sort_area_size;
Sort direct writes? Y/N
Size of TEMP tablespace: select bytes/1024 from dba_temp_files;
Rollback segments: size, number, extents, maxextents from
v$rollstat_________________
#/users per rollback segment ____________
Date of oldest user table/index analyze:
select owner, min(last_analyzed) from dba_tables group by owner;
dba_indexes
Tablespaces defined as LMT and uniform extents
Are redo logs stored on RAID5 disk? Y/N
TEMP tablespace NEXT size _____ SORT_AREA_SIZE ________
Is the NEXT extent size of the TEMP tablespace a multiple of SORT_AREA_SIZE?
Actions from audit:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
