Oracle 數(shù)據(jù)庫日常巡檢

1、Oracle 數(shù)據(jù)庫日常巡檢閱讀目錄1.檢查數(shù)據(jù)庫基本狀況2.檢查Oracle相關(guān)資源的使用情況3.檢查Oracle數(shù)據(jù)庫備份結(jié)果4.檢查Oracle數(shù)據(jù)庫性能5.檢查數(shù)據(jù)庫cpu、I/O、內(nèi)存性能6.檢查數(shù)據(jù)庫安全性7.其他檢查1.檢查數(shù)據(jù)庫基本狀況包含：檢查Oracle實例狀態(tài)，檢查Oracle服務(wù)進程，檢查Oracle監(jiān)聽進程， 共三個部分。檢查Oracle實例狀態(tài)select instance_name,host_name,startup_time,status,database_status from v$instance;其中“STATUS”表示Oracle當(dāng)前的實例狀態(tài)，必須為“

2、OPEN”； “DATABASE_STATUS”表示Oracle當(dāng)前數(shù)據(jù)庫的狀態(tài)，必須為“ACTIVE”。檢查Oracle在線日志狀態(tài)select group#,status,type,member from v$logfile;輸出結(jié)果應(yīng)該有3條以上（包含3條）記錄，“STATUS應(yīng)該為非“INVALID”， 非“DELETED”。注：“STATUS”顯示為空表示正常。檢查Oracle表空間的狀態(tài)select tablespace_name,status from dba_tablespaces;輸出結(jié)果中STATUS應(yīng)該都為ONLINE。檢查Oracle所有數(shù)據(jù)文件狀態(tài)select nam

3、e,status from v$datafile;輸出結(jié)果中“STATUS”應(yīng)該都為“ONLINE”?；蛘撸簊elect file_name,status from dba_data_files;輸出結(jié)果中“STATUS”應(yīng)該都為“AVAILABLE”。檢查無效對象select owner,object_name,object_type from dba_objects where status 二VALID and owner!=SYS and owner!=SYSTEM;如果有記錄返回，則說明存在無效對象。若這些對象與應(yīng)用相關(guān)，那么需要重新編 譯生成這個對象，或者：SELECT owner

4、, object_name, object_type FROM dba_objects WHERE status = INVALID;檢查所有回滾段狀態(tài)select segment_name,status from dba_rollback_segs;輸出結(jié)果中所有回滾段的“STATUS”應(yīng)該為“ONLINE”。2.檢查Oracle相關(guān)資源的使用情況包含：a檢查Oracle初始化文件中相關(guān)的參數(shù)值 b.檢查數(shù)據(jù)庫連接情況，檢查系統(tǒng)磁盤空間c檢查Oracle各個表空間使用情況，檢查一些擴展異常的對象，d檢查system表空間內(nèi)的內(nèi)容，檢查對象的下一擴展與表空間的最大擴展值，總 共七個部分。檢查O

5、racle初始化文件中相關(guān)參數(shù)值select resource_name,max_utilization, initial_allocation, limit_valuefrom v$resource_limit;若 LIMIT_VALU-MAX_UTILIZATIONv = 5，則表明與 RESOURCE_NAME 相 關(guān)的Oracle初始化參數(shù)需要調(diào)整?？梢酝ㄟ^修改Oracle初始化參數(shù)文件 $ORACLE_BASE/admin/CKDB/pfile/initORCL.ora 來修改。檢查數(shù)據(jù)庫連接情況查看當(dāng)前會話連接數(shù)，是否屬于正常范圍。select count(*) from v$se

6、ssion;select sid,serial#,username,program,machine,status from v$session;其中：SID會話(session)的ID號；SERIAL#會話的序列號，和SID 一起用來唯一標(biāo)識一個會話；USERNAME建立該會話的用戶名；PROGRAM這個會話是用什么工具連接到數(shù)據(jù)庫的；STATUS當(dāng)前這個會話的狀態(tài)，ACTIVE表示會話正在執(zhí)行某些任務(wù)， INACTIVE表示當(dāng)前會話沒有執(zhí)行任何操作；如果建立了過多的連接，會消耗數(shù)據(jù)庫的資源，同時，對一些掛死”的連接可能需 要手工進行清理。如果DBA要手工斷開某個會話，則執(zhí)行：(一般不建議使用

7、這種方式去殺掉數(shù)據(jù)庫的連接，這樣有時候session不會斷開。 容易引起死連接。建議通過sid查到操作系統(tǒng)的spid,使用ps -ef|grep spidno 的方式確認(rèn)spid不是ORACLE的后臺進程。使用操作系統(tǒng)的kill -9命令殺掉連 接)alter system kill session SID,SERIAL#;注意：上例中SID為1到10(USERNAME列為空)的會話，是Oracle的后臺進 程，不要對這些會話進行任何操作。檢查系統(tǒng)磁盤空間如果文件系統(tǒng)的剩余空間過小或增長較快，需對其進行確認(rèn)并刪除不用的文件以釋 放空間。oracleAS14 $ df -hFilesystem

