没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
Oracle DBA Checklist
Version 1.4 Generic
Revised: 23-Aug-2000
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, <tbcox@att.net>
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
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
2
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
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.
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.
3
b) Go to each instance, run space.sql to check percentage free in tablespaces.
Compare to the minimum percent free for that tablespace. Note any low-space conditions
and correct.
2. Verify rollback segment.
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.
a) Optional: each database may have a list of rollback segment names and their
expected statuses.
b) For current status of each ONLINE or FULL rollback segment (by ID not by
name), query on V$ROLLSTAT.
c) For storage parameters and names of ALL rollback segment, query on
DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than
V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses,
showing these as OFFLINE and ONLINE respectively.
3. Identify bad growth projections.
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.
a) To gather daily sizing information, run analyze5pct.sql. If you are collecting
nightly volumetrics, skip this step.
b) To check current extents, run nr_extents.sql
c) Query current table sizing information
d) Query current index sizing information
e) Query growth trends
4. Identify space-bound objects.
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.
4
剩余16页未读,继续阅读
资源评论
plutoyeh
- 粉丝: 0
- 资源: 8
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功