Oracle DBA 日常管理

Oracle DBA 日常管理 
=========================================================== 
作者: shwenwen(http://shwenwen.itpub.net)
发表于: 2007.12.18 16:47
分类: oracle数据库 
出处: http://shwenwen.itpub.net/post/34911/443025
--------------------------------------------------------------- 

目的：这篇文档有很详细的资料记录着对一个甚至更多的 ORACLE 数据库每天的，每月的， 
每年的运行的状态的结果及检查的结果，在文档的附录中你将会看到所有检查，修改的 SQL 
和 PL/SQL 代码。

Oracle DBA 日常管理 
目的：这篇文档有很详细的资料记录着对一个甚至更多的 ORACLE 数据库每天的，每月的， 
每年的运行的状态的结果及检查的结果，在文档的附录中你将会看到所有检查，修改的 SQL 
和 PL/SQL 代码。 
目录 
1. 日常维护程序 
A ． 检查已起的所有实例 
B ． 查找一些新的警告日志 
C ． 检查 DBSNMP 是否在运行 
D ． 检查数据库备份是否正确 
E ． 检查备份到磁带中的文件是否正确 
F ． 检查数据库的性能是否正常合理，是否有足够的空间和资源 
G ． 将文档日志复制到备份的数据库中 
H ． 要常看 DBA 用户手册 
2. 晚间维护程序 
A ．收集 VOLUMETRIC 的数据 
3. 每周维护工作 
A ． 查找那些破坏规则的 OBJECT 
B ． 查找是否有违反安全策略的问题 
C ． 查看错误地方的 SQL*NET 日志 
D ． 将所有的警告日志存档 
E ． 经常访问供应商的主页 
4. 月维护程序 
A ． 查看对数据库会产生危害的增长速度 
B ． 回顾以前数据库优化性能的调整 
C ． 查看 I/O 的屏颈问题 
D ． 回顾 FRAGMENTATION 
E ． 将来的执行计划 
F ． 查看调整点和维护 
5. 附录 
A ． 月维护过程 
B ． 晚间维护过程 
C ． 周维护过程 

---------------------------------------------------------------- 
一．日维护过程 
A ．查看所有的实例是否已起 
确定数据库是可用的，把每个实例写入日志并且运行日报告或是运行测试 
文件。当然有一些操作我们是希望它能自动运行的。 
可选择执行：用 ORACLE 管理器中的‘ PROBE' 事件来查看 
B ．查找新的警告日志文件 
1. 联接每一个操作管理系统 
2. 使用‘ TELNET' 或是可比较程序 
3. 对每一个管理实例，经常的执行 $ORACLE_BASE/<SID>/bdump 操 
作，并使其能回退到控制数据库的 SID 。 
4. 在提示下，使用 UNIX 中的‘ TAIL '命令查看 alert_<SID>.log ，或是 
用其他方式检查文件中最近时期的警告日志 
5. 如果以前出现过的一些 ORA_ERRORS 又出现，将它记录到数据库 
恢复日志中并且仔细的研究它们，这个数据库恢复日志在〈 FILE 〉中 
C ．查看 DBSNMP 的运行情况 
检查每个被管理机器的‘ DBSNMP' 进程并将它们记录到日志中。 
在 UNIX 中，在命令行中，键入 ps –ef | grep dbsnmp, 将回看到 2 个 
DBSNMP 进程在运行。如果没有，重启 DBSNMP 。 
D ．查数据库备份是否成功 
E ．检查备份的磁带文档是否成功 
F ．检查对合理的性能来说是否有足够的资源 
1. 检查在表空间中有没有剩余空间。 
对每一个实例来说，检查在表空间中是否存在有剩余空间来满足当天 
的预期的需要。当数据库中已有的数据是稳定的，数据日增长的平均 
数也是可以计算出来，最小的剩余空间至少要能满足每天数据的增 长。 
A ） 运行‘ FREE.SQL' 来检查表空间的剩余空间。 
B ） 运行‘ SPACE.SQL' 来检查表空间中的剩余空间百分率 
2. 检查回滚段 
回滚段的状态一般是在线的，除了一些为复杂工作准备的专用 段，它一般状态是离线的。 
a) 每个数据库都有一个回滚段名字的列表。 
b) 你可以用 V$ROLLSTAT 来查询在线或是离线的回滚段的现在状 态 . 
c) 对于所有回滚段的存储参数及名字， 可用 
DBA_ROLLBACK_SEGS 来查询。但是它不如 V$ROLLSTAT 准确。 
3. 识别出一些过分的增长 
查看数据库中超出资源或是增长速度过大的段，这些段的存储参 数需要调整。 
a ） 收集日数据大小的信息， 可以用 
‘ ANALYZE5PCT.SQL '。如果你收集的是每晚的信息， 则可跳过这一步。 
b ） 检查当前的范围，可用‘ NR.EXTENTS.SQL' 。 
c ） 查询当前表的大小信息。 
d ） 查询当前索引大小的信息。 
e ） 查询增长趋势。 
4. 确定空间的范围。 
如果范围空间对象的 NEXT_EXTENT 比表空间所能提供的最大范 
围还要大，那么这将影响数据库的运行。如果我们找到了这个目标，可 
以用‘ ALTER TABLESPACE COALESCE' 调查它的位置，或加另外 的数据文件。 
A ）运行‘ SPACEBOUND.SQL' 。如果都是正常的，将不返回任何行。 
5. 回顾 CPU ，内存，网络，硬件资源论点的过程 
A ）检查 CPU 的利用情况，进到 x:webphase2default.htm =>system 
metrics=>CPU 利用页， CPU 的最大限度为 400 ，当 CPU 的占用保持 
在 350 以上有一段时间的话，我们就需要查看及研究出现的问题。 
G ．将存档日志复制到备用数据库中 
如果有一个备用数据库，将适当的存档日志复制到备用数据库的期望 
位置，备用数据库中保存最近期的数据。 
H. 经常查阅 DBA 用户手册 
如果有可能的话，要广泛的阅读，包括 DBA 手册，行业杂志，新闻 组或是邮件列表。 
------------------------------------------------------------- 
二．晚间维护过程 
大部分的数据库产品将受益于每晚确定的检查进程的运行。 
A. 收集 VOLUMETRIC 数据 
1. 分析计划和收集数据 
更准确的分析计算并保存结果。 
a ） 如果你现在没有作这些的话，用‘ MK VOLFACT.SQL' 来创建测定体积的 表。 
b ） 收集晚间数据大小的信息，用‘ ANALYZE COMP.SQL' 。 
c ） 收集统计结果，用‘ POP VOL.SQL' 。 
d ） 在空闲的时候检查数据，可能的话，每周或每个月进行。 
我是用 MS EXCEL 和 ODBC 的联接来检查数据和图表的增长 
------------------------------------------------------------- 
三．每周维护过程 
A ． 查找被破坏的目标 
1. 对于每个给定表空间的对象来说， NEXT_EXTENT 的大小是相同的，如 
12/14/98 ，缺省的 NEXT_EXTENT 的 DATAHI 为 1G ， DATALO 为 500MB ， 
INDEXES 为 256MB 。 
A ） 检查 NEXT_EXTENT 的设置，可用‘ NEXTEXT 。 SQL' 。 
B ） 检查已有的 EXTENTS ，可用‘ EXISTEXT 。 SQL' 。 
2. 所有的表都应该有唯一的主键 
a ） 查看那些表没有主键，可用‘ NO_PK.SQL' 。 
b ） 查找那些主键是没有发挥作用的，可用‘ DIS_PK.SQL' 。 
c ） 所有作索引的主键都要是唯一的，可用‘ NONUPK 。 SQL' 来检 查。 
3. 所有的索引都要放到索引表空间中。运行‘ MKREBUILD_IDX 。 SQL' 
4. 不同的环境之间的计划应该是同样的，特别是测试环境和成品环境之间的 计划应该相同。 
a ） 检查不同的 2 个运行环境中的数据类型是否一致，可用 
‘ DATATYPE.SQL '。 
b ） 在 2 个不同的实例中寻找对象的不同点， 可用 
‘ OBJ_COORD.SQL '。 
c ） 更好的做法是，使用一种工具，象寻求软件的计划管理器那样的 工具。 
B ． 查看是否有危害到安全策略的问题。 
C ． 查看报错的 SQL*NET 日志。 
1. 客户端的日志。 
2. 服务器端的日志。 
D ． . 将所有的警告日志存档 
E ． . 供应商的主页 
1. ORACLE 供应商 
http://www.oracle.com 
http://technet.oracle.com 
http://www.oracle.com/support 
http://www.oramag.com 
2. Quest Software 
http://www.quests.com 
3. Sun Microsystems 
http://www.sun.com 
---------------------------------------------------------------- 
四．月维护过程 
A ．查看对数据库会产生危害的增长速度 
1. 从以前的记录或报告中回顾段增长的变化以此来确定段增长带来危害 
B ． 回顾以前数据库优化性能的调整 
1. 回顾一般 ORACLE 数据库的调整点，比较以前的报告来确定有害的发展 趋势。 
C ． 查看 I/O 的屏颈问题 
1. 查看前期数据库文件的活动性，比较以前的输出来判断有可能导致屏颈 问题的趋势。 
D ． 回顾 FRAGMENTATION 
E ． 计划数据库将来的性能 
1. 比较 ORACLE 和操作系统的 CPU ，内存，网络，及硬盘的利用率以此 
来确定在近期将会有的一些资源争夺的趋势 
2. 当系统将超出范围时要把性能趋势当作服务水平的协议来看 
F ． 完成调整和维护工作 
1. 使修改满足避免系统资源的争夺的需要，这里面包括增加新资源或使预期 的停工。 
---------------------------------------------------------------- 
五．附录 
A. 日常程序 
-- 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 
1. 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 
2. 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 ; 
/ 
3. 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 
4. 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 
SELECT a.table_name, a.next_extent, a.tablespace_name 
FROM all_tables a, 
( SELECT tablespace_name, max(bytes) as big_chunk 
FROM dba_free_space 
GROUP BY tablespace_name ) f 
WHERE f.tablespace_name = a.tablespace_name 
AND a.next_extent > f.big_chunk 
B. 每晚处理程序 
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 ; 
/ 
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 
/ 
C. 每周处理程序 
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 
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%' 
AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE' 
SELECT c.constraint_name, i.tablespace_name, i.uniqueness 
FROM all_constraints c , all_indexes i 
WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE' 
AND c.constraint_type = 'P' AND i.index_name = c.constraint_name 
6. mkrebuild_idx.sql 
-- 
-- mkrebuild_idx.sql 
-- 
-- Rebuild indexes to have correct storage parameters 
-- 
-- 11/2/98 
SELECT 'alter index ' || index_name || ' rebuild ' 
, 'tablespace INDEXES storage ' 
|| ' ( initial 256 K next 256 K pctincrease 0 ) ; ' 
FROM all_indexes 
WHERE ( tablespace_name != 'INDEXES' 
OR next_extent != ( <span