宁夏电信帐务结算系统数据库日常维护文档v2[1].0

1、宁夏电信帐务结算系统数据库日常维护文档v2.0宁夏电信帐务结算系统数据库日常维护文档(v2.0) 宏智科技股份有限公司2003年4月资料版本：v2.0日 期：2003年4月密 级：公开资料 内部资料 保密资料 机密资料状 态：初稿 讨论稿 发布版权声明宏智科技股份有限公司20022002年版权所有，保留一切权利非经本公司书面许可，任何单位和个人不得擅自摘抄、复制本文的部分或全部，并不得以任何形式传播。copyright2002 by wholewise sci.&tech. co.,ltd.all right reserved.no part of this document may be r

2、eproduced or transmitted in any form or by any means without prior written consent of wholewise sci.&tech. co.,ltd. 文档控制记录修改记录日期作者版本修改记录2002-11-13刘高智1.0初稿2003/04/30程国谦1.1修订错误2003/05/05程国谦2.0增加dba所必须掌握的unix命令审阅姓名时间职位程国谦2003/04/25技术资源部经理分发拷贝no.姓名分发时间单位1张建雷2003/05/10宁夏电信公司2崔鹏2003/05/10宏智科技宁夏电信计费系统项目经理目

3、录1系统监控41.1cpu使用情况监控41.2内存使用情况监控41.3系统空间使用情况42oracle存储监控52.1检查oracle日志52.2检查数据库数据表空间的使用情况62.3检查表和索引的扩展72.3.1检查扩展数多于100的表和索引72.3.2检查不能扩展的数据库对象72.4检查回滚段使用情况72.4.1检查回滚段配置情况72.4.2检查回滚段争夺82.4.3检查回滚段的扩展83数据库性能监控83.1收集数据库运行信息83.2跟踪应用运行情况93.3定期对数据库对象进行分析93.4定期更新ops视图103.5检查数据库排序和临时段103.5.1检查内存排序的比例103.5.2检查临

4、时表空间的设置103.6检查重做日志文件使用情况113.6.1检查logswitch113.6.2检查重做日志文件空间请求113.6.3检查重做日志文件的latch113.7检查数据库的命中率113.7.1检查library cache命中率113.7.2检查data dictionary cache命中率113.7.3检查buffer cache命中率123.8v$resource_limit124数据库备份125附录a 脚本126附录b. 其它的一些常用脚本167附录c 常用的数据字典198附录d dba常用unix命令及配置文件221 系统监控1.1 cpu使用情况监控用vmstat命令

5、监控系统，命令输出如下；# vmstat procs memory page faults cpur b w avm free re at pi po fr de sr in sy cs us sy id0 0 0 32243 1091988 7 0 0 0 0 0 2 2040 3341 1387 10 1 89 注意：cpu选项的id值。该值太小表示cpu繁忙。可能存在cpu瓶颈或有不正常进程存在。可用top命令近一步监控占用资源大的进程。1.2 内存使用情况监控用vmstat 命令监控# vmstat procs memory page faults cpur b w avm free

6、re at pi po fr de sr in sy cs us sy id0 0 0 32243 1091988 7 0 0 0 0 0 2 2040 3341 1387 10 1 89注意：如果memory的free list（free项）持续增大，可能程序存在内存泄漏。page in（pi）和page out（po）持续为非零值时，表示存在内存瓶颈。1.3 系统空间使用情况用df -k命令检查系统使用空间：注意：检查每个文件系统的使用空间百分比（used），特别是根文件系统（/）、oracle文件系统（/oracle）和归档日志文件系统（/global/arch1,/global/arc

7、h2）。不要让使用空间超过80%。当oracle文件系统没有空余空间时，数据库会出现中断的情况。应定期用df（或df k）命令察看oracle文件系统的使用情况，保证使用率在80%以下。不要将数据库备份文件长期存放在oracle文件系统中。注意定期清除oracle的日志文件。若使用oracle并行数据库（ops），禁止在oracle文件系统中创建数据文件。如果是归档日志文件系统满，需要检查备份系统。如果数据库启动在archive log（归档）模式下，应定期察看archive log的文件系统（在计费周期里，建议每隔12个小时察看一次，根据我们的经验，象宁夏电信这样的集中计费系统，每小时生成的

