1、Oracle10g数据库日常维护手册目 录1. 检查数据库基本状况 41.1. 检查 ORACLE实例状态 41.2. 检查 ORACLE服务进程 41.3. 检查 ORACLE监听状态 52. 检查系统和 ORACLE日志文件 .62.1. 检查操作系统日志文件 62.2. 检查 ORACLE日志文件 62.3. 检查 ORACLE核心转储目录 72.4. 检查 ROOT用户和 ORACLE用户的 EMAIL.73. 检查 ORACLE对象状态 .73.1. 检查 ORACLE控制文件状态 73.2. 检查 ORACLE在线日志状态 83.3. 检查 ORACLE表空间的状态 83.4. 检
2、查 ORACLE所有数据文件状态 83.5. 检查无效对象 93.6. 检查所有回滚段状态 .104. 检查 ORACLE相关资源的使用情况 104.1. 检查 ORACLE初始化文件中相关参数值 .104.2. 检查数据库连接情况 .114.3. 检查系统磁盘空间 .124.4. 检查表空间使用情况 .124.5. 检查一些扩展异常的对象 .134.6. 检查 SYSTEM表空间内的内容 .144.7. 检查对象的下一扩展与表空间的最大扩展值 .145. 检查 ORACLE数据库备份结果 145.1. 检查数据库备份日志信息 .155.2. 检查 BACKUP卷中文件产生的时间 .155.3
3、. 检查 ORACLE用户的 EMAIL 156. 检查 ORACLE数据库性能 156.1. 检查数据库的等待事件 .156.2. DISK READ最高的 SQL语句的获取 156.3. 查找前十条性能差的 SQL166.4. 等待时间最多的 5个系统等待事件的获取 .166.5. 检查运行很久的 SQL .166.6. 检查消耗 CPU最高的进程 .166.7. 检查碎片程度高的表 .176.8. 检查表空间的 I/O 比例 176.9. 检查文件系统的 I/O 比例 176.10. 检查死锁及处理 .176.11. 检查数据库 CPU、I/O、内存性能 .186.12. 查看是否有僵死
4、进程 .196.13. 检查行链接/迁移 196.14. 定期做统计分析 .196.15. 检查缓冲区命中率 .206.16. 检查共享池命中率 .206.17. 检查排序区 .206.18. 检查日志缓冲区 .217. 检查数据库安全性 .217.1. 检查系统安全日志信息 .217.2. 检查用户修改密码 .218. 其他检查 .228.1. 检查当前 CRONTAB任务是否正常 228.2. ORACLE JOB是否有失败 228.3. 监控数据量的增长情况 .228.4. 检查失效的索引 .238.5. 检查不起作用的约束 .238.6. 检查无效的 TRIGGER 23巡检内容1.
5、检查数据库基本状况在本节中主要对数据库的基本状况进行检查，其中包含：检查 Oracle实例状态，检查Oracle服务进程，检查 Oracle监听进程，共三个部分。1.1. 检查 Oracle实例状态 SQL select instance_name,host_name,startup_time,status,database_status from v$instance;INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS- - - - - -CKDB AS14 2009-5-7 9:3 OPEN ACTIVE其中“STATUS
6、”表示 Oracle当前的实例状态，必须为“OPEN” ；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE” 。 SQL select name,log_mode,open_mode from v$database;NAME LOG_MODE OPEN_MODE- - -CKDB ARCHIVELOG READ WRITE其中“LOG_MODE”表示 Oracle当前的归档方式。 “ARCHIVELOG”表示数据库运行在归档模式下， “NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。1.2. 检查 Ora
7、cle服务进程$ps -ef|grep ora_|grep -v grepSTATUS NAME- -/data/oradata/CKDB/control01.ctl/data/oradata/CKDB/control02.ctl/data/oradata/CKDB/control03.ctl输出结果应该有 3条以上（包含 3条）的记录， “STATUS”应该为空。状态为空表示控制文件状态正常。 3.2. 检查 Oracle在线日志状态SQL select group#,status,type,member from v$logfile;GROUP# STATUS TYPE MEMBER- -
8、 - -3 ONLINE /data/oradata/CKDB/redo03.log2 ONLINE /data/oradata/CKDB/redo02.log1 ONLINE /data/oradata/CKDB/redo01.log4 ONLINE /data/oradata/CKDB/redo04.log5 ONLINE /data/oradata/CKDB/redo05.log6 ONLINE /data/oradata/CKDB/redo06.log6 rows selected输出结果应该有 3条以上（包含 3条）记录， “STATUS”应该为非“INVALID” ，非“DELET
9、ED”。 注：“STATUS”显示为空表示正常。3.3. 检查 Oracle表空间的状态SQL select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS- -SYSTEM ONLINEUNDOTBS1 ONLINESYSAUX ONLINETEMP ONLINEUSERS ONLINESJ1 ONLINEADM_INDEX ONLINEHOME_DATA ONLINEHOME_INDEX ONLINEPHOTO_DATA ONLINEPHOTO_INDEX ONLINE。 。 。 。 。 。 。输出结果中
10、 STATUS应该都为 ONLINE。3.4. 检查 Oracle所有数据文件状态SQL select name,status from v$datafile;NAME STATUS- -/data/oradata/CKDB/system01.dbf SYSTEM/data/oradata/CKDB/undotbs01.dbf ONLINE/data/oradata/CKDB/sysaux01.dbf ONLINE/data/oradata/CKDB/users01.dbf ONLINE/data/oradata/CKDB/sj.dbf ONLINE/data/oradata/CKDB/HOM
11、E_DATA1.dbf ONLINE/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE/data/oradata/CKDB/AUDIO_INDEX1.dbf
12、ONLINE/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE/data/oradata/CKDB/SYS_DATA1.dbf ONLINE/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE/data/oradata/CKDB/ADM_DATA1.dbf ONLINE/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE/data/oradata/CKDB/perfstat.dbf ONLINE输出结果中“STATUS”应该
13、都为“ONLINE” 。或者：SQL select file_name,status from dba_data_files;FILE_NAME STATUS- -/data/oradata/CKDB/users01.dbf AVAILABLE/data/oradata/CKDB/sysaux01.dbf AVAILABLE/data/oradata/CKDB/undotbs01.dbf AVAILABLE/data/oradata/CKDB/system01.dbf AVAILABLE/data/oradata/CKDB/sj.dbf AVAILABLE/data/oradata/CKDB/
14、perfstat.dbf AVAILABLE/data/oradata/CKDB/HOME_DATA1.dbf AVAILABLE/data/oradata/CKDB/HOME_INDEX1.dbf AVAILABLE/data/oradata/CKDB/PHOTO_DATA1.dbf AVAILABLE输出结果中“STATUS”应该都为“AVAILABLE” 。3.5. 检查无效对象sqlselect owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYS
15、TEM; no rows selected 如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者：SELECT owner, object_name, object_type FROM dba_objects WHERE status= INVALID;3.6. 检查所有回滚段状态SQL select segment_name,status from dba_rollback_segs;SEGMENT_NAME STATUS- -SYSTEM ONLINE_SYSSMU1$ ONLINE_SYSSMU2$ ONLINE_SYSSMU3$ ONLINE_S
16、YSSMU4$ ONLINE_SYSSMU5$ ONLINE_SYSSMU6$ ONLINE_SYSSMU7$ ONLINE_SYSSMU8$ ONLINE_SYSSMU9$ ONLINE_SYSSMU10$ ONLINE11 rows selected输出结果中所有回滚段的“STATUS”应该为“ONLINE” 。4. 检查 Oracle相关资源的使用情况在本节主要检查 Oracle相关资源的使用情况，包含：检查 Oracle初始化文件中相关的参数值，检查数据库连接情况，检查系统磁盘空间，检查 Oracle各个表空间使用情况，检查一些扩展异常的对象，检查 system表空间内的内容，检查对象
17、的下一扩展与表空间的最大扩展值，总共七个部分。4.1. 检查 Oracle初始化文件中相关参数值SQL select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit;RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE- - - -processes 162 500 500sessions 168 555 555enqueue_locks 136 6930 6930enqueue_resources 111 2660
18、 UNLIMITEDges_procs 0 0 0ges_ress 0 0 UNLIMITEDges_locks 0 0 UNLIMITEDges_cache_ress 0 0 UNLIMITEDges_reg_msgs 0 0 UNLIMITEDges_big_msgs 0 0 UNLIMITEDges_rsv_msgs 0 0 0gcs_resources 0 0 0gcs_shadows 0 0 0dml_locks 76 2440 UNLIMITEDtemporary_table_locks 26 UNLIMITED UNLIMITED transactions 13 610 UNLI
19、MITEDbranches 0 610 UNLIMITEDcmtcallbk 3 610 UNLIMITEDsort_segment_locks 5 UNLIMITED UNLIMITEDmax_rollback_segments 11 610 65535RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE- - - -max_shared_servers 1 UNLIMITED UNLIMITEDparallel_max_servers 16 80 360022 rows selected若 LIMIT_VALU-MAX_UTIL
20、IZATION select count(*) from v$session;COUNT(*)-29select sid,serial#,username,program,machine,status from v$session;SID SERIAL# USERNAME PROGRAM MACHINE STATUS- - - - - -1 3 oraclexz15saledb (PMON) xz15saledb ACTIVE2 3 oraclexz15saledb (DBW0) xz15saledb ACTIVE3 3 oraclexz15saledb (DBW1) xz15saledb A
21、CTIVE4 3 oraclexz15saledb (LGWR) xz15saledb ACTIVE5 3 oraclexz15saledb (CKPT) xz15saledb ACTIVE6 3 oraclexz15saledb (SMON) xz15saledb ACTIVE7 3 oraclexz15saledb (RECO) xz15saledb ACTIVE8 1 oraclexz15saledb (CJQ0) xz15saledb ACTIVE9 3 oraclexz15saledb (ARC0) xz15saledb ACTIVE10 3 oraclexz15saledb (AR
22、C1) xz15saledb ACTIVE11 11319 ZK AccPrtInv_svrxz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE13 48876 ZG uploadxz15saleap (TNS V1-V3) xz15saleap INACTIVE17 20405 ZK AccCreateRptxz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE20 12895 ZK OweScanSvrxz15billdb (TNS V1-V3) xz15billdb INACTIVE其中：SID 会话(session)
23、的 ID号；SERIAL# 会话的序列号，和 SID一起用来唯一标识一个会话；USERNAME 建立该会话的用户名；PROGRAM 这个会话是用什么工具连接到数据库的；STATUS 当前这个会话的状态，ACTIVE 表示会话正在执行某些任务，INACTIVE 表示当前会话没有执行任何操作；如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果 DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候 session不会断开。容易引起死连接。建议通过 sid查到操作系统的 spid,使用 ps ef|grep spidno的
24、方式确认 spid不是 ORACLE的后台进程。使用操作系统的 kill -9命令杀掉连接 ）alter system kill session SID,SERIAL#;注意：上例中 SID为 1到 10(USERNAME列为空)的会话，是 Oracle的后台进程，不要对这些会话进行任何操作。4.3. 检查系统磁盘空间如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。oracleAS14 $ df -hFilesystem Size Used Avail Use% Mounted on/dev/sda5 9.7G 3.9G 5.4G 42% /dev/sda1 4
25、79M 16M 438M 4% /boot/dev/sda2 49G 19G 28G 41% /datanone 1014M 0 1014M 0% /dev/shm4.4. 检查表空间使用情况SQL select f.tablespace_name,a.total,f.free,round(f.free/a.total)*100) “% Free“from(select tablespace_name, sum(bytes/(1024*1024) total from dba_data_files group by tablespace_name) a,(select tablespace_n
26、ame, round(sum(bytes/(1024*1024) free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by “% Free“;TABLESPACE_NAME TOTAL FREE % Free- - - -OPERATION_DATA 1800 547 30WAPWEB_DATA 100 36 36OPERATION_INDEX 500 186 37SYSTEM 1024 515 50SYSAUX 1024 534 52SA
27、LE8_TEMP 100 62 62SJ1 500 348 70PERFSTAT 500 356 71.HOME_DATA 100 77 77SYS_INDEX 100 100 100VIDEO_INDEX 100 100 100VIDEO_DATA 100 100 100BLOG_DATA 100 100 10039 rows selected如果空闲率%Free 小于 10%以上（包含 10%） ，则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为 2G或者 4G，自动扩展的最大限制在 8G。4.5.
28、检查一些扩展异常的对象sqlselect Segment_Name, Segment_Type, TableSpace_Name, (Extents/Max_extents)*100 Percent From sys.DBA_Segments Where Max_Extents != 0 and (Extents/Max_extents)*100=95 order By Percent; no rows selected如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。4.6. 检查 system表空间内的内容select distinct(o
29、wner) from dba_tables where tablespace_name=SYSTEM and owner!=SYS and owner!=SYSTEM union select distinct(owner) from dba_indexes where tablespace_name=SYSTEM andowner!=SYS and owner!=SYSTEM;no rows selected如果记录返回，则表明 system表空间内存在一些非 system和 sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非 System表空间，同时应该
30、检查这些对象属主的缺省表空间值。4.7. 检查对象的下一扩展与表空间的最大扩展值sqlselect 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 union select
31、 a.index_name, a.next_extent, a.tablespace_name from all_indexes 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;no rows selected如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表
32、空间的存储参数。5. 检查 Oracle数据库备份结果在本节主要检查 Oracle数据库备份结果，包含：检查数据库备份日志信息，检查backup卷中文件产生的时间，检查 oracle用户的 email，总共三个部分。5.1. 检查数据库备份日志信息假设：备份的临时目录为/backup/hotbakup，我们需要检查 2009年 7月 22日的备份结果，则用下面的命令来检查： #cat /backup/hotbackup/hotbackup-09-7-22.log|grep i error备份脚本的日志文件为 hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“E
33、RROR:” ，则表明备份没有成功，存在问题需要检查。5.2. 检查 backup卷中文件产生的时间#ls lt /backup/hotbackupbackup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。5.3. 检查 oracle用户的 email#tail n 300 /var/mail/oracle热备份脚本是通过 Oracle用户的 cron去执行的。cron 执行完后操作系统就会发一条Email通知 Oracle用户任务已经完成。查看 Oracle email中今天凌晨部分有无 ORA-，Error，Fa
34、iled 等出错信息，如果有则表明备份不正常。6. 检查 Oracle数据库性能在本节主要检查 Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查 cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。6.1. 检查数据库的等待事件set pages 80set lines 120col event for a40select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where
35、 event not like SQL% and event not like rdbms%;如果数据库长时间持续出现大量像 latch free，enqueue ，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。6.2. Disk Read最高的 SQL语句的获取SQLSELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHERE ROWNUM 0 AND SQL_ADDRESS=A
36、DDRESS AND SQL_HASH_VALUE = HASH_VALUE;6.6. 检查消耗 CPU最高的进程SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999 COLUMN S_# FORMAT 999COLUMN USERNAME FORMAT A9 HEADING “ORA USER“COLUMN PROGRAM FORMAT A29COLUMN SQL FORMAT A60COLUMN OSNAME FORMAT A9 HEADING “OS USER“SELECT P.PID PID,S.SID
37、 SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE %6.7. 检查碎片程度高的表SQL SELECT segmen
38、t_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);6.8. 检查表空间的 I/O 比例SQLSELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME “FILE“,F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW
39、, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;6.9. 检查文件系统的 I/O 比例SQLSELECT SUBSTR(A.FILE#,1,2) “#“, SUBSTR(A.NAME,1,30) “NAME“, A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;6.10.检查死锁及处理查询目前
40、锁对象信息：col sid for 999999col username for a10col schemaname for a10col osuser for a16col machine for a16col terminal for a20col owner for a10col object_name for a30col object_type for a10select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_id f
41、rom dba_objects o,v$locked_object l,v$session s where o.object_id=l.object_id and s.sid=l.session_id;oracle级 kill掉该 session：alter system kill session 操作系统级 kill掉 session：#kill -9 pid6.11.检查数据库 cpu、I/O、内存性能记录数据库的 cpu使用、IO、内存等使用情况，使用 vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。1. CPU使用情况：rootsale8
42、# toptop - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombieCpu(s): 1.2% us, 0.1% sy, 0.0% ni, 98.8% id, 0.0% wa, 0.0% hi, 0.0% siMem: 16404472k total, 12887428k used, 3517044k free, 60796k buffersSwap: 8385920k total, 665576k used, 7720344k free, 10358384k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle 3