8、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/shm檢查表空間使用情況select f.tablespace_name,a.total,f.free,round(f.free / a.total) * 100) % Freefrom (select tablespace_name, sum(bytes / (1024 * 1024) tota

9、lfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name, round(sum(bytes / (1024 * 1024) free from dba_free_spacegroup by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+) order by % Free;如果空閑率%Free小于10%以上(包含10%)，則注意要增加數(shù)據(jù)文件來擴展 表空間而不要是用數(shù)據(jù)文件的自動擴展功能。請不要對表空間增加過多的數(shù)據(jù)文件， 增加數(shù)據(jù)

10、文件的原則是每個數(shù)據(jù)文件大小為2G或者4G，自動擴展的最大限制在 8G。檢查一些擴展異常的對象select Segment_Name,Segment_Type,TableSpace_Name,(Extents / Max_extents) * 100 PercentFrom sys.DBA_SegmentsWhere Max_Extents != 0and (Extents / Max_extents) * 100 = 95order By Percent;如果有記錄返回，則這些對象的擴展已經(jīng)快達到它定義時的最大擴展值。對于這些 對象要修改它的存儲結(jié)構(gòu)參數(shù)。2.6.檢查system表空間內(nèi)的內(nèi)

11、容select distinct (owner)from dba_tableswhere tablespace_name = SYSTEM and owner != SYSand owner != SYSTEMunionselect distinct (owner)from dba_indexeswhere tablespace_name = SYSTEM and owner != SYS and owner != SYSTEM;如果記錄返回，則表明system表空間內(nèi)存在一些非system和sys用戶的對象。 應(yīng)該進一步檢查這些對象是否與我們應(yīng)用相關(guān)。如果相關(guān)請把這些對象移到非 System

12、表空間，同時應(yīng)該檢查這些對象屬主的缺省表空間值。檢查對象的下一擴展與表空間的最大擴展值select a.table_name, a.next_extent, a.tablespace_name from all_tables a,(select tablespace_name, max(bytes) as big_chunk from dba_free_spacegroup by tablespace_name) fwhere f.tablespace_name = a.tablespace_nameand a.next_extent f.big_chunkunion select a.ind

13、ex_name, a.next_extent, a.tablespace_name from all_indexes a,(select tablespace_name, max(bytes) as big_chunk from dba_free_spacegroup by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent f.big_chunk;如果有記錄返回，則表明這些對象的下一個擴展大于該對象所屬表空間的最大擴展 值，需調(diào)整相應(yīng)表空間的存儲參數(shù)。檢査Oracle數(shù)據(jù)庫備份結(jié)果包含

14、：檢查數(shù)據(jù)庫備份日志信息；檢查backup卷中文件產(chǎn)生的時間；檢查oracle用戶的email檢查數(shù)據(jù)庫備份日志信息假設(shè)：備份的臨時目錄為/backup/hotbakup,我們需要檢查2009年7月22日的備份 結(jié)果，則用下面的命令來檢查：cat /backup/hotbackup/hotbackup-09-7-22.log|grep -i error備份腳本的日志文件為hotbackup-月份汩期-年份.log,在備份的臨時目錄下面。如果 文件中存在“ERROR:”，則表明備份沒有成功，存在問題需要檢查。檢查backup卷中文件產(chǎn)生的時間#ls -lt /backup/hotbackupba

15、ckup卷是備份的臨時目錄，查看輸出結(jié)果中文件的日期，都應(yīng)當(dāng)是在當(dāng)天凌晨由熱備 份腳本產(chǎn)生的。如果時間不對則表明熱備份腳本沒執(zhí)行成功。檢查oracle用戶的email#tail -n 300 /var/mail/oracle熱備份腳本是通過Oracle用戶的cron去執(zhí)行的。cron執(zhí)行完后操作系統(tǒng)就會發(fā)一條 Email通知Oracle用戶任務(wù)已經(jīng)完成。查看Oracle email中今天凌晨部分有無ORA-, Error, Failed等出錯信息，如果有則表明備份不正常。檢查Oracle數(shù)據(jù)庫性能在本節(jié)主要檢查Oracle數(shù)據(jù)庫性能情況，包含：檢查數(shù)據(jù)庫的等待事件，檢查死 鎖及處理，檢查cpu

16、、I/O、內(nèi)存性能，查看是否有僵死進程，檢查行鏈接/遷移， 定期做統(tǒng)計分析，檢查緩沖區(qū)命中率，檢查共享池命中率，檢查排序區(qū)，檢查日志 緩沖區(qū)，總共十個部分。檢查數(shù)據(jù)庫的等待事件set pages 80set lines 120col event for a40select sid, event, pl, p2, p3, WAIT_TIME, SECONDS_IN_WAITfrom v$session_waitwhere event not like SQL%and event not like rdbms%;如果數(shù)據(jù)庫長時間持續(xù)出現(xiàn)大量像latch free, enqueue, buffer

17、busy waits, db file sequential read, db file scattered read 等等待事件時，需要對其進 行分析，可能存在問題的語句。Disk Read最高的SQL語句的獲取SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHERE ROWNUM = 5;查找前十條性能差的sql毎SELECT *FROM (SELECT PARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROM V$SQLARE

18、AORDER BY DISK_READS DESC)WHERE ROWNUM 10;4.4.等待時間最多的5個系統(tǒng)等待事件的獲取SELECT *FROM (SELECT *FROM V$SYSTEM_EVENTWHERE 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,OPNAME,ROUND(SOF

19、AR * 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;檢查消耗CPU最高的進程侖SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999COLUMN S_# FORMAT 999COLUMN USERNAME FORMAT A9 H

20、EADING 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_TEXT, 1, 80) SQLFROM V$PROCESS P, V$SESSI

21、ON S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE %& 1%;電檢查碎片程度高的表SELECT segment_name table_name, COUNT( ) extentsFROM dba_segmentsWHERE owner NOT IN (SYS, SYSTEM)GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM dba_segments GROUP BY segment_nam

22、e);%4.8.檢查表空間的I/O比例SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME FILE,F.PHYRDS PYR,F.PHYBLKRD PBR,F.PHYWRTS PYW,F.PHYBLKWRT PBWFROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_IDORDER BY DF.TABLESPACE_NAME;4.9.檢查文件系統(tǒng)的I/O比例SELECT SUBSTR(A.FILE#, 1, 2) #, SUBSTR(A.NAME, 1, 30) NAME, A.STATUS

23、,BYTES,PHYRDS, B.PHYWRTSFROM V$DATAFILE A, V$FILESTAT BWHERE A.FILE# = B.FILE#;4.10.檢查死鎖及處理查詢目前鎖對象信息：select sid,serial#, username, SCHEMANAME, osuser, MACHINE, terminal, PROGRAM,owner,object_name,object_type,o.object_idfrom dba_objects o, v$locked_object l, v$session s where o.object_id = l.object_i

24、dand s.sid = l.session_id;oracle 級 kill 掉該 session：alter system kill session &sid,&serial#;操作系統(tǒng)級kill掉session：#kill -9 pid5.檢查數(shù)據(jù)庫cpu、I/O、內(nèi)存性能記錄數(shù)據(jù)庫的cpu使用、10、內(nèi)存等使用情況，使用vmstat,iostat,sar,top等 命令進行信息收集并檢查這些信息，判斷資源使用情況。5.1 CPU使用情況：rootsale8 # toptop - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37

25、, 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 RE

26、S 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注意上面的藍色字體部分，此部分內(nèi)容表示系統(tǒng)剩余的cpu，當(dāng)其平均值下降至 10%以下的時視為CPU使用率異常，需記錄下該數(shù)值，并將狀態(tài)記為異常。5.2內(nèi)存使用情況：# free -mtotal u

27、sed free shared buffers cachedMem: 2026 1958 67 0 76 1556-/+ buffers/cache: 326 1700Swap: 5992 92 5900如上所示，藍色部分表示系統(tǒng)總內(nèi)存，紅色部分表示系統(tǒng)使用的內(nèi)存，黃色部分表 示系統(tǒng)剩余內(nèi)存，當(dāng)剩余內(nèi)存低于總內(nèi)存的10%時視為異常。5.3系統(tǒng)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.43Devic

28、e: tps kB_read/s kB_wrt n/s kB_read kB_wrt n sda 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系統(tǒng)負(fù)載情況：#uptime12:08:37 up 162 days, 23:

29、33, 15 users, load average: 0.01, 0.15,0.10如上所示，藍體字部分表示系統(tǒng)負(fù)載，后面的3個數(shù)值如果有高于2.5的時候就 表明系統(tǒng)在超負(fù)荷運轉(zhuǎn)了，并將此值記錄到巡檢表，視為異常。查看是否有僵死進程select spid from v$process where addr not in (select paddr from v$session);有些僵尸進程有阻塞其他業(yè)務(wù)的正常運行，定期殺掉僵尸進程。檢查行鏈接/遷移select table_name, num_rows, chain_cntFrom dba_tablesWhere owner = CTAIS

30、2And chain_cnt 0;注：含有l(wèi)ong raw列的表有行鏈接是正常的，找到遷移行保存到chained_rows表中，如沒有該表執(zhí)行./rdbms/admin/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 where a.rowid b.head_rowid and b.table_name =SB_ZSXX

31、;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定期做統(tǒng)計分析對于采用Oracle Cost-Based-Optimizer的系統(tǒng)，需要定期對數(shù)據(jù)對象的統(tǒng)計信 息進行采集更新，使優(yōu)化器可以根據(jù)準(zhǔn)備的信息作出正確的explain plan。在以 下情況更需要進行統(tǒng)計信息的更新：應(yīng)用發(fā)生變化大規(guī)

32、模數(shù)據(jù)遷移、歷史數(shù)據(jù)遷出、其他數(shù)據(jù)的導(dǎo)入等c 數(shù)據(jù)量發(fā)生變化查看表或索引的統(tǒng)計信息是否需更新，如：Select table_name,num_rows,last_analyzed From user_tables where table_name =DJ_NSRXXselect count(*) from DJ_NSRXX 如 num_rows 和 count()如果行數(shù)相差很多，則該表需要更新統(tǒng)計信息，建議一周做一次統(tǒng)計信息收集，如:exec sys.dbms_stats.gather_schema_stats(ownname=CTAIS2,cascade = TRUE,degree = 4

33、);5.8檢查緩沖區(qū)命中率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%則需加大數(shù)據(jù)庫參數(shù)db_cache_size。5.9檢查共享池命中

34、率select sum(pinhits) / sum(pins) * 100 from v$librarycache;如低于95%，則需要調(diào)整應(yīng)用程序使用綁定變量，或者調(diào)整數(shù)據(jù)庫參數(shù)shared pool的大小。5.10檢查排序區(qū)select name,value from v$sysstat where name like %sort%;如果disk/(memoty+row)的比例過高，則需要調(diào)整 sort_area_size(workarea_size_policy=false)或 pga_aggregate_target(workarea_size_policy=true)。5.11檢查

35、日志緩沖區(qū)select name,value from v$sysstat where name in (redo entries,redo buffer allocation retries);如果 redo buffer allocation retries/redo entries 超過 1%,則需要增大 log_buffer。檢查數(shù)據(jù)庫安全性在本節(jié)主要檢查Oracle數(shù)據(jù)庫的安全性，包含：檢查系統(tǒng)安全信息，定期修改密 碼，總共兩個部分。檢查系統(tǒng)安全日志信息系統(tǒng)安全日志文件的目錄在/var/log下，主要檢查登錄成功或失敗的用戶日志信 息。檢查登錄成功的日志：rootrac2 # gre

36、p -i accepted /var/log/secureJan 8 08:44:43 rac2 sshd29559: Accepted password for rootfrom :ffff: por t 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:Jan 9 10:30:56 rac2 ss

37、hd3071: Failed password for in valid user ydbuser from :ffff: port 36005 ssh2Jan 9 10:30:56 rac2 sshd3071: Failed password for in valid userydbuser from :ffff: port 36005 ssh2Jan 10 22:44:38 rac2 sshd21611: Failed password for rootfrom :ffff: port 1723 ssh2在出現(xiàn)的日志信息中沒有

38、錯誤(Invalid、refused)提示，如果沒有(Invalid、 refused)視為系統(tǒng)正常，出現(xiàn)錯誤提示，應(yīng)作出系統(tǒng)告警通知。檢查用戶修改密碼在數(shù)據(jù)庫系統(tǒng)上往往存在很多的用戶，如：第三方數(shù)據(jù)庫監(jiān)控系統(tǒng)，初始安裝數(shù)據(jù) 庫時的演示用戶，管理員用戶等等，這些用戶的密碼往往是寫定的，被很多人知道, 會被別有用心的人利用來攻擊系統(tǒng)甚至進行修改數(shù)據(jù)。需要修改密碼的用戶包括： 數(shù)據(jù)庫管理員用戶SYS，SYSTEM；其他用戶。登陸系統(tǒng)后，提示符下輸入cat /etc/passwd，在列出來的用戶中查看是否存在 已經(jīng)不再使用的或是陌生的帳號。若存在，則記錄為異常。修改密碼方法：alter user U

39、SER_NAME identified by PASSWORD;其他檢查在本節(jié)主要檢查當(dāng)前cron tab任務(wù)是否正常，檢查Oracle Job是否有失敗等共六 個部分。7.1檢查當(dāng)前cron tab任務(wù)是否正常oracleAS14 $ cro ntab -l7.2 Oracle Job是否有失敗select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user CAIKE;如有問題建議重建job,如：exec sys.dbms_job.remove(1);commit;execsys.dbms_job.isubmit(1,REFRESH_ALL_SNAPSHOT;,SYSDATE +1/1440,SYSD ATE+4/1440);co