数据库日常检查学习资料.docx

1、数据库日常检查学习资料 1. 检查数据库基本状况 2. 检查Oracle相关资源的使用情况 3. 检查Oracle数据库备份结果 4. 检查Oracle数据库性能 5. 检查数据库cpu、I/O、内存性能 6. 检查数据库安全性 7. 其他检查回到顶部1. 检查数据库基本状况包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。1.1. 检查Oracle实例状态select instance_name,host_name,startup_time,status,database_status from v$instance;其中“STATUS”表示Or

2、acle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。1.2. 检查Oracle在线日志状态select group#,status,type,member from v$logfile;输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。注：“STATUS”显示为空表示正常。1.3. 检查Oracle表空间的状态select tablespace_name,status from dba_tablespaces;输出结果中STATUS应该都为ONLINE。1

3、.4. 检查Oracle所有数据文件状态select name,status from v$datafile;输出结果中“STATUS”应该都为“ONLINE”。或者：select file_name,status from dba_data_files;输出结果中“STATUS”应该都为“AVAILABLE”。1.5. 检查无效对象select owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM;如果有记录返回，则说明存在无效对象。若这些对象与应

4、用相关，那么需要重新编译生成这个对象，或者：SELECT owner, object_name, object_type FROM dba_objects WHERE status= INVALID;1.6. 检查所有回滚段状态select segment_name,status from dba_rollback_segs;输出结果中所有回滚段的“STATUS”应该为“ONLINE”。回到顶部2. 检查Oracle相关资源的使用情况包含：a.检查Oracle初始化文件中相关的参数值b.检查数据库连接情况，检查系统磁盘空间c.检查Oracle各个表空间使用情况，检查一些扩展异常的对象，d.检查

5、system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七个部分。2.1. 检查Oracle初始化文件中相关参数值select resource_name,max_utilization,initial_allocation, limit_valuefrom v$resource_limit;若LIMIT_VALU-MAX_UTILIZATION= 95order By Percent;如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。2.6. 检查system表空间内的内容select distinct (owner)from

6、 dba_tableswhere tablespace_name = SYSTEMand owner != SYSand owner != SYSTEMunionselect distinct (owner)from dba_indexeswhere tablespace_name = SYSTEMand owner != SYSand owner != SYSTEM;如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。2.7. 检查对象

7、的下一扩展与表空间的最大扩展值select a.table_name, a.next_extent, a.tablespace_namefrom all_tables a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent f.big_chunkunionselect a.index_name, a.next_extent, a.t

8、ablespace_namefrom all_indexes a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent f.big_chunk;如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。回到顶部3. 检查Oracle数据库备份结果包含：a.检查数据库备份日志信息;b.检查b

9、ackup卷中文件产生的时间;c.检查oracle用户的email3.1. 检查数据库备份日志信息假设：备份的临时目录为/backup/hotbakup，我们需要检查2009年7月22日的备份结果，则用下面的命令来检查：cat /backup/hotbackup/hotbackup-09-7-22.log|grep i error备份脚本的日志文件为hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“ERROR:”，则表明备份没有成功，存在问题需要检查。3.2. 检查backup卷中文件产生的时间#ls lt /backup/hotbackupbackup卷是备

10、份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。3.3. 检查oracle用户的email#tail n 300 /var/mail/oracle热备份脚本是通过Oracle用户的cron去执行的。cron执行完后操作系统就会发一条Email通知Oracle用户任务已经完成。查看Oracle email中今天凌晨部分有无ORA-，Error，Failed等出错信息，如果有则表明备份不正常。回到顶部4. 检查Oracle数据库性能在本节主要检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查cpu、

11、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。4.1. 检查数据库的等待事件set pages 80set lines 120col event for a40select 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，buff

12、er busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。4.2. Disk Read最高的SQL语句的获取SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHERE ROWNUM = 5;4.3. 查找前十条性能差的sqlSELECT *FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXTFRO

13、M V$SQLAREAORDER BY DISK_READS DESC)WHERE ROWNUM 10;4.4. 等待时间最多的5个系统等待事件的获取SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE SQL% ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM = 5;4.5. 检查运行很久的SQLCOLUMN USERNAME FORMAT A12COLUMN OPNAME FORMAT A16COLUMN PROGRESS FORMAT A8SELECT USERNAME, SID,

14、 OPNAME, ROUND(SOFAR * 100 / TOTALWORK, 0) | % AS PROGRESS, TIME_REMAINING, SQL_TEXT FROM V$SESSION_LONGOPS, V$SQL WHERE TIME_REMAINING 0 AND SQL_ADDRESS = ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;4.6. 检查消耗CPU最高的进程SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999COLUMN S_# FORMAT 9

15、99COLUMN USERNAME FORMAT A9 HEADING ORA USERCOLUMN PROGRAM FORMAT A29COLUMN SQL FORMAT A60COLUMN OSNAME FORMAT A9 HEADING OS USERSELECT P.PID PID, S.SID 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_TE

16、XT, 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 %&1%;4.7. 检查碎片程度高的表SELECT segment_name table_name, COUNT(*) extents FROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(C

17、OUNT(*) FROM dba_segments GROUP BY segment_name);4.8. 检查表空间的I/O比例SELECT 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;4.9. 检查文件系统的I/O比例SELECT SUBSTR(

18、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#;4.10.检查死锁及处理查询目前锁对象信息：select sid, serial#, username, SCHEMANAME, osuser, MACHINE, terminal, PROGRAM, owner, object_name, object_type, o.object_id from dba_obje

19、cts 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 &sid,&serial#;操作系统级kill掉session：#kill -9 pid回到顶部5. 检查数据库cpu、I/O、内存性能记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。5.1 CPU使用情况：rootsa

20、le8 # 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,

21、 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 32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle 32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu，

22、当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。5.2 内存使用情况：# free -mtotal used free shared buffers cachedMem: 2026 1958 67 0 76 1556-/+ buffers/cache: 326 1700Swap: 5992 92 5900如上所示，蓝色部分表示系统总内存，红色部分表示系统使用的内存，黄色部分表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。5.3 系统I/O情况：# iostat -k 1 3Linux 2.6.9-22.ELsmp (AS14) 07/29/200

23、9avg-cpu: %user %nice %sys%iowait %idle0.16 0.00 0.05 0.36 99.43Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 3.33 13.16 50.25 94483478 360665804avg-cpu: %user %nice %sys%iowait %idle0.00 0.00 0.00 0.00 100.00Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 0.00 0.00 0.00 0 0如上所示，蓝色字体部分表示磁盘读写

24、情况，红色字体部分为cpu IO等待情况。5.4 系统负载情况：#uptime12:08:37 up 162 days, 23:33, 15 users, load average: 0.01, 0.15, 0.10如上所示，蓝体字部分表示系统负载，后面的3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常。5.5.查看是否有僵死进程select spid from v$process where addr not in (select paddr from v$session);有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。5.6.检查行链接/迁移

25、select table_name, num_rows, chain_cnt From dba_tables Where owner = CTAIS2 And chain_cnt 0;注：含有long raw列的表有行链接是正常的,找到迁移行保存到chained_rows表中,如没有该表执行./rdbms/admin/utlchain.sqlanalyze table tablename list chained rows;可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行create table aa as select a.* from sb_z

26、sxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name =SB_ZSXX;delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = SB_ZSXX);insert into sb_zsxx select * from chained_row where table_name = SB_ZSXX;5.7 定期做统计分析对于采用Oracle Cost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explain plan。在以下情况更需要进行统计信息的更新：a. 应用发生变化b. 大规模数据迁移、历史数据迁出、其他数