8、日志文件估计在5gb左右，若不及时将归档日志文件备份出去，将导致数据库系统hang住），保证有足够的空间存放archive log文件。及时的备份archive log文件，并做及时的清理。数据库管理员应定期清理log文件、trc文件、和core文件。oracle的日志文件有： /oracle/app/oracle/admin/bill/budmp目录下的alert_bill.log文件（数据库日志文件） /oracle/app/oracle/admin/bill/budmp目录下的*.trc 文件（oracle后台进程产生的trace文件） /oracle/app/oracle/admin/

9、bill/udump目录下的*.trc 文件 （oracle错误时产生的trace文件） /oracle/app/oracle/admin/bill/cdump目录下的core_* (oracle core dump 文件) /oracle/app/oracle/product/8.1.7/dbs 目录下的core_* (oracle core dump 文件) /oracle/app/oracle/proudct/8.1.7/network/log目录下的listener.log （监听进程日志） /oracle/app/oracle/proudct/8.1.7/network/log目录下

10、的sqlnet.log （sqlnet日志）应特别注意的是listener.log文件，该文件存放在$oracle_home/network/log目录下。在监听进程配置不正确或监听进程出现错误时，log文件会增长得很快，应定期察看log文件的大小，并清空该文件，但该文件只能采用 $oracle_home/network/log/listener.log命令清空该文件而不能删除该文件。2 oracle存储监控2.1 检查oracle日志用vi命令查看数据库日志（/oracle/app/oracle/admin/bill/bdump/alert_bill.log文件）检查数据库的报错信息，尤其注

11、意ora-600错误、ora-07445错误、ora-01578错误等对系统影响较大的错误。对数据库的报错，应首先使用oerr命令查看该错误的解释和基本的应对手段。如：对错误ora-1562oerr ora 165201652, 00000, unable to extend temp segment by %s in tablespace %s/ *cause: failed to allocate an extent for temp segment in tablespace./ *action: use alter tablespace add datafile statement t

12、o add one or more/ files to the tablespace indicated我们可以很轻易的了解到由于磁盘空间不足导致该错误，可以通过增加数据文件或其他方式解决该故障。对于不能处理的报错，请将错误日志和trace文件mail给系统集成事业部、拨打宏智科技服务热线、通过oracle metalink网站（）开tar或拨打oracle 800服务热线 8008100366解决故障。错误的trace文件存放在/oracle/app/oracle/admin/bill/cdump或/oracle/app/oracle/admin/bill/bdump目录下。如：mon ap

13、r 15 20:01:44 2002errors in file /oracle/app/oracle/admin/bill/bdump/smon_11866_bill.trc:ora-00600: internal error code, arguments: 16224, , , , , , , 2.2 检查数据库数据表空间的使用情况使用如下的脚本检查表空间使用情况，相关脚本参见附录a。free_space.sql 检查表空间剩余空间。free_per_tp.sql 检查表空间剩余空间百分比。当表空间使用空间超过80%，建议进行垃圾表的清理或增加数据库文件扩大表空间大小。l ops环境下，

14、不要使用一般文件做数据库的数据文件，日志文件，控制文件。一定要用裸设备，裸设备的设备名是/dev/r，同时一定要注意数据文件大小一定要略小于裸设备大小。l 在增加或删除数据文件后，注意及时维护安装配置文档中的数据文件分配表。2.3 检查表和索引的扩展2.3.1 检查扩展数多于100的表和索引使用lagre_extent.sql检查扩展数多于100的表和索引。对于扩展数多的表和索引，建议：修改数据库对象的存储参数并重建该数据对象。2.3.2 检查不能扩展的数据库对象使用max_extent.sql检查因达到maxextent参数引起的表或索引不能扩展。使用no_space.sql检查因表空间的最

