oracle-DBA日常检查.doc

Oracle10gOracle10g 数据库数据库 日常维护手册日常维护手册 目 录 1.1.检查数据库基本状况检查数据库基本状况.4 4 1.1.检查 ORACLE实例状态.4 1.2.检查 ORACLE服务进程.4 1.3.检查 ORACLE监听状态.5 2.2.检查系统和检查系统和 ORACLEORACLE 日志文件日志文件.6 6 2.1.检查操作系统日志文件.6 2.2.检查ORACLE日志文件.6 2.3.检查 ORACLE核心转储目录.7 2.4.检查 ROOT用户和 ORACLE用户的EMAIL.7 3.3.检查检查 ORACLEORACLE 对象状态对象状态.7 7 3.1.检查 ORACLE控制文件状态.7 3.2.检查 ORACLE在线日志状态.8 3.3.检查 ORACLE表空间的状态.8 3.4.检查 ORACLE所有数据文件状态.8 3.5.检查无效对象.9 3.6.检查所有回滚段状态.10 4.4.检查检查 ORACLEORACLE 相关资源的使用情况相关资源的使用情况.1010 4.1.检查 ORACLE初始化文件中相关参数值.10 4.2.检查数据库连接情况.11 4.3.检查系统磁盘空间.12 4.4.检查表空间使用情况.12 4.5.检查一些扩展异常的对象.13 4.6.检查SYSTEM表空间内的内容.14 4.7.检查对象的下一扩展与表空间的最大扩展值.14 5.5.检查检查 ORACLEORACLE 数据库备份结果数据库备份结果.1414 5.1.检查数据库备份日志信息.15 5.2.检查BACKUP卷中文件产生的时间.15 5.3.检查ORACLE用户的EMAIL.15 6.6.检查检查 ORACLEORACLE 数据库性能数据库性能.1515 6.1.检查数据库的等待事件.15 6.2.DISK READ最高的 SQL 语句的获取 .15 6.3.查找前十条性能差的SQL.16 6.4.等待时间最多的 5 个系统等待事件的获取.16 6.5.检查运行很久的 SQL .16 6.6.检查消耗 CPU 最高的进程.16 6.7.检查碎片程度高的表.17 6.8.检查表空间的 I/O 比例.17 6.9.检查文件系统的 I/O 比例.17 6.10.检查死锁及处理.17 6.11.检查数据库CPU、I/O、内存性能.18 6.12.查看是否有僵死进程.19 6.13.检查行链接/迁移.19 6.14.定期做统计分析.19 6.15.检查缓冲区命中率.20 6.16.检查共享池命中率.20 6.17.检查排序区.20 6.18.检查日志缓冲区.21 7.7.检查数据库安全性检查数据库安全性.2121 7.1.检查系统安全日志信息.21 7.2.检查用户修改密码.21 8.8.其他检查其他检查.2222 8.1.检查当前CRONTAB任务是否正常 .22 8.2.ORACLE JOB是否有失败.22 8.3.监控数据量的增长情况.22 8.4.检查失效的索引.23 8.5.检查不起作用的约束.23 8.6.检查无效的TRIGGER.23 巡检内容 1.1.检查数据库基本状况检查数据库基本状况 在本节中主要对数据库的基本状况进行检查，其中包含：检查 Oracle 实例状态，检查 Oracle 服务进程，检查 Oracle 监听进程，共三个部分。 . 检查检查 OracleOracle 实例状态实例状态 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”表示 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”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行 在归档方式下。 . 检查检查 OracleOracle 服务进程服务进程 $ps -ef|grep ora_|grep -v grep STATUS NAME - - - /data/oradata/CKDB/control01.ctl /data/oradata/CKDB/control02.ctl /data/oradata/CKDB/control03.ctl 输出结果应该有 3 条以上（包含 3 条）的记录， “STATUS”应该为空。状态为空表示控制文 件状态正常。 . 检查检查 OracleOracle 在线日志状态在线日志状态 SQL select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER - - - - 3 ONLINE /data/oradata/CKDB/redo03.log 2 ONLINE /data/oradata/CKDB/redo02.log 1 ONLINE /data/oradata/CKDB/redo01.log 4 ONLINE /data/oradata/CKDB/redo04.log 5 ONLINE /data/oradata/CKDB/redo05.log 6 ONLINE /data/oradata/CKDB/redo06.log 6 rows selected 输出结果应该有 3 条以上（包含 3 条）记录， “STATUS”应该为非“INVALID” ，非 “DELETED” 。 注：“STATUS”显示为空表示正常。 . 检查检查 OracleOracle 表空间的状态表空间的状态 SQL select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS - - SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE SJ1 ONLINE ADM_INDEX ONLINE HOME_DATA ONLINE HOME_INDEX ONLINE PHOTO_DATA ONLINE PHOTO_INDEX ONLINE 。 。 。 。 。 。 。 输出结果中 STATUS 应该都为 ONLINE。 . 检查检查 OracleOracle 所有数据文件状态所有数据文件状态 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/HOME_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 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”应该都为“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/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” 。 . 检查无效对象检查无效对象 sqlselect owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM; no rows selected 如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成 这个对象，或者： SELECT owner, object_name, object_type FROM dba_objects WHERE status= INVALID; . 检查所有回滚段状态检查所有回滚段状态 SQL select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS - - SYSTEM ONLINE _SYSSMU1$ ONLINE _SYSSMU2$ ONLINE _SYSSMU3$ ONLINE _SYSSMU4$ ONLINE _SYSSMU5$ ONLINE _SYSSMU6$ ONLINE _SYSSMU7$ ONLINE _SYSSMU8$ ONLINE _SYSSMU9$ ONLINE _SYSSMU10$ ONLINE 11 rows selected 输出结果中所有回滚段的“STATUS”应该为“ONLINE” 。 4.4.检查检查 OracleOracle 相关资源的使用情况相关资源的使用情况 在本节主要检查 Oracle 相关资源的使用情况，包含：检查 Oracle 初始化文件中相关 的参数值，检查数据库连接情况，检查系统磁盘空间，检查 Oracle 各个表空间使用情况， 检查一些扩展异常的对象，检查 system 表空间内的内容，检查对象的下一扩展与表空间的 最大扩展值，总共七个部分。 . 检查检查 OracleOracle 初始化文件中相关参数值初始化文件中相关参数值 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 500 sessions 168 555 555 enqueue_locks 136 6930 6930 enqueue_resources 111 2660 UNLIMITED ges_procs 0 0 0 ges_ress 0 0 UNLIMITED ges_locks 0 0 UNLIMITED ges_cache_ress 0 0 UNLIMITED ges_reg_msgs 0 0 UNLIMITED ges_big_msgs 0 0 UNLIMITED ges_rsv_msgs 0 0 0 gcs_resources 0 0 0 gcs_shadows 0 0 0 dml_locks 76 2440 UNLIMITED temporary_table_locks 26 UNLIMITED UNLIMITED transactions 13 610 UNLIMITED branches 0 610 UNLIMITED cmtcallbk 3 610 UNLIMITED sort_segment_locks 5 UNLIMITED UNLIMITED max_rollback_segments 11 610 65535 RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE - - - - max_shared_servers 1 UNLIMITED UNLIMITED parallel_max_servers 16 80 3600 22 rows selected 若 LIMIT_VALU-MAX_UTILIZATION select count(*) from v$session; COUNT(*) - 29 select sid,serial#,username,program,machine,status from v$session; SID SERIAL# USERNAME PROGRAM MACHINE STATUS - - - - - - - 1 3 oraclexz15saledb (PMON) xz15saledb ACTIVE 2 3 oraclexz15saledb (DBW0) xz15saledb ACTIVE 3 3 oraclexz15saledb (DBW1) xz15saledb ACTIVE 4 3 oraclexz15saledb (LGWR) xz15saledb ACTIVE 5 3 oraclexz15saledb (CKPT) xz15saledb ACTIVE 6 3 oraclexz15saledb (SMON) xz15saledb ACTIVE 7 3 oraclexz15saledb (RECO) xz15saledb ACTIVE 8 1 oraclexz15saledb (CJQ0) xz15saledb ACTIVE 9 3 oraclexz15saledb (ARC0) xz15saledb ACTIVE 10 3 oraclexz15saledb (ARC1) xz15saledb ACTIVE 11 11319 ZK AccPrtInv_svrxz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE 13 48876 ZG uploadxz15saleap (TNS V1-V3) xz15saleap INACTIVE 17 20405 ZK AccCreateRptxz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE 20 12895 ZK OweScanSvrxz15billdb (TNS V1-V3) xz15billdb INACTIVE 其中：SID 会话(session)的 ID 号； SERIAL# 会话的序列号，和 SID 一起用来唯一标识一个会话； USERNAME 建立该会话的用户名； PROGRAM 这个会话是用什么工具连接到数据库的； STATUS 当前这个会话的状态，ACTIVE 表示会话正在执行某些任务，INACTIVE 表示当前会 话没有执行任何操作； 如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手 工进行清理。如果 DBA 要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉 数据库的连接，这样有时候 session 不会断开。容易引起死连接。建议通过 sid 查到操作 系统的 spid,使用 ps ef|grep spidno 的方式确认 spid 不是 ORACLE 的后台进程。使用 操作系统的 kill -9 命令杀掉连接 ） alter system kill session SID,SERIAL#; 注意：上例中 SID 为 1 到 10(USERNAME 列为空)的会话，是 Oracle 的后台进程，不要对这 些会话进行任何操作。 . 检查系统检查系统磁盘空间磁盘空间 如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。 oracleAS14 $ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda5 9.7G 3.9G 5.4G 42% / /dev/sda1 479M 16M 438M 4% /boot /dev/sda2 49G 19G 28G 41% /data none 1014M 0 1014M 0% /dev/shm . 检查表空间使用情况检查表空间使用情况 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_name, round(sum(bytes/(1024*1024) free from dba_free_space group by tablespace_name) f WHERE a.tablespace_name = f.tablespace_name(+) order by % Free; TABLESPACE_NAME TOTAL FREE % Free - - - - OPERATION_DATA 1800 547 30 WAPWEB_DATA 100 36 36 OPERATION_INDEX 500 186 37 SYSTEM 1024 515 50 SYSAUX 1024 534 52 SALE8_TEMP 100 62 62 SJ1 500 348 70 PERFSTAT 500 356 71 . HOME_DATA 100 77 77 SYS_INDEX 100 100 100 VIDEO_INDEX 100 100 100 VIDEO_DATA 100 100 100 BLOG_DATA 100 100 100 39 rows selected 如果空闲率%Free 小于 10%以上（包含 10%） ，则注意要增加数据文件来扩展表空间而不要 是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原 则是每个数据文件大小为 2G 或者 4G，自动扩展的最大限制在 8G。 . 检查一些扩展异常的对象检查一些扩展异常的对象 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 如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要 修改它的存储结构参数。 . 检查检查 systemsystem 表空间内的内容表空间内的内容 select distinct(owner) from dba_tables where tablespace_name=SYSTEM and owner!=SYS and owner!=SYSTEM union select distinct(owner) from dba_indexes where tablespace_name=SYSTEM and owner!=SYS and owner!=SYSTEM; no rows selected 如果记录返回，则表明 system 表空间内存在一些非 system 和 sys 用户的对象。应该进一 步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非 System 表空间，同时 应该检查这些对象属主的缺省表空间值。 . 检查对象的下一扩展与表空间的最大扩展值检查对象的下一扩展与表空间的最大扩展值 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 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 如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需 调整相应表空间的存储参数。 5.5.检查检查 OracleOracle 数据库备份结果数据库备份结果 在本节主要检查 Oracle 数据库备份结果，包含：检查数据库备份日志信息，检查 backup 卷中文件产生的时间，检查 oracle 用户的 email，总共三个部分。 . 检查数据库备份日志信息检查数据库备份日志信息 假设：备份的临时目录为/backup/hotbakup，我们需要检查 2009 年 7 月 22 日的备份结果， 则用下面的命令来检查： #cat /backup/hotbackup/hotbackup-09-7-22.log|grep i error 备份脚本的日志文件为 hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文 件中存在“ERROR:” ，则表明备份没有成功，存在问题需要检查。 . 检查检查 backupbackup 卷中文件产生的时间卷中文件产生的时间 #ls lt /backup/hotbackup backup 卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份 脚本产生的。如果时间不对则表明热备份脚本没执行成功。 . 检查检查 oracleoracle 用户的用户的 emailemail #tail n 300 /var/mail/oracle 热备份脚本是通过 Oracle 用户的 cron 去执行的。cron 执行完后操作系统就会发一条 Email 通知 Oracle 用户任务已经完成。查看 Oracle email 中今天凌晨部分有无 ORA- ，Error，Failed 等出错信息，如果有则表明备份不正常。 6.6.检查检查 OracleOracle 数据库性能数据库性能 在本节主要检查 Oracle 数据库性能情况，包含：检查数据库的等待事件，检查死锁及 处理，检查 cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计 分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个 部分。 . 检查数据库的等待事件检查数据库的等待事件 set pages 80 set lines 120 col event for a40 select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like SQL% and event not like rdbms%; 如果数据库长时间持续出现大量像 latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read 等等待事件时，需要对其进行分析，可能存 在问题的语句。 . DiskDisk ReadRead 最高的最高的 SQLSQL 语句的获取语句的获取 SQLSELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM=5 desc; . 查找前十条性能差的查找前十条性能差的 sqlsql SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM10 ; . 等待时间最多的等待时间最多的 5 5 个系统等待事件的获取个系统等待事件的获取 SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE SQL% ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM 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); . 检查表空间的检查表空间的 I/OI/O 比例比例 SQLSELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME FILE,F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; . 检查文件系统的检查文件系统的 I/OI/O 比例比例 SQLSELECT SUBSTR(A.FILE#,1,2) #, SUBSTR(A.NAME,1,30) NAME, A