Oracle 数据库日常巡检

Oracle数据库日常巡检阅读目录 1.检查数据库基本状况 2.检查Oracle相关资源的使用情况 3.检查Oracle数据库备份结果 4.检查Oracle数据库性能 5.检查数据库cpu、1/0、内存性能 6.检查数据库安全性 7.其他检查1. 检查数据库基本状况包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个 部分。1.1. 检查Oracle实例状态select instance_name, host_name,startup_time,status,database_status from v$instance;其中“STATUS表示Oracle当前的实例状态，必须为“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.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;如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生 成这个对象，或者：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检查system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七 个部分。2.1. 检查Oracle初始化文件中相关参数值select resource_name,max_utilization,initial_allocation,limit_valuefrom v$resource_limit;若 LIMIT_VALU-MAX_UTILIZATIONv = 5,则表明与 RESOURCE_NAME 相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件 $ORACLE_BASE/admin/CKDB/pfile/initORCL.ora 来修改。2.2. 检查数据库连接情况查看当前会话连接数，是否属于正常范围。select count(*) from v$session;select sid,serial#,username,program,machine,status from v$session;其中：SID会话(session)的ID号；SERIAL#会话的序列号，和SID 一起用来唯一标识一个会话；USERNAME建立该会话的用户名；PROGRAM这个会话是用什么工具连接到数据库的；STATUS当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表 示当前会话没有执行任何操作；如果建立了过多的连接，会消耗数据库的资源，同时，对一些挂死的连接可能需要手 工进行清理。如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易 引起死连接。建议通过引d查到操作系统的spid,使用ps -ef|grep spidno的方式确 认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接）alter system kill session SID,SERIAL#;注意：上例中SID为1到10（USERNAME列为空）的会话，是Oracle的后台进程， 不要对这些会话进行任何操作。2.3. 检查系统磁盘空间如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空 间。oracleAS14 $ df -hFilesystem Size Used Avail Use% Moun ted on/dev/sda5 9.7G 3.9G 5.4G 42% /dev/sdal 479M 16M 438M 4% /boot/dev/sda2 49G 19G 28G 41% /datanone 1014M 0 1014M 0% /dev/shm2.4. 检查表空间使用情况select f.tablespace_name,a.total,f.free,round(f.free / a.total) * 100) % Freefrom (select tablespace_name, sum(bytes / (1024 * 1024) totalfrom dba_data_filesgroup by tablespace_name) a,如果空闲率Free小于10%以上(包含10%)，则注意要增加数据文件来扩展表空 间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数 据文件的原则是每个数据文件大小为2G或者4G,自动扩展的最大限制在8G。2.5.检查一些扩展异常的对象select Segment_Name,Segment_Type,TableSpace_Name,(Extents / Max_extents) * 100 PercentFrom sys.DBA_SegmentsWhere Max_Extents != 0and (Extents / Max_extents) * 100;如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象 要修改它的存储结构参数。2.6.检查system表空间内的内容select distinct (owner)from dba_tableswhere tablespace_name = SYSTEMand owner != SYS and owner != SYSTEMunionselect distinct (owner)from dba_indexeswhere tablespace_name = SYSTEMand owner != SYS and owner != SYSTEM;如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应 该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表 空间，同时应该检查这些对象属主的缺省表空间值。2.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.tablespace_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检查backup卷中文件产生的时间;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 -It /backup/hotbackup backup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热 备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。3.3.检查oracle用户的email#tail -n 300 /var/mail/oracle热备份脚本是通过Oracle用户的cron去执行的。cron执行完后操作系统就会发一条Email通知Oracle用户任务已经完成。查看Oracle emai I中今天凌晨部分有无ORA-, Error，Failed等出错信息，如果有则表明备份不正常。回到顶部4. 检查Oracle数据库性能在本节主要检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及 处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链按迁移，定期做统 计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共 十个部分。4.1.检查数据库的等待事件set pages 80set lines 120 col event for a40 select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAITfrom v$session waitwhere event not like SQL%and event not like rdbms%;如果数据库长时间持续出现大量像latch free, enqueue, buffer 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_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROM V$SQLAREAORDER BY DISK_READS DESC)WHERE ROWNUM 10;4.4.等待时间最多的5个系统等待事件的获取SELECT *FROM (SELECT *FROM V$SYSTEM_EVENTORDER BY TOTAL_WAITS DESC)WHERE ROWNUM = 5；4.5.检查运行很久的SQLCOLUMN USERNAME FORMAT A12COLUMN OPNAME FORMAT A16COLUMN PROGRESS FORMAT A8SELECT USERNAME,SID,OPNAME,ROUND(SOFAR * 100 / TOTALWORK, 0)|% AS PROGRESS,TIME_REMAINING,SQL_TEXTFROM V$SESSION_LONGOPS, V$SQLWHERE TIME_REMAINING 0AND SQL_ADDRESS = ADDRESSAND SQL_HASH_VALUE = HASH_VALUE;4.6.检查消耗CPU最高的进程SET LINE 240SET VERIFY OFFCOLUMNSID FORMAT 999COLUMNPID FORMAT 999COLUMNS # FORMAT 999COLUMNUSERNAME FORMAT A9 HEADING ORA USERCOLUMNPROGRAM FORMAT A29COLUMNSQL FORMAT A60COLUMNOSNAME FORMAT A9 HEADING OS USERSELECTP.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 TEXT, 1, 80) SQLFROM V$PROCESS P,V$SQLAREA AWHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) ANDP.SPID LIKE %&1%;4.7.检查碎片程度高的表SELECT segment_name table_name, COUNT(*) extentsFROM dba_segmentsWHERE owner NOT IN (SYS, SYSTEM)GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*)FROM dba_segmentsGROUP BY segment_name);4.8.检查表空间的I/O比例SELECT DF.TABLESPACE_NAME NAME,DFFILE_NAMEFILE,F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROM V$FILESTAT F, DBA_DATA_FILES DFORDER BY DF.TABLESPACE_NAME;4.9.检查文件系统的I/O比例SELECT SUBSTR(A.FILE#, 1, 2)#,SUBSTR(A.NAME,1, 30) NAME,A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROM V$DATAFILE A, V$FILESTAT BWHERE A.FILE# = B.FILE#;4.10.检查死锁及处理查询目前锁对象信息：select sid,serial#,username,SCHEMANAME,MACHINE, terminal,PROGRAM, owner,object_name,object_type, oobject_idfrom dba_objects o, v$locked_object l, v$session swhere o.object_id = l.object_idand s.sid = l.session id;oracle 级 kill 掉该 session：alter system kill session &sid,&serial#;操作系统级kill掉session：#kill -9 pid回到顶部5. 检查数据库cpu、I/O、内存性能记录数据库的cpu使用、10、内存等使用情况，使用vmstat,iostat,sar,top等命令 进行信息收集并检查这些信息，判断资源使用情况。5.1 CPU使用情况: rootsale8 # toptop - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29Tasks: 353 total, 2 running, 351 sleep ing, 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 oracle32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu,当其平均值下降至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/2009avg-cpu: %user %n ice %sys%iowait %idle0.16 0.00 0.05 0.36 99.43Device: tps kB_read/s kB_wrt n/s kB_read kB_wrt nsda 3.33 13.16 50.25 94483478 360665804avg-cpu: %user %n ice %sys%iowait %idle0.00 0.00 0.00 0.00 100.00Device: tps kB_read/s kB_wrt n/s kB_read kB_wrt nsda 0.00 0.00 0.00 0 0如上所示，蓝色字体部分表示磁盘读写情况，红色字体部分为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. 检查行链接/迁移select table_name, num_rows, chain_cntFrom dba_tablesWhere owner = CTAIS2And chain_cnt 0;注：含有long raw列的表有行链接是正常的，找到迁移行保存到chained_rows表中,如没有该表执行./rdbms/adm in/utlchai n. sqlanalyze table tablename list chained rows;可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行create table aa as select a.* from sb_zsxx a,chained_rows b wherea.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的系统，需要定期对数据对象的统计信息进 行采集更新，使优化器可以根据准备的信息作出正确的explai n pla n。在以下情况更 需要进行统计信息的更新：a. 应用发生变化b. 大规模数据迁移、历史数据迁出、其他数据的导入等c 数据量发生变化查看表或索引的统计信息是否需更新，如:Select table_name,num_rows,last_analyzed From user_tables where table_name =DJ_NSRXX select count ( ) from DJ_NSRXX 女口 num_rows 和 count()如果行数相差很多，则该表需要更新统计信息，建议一周做一次统计信息收集，如:exec sys.dbms_stats.gather_schema_stats(ownname =CTAIS2,cascade = TRUE,degree = 4);5.8检查缓冲区命中率SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,round(100 *(1 - c.value / (a.value + b.value), 4) hit_ratioFROM v$sysstat a, v$sysstat b, v$sysstat cWHERE a.NAME = db block getsAND b.NAME = consistent getsAND c.NAME = physical reads;如果命中率低于90%则需加大数据库参数db_cache_size。5.9检查共享池命中率select sum(pinhits) / sum(pins) * 100 from v$librarycache;如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool 的大小。5.10检查排序区select name,value from v$sysstat where name like %sort%;如果disk/(memoty+row)的比例过高，则需要调整 sort_area_size(workarea_size_policy=false)或 pga_aggregate_target(workarea_size_policy=true)。5.11检查日志缓冲区select name,value from v$sysstat where name in (redo entries,redo buffer allocation retries);如果 redo buffer allocati on retries/redo en tries超过 1%,则需要增大 log_buffer。回到顶部6. 检查数据库安全性在本节主要检查Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码， 总共两个部分。6.1. 检查系统安全日志信息系统安全日志文件的目录在/var/log下，主要检查登录成功或失败的用户日志信息。检查登录成功的日志：rootrac2 # grep -i accepted /var/log/secureJan 8 08:44:43 rac2 sshd29559: Accepted password for rootfrom :ffff:10.10.10.6 port 1119 ssh2 检查登录失败的日志:rootrac2 # grep -i in val /var/log/secure &grep -i failed /var/log/secureJan 9 10:30:44 rac2 sshd3071: In valid user ydbuserfrom :ffff:192.168.3.5Jan 9 10:30:56 rac2 sshd3071: Failed password for in valid user ydbuser from :ffff:192.168.3.5 port 36005 ssh2Jan 9 10:30:56 rac2 sshd3071: Failed password for in valid user ydbuser from :ffff:192.168.3.5 port 36005 ssh2Jan 10 22:44:38 rac2 sshd21611: Failed password for rootfrom :ffff:10.10.10.6 port 1723 ssh2在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused) 视为系统正常，出现错误提示，应作出系统告警通知。6.2. 检查用户修改密码在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时 的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别 有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括： 数据库管理员用户SYS, SYSTEM；其他用户。登陆系统后，提示符下输入cat /etc/passwd,在列出来的用户中查看是否存在已经不 再使用的或是陌生的帐号。若存在，则记录为异常。修改密码方法：alter user USER_NAME identified by PASSWORD;回到顶部7. 其他检查在本节主要检查当前crontab任务是否正常，检查Oracle Job是否有失败等共六个部 分。7.1检查当前crontab任务是否正常oracleAS14 $ cro ntab -l7.2 Oracle Job是否有失败select job,what,last_date,next_date,failures,broken from dba_jobs Whereschema_user=CAIKE;如有问题建议重建job,如：exec sys.dbms_job.remove(1);commit;execsys.dbms_job.isubmit(1,REFRESH_ALL_SNAPSHOT;,SYSDATE+1/1440,SYSDATE+4/144O);commit;7.3. 监控数据量的增长情况select A.tablespace_name, (1 一 (A. total) / Btotal) * 100 used_percentfrom (select tablespace_name, sum(bytes) totalfrom dba_free_spacegroup by tablespace_name) A,select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) Bwhere A.tablespace_name = B.tablespace_name;根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施:-删除历史数据移动规定数据库中至少保留6个月的历史数据，所以以前的历史数据可以考虑备份然后 进行清除以便释放其所占的资源空间。扩表空间alter tablespace add datafile size sizeautoextend off;注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这 些操作，都会造成Oracle数据库控制文件的变化，DBA应及进行控制文件的备份，备 份方法是：执行SQL语句：alter database backup controlfile to /home/backup/control.bak;或：alter database backup controlfile to trace;这样，会在USER_DUMP_DEST（初始化参数文件中指定）目录下生成创建控制文件的 SQL命令。7.4.检查失效的索引select index_name, table_name, tablespace_name, statusFrom dba_indexesWhere owner = CTAIS2And status VALID;注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild, 如：alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;7.5. 检查不起作用的约束SELECT owner, constraint_name, table_name, constraint_type, statusFROM dba_constraintsWHERE status = DISABLEand constraint_type = P;如有失效约束则启用，如：alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;7.6. 检查无效的triggerstatusSELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE=DISABLED;如有失效触发器则启用，如：alter Trigger TRIGGER_NAME Enable;