15、大空闲块小于表的扩展块引起的表或索引不能扩展。对于第一种情况，可暂时将maxextent设为unlimited或通过重建数据库对象的方式彻底解决该问题。对于第二种情况，应将next调小，将pctincrease设为0。以上两种错误，均应考虑对对象的存储策略进行修订，避免再次出现类似故障。2.4 检查回滚段使用情况2.4.1 检查回滚段配置情况使用roll_segs.sql脚本查看回滚段配置。回滚段配置及使用注意以下几点： 数据库有足够的回滚段。（见6.2） 回滚段使用独立的表空间。 每个回滚段的initial_extent和next_extent一样大，pct_increase为0。 每个回滚

16、段使用相同的存储策略 system回滚段例外 大事务使用少量大的回滚段，小事务使用大量小的回滚段2.4.2 检查回滚段争夺使用roll_cont.sql检查回滚段竞争。当结果大于1%时，需增加回滚段的数量。2.4.3 检查回滚段的扩展使用roll_extent.sql检查回滚段的扩展。如果大部分回滚段扩展次数较大。则： 在回滚段的存储参数中设置optimal值。optimal值必须大于minextent值。 增加回滚段的initial和next值。 增加回滚段表空间的大小。如果是某个回滚段扩展较大。则表明数据库中有较大的事务。建议建一个大的回滚段，用于大的事务。平时大的回滚段处于offline

17、状态，在大事务执行时，online大的回滚段。在sql语句中指明事务使用大的回滚段。3 数据库性能监控在日常维护过程中，应定时对数据库系统运行情况进行检查。例如定时检查数据库表空间使用情况；定时对数据库性能进行分析（使用utlbstat/utlestat脚本对数据库的数据库缓冲区命中率等进行监控）；定时对数据库的表和索引进行分析；定时对占用系统资源最多的数据库进程进行跟踪并反馈给应用开发人员等以保证数据库系统的运行正常与高效。3.1 收集数据库运行信息oracle提供了两个脚本utlbstat.sql和utlestat.sql用于收集数据库运行信息（包括数据库缓冲区命中率、磁盘读写分布等），在

18、数据库最忙的时候执行如下脚本收集信息：svrmgrl alter system set timed_statistics=true;svrmgrl ?/rdbms/admin/utlbstat.sql执行该语句2030分钟svrmgrl ?/rdbms/admin/utlestat.sql(?$oracle_home)svrmgrl alter system set timed_statistics=false;在当前目录下将生成一个report.txt文件，可以与在系统空闲或其他时间生成的文件进行比较，判断是否需要修改数据库初始化参数。3.2 跟踪应用运行情况数据库运行过程中可能由于应用sq

19、l语句不规范、索引失效或其他问题导致性能下降，响应速度变慢，可以通过如下方式对应用进行跟踪：svrmgrl ?/utlxplansvrmgrl select sid,serial# from v$session where username=&username;svrmgrl execute dbms_system.set_sql_trace_in_session(sid,serial#,true);执行该语句2030分钟左右svrmgrl execute dbms_system.set_sql_trace_in_session(sid,serial#,false);在$oracle_base

20、/admin/$oracle_sid/udump目录下将生成一个trc文件，可以使用如下命令将其转换为文本文件：$ tkprof out.trc out.txt explain=username/password sys=no3.3 定期对数据库对象进行分析定期对表进行分析有几点需要注意：u 系统表不能进行分析，一旦对系统表进行分析将导致数据库性能缓慢甚至产生其他问题，这是由于oracle对系统表的查询的优化方式和一般情况下的sql优化方式不同。u 需要对所有非系统的表全部进行分析。如果只分析部分表则可能导致数据库执行时选择错误的执行策略，导致数据库性能缓慢。u 由于目前计费帐务系统的应用方式

21、，建议每旬下帐后对所有的应用的表进行分析；但由于部分表记录数可能达到几千万条，这样一个晚上可能无法完成所有表的分析，因此建议采用采样分析。建议在应用部门的配合下，编写一个适合当地的分析脚本（大表采用采样分析10万条，小表则全部分析）并定期进行分析并记录分析时间及执行人以提高系统的运行效率。3.4 定期更新ops视图在ops环境下，建议每隔3/4周运行catparr.sql以刷新ops依赖的底层数据字典ext_to_obj视图。由于在ops的环境下，v$ping和v$cache是依据内部表ext_to_object的信息产生的。而表ext_to_obj是从ext_to_obj_view（由脚本c

22、atparr.sql产生的）视图产生的静态表。由此可知，ext_to_obj是静态的，它随时间而老化，这就引起为什么在v$ping和v$cache视图中某些对象的丢失，解决这个问题只有定期地运行catparr.sql，以刷新ext_to_obj内部表。3.5 检查数据库排序和临时段3.5.1 检查内存排序的比例用disk_sort.sql检查在磁盘排序的百分比。当比例大于5时，需增加sort_area_size值。3.5.2 检查临时表空间的设置临时表空间的设置应注意以下事项： 数据库中使用一个或多个临时表空间 在每个用户定义时指定临时表空间，不允许使用system表空间作为用户的临时表空间

23、将临时表空间的类型设为temporary。 临时表空间的存储参数设置。使用相同的initial和next值。设置pctincease为0，initial及next应为sort_area_size的整数倍3.6 检查重做日志文件使用情况3.6.1 检查logswitch查看alert_.log文件，看重做日志文件切换的间隔。如果间隔时间经常性的小于10分钟或经常出现诸如“checkpoint not complete”之类的提示则建议增大重做日志文件的大小。oracle建议重做日志文件切换的间隔时间应为2040分钟。3.6.2 检查重做日志文件空间请求用redo_space.sql查看。百分比应

24、小于1%，否则应增加log_buffers设置。3.6.3 检查重做日志文件的latch用redo_latch1.sql检查redolog的allocation latch。用redo_latch2.sql检查redolog的copy latch。如果百分比大于1%，则应增加db_block_lru_latches和log_buffers。3.7 检查数据库的命中率3.7.1 检查library cache命中率用lib_cache.sql查看library cache命中率。如果命中率miss大于1%，增大shared_pool_size。3.7.2 检查data dictionary ca

25、che命中率用dict_cache.sql查看data dictionary cache命中率。如果命中率miss大于15，增大shared_pool_size。3.7.3 检查buffer cache命中率用buffer_cache.sql查看buffer cache命中率。如果命中率小于95%，增大db_block_buffers。分析non_sys用户的表和索引经常分析非sys用户的表和索引，有利于数据库性能。3.8 v$resource_limit在oracle8版本中，双机之间的参数应该经常进行观察并做及时调整，否则可能导致客户端无法正常登录数据库服务器：svrmgrl select

26、 * from v$resource_limit;一旦发现参数max_allocate超过或接近initial_allocate设置，应及时调整相应参数4 数据库备份对于数据库备份来说，最佳的方式是使用自动备份系统（例如legato或veritas备份软件oracle rman磁带库）对数据库进行定时自动备份，但自动备份系统不能解决所有的问题，应使用exp命令对系统中的配置表等进行定期备份，以便于系统故障时的快速恢复：常用备份脚本如下：exp user/passwd file=exp.dmp log=exp.log buffer=1048576 compress=y 注意： 使用exp备份前一

27、定要检查数据库环境变量尤其是语言集设置是否有误（zhs16cgb231280或zhs16gbk）； 备份时一定要输出log文件以利于检查备份工作是否正常完成； 使用compressy选项有利于在导入时对数据块扩展进行优化。5 附录a 脚本 free_space.sql 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_chunksfrom dba_free_spacegroup

28、 by tablespace_name free_per_tp.sqselect 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_freefrom ( select tablespace_name, sum(blocks) as sum_alloc_blocksfrom dba_data_filesgroup by tablespace_na

29、me),( select tablespace_name as fs_ts_name,max(blocks) as largest_free_chunk,count(blocks) as nr_free_chunks,sum(blocks) as sum_free_blocksfrom dba_free_spacegroup by tablespace_name )where tablespace_name = fs_ts_name lagre_extent.sqlselect owner,segment_name,extents,bytes , max_extents,next_extent

30、 from dba_segments where segment_type in (table,index) and extents200 order by owner,segment_name; max_extent.sqlselect a.owner,a.segment_type,a.segment_name,a.tablespace_name from dba_segments a,dba_semgments bwhere a.extnets+1b.max_extents and a.owner=b.owner and a.segment_name=b.segment no_space.

31、sqlselect a.owner, a.segment_name, b.tablespace_name, decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100) nextext, freesp.largest from dba_extents a, dba_segments b, (select owner, segment_name, max(extent_id) extent_id, count(*) extents from dba_extents group by owner, segment_name )

32、 ext, (select tablespace_name, max(bytes) largest from dba_free_space group by tablespace_name ) freesp where a.owner=b.owner and a.segment_name=b.segment_name and a.owner=ext.owner and a.segment_name=ext.segment_name and a.extent_id=ext.extent_id and b.tablespace_name = freesp.tablespace_name and d

33、ecode(ext.extents,1,b.next_extent, bytes*(1+b.pct_increase/100) freesp.largest roll_segs.sqlselect segment_name，tablespace_name,initial_extent，next_extent，pct_increase from dba_rollback_segs roll_cont.sqlselect w.class, (sum(w.count)/sum(s.value) * 100from v$waitstat w , v$sysstat swhere w.class in

34、(system undo header,system undo block, undo header, undo block) and in (db block gets,consistent gets) group by w.class roll_extent.sqlselect ,b.extentsfrom v$rollname a,v$rollstat bwhere a.usn=b.usn disk_sort.sqlselect disk.value disk, mem.value mem,(disk.value/mem.value)*100 ratio fro

35、m v$sysstat mem, v$sysstat disk where mem.name = sorts (memory) and disk.name = sorts (disk) redo_space.sqlselect (a.value/b.value)*100from v$sysstat a,v$sysstat bwhere =redo buffer allocation retriesand =redo entries redo_latch1.sqlselect (misses/decode(gets,0,1,gets)*100 ratio1,(immedi

36、ate_misses/decode(immediate_misses+ immediate_gets,0,1, immediate_misses+immediate_gets)*100) ratio2from v$latch lwhere = redo allocation redo_latch2.sqlselect (misses/decode(gets,0,1,gets)*100 ratio1, (immediate_misses/decode(immediate_misses+ immediate_gets,0,1, immediate_misses+immediate_g

37、ets)*100) ratio2from v$latch lwhere = redo copy lib_cache.sqlselect sum(pins), sum(reloads),sum(reloads)/sum(pins)*100from v$librarycache dict_cache.sqlselect sum(gets), sum(getmisses),sum(getmisses)/sum(gets)*100from v$rowcache buffer_cache.sqlselectsum(decode(name, consistent gets,value, 0)

38、 consis gets,sum(decode(name, db block gets,value, 0) db blk gets,sum(decode(name, physical reads,value, 0) phys reads,(sum(decode(name, consistent gets,value, 0) +sum(decode(name, db block gets,value, 0) -sum(decode(name, physical reads,value, 0)/(sum(decode(name, consistent gets,value, 0) + sum(de

39、code(name, db block gets,value, 0) ) * 100 hit ratiofrom v$sysstatwhere name in (db block gets,consistent gets,physical reads)6 附录b. 其它的一些常用脚本 监控实例的等待select event,sum(decode(wait_time,0,0,1) prev, sum(decode(wait_time,0,1,0) curr,count(*) tot from v$session_wait group by event order by 4 监控当前数据库谁在运行

40、什么sql语句select osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece 查找使用cpu最多的用户sessionselect a.sid,spid,status,substr(gram,1,40) prog,terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and

41、c.sid=a.sid and a.paddr=b.addr order by value desc 查看锁信息select sn.username, m.sid, m.type, decode(m.lmode, 0, none, 1, null, 2, row share, 3, row excl., 4, share, 5, s/row excl.,6, exclusive, lmode, ltrim(to_char(lmode,990) lmode, decode(m.request,0, none, 1, null, 2, row share, 3, row excl., 4, sha

42、re, 5, s/row excl., 6, exclusive, request, ltrim(to_char(m.request, 990) request, m.id1, m.id2 from v$session sn, v$lock m where (sn.sid = m.sid and m.request != 0) or (sn.sid = m.sid and m.request = 0 and lmode != 4 and (id1, id2) in (select s.id1, s.id2 from v$lock s where request != 0 and s.id1 =

43、 m.id1 and s.id2 = m.id2) ) order by id1, id2, m.request 检查哪个用户锁住了表column username format a13 column object_name format a20 column machine format a10 select a.sid,a.type,a.id1,c.object_name,b.username,a.lmode,b.machine,d.spidfrom v$lock a,v$session b,all_objects c,v$process d where a.sid=b.sid and a

44、.type in (tm,tx) and c.object_id=a.id1 and b.paddr=d.addr order by username 查看前台正在发出的sql语句select user_name,sql_text from v$open_cursor where sid in (select sid from (select sid,serial#,username,program from v$sessionwhere status=active) 显示session的i/o情况set newpage 0set pagesize 58set line 132set feed

45、back offset verify offttitle offbtitle offclear breakscolumn sid format 99999 heading sidcolumn username format a20 heading usernamecolumn block_gets format 99,999,999,990 heading block getscolumn consistent_gets format 99,999,999,990 heading consistent getscolumn physical_reads format 99,999,999,99

46、0 heading physical readscolumn instance new_value _instance noprintcolumn today new_value _date noprintselect global_name instance, to_char(sysdate, dd-mon-yyyy) todayfrom global_name;ttitle onttitle left date run: _date skip 1- center session i/o skip 1 - center instance name: _instance skip 2selec

47、t a.sid, a.username, b.block_gets, b.consistent_gets, b.physical_readsfrom sys.v$session a, sys.v$sess_io bwhere a.sid = b.sidorder by a.username 当前会话长时间执行的sql语句select sid,sql_text from v$session_longops sl,v$sqltext st where sl.sql_address = st.address and sl.sql_hash_value = st.hash_value and sid

48、in(select sid from v$access)order by sid,piece 查看碎片程度高的表 select segment_name table_name , count(*) extents from dba_segments where owner not in (sys, system) group by segment_name having count(*) = (select max( count(*) ) from dba_segments group by segment_name) 查找当前用户下所有的索引 select user_indexes.tabl

49、e_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column

50、_position7 附录c 常用的数据字典select * from v$reserved_words 数据库的保留字 select * from v$instance 实例建立信息 select * from v$license 连接许可 select *from v$resource_limit 资源使用限制 select * from v$dblink 数据库连接 select *from v$pwfile_users 在密码文件中的用户 select * from v$option 数据库的应用选项 select* from v$compatibility 数据库的兼容性 selec

51、t *from v$fixed_table oracle数据字典 select * from v$fixed_view_definition oracle视图及定义 select * from v$indexed_fixed_column oracle索引的列 select * from sys.props$ 数据库nls格式 select * from v$tablespace 表空间名字及数量 select * from v$controlfile 控制文件名称及位置 select * from v$logfile 日志文件名称及位置 select * from v$datafile 数据

52、文件位置及当前状态 select * from v$backup 数据文件备份状态 select *from v$filestat 数据文件读、写状态 select * from v$archived_log 数据库当前所有归档日志 select * from v$archive_dest 归档日志路径及状态 select * from v$archive_processes 日志写进程状态 select * from v$rollname 回滚段名 select * from v$pollstat 回滚段状态 select * from v$sgastat sga分配状态 select * from v$sysstat 系统资源使用状态 selct * from v$librarycache 对象使用命中率 select * from v$reserved_words 数据库的保留字 select * from v$instance 实例建立信息 select * from v$license 连接许可 select *from v$resourc